3

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!

2

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?)

| improve this answer | |
1

Edit the /etc/my.cnf file.

Add the parameter under:

[mysqld]

innodb_strict_mode=off

Now, Restart the server

| improve this answer | |

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.