Tag Archives: data integrity

Why does MySQL replication fail?

When considering active-active multi-master, you must consider it’s foundation technology. Although MySQL replication is straightforward to setup, it can fail in a myriad of ways. Most of those are known and well understood. We can solve them only if we use the technology in the standard way.

Click through to the end for multi-master solutions that work with MySQL.

Reason 2 – Replication is brittle to start with

– statement based – throw it over the fence architecture
– use non-deterministic functions causes unpredictable behavior
– no globally unique transaction id (like Oracle SCN – system change number)
– no built in checksums – pt-table-checksum is a must
– replication position syncing to index files prone to breaking
– temp tables disappear after restart
– row-based still a new code path – doesn’t support zero downtime
– even with row-based mysql can fall back to statement
– row-based does not include SQL in binlogs
– MySQL replica slaves die frequently, require re-clone from authoritative master

NEXT: Reason 3 – MySQL row-based replication has limitations

PREV: Reason 1 – auto increment settings create new problems

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

Data Spot Checks – What are they and why are they important?

Loading data into a database whether it is Oracle or MySQL, involves passing a lot of hurdles.  If you’re working with CSV files, special characters may cause trouble, end of line characeters, very long lines, and so forth.

Once your data load is complete, spot checks involve basic checks to determine that all went well.  Counting the number of records which should have been loaded and comparing the table’s count before and after the load, would be one simple spot check.

These types of checks are a basic once over, as opposed to a more thorough and rigorous analysis of the data, for example calculating checksums, that can guarantee that a data load completed and all data is in good shape.

Quora discussion – What are data spot checks and why are they important?