0

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
2
  • The string value of ENUM datatype column is not a column value but a column definition. Real column value (the value which is stored in the table body) is TINYINT. So charset/collation specifying for the value makes no sense. Check for character_set_results value.
    – Akina
    Mar 10, 2023 at 4:23
  • character_set_results is utf8mb4
    – Toto
    Mar 10, 2023 at 12:39

2 Answers 2

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.)

0

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

3
  • Unfortunately I get the same error doing the conversion on the table or column level. May it be an old bug? heidisql.com/forum.php?t=21524
    – Toto
    Mar 17, 2023 at 17:45
  • This answer should work (see: DBFIDDLE). Please specify the COMPLETE definition from your ENUM, because 'Cor?e' does not seem to exist in the given example....
    – Luuk
    Mar 18, 2023 at 12:14
  • @Luuk Thanks!!! Actually it is an other table to replicate issue: "Bleu foncé", "Bleu foncé moyen". Seems like meta data is stored in ISO and does not properly convert. Maybe there is a configuration issue I have to investigate.
    – Toto
    Mar 19, 2023 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.