Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Test DDL statements before committing?

I am developing a PHP-based web application, in which I use the MySQLi driver. I make fairly standard use of this resource – I have a list of prepared statements, which I call at various points in my application, checks the result, and commits if successful (or rolls back if unsuccessful).

I have recently learned (through another post here) that it is impossible to "roll back" a DDL query. These auto-commit any pending transaction when executed. This is a problem for the section of my application where I essentially create the whole structure of my database (during setup) – I have a list of TABLEs, which I CREATE one after the other in quick succession (using mysqli::query rather than prepared statements, as I can’t pass a table’s name as a parameter).

My question, therefore, is this: is it possible to run a bunch of DDL queries (CREATE TABLE, CREATE PROCEDURE, etc.), without committing them? In other words, is it possible to check the results of these queries (on a dummy database perhaps?) without actually changing the live database? I would ideally like to be able to check that no errors have occurred during the execution of my DDL queries before actually changing the database at all.

I would have a significant problem if, half-way through my set-up, I figured out one of my DDL queries had an error – I wouldn’t be able to rollback and correct it, and my database would be half set-up (containing only half the required tables / procedures).

If anyone knows of a way of doing this (within the MySQLi object or otherwise), I would be immensely grateful. I don’t know whether there are any accepted practices regarding the execution of DDL queries, but it seems odd to me that there wouldn’t be a way to check the state of the database before actually committing these changes.

Tags:

2

Answers


  1. 0

    This cannot be done. It’s not a problem with PHP drivers or any language for that matter.

    It is a feature of the databases themselves. DDL are self-commit instructions.

    The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.

    Here is more about it in MySQL documentation: 15.3.3 Statements That Cause an Implicit Commit

    And here is somes examples about mysqli, transctions, and DDL statments: How to start and end transaction in mysqli?

    Login or Signup to reply.
  2. 0

    DDLs are statements that change the underlying structure of your schema. They cannot be rolled back or be a part of a transaction. It wouldn’t make any sense. If a DDL statement fails then your whole application is broken; there’s no way to recover from it. It is the job of the developer or a dedicated database administrator to ensure that the DDL query is correct and executes successfully.

    Think of your table schema as part of your application code. The schema must be precisely as the developer designed it and cannot be in any other state as all the other queries would suddenly stop working. For example, if you have a DDL ALTER TABLE table_name ADD column_name datatype; and then in your application you have SELECT column_name FROM table_name; then the select would fail if your DDL wasn’t successful. However, DDLs should generally not be executed as part of the daily operations of your application. Have a dedicated installation/upgrade/migration utility that is run only once.

    All of your database schema changes should be tested by the developer first, and then once again in a test environment. Only then do you know that they are 100% correct and will never fail. Having them wrapped in transactions at this point would be useless as they CAN NEVER FAIL. If they would be to fail then it would mean they are either incorrectly written (which should not happen if they are tested) or that the production schema is different than your test/development schema. Either way, it would mean a failure outside of the normal operations of your software.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search