Bug #94161 ROW_FORMAT=COMPRESSION isn't accepted during table creation
Submitted: 1 Feb 4:20 Modified: 1 Feb 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

[1 Feb 4:20] John Ramsden
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.
[1 Feb 6:36] Umesh Shastry
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