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

Stray .ibd file left behind after killing "CREATE TABLE ... AS SELECT ..."

    XMLWordPrintable

Details

    Description

      Under certain conditions, when killing a CREATE TABLE ... AS SELECT query, the InnoDB .ibd file stays behind even after the killed query has completely been rolled back, only the .frm file is removed, and the table still exists in the InnoDB data dictionary, so that DROP fails with 'Unknown table', but CREATE using the same name says 'Table already exists'

      This only seems to happen when two preconditions are met:

      • at least one table join in the table
      • the joined tables are of a certain minimum size

      How to reproduce:

      create table t1 (id int primary key auto_increment);
      insert into t1 select null;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      insert into t1 select null from t1;
      create table t2 as select x.id as x_id, y.id as y_id from t1 x, t1 y;
      CTRL-C 
      CTRL-C
      

      After killing the query with CTRL-C or KILL, the t2.frm file is gone,
      but the t2.ibd file remains, and even after removing it manually
      t2 can't be recreated as it still exists in the InnoDB data directory

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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