php10501错误,error：1050

一. 发现

1.在主库执行insert语句， 发现从库上没有相应的执行。2.使用show processlist，没有发现SQL IO的进程mysql> show processlist;+—-+————-+———–+——+———+——+—————————————————————————–+——————+|

Id | User        | Host      | db   | Command | Time |

State

| Info             |+—-+————-+———–+——+———+——+—————————————————————————–+——————+|

3 | root        | localhost | an   | Query   |    0 |

NULL

| show processlist || 12

| system user |           | NULL | Connect |  117 | Waiting for master

to send event                                            |

NULL             |+—-+————-+———–+——+———+——+—————————————————————————–+——————+3.查看err日志101008

10:04:03 [Note] Slave SQL thread initialized, starting replication in

log 'bin-log.000002′ at position 198, relay log

'/var/local/mysql/logdir/relay_log.000004′ position: 342101008

10:04:28 [ERROR] Slave SQL: Error 'Table 'an_tb’ already exists’ on

query. Default database: 'an’. Query: 'create table an_tb (id int)

type=innodb’, Error_code: 1050101008

10:04:28 [Warning] Slave: The syntax 'TYPE=storage_engine’ is

deprecated and will be removed in MySQL 6.0. Please use

'ENGINE=storage_engine’ instead Error_code: 1287101008 10:04:28 [Warning] Slave: Table 'an_tb’ already exists Error_code: 1050101008

10:04:28 [ERROR] Error running query, slave SQL thread aborted. Fix the

problem, and restart the slave SQL thread with “SLAVE START”. We

stopped at log 'bin-log.000002′ position 98

4.根据陶方的提醒，使用show slave status\Gmysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: dbadb1.corp.alimama.comMaster_User: anmhMaster_Port: 3307Connect_Retry: 15Master_Log_File: bin-log.000006Read_Master_Log_Pos: 612Relay_Log_File: relay_log.000004Relay_Log_Pos: 242Relay_Master_Log_File: bin-log.000002Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1050Last_Error: Error 'Table 'an_tb’ already exists’ on query. Default database: 'an’. Query: 'create table an_tb (id int) type=innodb’Skip_Counter: 0Exec_Master_Log_Pos: 98Relay_Log_Space: 14332Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1050Last_SQL_Error:

Error 'Table 'an_tb’ already exists’ on query. Default database: 'an’.

Query: 'create table an_tb (id int) type=innodb’Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)

总结原因： slave服务器在执行relay log的时候，有个sql执行不下去了，卡住了。该sql为：create table an_tb (id int) type=innodb’

二. 解决方法：

第一种：基本思路是drop掉an_tb表， 结果：不成功！过程是:在从库上：drop table an_tb;stop slave;start slave;结果是：报同样的错误。 因为每次start slave之后，都会从relay log中读出create table an_tb的SQL；

第二种：基本思路是跳过relay log， 结果：不成功！简单的过程：MYSQL> stop slave;MYSQL>change master tomaster_log_file=’bin-log.000006′,master_log_pos=612,RELAY_LOG_FILE=’relay_log.000023′,RELAY_LOG_POS=612;MYSQL> start slave;mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: dbadb1.corp.alimama.comMaster_User: anmhMaster_Port: 3307Connect_Retry: 15Master_Log_File: bin-log.000006Read_Master_Log_Pos: 612Relay_Log_File: relay_log.000017Relay_Log_Pos: 612Relay_Master_Log_File: bin-log.000006Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 612Relay_Log_Space: 15448Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)

此时在show slave status中看不到错误了， 但是在err文件中，还是有同样的错误的101008

10:40:37 [Note] Slave SQL thread initialized, starting replication in

log 'bin-log.000002′ at position 607, relay log

'/var/local/mysql/logdir/relay_log.000012′ position: 751101008 10:40:37[ERROR]

Slave SQL: Error 'Table 'an_idb1′ already exists’ on query. Default

database: 'an’. Query: 'create table an_idb1 (id int) type=innodb’,

Error_code: 1050101008

10:40:37 [Warning] Slave: The syntax 'TYPE=storage_engine’ is

deprecated and will be removed in MySQL 6.0. Please use

'ENGINE=storage_engine’ instead Error_code: 1287101008 10:40:37 [Warning] Slave: Table 'an_idb1′ already exists Error_code: 1050101008

10:40:37 [ERROR] Error running query, slave SQL thread aborted. Fix the

problem, and restart the slave SQL thread with “SLAVE START”. We

stopped at log 'bin-log.000002′ position 607101008

10:40:37 [Note] Slave I/O thread: connected to master

'anmh@dbadb1.corp.alimama.com:3307′,replication started in log

'bin-log.000006′ at position 612但是发现现在的表变成了an_idb1了， 不再是an_tb。  看来这个方法是不对的。

第三种方法：思路还是要跳过relay log。采用reset slave的方法。结果是：不成功！mysql> stop slave;Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;Query OK, 0 rows affected (0.12 sec)

mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: dbadb1.corp.alimama.comMaster_User: anmhMaster_Port: 3307Connect_Retry: 15Master_Log_File:Read_Master_Log_Pos: 4Relay_Log_File: relay_log.000001Relay_Log_Pos: 4Relay_Master_Log_File:Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 126Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)

可以看到reset slave是把相关值做默认设置然后使用mysql> start slave;Query OK, 0 rows affected (0.09 sec)

mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: dbadb1.corp.alimama.comMaster_User: anmhMaster_Port: 3307Connect_Retry: 15Master_Log_File: bin-log.000006Read_Master_Log_Pos: 612Relay_Log_File: relay_log.000004Relay_Log_Pos: 751Relay_Master_Log_File: bin-log.000002Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1050Last_Error: Error 'Table 'an_idb1′ already exists’ on query. Default database: 'an’. Query: 'create table an_idb1 (id int) type=innodb’Skip_Counter: 0Exec_Master_Log_Pos: 607Relay_Log_Space: 14332Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1050Last_SQL_Error:

Error 'Table 'an_idb1′ already exists’ on query. Default database:

'an’. Query: 'create table an_idb1 (id int) type=innodb’Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)发现还是没有启动slave io进程， 查看err日志101008 10:53:52 [Note] Slave I/O thread killed while reading event101008 10:53:52 [Note] Slave I/O thread exiting, read up to log 'bin-log.000006′, position 612101008

10:58:15 [Note] Slave SQL thread initialized, starting replication in

log 'bin-log.000006′ at position 612, relay log

'/var/local/mysql/logdir/relay_log.000017′ position: 612101008 10:58:15 [ERROR] Error in Log_event::read_log_event(): 'read error’, data_len: 258, event_type: 0101008 10:58:15 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error101008 10:58:15 [ERROR] Slave

SQL: Relay log read failure: Could not parse relay log event entry. The

possible reasons are: the master’s binary log is corrupted (you can

check this by running 'mysqlbinlog’ on the binary log), the slave’s

relay log is corrupted (you can check this by running 'mysqlbinlog’ on

the relay log), a network problem, or a bug in the master’s or slave’s

MySQL code. If you want to check the master’s binary log or slave’s

relay log, you will be able to know their names by issuing 'SHOW SLAVE

STATUS’ on this slave. Error_code: 1594101008

10:58:15 [ERROR] Error running query, slave SQL thread aborted. Fix the

problem, and restart the slave SQL thread with “SLAVE START”. We

stopped at log 'bin-log.000006′ position 612101008

10:58:15 [Note] Slave I/O thread: connected to master

'anmh@dbadb1.corp.alimama.com:3307′,replication started in log

'bin-log.000006′ at position 612报新的错误了，binlog都挂了。呵呵，这说明reset slave之后， slave的一切设置都被清空了。这也让我想起了，可以继续使用change master to来做设置。

第四种，终于成功了的方法。 基本思路还是跳过relay logmysql> stop slave;Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;Query OK, 0 rows affected (0.13 sec)

mysql> change master to-> master_log_file=’bin-log.000006′,-> master_log_pos=612;Query OK, 0 rows affected (0.18 sec)在这里的master log和master log pos一定要是最新的，或者是比较新的。(总之要跳过报错的sql)

mysql> start slave;Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: dbadb1.corp.alimama.comMaster_User: anmhMaster_Port: 3307Connect_Retry: 15Master_Log_File: bin-log.000006Read_Master_Log_Pos: 612Relay_Log_File: relay_log.000002Relay_Log_Pos: 242Relay_Master_Log_File: bin-log.000006Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 612Relay_Log_Space: 392Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)

mysql> show processlist;+—-+————-+———–+——+———+——+—————————————————————————–+——————+|

Id | User        | Host      | db   | Command | Time |

State

| Info             |+—-+————-+———–+——+———+——+—————————————————————————–+——————+|

3 | root        | localhost | an   | Query   |    0 |

NULL

| show processlist ||

12 | system user |           | NULL | Connect |  117 | Waiting for

master to send event                                            |

NULL             ||

13 | system user |           | NULL | Connect |   51 | Slave has read

all relay log; waiting for the slave I/O thread to update it |

NULL             |+—-+————-+———–+——+———+——+—————————————————————————–+——————+3 rows in set (0.00 sec)

终于不报错啦， 而且SQL IO进程也启动起来了。 一切ok。