MySQL Bugs: #83425: Unsuccessful statement commits

Bug #83425	Unsuccessful statement commits
Submitted:	18 Oct 2016 12:54	Modified:	18 Oct 2016 14:21
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.7.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It is wellknown that (most) DDL statements will cause an implict commit.

But commit still happens in some cases if statement is not executed.

How to repeat:
-- session 1
CREATE TABLE tr (id INT);
START TRANSACTION;
INSERT INTO tr VALUES (1);
ALTER TABLE trr CHANGE `id` `id` BIGINT NULL;
-- Error Code: 1146
-- Table 'pltest.trr' doesn't exist
SHOW CREATE TABLE tr;
/* CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

-- session 2
SELECT * FROM tr;
/*
    id  
--------
       1
*/

Suggested fix:
Is this really intentional And is it documented? I dont think so.

Updated version.

In addition to the mentioned case some other cases are:

CREATE TABLE `a1` (
 `id` int(11) NOT NULL,
 `id2` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `id2` (`id2`),
 CONSTRAINT `a1_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `a1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Case-1:
ALTER TABLE `a`.`a1`  
 ADD FOREIGN KEY (`id`) REFERENCES `a`.`a1`(`id2`);

Case-2: (‘FIXED’ value is not supported for ROW_FORMAT in InnoDB)
ALTER TABLE `a`.`a1` ROW_FORMAT=FIXED;

Case-3:(If an Index is required/used in a Foreign Key constraint is dropped)
ALTER TABLE `a`.`a1` DROP INDEX `id2`;

All the above cases are causing transaction to end even if those queries result in an error.

Netik is my colleague.

Please consider all cases reported by him and me.

Hi!

Our manual has this fully covered. Read especially the chapter 13.3.3. It explains in a detail when the implicit commit occurs. Do note the following words in the chapter:

"as if you had done a COMMIT before executing the statement."

BEFORE executing the statement. Hence, implicit commit happens at the start of the DDL statement. In some cases, explained in 13.3.3, it happens both at the start and at the finish of the DDL statement.

Changing this behavior is a very serious and large project. It would be only doable in 8.0. But, plans are that DDLs could be rolled back in 8.0, anyway. Hence, this is not a bug.

"Changing this behavior is a very serious and large project.". That was the reply I expected unfortunately. I am not totally unaware of limitations of MySQL code.

But as "It would be doable in 8.0" can we at least have it verified as a feature request for future versions? Ie. even if MySQL 8 makes it possible to rollback DDL there may still be introduced some backards compability option that should then not apply to unsuccesfull statements.

Peter,

Yes, why not !!! 

You are a very appreciated contributor and I am happy to oblige.

This is a feature request to make DDL's transactional in MySQL 8.0,