| Bug #26731 | ALTER TABLE `Table` CONVERT TO CHARSET .... Fails on Enums | ||
|---|---|---|---|
| Submitted: | 28 Feb 2007 20:06 | Modified: | 1 Mar 2007 8:55 |
| Reporter: | Jonas Jonsson | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.0.26-log, 4.1, 5.1, 5.2 | OS: | Linux (Gentoo Linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Feb 2007 20:09]
Jonas Jonsson
Sorry..
Forgot one thing in the test case.
mysql> show full fields from `test`;
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| letter | enum('a',' | utf8_swedish_ci | NO | | a | | select,insert,update,references | |
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
[1 Mar 2007 8:55]
Sveta Smirnova
Thank you for the report. Verified as described.
[5 Nov 2014 15:33]
Daniël van Eeden
Reproducable on 5.6.21. This does a lot of damage even if STRICT_ALL_TABLES and InnoDB Strict Mode are enabled. I think this should have severity S2 instead of S3.
mysql [test3] > CREATE TABLE t1 (id int auto_increment primary key, val varchar(100), val2 enum('Café','Wrocław')) default character set utf8;
Query OK, 0 rows affected (0.01 sec)
mysql [test3] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(100) DEFAULT NULL,
`val2` enum('Café','Wrocław') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [test3] > ALTER TABLE t1 CONVERT TO CHARACTER SET utf32;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [test3] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(100) DEFAULT NULL,
`val2` enum('?','??') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf32
1 row in set (0.00 sec)
mysql [test3] > ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [test3] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(100) DEFAULT NULL,
`val2` enum('Café6C3A9','WrocławC5826177') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [test3] > select @@innodb_strict_mode, @@sql_mode;
+----------------------+-------------------+
| @@innodb_strict_mode | @@sql_mode |
+----------------------+-------------------+
| 1 | STRICT_ALL_TABLES |
+----------------------+-------------------+
1 row in set (0.00 sec)
[5 Nov 2014 15:38]
Daniël van Eeden
Also STRICT_ALL_TABLES and InnoDB Strict Mode do not prevent me creating ENUMs with chars which are not valid for the character set of the table.
mysql [test3] > CREATE TABLE t2 (id int auto_increment primary key, val varchar(100), val2 enum('Café','Wrocław'));
Query OK, 0 rows affected (0.01 sec)
mysql [test3] > show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(100) DEFAULT NULL,
`val2` enum('Café','Wroc?aw') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [test3] > select @@innodb_strict_mode, @@sql_mode; +----------------------+-------------------+
| @@innodb_strict_mode | @@sql_mode |
+----------------------+-------------------+
| 1 | STRICT_ALL_TABLES |
+----------------------+-------------------+
1 row in set (0.00 sec)
[5 Nov 2014 15:42]
Daniël van Eeden
Addition to my previous message: I can create the table and are also allowed to insert this value. This differs from how a varchar is handled.
mysql [test3] > insert into t2(val, val2) values('Wrocław','Wrocław');
ERROR 1366 (HY000): Incorrect string value: '\xC5\x82aw' for column 'val' at row 1
mysql [test3] > insert into t2(val, val2) values('Café','Wrocław');
Query OK, 1 row affected (0.00 sec)
mysql [test3] > select * from t2;
+----+-------+---------+
| id | val | val2 |
+----+-------+---------+
| 1 | Café | Wroc?aw |
+----+-------+---------+
1 row in set (0.01 sec)
[5 Nov 2014 16:04]
Daniël van Eeden
This can also cause information_schema queries to generate warnings.
mysql [footest] > create table t3 (id int, val enum('Café','foo'));
Query OK, 0 rows affected (0.01 sec)
mysql [footest] > select table_schema, table_name, column_name from information_schema.columns where table_name='t3';
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| footest | t3 | id |
| footest | t3 | val |
+--------------+------------+-------------+
2 rows in set (0.00 sec)
mysql [footest] > alter table t3 convert to character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [footest] > select table_schema, table_name, column_name from information_schema.columns where table_name='t3';
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| footest | t3 | id |
| footest | t3 | val |
+--------------+------------+-------------+
2 rows in set, 2 warnings (0.00 sec)
Warning (Code 1366): Incorrect string value: '\xE9','fo...' for column 'COLUMN_TYPE' at row 1
Warning (Code 1366): Incorrect string value: '\xE9','fo...' for column 'COLUMN_TYPE' at row 1
mysql [footest] > show create table t3\G *************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`val` enum('Caf�','foo') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[8 Nov 2014 9:01]
Daniël van Eeden
This is how PostgreSQL 9.4 behaves with enums:
I had to start psql with --no-readline to be able to enter multibyte characters.
---------------------------------------------
postgres=# CREATE DATABASE test1 WITH TEMPLATE template0 ENCODING 'LATIN1' LC_CTYPE 'C' LC_COLLATE='C';
CREATE DATABASE
postgres=# CREATE DATABASE test2;
CREATE DATABASE
postgres=# \c test2
You are now connected to database "test2" as user "postgres".
test2=# create type test1 as enum ('Cafe','Wrocław');
CREATE TYPE
test2=# create type test2 as enum ('Café','Wrocław');
CREATE TYPE
test2=# \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Access privileges | Description
--------+-------+---------------+------+----------+-------------------+-------------
public | test1 | test1 | 4 | Cafe +| |
| | | | Wrocław | |
public | test2 | test2 | 4 | Café +| |
| | | | Wrocław | |
(2 rows)
test2=# \c test1
You are now connected to database "test1" as user "postgres".
test1=# create type test1 as enum ('Cafe','Wrocław');
ERROR: character with byte sequence 0xc5 0x82 in encoding "UTF8" has no equivalent in encoding "LATIN1"
test1=# create type test2 as enum ('Café','Wrocław');
ERROR: character with byte sequence 0xc5 0x82 in encoding "UTF8" has no equivalent in encoding "LATIN1"
---------------------------------------------
PostgreSQL handles character set encodings on a cluster and database level, not on a table/column level.
PostgreSQL doesn't have a CONVER TO CHARACTER SET option. Conversion is done with a dump/restore cycle.
---------------------------------------------
test1=# create type test3 as enum ('Café','Wroclaw');
CREATE TYPE
test1=# \dT+ test3
List of data types
Schema | Name | Internal name | Size | Elements | Access privileges | Description
--------+-------+---------------+------+----------+-------------------+-------------
public | test3 | test3 | 4 | Café +| |
| | | | Wroclaw | |
(1 row)
$ pg_dump -U postgres -E utf8 test1 > test1_utf8.sql
$ psql --no-readline -U postgres test2 < test1_utf8.sql
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TYPE
ALTER TYPE
REVOKE
REVOKE
GRANT
GRANT
test2=# \dT+ test3
List of data types
Schema | Name | Internal name | Size | Elements | Access privileges | Description
--------+-------+---------------+------+----------+-------------------+-------------
public | test3 | test3 | 4 | Café +| |
| | | | Wroclaw | |
(1 row)
---------------------------------------------
Description: When using the ALTER TABLE `table` CONVERT TO CHARACTER SET. Enums fails to convert. If I create a table with an enum with chars that doesn't exists in the new charset then this char and rest of the enum definition will be removed. If I set a default value the alter command fails with #1067: Invalid default value for.... If I change the default value to a char that exists in the new charset, the commnad is accepted. But then the problem above occurs. How to repeat: mysql> select version(); +------------+ | version() | +------------+ | 5.0.26-log | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `test` (`letter` ENUM('a','å','ä','ö') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'å') ENGINE = myisam CHARACTER SET latin1; Query OK, 0 rows affected (0.02 sec) mysql> show full fields from `test`; +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | letter | enum('a','å','ä','ö') | latin1_swedish_ci | NO | | å | | select,insert,update,references | | +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 1 row in set (0.00 sec) mysql> insert into `test` values (); Query OK, 1 row affected (0.00 sec) mysql> insert into `test` values ('a'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +--------+ | letter | +--------+ | å | | a | +--------+ 2 rows in set (0.00 sec) mysql> alter table `test` convert to character set utf8 collate utf8_swedish_ci; ERROR 1067 (42000): Invalid default value for 'letter' mysql> alter table `test` alter `letter` set default 'a'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show full fields from `test`; +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | letter | enum('a','å','ä','ö') | latin1_swedish_ci | NO | | a | | select,insert,update,references | | +--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 1 row in set (0.00 sec) mysql> alter table `test` convert to character set utf8 collate utf8_swedish_ci; Query OK, 2 rows affected, 3 warnings (0.02 sec) Records: 2 Duplicates: 0 Warnings: 1 mysql> select * from `test`; +--------+ | letter | +--------+ | | | a | +--------+ 2 rows in set (0.00 sec)