I am very aware of all the shortcomings in MySQL, but they still bite me in the behind almost daily. Today was one of those days.
Case in point: some records were accidentally removed from the database. These things happen, people click the wrong button every now and again. Restore from backup is not an option, so I restored a backup to a temporary database so I could copy the records over.
Obviously I ran the insert queries from the command line tool (using the query-editor) inside a transaction to make sure that all queries would work and I would not be left with a broken set of data.
And I was right to do so because one of the id's had changed since the backup was made. No worries, there is no COMMIT statement so all the data is still unchanged. Just fix the query and try again. This time I got a bunch of unique constraint violations.
I had not given any commits yet and the block ends with a ROLLBACK so why did the data suddenly already exist?
And then I remembered...
Yes I put the queries in a transaction but when a query fails in the commandline tool. It stops executing, so it never executed the ROLLBACK and in MySQL, a BEGIN is executed as "COMMIT;BEGIN;", so when I fixed the first error and tried again, it had secretly committed the queries that did work during the first run.
And now my data really was fscked.
Any other database will give you an error telling you to close the current transaction before opening a new one, saving you from destroying your data by *NOT COMMITTING*.
I don't really have words for just how stupid it is that you can commit data by *not* doing a commit... it's just... argh!