Search code examples
mysqltransactionssql-insertcreate-table

MySQL does commit of transaction when fails with creation of table

Server version: 5.7.17 MySQL Community Server (GPL)

Here is my case. I have two tables.

CREATE TABLE a (id INT PRIMARY KEY);
CREATE TABLE b (id INT PRIMARY KEY);

Now, I doing my transaction:

START TRANSACTION;
INSERT INTO `a` VALUE (1);
CREATE TABLE b (id INT PRIMARY KEY);

Of course I expect that statement 3 (CRATE TABLE 'b') will fail since table already exists. And it does. The problem, that it does also insert as well, which should not before I run 

COMMIT;

There is no way to rollback transaction - record '1' in table 'a' stays in place.

Do you have any ideas or thoughts how to avoid such behaviour? I've also noticed that when I intentionally do syntax error in CREATE TABLE STATEMENT it fails like expected, but record in 'a' isn't created.

Solution

  • There are a number of statements that cause an implicit commit. CREATE TABLE is one of them.

    More info: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

    Maybe How to prevent mysql implicit commit can help a bit.