MySQL-error message

[Lifeform]

Why does this show up?

 

Orders Order ID:

Status: All OrdersPendingProcessingDelivered

 

 

Customers Order Total Date Purchased Status 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 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 ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

 

 

 

Under Orders in the backend

[OceanRanch]

http://www.oscommerce.com/community/bugs,1605

A quick summary of the patch code.....

 

edit /includes/classes/split_page_results.php

find

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

(line about 66)

insert before it

if($offset <0 ) $offset = 0;

 

the same in admin//includes/classes/split_page_results.php

do the same, but this time it's line about 38

 

HTH

Tom

[Lifeform]

Thanks a lot ! Worked perfectly !

[smesol]

I the orders section i have got 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 '-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 ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

[TEP STOP]

 

I have found out so far that this is due to an update on our server of the MySQL database, i have looked at the bug on the page you suggested above, which someone else also suggested, but the fix does not work for me, as written in the other comments on the bug page.

 

Is there any other solution?

[OceanRanch]

Are you sure you made the changes properly?

 

The code change should have produced your query with "limit 0, 20" but your post is still showing it to be "limit -20, 20".

 

Tom

[smesol]

Are you sure you made the changes properly?

 

One of the problems i have is that the code in the two documents are different, and not as stated, the first part is fine:

 

edit /includes/classes/split_page_results.php

find

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

(line about 66)

insert before it

if($offset <0 ) $offset = 0;

 

but in the second stage below, the same code is not there

 

the same in admin//includes/classes/split_page_results.php

do the same, but this time it's line about 38

 

the code i have is as follows:

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

 

i'm sure this must be a problem, is it me that's wrong?

[OceanRanch]

Yes you are correct. The bug report and associated fixes for the admin side appears to apply to an earlier version of split_page_results.php

 

The code you (and I) have is correct for the production MS2.

 

I don't have the latest mysql that appears to have the issue with the "limit" being negative. Did you add the

 

if($offset <0 ) $offset = 0;

 

to your code after the $offset line and before the $sql_query line?

 

Even though someone else posted to place it BEFORE the $offset, IMHO it has to be after.

 

Tom

[smesol]

Yes definatly, but it has made no difference, i have tried it both ways, before the $offset line, and between the two lines.

 

my code for the admin page is as follows

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if ($offset < 0) $offset = 0;

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

 

I don't know enough to know if it should make a difference.

 

Steve.

[OceanRanch]

Make sure you have a value in admin->configuration->maximum values ->search results.

 

Also, in a search of the forum, I saw the same problem showing up with a few folks that had the "download controller" installed. Perhaps that reporting module has a mysql query with the "limit" value being set and not using the "split pages function." If that's the case then the code to "reset the offset" to zero may need to be added in there.

 

Tom.

[smesol]

Make sure you have a value in admin->configuration->maximum values ->search results.

 

I have a maximum value of 4 set up

 

Also, in a search of the forum, I saw the same problem showing up with a few folks that had the "download controller" installed. Perhaps that reporting module has a mysql query with the "limit" value being set and not using the "split pages function." If that's the case then the code to "reset the offset" to zero may need to be added in there.

 

I have not installed the "download controller", so that is not the problem either.

 

I find it really strange that no one else is having this problem, because it seems inherent with the MySQL update to 4.1 i have tested multiple sites on our server, including brand new installs that all have the same problem.

 

Steve