Bug #40200 No rollback on error for transactions defined in a script
Submitted: 21 Oct 2008 10:12 Modified: 23 Oct 2008 7:58
Reporter: Stephane Varoqui Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.2.13 OS:Any
Assigned to: Mike Lischke CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[21 Oct 2008 10:12] Stephane Varoqui
Description:
Hi ,

Query Browser does not assume atomicity of transactional scripting .  

The MySQL-server handle auto rollback at various level (transaction or statement) as describe in the documentation : 
http://dev.mysql.com/doc/refman/5.0/en/innodb-error-handling.html

It stay so API specific to implement a proper atomic scripting. The Query Browser should be able to provide the feature to preserve data integrity from bad SQL syntax already glued into transactions.

Rds
Stephane.

How to repeat:
Hi in the Query Browser, 

DROP TABLE IF EXISTS `KEWEGO`.`CUSTOMER`;
CREATE TABLE  `KEWEGO`.`CUSTOMER` (
  `customer_id` int(11) NOT NULL auto_increment,
  `creation_date` datetime NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `deleted` enum('yes','no') NOT NULL default 'no',
  `business_unit` enum('Fr','De','Es','Uk','None') NOT NULL default 'None',
  PRIMARY KEY  (`customer_id`),
  UNIQUE KEY `name_idx` (`customer_name`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

USE KEWEGO;
BEGIN;

INSERT INTO CUSTOMER VALUES(9997, NOW(), 'test transaction 1 - good', 'yes', 'None');
INSERT INTO CUSTOMER VALUES(9998, NOW(), 'test transaction 2 - bad', 'yes');
INSERT INTO CUSTOMER VALUES(9999, NOW(), 'test transaction 3 - good', 'yes', 'None');

COMMIT;

found in the table :

'test transaction 1 - good'
'test transaction 3 - good' 

Suggested fix:
Run a ROLLBACK and stop the script execution on the statement after the transaction
[21 Oct 2008 10:19] Valeriy Kravchuk
Thank you for a problem report. 

According to my test mysql command line client demonstrates the same behaviour. It is up to script writer to explixitely ROLLBACK in case of error in script. So, this is a feature request, at best. Do you agree?
[21 Oct 2008 11:01] Stephane Varoqui
mysql client is an interactive process and can let the user ROLLBACK anytime according to the result of each statement.

but : 

mysql -uroot -p test < trans.sql   

behave like a charm and will rollback the all transaction.
[21 Oct 2008 13:30] Valeriy Kravchuk
Verified just as described. Script in Script tab should ROLLBACK transaction in case of error, as mysql does when script comes from stdin.
[21 Oct 2008 13:36] Mike Lischke
QB, in opposition to the CLI, does not stop executing the script if an error occurs. It just logs the error. Hence it reaches the commit command even though there was a problem. The question is, should we not do this and instead explicitely roll back the current transaction (if there is one)?
[21 Oct 2008 13:54] Stephane Varoqui
A checkbox simulating the --force option that we can setup in the mysqlclient could be a rock feature request.
[23 Oct 2008 7:58] Mike Lischke
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html