Jump to content



Photo
* * * * * 2 votes

1064 - You have an error in your SQL syntax


This topic has been archived. This means that you cannot reply to this topic.
355 replies to this topic

#1   Simplyeasier

Simplyeasier
  • Members
  • 966 posts

Posted 01 April 2005 - 09:10

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 /biggrin.gif' class='bbc_emoticon' alt=':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.

/ohmy.gif' class='bbc_emoticon' alt=':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

Edited by Jan Zonjee, 26 July 2008 - 07:53.

A kite flies highest AGAINST the wind !

"Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body, but rather to skid in sideways, a lover in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO!! What a ride!"

#2   antonie

antonie
  • Members
  • 36 posts

Posted 01 April 2005 - 10:26

OK. Thx 4 ur wamrly support!
My site works fine now...

#3   akasharkbow

akasharkbow
  • Members
  • 52 posts

Posted 09 April 2005 - 22:55

Thanks so much. Great work!

Cheers,

David

#4   janetgot

janetgot
  • Members
  • 149 posts

Posted 18 April 2005 - 14:16

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

#5   Simplyeasier

Simplyeasier
  • Members
  • 966 posts

Posted 18 April 2005 - 17:13

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 /sad.gif' class='bbc_emoticon' alt=':(' />

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
A kite flies highest AGAINST the wind !

"Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body, but rather to skid in sideways, a lover in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO!! What a ride!"

#6   Jan Zonjee

Jan Zonjee

    Governor of Peace

  • Core Team
  • 7,095 posts

Posted 18 April 2005 - 21:08

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.

#7   janetgot

janetgot
  • Members
  • 149 posts

Posted 19 April 2005 - 01:17

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.gif' class='bbc_emoticon' alt=':blink:' />

Many thanks for your assistance (again!)
Janet

#8   sidmel

sidmel
  • Members
  • 17 posts

Posted 25 April 2005 - 19:09

Great tip.

Thanks for the post.

#9   Deek

Deek
  • Members
  • 3 posts

Posted 26 April 2005 - 09:55

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

#10   NJTackle

NJTackle
  • Members
  • 16 posts

Posted 26 April 2005 - 21:51

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.

#11   tdreher

tdreher
  • Members
  • 1 posts

Posted 10 May 2005 - 12:22

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  /biggrin.gif' class='bbc_emoticon' alt=':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.

/ohmy.gif' class='bbc_emoticon' alt=':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}>



#12   ebayer

ebayer
  • Members
  • 68 posts

Posted 18 May 2005 - 20:30

Great solution!

Love u!

#13   Mandarin Mark

Mandarin Mark
  • Members
  • 20 posts

Posted 22 May 2005 - 20:18

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.

#14   ekendra

ekendra
  • Members
  • 9 posts

Posted 23 May 2005 - 22:30

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?

#15   Simplyeasier

Simplyeasier
  • Members
  • 966 posts

Posted 23 May 2005 - 22:35

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
A kite flies highest AGAINST the wind !

"Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body, but rather to skid in sideways, a lover in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO!! What a ride!"

#16   ekendra

ekendra
  • Members
  • 9 posts

Posted 23 May 2005 - 22:37

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



#17   ekendra

ekendra
  • Members
  • 9 posts

Posted 23 May 2005 - 22:40

FIXED!!!


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


/tongue.gif' class='bbc_emoticon' alt=':P' />


cheers,
ekendra

/thumbsup.gif' class='bbc_emoticon' alt=':thumbsup:' />

#18   Sierrab

Sierrab
  • Members
  • 290 posts

Posted 29 May 2005 - 13:35

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

#19   Simplyeasier

Simplyeasier
  • Members
  • 966 posts

Posted 30 May 2005 - 21:36

I am running on MySQL 4.023a

<{POST_SNAPBACK}>


This is a fix for MySQL 4.1.xx

Charles
A kite flies highest AGAINST the wind !

"Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body, but rather to skid in sideways, a lover in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO!! What a ride!"

#20   christy

christy
  • Members
  • 18 posts

Posted 31 May 2005 - 05:44

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

I got the dreaded 1064 error when I deleted all of the manufacturers. Oops! /blink.gif' class='bbc_emoticon' alt=':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! /crying.gif' class='bbc_emoticon' alt=':'(' />