MySQL/PHP PDO – Using Simulate or Dry Run Option

developmentmysql-5.5transaction

Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually committing any INSERT, DELETE or UPDATE statements?)

'Inserted' data (people IDs, business IDs, phone number Ids, phone type IDs, etc.) should be stored somewhere, so that they can be retrieved if need be. Data shouldn't be written to the main database during testing/development, only when the script is complete and ready for production use.

The reason for asking is this: In developing a data-copying/transferring script, it's inconvenient to delete the test data out of the tables and reset AUTO_INCREMENT before running the changed import script.

Alternately, should I …

  1. Create my tables in memory at the start of the script and delete them at the end?
  2. Replicate the database (create a slave) for use in development and replace the slave with each modification to the script?

(There will be about 1000-2000 people imported, with up to 15 phone numbers for each person, 1 business for each person and up to 10 roles for each person.)

Best Answer

The best way to do SQL syntax checking without actually writing data is using the engine BLACKHOLE, which is the MySQL equivalent to UNIX /dev/null. A its name suggests, it is an engine that accepts all (but invalid syntax) but stores no data. Great for testing, and incredible inserting performance :-)

Aside from that, people test setups by creating slaves with live data on a separate instance.

Be careful with very dynamic data on InnoDB, and large uncommitted transactions, as that could create a lot of unused gaps on production depending on the configuration (innodb_file_per_table = 0, undo area, etc.) and also performance problems (rollbacks are very costly in InnoDB).

Related Question