MySQL Bugs: #59097: Fix of bug #29288 does not work in 5.1

Bug #59097	Fix of bug #29288 does not work in 5.1
Submitted:	21 Dec 2010 19:59	Modified:	31 Jan 2011 17:19
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.47, 5.0, 5.1	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	begin, commit, replication

Description:
To fix a bug, auto-commit statements were wrapped with begin/commit events in the binlog. I forget the bug number for this. However, I don't understand why this isn't always done.

Should it always be done?

When a blackhole-only or myisam-only transaction follows an innodb-only transaction, then a query event with "BEGIN" is not used for it.

When a blackhole-only or myisam-only transaction follows a blackhole-only or myisam-only transaction, then a query event with "BEGIN" is used.

How to repeat:
create table ti(i int)engine=innodb;
create table tm(i int)engine=myisam;
create table tb(i int)engine=blackhole;

show binlog events;
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                               |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| dev347-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.47-log, Binlog ver: 4              |
| dev347-bin.000001 | 106 | Query       |         1 |         204 | use `test`; create table ti(i int)engine=innodb    |
| dev347-bin.000001 | 204 | Query       |         1 |         302 | use `test`; create table tm(i int)engine=myisam    |
| dev347-bin.000001 | 302 | Query       |         1 |         403 | use `test`; create table tb(i int)engine=blackhole |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+

# first example, the first transaction that follows the innodb-only commit does not get a query
# event with "BEGIN"

insert into ti values (1);
insert into tm values (1);
insert into tb values (1);

show binlog events;
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                               |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| dev347-bin.000001 | 403 | Query       |         1 |         471 | BEGIN                                              |
| dev347-bin.000001 | 471 | Query       |         1 |         559 | use `test`; insert into ti values (1)              |
| dev347-bin.000001 | 559 | Xid         |         1 |         586 | COMMIT /* xid=7 */                                 |
| dev347-bin.000001 | 586 | Query       |         1 |         674 | use `test`; insert into tm values (1)              |
| dev347-bin.000001 | 674 | Query       |         1 |         742 | BEGIN                                              |
| dev347-bin.000001 | 742 | Query       |         1 |         830 | use `test`; insert into tb values (1)              |
| dev347-bin.000001 | 830 | Query       |         1 |         899 | COMMIT                                             |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------+

# and the result is the same when I don't use auto-commit

begin; insert into ti values (1); commit;
begin; insert into tm values (1); commit;
begin; insert into tb values (1); commit;

show binlog events;
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name          | Pos  | Event_type  | Server_id | End_log_pos | Info                                               |
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| dev347-bin.000001 |  899 | Query       |         1 |         967 | BEGIN                                              |
| dev347-bin.000001 |  967 | Query       |         1 |        1055 | use `test`; insert into ti values (1)              |
| dev347-bin.000001 | 1055 | Xid         |         1 |        1082 | COMMIT /* xid=12 */                                |
| dev347-bin.000001 | 1082 | Query       |         1 |        1170 | use `test`; insert into tm values (1)              |
| dev347-bin.000001 | 1170 | Query       |         1 |        1238 | BEGIN                                              |
| dev347-bin.000001 | 1238 | Query       |         1 |        1326 | use `test`; insert into tb values (1)              |
| dev347-bin.000001 | 1326 | Query       |         1 |        1395 | COMMIT                                             |
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+

# but the query event with "BEGIN" is not missing for a sequence of innodb-only transactions

begin; insert into ti values (1); commit; begin; insert into ti values (2); commit; begin; insert into ti values (3); commit;
show binlog events;
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name          | Pos  | Event_type  | Server_id | End_log_pos | Info                                               |
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| dev347-bin.000001 | 1170 | Query       |         1 |        1238 | BEGIN                                              |
| dev347-bin.000001 | 1238 | Query       |         1 |        1326 | use `test`; insert into tb values (1)              |
| dev347-bin.000001 | 1326 | Query       |         1 |        1395 | COMMIT                                             |
| dev347-bin.000001 | 1395 | Query       |         1 |        1463 | BEGIN                                              |
| dev347-bin.000001 | 1463 | Query       |         1 |        1551 | use `test`; insert into ti values (1)              |
| dev347-bin.000001 | 1551 | Xid         |         1 |        1578 | COMMIT /* xid=22 */                                |
| dev347-bin.000001 | 1578 | Query       |         1 |        1646 | BEGIN                                              |
| dev347-bin.000001 | 1646 | Query       |         1 |        1734 | use `test`; insert into ti values (2)              |
| dev347-bin.000001 | 1734 | Xid         |         1 |        1761 | COMMIT /* xid=25 */                                |
| dev347-bin.000001 | 1761 | Query       |         1 |        1829 | BEGIN                                              |
| dev347-bin.000001 | 1829 | Query       |         1 |        1917 | use `test`; insert into ti values (3)              |
| dev347-bin.000001 | 1917 | Xid         |         1 |        1944 | COMMIT /* xid=28 */                                |
+-------------------+------+-------------+-----------+-------------+----------------------------------------------------+

Looks like bug #26395.

One of the commit messages for that bug is this, which is incorrect given my example:
>>>
  Now, every transaction (including autocommit transactions) starts with
  a BEGIN and ends with a COMMIT/ROLLBACK in the binlog.
  Added a test case, and updated lots of test case result files.
>>>

Thank you for the report.

Verified as described.

This is actually duplicate of bug #29288 which should be fixed in 5.1.48, but actually is not:

=====mysql-5.1=====
=====bug59097=====
create table ti(i int)engine=innodb;
create table tm(i int)engine=myisam;
create table tb(i int)engine=blackhole;
insert into ti values (1);
insert into tm values (1);
insert into tb values (1);
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.55-debug-log, Binlog ver: 4
master-bin.000001       106     Query   1       204     use `test`; create table ti(i int)engine=innodb
master-bin.000001       204     Query   1       302     use `test`; create table tm(i int)engine=myisam
master-bin.000001       302     Query   1       403     use `test`; create table tb(i int)engine=blackhole
master-bin.000001       403     Query   1       471     BEGIN
master-bin.000001       471     Query   1       559     use `test`; insert into ti values (1)
master-bin.000001       559     Xid     1       586     COMMIT /* xid=14 */
master-bin.000001       586     Query   1       674     use `test`; insert into tm values (1)
master-bin.000001       674     Query   1       742     BEGIN
master-bin.000001       742     Query   1       830     use `test`; insert into tb values (1)
master-bin.000001       830     Query   1       899     COMMIT

Therefore I change synopsis from "Query_event with "BEGIN" missing after XID event" to "Fix of bug #29288 does not work in 5.1"

Sveta explained that this is only an issue for MyISAM in 5.1. It is not an issue for Blackhole in 5.1. And it is not an issue in 5.5.

Sveta, Mark,

Actually bug #29288 fixes were pushed to 5.1 through BUG#49522 6.0->5.1 backporting. But those did not attempt to wrap up non-transactional autocommit
queries with BEGIN/COMMIT. For the record, the wrapping for the transactional
autocommit:s was done by BUG#26395 fixes.
And finally `wl#2687 Write non-transactional binlog events
directly to binary log' extended wrapping to all engines which we observe in 5.5.

Given that backporting of wl#2687 is rather risky and pretty involving procedure
and after consulting with Luis I would rather to set Won't fix status.
Deferring with that until Sveta (please) has verified if docs for 5.1 reflected
the facts mentioned. If not, the status should go to Documenting to add missed pieces.

Thanks!

Andrei.

I've read over the comments several times, but I still don't understand what the docs issue is.

Can you please sum up the problem in 1-3 sentences for me?

Thanks!

This is a Docs rather than Server issue. Updated category/status/lead/assignee to match, set prio and target versions.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.