Bug #82699 won't work in 5.6

• Submitted: 24 Aug 2016 3:23
• Modified: 24 Aug 2016 5:01
• Reporter: youfei patcher
• Status: Can't repeat
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.6.24
• OS: CentOS
• CPU Architecture: Any

[24 Aug 2016 3:23] youfei patcher

Description:
in mysql5.6 exec  alter table tbl_name CONVERT TO CHARACTER SET utf8mb4, but mysql won't work.

How to repeat:
table structure:
1.
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `oid` int(11) unsigned NOT NULL DEFAULT '0',
  `sid_1` int(11) unsigned NOT NULL DEFAULT '0',
  `sid_2` int(11) unsigned NOT NULL DEFAULT '0',
  `ctime` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`sid_1`,`sid_2`,`oid`),
  KEY `idx_1` (`oid`),
  KEY `idx_2` (`sid_1`,`ctime`),
  KEY `idx_3` (`sid_1`,`sid_2`,`ctime`)
) ENGINE=InnoDB AUTO_INCREMENT=3172080 DEFAULT CHARSET=utf8

2. alter tale test    CONVERT TO CHARACTER SET utf8mb4
  Query OK

3. show create table  test ;
....
....
ENGINE=InnoDB AUTO_INCREMENT=3172080 DEFAULT CHARSET=utf8

but in  5.7 ,it works.

[24 Aug 2016 5:01] MySQL Verification Team

Hello!

Thank you for the report and test case.
I'm not seeing the reported issue with GA builds. 
Please try with one the of the latest GA builds and let us know if you are still having this issue.

-- 5.5.51, 5.6.32 and 5.7.14

mysql> use test
Database changed
mysql> CREATE TABLE `test` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `oid` int(11) unsigned NOT NULL DEFAULT '0',
  `sid_1` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `sid_1` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `sid_2` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `ctime` int(11) unsigned NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`sid_1`,`sid_2`,`oid`),
    ->   UNIQUE KEY `uniq_1` (`sid_1`,`sid_2`,`oid`),
    ->   KEY `idx_1` (`oid`),
    ->   KEY `idx_2` (`sid_1`,`ctime`),
    ->   KEY `idx_3` (`sid_1`,`sid_2`,`ctime`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3172080 DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql>  alter table test    CONVERT TO CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `oid` int(11) unsigned NOT NULL DEFAULT '0',
  `sid_1` int(11) unsigned NOT NULL DEFAULT '0',
  `sid_2` int(11) unsigned NOT NULL DEFAULT '0',
  `ctime` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`sid_1`,`sid_2`,`oid`),
  KEY `idx_1` (`oid`),
  KEY `idx_2` (`sid_1`,`ctime`),
  KEY `idx_3` (`sid_1`,`sid_2`,`ctime`)
) ENGINE=InnoDB AUTO_INCREMENT=3172080 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Thanks,
Umesh