a quick stored procedure to get rid of the infamous : Error Code: 1267. Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’
first of all i had to find those tables and databases, and to do so i used the following sql code
SELECT
table_schema,
TABLE_NAME,
column_name,
character_set_name,
collation_name
FROM
information_schema.COLUMNS
WHERE
collation_name = ‘latin1_swedish_ci’
AND
table_schema = ‘siis_qsut’
ORDER BY
table_schema,
TABLE_NAME,
ordinal_position
reaplce your_db_name with the name od the db you want to change the charset.
DELIMITER $$
CREATE PROCEDURE change_charset_and_collation_for_tables()
BEGIN
DECLARE TABLE_NAME VARCHAR(255);
DECLARE end_of_tables INT DEFAULT ;
DECLARE num_tables INT DEFAULT ;
DECLARE cur CURSOR FOR
SELECT
t.TABLE_NAME
FROM
information_schema.TABLES t
WHERE
t.table_schema = ‘your_db_name’
AND
t.table_type=‘BASE TABLE’;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
OPEN cur;
tables_loop: LOOP
FETCH cur INTO TABLE_NAME;
IF end_of_tables = 1 THEN
LEAVE tables_loop;
END IF;
SET num_tables = num_tables + 1;
SET @s = CONCAT(‘ALTER TABLE ’ , TABLE_NAME , ’ CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci’);
PREPARE stmt FROM @s;
EXECUTE stmt;
END LOOP;
CLOSE cur;
END $$