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

Notice removed Draw attention by CommunityBot

occurred Mar 21, 2023 at 16:08

Bounty Ended with no winning answer by CommunityBot

occurred Mar 21, 2023 at 16:08

added 14 characters in body

Source Link

edited Mar 20, 2023 at 16:44

Toto

93
1
7

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:

convert enums/sets to varchars
convert table charset
convert back varchars to enums/sets

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:

convert enums to varchars
convert table charset
convert back varchars to enums

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:

convert enums/sets to varchars
convert table charset
convert back varchars to enums/sets

added 122 characters in body

Source Link

edited Mar 17, 2023 at 17:53

Toto

93
1
7

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:

convert enums to varchars

convert table charset

convert back varchars to enums

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 |
+--------------------------+--------------------+

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:

convert enums to varchars

convert table charset

convert back varchars to enums

Notice added Draw attention by Toto

occurred Mar 13, 2023 at 14:54

Bounty Started worth 50 reputation by Toto

occurred Mar 13, 2023 at 14:54

added 809 characters in body

Source Link

edited Mar 10, 2023 at 14:05

Toto

93
1
7

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 |
+--------------------------+--------------------+

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

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 |
+--------------------------+--------------------+

Source Link

asked Mar 10, 2023 at 2:27

Toto

93
1
7

Loading

Stack Exchange Network

Return to Question