Posted by Sheeri Cabral on Jan 25, 2009
I realized tonight exactly why MySQL’s default behavior of silent truncation bothers me.
It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they’re doing more work.
It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because then their throughput times are faster.
Similarly, with MySQL’s default behavior of silent truncation, it’s as if the database server is saying “the fewer database errors raised, the better.” As in the previous two examples, the metrics do not matter if the quality of service is poor — particularly when the quality of service is poor specifically *because* people are trying to meet the metrics instead of the actual goal — customer satisfaction.
While it’s true that the goal is fewer database errors, the idea is to have fewer errors because there are fewer problems — not because the problems are not being reported!!!
Posted by Raj Thukral on Oct 8, 2008
Do you have a master-slave MySQL set up? Ever do DDL changes on the master? You may be hit with a serious data integrity bug. Read on.
One of our clients does a regular rename tables on the master to keep the current table small and archive off old data. We’d occasionally be hit by a ‘duplicate key’ error on the slave on the current table and have to resolve it manually. Digging into the issue, I managed to replicate it on demand and filed bug 39675 with MySQL, which subsequently has been verified and slated for fix, though from what it seems only in version 6.0. The bug affects all versions of MySQL from 4.1 to 6.0.
In a nutshell, here is what happens. The rename tables command only checks for pending transactions or locks in the current session. If there is a pending transaction in another session on the table being renamed, the rename will succeed, but the order in which the transaction is written to the binlog will be different from the order in which the transactions were applied on the master. This means that the data on the slave will now be out of sync for this table.
Here’s a test-case:
Read the rest of this entry . . .
