user warning: You have an error in your SQL syntax
Starminder - November 5, 2008 - 20:23
| Project: | Stumble |
| Version: | 6.x-1.1 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Dave Reid |
| Status: | closed |
Jump to:
Description
Clicking on Stumble link produced the following error:
* 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)) count, MAX(n.nid) max, MIN(n.nid) min FROM node n LEFT JOIN his' at line 1 query: SELECT COUNT(DISTINCT DISTINCT(n.nid)) count, MAX(n.nid) max, MIN(n.nid) min FROM node n LEFT JOIN history h ON n.nid = h.nid WHERE n.nid <> 60 AND n.status = 1 AND COALESCE(h.uid, 0) <> 1 AND n.type IN ('ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks','webform') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 74.
* 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)) count, MAX(n.nid) max, MIN(n.nid) min FROM node n WHERE n.nid <' at line 1 query: SELECT COUNT(DISTINCT DISTINCT(n.nid)) count, MAX(n.nid) max, MIN(n.nid) min FROM node n WHERE n.nid <> 60 AND n.status = 1 AND n.type IN ('ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks','webform') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 86.
#1
Thanks for the report. I'll take a look into this. Could you let me know which version of web server, PHP, and database you're using?
#2
Could you also included which other modules you are using? I can't duplicate this error with my Drupal 6 install. I'm guessing another module is using db_rewrite_query and adding the DISTINCT.
#3
Here is db, web server, etc:
MySQL database 4.1.22
PHP 4.4.7
Web server Apache/1.3.37 (Unix) mod_choke/0.07 mod_fastcgi/2.4.2 mod_gzip/1.3.26.1a mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635.SR1.2 PHP-CGI/0.1b
and my brief list of contrib mods: ;)
ACL 6.x-1.0-beta2
Advertisement 6.x-1.x-dev (2008-Oct-31)
Drupal Administration Menu 6.x-1.1
AdSense 6.x-1.0-beta2
Advanced help 6.x-1.1
Announcements 6.x-1.2
Avatar Selection 6.x-1.5
Block Theme 6.x-1.0-beta1
Buy Me a Beer 6.x-1.0
Calendar 6.x-2.0-rc4
CAPTCHA 6.x-1.0-rc2
Comment Notify 6.x-1.1
Content Construction Kit (CCK) 6.x-2.0-rc10
Cumulus 6.x-1.x-dev
Daily 6.x-1.1
Dash Media Player : a media player built for Drupal CMS 6.x-1.1
Date 6.x-2.0-rc4
Read More Tweak 6.x-3.x-dev (2008-Aug-14)
Event 6.x-2.x-dev (2008-Oct-27)
Fasttoggle 6.x-1.3
Services 6.x-0.13
Fivestar 6.x-1.13
FlashVideo 6.x-1.4-beta5
GMap Module 6.x-1.0-rc2
Image 6.x-1.0-alpha3
Image FUpload 6.x-1.1-rc1
IMCE 6.x-1.1
Lightbox2 6.x-1.8
Link To Us 6.x-1.x-dev (2008-Oct-21)
Live 6.x-1.0
location (API, module) 6.x-3.0-rc2
Mail editor 6.x-1.x-dev (2008-Oct-08)
Pathauto 6.x-1.1
album photos 6.x-1.4-beta2
phpBBforum Integration module 6.x-1.03
Pingback 6.x-1.0
Poormanscron 6.x-1.0
Printer, e-mail and PDF versions 6.x-1.0
Quote 6.x-1.1-beta
Banner Rotor Module 6.x-1.3
Scheduler 6.x-1.3
Service links 6.x-1.0
Signatures for Forums 6.x-1.0-alpha1
Site map 6.x-1.x-dev (2008-Oct-25)
Stumble 6.x-1.1
Subscriptions 6.x-1.0-beta3
Tagadelic 6.x-1.0
Taxonomy Manager 6.x-1.0-beta2
Theme Settings API 6.x-1.4
Thickbox 6.x-1.0
Tiny Tiny MCE 6.x-1.10
Token 6.x-1.11
User Stats 6.x-1.0-alpha1
User Points 6.x-1.x-dev (2008-Oct-08)
Video Filter (YouTube, Google Video, Godtube and more...) 6.x-2.4
Views 6.x-2.1
Voting API 6.x-2.0-rc1
Weather 6.x-4.2
Webform 6.x-2.3
Web Links 6.x-1.x-dev (2008-Nov-05)
Wikitools 6.x-1.0
If there is aything I can do to help you pinpoint, just yell. Thanks!
#4
Well, while I'm looking through the code for all those modules, if you could, disable something about 5 modules at a time and click the stumble link. Find out which batch of modules start the problem, and see if you can narrow it down to the module that is not compatible with Stumble. That will help me narrow down the problem faster as well.
#5
Try, newest dev-Version. Cant reproduce.
regards
#6
I'm actually going to mark this as fixed. I completely rewrote the SQL query/function for better performance and compatibility. Please give the 6.x-1.x version a try and if you have problems with that SQL again, reopen this issue.
#7
Installed dev version, re-enabled, and am successfully able to stumble around my site so far without error. YAY! Thanks, this is a great mod, and the extra support very much appreciated.
#8
Automatically closed -- issue fixed for two weeks with no activity.
#9
Hi Dave,
Sadly this one is back, am using dev version from nov 28:
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 ') AND n.type IN ('549','ad','photos','blog','book','cotd','daily_container','eve' at line 1 query: SELECT COUNT( DISTINCT(n.nid)) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM node n WHERE n.status = 1 AND n.nid NOT IN () AND n.type IN ('549','ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 103.
Thanks!
R
#10
Ah, thanks for letting me know. I had a stupid error in code that is fixed locally and will commit soon. Once I get a full testing suite for this module written, this won't happen again. :)
#11
Committed the fix to 6.x-1.x.
#12
Dev version works great, no more error - thanks!
#13
Hi Dave - it's ba-ack....
* 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 ') AND n.type IN ('1','ad','photos','blog','book','cotd','daily_container','event' at line 1 query: SELECT COUNT( DISTINCT(n.nid)) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM node n WHERE n.status = 1 AND n.nid NOT IN () AND n.type IN ('1','ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 103.* 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 ') AND n.type IN ('562','ad','photos','blog','book','cotd','daily_container','eve' at line 1 query: SELECT COUNT( DISTINCT(n.nid)) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM node n WHERE n.status = 1 AND n.nid NOT IN () AND n.type IN ('562','ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 103.
* 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 ') AND n.type IN ('1','ad','photos','blog','book','cotd','daily_container','event' at line 1 query: SELECT COUNT( DISTINCT(n.nid)) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM node n WHERE n.status = 1 AND n.nid NOT IN () AND n.type IN ('1','ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 103.
* 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 ') AND n.type IN ('562','ad','photos','blog','book','cotd','daily_container','eve' at line 1 query: SELECT COUNT( DISTINCT(n.nid)) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM node n WHERE n.status = 1 AND n.nid NOT IN () AND n.type IN ('562','ad','photos','blog','book','cotd','daily_container','event','image','page','poll','story','video','weblinks') in /usr/home/hoslo/public_html/sites/all/modules/stumble/stumble.module on line 103.
#14
You're so funny. Ok, taking a look. :)
#15
I suppose instead of:
<?php$sql = "SELECT COUNT(n.nid) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM {node} n WHERE n.status = 1 AND n.nid NOT IN ($subquery_sql) AND n.type IN ($placeholders)";
$subquery_sql = $unread ? "SELECT h.nid FROM {history} h WHERE h.uid = %d" : '%d';
?>
it might help to do:
<?php$subquery_sql = $unread ? "SELECT h.nid FROM {history} h WHERE h.uid = %d" : '%d';
$sql = "SELECT COUNT(n.nid) AS count, MAX(n.nid) AS max, MIN(n.nid) AS min FROM {node} n WHERE n.status = 1 AND n.nid NOT IN ($subquery_sql) AND n.type IN ($placeholders)";
?>
Fixed in 6.x-1.x. :)
#16
You're speaking in php now, Dave. Might be time for a break LOL. Whee - it works, I can stumble again...life is good. Merry Merry and all that!
#17
Automatically closed -- issue fixed for two weeks with no activity.