355 replies to this topic

[stubbsy]

Thanks very much for posting this tip. I noticed the problem when I was installing a contribution and thought that was the cause.

I was losing all hope of fixing it after replacing the original files and restoring the database.

It turned out to be that the hosts had updated SQL and it just happened to coincide with me installing my contribution.

Thanks again!

[DeKemp]

Simplyeasier, on Apr 1 2005, 11:10 AM, said:

Hi  

First off - this fix is actually already recorded in the contributions section and many thanks to those who recorded it - but I don't think it is a contribution - rather a tip - Secondly the same problem has arisen for 4 people in a few days and I dare say it will happen to more as hosts migrate their mySQL databases to version 4.1.xx -

Hence I post here  

If you get an error whilst creating \ modifying your database such that thro admin you delete all entries on a particular table you will likely get this message

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-20, 20' at line 1

followed by something like

select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

or

select banners_id, banners_title, banners_image, banners_group, status, expires_date, expires_impressions, date_status_change, date_scheduled, date_added from banners order by banners_title, banners_group limit -20, 20

depending on which table you have deleted all records from.

To fix this

In admin/includes/classes find split_page_results.php and - BACK IT UP then find the lines

$offset = ($max_rows_per_page * ($current_page_number - 1));
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

change to

$offset = ($max_rows_per_page * ($current_page_number - 1));
if ($offset < 0)
{
$offset = 0 ;
}
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
MySQL 4.1.xx handles negatives correctly (by forcing an error) in the code above unlike earlier versions of MySQL.

  NOTE - I have NOT asked you to change catalog/includes/classes/split_page_results.php  - If you have a problem on the catalog side you may want to try the same change in that file - I did and it went wrong so the change to my catalog/includes/classes/split_page_results.php  file is a little more elaborate and I will share that here if it becomes an issue - but for now it seems people will mostly have a problem with empty files on the admin side.

hth

Charles

Fifty Cheers.
Went to PhP 5 MySql 4.1+ yesterday night.
Got the described 1064, this morning when checking customer/orders.
1 search for 1064, your solution, applied, works.
Yes YOU ARE a CHAMP !
thanks

Walter Kempees (NL 2.2MS2)
(I am now going to search why some have such a nice Admin interface with icons and colors and stuff, and mine is plain grey)

[mouschi]

you are simply a miracle worker thanks

[dreamingdigital]

Cool. On the right track now. I did the fix on Post #1 here.

The problem I was having was in my "Specials" admin page. I did your fix and now I can use my Specials admin. Now got a new issue: On the client side, the specials.php page displays the total number of products -1. SO one less than it should. Is there another file I need modify?

Colin

[luciano]

Whatever I do, I keep getting

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-20, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20


And I'm getting a little nerveous :-)

I don't think that there is anything I didn't try. Is there a way that someone would post the complete altered working files?  Or is there a way to excude this LIMIT thing? Because I want to have the site running... It has been weeks now that I'm trying to get this fixed...

Edited by luciano, 06 October 2005 - 02:44 PM.

[luciano]

BTW I emptied split_page results in admin... and I keep getting the same error ?!

I wonder then how the error can be in that file?

[dreamingdigital]

If you can, downgrade your MySQL to 4.0.XX and it should solve the issue. Did with me!

CP

[luciano]

That's not an option.... But thanks anyway

[DeKemp]

luciano, on Oct 6 2005, 04:41 PM, said:

Whatever I do, I keep getting

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-20, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20
And I'm getting a little nerveous :-)

I don't think that there is anything I didn't try. Is there a way that someone would post the complete altered working files?  Or is there a way to excude this LIMIT thing? Because I want to have the site running... It has been weeks now that I'm trying to get this fixed...

The fix worked for me, should work for u too.
I'll try and trace your problem, donot despair, I'll be back.

Walter

[DeKemp]

DeKemp, on Oct 7 2005, 04:49 AM, said:

The fix worked for me, should work for u too.
I'll try and trace your problem, donot despair, I'll be back.

Walter

Found the following text in teh BUG list, it is posted by HPDL.
Basically the same fix as post #1 but more compact:

Quote

29 Sep 2005 19:21:49 hpdl (AT) oscommerce (DOT) com

Thanks for the report, here is the official fix:

Line 67 in catalog/includes/classes/split_page_results.php must be changed from:

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to:

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page;

Line 38 in catalog/admin/includes/classes/split_page_results.php must be changed from:

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

to:

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

[luciano]

DeKemp, on Oct 7 2005, 05:05 AM, said:

Found the following text in teh BUG list, it is posted by HPDL.
Basically the same fix as post #1 but more compact:

Thanks for the effort Walter... but as I wrote: I tried it all and nothing helped.

Isn't it strage that even an empty split.... . php gave me the same error. That can only mean that the error is somewhere else.

So what I'm going to try now is installing one of the add-ons and if that doesn't work I delete the whole thing and start over with an easier system.

[luciano]

In admin/orders.php i found this bit of code

<?php
	if (isset($HTTP_GET_VARS['cID'])) {
	  $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
	} elseif (isset($HTTP_GET_VARS['status'])) {
	  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
	} else {
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
	}

Is there a way to exclude the DESC? Will that help? Is there a way to get rid of that LIMIT stuff? Is it possible to just generate a plain list, no split page?

[DeKemp]

luciano, on Oct 7 2005, 12:17 PM, said:

In admin/orders.php i found this bit of code

<?php
	if (isset($HTTP_GET_VARS['cID'])) {
	  $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
	} elseif (isset($HTTP_GET_VARS['status'])) {
	  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
	} else {
	  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
	}

Is there a way to exclude the DESC? Will that help? Is there a way to get rid of that LIMIT stuff? Is it possible to just generate a plain list, no split page?

you do not want to loose the DESC.
it means descending as oposed to ASC

rhis raw sql is being parsed somewhere and gets the limit added.

Still looking 4U

[luciano]

DeKemp, on Oct 7 2005, 12:49 PM, said:

you do not want to loose the DESC.
it means descending as oposed to ASC

rhis raw sql is being parsed somewhere and gets the limit added.

Still looking 4U

Thanks  

[murray_simon]

Hi There, Please help I am having kittens here...

I have done a very stupid thing whilst trying to figure out easy populate - I think I have managed to delete all my products tables - and what this means is that I can no longer access the admin panel in the browser, instead I get the error message:

1146 - Table 'cmm_com_au_-_osc6.configuration' doesn't exist

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

[TEP STOP]

I am running on MySQL 3.23.58

I contacted my hosting company and they are pretty useless and offer no support at all - they did suggest however to go to MyphpAdmin and select the database->SQL and then browse for the backup file and upload which I did and I get this error in Myphp after the upload

SQL-query:

-- MySQL dump 8.23
--
-- Host: localhost Database: cmm_com_au_-_osc6
------------------------------------------------------- --
-- Server version 3.23.58
--
-- Table structure for table `address_book`
--
DROP TABLE IF EXISTS address_book

MySQL said: Documentation
#1064 - You have an error in your SQL syntax near '---------------------------------------------------------
-- Server version    3.23' at line 4


If anyone could please help me out of this mess I will be eternally grateful... I did back up, but somehow managed to destroy the database anyway...

[murray_simon]

murray_simon, on Nov 29 2005, 11:33 AM, said:

Hi There, Please help I am having kittens here...

I have done a very stupid thing whilst trying to figure out easy populate - I think I have managed to delete all my products tables - and what this means is that I can no longer access the admin panel in the browser, instead I get the error message:

1146 - Table 'cmm_com_au_-_osc6.configuration' doesn't exist

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

[TEP STOP]

I am running on MySQL 3.23.58

I contacted my hosting company and they are pretty useless and offer no support at all - they did suggest however to go to MyphpAdmin and select the database->SQL and then browse for the backup file and upload which I did and I get this error in Myphp after the upload

SQL-query:

-- MySQL dump 8.23
--
-- Host: localhost Database: cmm_com_au_-_osc6
------------------------------------------------------- --
-- Server version 3.23.58
--
-- Table structure for table `address_book`
--
DROP TABLE IF EXISTS address_book

MySQL said: Documentation
#1064 - You have an error in your SQL syntax near '---------------------------------------------------------
-- Server version    3.23' at line 4


If anyone could please help me out of this mess I will be eternally grateful... I did back up, but somehow managed to destroy the database anyway...

I fixed it - Phew!

The error that was appearing (MySQL said: Documentation
#1064 - You have an error in your SQL syntax near '---------------------------------------------------------
-- Server version 3.23' at line 4) led me to look into the backup SQL file that I had saved and I also opened an earlier saved version and I noticed there was a difference between the two.

The backup file that would not work was like this:

-- MySQL dump 8.23---- Host: localhost    Database: cmm_com_au_-_osc6 ----------------------------------------------------------- Server version 3.23.58----

an earlier version looked different so I changed it to look the same i.e.

-- MySQL dump 8.23---- Host: localhost    ##Database: cmm_com_au_-_osc6# Server version 3.23.58----

The difference was the # sign before and after and getting rid of the ------------------ not sure which bit fixed it, but it now works

I re-uploaded it and hey presto it worked!! Yippppeeeee

[DeKemp]

murray_simon, on Nov 29 2005, 07:07 AM, said:

I fixed it - Phew!

The error that was appearing (MySQL said: Documentation
#1064 - You have an error in your SQL syntax near '---------------------------------------------------------
-- Server version 3.23' at line 4) led me to look into the backup SQL file that I had saved and I also opened an earlier saved version and I noticed there was a difference between the two.

The backup file that would not work was like this:

-- MySQL dump 8.23---- Host: localhost    Database: cmm_com_au_-_osc6 ----------------------------------------------------------- Server version 3.23.58----

an earlier version looked different so I changed it to look the same i.e.

-- MySQL dump 8.23---- Host: localhost    ##Database: cmm_com_au_-_osc6# Server version 3.23.58----

The difference was the # sign before and after and getting rid of the ------------------ not sure which bit fixed it, but it now works

I re-uploaded it and hey presto it worked!! Yippppeeeee

[Vger]

All of these issues are now dealt with by the updated version of MS2 available from www.oscommerce.com - to which a direct link is provided at the top of the forum homepage.

Vger

[Sliper]

A noob having a similiar problem, although mine isn´t because of mysql 4.xxx since I´m running 3.23.58-
I was installing a contribution and after altering the DB i got this message:

----------------------------------------------------------------
1064 - You have an error in your SQL syntax near ' p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, ' at line 1

select p.products_image, pd.products_name, pd.products_description,, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '59' order by pd.products_name

[TEP STOP]
---------------------------------------------------------------

Does anyone know what is wrong? I need to fix this fast cos im knee deep in it now that the site is down :-(
I´m afraid of doing anything before consulting some pro help here...

[Sliper]

Referring to my post above. When I replaced the edited files with the backups, somehow it never happened. Don´t know why but I tried to replace the files with the backed upped ones and this time the site got working again. So, this time I managed not to screw it up... *pheewww*

It´s not fun to explain to the boss that we ain´t doing business on the web today cos I messed it up and have no clue on how to fix it :-D