[MDEV-33795] MariaDB segfault on rowid filter query involving generated column - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.11.7, 11.3.2
Fix Version/s: 10.4.34, 10.5.25, 10.11.8, 10.6.18, 11.0.6, 11.1.5, 11.2.4
Component/s: Storage Engine - InnoDB, Virtual Columns
Labels:
None
Environment:
10.11.7 & 11.3.2 on docker linux/arm64
10.11.7 on AWS RDS

mysql client version: Ver 8.1.0 for macos13.3 on arm64 (Homebrew)

Description

Certain queries involving a generated column will cause the mariadb server process to segfault giving output like below before exiting:

240329  5:38:20 [ERROR] mysqld got signal 11 ;

Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed,

something is definitely wrong and this may fail.

Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision: 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=1

max_threads=153

thread_count=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468073 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xffff44000c78

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...

The schema in question looks like

CREATE TABLE `testtable` (

  `a_id` int(11) DEFAULT NULL,

  `b_id` int(11) NOT NULL,

  `c_id` varchar(64) DEFAULT NULL,

  `d_bool` tinyint(1) DEFAULT 1,

  `d_bool_gen` tinyint(1) GENERATED ALWAYS AS (if(`d_bool`,1,NULL)),

  UNIQUE KEY `testkey` (`b_id`,`c_id`,`d_bool_gen`),

  KEY `a_key` (`a_id`)

);

Noting the existence of a unique key constraint involving a generated column. Then a query that looks like

select * from testtable where b_id = 10 and a_id = 10;

can trigger the fault seen above. Explaining the above query gives the output below which is why I suspect the rowid filter is involved

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

| id   | select_type | table     | type       | possible_keys | key           | key_len | ref   | rows     | Extra                           |

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

|    1 | SIMPLE      | testtable | ref|filter | testkey,a_key | testkey|a_key | 4|5     | const | 202 (0%) | Using where; Using rowid filter |

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

To help with reproduction I have found that the following synthetic data is sufficient to trigger the fault

INSERT INTO testtable (a_id, b_id, c_id, d_bool)

SELECT MOD(seq, 1000), MOD(seq, 1001), IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(seq), 64)), MOD(seq, 2) FROM seq_1_to_100000;

INSERT INTO testtable (a_id, b_id, c_id, d_bool)

SELECT 10, 10, IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(-seq), 64)), MOD(seq, 2) FROM seq_1_to_100;

INSERT INTO testtable (a_id, b_id, c_id, d_bool) VALUES

(10, 10, '', 0),

(10, 10, '', 1);

With that synthetic data in place, repeatedly querying the below query will eventually cause the segfault.

select * from testtable where b_id = 10 and a_id = 10;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

reprod.sh
1 kB
2024-03-29 06:05

Issue Links

relates to

MDEV-31154 Fatal InnoDB error or assertion `!is_v' failure upon multi-update with indexed virtual column

Closed

Activity

Ascending order - Click to sort in descending order

Mark Gordon created issue - 2024-03-29 05:43

Mark Gordon made changes - 2024-03-29 05:45

Field	Original Value	New Value
Description	Certain queries involving a generated column will cause the mariadb server process to segfault giving output like below before exiting: ``` 240329 5:38:20 [ERROR] mysqld got signal 11 ; Sorry, we probably made a mistake, and this is a bug. Your assistance in bug reporting will enable us to fix this for the next release. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision: 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42 key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=153 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)max_threads = 468073 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0xffff44000c78 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... ``` The schema in question looks like ```sql CREATE TABLE `testtable` ( `a_id` int(11) DEFAULT NULL, `b_id` int(11) NOT NULL, `c_id` varchar(64) DEFAULT NULL, `d_bool` tinyint(1) DEFAULT 1, `d_bool_gen` tinyint(1) GENERATED ALWAYS AS (if(`d_bool`,1,NULL)), UNIQUE KEY `testkey` (`b_id`,`c_id`,`d_bool_gen`), KEY `a_key` (`a_id`) ); ``` Noting the existence of a unique key constraint involving a generated column. Then a query that looks like ```sql select from testtable where b_id = 10 and a_id = 10; ``` can trigger the fault seen above. Explaining the above query gives the output below which is why I suspect the rowid filter is involved ``` +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ \| 1 \| SIMPLE \| testtable \| ref\|filter \| testkey,a_key \| testkey\|a_key \| 4\|5 \| const \| 202 (0%) \| Using where; Using rowid filter \| +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ ``` To help with reproduction I have found that the following synthetic data is sufficient to trigger the fault ```sql INSERT INTO testtable (a_id, b_id, c_id, d_bool) SELECT MOD(seq, 1000), MOD(seq, 1001), IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(seq), 64)), MOD(seq, 2) FROM seq_1_to_100000; INSERT INTO testtable (a_id, b_id, c_id, d_bool) SELECT 10, 10, IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(-seq), 64)), MOD(seq, 2) FROM seq_1_to_100; INSERT INTO testtable (a_id, b_id, c_id, d_bool) VALUES (10, 10, '', 0), (10, 10, '', 1); ``` With that synthetic data in place, repeatedly querying `select * from testtable where b_id = 10 and a_id = 10;` will eventually cause the segfault.	Certain queries involving a generated column will cause the mariadb server process to segfault giving output like below before exiting: {noformat} 240329 5:38:20 [ERROR] mysqld got signal 11 ; Sorry, we probably made a mistake, and this is a bug. Your assistance in bug reporting will enable us to fix this for the next release. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision: 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42 key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=153 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)max_threads = 468073 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0xffff44000c78 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... {noformat} The schema in question looks like {code:sql} CREATE TABLE `testtable` ( `a_id` int(11) DEFAULT NULL, `b_id` int(11) NOT NULL, `c_id` varchar(64) DEFAULT NULL, `d_bool` tinyint(1) DEFAULT 1, `d_bool_gen` tinyint(1) GENERATED ALWAYS AS (if(`d_bool`,1,NULL)), UNIQUE KEY `testkey` (`b_id`,`c_id`,`d_bool_gen`), KEY `a_key` (`a_id`) ); {code} Noting the existence of a unique key constraint involving a generated column. Then a query that looks like ```sql select from testtable where b_id = 10 and a_id = 10; ``` can trigger the fault seen above. Explaining the above query gives the output below which is why I suspect the rowid filter is involved ``` +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ \| 1 \| SIMPLE \| testtable \| ref\|filter \| testkey,a_key \| testkey\|a_key \| 4\|5 \| const \| 202 (0%) \| Using where; Using rowid filter \| +------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+ ``` To help with reproduction I have found that the following synthetic data is sufficient to trigger the fault ```sql INSERT INTO testtable (a_id, b_id, c_id, d_bool) SELECT MOD(seq, 1000), MOD(seq, 1001), IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(seq), 64)), MOD(seq, 2) FROM seq_1_to_100000; INSERT INTO testtable (a_id, b_id, c_id, d_bool) SELECT 10, 10, IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(-seq), 64)), MOD(seq, 2) FROM seq_1_to_100; INSERT INTO testtable (a_id, b_id, c_id, d_bool) VALUES (10, 10, '', 0), (10, 10, '', 1); ``` With that synthetic data in place, repeatedly querying `select * from testtable where b_id = 10 and a_id = 10;` will eventually cause the segfault.

Mark Gordon made changes - 2024-03-29 05:46

Description

Certain queries involving a generated column will cause the mariadb server process to segfault giving output like below before exiting:

{noformat}
240329 5:38:20 [ERROR] mysqld got signal 11 ;
Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision: 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468073 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xffff44000c78
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...
{noformat}

The schema in question looks like

{code:sql}
CREATE TABLE `testtable` (
  `a_id` int(11) DEFAULT NULL,
  `b_id` int(11) NOT NULL,
  `c_id` varchar(64) DEFAULT NULL,
  `d_bool` tinyint(1) DEFAULT 1,
  `d_bool_gen` tinyint(1) GENERATED ALWAYS AS (if(`d_bool`,1,NULL)),
  UNIQUE KEY `testkey` (`b_id`,`c_id`,`d_bool_gen`),
  KEY `a_key` (`a_id`)
);
{code}

Noting the existence of a unique key constraint involving a generated column. Then a query that looks like

```sql
select * from testtable where b_id = 10 and a_id = 10;
```

can trigger the fault seen above. Explaining the above query gives the output below which is why I suspect the rowid filter is involved

```
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
| 1 | SIMPLE | testtable | ref|filter | testkey,a_key | testkey|a_key | 4|5 | const | 202 (0%) | Using where; Using rowid filter |
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
```

To help with reproduction I have found that the following synthetic data is sufficient to trigger the fault

```sql
INSERT INTO testtable (a_id, b_id, c_id, d_bool)
SELECT MOD(seq, 1000), MOD(seq, 1001), IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(seq), 64)), MOD(seq, 2) FROM seq_1_to_100000;
INSERT INTO testtable (a_id, b_id, c_id, d_bool)
SELECT 10, 10, IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(-seq), 64)), MOD(seq, 2) FROM seq_1_to_100;
INSERT INTO testtable (a_id, b_id, c_id, d_bool) VALUES
(10, 10, '', 0),
(10, 10, '', 1);
```

With that synthetic data in place, repeatedly querying `select * from testtable where b_id = 10 and a_id = 10;` will eventually cause the segfault.

Certain queries involving a generated column will cause the mariadb server process to segfault giving output like below before exiting:

{noformat}
240329 5:38:20 [ERROR] mysqld got signal 11 ;
Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision: 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468073 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xffff44000c78
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...
{noformat}

The schema in question looks like

{code:sql}
CREATE TABLE `testtable` (
  `a_id` int(11) DEFAULT NULL,
  `b_id` int(11) NOT NULL,
  `c_id` varchar(64) DEFAULT NULL,
  `d_bool` tinyint(1) DEFAULT 1,
  `d_bool_gen` tinyint(1) GENERATED ALWAYS AS (if(`d_bool`,1,NULL)),
  UNIQUE KEY `testkey` (`b_id`,`c_id`,`d_bool_gen`),
  KEY `a_key` (`a_id`)
);
{code}

Noting the existence of a unique key constraint involving a generated column. Then a query that looks like

{code:sql}
select * from testtable where b_id = 10 and a_id = 10;
{code}

can trigger the fault seen above. Explaining the above query gives the output below which is why I suspect the rowid filter is involved

{noformat}
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
| 1 | SIMPLE | testtable | ref|filter | testkey,a_key | testkey|a_key | 4|5 | const | 202 (0%) | Using where; Using rowid filter |
+------+-------------+-----------+------------+---------------+---------------+---------+-------+----------+---------------------------------+
{noformat}

To help with reproduction I have found that the following synthetic data is sufficient to trigger the fault

{code:sql}
INSERT INTO testtable (a_id, b_id, c_id, d_bool)
SELECT MOD(seq, 1000), MOD(seq, 1001), IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(seq), 64)), MOD(seq, 2) FROM seq_1_to_100000;
INSERT INTO testtable (a_id, b_id, c_id, d_bool)
SELECT 10, 10, IF(MOD(seq, 3) = 0, NULL, LEFT(MD5(-seq), 64)), MOD(seq, 2) FROM seq_1_to_100;
INSERT INTO testtable (a_id, b_id, c_id, d_bool) VALUES
(10, 10, '', 0),
(10, 10, '', 1);
{code}

With that synthetic data in place, repeatedly querying the below query will eventually cause the segfault.

{code:sql}
select * from testtable where b_id = 10 and a_id = 10;
{code}

Mark Gordon made changes - 2024-03-29 06:05

Attachment

reprod.sh [ 73325 ]

Marko Mäkelä made changes - 2024-03-31 09:56

Link

This issue relates to ~~MDEV-31154~~ [ ~~MDEV-31154~~ ]

Debarun Banerjee made changes - 2024-04-01 06:45

Assignee

Debarun Banerjee [ JIRAUSER54513 ]

Debarun Banerjee made changes - 2024-04-01 06:50

Component/s		Storage Engine - InnoDB [ 10129 ]
Component/s		Virtual Columns [ 10803 ]
Fix Version/s		10.4.34 [ 29625 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

JiraAutomate made changes - 2024-04-01 06:50

Fix Version/s		10.5.25 [ 29626 ]
Fix Version/s		10.6.18 [ 29627 ]
Fix Version/s		10.11.8 [ 29630 ]
Fix Version/s		11.0.6 [ 29628 ]
Fix Version/s		11.1.5 [ 29629 ]
Fix Version/s		11.2.4 [ 29631 ]

People

Assignee:: Debarun Banerjee

Reporter:: Mark Gordon

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-03-29 05:43

Updated:: 2024-04-01 06:50

Resolved:: 2024-04-01 06:50

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration