By dave88 on

Hi,

As soon as I open up my site this appears:

user warning: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' query: SELECT aa.aid, a.type FROM trigger_assignments aa LEFT JOIN actions a ON aa.aid = a.aid WHERE aa.hook = 'nodeapi' AND aa.op = 'view' ORDER BY weight in /home/davie88/public_html/rexposure.com/modules/trigger/trigger.module on line 146.

I don't know what it means, and please if you do can you explain as though you're explaining it to a 10 year old, cheers

⋅ Categories: Drupal 6.x

Comments

cog.rusty’s picture

cog.rusty commented

This is about a comparison

trigger_assignments.aid = actions.aid

It seems that in your database,
- the collation of the table "trigger_assignments" (or some of its columns) is utf8_general_ci
- the collation of the table "actions" (or some of its columns) is utf8_unicode_ci

Collation is very important for comparisons, searching, and sorting. If comparisons become unreliable the whole database system becomes unreliable. The details are very complicated for an 10-year old that I know (for example http://dev.mysql.com/doc/refman/5.0/en/charset-collate-tricky.html).

Possible cause... I don't know maybe a partial load of some database tables from a backup which specified the wrong collation (there was no reason to specify a collation at all).

To fix the problem you will need to do database surgery. But before doing anything, check for yourself. This is dangerous territory, don't take my blind diagnosis for granted.

One way is to go to your database with phpmyadmin, check the collation
(a) of the database itself,
(b) of each database table, and
(c) of each table column (at least the ones that carry characters),
and make sure that either all of them are utf8_general_ci (the default and also my personal preference) or all of then utf8_unicode_ci. In phpmyadmin you change that in the "Operations" tab.

Another way is to
- export your database to a file (for example using phpmyadmin),
- empty it (delete all tables),
- set the empty database's default collation to utf8_general_ci
- check your database dump file with a good text editor to make sure that no collation is specified
- import it back from your file, using phpmyadmin.

ShannonK’s picture

ShannonK commented

I had this same problem and ended up using cog.rusty's instructions for phpMyAdmin. There's probably an automatic way of doing this, because this is very tedious, but I don't know one. If you have a lot of tables, this may not be the way to go. Also, I've heard changing the database is dangerous, so I cannot attest to the safety of this. But if you do take the leap anyway, here's the steps:

Using phpMyAdmin (in your cPanel):

1. Locate your database on the left frame, and click it.
2. Click the "Operations" tab, change collation to utf8_general_ci (or whatever you want it to be). Click the Go button. *That sets your database collation, but now you need to check the tables and columns.
3. Click on a table on the left frame.
4. Click the "Operations" tab and check that under Table Options the collation is set to utf8_general_ci (or what you want).
5. Click the "Structure" tab on the main frame.
6. Click the "Check All" link below the list of fields.
7. Click the "Change" icon, the one that looks like a pencil at the right of `With Selected`.
8. You will now be presented with a form which allows you to modify the selected fields. What you're interested in is the "Collation" row. Change each collation to utf8_general_ci (only for those with an existing collation already). Press save once done.
9. Repeat for each of your tables.

cog.rusty’s picture

cog.rusty commented

I agree, this method is tedious except if only a couple tables have the problem. It could be automated with some script, but it must be carefully written not to change the collation of binary columns etc.

The second method is much easier for big changes, maybe with a small improvement to be safe: Create a new database and database user, set it to the right collation while empty, take a backup of the existing one, make sure that it doesn't contain any collation information (edit it if necessary), load it to the new database, switch your settings.php file's $db_url to the new database, if it works you are done.

ShannonK’s picture

ShannonK commented

I came across this code for changing the collation for all the tables in a database:

$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";

Make sure to substitute in the above script:

- myuser_mydbname with your database name;

- myuser_mydbuser with your mysql username;

- mypassword with your password for the mysql user;

- utf8-general_ci with your new collation if different;

However I wasn't 100% comfortable with using it so I went the PHPMyAdmin way. If someone who's familiar with PHP trusts the above code or sees errors or drawbacks, maybe they could post?

BTW, thank you cog.rusty for your help...it's very much appreciated!

dave88’s picture

dave88 commented

thanks,

I couldn't find the pencil shaped icon but in the drop down box it said repair table, then I went bakc in there was now a new message:

warning: dl() has been disabled for security reasons in /home/davie88/public_html/rexposure.com/modules/mappingkit/mapserver/mapserver.module on line 41.

so I guess the utf8_general_ci worked but what does this mean, thank you.

cog.rusty’s picture

cog.rusty commented

The "dl() has been disabled" error means that the mappingkit module needed a PHP extension, it didn't find it loaded in php.ini, it tried to load it itself, but your server configuration doesn't allow that.

From what I see in the installation instructions in http://aardbodem.nl/node/14, the mappingkit module needs the PHP extension "mapscript.so" (or "mapscript.dll" if it is Windows). You need to load it in php.ini or ask your host to do it.

dave88’s picture

dave88 commented

thanks, now how do I load it in php.ini and get the mapscript.dll?

thanks

cog.rusty’s picture

cog.rusty commented

I don't know about that, so I would check
http://php.net/manual/en/install.windows.extensions.php