MySQL Bugs: #21840: Spurious ER_WARNING_NOT_COMPLETE

Bug #21840	Spurious ER_WARNING_NOT_COMPLETE_ROLLBACK for transactional tables
Submitted:	25 Aug 2006 18:11	Modified:	6 Oct 2008 10:48
Reporter:	Marc ALFF	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0/5.1BK	OS:	Linux (Linux)
Assigned to:	Magne Mæhre	CPU Architecture:	Any

Description:
The following script cause the warning :

rollback;
Warnings:
Warning 1196    Some non-transactional changed tables couldn't be rolled back

to be raised even if *no* non-transactional table is involved.

Reproduced with BDB, InnoDB and NDB.

let $engine_type = BerkeleyDB;

set autocommit=1;

--disable_warnings
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop function if exists f2;
--enable_warnings

eval create table t1 (a int) engine = $engine_type;
eval create table t2 (a int unique) engine = $engine_type;
eval create table t3 (a int) engine = $engine_type;

insert into t1 (a) values (1), (2);
insert into t3 (a) values (1), (2);

delimiter //;

## Cause a failure every time
create function f2(x int) returns int
begin
  insert into t2 (a) values (x);
  insert into t2 (a) values (x);
  return x;
end//

delimiter ;//

set autocommit=0;

insert into t2 (a) values (1004);
--error ER_DUP_ENTRY
update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);
select * from t2;

## This rollback complain with a warning about non transactional data,
## which is incorrect.
rollback;
select * from t2;
commit;

set autocommit=1;

drop table t1;
drop table t2;
drop table t3;
drop function f2;

How to repeat:
See above

Thank you for the bug report.

Another test case:
set autocommit=0;
create temporary table t1 (a int) engine=innodb;
insert into t1 (a) values (1);
rollback;
show warnings;

Output:

mysql> create temporary table t1 (a int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 (a) values (1); 
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
'Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                      
|
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables  
couldn't be rolled back | 
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Can't repeat in 6.0. 

Magne, could you please add a test case (the small one, by me) to innodb_mysql.test and close the bug?

Thanks.

Test case is added