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'
15
  • Run the query SELECT @@innodb_file_format, @@innodb_file_per_table;. Just because you believe they are configured as shown does not prove that they in fact are. – Michael - sqlbot Jan 31 '19 at 21:10
  • |Barracuda | 1 | – johnramsden Jan 31 '19 at 22:14
  • It is rather useless to compress a table with only a couple of INTs. – Rick James Feb 1 '19 at 2:20
  • Which version of 5.7? – Rick James Feb 1 '19 at 2:22
  • If you are having trouble with "row size", present that as the problem, rather than chasing a possible solution ('compressed') that may not work. – Rick James Feb 1 '19 at 2:25
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

5
  • I tried removing the engine, exact same error. – johnramsden Jan 31 '19 at 22:20
  • @johnramsden, why you want to remove innodb engine. From the mysql 5.5 version, it's by default storage engine of mysql server. Why not you are using ROW_FORMAT=DEFAULT. – Md Haidar Ali Khan Jan 31 '19 at 22:24
  • I don't, I want to use InnoDB, I just want to enable compression. I'm not using default because I'm having trouble with row size length and enabling compression has been recommended. I meant I removed it from the create option. I thought what you were saying in your reply was that it's not a valid create option, maybe I misunderstood. – johnramsden Jan 31 '19 at 22:31
  • @johnramsden,Tables that use the COMPRESSED row format can be created in file-per-table tablespaces or general tablespaces. The system tablespace does not support the COMPRESSED row format. To store aCOMPRESSED table in a file-per-table tablespace, theinnodb_file_per_table variable must be enabled and innodb_file_format must be set toBarracuda. The innodb_file_per_table andinnodb_file_format variables are not applicable to general tablespaces. – Md Haidar Ali Khan Jan 31 '19 at 22:35
  • See my post. I have created file per table tablespaces. – johnramsden Jan 31 '19 at 22:37
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.