Sqlstate hy000 general error 1267 illegal mix of collations - Исправление ошибок и поиск оптимальных решений проблем

#1 30.03.2010 21:51:14

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Ребята, подскажите как разрешить следующую проблему.
Имеется код:

CREATE FUNCTION `add_word`(
word VARCHAR(100)
) RETURNS tinyint(4)
DETERMINISTIC
BEGIN
DECLARE id INT;

# check such word does not exist
SELECT words.word_id INTO id FROM words WHERE
words.word = word LIMIT 1;
IF (id IS NOT NULL) THEN RETURN 102;
END IF;

RETURN 0;
END

При запуске получаю сообщение об ошибке:

Debugger for MySQL написал:

—————————
Error code: 1267, SQLState: HY000, Message: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
—————————

Видно, что сравнивается значение utf8_unicode_ci с utf8_general_ci, что приводит к ошибке. Не понятно как разрешить…
—
Версия MySQL: 5.0.45
Сравнение таблицы: utf8_unicode_ci

Отредактированно FiMko (30.03.2010 22:04:56)

Неактивен

#2 30.03.2010 22:04:27

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Вот помогло, но я пока не нашел научных обоснований

CREATE FUNCTION `add_word`(
word VARCHAR(100)
) RETURNS tinyint(4)
DETERMINISTIC
BEGIN
DECLARE id INT;

SET NAMES latin1; ## <- HERE!
# check such word does not exist
SELECT words.word_id INTO id FROM words WHERE
words.word = word LIMIT 1;
IF (id IS NOT NULL) THEN RETURN 102;
END IF;

RETURN 0;
END

Нет фигня какая-то… во втором случае (SELECT words.word_id INTO id FROM words WHERE) id всегда получает значение 1

Отредактированно FiMko (30.03.2010 22:12:21)

Неактивен

#3 30.03.2010 22:26:31

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Отредактированно FiMko (30.03.2010 22:46:25)

Неактивен

#4 31.03.2010 00:28:43

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Ну, проблему Вы идентифицировали правильно. Не понятно, почему Вы
не смогли также сразу и решение проблемы придумать

При создании таблицы Вы сделали сопоставление utf8_unicode (зачем?).
При создании процедуры у текущего соединения сопоставление
utf8_general. Для того, чтобы всё работало, нужно перед созданием
процедуры сделать соответствующие изменения:
SET collation_connection = utf8_unicode_ci;

В любом случае, если сопоставления будут одинаковые, — все будет хорошо.

Предваряя вопрос «чем отличаются» —
http://forums.mysql.com/read.php?103,18 … msg-188748
но я предпочитаю использовать general — он проще и быстрее.

Неактивен

#5 31.03.2010 12:03:13

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

paulus написал:

Ну, проблему Вы идентифицировали правильно. Не понятно, почему Вы
не смогли также сразу и решение проблемы придумать

Дык… пробовал set names только. Про set collation_connection не нагуглил

paulus написал:

При создании таблицы Вы сделали сопоставление utf8_unicode (зачем?).
я предпочитаю использовать general — он проще и быстрее.

Спасибо! Перевел на general.

Проблема решена.

Неактивен

#6 31.03.2010 22:18:42

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Ничего не понимаю… казалось бы ничего специально не делал (поменял сравнение таблицы на utf8_general_ci), но теперь проблема ERROR 1267 проявила себя вновь…

mysql> SET collation_connection = «utf8_general_ci»;
Query OK, 0 rows affected (0.00 sec)

DELIMITER //
DROP FUNCTION IF EXISTS add_word //
CREATE FUNCTION `add_word`(
word VARCHAR(100)
) RETURNS TINYINT
DETERMINISTIC
BEGIN
DECLARE id INT;

# check such word does not exist
SELECT words.word_id INTO id FROM words WHERE
words.lng_id = lng_id AND
words.word = word
LIMIT 1;
IF (id IS NOT NULL) THEN RETURN 102;
END IF;

INSERT INTO words SET words.word = word;

RETURN 0;
END //
DELIMITER ;

mysql> select add_word(«q»);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

Причем даже так (несмотря на явное указание совершенно левого collation, все равно Illegal mix of collations utf8_general_ci and utf8_unicode_ci):

mysql> SET collation_connection = «cp1251_general_ci»;
Query OK, 0 rows affected (0.00 sec)

mysql> select add_word(«q»);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

Причем, странная особенность, не работает из под phpMyAdmin и консоли. Из под отладчика Debugger for MySQL все работает

Отредактированно FiMko (31.03.2010 22:35:29)

Неактивен

#7 31.03.2010 22:49:05

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

И на этот раз раз удалось победить проблему. Порядок был следующим:
1. Сделал show variables like ‘colla%’; (результат см. выше — все переменные в «utf8_general_ci»)
2. Перезапустил Denwer, сделал variables like ‘colla%’; снова, результат:

Установки для collation_connection и collation_server соответствуют заданным в my.cnf. Изменил настройки в файле (перезапускаем Denwer)
3. Снова выполнил show variables like ‘colla%’; результат:

collation_database = utf8_unicode_ci (несмотря на то, что я делал ALTER DATABASE `db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci), нашел файл «db.opt» для базы данных и руками изменил в нем значение для default-collation, задав default-collation=utf8_general_ci вместо установленного там utf8_unicode_ci. Перезапускаем Denwer.

После этого проблема исчезла… Остается только представить, что начнет твориться на виртуальном хостинге, где нет доступа к «db.opt» и «my.cnf».

Отредактированно FiMko (31.03.2010 22:50:28)

Неактивен

#8 01.04.2010 02:28:27

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Эм, ну опять вы бесконечно близки к решению, но почему-то напрямую
не решаете ее, а ищете обходные пути

Проблема сравнения возникает тогда, когда Вы пытаетесь сравнить две
строки в разных сопоставлениях. Сравнение в Вашем коде происходит
строки, которая приезжает снаружи, и строки, которая записана в таб-
лице. Стало быть, кодировки различаются у таблицы и у внешней строки.
Сопоставление внешней строки определяется сопоставлением соединения.
Сопоставление таблицы — это сопоставление таблицы Если Вы ее дела-
ете в клиенте, который по умолчанию использует сопоставление unicode,
то она создается (да, да) в сопоставлении unicode. При этом, если
запускать процедуру Вы будете из клиента, работающего в этом сопо-
ставлении, то всё будет работать хорошо.

Мораль сей басни такова: MySQL Debugger работает в неудачном сопостав-
лении для создания таблиц и процедур

Неактивен

#9 01.04.2010 02:30:21

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Кстати, у Вас процедура сильно урезана? Может, просто уникальный ключ
на табличке решит Вашу проблему?

Неактивен

#10 01.04.2010 09:26:58

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

paulus написал:

Проблема сравнения возникает тогда, когда Вы пытаетесь сравнить две
строки в разных сопоставлениях. Сравнение в Вашем коде происходит
строки, которая приезжает снаружи, и строки, которая записана в таб-
лице.

Не поспоришь… Это было понятно.

paulus написал:

Стало быть, кодировки различаются у таблицы и у внешней строки.
Сопоставление внешней строки определяется сопоставлением соединения.
Сопоставление таблицы — это сопоставление таблицы Если Вы ее дела-
ете в клиенте, который по умолчанию использует сопоставление unicode,
то она создается (да, да) в сопоставлении unicode. При этом, если
запускать процедуру Вы будете из клиента, работающего в этом сопо-
ставлении, то всё будет работать хорошо.

Так а какого… первый show variables like ‘colla%’; показал, что collation соединения, базы и сервера в «utf8_general_ci».
При этом на клиенте несколько раз сделал SET collation_connection = «utf8_general_ci»; Перевел collation базы, всех таблиц и текстовых полей в «utf8_general_ci». И все равно не работало… Перезапуск Denwer сбросил collation в некоторые дефолтные значения, потом события развивались, как описано выше. Итог: почему, после описанных только что стараний, проблема все еще присутствовала?

paulus написал:

Кстати, у Вас процедура сильно урезана? Может, просто уникальный ключ
на табличке решит Вашу проблему?

Да, урезал чутка для простоты примера, спасибо
А вот еще вопрос: вы предлагаете создать уникальный ключ по колонке word (VARCHAR(100))? А если в таблице ~миллионов записей, то будет ли правильным так поступать? В том смысле, что не будет ли менее накладным самостоятельно (с помощью select) отслеживать добавление дубликатов?

Отредактированно FiMko (01.04.2010 13:31:24)

Неактивен

#11 01.04.2010 16:40:33

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Важно не сопоставление базы, а сопоставление конкретной колонки в конкретной
табличке

Кажется, ALTER TABLE … CONVERT TO CHARSET … COLLATE … над всеми табличками
и установка сопоставления на соединении должны были полечить все проблемы.

А уникальный ключ я предлагаю не просто по word, а по (word, lang) — у Вас же
уникальность нужна именно такая. Что касается скорости и SELECT — подумайте,
сколько будет выполняться Ваш SELECT без индекса… все равно же будете делать
его

Неактивен

#12 01.04.2010 21:59:18

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

paulus написал:

А уникальный ключ я предлагаю не просто по word, а по (word, lang) — у Вас же
уникальность нужна именно такая. Что касается скорости и SELECT — подумайте,
сколько будет выполняться Ваш SELECT без индекса… все равно же будете делать
его

Правильно ли строить совместный уникальный индекс на два поля разных типов — одно типа VARCHAR, другое — INT? Мне кажется такой индекс должен быть медленным в работе.
Индекс для SELECT я конечно собирался сделать, но не по двум колонкам, а отдельно для word, отдельно для word_id.

Или вот в одной из других моих таблиц уникальность определяется по более чем двум полям. Будет ли и в этом случае более оптимальным строить уникальный индекс по всем этим полям?

Отредактированно FiMko (01.04.2010 22:04:08)

Неактивен

#13 02.04.2010 15:28:17

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

«Да» на оба вопроса

Компьютеру все равно, что вы храните в этих последовательностях битиков. А MySQL
в свою очередь все равно, что Вы храните в индексе

Неактивен

#14 02.04.2010 15:45:55

FiMko: Активист; Откуда: Санкт-Петербург; Зарегистрирован: 18.09.2009; Сообщений: 198

Re: Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

paulus написал:

«Да» на оба вопроса

Компьютеру все равно, что вы храните в этих последовательностях битиков. А MySQL
в свою очередь все равно, что Вы храните в индексе

Отлично! Это сократит мне много времени на написание хранимых процедур и вообще очень полезно

Неактивен

Источник

Situation:

You installed Drupal core using a third-party installer. Then, you installed or enabled more modules to add more features to your site.

You encounter illegal mix of collation errors involving latin1_swedish_ci and utf8_general_ci collations.
For example, whenever you perform a search using a Chinese keyword, you encounter an illegal mix of collation error.

You realised that about 40 tables in the Drupal database have been created with latin1_swedish_ci collation.
And, Drupal created the rest of the tables with utf8_general_ci collation.

Solution:

Manual Method (30 mins to 1 hr)

1. Put your site to offline mode.

2. Backup all your databases (Important).

3. Select phpMyAdmin.

4. Select the correct Drupal database.

5. Modify table collation:

Locate table with latin1_swedish_ci collation
Select Structure (Index finger pointing icon)
Select Operations tab
Change collation to utf8_general_ci
Go

6. Modify field collation:

* Some fields have collations, some do not have collation. Do not modify those fields without collation.

(Select Structure tab)
Locate field with latin1_swedish_ci collation
Select Change (Pencil icon)
Change collation to utf8_general_ci
Save
Repeat step 6 to modify other fields with latin1_swedish_ci collation to utf8_general_ci collation.

7. Repeat steps 4 to 6 to modify other tables with latin1_swedish_ci collation to utf8_general_ci collation.

8. Put your site to online mode.

9. Make sure that your site is functioning normally.

Easier Method (2 mins to 10 mins)

After making the mistake of installing Fantastico, there is an easier way to correct the collation of all tables:

Create a new php file on the server with the following and then run it:

$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;

Источник

Home
>
MySQL
>
Detail page

1. This bug occurs in production server:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:604)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:638)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:667)
--

2. solving ideas
1) MySQL command tests the Emoji expression:

select count(*) from player where name  collate utf8mb4_unicode_ci =  
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) for operation 
;

The same error occurred
2) look at the player table:

show create player;
 CREATE TABLE `player` (
  `id` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Both field and table are in the utf8mb4 character and support the Emoji expression
3) check the character configuration of the database:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_general_ci |
| collation_server         | utf8_general_ci |
+--------------------------+-----------------+

The character set is not right. Before that, the other server database character sets are utf8mb4

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

3. solution:
So you need to change this database character set to utf8mb4, online items.

Online similar problem to solve for reference:
https://stackoverflow.com/questions/32511288/illegal-mix-of-collations-utf8mb4-unicode-ci-explicit-and-utf8-general-ci-coe

Posted by wangtaolearn
in MySQL
at Jul 23, 2017 — 9:00 PM
Tag:
emoji

Источник