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
Last activity: Mar 29, 2023, 11:19 PM