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 Z
s):
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!
Best Answer
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
, andREDUNDANT
are allowed as default valuesCOMPRESSED
is not allowed to a default value, but you can explicitly useROW_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
andREDUNDANT
. You should find changing to eitherROW_FORMAT=COMPACT
orROW_FORMAT=REDUNDANT
in the dump definition. If you want yourVARCHAR
columns to behave with the same characteristics as ifROW_FORMAT=FIXED
still existed, just change all yourVARCHAR
columns toCHAR
. 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?)