Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

MYSQL 1064 errors in admin area.


nobadeer

Recommended Posts

I have seen this problem all over the forum but no one has come up with a solution. My ISP upgraded MYSQL from 3.26 to 4.17. After doing so any portion of OSCommerce that did not have data in it (reviews, specials, pending, and processing) returns a 1064 error if I click on it in the admin area. The end of the error has -20, 20 in it. Each portion of the admin area has a different error message (here is the error from specials for example)

 

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 p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

 

here is the error from Orders:

 

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 = '1' and s.orders_status_id = '2' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

These problems were non-existent before the upgrade to mySQL. Prior to this OSCommerce worked fine, orders were placed, customers were happy. Now every time an order is placed the info. gets lost and the credit card is not processed. Other people on the forum have run in to this as well. There only solution was to enter data, then the problem went away. The only problem is sometimes there won't be any data in pending or processing, therefore we will continue to get errors. I have not made any mods to OSCommerce, the only plugin I use is STS_Templates. Thanks in advance for your help. Please help. Thanks.

Link to comment
Share on other sites

I don't have the problem (yet) so I can't check it quickly, but perhaps it has to do with the code in classes/split_page_results. Part of the code says (line number 37-38):

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

I suspect that if the $current_page is 0 then the limit would be -20,20 if $max_rows_per_page is 20.

If that is the case you could easily prevent it with a few extra lines of code in front of it to catch that.

Something like

IF ($current_page_number == '' || $current_page_number == '0' ) {
$current_page_number = '1';
}

perhaps?

Link to comment
Share on other sites

Thanks JanZ, i added that code and still got an error 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 '-15, 15' 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 = '1' and s.orders_status_id = '2' and ot.class = 'ot_total' order by o.orders_id DESC limit -15, 15

Link to comment
Share on other sites

i added that code
To admin/includes/classes/split_page_results.php or catalog/includes/classes/split_page_results.php? That class definitely has a limit x, y in it that influence viewing of orders... I can see that when I change the $sql in those lines to:

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

Maybe the example code I gave is not working. Perhaps this works better? :

     $offset = ($max_rows_per_page * ($current_page_number - 1));
     if ( $offset < 0 ) {
     $offset = '0';
     }
     $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

Link to comment
Share on other sites

  • 2 weeks later...

Did you recently upgrade your mysql?

 

I upgraded mysql on the server, added a new OS commerce site and got this error too. I am running version 4.1.7, I was running version 3 previoulsy. Perhaps there are some extra sql steps to take to upgrade the database?

 

Perhaps backup the db and reload it.

 

KM

 

To admin/includes/classes/split_page_results.php or catalog/includes/classes/split_page_results.php? That class definitely has a limit x, y in it that influence viewing of orders... I can see that when I change the $sql in those lines to:

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

Maybe the example code I gave is not working. Perhaps this works better? :

     $offset = ($max_rows_per_page * ($current_page_number - 1));
     if ( $offset < 0 ) {
     $offset = '0';
     }
     $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...