SQL syntax error
| Project: | Advanced User |
| Version: | 6.x-2.3 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
The advanced user module used to work properly, but lately it's been returning no results and generating SQL syntax errors in our logs. Here's the error:
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 .uid = u.uid WHERE u.uid != 0 ORDER BY u.created DESC LIMIT 0, 50' at line 1 query: SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid LEFT JOIN profile_values profile_first_name ON profile_first_name.fid = 1 AND profile_first_name.uid = u.uid LEFT JOIN profile_values profile_last_name ON profile_last_name.fid = 5 AND profile_last_name.uid = u.uid LEFT JOIN profile_values profile_referrer ON profile_referrer.fid = 2 AND profile_referrer.uid = u.uid LEFT JOIN profile_values profile_phone ON profile_phone.fid = 4 AND profile_phone.uid = u.uid LEFT JOIN profile_values ON .fid = AND .uid = u.uid WHERE u.uid != 0 ORDER BY u.created DESC LIMIT 0, 50 in /var/www/vhosts/ajisourceutility.com/httpdocs/sites/all/modules/advuser/advuser.module on line 108.
It generates this error even if I clear the filters.
#1
Interesting. What version of Drupal? I'll try to have it resolved ASAP.
#2
I was getting that error a few days ago in Drupal 6.13, and I'm still getting it after upgrading to Drupal 6.14.
#3
I also get the same error. I am using drupal 6.13 and I also get it on 6.14 (on a fresh install). Same as a.bond.
please resolve this issue please!!!
#4
Please check your data in the profile_fields table. It appears to me that the possibility that you have an empty name column in a row.
<?phpforeach (advuser_profile_fields() as $field) {
$ff[] = array('data'=>t($field->title), 'field'=>"$field->name.value");
$pf[] = "LEFT JOIN {profile_values} $field->name ON $field->name.fid = $field->fid AND $field->name.uid = u.uid";
}
?>
<?php/**
* Selected Profile Fields
* @return array
*/
function advuser_profile_fields() {
static $ret = array();
if (!count($ret) && module_exists('profile')) {
$fields = variable_get('advuser_profile_fields', NULL);
if (is_array($fields)) {
foreach ( $fields as $fid => $value) {
if ( $value ) {
$ret[]= db_fetch_object(db_query('SELECT * FROM {profile_fields} WHERE fid = %d', $fid));
}
}
}
}
return $ret;
}
?>
I suppose a possibility exists that a stupid change to the name column may have happened between 6.12 and 6.13 but I hope that isn't the case.
#5
Hey earnie,
I checked in my database and I don't have empty name columns. All information has been filled in. I also checked if I have duplicates, but that is also not the case.
When I go to "administer > users > advanced" I also see ARRAY, ARRAY, ARRAY, so it seems the Array or the length of the Array is wrong. See attachment for a view of my "advanced" page (with errors).
Jessica
#6
Thanks for the information. I will try to debug this today but it may push to the weekend.
#7
I entered a multicolumn field in version 6.12 and upgraded 6.13 with no issues.
#8
Also admin/settings/performance and click 'Clear cached data'
Does this help?
#9
Clear cache doesn't help. I have signature support disabled. I have the "selection", "date" and "textfield" profile type.
I hope that helps you with debugging.
#10
I feel certain I will need a list of modules and their versions. Please specify them in a file listing format, e.g. advuser-6.x.2.3, so I can easily download if I need to.
#11
I have signature support disabled. There are no empty values in the "name" column in my "profile_fields" table, although I do have some empty "explanation" fields. I have types "textfield" "selection" and "checkbox".
I am using the following modules:
Advanced Help v. 6.x-1.2
Advanced User v. 6.x-2.3
Backup and Migrate v. 6.x-1.2
Browscap v. 6.x-1.1
CCK Redirection v. 6.x-1.2
Checkbox Validate v. 6.x-1.1
Contact Forms v. 6.x-1.5
CCK v. 6.x-2.5
Date v. 6.x-2.4
Flash Node v. 6.x-3.1
Google Analytics v. 6.x-2.2
Gradebook v. 6.x-1.0-rc2
Graphstat v. 6.x-1.0
Legal v. 6.x-2.2-beta4
LoginToboggan v. 6.x-1.5
SWF Tools v. 6.x-2.5
Mass Contact v. 6.x-1.0-beta2
Poormanscron v. 6.x-1.0
Role Expire v. 6.x-1.5
Rules v. 6.x-1.1
Search Engine Referers v. 6.x-1.0
Statistics Advanced Settings v. 6.x-1.5
Taxonomy Access Control Lite v. 6.x-1.3
Taxonomy hide v. 6.x-1.02
Token v. 6.x-1.12
User Stats v. 6.x-1.0-rc1
Username originality AJAX check v. 6.x-1.5
Views v. 6.x-2.6
Views Bonus Pack v. 6.x-1.0-beta4
Views Bulk Operations (VBO) v. 6.x-1.8
Views Calc v. 6.x-1.3
Views Custom Field v. 6.x-1.0
Views Date range v. 6.x-1.0
Visitors v. 6.x-0.22
#12
I have encountered exactly the same problem. When Profile is disabled, the Advanced User Filter works fine and I am able to send an email to my users. I want to add a Profile field so I can give users an Opt Out option for emails from the administrator. When I enable Profile I get the attached error (same as above).
After a large amount of research, I realized that I hadn't selected any fields in the: Profile module special settings section of Site Configuration->Advanced User. If no profile field is selected here you get the above error; however, if a profile field is selected the error goes away.
#13
@darrylind: Thank you for the analysis. I should be able to provide a fix next week.
#14
@earnie:
Have you already found a solution, because I need to get this fixed
#15
@duckstar: As darrylind pointed out the work around for this issue is to select a profile field to include in the filters in the settings page for advuser. But to answer your question, I've been overly busy with real life work to even keep up with the mail list. We're wrapping up a year long project so I should have more time soon.
The fix will involve checking to see if profile fields have been chosen before adding the selection to the filter for the SQL. I don't know what that involves yet; patches for review are welcome.