2 minor edits, plus

fix for BUG#1113 "INSERT into non-trans table SELECT ; ROLLBACK" does not send warning"
and
fix for BUG#873 "In transaction, INSERT to non-trans table is written too early to binlog".
Now we don't always write the non-trans update immediately to the binlog;
if there is something in the binlog cache we write it to the binlog cache
(because the non-trans update could depend on a trans table which was modified
earlier in the transaction); then in case of ROLLBACK, we write the binlog
cache to the binlog, wrapped with BEGIN/ROLLBACK.
This guarantees that the slave does the same updates.
For ROLLBACK TO SAVEPOINT: when we execute a SAVEPOINT command we write it
to the binlog cache. At ROLLBACK TO SAVEPOINT, if some non-trans table was updated,
we write ROLLBACK TO SAVEPOINT to the binlog cache; when the transaction
terminates (COMMIT/ROLLBACK), the binlog cache will be flushed to the binlog
(because of the non-trans update) so we'll have SAVEPOINT and ROLLBACK TO
SAVEPOINT in the binlog.

Apart from this rare case of updates of mixed table types in transaction, the
usual way is still clear the binlog cache at ROLLBACK, or chop it at
ROLLBACK TO SAVEPOINT (meaning the SAVEPOINT command is also chopped, which
is fine).
Note that BUG#873 encompasses subbugs 1) and 2) of BUG#333 "3 binlogging bugs when doing INSERT with mixed InnoDB/MyISAM".

4 files changed, 364 insertions, 11 deletions

diff --git a/mysql-test/r/mix_innodb_myisam_binlog.result b/mysql-test/r/mix_innodb_myisam_binlog.result
new file mode 100644
index 00000000000..8a3415a81d0
--- /dev/null
+++ b/mysql-test/r/mix_innodb_myisam_binlog.result
@@ -0,0 +1,180 @@
+drop table if exists ti, tm;
+create table ti (a int) type=innodb;
+create table tm (a int) type=myisam;
+reset master;
+begin;
+insert into ti values(1);
+insert into tm select * from ti;
+commit;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(1)
+master-bin.001	178	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	244	Query	1	244	use test; COMMIT
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(2);
+insert into tm select * from ti;
+rollback;
+Warning:  Some non-transactional changed tables couldn't be rolled back
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(2)
+master-bin.001	178	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	244	Query	1	244	use test; ROLLBACK
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(3);
+savepoint my_savepoint;
+insert into ti values(4);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+Warning:  Some non-transactional changed tables couldn't be rolled back
+commit;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(3)
+master-bin.001	178	Query	1	79	use test; savepoint my_savepoint
+master-bin.001	235	Query	1	79	use test; insert into ti values(4)
+master-bin.001	294	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	360	Query	1	79	use test; rollback to savepoint my_savepoint
+master-bin.001	429	Query	1	429	use test; COMMIT
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(5);
+savepoint my_savepoint;
+insert into ti values(6);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+Warning:  Some non-transactional changed tables couldn't be rolled back
+insert into ti values(7);
+commit;
+select a from ti order by a;
+a
+5
+7
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(5)
+master-bin.001	178	Query	1	79	use test; savepoint my_savepoint
+master-bin.001	235	Query	1	79	use test; insert into ti values(6)
+master-bin.001	294	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	360	Query	1	79	use test; rollback to savepoint my_savepoint
+master-bin.001	429	Query	1	79	use test; insert into ti values(7)
+master-bin.001	488	Query	1	488	use test; COMMIT
+delete from ti;
+delete from tm;
+reset master;
+select get_lock("a",10);
+get_lock("a",10)
+1
+begin;
+insert into ti values(8);
+insert into tm select * from ti;
+select get_lock("a",10);
+get_lock("a",10)
+1
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(8)
+master-bin.001	178	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	244	Query	1	244	use test; ROLLBACK
+delete from ti;
+delete from tm;
+reset master;
+insert into ti values(9);
+insert into tm select * from ti;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; insert into ti values(9)
+master-bin.001	138	Query	1	138	use test; insert into tm select * from ti
+delete from ti;
+delete from tm;
+reset master;
+insert into ti values(10);
+begin;
+insert into tm select * from ti;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; insert into ti values(10)
+master-bin.001	139	Query	1	139	use test; insert into tm select * from ti
+insert into ti values(11);
+commit;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; insert into ti values(10)
+master-bin.001	139	Query	1	139	use test; insert into tm select * from ti
+master-bin.001	205	Query	1	205	use test; BEGIN
+master-bin.001	245	Query	1	205	use test; insert into ti values(11)
+master-bin.001	305	Query	1	305	use test; COMMIT
+alter table tm type=INNODB;
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(12);
+insert into tm select * from ti;
+commit;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(12)
+master-bin.001	179	Query	1	79	use test; insert into tm select * from ti
+master-bin.001	245	Query	1	245	use test; COMMIT
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(13);
+insert into tm select * from ti;
+rollback;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(14);
+savepoint my_savepoint;
+insert into ti values(15);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+commit;
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(14)
+master-bin.001	179	Query	1	179	use test; COMMIT
+delete from ti;
+delete from tm;
+reset master;
+begin;
+insert into ti values(16);
+savepoint my_savepoint;
+insert into ti values(17);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+insert into ti values(18);
+commit;
+select a from ti order by a;
+a
+16
+18
+show binlog events from 79;
+Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
+master-bin.001	79	Query	1	79	use test; BEGIN
+master-bin.001	119	Query	1	79	use test; insert into ti values(16)
+master-bin.001	179	Query	1	79	use test; insert into ti values(18)
+master-bin.001	239	Query	1	239	use test; COMMIT
+drop table ti,tm;
diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result
index e93f8487c32..0302381c119 100644
--- a/mysql-test/r/rpl_loaddata.result
+++ b/mysql-test/r/rpl_loaddata.result
@@ -20,9 +20,9 @@ day	id	category	name
 2003-02-22	2461	b	a a a @ %  ' " a
 2003-03-22	2161	c	asdf
 2003-04-22	2416	a	bbbbb
-show binlog events from 898;
-Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-slave-bin.001	898	Query	1	898	use test; insert into t3 select * from t2
+show master status;
+File	Position	Binlog_do_db	Binlog_ignore_db
+slave-bin.001	964		
 drop table t1;
 drop table t2;
 drop table t3;
diff --git a/mysql-test/t/mix_innodb_myisam_binlog.test b/mysql-test/t/mix_innodb_myisam_binlog.test
new file mode 100644
index 00000000000..240aaefb349
--- /dev/null
+++ b/mysql-test/t/mix_innodb_myisam_binlog.test
@@ -0,0 +1,175 @@
+# Check that binlog is ok when a transaction mixes updates to InnoDB and
+# MyISAM. It would be nice to make this a replication test, but in 4.0 the slave
+# is always with --skip-innodb in the testsuite. I (Guilhem) however did some
+# tests manually on a slave; tables are replicated fine and Exec_master_log_pos
+# advances as expected.
+
+-- source include/have_innodb.inc
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+
+connection con1;
+
+drop table if exists ti, tm;
+create table ti (a int) type=innodb;
+create table tm (a int) type=myisam;
+
+reset master;
+
+begin;
+insert into ti values(1);
+insert into tm select * from ti;
+commit;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(2);
+insert into tm select * from ti;
+# should say some changes to non-transactional tables couldn't be rolled back
+--error 1196
+rollback;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(3);
+savepoint my_savepoint;
+insert into ti values(4);
+insert into tm select * from ti;
+--error 1196
+rollback to savepoint my_savepoint;
+commit;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(5);
+savepoint my_savepoint;
+insert into ti values(6);
+insert into tm select * from ti;
+--error 1196
+rollback to savepoint my_savepoint;
+insert into ti values(7);
+commit;
+select a from ti order by a; # check that savepoints work :)
+
+show binlog events from 79;
+
+# and when ROLLBACK is not explicit?
+delete from ti;
+delete from tm;
+reset master;
+
+select get_lock("a",10);
+begin;
+insert into ti values(8);
+insert into tm select * from ti;
+disconnect con1;
+
+connection con2;
+# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
+# guarantee that logging of the terminated con1 has been done yet (it may not
+# even be started, so con1 may have not even attempted to lock the binlog yet;
+# so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that
+# logging has been done, we use a user lock.
+select get_lock("a",10);
+show binlog events from 79;
+
+# and when not in a transaction?
+delete from ti;
+delete from tm;
+reset master;
+
+insert into ti values(9);
+insert into tm select * from ti;
+
+show binlog events from 79;
+
+# Check that when the query updating the MyISAM table is the first in the
+# transaction, we log it immediately.
+delete from ti;
+delete from tm;
+reset master;
+
+insert into ti values(10); # first make ti non-empty
+begin;
+insert into tm select * from ti;
+show binlog events from 79;
+insert into ti values(11);
+commit;
+
+show binlog events from 79;
+
+
+# Check that things work like before this BEGIN/ROLLBACK code was added, when tm
+# is INNODB 
+
+alter table tm type=INNODB;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(12);
+insert into tm select * from ti;
+commit;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(13);
+insert into tm select * from ti;
+rollback;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(14);
+savepoint my_savepoint;
+insert into ti values(15);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+commit;
+
+show binlog events from 79;
+
+delete from ti;
+delete from tm;
+reset master;
+
+begin;
+insert into ti values(16);
+savepoint my_savepoint;
+insert into ti values(17);
+insert into tm select * from ti;
+rollback to savepoint my_savepoint;
+insert into ti values(18);
+commit;
+select a from ti order by a; # check that savepoints work :)
+
+show binlog events from 79;
+
+drop table ti,tm;
diff --git a/mysql-test/t/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test
index 65e07aaa823..4c4ff6a093e 100644
--- a/mysql-test/t/rpl_loaddata.test
+++ b/mysql-test/t/rpl_loaddata.test
@@ -32,15 +32,13 @@ sync_with_master;
 select * from t1;
 select * from t3;
 # We want to be sure that LOAD DATA is in the slave's binlog.
-# But we can't simply read this binlog, because the file_id is uncertain (would
-# cause test failures). So instead, we test if the binlog looks long enough to
+# But we can't simply read this binlog, because as the slave has not been
+# restarted for this test, the file_id is uncertain (would cause test
+# failures). So instead, we test if the binlog looks long enough to
 # contain LOAD DATA. That is, I (Guilhem) have done SHOW BINLOG EVENTS on my
-# machine, saw that the last event is 'create table t3' and is at position 898
-# when things go fine. If LOAD DATA was not logged, the binlog would be shorter
-# than 898 bytes and there would be an error in SHOW BINLOG EVENTS. Of course,
-# if someone changes the content of '../../std_data/rpl_loaddata2.dat', 898 will
-# have to be changed too.
-show binlog events from 898;
+# machine, saw that the binlog is of size 964 when things go fine.
+# If LOAD DATA was not logged, the binlog would be shorter.
+show master status;
 
 connection master;