MySQL Replication Does Not Run Updates in Binlog

advertisements

I have a number mysql servers running version 5.1.63 and whilst running some queries against the slave earlier this week, I noticed some data on the slave that should have been removed using an update statement on the master.

My initial thoughts were:

  • someone on the team was updating the slave, which I have since disproved
  • that the column being updated had changed

So, I investigated by running a mysql show status "table" query. This was run against a test database on each of the servers to see what the data length was, in a lot of cases it was showing me the data length differed between servers, but on an eyeball look at the data I could see the data was the same, so I couldn't use this method to see if there were any differences as it appears to be prone to error.

Next I ran a simple (across all dbs) row count for each table to confirm the row count was the same - it was.

I then started looking in the bin logs for replication. I could see the update statements that should have run clearly visible in the logs, but the update never ran.

What I need to know is:

  1. is replication broken? I'm assuming it is
  2. if I create new slave servers, will I encounter the same issue?
  3. how do I find out the extent of the issue on my servers?

Any help is appreciated.

If you are using statement based replication then it is easily possible to end up with different results on master and slave due to badly constructed INSERT statements.

INSERT SELECT without ORDER BY, or where the ORDER BY can leave non deterministic results will cause the slaves to diverge from master.

From the MySQL site http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The order in which rows are returned by a SELECT statement with no ORDER BY clause is not determined. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY column. The choice of column does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 16.4.1.15, “Replication and LIMIT”.

If this has happened then your replicas have diverged and the only safe way to bring them back in line is to rebuild them from a recent backup of the master DB. The worst part of this is the error may never cause the replication to fail, yet the results are inconsistent. Normally replication fails when an UPDATE or DELETE statement affects a different number of rows than on master, this is confusing as it was not the UPDATE that actually caused the error and the only way I know to fix the issue is to inspect every INSERT query in the code base!

Related Articles

The nested MySQL query does not run, runs on SQL Server

I'm running multiple queries on both MySQL and SQLServer (same queries on both servers, same db). Almost all of them run fine. I have a problem with this one: SELECT `Extent1`.`IdGosc`, `Extent2`.`Imie`, `Extent2`.`Nazwisko` FROM `TGosc` AS `Extent1`

The mysql command does not run using php

For some reason my sql query is not executing and the error message is not printing in php. Here is the code: mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name&

The MySQL command does not run from the Bash file

I have the following command mysql -u root -ppass myDB -e "select * from table limit 10;" which when I run it from a linux server it works normally (10 tuples are displayed) When the following is added in a bash file echo 'mysql -u root -ppass m

AsyncTask does not run during the next activity call

In my splash screen Activity I run some lengthy initializations. So I create a background Async Task and execute it to do the heavy lifting. The main Activity (UI) thread then displays the splash screen image and returns from the onCreate() method, w

Ajax does not run after var data = json_parse (msg.d);

This is the first time i am playing with Ajax. I am trying to just create a basic login at the moment. Anyways i have the following script.. <script language = "javascript"> function Login() { $.ajax({ type: "POST", url: "aj

PHP python does not run PHP

hey yall. Im running python on a webserver from dreamhost. I am using their install of python and am using a lastfm module that can be found here: http://code.google.com/p/python-lastfm/ to get it to import properly i do this import sys sys.path.appe

Crontab Does Not Run a Function Shell Script

I have a script of the following format: #!/bin/bash cd /abc/def/user1/test export NIMBUS_ENV_FILE=/abc/def/user1/test/nimbus_env main/vdnet -c yaml/config.yaml -t ABC...* my script executes perfectly when run from the shell.I want to execute this sc

Karma Runner does not run all files

For some weird reason that I cannot fathom, my karma runner sometimes does not run all the test files. karma.conf.js: module.exports = function(config) { config.set({ basePath: '', frameworks: ['jasmine'], files: [ '../lib/angular/angular.min.js', '.

java maven exec-maven-plugin does not run on mvn clean install

Follow-up to a previous question: Maven run class before test phase: exec-maven-plugin exec:java not executing class. I am running jUnit4 tests, built with maven, on a jenkins box. I need to run a specific main-method java program before before the t