[MDEV-8299] MyISAM or Aria table gets corrupted after EXPLAIN INSERT and INSERT Created: 2015-06-11  Updated: 2015-10-06  Resolved: 2015-10-06

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.0.22, 10.1.8

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by Sergei Petrunia [ 2015-10-05 ]

The problem is not affected by the candidate fix for MDEV-6223 and MDEV-8321. It's a different issue, it seems.

Comment by Sergei Petrunia [ 2015-10-05 ]

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

Comment by Sergei Petrunia [ 2015-10-05 ]

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().

Comment by Sergei Petrunia [ 2015-10-05 ]

http://lists.askmonty.org/pipermail/commits/2015-October/008486.html.

Comment by Oleksandr Byelkin [ 2015-10-06 ]

OK to push

Generated at Thu Feb 08 07:26:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.