What should I do if I have collations inconsistency in my database (MySQL)?

Question

What should I do if I have collations inconsistency in my database (MySQL)?

---

Answer

The following applies only to MySQL :
 
As of version 8.2, schema collation must be consistent with the collation of individual tables and columns or Infoport will not allow migration. The database admin must unify the collation on the schema. 

To display collation on columns, use query:

SELECT DISTINCT COLLATION_NAME 

FROM information_schema.columns 

WHERE TABLE_SCHEMA = 'schemaName'AND TABLE_NAME <>  '__EfMigrationsHistory' AND COLLATION_NAME IS NOT null 

To display collation on tables, use query:  

SELECT DISTINCT TABLE_COLLATION 

FROM information_schema.`TABLES` 

WHERE TABLE_SCHEMA = 'schemaName'  AND TABLE_NAME <>  '__EfMigrationsHistory'

To display the collation on the diagram, use query:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 

FROM information_schema.columns 

WHERE TABLE_SCHEMA = 'schemaName' 

  AND COLLATION_NAME IS NOT NULL 

  AND TABLE_NAME <>  '__EfMigrationsHistory' 

GROUP BY TABLE_NAME, COLUMN_NAME, COLLATION_NAME 

 

Please reunite the collation and restart Infoport.

 

You can use the following scripts for unification: 

For each table:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,  
'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')  
AS 'USE `DATABASE_NAME`;'  
FROM INFORMATION_SCHEMA.TABLES  
WHERE TABLE_SCHEMA = 'DATABASE_NAME'  
AND TABLE_TYPE LIKE 'BASE TABLE' 

For each column:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ',  
DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL  
OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH,  
')')  
), ' COLLATE utf8mb4_unicode_ci;') AS 'USE `DATABASE_NAME`;'  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_SCHEMA = 'DATABASE_NAME'  
AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE  
FROM INFORMATION_SCHEMA.TABLES  
WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =  
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  
AND INFORMATION_SCHEMA.TABLES.TABLE_NAME =  
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME  
LIMIT 1) LIKE 'BASE TABLE'  
AND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */ 

 

Please contact the helpdesk for more information https://www.dphelpdesk.cz/ .