Description:
If you modify both innodb and myiasm tables within same transaction and roll backed it finaly, of cause the roll back will be failed because myiasm tables do not support transaction and get an error of
ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back
As my test, the modification to both innodb & myiasm tables have been COMMITED at this point, how ever, the worst thing is all sql modifing innodb tables WON'T be written to binlog!!, replication slave will miss these statements and cause data integrity fail.
How to repeat:
use test;
mysql> create table my_table(n int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table inn_table(n int) type=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert inn_table values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert my_table values(1);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back
mysql> select * from inn_table;
+------+
| n |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from my_table;
+------+
| n |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
mysqlbinlog --database test mysql-bin.036 will output like this:
# at 6132814
#050414 15:00:46 server id 1 log_pos 6132814 Query thread_id=2985 exec_time=0 error_code=0
SET TIMESTAMP=1113462046;
create table my_table(n int);
# at 6132877
#050414 15:00:59 server id 1 log_pos 6132877 Query thread_id=2985 exec_time=0 error_code=0
SET TIMESTAMP=1113462059;
create table inn_table(n int) type=innodb;
# at 6132953
#050414 15:01:44 server id 1 log_pos 6132953 Query thread_id=2985 exec_time=0 error_code=0
SET TIMESTAMP=1113462104;
insert my_table values(1);
***You see, no "insert inn_table value(1)" in the binlog***
