# What should I do if I have collations inconsistency in my database (MySQL)?
[](https://doc.eainfoport.cz/uploads/images/gallery/2022-03/question-mark-1.png)
### 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/](https://www.dphelpdesk.cz/) .