CODEWITHSUNDEEP
mysqltransactions
Yary
Yary

Reputation: 335

How to prevent implicit transactions in a MySQL session?

I'm writing tests against a large app with a MySQL database. The language makes it easy to hook into "Commit" and "Rollback"; those with SAVEPOINT tricks allows my tests to temporarily change the state of the database, and roll them all back at exit.

But this is a big project with many contributors, and it's possible that there'll be a DDL statement mixed in there. Any statement that implicitly commits breaks the testing contract of returning the database to its original state. Is there a way to force an error on any attempt to execute an implicitly-committing command?

Cosiderations:

  1. The read_only variable looks promising, but is too restrictive. I want to allow insert/update/delete statements. Is there a similar but "lighter" variable that can disable DDL?
  2. Does MySQL have any "hook" or "callback" mechanism to intercept calls, either all calls in a session, or certain commands only? If so then I could write a procedure that simply throws an exception on any implicitly-committing command.
  3. I do have some workarounds, which I'll put in an answer. If anyone has a better or simply more interesting answer, will accept that.

Upvotes: 1

Views: 1044

Answers (1)

Yary
Yary

Reputation: 335

Here are two workarounds, neither of which completely satisfies me.

  1. REVOKE CREATE, ALTER, DROP, LOCK TABLES on MyDB.* FROM 'testuser'; - followed by a GRANT to under the privilege change when the test is over. This one looks great, but it requires a DB user with GRANT & REVOKE privs. The app's database user does not have them, and rightfully so. Ideally, the developers running these tests should not need to do any extra admin work, like getting extra privs or creating a new DB user.

  2. Instead of using normal transactions, use the two-phase commit transactions. This is awesome! It doesn't need any special privileges, it prevents any implicit commits without my needing to list or even know which statements cause those commits, and it's a change which "goes away" by itself. But... it interferes with the SAVEPOINTS that let me run a few transactions, roll back or commit partway, and continue. It makes it difficult to write a test that changes a few things & lets the check test and use those changes later in the test.

mysql> XA START 0x123; -- Query OK, 0 rows affected (0.00 sec)

mysql> insert into temp_yh_test values(0,'popbus'); -- Query OK, 1 row affected (0.00 sec)

mysql> create table temp_new_1 (id int NOT NULL AUTO_INCREMENT, xyz varchar(40), primary key (id)) ENGINE=InnoDB; -- ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state

Upvotes: 2

Related Questions