Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18695

Corruption (?) with multi-value insert and stored virtual column index

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              dragle Daniel Ragle
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: