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
Triage: Triaged: D4 (Minor)

[25 Aug 2006 18:11] Marc Alff
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
[25 Aug 2006 18:50] Miguel Solorzano
Thank you for the bug report.
[15 Mar 2007 15:39] Konstantin Osipov
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)
[1 Oct 2008 22:46] Konstantin Osipov
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.
[6 Oct 2008 10:47] Magne Mæhre
Test case is added