Home » Issue Tracker »

View File Downloads SQL Syntax Error

Submitted by jasonyoung on Sun, 11/18/2007 - 13:29.
Category: 
bug report
Priority: 
normal
Status: 
closed

First, sorry, but I'm a total ubercart noob and pretty new to drupal as well. I appreciate any help I can get, even if it means pointing out something I've done that's completely retarded!

When I go to "View File Downloads" in "Store Administration", I get the following:

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 'DISTINCT(n.nid) FROM uc_files as f LEFT JOIN uc_file_products as fp ON (f.fid = ' at line 1 query: SELECT f.filename, n.title, fp.model, f.fid, pf.pfid, DISTINCT(n.nid) FROM uc_files as f LEFT JOIN uc_file_products as fp ON (f.fid = fp.fid) LEFT JOIN uc_product_features as pf ON (fp.pfid = pf.pfid) LEFT JOIN node as n ON (pf.nid = n.nid) ORDER BY f.filename ASC LIMIT 0, 50 in /var/www/includes/database.mysql.inc on line 172.

I'm running the following:

Ubuntu Gutsy Gibbon
Drupal 5.3
MySQL database 5.0.45
PHP 5.2.3-1ubuntu6
Ubercart 5.x-1.0-alpha8
Content Construction Kit (CCK) 5.x-1.6-1
imagecache 5.x-1.x-dev (2007-Oct-20)
imagefield 5.x-1.x-dev (2007-Oct-19)
TAPIr 5.x-1.4
Taxonomy Access Control 5.x-1.1
Token 5.x-1.9
uBrowser 5.x-1.2
Update status 5.x-2.0
Workflow-ng 5.x-1.3

Thanks for any help anyone can offer!
#1Submitted by radScientist on Fri, 11/30/2007 - 19:50.

You are not alone

I noticed I also have this error with version .8 of ubercart. Hopefully I'll be able to find something out when I setup my PDF downloads next week...

#2Submitted by torgosPizza on Fri, 11/30/2007 - 20:58.

It looks like this bit: ON

It looks like this bit:

ON (f.fid = '

is causing the issue. Like there is an issue with finding the "fid" column. Is there anything in the Watchdog logs that might give you a better idea? (/admin/logs/watchdog)

Do you have any file downloads already setup? In other words have you added a File Download feature to one of your products already?

#3Submitted by radScientist on Wed, 12/05/2007 - 12:03.

re: Are there any file downloads setup?

Hi torgosPizza - thanks for your help.

Yes I do have a file download Feature setup on a test product node. What's interesting is that the action of uploading a new File at "/admin/store/products/files" successfully places the file on the server and allows it to be setup as a feature for a product node - but the "/admin/store/products/files" page still gives the error and does not correctly show a list of files that have been uploaded, checking the database - it looks as though there is correct data in the uc_files, uc_file_products, and uc_product_features tables too... Unfortunately I'm not a SQL Ninja. But it looks like there is data in all the tables where the query is calling for an "fid". I'll try and see if I can get a query that works and report back.

#4Submitted by radScientist on Wed, 12/05/2007 - 12:27.

Found the Bug!

The issue on my setup is with the placement of the DISTINCT(n.nid) in the query. If I manually do the same query, but move the DISTINCT(n.nid) to the beginning of the query, it works.

//This fails:
SELECT f.filename, n.title, fp.model, f.fid, pf.pfid, DISTINCT(n.nid) FROM uc_files....

//but this works:
SELECT DISTINCT(n.nid), f.filename, n.title, fp.model, f.fid, pf.pfid FROM uc_files....

It Appears that maybe the pager_query function or db_rewrite_sql function is adding this DISTINCT into the query from line 560 of the uc_file module. I have solved the problem by either moving the n.nid to the start of the query as so:

// original $query = pager_query(db_rewrite_sql("SELECT f.filename, n.title, fp.model, f.fid, pf.pfid, n.nid...
$query = pager_query(db_rewrite_sql("SELECT n.nid, f.filename, n.title, fp.model, f.fid, pf.pfid...

But I was curious after looking at it, if getting the node id from the uc_product_features table would be a more optimized query:

$query = pager_query(db_rewrite_sql("SELECT pf.nid, f.filename, n.title, fp.model, f.fid, pf.pfid...

Thanks again for your help

#5Submitted by torgosPizza on Wed, 12/05/2007 - 15:29.

Re: Found the Bug!

My question is, what caused this change? I don't have any issues with that page on my end .. did you add some custom code that would do something to the module like this?

Unless it's from a recent bzr update that I haven't gotten yet..

#6Submitted by radScientist on Wed, 12/05/2007 - 20:55.

I'm not sure what's different -

I'm not sure what is different - as I haven't made any changes to the ubercart .8 version, other than fixing a bug in the cybersource processing. I have defined some special weights to custom modules, so it is possible something is happening there - but I don't really know what would be adding the SELECT DISTINCT to the query - what are your thoughts on selecting the node id from the product_features table instead?

#7Submitted by jasonyoung on Thu, 12/27/2007 - 17:39.

Bug Fix Worked For Me Too

I can confirm radScientist's bug fix worked (thanks radScientist). I experienced this bug with a fresh install of everything listed in my original post, without any subsequent modifications to anything being made by me.

#8Submitted by Shawn Conn on Fri, 12/28/2007 - 10:01.

Re: Found the Bug!

Interesting, when I initially had this problem prior to implementing a count_query I couldn't figure out for the life of me what the issue was with adding DISTINCT to n.nid. I came to the conclusion that the query was too convoluted for pager_query so I added a count_query; that fixed my issue. Apparently it didn't fix everyone's. I didn't think the order of columns in a select mattered but I guess with DISTINCT and a whole chain of LEFT JOINs you need the DISTINCT columns first (as I've confirmed this in phpMyAdmin) I've made the change and committed it to Bazaar.