Jump to content

Archived

This topic is now archived and is closed to further replies.

Simplyeasier

1064 - You have an error in your SQL syntax

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 :D

:thumbsup:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi! for me, problems were instantly solved apllying this solution.

Thank you, Charles!

 

Andr?s.

 

 

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;

 

 

hth

 

Charles

Share this post


Link to post
Share on other sites
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.

 

Charles, I have this problem in the catalog. could you please send your more complicated fixes at this point?

Share this post


Link to post
Share on other sites

Simplyeasier IS THE MASTA! WOW! How the hell did you figure that out? I have know Idea how. That was amazing. Literally. I deleted the tax zones default of Florida (as the tutorial I was using recommended) and got that freaky ass error. I googled it and PRESTO! Thanks for saving me boatloads of stress O great wise one. Keep it up!!!!!

 

http://www.notfarnow.com

Share this post


Link to post
Share on other sites

HELP PLEASE! I am having this error and don't know what to do.

 

 

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 'order by pd.products_name' at line 1

 

select count(p.products_id) as total order by pd.products_name

Share this post


Link to post
Share on other sites

Hi there,

 

I tried the above but am still having the following error message: (after installation of contribution purchase without account)

 

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 'customers_id, select customers_name, customers_company, custome

 

customers_id, select customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, currency, currency_value, date_purchased, orders_status, last_modified, cc_start, cc_issue, cc_cvv from orders where orders_id = '29'

 

can anybody help me fixing it? Will send you a box of sweets :)

Share this post


Link to post
Share on other sites

mySQL query error: SELECT * FROM ibf_skin_templates_cache WHERE template_set_id= AND template_group_name IN ('skin_global', 'skin_shoutbox')

 

SQL error: 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 'AND template_group_name IN ('skin_global', 'skin_shoutbox')' at

SQL error code:

Date: Saturday 18th of February 2006 03:08:38 AM

 

Apache version 1.3.34 (Unix)

 

PERL version 5.8.7

 

Path to PERL /usr/bin/perl

 

Path to sendmail /usr/sbin/sendmail

 

Installed Perl Modules Click to View

 

PHP version 4.4.1

 

MySQL version 4.0.25-standard-log

 

cPanel Build 10.8.1-STABLE 114

 

Theme cPanel X v2.5.0

 

plz help help help my site is going down plz guys !!

Share this post


Link to post
Share on other sites

Hi guys, I really need help now...

 

I installed CCC 9.3, works fine exept for a small detail, when I click on the link in the shopping cart infobox after the creation of a build it sends me the following error.

 

[sTART CODE]

 

 

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

[TEP STOP]

 

 

[END CODE] Any ideas that could have me go in the right direction, also, I think this causes to not have the builds show in the pendings on the admin side...

 

Thanks to all


Martin Raymond

Xtreme Medias, Owner

Share this post


Link to post
Share on other sites
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

 

Thanks Charles - I tried what you asked me to do, but I cannot edit the file at all. I was able to copy what you wrote, but not paste or cut. I did back up the original file.

 

I am not a programmer. All I did was to open the file and tried to modify it. Is there something else I am supposed to do? The file properties were set for read, write and execute.

 

 

Debi

Share this post


Link to post
Share on other sites

Charles:

 

I tried what you told me to do, but I cannot edit the file at all. I checked the properties of the file, and it is set for write, read and execute. I cannot cut, paste or type in the file. I did back the file up.

 

I am not a programmer. All I did was to open the file. Is there something else, I am supposed to do?

 

Debi :huh:

Share this post


Link to post
Share on other sites

Charles:

 

I cannot edit the file. It won't let me type in the file or paste into the file. Please help.

 

Debi

Share this post


Link to post
Share on other sites

thank you so much for the help! :)

 

dvoris you must go into your ftp and copy it to your local PC then edit with wordpad the save and upload and overite the orignal! >_<

Share this post


Link to post
Share on other sites

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

 

 

Anybody has an idea!!!!


Martin Raymond

Xtreme Medias, Owner

Share this post


Link to post
Share on other sites

Hi,

when i create a system with CCC 9.3 it goes to the cart, than when I press on the shopping cart box link it sends me a error message like so:

 

 

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

[TEP STOP]

 

[code stop]

 

Any ideas before I submit my own revemped version of CCC.


Martin Raymond

Xtreme Medias, Owner

Share this post


Link to post
Share on other sites

Charles,

 

Thank you so much. This fix worked on the first try. I had 4 1064 errors and it fixed them all.

 

Again, Thank you for helping the less knowledgable users work through problems!

 

Gidget

Share this post


Link to post
Share on other sites

It worked for me straight up from the initial posting by "Simplyeasier"

Thank you soo much.

 

It seemed that my hosting provider upgraded their SQL without notifying me and when I checked I had that dreaded "1064 - You have an error in your SQL syntax" error.

 

Everything works fine now.

Share this post


Link to post
Share on other sites

Hello,

 

I have a little problem with Shipping modules. If I install a shipping module in the admin panel, I received the following messages :

 

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 'True', 'False'), ', now())' at line 1

 

insert into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Enable FLAT Shipping', 'MODULE_SHIPPING_FLAT_STATUS', 'True', 'Do you want to offer FLAT rate shipping?', '6', '0', 'tep_cfg_select_option(array('True', 'False'), ', now())

 

[TEP STOP]

 

Any help?

Thanks...

Share this post


Link to post
Share on other sites

Hi guys, I've been reading through the post and have a smiliar problem... On my catalogue side of things (on the index.php page), I am getting the following error (instead of a list of products):

 

1064 - You have an error in your SQL syntax near '-_oscommerce' at line 1

 

show table status from domainname_com_au_-_oscommerce

 

[TEP STOP]

 

Please please please does anyone know what's happening?

 

Thanks, Barbara

Share this post


Link to post
Share on other sites
Hi, thanks for posting this, unfortunately it did not fix my problem. =(

 

Here is what I am getting on catalog/account_history.php

 

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

 

select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from orders o, orders_total ot, orders_status s where o.customers_id = '20' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '1' order by orders_id DESC limit -10, 10

 

I did just change my orders.php but I had to in order to get the Cash/Check orders to show up in my admin section again.

 

Help!

 

Here is my /includes/classes/split_page code

 

  $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));
 // begin make sure that $offset is not lower than 0
 if ($offset < 0 ) {
  $offset = 0;
 }
 // end make sure that $offset is not lower than 0
 $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

 

MECHBUN,

Did you ever get this working properly? I am having the EXACT same issue/error and can't get it resolved.

 

If no orders were made it appears fine. Once an order is placed I get the 1064 error in the "order history" page.

 

I added the code to the admin/includes/class/split...but not sure what to do now to get it totaly resolved.

 

What exactly did you do?

 

Thanks!

~Mark

Share this post


Link to post
Share on other sites

Ok, got everything working now. wheeeewww....

 

The only thing is now my customer "order history" has -4, -3, -2, -1, 0 results pages.

 

I KNOW I saw this when I was looking through the 10,000 threads today but can't find it now.

 

~M

Share this post


Link to post
Share on other sites

×