Problem with MySQL 4

[SpongeBob]

I had our OSMAX running fine under a server with apache 1.3x and MySQL MySQL-3.23.58-1, we moved it over to a newer server with Apache 2.x and MySQL-server-4.1.8-0.
My Problem is when you go to certain menu options like reports I get this error:

Best Customer Orders-Total
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 '-12, 12' at line 1

select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC limit -12, 12

[TEP STOP]

Most of the other menus, and the front end run just fine....

I used MySQL administrator to backup from the old server, and restore to the new server the DB. Then I did an Oscommerce backup, and a command line restore as specified in the docs, with the same result.

Any ideas?

Thanks!

[michael_s]

osC is not compatible with mysql 4.1.x. Downgrade to 4.0.x Otherwise, you will have to track down the problems with the php code and fix them to be compatible with 4.1.x

[paulM]

I'm running osC on mysql 4.1.x, and don't experience any problems, after a small bug fix

I'm allmost sure the mentioned problem is caused by the split_page_results.php bug wich causes negative limits (like: limit -12, 12) which aren't allowed.

see: http://www.oscommerce.com/community/bugs,2300

solution ( both admin and catalog):

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

to

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