Issue: Bad collation orderr on vault_protect

Issue Tools
- View Changes

May 14, 2009 8:30 AM

tommythejoat tommythejoat is offline

Regular Member

Bad collation orderr on vault_protect

Got database error on query to vault_protect with like

I received the following database error:

Code:

Database error in vBulletin 3.8.2: 

Invalid SQL: 

                SELECT COUNT(thread.threadid) AS rowcount 
                FROM vault_protect AS protect 
                LEFT JOIN thread AS thread ON (thread.title LIKE protect.title) 
                WHERE thread.forumid IN (229,239,248,249,241,246,247,250,251,252,253,255,254,243,245) 
                        AND protect.namespaceid = 6 
                        AND protect.options NOT IN (0,4); 

MySQL Error   : Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'like'

Error Number  : 1267

I changed the collation order of the table to utf8_unicode_ci to conform with the thread collation order.

Issue Details

Issue Number 591

Issue Type Bug

Project VaultWiki 3.x Series

Category Protection

Status Not a Bug

Priority 2 - Fatal / Database Errors

Affected Version 2.3.0

Fixed Version (none)

Milestone (none)

Software DependencyAny

Users able to reproduce bug 0

Users unable to reproduce bug 0

Attachments 0

Assigned Users (none)

Tags (none)

May 14, 2009 12:30 PM

pegasus

VaultWiki Team

Firstly, this is not a very good query to start with. The LIKE on the on-clause is going to be used before thread.forumid, because title occurs less frequently than a particular forumid. And since it's case insensitive, it's going to be slow. This is something we can try to improve.

However, the collation is not something that was defined by this mod. It's possible that your original install of vBulletin was on a different server, or used a different default collation setting than your database has now.

Please check your database's settings to make sure the default collation matches your vBulletin table collations. If you have any additional tables that use the incorrect collation, you will need to change each of them manually. Additionally, you will need to change the improper collation of their existing columns manually. Overall, this should improve the performance of your site.

Reply

+ Reply

All times are GMT -4. The time now is 2:06 PM.

This site uses cookies to help personalize content, to tailor your experience, and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Learn more… Accept Remind me later

Welcome to VaultWiki.org, home of the wiki add-on for vBulletin and XenForo!

Issue: Bad collation orderr on vault_protect

Issue Tools