I'm getting the following error when I try to run report for Products:

user warning: 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 'SELECT SUM(qty) FROM drupal_uc_order_products AS p LEFT JOIN dr query: SELECT n.nid, n.title, (SELECT SUM(qty) FROM drupal_uc_order_products AS p LEFT JOIN drupal_uc_orders AS o ON p.order_id = o.order_id WHERE o.order_status IN ('completed') AND p.nid = n.nid) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM drupal_uc_order_products AS p2 LEFT JOIN drupal_uc_orders AS o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN ('completed') AND p2.nid = n.nid) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM drupal_uc_order_products AS p3 LEFT JOIN drupal_uc_orders AS o3 ON p3.order_id = o3.order_id WHERE o3.order_status IN ('completed') AND p3.nid = n.nid) AS gross FROM drupal_node AS n WHERE type IN ('product') GROUP BY n.nid DESC ORDER BY revenue DESC LIMIT 0, 30 in /homepages/30/dxxxxxxxx/htdocs/elders/Members-Drupal5/includes/database.mysql.inc on line 172.

Has anyone else come across this problem?

Comments

tr’s picture

tr commented

I suspect you're running a very old version of MySQL. As the error message implies, valid syntax depends on version.

affinitymc’s picture

affinitymc commented

Yes, unfortunately 1and1 are very slow to upgrade their MySQL installations on existing accounts. The version I believe is 4.0.27.

tr’s picture

tr commented
Status: Active » Closed (won't fix)

You're going to have to get MySQL 4.1 or greater. Preferably MySQL 5. I don't know the exact minimum version number, but it's known that Ubercart doesn't work with 4.0. 5.0 has been out more than two years, and you can't even download 4.0 anymore from mysql.com, it's so old. IMO a webhost that can't or won't install MySQL 5 and PHP 5 isn't worth dealing with, even if they only charge a few dollars a month. There are plenty of other choices out there.