[SOLUCIONADO] 1271 - Ilegal mix of collations for operation 'UNION'

[quimfv]

1271 - Ilegal mix of collations for operation 'UNION'

Intentando restaurar, en una maquina con W7, un backup me da este error.

El mismo backup se ha restaurado en mi maquina XP sin problemas, en el segundo intento, pero la habia restaurado otras veces sin ningun problema.

El backup se ha generado en phpmyadmin 3.3.1 del servidor remoto y lo intento resteurar con el phpmyadmin local 2.10.3

phpmyadmin
Remoto 3.3.1
Local 2.10.3

Versión del servidor:
Remoto 5.0.82-log
Local 5.0.51b-community-nt-log

Juego de caracters de MySQL:
Remoto UTF-8 Unicode (utf8)
Local UTF-8 Unicode (utf8)

Todas las tablas tienen la misma collation utf8_general_ci.

Da el error al crear una vista que une varias tablas...

Alguna pista?

[gnzsoloyo]

¿Podrías postear el CREATE VIEW de esa vista?
Podría darnos una mejor idea de lo que puede pasar.

[quimfv]

Código MySQL:

CREATE ALGORITHM=UNDEFINED DEFINER=`quim`@`localhost` SQL SECURITY DEFINER VIEW `bbdd`.`v_grupos` AS 
select concat(_utf8'SL',`vsl`.`idGruposSL`) AS `idGrupo`,
convert(concat_ws(_utf8' ',`vsl`.`grupoActividad`,`vsl`.`gruposSL`,`vsl`.`abreviacionAU`) using utf8) AS `grupo`,
`vsl`.`abreviacionAU` AS `au`,
`vsl`.`idCursos` AS `idCursos` 
from `bbdd`.`v_grupossl` `vsl` 
union all 
select concat(_utf8'GA',`vga`.`idGrupoActividad`) AS `idGrupo`,
convert(concat_ws(_utf8' ',`vga`.`grupoActividad`,_utf8'GA',`vga`.`abreviacionAU`) using utf8) AS `grupo`,
`vga`.`abreviacionAU` AS `au`,
`vga`.`idCursos` AS `idCursos` 
from `bbdd`.`v_gruposactividad` `vga` 
union all 
select concat(_utf8'TP',`vtp`.`idTutoriasP`) AS `idGrupo`,
convert(concat_ws(_utf8' ',`vtp`.`grupoActividad`,`vtp`.`grupoTP`,`vtp`.`abreviacionAU`) using utf8) AS `grupo`,
`vtp`.`abreviacionAU` AS `au`,
`vtp`.`idCursos` AS `idCursos` 
from `bbdd`.`v_tutoriasp` `vtp`;
 

Es una union de otras vistas... pero es que no me habia dado nunca problemas.

La mas complicada es

Código MySQL:

CREATE ALGORITHM=UNDEFINED DEFINER=`quim`@`localhost` SQL SECURITY DEFINER VIEW `bbdd`.`v_gruposactividad` AS 
select distinct `bbdd`.`grupoactividad`.`idGrupoActividad` AS `idGrupoActividad`,
`bbdd`.`grupoactividad`.`idGrupoActividadCAnt` AS `idGrupoActividadCAnt`,
`bbdd`.`grupoactividad`.`grupoActividad` AS `grupoActividad`,
`bbdd`.`asignaturasu`.`idAsignaturasU` AS `idAsignaturasU`,
`bbdd`.`asignaturasu`.`codigoAU` AS `codigoAU`,
`bbdd`.`asignaturasu`.`denominacionAU` AS `denominacionAU`,
`bbdd`.`asignaturasu`.`abreviacionAU` AS `abreviacionAU`,
`bbdd`.`asignaturasu`.`docenciaSiNo` AS `docenciaSiNo`,
`bbdd`.`asignaturasu`.`idTpTemporalidad` AS `idTpTemporalidad`,
`bbdd`.`grupoactividad`.`idGrupoActividadRef` AS `idGrupoActividadRef`,
`grupoactividad_1`.`grupoActividad` AS `gARef`,
`grupoactividad_1`.`idAsignaturasU` AS `idAURef`,
`asignaturasu_1`.`codigoAU` AS `cAURef`,
`asignaturasu_1`.`denominacionAU` AS `aURef`,
`asignaturasu_1`.`abreviacionAU` AS `abreAURef`,
`bbdd`.`grupoactividad`.`idCursos` AS `idCursos`,
`bbdd`.`grupoactividad`.`numSL` AS `numSL`,
`bbdd`.`grupoactividad`.`numTP` AS `numTP`,
`bbdd`.`grupoactividad`.`ausencias` AS `ausencias`,
`v_coordinadores`.`idProfesorado` AS `idProfesorado`,
`v_coordinadores`.`profe` AS `profe`,
`bbdd`.`asignaturasu`.`idTpTitulaciones` AS `idTpTitulaciones`,
`bbdd`.`tptitulaciones`.`abrTpTit` AS `abrTpTit` 
from (((((`bbdd`.`grupoactividad` left join `bbdd`.`grupoactividad` `grupoactividad_1` on((`bbdd`.`grupoactividad`.`idGrupoActividadRef` = `grupoactividad_1`.`idGrupoActividad`))) 
             join `bbdd`.`asignaturasu` on((`bbdd`.`grupoactividad`.`idAsignaturasU` = `bbdd`.`asignaturasu`.`idAsignaturasU`))) 
             left join `bbdd`.`asignaturasu` `asignaturasu_1` on((`grupoactividad_1`.`idAsignaturasU` = `asignaturasu_1`.`idAsignaturasU`))) 
             left join `bbdd`.`v_coordinadores` on((`bbdd`.`grupoactividad`.`idGrupoActividad` = `v_coordinadores`.`idGrupoActividad`))) 
             join `bbdd`.`tptitulaciones` on((`bbdd`.`asignaturasu`.`idTpTitulaciones` = `bbdd`.`tptitulaciones`.`idTpTitulaciones`)));
 

Seguramente ahora que me las miro fuera de contexto serian optimizables... pero no es un problema de performanc si no de cración...

[gnzsoloyo]

Por lo que he podido averiguar, este problema se suele producir en este caso cuando se genera un conflicto de collations entre cadenas de texto que quedan impactadas por las collation del ambiente de ejecución.
Básicamente no es un "error", y es resoluble.

Por lo pronto, confirmemos algunos detalles, como por ejemplo ciertas definiciones que suponemos correctas desde el inici.
Muestranos el resultado de estas consultas:

Código MySQL:

SHOW CREATE DATABASE TEST;
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
 

[quimfv]

Código pre:

Variable_name   Value
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
 

En el remoto!!!... por aqui estara puesto que en el local no esta asi.... cuando llegue a casa lo posteo.

Pero el error se produce al restaurar la bbdd sobre un esquema creado y vacio...

Gracias

[gnzsoloyo]

Por lo que entiendo de un caso informado en Bugs de la web oficial, el problema puede estar produciéndose en aquellos campos o valores que no se están declarando como UTF8 dentro de la función CONCAT() o CONCAT_WS(), ya que para esos casos MySQL toma los valores de collation del ambiente de ejecución, o bien los declarados en la creación de la base.
no hay que olvidarse que aunque el dato de origen sea de una tabla con cierta collation, al momento del anidamiento, pueden estar impactando las otras collation, porque se vuelve un dato o tabla derivada (virtual en memoria).

[quimfv]

Remoto CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
Local CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

SHOW VARIABLES LIKE 'character_set%';
Remoto
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

Local
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
character_sets_dir C:\AppServ\MySQL\share\charsets\

SHOW VARIABLES LIKE 'collation%';
Remoto
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

Local
collation_connection utf8_unicode_ci
collation_database utf8_general_ci
collation_server utf8_general_ci

Estos son los datos de mi maquina donde si se ha restaurado aun que no a la primera la bbdd... hay difrencias. La tercera maquina en discordia no la tengo ahora.

Para igualar los los distintos valores hay que modificar my.cnf directamente, no veo instrucciones ALTER... no?