In order to run PHPUnit tests I use a boostrap file which uses mysqldump to export the ever-changing database schema without any data, and import it into the testing database. After using this method for a while I stumbled on a problem with the auto-increment values for the table primary keys. There is no easy way to get mysqldump to avoid including the next auto-increment value in the dump. The problem this causes is that if your tests depend on specific primary key values things will go wrong the second time you run your test suite. There is a way to fix this by piping mysqldump’s output into sed. Here’s the actual command:
mysqldump -d [devDB] | sed 's/AUTO_INCREMENT=[0-9]*//' >
This will work under Linux but sadly not on Windows which doesn’t have sed but there’s a solution for that.
Thankfully I found the sed binary for Windows here. Download UnxUpdates.zip, which contains sed.exe. Note that Windows command prompt will give you an error if you use the command as its shown above because it gets confused with the single quotes, you’ll need to replace them with double quotes.
Here’s the full command I use for export/strip auto-increment and import:
mysqldump -d -h localhost -u [username] -p[password] [devDBName] |
sed "s/ AUTO_INCREMENT=[0-9]*\b//" |
mysql -h localhost -u [username] [testDBName]