MySQL Bugs: #49522: Replication problem with mixed MyISAM/InnoDB

Bug #49522	Replication problem with mixed MyISAM/InnoDB
Submitted:	8 Dec 2009 1:33	Modified:	14 Oct 2010 12:27
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.39, 5.1 bzr	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	innodb, myisam, regression, replication

Description:
A transaction appears to be incorrectly transported in replication when the master is MyISAM and the slave is InnoDB.

It works fine in 5.0.87, but fails in 5.1.39

How to repeat:
MySQL 5.1.39
master/slave configuration with following table on master:

CREATE TABLE `MailerType` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`active` tinyint(1) unsigned DEFAULT NULL,
`en_US` varchar(300) DEFAULT NULL,
`email_id` int(10) unsigned NOT NULL,
`min_resend_hours` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On slave the table is converted to InnoDB:

alter table MailerType engine=innodb;

On master:

SET autocommit=0;
insert into MailerType (active, en_US, email_id, min_resend_hours, id)
values (1, 'OA bombs19', 13, null, 1501580);
commit;
rollback;
SET autocommit=1;

Result on master:

mysql> select * from MailerType;
+---------+--------+------------+----------+------------------+
| id | active | en_US | email_id | min_resend_hours |
+---------+--------+------------+----------+------------------+
| 1501580 | 1 | OA bombs19 | 13 | NULL |
+---------+--------+------------+----------+------------------+
1 row in set (0.00 sec)

result on slave:

select * from MailerType;
Empty set (0.00 sec)

Thank you for the report.

Verified as described. Versions 5.0 and next-mr are not affected. One should have binlog-format=statement to repeat this.

Related: BUG#29288.

Could you please add the result of SHOW BINLOG EVENTS after executing the statements above?

Sorry don't have it handy - Sveta, since you repeated it, can you provide the binlog events to Mats?
Thanks

Btw Mats, to see the ref to the other bug that was fixed in a later version... if it's the same thing, fixing this is as simple as backporting that fix. It is a regression.

Arjen,

Yes, but the reason to why it works earlier is because we started to replicate the AUTOCOMMIT flag to fix some other bugs.

The binary log from a similar example is:

  mysqld.1> show binlog events from 106;
  +-------------------+-----+------------+-----------+-------------+-------------------------------------------+
  | Log_name          | Pos | Event_type | Server_id | End_log_pos | Info                                      |
  +-------------------+-----+------------+-----------+-------------+-------------------------------------------+
  | master-bin.000001 | 106 | Query      |         1 |         192 | use `test`; create table t1 (a int)       |
  | master-bin.000001 | 192 | Query      |         1 |         284 | use `test`; insert into t1 values (1),(2) |
  | master-bin.000001 | 284 | Query      |         1 |         376 | use `test`; insert into t1 values (3),(4) |
  +-------------------+-----+------------+-----------+-------------+-------------------------------------------+
  3 rows in set (0.00 sec)

  mysqld.1> select @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            0 |
  +--------------+
  1 row in set (0.00 sec)

Note that there is no BEGIN-COMMIT around the statements even though a commit was executed just after the statement.

SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver: 5.1.43-debug-log, Binlog ver: 4
master-bin.000001       106     Query   1       467     use `test`; CREATE TABLE `MailerType` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`active` tinyint(1) unsigned DEFAULT NULL,
`en_US` varchar(300) DEFAULT NULL,
`email_id` int(10) unsigned NOT NULL,
`min_resend_hours` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
master-bin.000001       467     Query   1       644     use `test`; insert into MailerType (active, en_US, email_id, min_resend_hours, id)
values (1, 'OA bombs19', 13, null, 1501580)

Test used:

$cat src/tests/t/rpl_bug49522.test 
--source include/master-slave.inc

CREATE TABLE `MailerType` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`active` tinyint(1) unsigned DEFAULT NULL,
`en_US` varchar(300) DEFAULT NULL,
`email_id` int(10) unsigned NOT NULL,
`min_resend_hours` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

sleep 1;
connection slave;
alter table MailerType engine=innodb;

connection master;
SET autocommit=0;
insert into MailerType (active, en_US, email_id, min_resend_hours, id)
values (1, 'OA bombs19', 13, null, 1501580);
commit;
rollback;
SET autocommit=1;
select * from MailerType;

SHOW BINLOG EVENTS;

sleep 1;
connection slave;
select * from MailerType;

$cat src/tests/t/rpl_bug49522-master.opt 
--binlog-format=statement

$cat src/tests/t/rpl_bug49522-slave.opt  
--innodb

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107778

3373 Luis Soares	2010-05-07 [merge]
      BUG#49522: Replication problem with mixed MyISAM/InnoDB
      
      When using a non-transactional table (t1) on the master 
      and with autocommit disabled, no COMMIT is recorded 
      in the binary log ending the statement. Therefore, if 
      the slave has t1 in a transactional engine, then it will 
      be as if a transaction is started but never ends. This is
      actually BUG#29288 all over again.
      
      We fix this by cherrypicking the cset for BUG#29288 which
      was pushed to a later mysql version. The revision picked
      was: mats@sun.com-20090923094343-bnheplq8n95opjay .
      
      Additionally, a test case for covering the scenario depicted
      in the bug report is included in this cset.

Queued in mysql-5.1-bugteam and mysql-pe:
  - http://pb2.norway.sun.com/web.py?template=push_details&push=1270678
  - http://pb2.norway.sun.com/web.py?template=push_details&push=1270677

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100524190409-5w4l7mje1wk1c90l) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100523204118-0tl3goawu658rxh6) (merge vers: 5.5.5-m3) (pib:16)

Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:luis.soares@sun.com-20100519234918-lwg5aqhatjuesp9l) (merge vers: 5.1.47) (pib:16)

Documented fix in the 5.1.48, 5.5.5, and 6.0.14 changelogs, as follows:

      When using a non-transactional table on the master with autocommit
      disabled, no COMMIT was recorded in the binary log following a 
      statement affecting this table. If the slave's copy of the table 
      used a transactional storage engine, the result on the slave was 
      as though a transaction had been started, but never completed.

      See also BUG#29288.

Closed.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

No new changelog entry required. Setting back to Closed state.