# Nekonzistence collations (MySQL, MariaDB)
[](https://doc.eainfoport.cz/uploads/images/gallery/2022-03/question-mark-1.png)
### Otázka
Co mám dělat, když mám v databázi nekonzistenci collations (MySQL, MariaDB)?
- - - - - -
### Odpověď
**Nasledující platí jen pro MySQL a MariaDB databázi :**
Od verze 8.2 musí být collation schématu konzistentní s collation jednotlivých tabulek a sloupců jinak Infoport nedovolí zmigrovat. Admin databáze musí sjednotit collation na schématu.
Pro zobrazení collation na sloupečkách použijte query:
SELECT DISTINCT COLLATION\_NAME
FROM information\_schema.columns
WHERE TABLE\_SCHEMA = 'schemaName' AND TABLE\_NAME <> '\_\_EfMigrationsHistory' AND COLLATION\_NAME IS NOT null
Pro zobrazení collation na tabulkách použijte query:
SELECT DISTINCT TABLE\_COLLATION
FROM information\_schema.`TABLES`
WHERE TABLE\_SCHEMA = 'schemaName' AND TABLE\_NAME <> '\_\_EfMigrationsHistory'
Pro zobrazení collation na schématu použijte query:
SELECT DEFAULT\_COLLATION\_NAME
FROM information\_schema.SCHEMATA
WHERE SCHEMA\_NAME = 'schemaName' AND TABLE\_NAME <> '\_\_EfMigrationsHistory'
Pro zobrazení tabulek, sloupců a jejich collation použijte 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
Sjednoťte prosím collation a znovu spusťte Infoport.
Pro sjednocení můžete použít tyto scripty:
Pro každou tabulku:
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'
Pro každý sloupec:
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 \*/
Pro více informací se obraťte na nás helpdesk [https://www.dphelpdesk.cz/](https://www.dphelpdesk.cz/) .