InnoDB:执行select查询的断言失败- MySQL 5.7.31

Question

我正在使用pt-archiver进行表的日常归档，但是在从一个表中选择数据时，我会得到以下错误，并重新启动mysql实例。

2021-07-07 13:21:17 0x7fe0dffdc700  InnoDB: Assertion failure in thread 140603807352576 in file btr0pcur.cc line 46

我使用-dry运行pt-存档程序，下面是我的选择查询。

SELECT /*!40001 SQL_NO_CACHE */ `irig_time`,`device_id`,`message_id`,`mode`,`protection_1`,`protection_2`,`protection_3`,`protection_4`,`alarm_1`,`alarm_2`,`alarm_3`,`alarm_4`,`grid_switch_control`,`dc_switch_1_on`,`dc_switch_2_on`,`additional_feedback_external_sensor`,`module_communication_fault_position` FROM `acbm_status_v2_0_0` FORCE INDEX(`PRIMARY`) WHERE (DATE(irig_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY)) ORDER BY `irig_time`,`device_id` LIMIT 200

如果手动运行此查询，仍然会得到断言错误，并重新启动mysql实例。

以下是表格结构

Table: acbm_status_v2_0_0
Columns:
irig_time                             datetime(6) PK 
device_id                             int(11) PK 
message_id                            bigint(20) UN 
mode                                  varchar(64) 
protection_1                          int(10) UN 
protection_2                          int(10) UN 
protection_3                          int(10) UN 
protection_4                          int(10) UN 
alarm_1                               int(10) UN 
alarm_2                               int(10) UN 
alarm_3                               int(10) UN 
alarm_4                               int(10) UN 
grid_switch_control                   tinyint(1) 
dc_switch_1_on                        tinyint(1) 
dc_switch_2_on                        tinyint(1) 
additional_feedback_external_sensor   tinyint(1) 
module_communication_fault_position   int(10) UN

下面是完整的跟踪

2021-07-07 13:21:17 0x7fe0dffdc700  InnoDB: Assertion failure in thread 140603807352576 in file btr0pcur.cc line 461
InnoDB: Failing assertion: page_is_comp(next_page) == page_is_comp(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:21:17 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

 

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=18
max_threads=500
thread_count=18
connection_count=17
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 206883 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

 

Thread pointer: 0x7fe01c000d40
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fe0dffdbe60 thread_stack 0x40000
mysqld(my_print_stacktrace+0x2c)[0x556a3c9cab7c]
mysqld(handle_fatal_signal+0x501)[0x556a3c2e1f01]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7fe1fffaa730]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b)[0x7fe1ffa857bb]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x121)[0x7fe1ffa70535]
mysqld(+0x6c1083)[0x556a3c2a9083]
mysqld(+0x6c30da)[0x556a3c2ab0da]
mysqld(_Z15row_search_mvccPh15page_cur_mode_tP14row_prebuilt_tmm+0xd03)[0x556a3cc699a3]
mysqld(_ZN11ha_innobase13general_fetchEPhjj+0xdf)[0x556a3cb6d4af]
mysqld(_ZThn760_N11ha_innopart18index_next_in_partEjPh+0x2d)[0x556a3cb8351d]
mysqld(_ZN16Partition_helper19handle_ordered_nextEPhb+0x299)[0x556a3c714199]
mysqld(_ZN7handler13ha_index_nextEPh+0x1c5)[0x556a3c3358d5]
mysqld(+0xb932dc)[0x556a3c77b2dc]
mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x18f)[0x556a3c7817cf]
mysqld(_ZN4JOIN4execEv+0x20b)[0x556a3c77aacb]
mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x2e0)[0x556a3c7e2d50]
mysqld(+0xbbd45b)[0x556a3c7a545b]
mysqld(_Z21mysql_execute_commandP3THDb+0x4924)[0x556a3c7ac564]
mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3dd)[0x556a3c7ae94d]
mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x1062)[0x556a3c7afa22]
mysqld(_Z10do_commandP3THD+0x207)[0x556a3c7b0d67]
mysqld(handle_connection+0x298)[0x556a3c8690c8]
mysqld(pfs_spawn_thread+0x157)[0x556a3ce77cd7]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7fe1fff9ffa3]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fe1ffb474cf]

 

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fe01c004860): SELECT /*!40001 SQL_NO_CACHE */ `irig_time`,`device_id`,`message_id`,`mode`,`protection_1`,`protection_2`,`protection_3`,`protection_4`,`alarm_1`,`alarm_2`,`alarm_3`,`alarm_4`,`grid_switch_control`,`dc_switch_1_on`,`dc_switch_2_on`,`additional_feedback_external_sensor`,`module_communication_fault_position` FROM `ycube2`.`acbm_status_v2_0_0` FORCE INDEX(`PRIMARY`) WHERE (DATE(irig_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY)) ORDER BY `irig_time`,`device_id` LIMIT 1000
Connection ID (thread ID): 41
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Answer 1

我能够解决问题，通过更改以下的哪里条款，我不知道技术细节如何帮助，但将搜索，如果有人解释更多细节，这将是很好的。

从…

where (DATE(irig_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY))

至

where irig_time BETWEEN '2021-07-07 00:00:00' AND '2021-07-07 23:59:59'