Bug #70778 SHOW CREATE TABLE doesn't put STATS_PERSISTENT into version specific comments
Submitted: 30 Oct 2013 22:41 Modified: 31 Oct 2013 3:51
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:mysql-5.6.14 OS:Any
Assigned to: CPU Architecture:Any

[30 Oct 2013 22:41] Hartmut Holzgraefe 
Description:
When using the new STATS_PERSISTENT option it is not version protected like other not backwards-compatible options. This makes restore attempts of dumps taken from mysql 5.6 in older versions like 5.5 impossible if STATS_PERSISTENT is explicitly set, as it is for some of the mysql system tables like mysql.slave_master_info and mysqldump relies on SHOW CREATE TABLE to dump schema information

How to repeat:
CREATE TABLE t5 (
  id PRIMARY KEY,
  msg varchar(100) COLUMN_FORMAT FIXED
) ENGINE=InnoDB STATS_PERSISTENT=0;

mysql> show create table test.t1;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t5` (
  `id` int(11) NOT NULL,
  `msg` varchar(100) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
1 row in set (0.00 sec)

See how e.g.COLUMN_FORMAT is version protected but STATS_PERSISTENT is not

Suggested fix:
Add /*!50606 */ around STATS_PERSISTENT (AFAICT this was added in 5.6.6),
do similar for any other new option not understood by older versions ...
[31 Oct 2013 3:51] MySQL Verification Team 
Hello Hartmut,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[29 Nov 2013 19:17] MySQL Verification Team 
// 5.6.15

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (
    ->   id int not null PRIMARY KEY,
    ->   msg varchar(100) COLUMN_FORMAT FIXED
    -> ) ENGINE=InnoDB STATS_PERSISTENT=0;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `msg` varchar(100) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
1 row in set (0.00 sec)
[25 Jan 2015 16:11] Frodo Looijaard 
Patch against MySQL 5.6.22: stats_persistent as version specific comment

Attachment: mysql-5.6.22-stats_persistent.patch (text/x-patch), 886 bytes.