Converting from latin1 to utf8mb4 of ENUM and SET with accented characters on mysql

Question

I am trying to convert tables from latin1 to utf8mb4 on MySQL 5.7.

Everything works fine except the tables with ENUM and SET which contains a value with an accented character. 2 types of errors are generated.

For exemple:

CREATE TABLE `car` (
  ...
  `color` enum('Rouge','Bleu foncé',...) DEFAULT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This query does not work:

> ALTER TABLE `car` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#1265 - Data truncated for column 'color' at row 4

On other tables I get this error:

#1291 - Column 'origin' has duplicated value 'Cor?e' in ENUM

Global and session variables are:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

PS: Current work around is to:

1. convert enums/sets to varchars
2. convert table charset
3. convert back varchars to enums/sets

Answer 1

The charset for ENUM/SET may be set

• on CREATE DATABASE
• or CREATE TABLE
• Or during the connection parameters (Cf SET NAMES) together with the setting of the app that is talking to MySQL.

I don't know which.

(The DBFIDDLE proposed fails to play with DATABASE or the connection, so it may be working without understanding why.)

Answer 2

Instead of trying change the whole table, try changing just the column and

First make an empty table and change the definition of the color column

CREATE TABLE car_copy LIKE car;
ALTER TABLE car_copy
    MODIFY COLUMN color enum('Rouge','Bleu foncé',...) 
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
;

If this works, you can attempt or inserting data into car_copy

INSERT INTO car_copy SELECT * FROM car;

or just do the ALTER TABLE on car

ALTER TABLE car
    MODIFY COLUMN color enum('Rouge','Bleu foncé',...) 
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
;

Please try these and tell us what happens