[MDEV-11412] Ensure that table is truly dropped when using DROP TABLE - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.5.4
Component/s: Server
Labels:
None

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#...

Attachments

Issue Links

blocks

MDEV-23570 deprecate keep_files_on_create

Closed

causes

MDEV-23549 CREATE fails after DROP without FRM

Closed

MDEV-30170 no error for DROP TABLE and no warning for DROP TABLE IF EXISTS with absent table when Spider plugin is included

Closed

MDEV-34205 ASAN stack-buffer-overflow in strxnmov | frm_file_exists

Closed

relates to

MCOL-4164 Multi-table DROP

Closed

MDEV-25373 DROP TABLE doesn't raise error while dropping non-existing table in MariaDB 10.5.9 when OQGraph SE is loaded to the server

Closed

MDEV-26966 The parameter innodb_force_load_corrupted makes no sense

Closed

MDEV-15185 PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS_TABLES

Closed

MDEV-16417 Store Foreign Key metadata outside of InnoDB

In Review

MDEV-23000 Ensure we get a warning from THD::drop_temporary_table() in case of disk errors

Closed

MDEV-23185 CREATE OR REPLACE TABLE is less robust than DROP TABLE + CREATE TABLE

Open

(6 relates to)

Sub-Tasks

1.	Bogus "Unknown table" warnings produced upon attempt to drop parent table referenced by FK	Closed	Michael Widenius
2.	Presence of Spider prevents tables from being force-deleted from other engines	Closed	Michael Widenius
3.	Forced DROP does not work when InnoDB has inconsistent information about table	Closed	Marko Mäkelä

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2020-06-10 12:25

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

Elena Stepanova added a comment - 2020-06-10 12:25 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

Daniel Lenski (Inactive) added a comment - 2021-11-05 23:26 - edited

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

Daniel Lenski (Inactive) added a comment - 2021-11-05 23:26 - edited 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

Sergei Golubchik added a comment - 2021-11-07 18:18

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.

Sergei Golubchik added a comment - 2021-11-07 18:18 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.

Michael Widenius added a comment - 2022-12-13 09:00

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.

Michael Widenius added a comment - 2022-12-13 09:00 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.

Michael Widenius added a comment - 2022-12-13 09:07

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.

Michael Widenius added a comment - 2022-12-13 09:07 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.

MariaDB Server

Ensure that table is truly dropped when using DROP TABLE

Details

Description

Attachments

Issue Links

Sub-Tasks

Activity

People

Dates

Git Integration