Cluster crashes when composite keys are queried by SHOW INDEXES

General

Escalation

General

Escalation

Description

I discovered strange crashes with a client who used tools like Mysql Workbench and HeidiSQL. After some reserch I was able to reproduce the problem.
These tools use implicit queries like SHOW indexes and SHOW TABLE STATUS.

2020-10-07T15:30:30.749787Z 0 [Note] [MY-000000] [WSREP] Initiating SST cancellation
15:30:30 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f6e2847be10
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 = 7f6e98296d50 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x20231f1]
/usr/sbin/mysqld(handle_fatal_signal+0x33b) [0x1231cab]
/lib64/libpthread.so.0(+0x12dd0) [0x7f737aeeedd0]
/usr/lib64/galera4/libgalera_smm.so(+0x1dad17) [0x7f736725cd17]
/usr/lib64/galera4/libgalera_smm.so(+0x1f5f61) [0x7f7367277f61]
/usr/sbin/mysqld(wsrep::wsrep_provider_v26::replay(wsrep::ws_handle const&, wsrep::high_priority_service*)+0x3c) [0x27400ec]
/usr/sbin/mysqld(Wsrep_client_service::replay()+0xb7) [0x123f8b7]
/usr/sbin/mysqld(wsrep::transaction::after_statement()+0x23d) [0x273e45d]
/usr/sbin/mysqld(wsrep::client_state::after_statement()+0x12d) [0x27218cd]
/usr/sbin/mysqld(ha_commit_trans(THD*, bool, bool)+0xa8b) [0xdfeb5b]
/usr/sbin/mysqld(trans_commit_stmt(THD*, bool)+0x40) [0x11ec930]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x5486) [0x10e5436]
/usr/sbin/mysqld(mysql_parse(THD*, Parser_state*, bool)+0x408) [0x10e7ee8]
/usr/sbin/mysqld() [0x10e8517]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x3968) [0x10ecc38]
/usr/sbin/mysqld(do_command(THD*)+0x27f) [0x10ed2cf]
/usr/sbin/mysqld() [0x1221f98]
/usr/sbin/mysqld() [0x2518c40]
/lib64/libpthread.so.0(+0x82de) [0x7f737aee42de]
/lib64/libc.so.6(clone+0x43) [0x7f7378cbbe83]Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

Here is a minimal test case to cause the crash. On a running cluster (3 nodes in my case) execute:

CREATE DATABASE test;
USE test;
CREATE TABLE `not_ok` (
 `emp_no` int NOT NULL,
 `title` varchar(50),
 `from_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`title`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SHOW INDEXES FROM `not_ok`;

This crahes the server. SHOW TABLE STATUS LIKE 'not_ok' also does the trick.

I did a test with a table without composed keys:

CREATE TABLE `ok` (
 `emp_no` int NOT NULL,
 `title` varchar(50) NOT NULL,
 `from_date` date NOT NULL,
 PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SHOW INDEXES FROM `ok`;

This works fine.

I also checked with only one node started as a normal Mysql server (no wsrep provider). There I was not able to cause the crash.

So my guess is that it has something to do with composite keys and the galera code.

I did some reasearch to find other people having the problem.
I found one closed ticket reporting exactly this crash https://perconadev.atlassian.net/browse/PXC-3376#icft=PXC-3376 and an older stackoverflow post which could be related: https://stackoverflow.com/questions/50648665/using-mysql-workbench-causes-errors-on-galera-cluster

Environment

Ver 8.0.19-10 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel10, Revision 727f180, WSREP version 26.4.3)

AFFECTED CS IDs

PRB0040901

Linked issues

is duplicated by

PXC-3542

Node crashes on INSERT

PXC-3376

Unspecified crash

PXC-3570

MySQL segfault when collecting an item by zabbix_agent2

PXC-3502

Correct condition in thd_binlog_format() function for List Index process

Smart Checklist

Activity

Show:

Sveta Smirnova September 23, 2021 at 12:41 AM

Duplicate of https://perconadev.atlassian.net/browse/PXC-3502#icft=PXC-3502

Lalit Choudhary September 17, 2021 at 11:22 AM

Server version: 8.0.21-12.1 Percona XtraDB Cluster binary (GPL) 8.0.21, Revision 4d973e2, WSREP version 26.4.3

node1 [localhost:22002] {msandbox} (test) > CREATE TABLE `not_ok` (
 -> `emp_no` int NOT NULL,
 -> `title` varchar(50),
 -> `from_date` date NOT NULL,
 -> PRIMARY KEY (`emp_no`,`title`,`from_date`)
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
node1 [localhost:22002] {msandbox} (test) > show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
node1 [localhost:22002] {msandbox} (test) > SHOW index from not_ok;
ERROR 2013 (HY000): Lost connection to MySQL server during query

So original crash reported reproducible with PXC- 8.0.21 consistently, but I don't see crash for PXC 8.0.22 and 8.0.23 with same test.

So it fixed in 8.0.22

Lalit Choudhary September 17, 2021 at 10:46 AM
Edited

I do not see the crash with the latest PXC version 8.0.23 with a reproducible test in https://jira.percona.com/browse/PXC-3455?focusedCommentId=277232&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-277232

Test with PXC 8.0.23:(also tested with pxc 8.0.22)

node1 [localhost:23002] {msandbox} (test) > show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
node1 [localhost:23002] {msandbox} (test) > CREATE TABLE `not_ok` (
 -> `emp_no` int NOT NULL,
 -> `title` varchar(50),
 -> `from_date` date NOT NULL,
 -> PRIMARY KEY (`emp_no`,`title`,`from_date`)
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
 
node1 [localhost:23002] {msandbox} (test) > SHOW index from not_ok;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| not_ok | 0 | PRIMARY | 1 | emp_no | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| not_ok | 0 | PRIMARY | 2 | title | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| not_ok | 0 | PRIMARY | 3 | from_date | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.02 sec)
node1 [localhost:23002] {msandbox} (test) > SHOW TABLE STATUS LIKE 'not_ok';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| not_ok | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-09-17 06:39:31 | NULL | NULL | utf8_unicode_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)
node1 [localhost:23002] {msandbox} (test) > select @version,@comment;
+--------------------+--------------------+
| @version | @comment |
+--------------------+--------------------+
| NULL | NULL |
+--------------------+--------------------+
1 row in set (0.00 sec)

node1 [localhost:23002] {msandbox} (test) > select @@version;
+-------------+
| @@version |
+-------------+
| 8.0.23-14.1 |
+-------------+
1 row in set (0.00 sec)

Lalit Choudhary February 9, 2021 at 11:23 AM

Hi @Martin Correll , @DS

Thank you for test case. Verified as described with PXC 8.0.21 version.

Reproduciable test case:

Setup 3 node cluster (1 node is also fine)

Disable binlog on all nodes.

my.cnf
skip-log-bin = 1
restart nodes:

node1 [localhost:23002] {msandbox} ((none)) > show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)

node1 [localhost:23002] {msandbox} (test) > CREATE TABLE `not_ok` (
 -> `emp_no` int NOT NULL,
 -> `title` varchar(50),
 -> `from_date` date NOT NULL,
 -> PRIMARY KEY (`emp_no`,`title`,`from_date`)
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
node1 [localhost:23002] {msandbox} (test) > SHOW TABLE STATUS LIKE 'not_ok' 
 -> ;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| not_ok | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-02-09 05:52:28 | NULL | NULL | utf8_unicode_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

node1 [localhost:23002] {msandbox} (test) > SHOW index from not_ok;
ERROR 2013 (HY000): Lost connection to MySQL server during query
node1 [localhost:23002] {msandbox} (test) >
 
11:02:12 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Build ID: Not Available
Server Version: 8.0.21-12.1 Percona XtraDB Cluster binary (GPL) 8.0.21, Revision 4d973e2, WSREP version 26.4.3, wsrep_26.4.3
Thread pointer: 0x7f1bb268d000
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 = 7f1bf908dc80 thread_stack 0x46000
/bigdisk/lalit/8.0.21/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1faeece]
/bigdisk/lalit/8.0.21/bin/mysqld(handle_fatal_signal+0x363) [0x1189273]
/lib64/libpthread.so.0(+0xf630) [0x7f1bfca47630]
/bigdisk/lalit/8.0.21/lib/libgalera_smm.so(+0x1f4020) [0x7f1bec0e7020]
/bigdisk/lalit/8.0.21/lib/libgalera_smm.so(+0x20f7d9) [0x7f1bec1027d9]
/bigdisk/lalit/8.0.21/bin/mysqld(wsrep::wsrep_provider_v26::replay(wsrep::ws_handle const&, wsrep::high_priority_service*)+0x29) [0x26df229]
/bigdisk/lalit/8.0.21/bin/mysqld(Wsrep_client_service::replay()+0xa4) [0x11968a4]
/bigdisk/lalit/8.0.21/bin/mysqld(wsrep::transaction::after_statement()+0x295) [0x26dd085]
/bigdisk/lalit/8.0.21/bin/mysqld(wsrep::client_state::after_statement()+0x11a) [0x26bce1a]
/bigdisk/lalit/8.0.21/bin/mysqld(ha_commit_trans(THD*, bool, bool)+0x1122) [0xd6bf32]
/bigdisk/lalit/8.0.21/bin/mysqld(trans_commit_stmt(THD*, bool)+0x34) [0x114fa04]
/bigdisk/lalit/8.0.21/bin/mysqld(mysql_execute_command(THD*, bool)+0x4043) [0x1040f83]
/bigdisk/lalit/8.0.21/bin/mysqld(mysql_parse(THD*, Parser_state*, bool)+0x3f3) [0x1044393]
/bigdisk/lalit/8.0.21/bin/mysqld() [0x104493c]
/bigdisk/lalit/8.0.21/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x399c) [0x104915c]
/bigdisk/lalit/8.0.21/bin/mysqld(do_command(THD*)+0x257) [0x1049807]
/bigdisk/lalit/8.0.21/bin/mysqld() [0x1179288]
/bigdisk/lalit/8.0.21/bin/mysqld() [0x24ac151]
/lib64/libpthread.so.0(+0x7ea5) [0x7f1bfca3fea5]
/lib64/libc.so.6(clone+0x6d) [0x7f1bfa91b98d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED
You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
Writing a core file

node2 [localhost:23003] {msandbox} (test) > show index from `not_ok`;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I do not see a crash for SHOW TABLE STATUS command.

node1 [localhost:23002] {msandbox} (test) > SHOW TABLE STATUS LIKE 'not_ok';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| not_ok | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-02-09 05:52:28 | NULL | NULL | utf8_unicode_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)
node1 [localhost:23002] {msandbox} (test) > SHOW TABLE STATUS FROM mysql;
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+-----------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+-----------------------------------------+
| columns_priv | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | NULL | 2021-02-09 06:09:33 | NULL | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | Column privileges |
| component | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 4194304 | 1 | 2021-02-09 06:09:34 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | 
-+-----------------------------------------+
36 rows in set (0.07 sec)
node1 [localhost:23002] {msandbox} ((none)) > show table status from test;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| not_ok | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-02-09 06:13:03 | NULL | NULL | utf8_unicode_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

Having binlog enabled will not crash the nodes.

node1 [localhost:23002] {msandbox} (test) > SHOW INDEXES FROM `not_ok`;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| not_ok | 0 | PRIMARY | 1 | emp_no | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| not_ok | 0 | PRIMARY | 2 | title | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| not_ok | 0 | PRIMARY | 3 | from_date | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

Martin Correll February 8, 2021 at 12:22 PM

Just to add to my original report: I also ran the servers without binlog.

Duplicate

Details
Assignee
Unassigned
Reporter
Martin Correll
Affects versions
8.0.19-10
8.0.21-12.1
Priority
Medium

Smart Checklist

Created October 13, 2020 at 8:17 AM

Updated March 6, 2024 at 9:22 PM

Resolved September 21, 2021 at 10:57 AM

Cluster crashes when composite keys are queried by SHOW INDEXES

Description

Environment

AFFECTED CS IDs

Linked issues

is duplicated by

Smart Checklist

Activity

Sveta Smirnova September 23, 2021 at 12:41 AM

Lalit Choudhary September 17, 2021 at 11:22 AM

Lalit Choudhary September 17, 2021 at 10:46 AM
Edited

Lalit Choudhary February 9, 2021 at 11:23 AM

Martin Correll February 8, 2021 at 12:22 PM

Details
Assignee
Unassigned
Reporter
Martin Correll
Affects versions
8.0.19-10
8.0.21-12.1
Priority
Medium

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Smart Checklist

Flag notifications

Something's gone wrong

Something's gone wrong

Cluster crashes when composite keys are queried by SHOW INDEXES

Description

Environment

AFFECTED CS IDs

Linked issues

is duplicated by

Smart Checklist

Activity

Sveta Smirnova September 23, 2021 at 12:41 AM

Lalit Choudhary September 17, 2021 at 11:22 AM

Lalit Choudhary September 17, 2021 at 10:46 AMEdited

Lalit Choudhary February 9, 2021 at 11:23 AM

Martin Correll February 8, 2021 at 12:22 PM

DetailsAssigneeUnassignedUnassignedReporterMartin CorrellMartin CorrellAffects versions8.0.19-108.0.21-12.1PriorityMedium

Details

Assignee

Reporter

Affects versions

Priority

Smart ChecklistOpen Smart Checklist

Smart Checklist

Flag notifications

Something's gone wrong

Something's gone wrong

Lalit Choudhary September 17, 2021 at 10:46 AM
Edited

Details
Assignee
Unassigned
Reporter
Martin Correll
Affects versions
8.0.19-10
8.0.21-12.1
Priority
Medium

Smart Checklist