Sample Header Ad - 728x90

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

0 votes
2 answers
790 views
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
Asked by Toto (93 rep)
Mar 10, 2023, 02:27 AM
Last activity: Mar 29, 2023, 11:19 PM