How layers of character set configuration fit together in MySQL?

Question

I have a web app (Rails) which configured to use utf8 for encoding. My database has configuration for character set like:

show variables like 'char%';


"character_set_client"  "utf8mb4"
"character_set_connection"  "utf8mb4"
"character_set_database"    "latin1"
"character_set_filesystem"  "binary"
"character_set_results" "utf8mb4"
"character_set_server"  "latin1"
"character_set_system"  "utf8"

What I need to do is to convert columns and tables (and maybe the database later) to a different character set.

1- In my setup does my app is client in MySQL terminology?

2- When converting a col/table/database, what should I change to not get the error:

Mysql2::Error: Illegal mix of collations...

Likely duplicate of https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error — Bill Karwin, Apr 10 '19 at 18:45
I think I have used this: ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; — MIA, Apr 19 '19 at 16:17

score 1 · Accepted Answer · answered Apr 11 '19 at 08:53

1

The charset does not matter with config of the server, a schema/database or the table.There are just the default that cascade from server -> database-> table. The columns and the connection on the other hand have charset and should be consistent to establish a fine database setup.

answered Apr 11 '19 at 08:53

MIA

373
3
18

1

And note that "utf8" and "utf8mb4" are different. (utf8mb4 is better.) – Rick James Apr 19 '19 at 15:55

How layers of character set configuration fit together in MySQL?

1 Answers1