主从复制报1173错误处理

来源：互联网发布：java 写成绩划分编辑：程序博客网时间：2024/05/16 10:59

在一次项目的业务压力测试过程中，遇到如下问题：

zabbix监控邮件中显示如下报错：
2016.09.28 19:16:11

Trigger: MYSQL error log desc in last 10 lines_2

Values:MYSQL error_log: 2016-09-28 19:16:10 47236992240384 [ERROR] Slave SQL: Error 'This table type requires a primary key' on query.
Default database: ''. Query: 'alter table m_g_ver.go_driver_copy drop primary key,add unique key (`id`)', Gtid 0-74603306-1854116,
Internal MariaDB error code: 1173MYSQL error_log: 2016-09-28 19:16:10 47236992240384 [ERROR] Slave SQL: Error 'This table type requires a primary key' on query.
Default database: ''. Query: 'alter table m_g_ver.go_driver_copy drop primary key,add unique key (`id`)', Gtid 0-74603306-1854116,
Internal MariaDB error code: 1173 MYSQL error_log: 2016-09-28 19:16:10 47236992240384 [ERROR] Slave SQL: Error 'This table type requires a primary key' on query.
Default database: ''. Query: 'alter table m_g_ver.go_driver_copy drop primary key,add unique key (`id`)', Gtid 0-74603306-1854116,
Internal MariaDB error code: 1173

检查从库：
dba@10.16.74.61:3306(db(从)) : (none) 08:49:57>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.16.74.60
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 161535100
               Relay_Log_File: relay-bin.000012
                Relay_Log_Pos: 161498545
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 1173
                   Last_Error: Error 'This table type requires a primary key' on query. Default database: ''. Query: 'alter table m_g_ver.go_driver_copy drop primary key,add unique key (`id`)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 161498257
              Relay_Log_Space: 1235284687

从上面报错看，是从库执行alter table m_g_ver.go_driver_copy drop primary key,add unique key (`id`)'而报错，比较主库和从库的innodb_force_primary_key参数设置：

主库：
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_force_primary_key | OFF |
+--------------------------+-------+

从库：
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_force_primary_key | ON |
+--------------------------+-------+

从上面参数查看得知，是由于主库有关闭了主键强制功能，而从库没有关闭，所以在从库上执行关闭操作：
set global innodb_force_primary_key=off;
然后再在从库启动sql线程：
start slave sql_thread;

再查看slave状态，不再出现报错，等sql线程执行完所有的relay log后，即同步完成。

dba@10.16.74.61:3306((从)) : midea_gls_ver 09:02:32>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.16.74.60
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 162029482
               Relay_Log_File: relay-bin.000012
                Relay_Log_Pos: 161499911
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 161499623
              Relay_Log_Space: 1235779069
              Until_Condition: None
               Until_Log_File:

最后在第二个从库上同样执行上面操作。

0 0