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
  • 1
  • 5
  • 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 01 '19 at 02:20
  • Which version of 5.7? – Rick James Feb 01 '19 at 02: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 01 '19 at 02:25
  • But was @@innodb_file_per_table=1 when you created the table? (The current value has no impact.) – Rick James Feb 01 '19 at 02:26
  • @RickJames Yes. I added the configuration file before installing MySQL. – johnramsden Feb 01 '19 at 02:46
  • Hmmm... This may be worth a bug report at bugs.mysql.com – Rick James Feb 01 '19 at 02:52
  • @RickJames this is also an example table, but the actual tables are enormous. I am having trouble with "row size", but that is a whole nother issue and this issue still needs a solution. I was considering posting my "row size" issue but considering there were so many other questions that we're off the same topic (all of the solutions which I tried didn't work), I figured it would be looked at as a duplicate. If you still think it would be worth while posting the question I have a fair bit of information regarding what I've tried and could post it. – johnramsden Feb 01 '19 at 02:54
  • @RickJames I'll post a bug report then. – johnramsden Feb 01 '19 at 02:54
  • "Row size" problems tend to be solvable by (1) not always saying VARCHAR(255); (2) Normalizing (where appropriate), or (3) "vertical" partitioning. Start a new Question; I'll critique it from those (and maybe other) point of view. – Rick James Feb 01 '19 at 03:10
  • @RickJames Thanks, I'll do that. Part of the problem was what working with a code base where vertically partitioning the existing database would be quite a undertaking and we were hoping for a temporary solution. – johnramsden Feb 01 '19 at 03:19
  • @johnramsden - Maybe this will 'eliminate' the vertical partition hassle: Build a VIEW that puts the pieces back together. – Rick James Feb 01 '19 at 03:22
  • @RickJames Yea, that's the solution we plan on pursuing ultimately. The hope was to fix it in configuration for now. – johnramsden Feb 01 '19 at 03:58
  • @RickJames thanks for your help so far. I posted my other question – johnramsden Feb 01 '19 at 04:13

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,447
  • 9
  • 38
  • 60
  • 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.

pbarney
  • 171
  • 1
  • 8