MySQL Bugs: #56318: Replication aborts with ER_TOO_BIG_ROWSIZE if innodb parameters don't match

Bug #56318	Replication aborts with ER_TOO_BIG_ROWSIZE if innodb parameters don't match
Submitted:	27 Aug 2010 1:15	Modified:	15 Nov 2010 14:38
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.50, 5.5.5-m3, 5.6.0-m4	OS:	Any
Assigned to:	Calvin Sun	CPU Architecture:	Any

Description:
If master has innodb_file_per_table=OFF or innodb_file_format=Antelope (and innodb_strict_mode=OFF), certain table parameters in CREATE TABLE, such as KEY_BLOCK_SIZE, are ignored. It might allow master to avoid ER_TOO_BIG_ROWSIZE -- it would not appear even as a warning. Meanwhile, even ignored table options are written into the binary log.

If slave has innodb_file_per_table=ON and innodb_file_format=Barracuda, it will hit ER_TOO_BIG_ROWSIZE executing the record, and slave SQL thread will abort.

The failure due to mismatch between master and slave happens in all versions. While master and slave are of the same version, it takes changing InnoDB parameters either on slave (in case of 5.1) or on master (in case of 5.5/5.6) to get the error, which does not look like a serious issue.

However if master is 5.1 and slave is 5.5/5.6, even if both are running with default parameters, the failure will still happen. This might cause problems during upgrade.

How to repeat:
1) 
- Start replication setup with master 5.1 (InnoDB plugin) and slave 5.5 with default InnoDB parameters on both;
- execute on master

DROP TABLE IF EXISTS t_no_partitions;
CREATE TABLE t_no_partitions 
(id INT NOT NULL,
name TINYTEXT NOT NULL,
INDEX name(name(255))) 
ENGINE = InnoDB 
ROW_FORMAT = COMPRESSED 
KEY_BLOCK_SIZE = 1
CHARACTER SET utf8;

- check slave status.

#--------------------

or

2)
 
# MTR test case to run on 5.5+,
# imitates default InnoDB plugin parameters on older master

--source include/master-slave.inc
--source include/have_innodb.inc

--echo # Setting parameters to imitate default setup on 5.1 master
SET GLOBAL innodb_file_per_table=0,
innodb_file_format=Antelope,
innodb_strict_mode=0;

DROP TABLE IF EXISTS t_no_partitions;
CREATE TABLE t_no_partitions 
(id INT NOT NULL,
name TINYTEXT NOT NULL,
INDEX name(name(255))) 
ENGINE = InnoDB 
ROW_FORMAT = COMPRESSED 
KEY_BLOCK_SIZE = 1
CHARACTER SET utf8;

--sync_slave_with_master

# EOF

Reverted the two default values in 5.5. Pushed into mysql-5.5-innodb tree.

Documented in the 5.5.7 changelog as follows:

        If the master had innodb_file_per_table=OFF,
        innodb_file_format=Antelope (and innodb_strict_mode=OFF), or
        both, certain CREATE TABLE options, such as KEY_BLOCK_SIZE, were
        ignored. This could allow master to avoid raising
        ER_TOO_BIG_ROWSIZE errors.

        However, the ignored CREATE TABLE options were still written
        into the binary log, so that, if the slave had
        innodb_file_per_table=ON and innodb_file_format=Barracuda, it
        would encounter an ER_TOO_BIG_ROWSIZE error while executing the
        record from the log, causing the slave SQL thread to abort and
        replication to fail.

        In the case where the master was running MySQL 5.1 and the slave
        was MySQL 5.5 (or later), the failure occurred when both master
        and slave were running with default values for
        innodb_file_per_table and innodb_file_format. This could cause
        problems during upgrades.

        To address this issue, the default values for
        innodb_file_per_table and innodb_file_format are reverted to the
        MySQL 5.1 default values&mdash;OFF and Antelope, respectively.

Also updated system variables info to show the default values changed in 5.5.0 and changed back in 5.5.7. (Not quite as simple as Luís claims in C1 above. :) )

Set NM status, waiting for merge to 5.6.

Already documented in 5.5.7; still waiting for push to -trunk.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Also documented in the 5.6.1 changelog (due to change in system variable default values).

Closed.