[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: File mdev13111.sql    

 Description   

The following table can be created in MariaDB 10.1.22:

CREATE TABLE `a10` (
  `id` int(11) NOT NULL,
  `v1` varchar(10) DEFAULT NULL,
  `v2` varchar(10) DEFAULT NULL,
[...]
  `v728` varchar(10) DEFAULT NULL,
  `v729` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

With MariaDB 10.2.6 this fails with

ERROR 1118 (42000) at line 2: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

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

ERROR 1118 (42000) at line 2: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

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.
mdev13111.sql

Comment by Marko Mäkelä [ 2017-06-26 ]

This appears to be expected behaviour.
The default value of the setting innodb_strict_mode was changed from OFF to ON in MySQL 5.7. MariaDB 10.2.2 inherited that change.
With the following, I can create all columns:

SET innodb_strict_mode=OFF;
--disable_query_log
eval CREATE TABLE a10 (id int PRIMARY KEY, $cols) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
eval INSERT INTO a10 SET id=1,$scols,v729='ab';
--enable_query_log
DROP TABLE a10;

SET innodb_strict_mode=OFF;
Warnings:
Warning	139	Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
DROP TABLE a10;

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

SET innodb_strict_mode=ON;

is issued before the CREATE TABLE.

For brevity, I have omitted the $cols and $scols above:

let $cols=
v1 varchar(10) DEFAULT NULL,
v2 varchar(10) DEFAULT NULL,
v728 varchar(10) DEFAULT NULL,
v729 varchar(10) DEFAULT NULL;
 
let $scols=
v1='abcdefghij',
v2='abcdefghij',
v727='abcdefghij',
v728='abcdefghij';

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/
Is there any specific area that this should be further documented?

Generated at Thu Feb 08 08:02:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.