New Error; Illegal mix of collations

Hi Mobile Joomla,
I started my day with 331.35 KB worth of errors in my server error log. All of which contained these kind of messages....My mobile traffic has gone down quite a lot in the past week. Please check into this error and get back to me. Sounds very serious.....
[30-Nov-2010 22:37:57] Caught exception 'Exception' with message 'Error in DB RIS Query: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='.
Query: CALL jos_TeraWurfl_RIS('Mozilla/5.0 (Windows; U; Windows NT 5.1; xx-xx; rv:1.9.2.12) Gecko/20101026 AlexaToolbar/alxf-1.54 Firefox/3.6.12 (.NET CLR 3.5.30729)',7,'Bot')
' in /home/folmag/public_html/plugins/mobile/terawurfl/DatabaseConnectors/TeraWurflDatabase_MySQL5.php:117

Thanks,
Robin

Put here the results of the following sql commands:



(replace 'YOUR_DB_NAME' by name of your database).

Hi,
Where do I replace (replace 'YOUR_DB_NAME' by name of your database)? What file and what line? Please give me a step by step instruction on how to solve this. I would really appreciate it.
Thanks,
Robin

YOUR_DB_NAME here is the name of used database (look at parameter $db in your configuration.php).

Hi,
I know what the database name is, I just don't know where in mobile joomla to replace it with. Where would I edit my database name?
Robin

No, it's not Joomla-related. I just asked you to run above-mentioned SQL commands in phpMyAdmin (or mysql console) and post results here.

Hi Denis,
I finally got the results you need......I am having these issues with 2 databases.
root@ise [/home/folmag]# cat /home/folmag/MHX-14415298.mysql
mysql> SHOW GLOBAL VARIABLES LIKE '%collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.02 sec)

mysql> SHOW COLUMNS FROM jos_TeraWurflIndex from folmag_mainsmart;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deviceID | varchar(64) | NO | PRI | | |
| matcher | varchar(64) | NO | | | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM jos_TeraWurflMerge from folmag_mainsmart;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| deviceID | varchar(64) | NO | PRI | | |
| user_agent | varchar(255) | YES | MUL | NULL | |
| fall_back | varchar(64) | YES | MUL | NULL | |
| actual_device_root | tinyint(1) | YES | MUL | 0 | |
| match | tinyint(1) | YES | MUL | 1 | |
| capabilities | mediumtext | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM jos_TeraWurflIndex from folmag_jo151;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deviceID | varchar(64) | NO | PRI | | |
| matcher | varchar(64) | NO | | | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM jos_TeraWurflMerge from folmag_jo151;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| deviceID | varchar(64) | NO | PRI | | |
| user_agent | varchar(255) | YES | MUL | NULL | |
| fall_back | varchar(64) | YES | MUL | NULL | |
| actual_device_root | tinyint(1) | YES | MUL | 0 | |
| match | tinyint(1) | YES | MUL | 1 | |
| capabilities | mediumtext | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

Hope this helps. My error logs are now over 10 megs a day on this issue.
Thanks,
Robin

Could you repeat the same with "SHOW FULL COLUMNS" instead of "SHOW COLUMNS" (I forgot that show columns doesn't print collation info)?

Here the second try,

mysql> SHOW FULL COLUMNS FROM jos_TeraWurflIndex from folmag_mainsmart;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| deviceID | varchar(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| matcher | varchar(64) | utf8_general_ci | NO | | | | select,insert,update,references | |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM jos_TeraWurflMerge from folmag_mainsmart;
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| deviceID | varchar(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| user_agent | varchar(255) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | |
| fall_back | varchar(64) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | |
| actual_device_root | tinyint(1) | NULL | YES | MUL | 0 | | select,insert,update,references | |
| match | tinyint(1) | NULL | YES | MUL | 1 | | select,insert,update,references | |
| capabilities | mediumtext | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
6 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM jos_TeraWurflIndex from folmag_jo151;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| deviceID | varchar(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| matcher | varchar(64) | utf8_general_ci | NO | | | | select,insert,update,references | |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM jos_TeraWurflMerge from folmag_jo151;
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| deviceID | varchar(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| user_agent | varchar(255) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | |
| fall_back | varchar(64) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | |
| actual_device_root | tinyint(1) | NULL | YES | MUL | 0 | | select,insert,update,references | |
| match | tinyint(1) | NULL | YES | MUL | 1 | | select,insert,update,references | |
| capabilities | mediumtext | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+--------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
6 rows in set (0.00 sec)

Run the following mysql-commands to fix collation og Index and Merge tables: