Форум пользователей MySQL

FiMko · 30.03.2010 21:51:14

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

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)

FiMko · 30.03.2010 22:04:27

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

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)

FiMko · 30.03.2010 22:26:31

Похоже, это известная проблема Cast Is Not Done On Row Comparison
Проблема исправлена в версии MySQL 5.0.79. Думаю как мне быть...

Ни REGEXP, ни LIKE, ни STRCMP ничего не прокатывает - Error 1267 Illegal mix of collations.

Как правильно CAST самому можно сделать никто не подскажет - CAST does not accept varchar type?

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

paulus · 31.03.2010 00:28:43

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

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

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

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

FiMko · 31.03.2010 12:03:13

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

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

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

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

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

FiMko · 31.03.2010 22:18:42

Ничего не понимаю... казалось бы ничего специально не делал (поменял сравнение таблицы на 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 все работает

mysql>  show variables like 'colla%';

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database   | utf8_general_ci |

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

3 rows in set (0.00 sec)

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

FiMko · 31.03.2010 22:49:05

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

+----------------------+-------------------+

| Variable_name        | Value             |

+----------------------+-------------------+

| collation_connection | cp1251_general_ci | # <- HERE!

| collation_database   | utf8_unicode_ci   | # <- HERE!

| collation_server     | cp1251_general_ci | # <- HERE!

+----------------------+-------------------+

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

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database   | utf8_unicode_ci | # <- HERE!

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

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)

paulus · 01.04.2010 02:28:27

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

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

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

paulus · 01.04.2010 02:30:21

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

FiMko · 01.04.2010 09:26:58

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)

paulus · 01.04.2010 16:40:33

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

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

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

FiMko · 01.04.2010 21:59:18

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

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

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

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

paulus · 02.04.2010 15:28:17

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

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

FiMko · 02.04.2010 15:45:55

paulus написал:
«Да» на оба вопроса

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

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

Форум пользователей MySQL

#1 30.03.2010 21:51:14

Error 1267 Illegal mix of collations (utf8_unicode,IMPLICIT)

Debugger for MySQL написал:

#2 30.03.2010 22:04:27

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

#3 30.03.2010 22:26:31

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

#4 31.03.2010 00:28:43

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

#5 31.03.2010 12:03:13

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

paulus написал:

paulus написал:

#6 31.03.2010 22:18:42

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

#7 31.03.2010 22:49:05

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

#8 01.04.2010 02:28:27

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

#9 01.04.2010 02:30:21

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

#10 01.04.2010 09:26:58

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

paulus написал:

paulus написал:

paulus написал:

#11 01.04.2010 16:40:33

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

#12 01.04.2010 21:59:18

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

paulus написал:

#13 02.04.2010 15:28:17

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

#14 02.04.2010 15:45:55

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

paulus написал:

Board footer