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

DISCARD TABLESPACE removes DATA DIRECTORY

Details

    Description

      root@spaceman:/home/snen# mariadb
      		 
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      		 
      Your MariaDB connection id is 40654
      		 
      Server version: 11.4.4-MariaDB-deb11-log mariadb.org binary distribution
      		 
       
      		 
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
      		 
       
      		 
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      		 
       
      		 
      MariaDB [(none)]> use my_db;
      		 
      Database changed
      		 
      MariaDB [my_db]> CREATE TABLE `t`(
      		 
          -> `id` int(10) unsigned DEFAULT NULL
      		 
          -> ) ENGINE=InnoDB DATA DIRECTORY='/ssdext/mysql_arch_tables';
      		 
      Query OK, 0 rows affected (0,006 sec)
      		 
       
      		 
      MariaDB [my_db]> SHOW CREATE TABLE `t` \G
      		 
      *************************** 1. row ***************************
      		 
             Table: t
      		 
      Create Table: CREATE TABLE `t` (
      		 
        `id` int(10) unsigned DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci DATA DIRECTORY='/ssdext/mysql_arch_tables/'
      		 
      1 row in set (0,001 sec)
      		 
       
      		 
      MariaDB [my_db]> ALTER TABLE `t` DISCARD TABLESPACE;
      		 
      Query OK, 0 rows affected (0,010 sec)
      		 
       
      		 
      MariaDB [my_db]> SHOW CREATE TABLE `t` \G
      		 
      *************************** 1. row ***************************
      		 
             Table: t
      		 
      Create Table: CREATE TABLE `t` (
      		 
        `id` int(10) unsigned DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
      		 
      1 row in set (0,001 sec)

      It does not allow to move the table to another location by "DISCARD TABLESPACE", copy *.cfg and *.ibd to new location and "IMPORT TABLESPACE;"

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          snen Dmitry Savolainen created issue -
          marko Marko Mäkelä made changes -
          Field Original Value New Value
          Description {{root@spaceman:/home/snen# mariadb
          Welcome to the MariaDB monitor. Commands end with ; or \g.
          Your MariaDB connection id is 40654
          Server version: 11.4.4-MariaDB-deb11-log mariadb.org binary distribution

          Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

          MariaDB [(none)]> use my_db;
          Database changed
          MariaDB [my_db]> CREATE TABLE `t`(
              -> `id` int(10) unsigned DEFAULT NULL
              -> ) ENGINE=InnoDB DATA DIRECTORY='/ssdext/mysql_arch_tables';
          Query OK, 0 rows affected (0,006 sec)

          MariaDB [my_db]> SHOW CREATE TABLE `t` \G
          *************************** 1. row ***************************
                 Table: t
          Create Table: CREATE TABLE `t` (
            `id` int(10) unsigned DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci DATA DIRECTORY='/ssdext/mysql_arch_tables/'
          1 row in set (0,001 sec)

          MariaDB [my_db]> ALTER TABLE `t` DISCARD TABLESPACE;
          Query OK, 0 rows affected (0,010 sec)

          MariaDB [my_db]> SHOW CREATE TABLE `t` \G
          *************************** 1. row ***************************
                 Table: t
          Create Table: CREATE TABLE `t` (
            `id` int(10) unsigned DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
          1 row in set (0,001 sec)}}

          It does not allow to move the table to another location by "DISCARD TABLESPACE", copy *.cfg and *.ibd to new location and "IMPORT TABLESPACE;"           		{noformat}root@spaceman:/home/snen# mariadb
          Welcome to the MariaDB monitor. Commands end with ; or \g.
          Your MariaDB connection id is 40654
          Server version: 11.4.4-MariaDB-deb11-log mariadb.org binary distribution

          Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

          MariaDB [(none)]> use my_db;
          Database changed
          MariaDB [my_db]> CREATE TABLE `t`(
              -> `id` int(10) unsigned DEFAULT NULL
              -> ) ENGINE=InnoDB DATA DIRECTORY='/ssdext/mysql_arch_tables';
          Query OK, 0 rows affected (0,006 sec)

          MariaDB [my_db]> SHOW CREATE TABLE `t` \G
          *************************** 1. row ***************************
                 Table: t
          Create Table: CREATE TABLE `t` (
            `id` int(10) unsigned DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci DATA DIRECTORY='/ssdext/mysql_arch_tables/'
          1 row in set (0,001 sec)

          MariaDB [my_db]> ALTER TABLE `t` DISCARD TABLESPACE;
          Query OK, 0 rows affected (0,010 sec)

          MariaDB [my_db]> SHOW CREATE TABLE `t` \G
          *************************** 1. row ***************************
                 Table: t
          Create Table: CREATE TABLE `t` (
            `id` int(10) unsigned DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
          1 row in set (0,001 sec){noformat}

          It does not allow to move the table to another location by "DISCARD TABLESPACE", copy *.cfg and *.ibd to new location and "IMPORT TABLESPACE;"
          serg Sergei Golubchik made changes -
          Fix Version/s 11.4 [ 29301 ]
          serg Sergei Golubchik made changes -
          Assignee Thirunarayanan Balathandayuthapani [ thiru ]
          thiru Thirunarayanan Balathandayuthapani made changes -
          Comment [ [~snen] We couldn't repeat the issue locally in 11.4.4 version. Can you please re-verify it? Any statement has been executed before discard tablespace? ]
          ramesh Ramesh Sivaraman added a comment -

          thiru As discussed, after discard tablespace restarting server do not show data_directory info in show create table statement

          MariaDB [my_db]> SHOW CREATE TABLE t\G
          		 
          *************************** 1. row ***************************
          		 
                 Table: t
          		 
          Create Table: CREATE TABLE `t` (
          		 
            `id` int(10) unsigned DEFAULT NULL
          		 
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci DATA DIRECTORY='/tmp/mysql_arch_tables/'
          		 
          1 row in set (0.001 sec)
          		 
           
          		 
          MariaDB [my_db]> ALTER TABLE t DISCARD TABLESPACE;
          		 
          Query OK, 0 rows affected (0.002 sec)
          		 
           
          		 
          MariaDB [my_db]>
          		 
          [ restarted server ]
          		 
          MariaDB [my_db]> SHOW CREATE TABLE t\G
          		 
          *************************** 1. row ***************************
          		 
                 Table: t
          		 
          Create Table: CREATE TABLE `t` (
          		 
            `id` int(10) unsigned DEFAULT NULL
          		 
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
          		 
          1 row in set (0.000 sec)
          		 
           
          		 
          MariaDB [my_db]>

          ramesh Ramesh Sivaraman added a comment - thiru As discussed, after discard tablespace restarting server do not show data_directory info in show create table statement MariaDB [my_db]> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `id` int (10) unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_general_ci DATA DIRECTORY= '/tmp/mysql_arch_tables/' 1 row in set (0.001 sec)   MariaDB [my_db]> ALTER TABLE t DISCARD TABLESPACE; Query OK, 0 rows affected (0.002 sec)   MariaDB [my_db]> [ restarted server ] MariaDB [my_db]> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `id` int (10) unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_general_ci 1 row in set (0.000 sec)   MariaDB [my_db]>
          thiru Thirunarayanan Balathandayuthapani added a comment -

          This problem happens from 10.5 onwards.

          thiru Thirunarayanan Balathandayuthapani added a comment - This problem happens from 10.5 onwards.
          thiru Thirunarayanan Balathandayuthapani made changes -
          Affects Version/s 10.5 [ 23123 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.11 [ 27614 ]
          Affects Version/s 11.4 [ 29301 ]
          Affects Version/s 11.8 [ 29921 ]
          thiru Thirunarayanan Balathandayuthapani made changes -
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.8 [ 29921 ]
          thiru Thirunarayanan Balathandayuthapani made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]

          People

            thiru Thirunarayanan Balathandayuthapani
            snen Dmitry Savolainen
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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