MySQL新做从库报错-1146 'Table 'xxxxx' doesn't exist'

前言

此案例介绍因为数据库大小写参数设置问题，导致新做的半同步从库SQL线程报错。

1 环境信息

自建MySQL：5.7.22

OS 版本：CentOS Linux release 7.5

2 错误描述

通过自建MySQL物理备份，新做MySQL半同步复制从库，配置同步后SQL线程报错：Error executing row event: 'Table 'xyy_crm.crm_bd_customer_relation' doesn't exist'.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXX.XXX.XXX.XXX
                  Master_User: XXXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.010111
          Read_Master_Log_Pos: 320328339
               Relay_Log_File: relay.000004
                Relay_Log_Pos: 180578344
        Relay_Master_Log_File: mysql_bin.010111
             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: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e246da38-f86e-11e8-894b-00163e0d4316:4576300746' at master log mysql_bin.010111, end_log_pos 180592114. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

mysql> select * from performance_schema.replication_applier_status_by_worker limit 1\G
*************************** 1. row ***************************
         CHANNEL_NAME: 
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: e246da38-f86e-11e8-894b-00163e0d4316:4576300746
    LAST_ERROR_NUMBER: 1146
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'e246da38-f86e-11e8-894b-00163e0d4316:4576300746' at master log mysql_bin.010111, end_log_pos 180592114; Error executing row event: 'Table 'test.t_bd_relation' doesn't exist'
 LAST_ERROR_TIMESTAMP: 2022-10-17 18:10:03
1 row in set (0.00 sec)

3 排查

既然报表不存在，那去主库查看。

mysql> use xyy_crm;
Database changed
mysql> show tables like '%t_bd_relation%';
Empty set (0.00 sec)

竟然不存在，但是不应该是这种情况，后面用单个关键字搜索就查到了。

mysql> show tables like '%relation%';
+--------------------------------------+
| Tables_in_xyy_crm (%relation%)       |
+--------------------------------------+
| t_BD_relation                        |
| t_follow_relation                    |
| t_follow_relation_log                |
| t_private_relation                   |
| t_shop_relation                      |
+--------------------------------------+
14 rows in set (0.00 sec)

t_BD_relation表名里面有大写，这里就想到是表名大小写问题。

## 主库mysql> show variables like '%case%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| lower_case_file_system             | OFF   |
| lower_case_table_names             | 0     |
| validate_password_mixed_case_count | 1     |
+------------------------------------+-------+
3 rows in set (0.00 sec)

## 新做从库mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

可以看到线上库区分大小写，新做库不区分大小写，主库binlog里的t_BD_relation表名，从库认为不存在。

4 原因

我们的自建数据库参数文件，lower_case_table_names参数默认值都是1（Linux下MySQL5.7默认值为0）。线上主库也是自建，使用时间比较久了，可能后面改过这个参数值。

5 解决方案

将新做从库该参数默认值改为0，然后重启实例，问题解决。

6 补充

报错事务对应binlog内容：

SET @@SESSION.GTID_NEXT= 'e246da38-f86e-11e8-894b-00163e0d4316:4576300746'/*!*/;
# at 180591334
#221017 16:43:28 server id 1114  end_log_pos 180591409 CRC32 0x30d51e1e 	Query	thread_id=2500334	exec_time=0	error_code=0
SET TIMESTAMP=1665996208/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 180591409
# at 180591922
#221017 16:43:28 server id 1114  end_log_pos 180592014 CRC32 0xd62f4d4a 	Table_map: `test`.`t_BD_relation` mapped to number 277
# at 180592014
#221017 16:43:28 server id 1114  end_log_pos 180592114 CRC32 0x031daacb 	Write_rows: table id 277 flags: STMT_END_F
### INSERT INTO `test`.`t_BD_relation`
### SET
###   @1=150189
###   @2=1501455552
###   @3=1931476
###   @4=18625
###   @5=2
###   @6='xxxxx'
###   @7='xxxxxxx'
###   @8='xxxxx'
###   @9=0
###   @10='2022-10-17 16:43:28'