ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option

Question

I am trying to import mysqldump into following system:

# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core) 
# rpm -qa | grep ^mysql
mysql-community-libs-compat-5.7.11-1.el7.x86_64
mysql-community-libs-5.7.11-1.el7.x86_64
mysql57-community-release-el7-7.noarch
mysql-community-client-5.7.11-1.el7.x86_64
mysqltuner-1.6.0-1.el7.noarch
mysql-community-server-5.7.11-1.el7.x86_64
mysql-community-common-5.7.11-1.el7.x86_64
# 

yet, I getting following error:

# mysql X < ~/X.mysqldump
ERROR 1031 (HY000) at line 13639: Table storage engine for 'Y' doesn't have this option
#

the Y table from mysqldump: (I replaced sensitive information with Zs):

DROP TABLE IF EXISTS `Y`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Y` (
  `Z` int(10) NOT NULL AUTO_INCREMENT,
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '0',
  `Z` tinyint(1) NOT NULL DEFAULT '1',
  `Z` int(10) NOT NULL DEFAULT '0',
  `Z` datetime DEFAULT NULL,
  `Z` datetime DEFAULT NULL,
  PRIMARY KEY (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`),
  KEY `Z` (`Z`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='InnoDB free: 8192 kB; (`Z`) REFER `Z/';
/*!40101 SET character_set_client = @saved_cs_client */;

/etc/my.cnf is all defaults, I did however added following line(s):

# tail -1 /etc/my.cnf
innodb_buffer_pool_size = 64G
# 

Also, here is full mysql> SHOW VARIABLES LIKE 'innodb%'; stack.

I'm going over MySQL :: MySQL 5.7 Reference Manual :: 14.4 InnoDB Configuration, but obviously I'm missing something...

Please advise.

Thanks!

Answer 1

Under MySQL 5.7, ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine.

According to the MySQL 5.7 Documentation for innodb_default_row_format, these are values you can use for the table's row format:

• DYNAMIC, COMPACT, and REDUNDANT are allowed as default values
• COMPRESSED is not allowed to a default value, but you can explicitly use ROW_FORMAT=COMPRESSED

There is no mention of ROW_FORMAT=FIXED for InnoDB in the MySQL 5.7 Docs.

I wrote an answer to a post about 8 months entitled Does an empty column value occupy same storage space as a filled column value? which explains the difference between COMPACT and REDUNDANT. You should find changing to eitherROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT in the dump definition. If you want your VARCHAR columns to behave with the same characteristics as if ROW_FORMAT=FIXED still existed, just change all your VARCHAR columns to CHAR. Then, load the dump.

Please keep in mind that ROW_FORMAT=FIXED really benefits MyISAM (See my old post Which DBMS is good for super-fast reads and a simple data structure?)

Answer 2

Edit the /etc/my.cnf file.

Add the parameter under:

[mysqld]

innodb_strict_mode=off

Now, Restart the server