Код ошибки 1267

I want to make the password column of my User table to be case sensitive in mysql.

Following is the description of the table:

/*Table: mst_user*/

   FIELD          TYPE          COLLATION        
-------------  ------------  -----------------
user_id        VARCHAR(100)  latin1_swedish_ci
first_name     VARCHAR(25)   latin1_swedish_ci
last_name      VARCHAR(25)   latin1_swedish_ci
USER_PASSWORD  VARCHAR(50)   latin1_swedish_ci
user_status    INT(11)       (NULL)           
version_id     INT(11)       (NULL)           
active_status  INT(11)       (NULL)           
user_type      INT(11)       (NULL)    

To make the USER_PASSWORD field case sensitive I executed following query:

ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;

This worked and the field is now case sensitive.

But I have a store procedure which executes a SELECT query on this table to check if the user exists for the given credentials.

Stored Proc::

CREATE PROCEDURE `usp_password_verify`(ip_login_id         VARCHAR(200),
                                 ip_user_password    VARCHAR(200),
                                INOUT success     INT(1),
INOUT tbl_usr_password          VARCHAR(100),
INOUT  pkg_user_password         VARCHAR(100))
BEGIN
  SELECT COUNT(*)
    INTO success
    FROM mst_user
   WHERE UPPER (user_id) = UPPER (ip_login_id)
   AND USER_PASSWORD=ip_user_password;

   SET tbl_usr_password = '';
   SET pkg_user_password= '';
END$$

When I call this stored proc from my java code I am getting the following error:

**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**

Can anyone help what is wrong with this?
Something that works as a simple query gives error while executing it in a stored proc!?

I know it’s a little bit late but if this could save someone half a day of swearing it’s still worth putting it down.

So, my setup was like this: 10.1.22-MariaDB, utf8mb4_general_ci.
All good, I restored a dump of my database, all went OK.

The database was originally in utf8_general_ci, but for some reasons was restored as utf8_unicode_ci. Changed that back to utf8_general_ci and checked there were no artifacts in the database like columns or table definitions collated as utf8_unicode_ci instead of utf8_general_ci

Trying to update a specific table resulted in an illegal mix of collation without any apparent reasons.

It boiled down to be in fact not the table itself but the associated trigger.

In fact the trigger called a procedure that had no collation info in my database, but that had a utf8_unicode_ci collation in information_schema.ROUTINES.DATABASE_COLLATION.

Recreating the procedure in the context of the new database collation solved my issue.

answered Jul 19, 2017 at 13:40

So after struggling with this error:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

I managed to fix this problem by changing the import database file from:

CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NO SQL

This is if you are importing functions/procedures/triggers, which my database had a ton of all of these features… I changed that to:

CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I really hope this helps someone. I know the above was helpful, but it still took me a few hours to turn that into a solution. Thanks

answered Nov 28, 2017 at 1:33

SET collation_connection = 'utf8_general_ci';

, затем для ваших баз данных

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL иногда пробирается туда, где нет разумной причины.

У меня была таблица, первоначально созданная с помощью CHARSET = latin1. После преобразования таблицы в utf8 некоторые столбцы не были преобразованы, однако это было не совсем очевидно. Вы можете попробовать запустить SHOW CREATE TABLE my_table; и посмотреть, какой столбец не был преобразован, или просто исправить неверный набор символов в проблемном столбце с запросом ниже (изменить длину varchar и CHARSET и COLLATE в соответствии с вашими потребностями):

 ALTER TABLE `my_table` CHANGE `my_column` `my_column` VARCHAR(10) CHARSET utf8 
 COLLATE utf8_general_ci NULL;

CONVERT(column1 USING utf8)

Решает мою проблему. Где column1 — столбец, который дает мне эту ошибку.

После внесения исправлений, перечисленных в верхнем ответе, измените настройки вашего сервера по умолчанию.

В вашем файле /etc/my.cnf.d/server.cnf или где он находится, добавьте по умолчанию используется раздел [mysqld], поэтому он выглядит так:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Источник: https://dev.mysql.com/doc/refman/5.7/en/charset-applications .html

Вы должны установить для вашей кодировки таблицы и кодирования соединения значение UTF-8:

ALTER TABLE keywords CHARACTER SET UTF8; -- run once

и

SET NAMES 'UTF8';
SET CHARACTER SET 'UTF8';

В общем, лучший способ — изменить сортировку таблицы. Однако у меня есть старое приложение и на самом деле не могу оценить результат, имеет ли это побочные эффекты. Поэтому я попытался каким-то образом преобразовать строку в другой формат, который решил проблему сортировки. То, что я нашел, — это выполнить сравнение строк, преобразуя строки в шестнадцатеричное представление его символов. В базе данных это делается с помощью HEX(column).. Для PHP вы можете использовать эту функцию:

public static function strToHex($string)
{
    $hex = '';
    for ($i=0; $i<strlen($string); $i++){
        $ord = ord($string[$i]);
        $hexCode = dechex($ord);
        $hex .= substr('0'.$hexCode, -2);
    }
    return strToUpper($hex);
}

При выполнении запроса базы данных исходная строка UTF8 должна быть сначала преобразована в строку iso (например, с помощью utf8_decode() в PHP), прежде чем использовать его в БД. Из-за типа сопоставления база данных не может содержать символы UTF8 внутри, поэтому сравнение должно работать, хотя это изменяет исходную строку (преобразование символов UTF8, которые не встречаются в символьной кодировке ISO, приводит к? Или они полностью удаляются). Просто убедитесь, что при записи данных в базу данных вы используете одно и то же преобразование UTF8 в ISO.

Используйте следующую инструкцию для ошибки

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

 ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;