MySQL Bugs: #77940: can't use output of "show create table" to create a new table

Bug #77940	can't use output of "show create table" to create a new table
Submitted:	5 Aug 2015 1:20	Modified:	20 Aug 2015 12:27
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.8, 5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql> create table t1 (a int, b blob, c blob) compression='zlib';
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` blob,
  `c` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESS='zlib'
1 row in set (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> use db2;
Database changed
mysql>  CREATE TABLE `t1` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` blob,
    ->   `c` blob
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESS='zlib';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMPRESS='zlib'' at line 5

How to repeat:
described above

Suggested fix:
print compression='zlib' rather than compress='zlib'

Hello Zhai,

Thank you for the report.
Verified this with 5.7.8, 5.7.9 builds.

Thanks,
Umesh

// 5.7.8, 5.7.9

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` blob,
  `c` blob
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMPRESS='zlib'
1 row in set (0.01 sec)

Posted by developer:
 
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 34488c1..4b9de75 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -4774,7 +4774,7 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables,
       /* In the .frm file this option has a max length of 2K. Currently,
       InnoDB uses only the first 5 bytes and the only supported values
       are (ZLIB | LZ4 | NONE). */
-      ptr= my_stpcpy(ptr, " COMPRESS=\"");
+      ptr= my_stpcpy(ptr, " COMPRESSION=\"");
       ptr= strxnmov(ptr, 7, share->compress.str, NullS);
       ptr= my_stpcpy(ptr, "\"");
     }
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8545348..3b8aea9 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4508,7 +4508,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	< compress->length)
     {
       my_error(ER_WRONG_STRING_LENGTH, MYF(0),
-	       compress->str, "COMPRESS", TABLE_COMMENT_MAXLEN);
+	       compress->str, "COMPRESSION", TABLE_COMMENT_MAXLEN);
       DBUG_RETURN(TRUE);
     }
   }

This should fix it.

Fixed as of the upcoming 5.7.9, 5.8.0 release, and here's the changelog entry:

The COMPRESSION option was displayed incorrectly in SHOW CREATE TABLE
output. 

Thank you for the bug report.