MySQL Bugs: #94161: ROW_FORMAT=COMPRESSION isn't accepted during table creation

Bug #94161	ROW_FORMAT=COMPRESSION isn't accepted during table creation
Submitted:	1 Feb 2019 4:20	Modified:	1 Feb 2019 6:36
Reporter:	John Ramsden	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	Any

Description:
I am unable to create a new table using compression.

I'm using the following options which from what I understand should allow me to use compression.

default-storage-engine          = InnoDB
innodb_large_prefix             = true
innodb_file_format              = barracuda
innodb_file_per_table           = true

This is on a brand new install of MySQL, and these configuration options have been set before creating the new database, so file per table is set.

When I try to create a new table I get the following response:

CREATE TABLE IF NOT EXISTS `db_example`.`table_example` (
   `ugid` INT NOT NULL,
   `uid` INT NOT NULL,
   PRIMARY KEY (`ugid`, `uid`))
   ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

I have tried the same thing without ENGINE=InnoDB.

Error message:

Error Code: 1031. Table storage engine for 'table_example' doesn't have this option.
I don't understand why this would be happening since the storage engine is set to innodb, which should have support for compression.

Additionally. If I force the creation of the schema to complete by disabling innodb strict mode, and then try to alter the tables enabling compression:

ALTER TABLE ${table_goes_here} ROW_FORMAT=COMPRESSED;
I get the following:

ERROR 1478 (HY000) at line 1: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'

How to repeat:
Create a table using innodb and ROW_FORMAT=COMPRESSED.

Hello John,

Thank you for the report.
I'm not seeing reported issue on latest 5.7.25 GA build, create succeeds without any issues. Second issue, i.e disabling innodb_strict_mode most likely it is duplicate of Bug #93069.

### 5.7.25 fresh build

rm -rf 94161/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/94161 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/94161 --core-file --port=3333 --socket=/tmp/mysql_ushastry.sock --log-error=$PWD/94161/log.err --log-error-verbosity=3 2>&1 &

mysql> CREATE TABLE IF NOT EXISTS `db_example`.`table_example` (
    ->    `ugid` INT NOT NULL,
    ->    `uid` INT NOT NULL,
    ->    PRIMARY KEY (`ugid`, `uid`))
    ->    ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table `db_example`.`table_example`\G
*************************** 1. row ***************************
       Table: table_example
Create Table: CREATE TABLE `table_example` (
  `ugid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`ugid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.01 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.25                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.25                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql>
mysql> SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format, @@GLOBAL.innodb_file_per_table, @@GLOBAL.innodb_default_row_format;
+------------------------------+-----------------------------+--------------------------------+------------------------------------+
| @@GLOBAL.innodb_large_prefix | @@GLOBAL.innodb_file_format | @@GLOBAL.innodb_file_per_table | @@GLOBAL.innodb_default_row_format |
+------------------------------+-----------------------------+--------------------------------+------------------------------------+
|                            1 | Barracuda                   |                              1 | dynamic                            |
+------------------------------+-----------------------------+--------------------------------+------------------------------------+
1 row in set (0.00 sec)

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

regards,
Umesh

I'm not sure if this is applicable here, but I received the same error on a table that I previously applied `COMPRESSION="zlib";`. When I removed the table compression, I was able to successfully `ALTER TABLE ROW_FORMAT=COMPRESSED`.