Bug #73153 | CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET | ||
---|---|---|---|
Submitted: | 30 Jun 2014 22:10 | Modified: | 1 Jul 2014 4:53 |
Reporter: | Daniel Black (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
Version: | 5.6, 5.6.20, 5.7.5 | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[30 Jun 2014 22:10]
Daniel Black
[1 Jul 2014 4:53]
MySQL Verification Team
Hello Daniel, Thank you for the bug report. Thanks, Umesh
[1 Jul 2014 4:53]
MySQL Verification Team
// 5.6 mysql> select version(); +-------------------------------------------+ | version() | +-------------------------------------------+ | 5.6.20-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `seller_counters` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `counter` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.43 sec) mysql> show create table seller_counters\G *************************** 1. row *************************** Table: seller_counters Create Table: CREATE TABLE `seller_counters` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `counter` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> alter table seller_counters CONVERT TO CHARACTER SET utf8; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table seller_counters\G *************************** 1. row *************************** Table: seller_counters Create Table: CREATE TABLE `seller_counters` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `counter` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) // 5.7 mysql> CREATE TABLE `seller_counters` ( -> `seller_id` int(11) NOT NULL, -> `category_id` int(11) NOT NULL, -> `counter` int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY (`seller_id`,`category_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.42 sec) mysql> alter table seller_counters CONVERT TO CHARACTER SET utf8; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table seller_counters\G *************************** 1. row *************************** Table: seller_counters Create Table: CREATE TABLE `seller_counters` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `counter` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select version(); +----------------------------------------------+ | version() | +----------------------------------------------+ | 5.7.5-m15-enterprise-commercial-advanced-log | +----------------------------------------------+ 1 row in set (0.00 sec) ## Observed that this issue is happens when there are no char columns n the table mysql> create table ttt(seller_id int not null, category_id int(11) NOT NULL DEFAULT '0',seller char(10) not null, PRIMARY KEY (`seller_id`,`category_id`))ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.34 sec) mysql> show create table ttt\G *************************** 1. row *************************** Table: ttt Create Table: CREATE TABLE `ttt` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL DEFAULT '0', `seller` char(10) NOT NULL, PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> alter table ttt CONVERT TO CHARACTER SET utf8; Query OK, 0 rows affected (0.60 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table ttt\G *************************** 1. row *************************** Table: ttt Create Table: CREATE TABLE `ttt` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL DEFAULT '0', `seller` char(10) NOT NULL, PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
[15 Mar 2016 18:40]
Gillian Gunson
This also seems to happen if the columns in the table are already the charset/collation that you're trying to convert the table to: mysql> show create table test_table\G *************************** 1. row *************************** Table: test_table Create Table: CREATE TABLE `test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `test_string` varchar(255) CHARACTER SET utf8 NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> alter table test_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test_table\G *************************** 1. row *************************** Table: test_table Create Table: CREATE TABLE `test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `test_string` varchar(255) CHARACTER SET utf8 NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Maybe when there's no column to convert the default change is forgotten.
[20 Jul 2016 10:48]
Sveta Smirnova
If you want to change default charset to such talbe use "default charset=utf8;" instead of "convert": mysql> CREATE TABLE `seller_counters` ( -> `seller_id` int(11) NOT NULL, -> `category_id` int(11) NOT NULL, -> `counter` int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY (`seller_id`,`category_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.28 sec) mysql> alter TABLE `seller_counters` default charset=utf8; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create TABLE `seller_counters`\G *************************** 1. row *************************** Table: seller_counters Create Table: CREATE TABLE `seller_counters` ( `seller_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `counter` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)