mysql1033错误存储引擎_存储引擎和Mysql服务层出现索引信息不一致错误提示-CSDN博客

本文链接：https://blog.csdn.net/weixin_39789857/article/details/113939652

这篇博客描述了一个关于MySQL数据库中索引统计信息不一致的问题。在执行DDL操作，如创建新索引后，由于frm文件的异常替换，导致存储引擎与MySQL服务层的索引数量不符。通过一系列的恢复步骤，包括回滚frm文件和重新设置存储引擎，最终解决了这个问题。日志显示了索引冲突和找不到索引的错误，但经过正确的操作，索引能够被正确使用。

摘要由CSDN通过智能技术生成

错误日志：

[ERROR] Table vip_cube/imp_sup_dm_sup_brand_name_goods_online_half_hm contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQL

存储引擎和Mysql服务层出现索引统计信息不一致，是否进行了DDL操作(创建了索引？出现这个错误，新创建的索引是否能使用？)

问题重现：

root@localhost*5.5.48-log[test] >create table employees like employees.employees;

Query OK, 0 rows affected (0.21 sec)

root@localhost*5.5.48-log[test] > \! cp employees.frm employees.frm.old

root@localhost*5.5.48-log[test] >alter table employees add index idx_first_name(first_name);

Query OK, 0 rows affected (0.64 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@localhost*5.5.48-log[test] >\! mv employees.frm.old employees.frm

root@localhost*5.5.48-log[test] >\! chown mysql.mysql employees.frm

root@localhost*5.5.48-log[test] >flush tables;

root@localhost*5.5.48-log[test] >select first_name from employees where first_name like 'a%' limit 1;

Empty set (0.00 sec)

查看错误日志：

[ERROR] Table test/employees contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQL

root@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

可以看到语句是走不到索引的，语句分析和优化是mysql server完成？

然后恢复创建索引后的frm文件：

root@localhost*5.5.48-log[test] > mv employees.frm.2 employees.frm – `employees.frm.2之前备份了`

root@localhost*5.5.48-log[test] >flush tables;

Query OK, 0 rows affected (0.02 sec)

root@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;

+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+

+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+

+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

root@localhost*5.5.48-log[test] >alter table employees engine=innodb;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@localhost*5.5.48-log[test] >flush tables;

Query OK, 0 rows affected (0.04 sec)

root@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

然后恢复有索引的frm文件

root@localhost*5.5.48-log[test] >flush tables;

Query OK, 0 rows affected (0.04 sec)

root@localhost*5.5.48-log[test] >show create table employees\G;

*************************** 1. row ***************************

Table: employees

Create Table: CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` enum('M','F') NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`),

KEY `idx_first_name` (`first_name`) – `索引是可以看到，查frm文件`

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

ERROR:

No query specified

但是日志会报如下错，说明存储引擎的索引已经在执行alter table employees engine=innodb;删除(重建表基于frm定义) ：

160508 17:44:01 [ERROR] Table test/employees contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MySQL

160508 17:44:01 [ERROR] Innodb could not find key n:o 1 with name idx_first_name from dict cache for table test/employees

160508 17:44:01 [ERROR] Table test/employees contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html