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

XML

Word

Printable

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

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.