[MDEV-11305] MariDB 10.2.2 errno: 140 "Wrong create options", when creating an innodb Table with 'ROW_FORMAT=FIXED' options Created: 2016-11-18  Updated: 2016-11-19  Resolved: 2016-11-19

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Storage Engine - InnoDB
Affects Version/s: 10.2.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Francisco Dueñas Assignee: Ian Gilfillan
Resolution: Not a Bug Votes: 0
Labels: innodb
Environment:

Windows 2012 server


 Description   

When trying to create an innodb table with this settings

CREATE TABLE `__syssequences` (
`systemName` CHAR(50) NOT NULL,
`sequenceName` CHAR(50) NOT NULL,
`sequenceStep` INT(11) DEFAULT '1',
`lastValue` INT(11) DEFAULT '1',
`lastInsert` DATETIME DEFAULT '0000-00-00 00:00:00',
`lastUpdate` DATETIME DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`systemName`,`sequenceName`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

Can't create table `ezgobx`.`__syssequences` (errno: 140 "Wrong create options")

If I remove ROW_FORMAT=FIXED or set ROW_FORMAT=DEFAULT it works perfect. I used that creation option in mysql 5.6 wihout issues

 Comments   
Comment by Elena Stepanova [ 2016-11-18 ]

InnoDB does not have ROW_FORMAT FIXED. It didn't have it in MySQL 5.6 either.

However, in InnoDB 5.6 (both in MySQL 5.6 and MariaDB 10.0/10.1) innodb_strict_mode=0 by default, that's why the CREATE statement simply issued a warning and assumed COMPACT format instead. 

MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.6.33-debug |
 
+--------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> CREATE TABLE `__syssequences` (
 
    -> `systemName` CHAR(50) NOT NULL,
 
    -> `sequenceName` CHAR(50) NOT NULL,
 
    -> `sequenceStep` INT(11) DEFAULT '1',
 
    -> `lastValue` INT(11) DEFAULT '1',
 
    -> `lastInsert` DATETIME DEFAULT '0000-00-00 00:00:00',
 
    -> `lastUpdate` DATETIME DEFAULT '0000-00-00 00:00:00',
 
    -> PRIMARY KEY (`systemName`,`sequenceName`)
 
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
 
Query OK, 0 rows affected, 1 warning (0.35 sec)
 
 
 
MySQL [test]> show warnings;
 
+---------+------+--------------------------------------+
 
| Level   | Code | Message                              |
 
+---------+------+--------------------------------------+
 
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
 
+---------+------+--------------------------------------+
 
1 row in set (0.00 sec)

In InnoDB 5.7 (both in MySQL 5.7 and MariaDB 10.2) innodb_strict_mode=1 by default, so both of them fail to create a table with the wrong ROW_FORMAT.
If you want the old behavior, set innodb_strict_mode=0.

greenman,
Maybe it should be explained somehow in the documentation if it hasn't been yet (I couldn't find).
While technically every part of it is documented separately – supported row formats as well as the new default for the strict mode, – it might be not easy for users to put it all together.
MySQL, for example, describes it directly at CREATE TABLE page: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

Comment by Ian Gilfillan [ 2016-11-19 ]

I have added some detail to the CREATE TABLE page, and, probably more importantly, highlighted the strict mode change on the 10.2.2 release notes page. Also discovered the rather obscure bug I reported at https://jira.mariadb.org/browse/MDEV-11316 as a result.

Closed as not a bug, as the behaviour described in the original report is expected with the change to the default strict mode.

Generated at Thu Feb 08 07:48:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.