Fixing Collation and Character Set of a MySQL Database – Confluence

Data Base Name = ‘Confluence’

MySQL Collation Repair: Database Level Changes

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S
WHERE schema_name = ‘confluence’
AND
(
DEFAULT_CHARACTER_SET_NAME != ‘utf8’
OR
DEFAULT_COLLATION_NAME != ‘utf8_bin’
);

MySQL Collation Repair: Table Level Changes

SELECT T.TABLE_NAME, C.CHARACTER_SET_NAME, C.COLLATION_NAME
FROM information_schema.TABLES AS T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = ‘confluence’
AND
(
C.CHARACTER_SET_NAME != ‘utf8’
OR
C.COLLATION_NAME != ‘utf8_bin’
);

Identifying Columns with the incorrect character set or collation:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘confluence’
AND
(
CHARACTER_SET_NAME != ‘utf8’
OR
COLLATION_NAME != ‘utf8_bin’
);

Adjusting the collation and character set of varchar columns

SELECT CONCAT(‘ALTER TABLE ‘, table_name, ‘` MODIFY `’, column_name, ‘` ‘, DATA_TYPE, ‘(‘, CHARACTER_MAXIMUM_LENGTH, ‘) CHARACTER SET UTF8 COLLATE utf8_bin’, (CASE WHEN IS_NULLABLE = ‘NO’ THEN ‘ NOT NULL’ ELSE ” END), ‘;’)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘confluence’
AND DATA_TYPE = ‘varchar’
AND
(
CHARACTER_SET_NAME != ‘utf8’
OR
COLLATION_NAME != ‘utf8_bin’
);

Adjusting the collation and character set of non varchar columns

SELECT CONCAT(‘ALTER TABLE ‘, table_name, ‘` MODIFY `’, column_name, ‘` ‘, DATA_TYPE, ‘ CHARACTER SET UTF8 COLLATE utf8_bin’, (CASE WHEN IS_NULLABLE = ‘NO’ THEN ‘ NOT NULL’ ELSE ” END), ‘;’)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘confluence’
AND DATA_TYPE != ‘varchar’
AND
(
CHARACTER_SET_NAME != ‘utf8’
OR
COLLATION_NAME != ‘utf8_bin’
);