[MDEV-18695] Corruption (?) with multi-value insert and stored virtual column index Created: 2019-02-22  Updated: 2019-02-28  Resolved: 2019-02-28

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.2.22, 10.3.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Ragle Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: persistent, virtual_columns
Environment:

CentOS 6/CentOS 7

Issue Links:
Duplicate
duplicates MDEV-18486 Database crash on a table with indexe... Closed

 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]



 Comments   
Comment by Alice Sherepa [ 2019-02-28 ]

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

Comment by Daniel Ragle [ 2019-02-28 ]

Thank you, will do.

Generated at Thu Feb 08 08:46:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.