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

MyISAM or Aria table gets corrupted after EXPLAIN INSERT and INSERT

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL)
    • 10.0.22, 10.1.8
    • OTHER
    • 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.

      Attachments

        Activity

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

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

          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

          psergei Sergei Petrunia added a comment - - edited 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().

          psergei Sergei Petrunia added a comment - 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().
          psergei Sergei Petrunia added a comment - http://lists.askmonty.org/pipermail/commits/2015-October/008486.html .

          OK to push

          sanja Oleksandr Byelkin added a comment - OK to push

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.