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.)


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).

  • As far as I understand, you can't get data back from BLACKHOLE (in the same way that you can't get the contents of /dev/null), so that wouldn't work for my SELECT or lastInsertID() statements. – Agi Hammerthief Sep 18 '14 at 14:54
  • 1
    SELECTs will work, it is just that they will return 0 rows. :-) If you want realistic results, then you need a test instance, as I suggested with the slave setup. – jynus Sep 18 '14 at 15:04
  • I'm not concerned about getting the SQL syntax right, but getting around the inconvenience of killing off test data/ resetting default values prior to each run of the import script during development. 'Slave setup' answers the question. Thank you. – Agi Hammerthief Sep 18 '14 at 15:05
  • 1
    Then, clone the server in binary form- using a cold backup, or xtrabackup, and then kill/destroy the instance every time. That is my best recommendation. – jynus Sep 18 '14 at 15:06
  • You can use a cold snapshot to rollback faster. – jynus Sep 21 '14 at 13:34

something like this? you explicit can disable the auto-commit mode http://php.net/manual/en/pdo.begintransaction.php

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.