Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.1.11
-
None
-
Linux, Windows
Description
ALTER TABLE table CONVERT TO CHARACTER SET ... has no effect on tables without any textual columns (TEXT, VARCHAR). INFORMATION_SCHEMA still reports the old character set after performing the conversion. If a textual column is added afterwards, it's still created with the old character set and collation. Here's an SQL script to reproduce the issue:
create database collation_test; |
use collation_test; |
|
|
drop table if exists `foobar`; |
|
|
CREATE TABLE `foobar` ( |
`foobar_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, |
`version` bigint(20) NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar'; |
|
|
ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
|
|
select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar'; |
|
|
alter table foobar add column txt VARCHAR(255); |
|
|
select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar'; |
|
|
ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
And the output from MariaDB:
MariaDB [(none)]> create database collation_test;
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [(none)]> use collation_test;
|
Database changed
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> drop table if exists `foobar`;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> CREATE TABLE `foobar` (
|
-> `foobar_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
-> `version` bigint(20) NOT NULL
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
|
+------------+-------------------+
|
| table_name | table_collation |
|
+------------+-------------------+
|
| foobar | latin1_swedish_ci |
|
+------------+-------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [collation_test]> /* Next statement should print utf8_unicode_ci but it's still latin1_swedish_ci */
|
MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
|
+------------+-------------------+
|
| table_name | table_collation |
|
+------------+-------------------+
|
| foobar | latin1_swedish_ci |
|
+------------+-------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> alter table foobar add column txt VARCHAR(255);
|
Query OK, 0 rows affected (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
|
+------------+-------------------+
|
| table_name | table_collation |
|
+------------+-------------------+
|
| foobar | latin1_swedish_ci |
|
+------------+-------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [collation_test]>
|
MariaDB [collation_test]> ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
|
Query OK, 0 rows affected (0.02 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [collation_test]>
|
Just FYI, MySQL 5.6 and newer behaves correctly (it runs on one of our CI servers).
Attachments
Issue Links
- duplicates
-
-
- Closed
-