MySQL Bugs: #60466: transaction is not persistent if failed

Bug #60466	transaction is not persistent if failed
Submitted:	14 Mar 2011 21:54	Modified:	21 Apr 2011 19:42
Reporter:	Tianshi Wang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.55	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	stored procedure, transaction failure, transactions

Description:
In the setup stored procedure, there are three insertions in a transaction. The second insertion should yield an error. If we run this stored procedure twice in different sessions, The result table is somewhat not expected. 

How to repeat:
version: 5.1.55
tx_isolation: repeatable-read
-----------------------
create table t_bug (
id int auto_increment primary key,
bugs varchar(20)
)ENGINE=innodb;

drop procedure if exists transactiontest;
delimiter $$
create procedure transactiontest()
BEGIN
START TRANSACTION;
insert into t_bug (bugs) values ('bug found1');
insert into t_bug (id, bugs) values (1, 'dup error');
insert into t_bug (bugs) values ('bug found2');
COMMIT;
END $$
delimiter ;

I use a php script to call this procedure twice in two different sessions,

<?php
$dbh = new PDO("mysql:host=localhost;dbname=mydb", 'user', 'pass');
$sql="call transactiontest()"; //should not inserted
$dbh->query($sql);
?>

The result set:

mysql> select * from t_bug;
+----+------------+
| id | bugs       |
+----+------------+
|  1 | dup error  |
|  2 | bug found1 |
|  3 | bug found2 |
+----+------------+
3 rows in set (0.00 sec)

Suggested fix:
The two procedure calls should be independent from each other. So the result table should be empty.

Please, send the output of:

select @@autocommit;

from your environment.

Ignore my previous question. This is what I see:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t_bug (
    -> id int auto_increment primary key,
    -> bugs varchar(20)
    -> )ENGINE=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter $$
mysql> create procedure transactiontest()
    -> BEGIN
    -> START TRANSACTION;
    -> insert into t_bug (bugs) values ('bug found1');
    -> insert into t_bug (id, bugs) values (1, 'dup error');
    -> insert into t_bug (bugs) values ('bug found2');
    -> COMMIT;
    -> END $$
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;
mysql> call transactiontest();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t_bug;
+----+------------+
| id | bugs       |
+----+------------+
|  1 | bug found1 |
+----+------------+
1 row in set (0.00 sec)

This transaction is not completed, so it can see the row already inserted, but no other transaction can. If I call this procedure from other session it hangs waiting for the lock until the first one is committed or rolled back, or until timeout exceeded:

mysql> call transactiontest();
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Then if you rollback transaction in the first session (as you should if you consider error message a good reason to decide that it failed), table will be empty.

Probably we miss some details in what you really did in your case. Maybe your table was NOT created as InnoDB one. Please, check.

Please note that when I call the procedure, I did not use mysql command line. I was using the following PHP script to call the procedure. Basically I run this script twice.

<?php
$dbh = new PDO("mysql:host=localhost;dbname=mydb", 'use', 'pass');
$sql="call transactiontest()"; //should not be inserted
$dbh->query($sql);
$dbh=null;
?>

Why do you think that calling environment matters? If it does, then we probably have a bug in PHP component used, NOT MySQL server bug. I've shown you that server works as expected when mysql command line client is used.

The only difference calling environment can make, I think, is autocommit setting, and I've asked you about it.

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

FIrst of all, I am sorry I did not make it clear. MySQL cmd line works great for me just like yours. And I agree that it should not be MySQL server bug as long as MySQL command line works. The only reason I post there was because I have tried three APIs, PHP (mysqli, pdo) and perl(DBI), and they all gives the same result. Please close this bug if it is not on your side. Would it be a general connection API issues?

Thank you for the feedback.

Which transaction isolation level do you use? Please check this with PDO application to be sure it uses same transaction isolation level as cli.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".