Bug Database error in vBulletin 4.2.0 (Invalid SQL)

[trexo]

Hi team,
I'm finishing my site for a future opening.

First I'm doing some tests on various products, to ensure proper operation,
Unfortunately this product is giving me a problem.
When the installation main page is no longer within reach, I can only enter the admin panel.
The index i returns the error database, the error is as follows:

Database error in vBulletin 4.2.0:

Invalid SQL:

        SELECT
            userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user.languageid AS saved_languageid,
            IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
            language.phrasegroup_global AS phrasegroup_global,
            language.phrasegroup_holiday AS phrasegroup_holiday,
            language.phrasegroupinfo AS lang_phrasegroupinfo,
            language.options AS lang_options,
            language.languagecode AS lang_code,
            language.charset AS lang_charset,
            language.locale AS lang_locale,
            language.imagesoverride AS lang_imagesoverride,
            language.dateoverride AS lang_dateoverride,
            language.timeoverride AS lang_timeoverride,
            language.registereddateoverride AS lang_registereddateoverride,
            language.calformat1override AS lang_calformat1override,
            language.calformat2override AS lang_calformat2override,
            language.logdateoverride AS lang_logdateoverride,
            language.decimalsep AS lang_decimalsep,
            language.thousandsep AS lang_thousandsep
            , GROUP_CONCAT(f.forumid) AS dbtech_forumtabs_favourite
        FROM user AS user
        LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
        LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = 0, 2, user.languageid))
        LEFT JOIN dbtech_forumtabs_favourite AS f ON user.userid = f.userid
        WHERE user.userid = 1;

MySQL Error  : Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Error Number  : 1140
Request Date  : Tuesday, October 23rd 2012 @ 05:31:38 PM
Error Date    : Tuesday, October 23rd 2012 @ 05:31:38 PM
Script        : 
Referrer      : 
IP Address    : 
Username      :
Classname    : vB_Database
MySQL Version :

I also tried with version 1.0.3 I will always return the same error.
Then I decided to try the Lite version but once again I will always return the same error.

The index page returns only accessible when I disable or uninstall this product.


How can I fix?

Thank you for your attention,
greetings.

 

[Mokonzi]

Very interesting... Thanks for the bug report.

Can you confirm what version of MySQL and PHP you are using? I've not come across this error before so if I can be sure of the MySQL I can figure out whether it's a bug in MySQL or a change in the way they use the GROUP_CONCAT function.

 

[Mokonzi]

Ok, done some scouting around, and found something that might be a fix.

In the file /dbtech/forumtabs/hooks/fetch_userinfo_query.php find:

$hook_query_fields .= ", GROUP_CONCAT(f.forumid) AS dbtech_forumtabs_favourite";

Replace that entire row with:

$hook_query_fields .= ", GROUP_CONCAT(f.forumid ORDER BY f.forumid) AS dbtech_forumtabs_favourite";

Let me know if that works for you. (Please ensure you test this not on a live production site, just in case this doesn't fix the error you have ).

 

[trexo]

Thank you for your solution, but unfortunately it did not work.

Any other ideas?

Edit:

Version MySQL client: 5.0.51a
PHP Version 5.2.6-1+lenny16

 

[Mokonzi]

Thank you for your solution, but unfortunately it did not work.

Any other ideas?

The problem stems from the way MySQL can be run. There is a setting that doesn't introduce that error, and another that does. I can't provide an immediate fix now, as I'll need to rework the files slightly. I'll try and get that done this week and release a patch later on.

In the mean time, you may want to Google that MySQL error code to see about some options that you might be able to take to disable the mode I think is causing the problem:ONLY_FULL_GROUP_BY.

Thanks again for spotting this issue.

 

[trexo]

The problem stems from the way MySQL can be run. There is a setting that doesn't introduce that error, and another that does. I can't provide an immediate fix now, as I'll need to rework the files slightly. I'll try and get that done this week and release a patch later on.

In the mean time, you may want to Google that MySQL error code to see about some options that you might be able to take to disable the mode I think is causing the problem:ONLY_FULL_GROUP_BY.

Thanks again for spotting this issue.

Not at all! I hope we can solve this problem.

 

[Mokonzi]

Fixed this in v1.0.5 which is available for download.

 

[TandyServices]

Fixed this in v1.0.5 which is available for download.

Now I had to disable the mod because it broke my site with database errors?? Will try to put it back to the old way..

 

[Ozzy47]

What are the errors, kinda hard to fix it the error is unknown.

 

[Mokonzi]

I think I know the problem. I'm out of the house atm, so can't fix it. One of the files is missing the table prefix. I might not have uploaded the fix yet. Will reupload when I get in.

 

[trexo]

Hello Mokonzi then I can download and install version 1.0.5 or should I wait? You TandyServices found errors - it could happen to me?

 

[Mokonzi]

I've just got back in the house and uploaded the correct file, so this issue should be fixed, assuming TandyServices had the problem, I thought it was.

So download v1.0.5 and reupload. This applies to anyone else who may have downloaded the files before this point.

 

[TandyServices]

I didn't get the error log.. Would not tell me with this host I have.. Just broke the whole page.. Didn't have time to chase it..

edit: Had time and Just re-uploaded and installed and works fine now..

Thanks

 

[Mokonzi]

I didn't get the error log.. Would not tell me with this host I have.. Just broke the whole page.. Didn't have time to chase it..

edit: Had time and Just re-uploaded and installed and works fine now..

Thanks

Glad it is fixed now. I noticed that sometimes the error log doesn't show which is annoying

Must be something in the settings or server setup that stops it showing