Details
Major
Description
MariaDB [test]> CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT, KEY(i)) ENGINE=MyISAM; Query OK, 0 rows affected (0.22 sec) MariaDB [test]> INSERT INTO t1 (i) VALUES (100),(200); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> CREATE TABLE t2 (j INT) ENGINE=MyISAM; Query OK, 0 rows affected (0.26 sec) MariaDB [test]> INSERT INTO t2 VALUES (10),(20); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> EXPLAIN INSERT INTO t1 (i) SELECT j FROM t2; +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) MariaDB [test]> INSERT INTO t1 (i) VALUES (300); Query OK, 1 row affected (0.00 sec)
MariaDB [test]> CHECK TABLE t1; +---------+-------+----------+-------------------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+-------------------+ | test.t1 | check | error | Found 2 keys of 3 | | test.t1 | check | error | Corrupt | +---------+-------+----------+-------------------+ 2 rows in set (0.00 sec)
Test case
DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT, KEY(i)) ENGINE=MyISAM; INSERT INTO t1 (i) VALUES (100),(200); CREATE TABLE t2 (j INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (10),(20); EXPLAIN INSERT INTO t1 (i) SELECT j FROM t2; INSERT INTO t1 (i) VALUES (300); CHECK TABLE t1; DROP TABLE t1, t2;
Reproducible on the current 10.0 (pre-10.0.20) and 10.1 trees, as well as 10.0.10 release; I didn't check further back.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Another observation: putting FLUSH TABLES after EXPLAIN INSERT ... SELECT makes the problem go away:
MariaDB [j2]> flush tables; Query OK, 0 rows affected (0.04 sec) MariaDB [j2]> INSERT INTO t1 (i) VALUES (300); Query OK, 1 row affected (2.73 sec) MariaDB [j2]> CHECK TABLE t1; +-------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+-------+----------+----------+ | j2.t1 | check | status | OK | +-------+-------+----------+----------+
Looks like EXPLAIN INSERT ... SELECT is not de-initializing the table properly
After some debugging: EXPLAIN INSERT ... SELECT doesn't call end_bulk_insert. The sequence of calls is as follows:
h->start_bulk_insert()
...
h->reset();
and it seems that with MyISAM, h->reset() doesn't cancel the action of start_bulk_insert().
The problem is not affected by the candidate fix for MDEV-6223 and MDEV-8321. It's a different issue, it seems.