change mysql file format and collation

Question

I need to apply this setting to MariaDB 5.5. Is it safe to do it on running DB? Will it affect only new databases or old DB in Latin1/nonUTF8 might be crashed? Of cource I will run full backup, but just want to know about others experience.

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

Please be sure to accept an answer to close the post and to show others the solution if they have a similar issue. — Mech, Feb 24 '20 at 01:18

score 2 · Answer 1 · answered Feb 10 '20 at 23:36

2

I've done this exact thing to allow for emojis to be correctly parsed. I didn't have an issue and I did it on live (along with a backup!).

My vote, based on my experience, is to proceed.

answered Feb 10 '20 at 23:36

Mech

3,952
2
14
25

1

Correct. All these setting apply to newly created tables (or for innodb* `ALTER TABLES` that result in a rebuild). – danblack Feb 11 '20 at 00:14
Until you rebuild the tables the encoding in any particular table, or even column, will remain as it was. As danblack says you must `ALTER` to rebuild. – tadman Feb 11 '20 at 00:39

Rick James · Answer 2 · 2020-02-19T18:53:38.157

Note the "default-" -- This implies that nothing already in existence will be affected by it.

There is more than what you did to make Emojis possible. See "best practice" in Trouble with UTF-8 characters; what I see is not what I stored

As Mech implied, existing tables will need to be ALTERed if the columns are not already CHARACTER SET utf8mb4.

Also, for Barracuda, file_per_table and large_prefix to take effect on a table, you must do an ALTER. When finished, use SHOW CREATE TABLE as a partial variation that changes have taken effect. ALTER TABLE t CONVERT TO utf8mb4; is probably the simplest.

Since you are using 5.5 or 5.6, there are more things that need fixing to handle indexing VARCHAR(255); see http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

change mysql file format and collation

2 Answers2