记一次mysql错误

作者: 爱钓鱼的码农 | 来源:发表于2021-11-16 22:15 被阅读0次

记一次mysql错误
记一次修复MySQL socker 错误
Centos下Mysql因为pid文件启动失败问题解析
记一次安装mysql过程
docker环境下mysql第一次登录报错
Linux Mysql 启动错误查看错误日志
Forcing close of thread 123 use
Mac MySQL Workbench mysql 5.7 E
利用mysql data文件夹恢复数据
MySQL 错误代码

背景

线上用备份数据拉起一个实例后，发现部分表找不到，然后查看错误日志如下所示。

2021-11-16T10:02:34.134628Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetapprovaltype_0` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134694Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetapprovaltype` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134729Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetbearer_0` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134790Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfgro_0_2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134836Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfgro_0_3` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134872Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfgro_0_4` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134906Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfgro_0_5` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134940Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfope_0_2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.134969Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfope_0_3` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135018Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfope_0_4` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135049Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdfope_0_5` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135093Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdf_0_2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135130Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdf_0_3` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135169Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdf_0_4` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135206Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdf_0_5` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135241Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgetdimension_0` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135276Z 4 [Warning] InnoDB: Load table `ecology`.`fnabudgettemplate_0` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2021-11-16T10:02:34.135304Z 4 [Warning] InnoDB: Load table `ecology`.`fnacostcontrol` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

原因分析

代码调试堆栈日志

cmp_cols_are_equal rem0cmp.cc:124
dict_foreign_qualify_index dict0dict.cc:6691
dict_foreign_find_index dict0dict.cc:3619
dict_foreign_add_to_cache dict0dict.cc:3772
dict_load_foreign dict0load.cc:3686
dict_load_foreigns dict0load.cc:3829
dict_load_table_one dict0load.cc:3195
dict_load_table dict0load.cc:2876
dict_table_open_on_name dict0dict.cc:1248
ha_innobase::open_dict_table ha_innodb.cc:6238
ha_innobase::open ha_innodb.cc:5876
handler::ha_open handler.cc:2758
open_table_from_share table.cc:3353
open_table sql_base.cc:3517
open_and_process_table sql_base.cc:5109
open_tables sql_base.cc:5720
open_tables_for_query sql_base.cc:6495
mysqld_list_fields sql_show.cc:1131
dispatch_command sql_parse.cc:1638
do_command sql_parse.cc:999
handle_connection connection_handler_per_thread.cc:300
pfs_spawn_thread pfs.cc:2188
start_thread 0x00007f2b011e6ea5
clone 0x00007f2affe389fd

MySQL代码

/*************************************************************//**
Returns TRUE if two columns are equal for comparison purposes.
@return TRUE if the columns are considered equal in comparisons */
ibool
cmp_cols_are_equal(
/*===============*/
    const dict_col_t*    col1,    /*!< in: column 1 */
    const dict_col_t*    col2,    /*!< in: column 2 */
    ibool            check_charsets)
                    /*!< in: whether to check charsets */
{
    if (dtype_is_non_binary_string_type(col1->mtype, col1->prtype)
        && dtype_is_non_binary_string_type(col2->mtype, col2->prtype)) {
        /* Both are non-binary string types: they can be compared if
        and only if the charset-collation is the same */

        // 这里检查到两个字段的字符集类型不同返回false，最终导致加载外健数据字典失败
        if (check_charsets) {
            return(dtype_get_charset_coll(col1->prtype)
                   == dtype_get_charset_coll(col2->prtype));
        } else {
            return(TRUE);
        }
    }
    if (dtype_is_binary_string_type(col1->mtype, col1->prtype)
        && dtype_is_binary_string_type(col2->mtype, col2->prtype)) {
        /* Both are binary string types: they can be compared */
        return(TRUE);
    }
    if (col1->mtype != col2->mtype) {
        return(FALSE);
    }
    if (col1->mtype == DATA_INT
        && (col1->prtype & DATA_UNSIGNED)
        != (col2->prtype & DATA_UNSIGNED)) {
        /* The storage format of an unsigned integer is different
        from a signed integer: in a signed integer we OR
        0x8000... to the value of positive integers. */
        return(FALSE);
    }
    return(col1->mtype != DATA_INT || col1->len == col2->len);
}

问题复现

CREATE TABLE `fnaaccountinfo` (
  `id` char(32) NOT NULL,
  `accountName` varchar(600) DEFAULT NULL,
  `beginDate` char(10) DEFAULT NULL,
  `endDate` char(10) DEFAULT NULL,
  `codeName` varchar(600) DEFAULT NULL,
  `accountNumber` int(11) DEFAULT NULL,
  `isArchive` int(11) DEFAULT NULL,
  `displayOrder` decimal(5,2) DEFAULT NULL,
  `numberCode` varchar(100) DEFAULT NULL,
  `accountType` int(11) DEFAULT NULL,
  `showType` int(11) DEFAULT NULL,
  `wfForceOverLogic` int(11) DEFAULT NULL,
  `isRollingBudget` int(11) DEFAULT NULL,
  `rollBudgetperiod` varchar(4000) DEFAULT NULL,
  `startRollBudget` int(11) DEFAULT NULL,
  `endRollBudget` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FnaAccountInfo_u1` (`accountNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `fnabudgetapprovaltype_0` (
  `id` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `accountId` char(32)  NOT NULL,
  `approvaTypelName` varchar(600) DEFAULT NULL,
  `displayOrder` decimal(6,2) DEFAULT NULL,
  `approvalFillDataSataus` int(11) DEFAULT NULL,
  `approvaTypelstatus` int(11) DEFAULT NULL,
  `approvalVersGroupId` char(32) DEFAULT NULL,
  `approvalVersion` int(11) DEFAULT NULL,
  `apprvoalActivation` int(11) DEFAULT NULL,
  `description` varchar(4000) DEFAULT NULL,
  `hashzdim` int(11) DEFAULT NULL,
  `jtopojson` longtext,
  `approvaldfstatus` int(11) DEFAULT NULL,
  `isanalysis` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_approval_accountId_0` (`accountId`),
  CONSTRAINT `fnabudgetapprovaltype_0_ibfk_1` FOREIGN KEY (`accountId`) REFERENCES `fnaaccountinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


set global foreign_key_checks=off;

退出回话再进入：
alter table fnabudgetapprovaltype_0 modify column `accountId` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

重启实例，检查fnabudgetapprovaltype_0，发现表不存在。

复现结果

mysql> show create table `ecology`.`fnabudgetdfope_0_3`\G;
*************************** 1. row ***************************
       Table: fnabudgetdfope_0_3
Create Table: CREATE TABLE `fnabudgetdfope_0_3` (
  `id` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `accountid` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `templateid` char(32) NOT NULL,
  `editionnumber` int(11) DEFAULT NULL,
  `approvalstatus` int(11) DEFAULT NULL,
  `budgeteditdate` char(10) DEFAULT NULL,
  `budgetedittime` char(8) DEFAULT NULL,
  `budgetdatasources` int(11) DEFAULT NULL,
  `displayorder` decimal(8,2) DEFAULT NULL,
  `wfrequestid` int(11) DEFAULT NULL,
  `approverequestid` int(11) DEFAULT NULL,
  `commencementall` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `deltemp` int(11) DEFAULT NULL,
  `eleinputdatavalue` varchar(4000) DEFAULT NULL,
  `isinputval` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_dfoa_0_3` (`accountid`),
  CONSTRAINT `fnabudgetdfope_0_3_ibfk_1` FOREIGN KEY (`accountid`) REFERENCES `fnaaccountinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create table ecology.fnaaccountinfo\G;
ERROR 1146 (42S02): Table 'ecology.fnaaccountinfo' doesn't exist
ERROR:
No query specified

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> show create table `ecology`.`fnabudgetdfope_0_3`\G;
*************************** 1. row ***************************
       Table: fnabudgetdfope_0_3
Create Table: CREATE TABLE `fnabudgetdfope_0_3` (
  `id` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `accountid` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `templateid` char(32) NOT NULL,
  `editionnumber` int(11) DEFAULT NULL,
  `approvalstatus` int(11) DEFAULT NULL,
  `budgeteditdate` char(10) DEFAULT NULL,
  `budgetedittime` char(8) DEFAULT NULL,
  `budgetdatasources` int(11) DEFAULT NULL,
  `displayorder` decimal(8,2) DEFAULT NULL,
  `wfrequestid` int(11) DEFAULT NULL,
  `approverequestid` int(11) DEFAULT NULL,
  `commencementall` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `deltemp` int(11) DEFAULT NULL,
  `eleinputdatavalue` varchar(4000) DEFAULT NULL,
  `isinputval` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_dfoa_0_3` (`accountid`),
  CONSTRAINT `fnabudgetdfope_0_3_ibfk_1` FOREIGN KEY (`accountid`) REFERENCES `fnaaccountinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> show create table ecology.fnaaccountinfo\G;
*************************** 1. row ***************************
       Table: fnaaccountinfo
Create Table: CREATE TABLE `fnaaccountinfo` (
  `id` char(32) NOT NULL,
  `accountName` varchar(600) DEFAULT NULL,
  `beginDate` char(10) DEFAULT NULL,
  `endDate` char(10) DEFAULT NULL,
  `codeName` varchar(600) DEFAULT NULL,
  `accountNumber` int(11) DEFAULT NULL,
  `isArchive` int(11) DEFAULT NULL,
  `displayOrder` decimal(5,2) DEFAULT NULL,
  `numberCode` varchar(100) DEFAULT NULL,
  `accountType` int(11) DEFAULT NULL,
  `showType` int(11) DEFAULT NULL,
  `wfForceOverLogic` int(11) DEFAULT NULL,
  `isRollingBudget` int(11) DEFAULT NULL,
  `rollBudgetperiod` varchar(4000) DEFAULT NULL,
  `startRollBudget` int(11) DEFAULT NULL,
  `endRollBudget` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FnaAccountInfo_u1` (`accountNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select  @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

结论

由于该实例存在部分表有外键，相关主表、从表相关联字段(包含类型、字符集等）有修改操作，此操作仅在foreign_key_checks=off条件可操作,需要客户确认是否有相关操作，目前实例发生重启也会出现此类问题，为避免此类情况再次发生可尽快删除相关外键或删除外键依赖。

记一次mysql错误
背景线上用备份数据拉起一个实例后，发现部分表找不到，然后查看错误日志如下所示。原因分析代码调试堆栈日志 My...
记一次修复MySQL socker 错误
问题描述初次往数据库中source 一个超大的文件，过了一会sql报错 table is full然后去data...
Centos下Mysql因为pid文件启动失败问题解析
问题描述 1. 启动问题服务器经过一次重启，然后再次使用以下命令开启Mysql，出现错误。 2. Mysql错误...
记一次安装mysql过程
记一次安装mysql过程更新 2019年6月10日10:57:03忘记了mysql的密码，重新安装了mysql-...
docker环境下mysql第一次登录报错
错误现象通过docker部署mysql 8.0.17后，第一次通过Navicat等客户端登录时会报如下错误：错...
Linux Mysql 启动错误查看错误日志
启动Mysql 出现错误不管是什么错误、先去查看Mysql的错误日志主机名 ([meng@M ~]$)：就是 ...
Forcing close of thread 123 use
记一次mysql5.5假死的问题状态表现重启mysql网站就可以正常访问排查在mysql文件中找到了日志文件搜索...
Mac MySQL Workbench mysql 5.7 E
一、环境信息： Mac、MySQL Workbench 6.3、mysql 5.7 二、错误产生三、错误信息四...
利用mysql data文件夹恢复数据
记一次误操作导致mysql被删除后，利用mysql的data文件夹恢复数据。以下方法仅适用于如下情况： mysql...
MySQL 错误代码
MySQL错误代码