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

Ensure that table is truly dropped when using DROP TABLE

Details

    • Task
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.5.4
    • Server
    • None

    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

          Task - A task that needs to be done. MDEV-23570 deprecate keep_files_on_create

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23549 CREATE fails after DROP without FRM

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30170 no error for DROP TABLE and no warning for DROP TABLE IF EXISTS with absent table when Spider plugin is included

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34205 ASAN stack-buffer-overflow in strxnmov | frm_file_exists

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MCOL-4164 Multi-table DROP

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. 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

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26966 The parameter innodb_force_load_corrupted makes no sense

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15185 PARTITION files removed, but not from INFORMATION_SCHEMA.INNODB_SYS_TABLES

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16417 Store Foreign Key metadata outside of InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23000 Ensure we get a warning from THD::drop_temporary_table() in case of disk errors

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23185 CREATE OR REPLACE TABLE is less robust than DROP TABLE + CREATE TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            svoj Sergey Vojtovich created issue -
            svoj Sergey Vojtovich made changes -
            Field Original Value New Value
            Epic Link MDEV-11372 [ 59082 ]
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            monty Michael Widenius made changes -
            Assignee Michael Widenius [ monty ]
            monty Michael Widenius made changes -
            Fix Version/s 10.5.4 [ 24264 ]
            monty Michael Widenius made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            monty Michael Widenius made changes -
            Description {noformat}
            Summary:
            --------
            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;
            {noformat}
            https://github.com/alibaba/AliSQL/commit/fc7e697b8b34a7751da8c8dca54ba152601d9ee5             		{noformat}
            Summary:
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.5.4 [ 24264 ]
            elenst Elena Stepanova added a comment -

            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

            elenst Elena Stepanova added a comment - 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
            serg Sergei Golubchik made changes -
            Assignee Michael Widenius [ monty ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            monty Michael Widenius made changes -
            Description {noformat}
            Summary:
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.             		This task is based on patches from Ali-SQL
            {noformat}
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.
            monty Michael Widenius made changes -
            Description This task is based on patches from Ali-SQL
            {noformat}
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.             		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.

            {noformat}
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.
            monty Michael Widenius made changes -
            Summary AliSQL: [Feature] Issue#34 Support force drop table Ensure that table is truly dropped when using DROP TABLE
            monty Michael Widenius made changes -
            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.

            {noformat}
            --------
            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;
            {noformat}
            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 doesn't, try dropping the table from all storage engines.             		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.

            {noformat}
            --------
            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;
            {noformat}
            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 (.CVS, 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.
            monty Michael Widenius made changes -
            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.

            {noformat}
            --------
            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;
            {noformat}
            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 (.CVS, 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.
            		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.

            {noformat}
            --------
            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;
            {noformat}
            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 (.CVS, 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#...
            serg Sergei Golubchik made changes -
            Component/s Server [ 13907 ]
            Fix Version/s 10.5.4 [ 24264 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Michael Widenius [ monty ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            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.

            {noformat}
            --------
            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;
            {noformat}
            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 (.CVS, 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#...
            		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.

            {noformat}
            --------
            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;
            {noformat}
            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#...
            drrtuy Roman made changes -
            marko Marko Mäkelä made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            dlenski Daniel Lenski (Inactive) added a comment - - 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

            dlenski Daniel Lenski (Inactive) added a comment - - 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
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 78522 ] MariaDB v4 [ 133045 ]
            elenst Elena Stepanova made changes -
            monty Michael Widenius added a comment -

            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.

            monty Michael Widenius added a comment - 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.
            monty Michael Widenius added a comment -

            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.

            monty Michael Widenius added a comment - 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.
            danblack Daniel Black made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            ramesh Ramesh Sivaraman made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201658
            Zendesk active tickets 201658

            People

              monty Michael Widenius
              svoj Sergey Vojtovich
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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