Last night I nailed an annoying bug in my bug tracker that has been tricky to locate, and was related to MySQL, transactions and apparently only certain MySQL versions which behaved correctly.
To ensure consistency in the bug tracker, I use a transaction to commit several changes. In this case, after an issue was created, it wouldn’t save stuff being inserted after the transaction->commit(). However – the id fields which were auto_inc – would all increment by one for each row that was never inserted. This would only happen on a select few MySQL versions, which lead me to believe it was actually a bug in those specific MySQL versions, and not in my code (it’s never me, of course ). This was also based on the assumption that doing a commit() would stop “transaction mode” and turn “autocommit” back on. One glance at the PHP manual proved me wrong.
It all did come down to the fact that in the class file that was creating an issue, the “autocommit” parameter for mysqli was never set back to “true” after committing a transaction. According to the manual, this should mean that all subsequent queries for that connection would “fail” unless a new “commit()” has been made. However, this obviously didn’t happen, as I have most of my bug tracker installations running properly, without this problem happening at all.
Under no circumstance was a commit() called after that point – which should mean that any queries being executed after that point were being rolled back. However, that only happed on a specific few MySQL versions, and none of the ones I tested on (confirmed at least 5.0.61 and 5.1.3). To make matters worse, the auto_inc id field did increment by one, even though the row was never saved!
The solution was simple – either do a second commit() at the end of the function to make sure all the queries after the initial transaction were commited, or make sure that the transaction turns on autocommit when it was done. In this case, I created a second function in the transaction class to commitAndEnd(), which turned autocommit back on after committing.
Lesson learned: Read manuals, don’t make assumptions. And don’t blindly trust MySQL.