[MDEV-18695] Corruption (?) with multi-value insert and stored virtual column index - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.2.22, 10.3.13
Fix Version/s: N/A
Component/s: Virtual Columns
Labels:
- persistent
- virtual_columns
Environment:
CentOS 6/CentOS 7

Description

Creating a table with a persistent (stored) virtual column, adding an index on that column, then performing a multi-value insert on the table with 100 rows or more results in table corruption (or at the very least, incorrect/inconsistent results when accessing the table). The query cache is turned off (see variable display below).

This also breaks replication (with STATEMENT or MIXED) from an earlier (<10.2) to later version; since the same multi-value insert statement will evidently produce differing results.

If anyone has already run into this (didn't see anything that looked quite the same in search) I am curious to hear what workarounds you may have found (other than "don't do that" )

In the following example, note that:

inserting less than 100 rows works as expected
creating the table without an index on the stored column works as expected
inserting the records one at a time individually works as expected

MariaDB [MyDB]> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected (0.000 sec)

MariaDB [MyDB]> CREATE TABLE t1 (

    ->     SomeNumber INT(10) NOT NULL DEFAULT 0,

    ->     SomeChar   VARCHAR(10) GENERATED ALWAYS AS ( HEX(SomeNumber) ) STORED,

    ->     PRIMARY KEY ( SomeNumber ),

    ->     KEY ( SomeChar )

    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.002 sec)

MariaDB [MyDB]> DESCRIBE t1;

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

| Field      | Type        | Null | Key | Default | Extra            |

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

| SomeNumber | int(10)     | NO   | PRI | 0       |                  |

| SomeChar   | varchar(10) | YES  | MUL | NULL    | STORED GENERATED |

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

2 rows in set (0.001 sec)

MariaDB [MyDB]> INSERT INTO t1 ( SomeNumber ) VALUES

    ->         ( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),(10),

    ->         (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    ->         (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),

    ->         (31),(32),(33),(34),(35),(36),(37),(38),(39),(40),

    ->         (41),(42),(43),(44),(45),(46),(47),(48),(49),(50),

    ->         (51),(52),(53),(54),(55),(56),(57),(58),(59),(60),

    ->         (61),(62),(63),(64),(65),(66),(67),(68),(69),(70),

    ->         (71),(72),(73),(74),(75),(76),(77),(78),(79),(80),

    ->         (81),(82),(83),(84),(85),(86),(87),(88),(89),(90),

    ->         (91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

Query OK, 100 rows affected (0.001 sec)

Records: 100  Duplicates: 0  Warnings: 0

MariaDB [MyDB]> DESCRIBE t1;

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

| Field      | Type        | Null | Key | Default | Extra            |

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

| SomeNumber | int(10)     | NO   | PRI | 100     |                  |

| SomeChar   | varchar(10) | YES  | MUL | 64      | STORED GENERATED |

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

2 rows in set (0.001 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1;

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

| COUNT(*) |

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

|      100 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 WHERE SomeNumber = 100;

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

| COUNT(*) |

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

|        1 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 WHERE SomeNumber != 100;

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

| COUNT(*) |

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

|        0 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 WHERE SomeNumber IS NULL;

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

| COUNT(*) |

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

|        0 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 FORCE INDEX (PRIMARY) WHERE SomeNumber = 100;

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

| COUNT(*) |

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

|        1 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 FORCE INDEX (PRIMARY) WHERE SomeNumber != 100;

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

| COUNT(*) |

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

|        0 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT COUNT(*) FROM t1 FORCE INDEX (PRIMARY) WHERE SomeNumber IS NULL;

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

| COUNT(*) |

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

|        0 |

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

1 row in set (0.000 sec)

MariaDB [MyDB]> SELECT * FROM t1;

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

| SomeNumber | SomeChar |

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

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

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

100 rows in set (0.000 sec)

MariaDB [MyDB]> SELECT * FROM t1 FORCE INDEX (PRIMARY);

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

| SomeNumber | SomeChar |

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

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

|        100 | 64       |

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

100 rows in set (0.000 sec)

MariaDB [MyDB]> SHOW VARIABLES LIKE 'version';

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

| Variable_name | Value           |

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

| version       | 10.3.13-MariaDB |

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

1 row in set (0.001 sec)

MariaDB [MyDB]> SHOW VARIABLES LIKE '%query_cache%';

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

| Variable_name                | Value   |

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

| have_query_cache             | YES     |

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 0       |

| query_cache_strip_comments   | OFF     |

| query_cache_type             | OFF     |

| query_cache_wlock_invalidate | OFF     |

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

7 rows in set (0.001 sec)

my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

my.cnf.d/server.cnf

[server]

[mysqld]

[embedded]

[mysqld-5.5]

[mariadb]

gtid-domain-id=1

binlog_checksum=NONE

secure_auth=1

max_allowed_packet=256M

group_concat_max_len=256M

sort_buffer_size=2M

read_buffer_size=128K

read_rnd_buffer_size=64M

myisam_sort_buffer_size=256M

skip-external-locking

myisam_block_size=4096

concurrent_insert=2

query_cache_type=0

query_cache_size=0

max_connect_errors=50

default-storage-engine=MyISAM

tmp_table_size=256M

max_heap_table_size=256M

tmpdir=/dev/shm

thread_cache_size=50

delay-key-write=OFF

skip-innodb

loose-innodb-trx=0

loose-innodb-locks-unsafe-for-binlog=0

loose-innodb-lock-waits=0

loose-innodb-cmp-per-index-enabled=0

loose-innodb-cmp-reset=0

loose-innodb-cmpmem-reset=0

loose-innodb-buffer-page-lru=0

loose-innodb-buffer-pool-stats=0

max_connections=250

table_open_cache=3072

table_definition_cache=2048

open_files_limit=8192

key_buffer_size=2G

key_cache_division_limit=20

[mariadb-5.5]

[mariadb-10.0]

Attachments

Issue Links

duplicates

MDEV-18486 Database crash on a table with indexed virtual column

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2019-02-28 10:52

Thanks for the report, reproducible as described on 10.2-10.4
The problem is the same as ~~MDEV-18486~~, on debug build with ASAN:

10.2 cac14b92252b3e7bcbeb
Version: '10.2.23-MariaDB-debug-log'
=================================================================
==27458==ERROR: AddressSanitizer: heap-use-after-free on address 0x611000052b49 at pc 0x558e88a31035 bp 0x7f8324351440 sp 0x7f8324351430
READ of size 4 at 0x611000052b49 thread T27
#0 0x558e88a31034 in Field_long::val_str(String, String) /10.2/sql/field.cc:4350
#1 0x558e8831a521 in Field::val_str(String*) /10.2/sql/field.h:866
#2 0x558e8831548f in Protocol_text::store(Field*) /10.2/sql/protocol.cc:1245
#3 0x558e88afbc24 in Item_field::send(Protocol, String) /10.2/sql/item.cc:7054
#4 0x558e88312f33 in Protocol::send_result_set_row(List<Item>*) /10.2/sql/protocol.cc:979
#5 0x558e8842cd1d in select_send::send_data(List<Item>&) /10.2/sql/sql_class.cc:2710
#6 0x558e885e7796 in end_send /10.2/sql/sql_select.cc:19930
#7 0x558e885e085f in evaluate_join_record /10.2/sql/sql_select.cc:18978
#8 0x558e885df42e in sub_select(JOIN, st_join_table, bool) /10.2/sql/sql_select.cc:18758
#9 0x558e885dd8ff in do_select /10.2/sql/sql_select.cc:18302
#10 0x558e8858021d in JOIN::exec_inner() /10.2/sql/sql_select.cc:3623
#11 0x558e8857df23 in JOIN::exec() /10.2/sql/sql_select.cc:3418
#12 0x558e88581270 in mysql_select(THD, TABLE_LIST, unsigned int, List<Item>&, Item, unsigned int, st_order, st_order, Item, st_order, unsigned long long, select_result, st_select_lex_unit, st_select_lex) /10.2/sql/sql_select.cc:3818
#13 0x558e88560a3c in handle_select(THD, LEX, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
#14 0x558e884e75e2 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6484
#15 0x558e884d45df in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3490
#16 0x558e884efd46 in mysql_parse(THD, char, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8018
#17 0x558e884cb232 in dispatch_command(enum_server_command, THD, char, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1829
#18 0x558e884c8361 in do_command(THD*) /10.2/sql/sql_parse.cc:1379
#19 0x558e887f096c in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1336
#20 0x558e887f0374 in handle_one_connection /10.2/sql/sql_connect.cc:1242
#21 0x558e899a05c7 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
#22 0x7f833b92a6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
#23 0x7f833adbf41c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

I am closing this report, but will add the test case, if you'd like to follow the progress, please, watch ~~MDEV-18486~~

Alice Sherepa added a comment - 2019-02-28 10:52 Thanks for the report, reproducible as described on 10.2-10.4 The problem is the same as MDEV-18486 , on debug build with ASAN: 10.2 cac14b92252b3e7bcbeb Version: '10.2.23-MariaDB-debug-log' ================================================================= ==27458==ERROR: AddressSanitizer: heap-use-after-free on address 0x611000052b49 at pc 0x558e88a31035 bp 0x7f8324351440 sp 0x7f8324351430 READ of size 4 at 0x611000052b49 thread T27 #0 0x558e88a31034 in Field_long::val_str(String*, String*) /10.2/sql/field.cc:4350 #1 0x558e8831a521 in Field::val_str(String*) /10.2/sql/field.h:866 #2 0x558e8831548f in Protocol_text::store(Field*) /10.2/sql/protocol.cc:1245 #3 0x558e88afbc24 in Item_field::send(Protocol*, String*) /10.2/sql/item.cc:7054 #4 0x558e88312f33 in Protocol::send_result_set_row(List<Item>*) /10.2/sql/protocol.cc:979 #5 0x558e8842cd1d in select_send::send_data(List<Item>&) /10.2/sql/sql_class.cc:2710 #6 0x558e885e7796 in end_send /10.2/sql/sql_select.cc:19930 #7 0x558e885e085f in evaluate_join_record /10.2/sql/sql_select.cc:18978 #8 0x558e885df42e in sub_select(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18758 #9 0x558e885dd8ff in do_select /10.2/sql/sql_select.cc:18302 #10 0x558e8858021d in JOIN::exec_inner() /10.2/sql/sql_select.cc:3623 #11 0x558e8857df23 in JOIN::exec() /10.2/sql/sql_select.cc:3418 #12 0x558e88581270 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.2/sql/sql_select.cc:3818 #13 0x558e88560a3c in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376 #14 0x558e884e75e2 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6484 #15 0x558e884d45df in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3490 #16 0x558e884efd46 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8018 #17 0x558e884cb232 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1829 #18 0x558e884c8361 in do_command(THD*) /10.2/sql/sql_parse.cc:1379 #19 0x558e887f096c in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1336 #20 0x558e887f0374 in handle_one_connection /10.2/sql/sql_connect.cc:1242 #21 0x558e899a05c7 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862 #22 0x7f833b92a6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9) #23 0x7f833adbf41c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c) I am closing this report, but will add the test case, if you'd like to follow the progress, please, watch MDEV-18486

Daniel Ragle added a comment - 2019-02-28 15:42

Thank you, will do.

Daniel Ragle added a comment - 2019-02-28 15:42 Thank you, will do.

People

Assignee:: Unassigned

Reporter:: Daniel Ragle

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-02-22 16:56

Updated:: 2019-02-28 15:42

Resolved:: 2019-02-28 10:53

Git Integration

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

MariaDB Server