When a MySQL trigger didn’t really
I just when through a convoluted debugging session I felt was worth sharing. I have a Python application which inserts data into a MySQL database table. There are triggers on this table that then insert data into another table in a different database.
Whenever I tested the Python code, I could see that the data was inserted successfully into the first database, but the triggers seemed to fail to insert data into the second database. However, I could tell that the triggers were firing because one of the columns in the second database lacked a default value and generated a warning because I was not inserting any data in that column.
I turned on query logging in order to get the exact query that the Python code was executing. I then copied this query and ran it from the command line. Now everything worked. What the hell? It’s the same query and I am logging into the DB with the same credentials, why should anything be different.
After some thought, it occurred to me that while my command line session was set up to auto-commit, Python probably does not work that way and the code may not be doing a commit. However, if that was the case why was the data from the initial query not getting rolled back, but only the data inserted into the second database via trigger?
As it turns out, this was the case. The Python code was missing a commit. The reason that the insert persisted on the first database is because it was using the MySQL default type of MyISAM which is non-transactional while the second database where the triggers inserted data was using InnoDB which is transactional. So when the rollback was attempted, only the triggers could be rolled back.
The only question that remained is why I didn’t see any warning message. I have run into this exact problem before in a Java application, but there was a clear warning message indicating that a transaction had been only partially rolled back.
