CODEWITHSUNDEEP
javamysqljdbcrollback
escudero380
escudero380

Reputation: 586

How to roll back DDL statements such CREATE TABLE and DROP TABLE while using JDBC driver with MySQL DBMS?

I have a sample code, which creates a table, inserts some rows in it and then tries to cancel the whole transaction, but conn.rollback() seems to have effect only on INSERT statements while CREATE TABLE keeps unaffected: a newly created table remains permanent in the database with no rows inserted in it.

Is this a standard behavior while using JDBC driver with MySQL DBMS?

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestBatch {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC",
                "root", "root")) {

            // drop table
            try (Statement dropStatement = conn.createStatement()) {
                dropStatement.executeUpdate("DROP TABLE IF EXISTS mytable");
            }

            // create table, insert rows and rollback
            conn.setAutoCommit(false);
            try (Statement stmt = conn.createStatement()) {
                stmt.addBatch("CREATE TABLE mytable (id INT)");
                stmt.addBatch("INSERT INTO mytable VALUES (1)");
                stmt.addBatch("INSERT INTO mytable VALUES (2)");
                stmt.executeBatch();
                conn.rollback();
            } finally {
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }

    }
}

Please, note, I'm using MySQL Server 8.0 with Connector/J ver. 8.0.17, also the parameter default-storage-engine (in C:\ProgramData\MySQL\MySQL Server 8.0\my.ini) is set to INNODB.

Upvotes: 4

Views: 2048

Answers (1)

Brian
Brian

Reputation: 902

As Paul Spiegel and Arnaud said, DDL statements cannot be rolled back in MySQL. This is true even for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

For better or worse, DDL statements commit any pending transactions, if they are local and cannot be used in XA transactions (because that would cause one participant to commit data without the transaction coordinator issuing the command to do so).

Upvotes: 7

Related Questions