1064 - You have an error in your SQL syntax

[Simplyeasier 0]

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 :D

 

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.

 

:o 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.

 

Change (catalog/includes/classes/split_page_results.php around line 67) 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;

 

Both on the catalog side and the admin side changing $offset to max($offset, 0) is the "official" fix.

 

hth

 

Charles

[antonie 0]

OK. Thx 4 ur wamrly support!

My site works fine now...

[akasharkbow 0]

Thanks so much. Great work!

 

Cheers,

 

David

[janetgot 0]

I am getting the following error, when I try to delete products through the admin section, and I'm wondering if it is related to what you are discussing here? I have tried your fix, but it didn't resolve the problem... the error is a bit different though.

 

I get at this url catalog/admin/categories.php?action=delete_product_confirm&cPath=81 the following error:

 

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 '' at line 1

 

delete from products_attributes where products_id=

 

[TEP STOP]

 

The only place I see in the site where the "delete from products_attributes where products_id=" is is in the admin/includes/classes/database.php file, where it says:

 

function jjg_db_attributeSets($arr_attributeSetID, $productID, $action, $link = 'db_link'){

 

//remove all references to this product_id in these tables, and reinsert new ones

if( $action == 'update_product' || $action == 'delete_product_confirm'){

$delete_query = "delete from products_attributes where products_id=".$productID;

tep_db_query($delete_query, $link);

$delete_query = "delete from products_attributes_sets_to_products where products_id=".$productID;

tep_db_query($delete_query, $link);

}

//if you remove the product, then our work is done

if( $action == 'delete_product_confirm' ){

return;

}

 

Thanks for any clues! This wasn't broken in the past, but I've been adding several contributions, including the attribute sets contribution.

 

Janet

[Simplyeasier 0]

I am getting the following error, when I try to delete products through the admin section, and I'm wondering if it is related to what you are discussing here?  I have tried your fix, but it didn't resolve the problem... the error is a bit different though.

 

I get at this url catalog/admin/categories.php?action=delete_product_confirm&cPath=81 the following error:

 

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 '' at line 1

 

delete from products_attributes where products_id=

 

[TEP STOP]

 

The only place I see in the site where the "delete from products_attributes where products_id=" is is in the admin/includes/classes/database.php file, where it says:

 

  function jjg_db_attributeSets($arr_attributeSetID, $productID, $action, $link = 'db_link'){

 

  //remove all references to this product_id in these tables, and reinsert new ones

  if( $action == 'update_product' || $action == 'delete_product_confirm'){

    $delete_query = "delete from products_attributes where products_id=".$productID;

    tep_db_query($delete_query, $link);

    $delete_query = "delete from products_attributes_sets_to_products where products_id=".$productID;

    tep_db_query($delete_query, $link);

  }

//if you remove the product, then our work is done

  if( $action == 'delete_product_confirm' ){

    return;

  }

 

Thanks for any clues!  This wasn't broken in the past, but I've been adding several contributions, including the attribute sets contribution.

 

Janet

<{POST_SNAPBACK}>

 

 

I would suggest the contrib work you refer to has broken your site :(

 

The fix here is only if you have an instal on MySQL v 4.1.xx AND you delete all of the records on any of the tables.

 

Charles

[Jan Zonjee 59]

Janet,

 

Regarding:

    $delete_query = "delete from products_attributes where products_id=".$productID;

Try: (single quote, double quote, dot, (integer)$productID, dot, double quote, single quote, double quote)

$delete_query = "delete from products_attributes where products_id='".(integer)$productID."'";

Also the $productID shouldn't contain any references to attributes ( with {number} ) in it. Therefore the casting to an integer.

[janetgot 0]

Janet,

 

Regarding:

 ? ?$delete_query = "delete from products_attributes where products_id=".$productID;

Try: (single quote, double quote, dot, (integer)$productID, dot, double quote, single quote, double quote)

$delete_query = "delete from products_attributes where products_id='".(integer)$productID."'";

Also the $productID shouldn't contain any references to attributes ( with {number} ) in it. Therefore the casting to an integer.

<{POST_SNAPBACK}>

 

Thanks Jan, I tried your code suggestion (well the first one, anyway, I wasn't sure where to edit the $productID though). I still get the error, plus there are other errors when I try to add an attribute that I hadn't seen prior to posting my question. I think my best bet is to uninstall the attribute sets contrib and see if the site stabilizes, and then figure what to do next. I need to add size attributes to all the products, which the attribute sets would be great for, since there's so much repetition, but I also have tons of unique colors and color combos, which need to be added individually. It seems that attribute sets isn't meant to be combined with using the attributes section too... sigh... :blink:

 

Many thanks for your assistance (again!)

Janet

[sidmel 0]

Great tip.

 

Thanks for the post.

[Deek 0]

worked very well.. now I just need to sort my other problem.

[NJTackle 1]

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.

 

Yep, problem there to. When you list 'all order' when logged in as a customer you get a similar error. I'm going to try and fix it myself with the admin patch from above.

[Guest]

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  :D

 

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.

 

:o  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

<{POST_SNAPBACK}>

[ebayer 0]

Great solution!

 

Love u!

[Guest]

My error message is slightly different and reads:

 

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 ''' at line 1

 

delete from products_options where products_id =206'

 

[TEP STOP]

 

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

[Guest]

I tried your fix and I thought it worked but now when I go to delete a manufacturer I get this:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/prabhu/public_html/shop/admin/includes/classes/split_page_results.php:110) in /home/prabhu/public_html/shop/admin/includes/functions/general.php on line 18

 

what do you think might have happened?

[Simplyeasier 0]

I tried your fix and I thought it worked but now when I go to delete a manufacturer I get this:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/prabhu/public_html/shop/admin/includes/classes/split_page_results.php:110) in /home/prabhu/public_html/shop/admin/includes/functions/general.php on line 18

 

what do you think might have happened?

<{POST_SNAPBACK}>

 

 

You most probably have white lines at the top or bottom of your amended file.

 

Use an pure text editor (notepad will do) and make sure all white lines at the top and bottom of your file are deleted.

 

Then try again.

 

Charles

[Guest]

I also get this when I try to add a manufacturer now:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/prabhu/public_html/shop/admin/includes/classes/split_page_results.php:110) in /home/prabhu/public_html/shop/admin/includes/functions/general.php on line 18

[Guest]

FIXED!!!

 

 

DUDE! YOU'RE A CHAMPION!!!!!

 

 

:P

 

 

cheers,

ekendra

 

:thumbsup:

[Sierrab 1]

I tried the fix with no joy.....

 

I get the error when I select Customers/Orders in admin. I looked at the orders table, it seemed fine.

I am running on MySQL 4.023a

 

The only changes to the basic db have been the addition of the Downloads Controller and the Master/slave sql files.

 

All other db functions seem to work fine.

 

For a moment I thought I had found the solution...ah well

 

Thnks anyway...any other thoughts that may be of help here

[Simplyeasier 0]

I am running on MySQL 4.023a

 

<{POST_SNAPBACK}>

 

This is a fix for MySQL 4.1.xx

 

Charles

[Guest]

Ok, I tried that, but it didn't work.

 

I got the dreaded 1064 error when I deleted all of the manufacturers. Oops! :blink: How do I add manufacturers now?

 

So I am in the admin section trying to edit the manufacturers.

 

"Manufacturers

 

Manufacturers Action

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 manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

 

[TEP STOP] "

 

Heeellp! :'(

[Simplyeasier 0]

Ok, I tried that, but it didn't work.

 

I got the dreaded 1064 error when I deleted all of the manufacturers. Oops!  :blink: How do I add manufacturers now?

 

So I am in the admin section trying to edit the manufacturers.

 

"Manufacturers 

 

Manufacturers Action 

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 manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

 

[TEP STOP]  "

 

Heeellp! :'(

<{POST_SNAPBACK}>

 

Are you running mysql 4.1.xx ? Have you changed the correct file in EXACTLY the way described ?

 

This has worked for the others - should work for you if the answers to the 2 questions are yes :D

 

Charles

[Guest]

Are you running mysql 4.1.xx ? Have you changed the correct file in EXACTLY the way described ?

 

This has worked for the others - should work for you if the answers to the 2 questions are yes  :D

 

Charles

<{POST_SNAPBACK}>

 

I figured it out. I went to my host, Dreamhost, and entered a value from there. They have a nice MySQL interface :)

 

Thanks,

Christy

[basalt 0]

Please HELP

 

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 ' products_id from orders_products where orders_id = '70'' at li

 

select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from, products_id from orders_products where orders_id = '70'

 

[TEP STOP]

 

 

Thx,

Basalt

[Guest]

I'm still on MySQL 3.23 ... and getting this error. is there a workaround for this?

[Guest]

what error are you getting techbot?