Monday, June 16, 2008

Weird SQL Error while Replicating

I encountered this weird SQL error / anomaly.

I have 5 replication slaves with one master. On the master, an insert statement was issued and it was replicated to all of the slaves except to one which I will call weird_slave. The weird_slave (MyISAM) complained that there was some SQL syntax error. I checked the insert statement and it looks pretty simple.

Also, the weird_slave has already been running for more than a couple of months now and it has received some variation of the insert statement.

I was thinking that the table could be corrupt and issued a check table and everything returned okay.

So, the question is why would the weird_slave complain about an SQL syntax error while the other slaves were okay with it. Sadly, I could not reproduce the error again. Also, I've tried to re-insert the same SQL statement on the weird_slave again and again and it was accepting it without complaining.

3 comments:

Mark Robson said...

I suppose one possibility is that the network corrupted the data (but passed the TCP checksum) when transferring the binary log.

Is this slave over a long internet link (i.e. many hops) ?

Scott said...

What was the statement and the error?

Bichonfrise74 said...

Hi Mark,

The Master database is in the west coast while the slave is in the east coast. So, you might be correct in saying that the network corrupted it.

Hi Scott,

Here's the error...

080612 12:52:32 [ERROR] Slave: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: billing_customer. Query: 'INSERT INTO customer (id, payment, date) VALUES (8420, 30, '2008-06-12'), (3020, 40, '2008-06-12'), (3213, 30, '2008-06-12');