Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

Question

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

Answer 1

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

• Agi Hammerthief: 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.
• jynus: 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.
• Agi Hammerthief: 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.
• jynus: 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: You can use a cold snapshot to rollback faster.

Answer 2

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