character encoding issues when migrating gyroscope app from mysql (latin1) to mariadb (utf8)

0
votes

I'm migrating a small Gyroscope app from MySQL to MariaDB. I asked this question earlier on why MariaDB would not start on latin1 as server encoding (MariaDB won't start when character-set-server = latin1 is in my.cnf), but now I've come to terms with using just utf8.

After importing the data dump:

mysql -uwebuser -p ezine < dump.sql

, what's supposed to be autotööstus shows up as autot??stus. In addition I got this error:

illegal mix of collations (latin1_general_ci implicit) and (utf8_general_ci coercible), storing ??

Attempt 1: I removed all the occurrences of charset=latin1 in the data dump, and the import terminated early with this error:

Specified key was too long; max key length is 767 bytes

Attempt 2: added back charset, but set it to charset=utf8, according to this post: MySQL Convert latin1 data to UTF8

The import works, but autotööstus is now showing as autotööstus

How do I interpret the data dump as latin1 encoded content but deposit into a utf8 storage?

1
mysqlmariadbgyroscope-framework
Also you can convert existing databases: alter database ezine character set utf8 collate utf8_general_ci; but after that you'll have to convert each table: alter table [table_name] convert to character set utf8 collate utf8_general_ci; – Schien
See "question mark" and "Mojibake" in stackoverflow.com/questions/38363566/… . For "767", see mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes – Rick James

1 Answers

1
votes

If you cannot use latin1 as the default system encoding for whatever reason, here's a quick solution:

Leave the data dump as-is. Even though it's latin1 encoded, it can be imported as utf8.

mysql> create database ezine character set utf8 collate utf8_general_ci;

mysql -uwebuser -p --default-character-set=utf8 ezine  < dump.sql

Now in your web app, you should correctly see autotööstus

From now on, the "native currency" of your database is utf8. When dumping the database, you have to be careful, make sure the exported file is still latin1 encoded. Otherwise you'll end up with the over-encoded data, such as autotööstus.

mysqldump -uwebuser -p --default-char-set=latin1 ezine > dump.sql