Saturday, June 21, 2008

Creation of Temp Table Anomaly

One of our replication slave broke last night. This is another strange one. I have multiple slaves running under version 4.1.19 (yup, this is an old one, hehe). But for some strange reason, only one replication slave exhibited the following behavior: It would seem like it cannot create a temporary table while other replication slaves have no problem with it. See below for the example.

mysql> create temporary table abc (id int);
ERROR 1046 (3D000): No database selected

mysql> \u customer
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'customer'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> \u test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'test'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)


As you can see, creating permanent tables is not a problem. But creating temporary tables on different databases is an issue. I wonder if anyone has seen this? And take note that only one replication slave is showing this error, while other slaves are working just fine.

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.

Sunday, June 8, 2008

Cause of Replication Delay? I/O or SQL Thread?

I was thinking of this and could not figure it out or maybe I am just confused... Hehehe.

If MySQL is lagging behind the master (7,000 secs.), would it be caused by the I/O thread being slow or by the SQL thread (of course, this is assuming that I only have these two options to choose from)? If it is the I/O thread, what should I be looking for?

I've always assumed that the I/O thread would not cause a huge replication delay as it is just reading the binary logs from the master. So, if the Master_Log_File and Relay_Master_Log_File are the same and the Exec_Master_Log_Pos and Read_Master_Log_Pos are the same too, then I can assume that the replication delay is not based on the I/O thread, right?

And does anyone know how to compute the Seconds_Behind_Master???