New Products SQL Error

Hi,
I am getting this error when anyone clicks on the new products link:

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 '-0, 0' at line 11
in:
[SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM zen_products p LEFT JOIN zen_manufacturers m ON (p.manufacturers_id = m.manufacturers_id), zen_products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 order by p.products_date_added DESC, pd.products_name limit -0, 0]

Any idea where or how I would fix this?

thank you,

betty

v1.38a with all the latest patches applied

Log in to your Admin, click on the Configuration menu then Maximum values. What does it say against 'New Products Listing- Number Per Page'?

Regards,
Christian.

It says 1.

thank you,
betty

CJPinder:

Log in to your Admin, click on the Configuration menu then Maximum values. What does it say against 'New Products Listing- Number Per Page'?

Regards,
Christian.

bettysue:

It says 1.
Try changing that to a larger number like 10 and see if that fixes the problem.

Regards,
Christian.

No that didn't fix it, I still get the same error message.

thank you,

betty

Hmm..very odd. The SQL error you posted earlier would only occur if 'New Products Listing- Number Per Page' was set to 0. It also looks as if 'New Product Listing - Limited to ...' is also set to 0.

If 'New Products Listing- Number Per Page' is set to something other than zero then it could be that the Configuration table in your database has become corrupt. Do you have a recent backup of your database from before the error started happening?

Regards,
Christian.

The main issue is that the site owner doesn't know how long it has been doing this. There are database backups but since we cannot put a finger on when it started, there would be no way to tell which database to restore. Plus he said he has made changes to the site over the past couple of weeks that he doesn't want to lose which a db restore would do.

Is there any other method other than a restore to fix it?

thank you,
betty

CJPinder:

Hmm..very odd. The SQL error you posted earlier would only occur if 'New Products Listing- Number Per Page' was set to 0. It also looks as if 'New Product Listing - Limited to ...' is also set to 0.

If 'New Products Listing- Number Per Page' is set to something other than zero then it could be that the Configuration table in your database has become corrupt. Do you have a recent backup of your database from before the error started happening?

Regards,
Christian.

First thing we can try is replacing the specific configuration key that might be causing the problem. Take a backup of the database then carefully copy and paste the following into Admin->Tools->Install SQL Patches and click Send :

DELETE FROM `configuration` where configuration_title='New Products Listing- Number Per Page';
INSERT INTO `configuration` (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('New Products Listing- Number Per Page', 'MAX_DISPLAY_PRODUCTS_NEW', '10', 'Number of new products listed per page', '3', '14', now());
```Let me know if that fixes the problem.

Regards,
Christian.

That did it :D

Thank you so much for the help and the fix. It is greatly appreciated.

betty

I'm glad you've got the problem fixed. Unfortunately it may indicate that there has been some database corruption. It might have been a isolated incident or it could be an ongoing issue. Sometimes these things can also happen if an add-on is installed incorrectly. I would talk with your website host and ask if they have had any problems with the database server.

Normally I would recommend restoring from the last known good backup. If you don't know which backup that is then it gets a bit tricky. If you have copies of previous backups then you could try restoring some of them to a test site to see if you can pinpoint the date on which the problem appeared.

If the last good backup was a long time ago then you probably don't want to convert back to it and lose the recent customer and order information. You could just restore the Configuration table though as that is where the corrupt New Products key was and the contents of that table do not change often.

Regards,
Christian.

I like your idea of using a test site to try and determine when the error occured and to see if there are any more errors.

Thank you again for your helo and guidance in this issue, it is greatly appreciated.

betty