[MDEV-11412] Ensure that table is truly dropped when using DROP TABLE Created: 2016-11-29  Updated: 2023-03-02  Resolved: 2020-06-15

Status: Closed
Project: MariaDB Server
Component/s: Server
Fix Version/s: 10.5.4

Type: Task Priority: Critical
Reporter: Sergey Vojtovich Assignee: Michael Widenius
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Blocks
blocks MDEV-23570 deprecate keep_files_on_create Closed
Problem/Incident
causes MDEV-23549 CREATE fails after DROP without FRM Closed
causes MDEV-30170 no error for DROP TABLE and no warnin... Closed
Relates
relates to MCOL-4164 Multi-table DROP Closed
relates to MDEV-25373 DROP TABLE doesn't raise error while ... Closed
relates to MDEV-26966 The parameter innodb_force_load_corru... Closed
relates to MDEV-15185 PARTITION files removed, but not from... Closed
relates to MDEV-16417 Store Foreign Key metadata outside of... In Review
relates to MDEV-23000 Ensure we get a warning from THD::dro... Closed
relates to MDEV-23185 CREATE OR REPLACE TABLE is less robus... Open
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MDEV-22820 Bogus "Unknown table" warnings produc... Technical task Closed Michael Widenius  
MDEV-22826 Presence of Spider prevents tables fr... Technical task Closed Michael Widenius  
MDEV-22838 Forced DROP does not work when InnoDB... Technical task Closed Marko Mäkelä  
Epic Link: AliSQL patches

 Description   

This task is based on a patch/idea from Ali-SQL: "[Feature] Issue#34 Support force drop table" and a patch from Tencent related to the same problem.

--------
 
Usually the meta data is not consistent between server layer and InnoDB layer.
 
So we supply a new syntax to clear the meta data.
 
 
 
Usage:
 
------
 
1. DROP TABLE FORCE t1;
 
2. ALTER TABLE t1 DROP PARTITION force p2;

https://github.com/alibaba/AliSQL/commit/fc7e697b8b34a7751da8c8dca54ba152601d9ee5

In the end we decided to support the idea of FORCE but without the FORCE keyword:

  • If a .frm table doesn't exists, try dropping the table from all storage engines.
  • If the .frm table exists but the table does not exist in the engine try dropping the table from all storage engines.
  • Update storage engines using many table files (.CSV, MyISAM, Aria) to succeed with the drop even if some of the files are missing.
  • Add HTON_AUTOMATIC_DELETE_TABLE to handlerton's where delete_table() is not needed and always succeed. This is used by ha_delete_table_force() to know which handlers to ignore when trying to drop a table without a .frm file.

The disadvantage of this solution is that a DROP TABLE on a non existing table will be a bit slower as we have to ask all active storage engines if they know anything about the table.

Things left to do in another MDEV:

  • Handle dropping of partitioned tables without .par file or .frm
  • Verify that one can drop partitions with a DROP TABLE #mysql50#...

 Comments   
Comment by Elena Stepanova [ 2020-06-10 ]

A note just for the record (it has already been discussed and confirmed to be expected).

1) If .par file is missing, orphan per-partition data files don't get removed upon DROP TABLE:

--source include/have_partition.inc
 
 
 
--let $datadir= `select @@datadir`
 
create table t1 (a int) partition by hash(a) partitions 2;
 
 
 
--remove_file $datadir/test/t1.par
 
drop table t1;
 
--list_files $datadir/test/


drop table t1;
 
Warnings:
 
Warning	1017	Can't find file: './test/t1.par' (errno: 2 "No such file or directory")
 
db.opt
 
t1#P#p0.MYD
 
t1#P#p0.MYI
 
t1#P#p1.MYD
 
t1#P#p1.MYI

2) If .TRG file is missing, orphan .TRN files don't get removed upon DROP TABLE

--let $datadir= `select @@datadir`
 
create table t1 (a int);
 
create trigger tr after insert on t1 for each row begin end;
 
 
 
--remove_file $datadir/test/t1.TRG
 
drop table t1;
 
--list_files $datadir/test/


drop table t1;
 
db.opt
 
tr.TRN

Comment by Daniel Lenski [ 2021-11-05 ]

Has this fix been verified to “play nicely” with the new S3 Storage Engine added in 10.5.4?

When an existing table is converted to S3 Storage Engine (with ALTER TABLE test ENGINE=S3), only the .frm file is "left behind" in local storage, but the other files (e.g. .ibd) are removed
(source in the docs).

# Before ALTER TABLE test ENGINE=S3;
 
[test]$ ll -alh test.*
 
-rw-rw---- 1 user grp 461 Sep 24 01:24 test.frm
 
-rw-rw---- 1 user grp 96K Sep 24 01:24 test.ibd
 
 
 
# After ALTER TABLE test ENGINE=S3;
 
[test]$ ll -alh test.*
 
-rw-rw---- 1 user grp 457 Sep 24 01:25 test.frm

Comment by Sergei Golubchik [ 2021-11-07 ]

Yes, there are test cases (for example here) that create and drop S3 tables. They're run in CI, so we'd notice if DROP wouldn't work.

Comment by Michael Widenius [ 2022-12-13 ]

Regarding S3.
The .frm file is left (or copied from the S3 storage on discovery) so that the server can easily find it.
When the .frm is opened, the server also checks from the S3 storage that the .frm is up to date. If not, it's updated or removed.

Comment by Michael Widenius [ 2022-12-13 ]

There are patches for backporting this to 10.2, but would advice against doing that as there has been a lot of changes in the code between 10.5 and 10.2 and backporting these to 10.2 can cause other unforeseen larger problems.
If anyone has a problem with this in 10.2, then the best option is to upgrade to 10.5 or 10.6.

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