Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11412 Ensure that table is truly dropped when using DROP TABLE
  3. MDEV-22838

Forced DROP does not work when InnoDB has inconsistent information about table

    XMLWordPrintable

    Details

      Description

      I think this case describes a reasonably realistic scenario (as far as realistic goes for this functionality). It imitates a presence of the system tablespace created in an older version, where innodb_file_per_table=0 was still default or common, but some .frm files got missing in time.

      So, the test first creates a table in the system tablespace and "loses" the .frm file.
      Then, already in a modern setup with innodb_file_per_table, a user unaware of the remains in the system tablespace attempts to create a namesake. The attempt fails with "Table already exists". But before failing, CREATE generates an .ibd file. So, now we don't have any .frm files for the table, but two pieces of data – the old one in the system tablespace and the new one in .ibd.

      The extended version of DROP TABLE removes the first one, but the engine apparently loses information about the other one, so the next DROP TABLE fails as if there is nothing left to drop, even though the .ibd file exists, and of course prevents further table creation.

      --source include/have_innodb.inc
       
      --let $datadir= `select @@datadir`
       
      set global innodb_file_per_table= 0;
      create table t1 (a int) engine=InnoDB;
       
      --echo # Removing .frm file of t1 stored in InnoDB system tablespace
      --remove_file $datadir/test/t1.frm
      --echo # There are no t1* files in the datadir at this point
      --list_files $datadir/test
       
      set global innodb_file_per_table= 1;
      --error ER_TABLE_EXISTS_ERROR
      create table t1 (a int) engine=InnoDB;
       
      --echo # CREATE failed, but t1.ibd file was created:
      --list_files $datadir/test
       
      --echo # So now probably InnoDB has orphan pieces of two t1 tables,
      --echo # the old one in the system tablespace and .ibd file in the datadir
       
      --echo # First DROP works, probably removes the older table:
      drop table t1;
      --echo # Second DROP fails, and the .ibd file is still there:
      --error ER_BAD_TABLE_ERROR
      drop table t1;
       
      --list_files $datadir/test
      

      bb-10.5-monty 645f37e49cb

      # First DROP works, probably removes the older table:
      drop table t1;
      # Second DROP fails, and the .ibd file is still there:
      drop table t1;
      ERROR 42S02: Unknown table 'test.t1'
      db.opt
      t1.ibd
      

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration