0

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 should be 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;;

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'
johnramsden
  • 101
  • 2
  • 5

2 Answers2

0

Error Code: 1031. Table storage engine for 'table_example' doesn't have this option.

I have gone through your error it's based on ROW_FORMAT.

As per MySQL documentation here If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf (For Linux environment) or my.ini (for windows environment) configuration file.

As MySQL documented here The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression. The COMPRESSED row format supports index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix variable, which is enabled by default.

Note: The compact family of row formats also optimizes CHAR column storage when using a variable-length character set such as utf8mb3 or utf8mb4.

For further your ref here , here

Md Haidar Ali Khan
  • 6,523
  • 9
  • 40
  • 62
0

For me, the problem was that I had previously enabled page compression on the table with:

ALTER TABLE t1 COMPRESSION="zlib";

This prevented me from applying compressed row formatting:

ALTER TABLE t1 ROW_FORMAT=COMPRESSED;

.. resulting in the following error when I tried it:

Error Code: 1031. Table storage engine for 't1' doesn't have this option.

I was able to fix the problem by removing page compression on the table:

ALTER TABLE t1 COMPRESSION="None";

This allowed me to apply compressed row formatting. See if removing page compression fixes the problem for you.

pbarney
  • 171
  • 1
  • 9