Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
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
|