MySQL Bugs: #40200: No rollback on error for transactions defined in a script

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

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

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?

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.

Verified just as described. Script in Script tab should ROLLBACK transaction in case of error, as mysql does when script comes from stdin.

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)?

A checkbox simulating the --force option that we can setup in the mysqlclient could be a rock feature request.

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