Estaba haciendo unos procedimientos almacenados en MySQL 5 y cuando estaba hanciedo pruebas, me comenzó a dar este error:
Script line: 73 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’
Buscando donde estaba el error, me di cuenta que el procedimiento almacenado que estaba probando utiliza un cursor comparando un String de parametro con una tabla definida explicitamente con una colación (COLLATION) latin1_general_ci.
Este es un ejemplo de como generar el error. Se crea una tabla “tabla_ejemplo” y un procedimiento almacenado “recorrer_ejemplo”. El procedimiento lo únicamente lo que hace es definir un cursor a la tabla “recorrer_ejemplo”, abrirlo y recorrerlo contando el numero de registros que contiene. (No tiene mucho sentido pero es para ejemplo solamente)
Definición de la tabla:
create table tabla_ejemplo(
id int unsigned not null auto_increment,
temp_key varchar(32) not null,
description varchar(250),
primary key (id)
) engine = InnoDB
default charset latin1 collate latin1_general_ci;
Definición del procedimiento almacenado (Stored Procedure):
DELIMITER //
CREATE PROCEDURE recorrer_ejemplo(
IN p_temp_key VARCHAR(32),
IN p_description VARCHAR(250),
OUT total_ejemplos INT UNSIGNED)
BEGIN
-- Declaracion de variables
DECLARE v_id VARCHAR(100);
DECLARE v_temp_key VARCHAR(32);
DECLARE v_description VARCHAR(100);
DECLARE v_done INT DEFAULT 0;
-- Declaracion del cursor
DECLARE cur_ejemplo CURSOR FOR
SELECT id,
temp_key,
description
FROM tabla_ejemplo
WHERE temp_key = p_temp_key;
-- Handler para el cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
-- ***************************
-- ** AQUI ME DA ERROR ***
-- ****************************
OPEN cur_ejemplo;
SET total_ejemplos = 0;
-- Inicio del repeat
REPEAT
-- Traiga los datos del cursor
FETCH cur_ejemplo
INTO v_id,
v_temp_key,
v_description;
-- ... ++ OPERACIONES ++ ...
SET total_ejemplos = total_ejemplos + 1;
-- Fin del repeat
UNTIL v_done END REPEAT;
END;
//
DELIMITER ;
Inserción de datos:
INSERT INTO tabla_ejemplo
VALUES (NULL, MD5('pablo'), 'Desc 1 ...'),
(NULL, MD5('pablo'), 'Desc 2 ...'),
(NULL, MD5('pablo'), 'Desc 3 ...');
Llamado al procedimiento:
SET @p_temp_key = MD5('pablo');
SET @p_description = 'Desc 2 ...';
SET @total_ejemplos = NULL;
CALL recorrer_ejemplo(
@p_temp_key,
@p_description,
@total_ejemplos);
SELECT @total_ejemplos;
Al ejecutar todas las instrucciones, me genera este error:
Script line: 74 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’
En resumen:
El problema está en que MySQL no sabe como resolver la conversión de datos internamente (ver referencias). Es decir, no sabe cual colación debería seguir.
Este comportamiento se llama “coercibility” (coercitivas) y basicamente significa la capacidad de poder ser convertido. En este caso, la capacidad de poder convertir un dato con una collation latin1_swedish_ci a latin1_general_ci.
Los parametros del procedimiento almacenado, tienen una colación latin1_swedish_ci, y cuando lo compara con el campo temp_key con una colación latin1_general_ci general el error, ya que MySQL no sabe cual colación usar a la hora de compara los campos VARCHAR.
Solución:
Convertir el parametro usando la funcion CONVERT y dandole la colación adecuada, ya que la colación default de latin1 es latin1_swedish_ci.
-- Declaracion del cursor
DECLARE cur_ejemplo CURSOR FOR
SELECT id,
temp_key,
description
FROM tabla_ejemplo
WHERE temp_key = CONVERT(p_temp_key USING latin1)
COLLATE latin1_general_ci;
Reglas de Coercibilidad (Coercibility).
En la gran mayoría de consultas, resulta obvio qué colación usa MySQL para resolver una operación de comparación. Por ejemplo, en los siguientes casos, debe quedar claro que la colación es “la colación de la columna x”:
SELECT x FROM T WHERE x = x;
Sin embargo, cuando están implicados varios operandos, puede haber ambigüedad. Por ejemplo:
SELECT x FROM T WHERE x = 'Y';
¿Esta consulta debe usar la colación de la columna x, o de la columna de caracteres literal ‘Y’?
SQL estándar resuelve tales cuestiones usando lo que se solía llamar reglas “coercitivas”. Es decir: Como x e ‘Y’ tienen colaciones, ¿cuál tiene precedencia? Puede ser difícil de resolver, pero las siguientes reglas resuelven la mayoría de situaciones:
- Una cláusula COLLATE explícita tiene una coercibilidad de 0. (No es coercible en absoluto.)
- La concatenación de dos cadenas de caracteres con diferentes colaciones tiene una coercibilidad de 1.
- La colación de una columna tiene una coercibilidad de 2.
- Una “constante de sistema” (la cadena de caracteres retornada por funciones como USER() o VERSION()) tiene una coercibilidad de 3.
- Una colación de un literal tiene una coercibilidad de 4.
- NULL o una expresión derivada de NULL tiene una coercibilidad de 5.
Estas reglas resuelven ambigüedades como:
- Uso de la colación con el valor más bajo de coercibilidad.
- Si ambos operadodres tienen la misma coercibilidad, entonces hay un error si las colaciones son distintas.
Ejemplos:
| columna1 = ‘A’ |
Usa colación de columna1 |
| columna1 = ‘A’ COLLATE x |
Usa colación de ‘A’ |
| columna1 COLLATE x = ‘A’ COLLATE y |
Error |
Referencias:
- http://dev.mysql.com/doc/refman/5.0/es/charset-collate-tricky.html
- http://dev.mysql.com/doc/refman/5.0/es/information-functions.html#function_coercibility
NOTA: Si tiene instalado Zend Server o alguna configuración para hacer debug con Zend Server, debe deshabilitarla, ya que Zend Server y XDebug no son compatibles:
Follow me on Twitter
RSS