[MDEV-13111] Different default value of innodb_strict_mode in MariaDB 10.1 and 10.2 Created: 2017-06-16 Updated: 2018-08-31 Resolved: 2017-07-12 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Affects Version/s: | 10.2.6, 10.2 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Hartmut Holzgraefe | Assignee: | Ian Gilfillan |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | None | ||
| Attachments: |
|
| Description |
|
The following table can be created in MariaDB 10.1.22:
With MariaDB 10.2.6 this fails with
When removing one of the VARCHAR(10) lines creating the table succeeds. When using ROW_FORMAT=COMPRESSED and innodb_file_format=Baracuda instead of Antelope creating the table still fails, now with
Again, when removing one of the VARCHAR(10) columns the CREATE succeeds. With MariaDB 10.1.22 and ROW_FORMAT=COMPACT the CREATE even succeeds with 1000 VARCHAR(10) columns. I assume that this difference is caused by our switch from XtraDB to InnoDB, as I ran into this with MySQL 5.6 earlier: https://bugs.mysql.com/bug.php?id=76793 I reported |
| Comments |
| Comment by Elena Stepanova [ 2017-06-25 ] | ||||||||||||||||||||||||
|
I see the difference in behavior between 10.1 and 10.2 with COMPACT row format, but it's not due to XtraDB=>InnoDB switch, the same difference can be observed if 10.1 runs with the InnoDB plugin. It also seems to be unrelated to Antelope vs Barracuda, since it's reproducible even when both servers are running with the same format (doesn't matter Antelope or Barracuda). Strangely, the same table created on 10.1 seems to work after upgrading to 10.2. I've attached the SQL which sets the format to Barracuda and (re-)creates the table. | ||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2017-06-26 ] | ||||||||||||||||||||||||
|
This appears to be expected behaviour.
This warning means that it is possible that some INSERT statements will fail due to exceeding the maximum row size. The maximum size of a record should be 8133 bytes (5 bytes fixed-size header, 4 bytes for the PRIMARY KEY, 6+7 bytes for DB_TRX_ID,DB_ROLL_PTR, and finally 729*11.125 bytes for the VARCHAR(10) columns. The 1.125 bytes are the actual length of the column and the ‘is NULL’ flag. If the last column value in the INSERT statement is made 1 byte longer, the INSERT will fail due to too long record, in both 10.2 and 10.1. In 10.1, the CREATE TABLE will fail if
is issued before the CREATE TABLE. For brevity, I have omitted the $cols and $scols above:
What can we change here? I believe that we cannot change the default value of innodb_strict_mode in 10.2. | ||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2017-07-05 ] | ||||||||||||||||||||||||
|
I think that this is a documentation bug. | ||||||||||||||||||||||||
| Comment by Ian Gilfillan [ 2017-07-06 ] | ||||||||||||||||||||||||
|
innodb_strict_mode is already specifically listed as an incompatibility in https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-101-to-mariadb-102/ |