MySQL Troubleshooting

Problems with the SQL Thread

As I already mentioned in Statement-Based Replication Issues, each slave has a single SQL thread, so all its errors can be tested in a single-threaded MySQL client. Even if you run the multithreaded slave preview, you can always ask it to use a single thread when you’re trying to reproduce an error. If reducing activity to one SQL thread fails to make the problem go away, use the following techniques to fix logic errors on the single thread, then switch to multiple threads again.

It’s easy to re-create a query that caused a slave to fail: just run it using the MySQL command-line utility.

When you get an SQL error on the slave, it stops. SHOW SLAVE STATUS shows the SQL thread error that caused the problem:

Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'test.t1' doesn't exist' on query.
Default database: 'test'.
Query: 'INSERT INTO t1 VALUES(1)'

The error message usually contains the text of the SQL query and the reason why it failed. In this case, the error message explains everything (I dropped the t1 table on the slave to create this example), but in case of doubt, you can try to run same query in the MySQL command-line client and see the results:

mysql> INSERT INTO t1 VALUES(1);
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

The error in this example makes it clear what you need to do to solve the problem: create the table.

mysql> CREATE TABLE t1(f1 INT);
Query OK, 0 rows affected (0.17 sec)

After the table is created, we can restart the slave SQL thread: ...