Weird characters when importing SQL to MariaDB

When moving databases between servers there are a few things to consider, one of these things is the charset.

The default character set in MariaDB is latin1, and the default collation is latin1_swedish_ci, although this is not true for Debian which uses utf8mb4 and utf8mb4_general_ci

Character sets and collations always cascade down, so a column without a specified collation will look for the table default, the table for the database, and the database for the server. It’s therefore possible to have extremely fine-grained control over all the character sets and collations used in your data.

To display the database encodings:

SELECT * FROM information_schema.SCHEMATA;

Show table encoding for a specific database:

SELECT table_name, table_collation, character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation 
AND T.table_schema = "DATABASE_NAME";

Or specific columns:

SELECT table_name,collation_name,character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "DATABASE_NAME" AND table_name = "TABLE_NAME";

To change charset and collation of a database.

ALTER DATABASE `DATBASE_NAME` CHARACTER SET latin1 COLLATE latin1_swedish_ci;

To dump a latin1/latin1_swedish_ci for use in a utf8 envirnoment

# mysqldump --skip-set-charset --default-character-set=latin1 -u root -p DATABASE_NAME > dump.sql;

 

Published by Nimpen J. Nordström

System Developer and Network Security Enthusiast

Leave a comment

Your email address will not be published. Required fields are marked *