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

Can't read from I_S.INNODB_SYS_INDEXES when having a discarded tablesace

Details

    Description

      When having a discarded tablespace it is not possible to read from INNODB_SYS_INDEXES, the below will fail with ERROR 1105 (HY000): Unknown error, and no further details in SHOW WARNINGS output or the error log.

      CREATE TABLE test.t1(id INT PRIMARY KEY);
      		 
      ALTER TABLE test.t1 DISCARD TABLESPACE;
      		 
      SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-30616 Provide an easy way to list table currently in DISCARDED state.

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-30616 Provide an easy way to list table currently in DISCARDED state.

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            hholzgra Hartmut Holzgraefe created issue -
            hholzgra Hartmut Holzgraefe made changes -
            Field Original Value New Value
            Affects Version/s 10.10.3 [ 28521 ]
            Affects Version/s 10.9.5 [ 28519 ]
            Affects Version/s 10.8.7 [ 28517 ]
            Affects Version/s 10.7.8 [ 28515 ]
            Affects Version/s 10.6.12 [ 28513 ]
            Affects Version/s 10.5.19 [ 28511 ]
            Affects Version/s 10.4.28 [ 28509 ]
            Affects Version/s 10.11.1 [ 28454 ]
            Description When having a discarded tablespace it is not possible to read from INNODB_SYS_INDEXES, the below will fail with {{ERROR 1105 (HY000): Unknown error}}, and no further details in {{SHOW WARNINGS}} output or the error log.

            {code}
            CREATE TABLE test.t1(id INT PRIMARY KEY);
            ALTER TABLE test.t1 DISCARD TABLESPACE;
            SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES;
            {code}
            Labels information_schema innodb
            Summary Can Can't read from I_S.INNODB_SYS_INDEXES when having a discarded tablesace
            marko Marko Mäkelä made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani added a comment -

            Patch is in bb-10.4-thiru

            thiru Thirunarayanan Balathandayuthapani added a comment - Patch is in bb-10.4-thiru
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä added a comment -

            OK to push. Please make the test demonstrate that INFORMATION_SCHEMA.INNODB_SYS_TABLES.SPACE will not be invalidated during ALTER TABLE…DISCARD TABLESPACE.

            marko Marko Mäkelä added a comment - OK to push. Please make the test demonstrate that INFORMATION_SCHEMA.INNODB_SYS_TABLES.SPACE will not be invalidated during ALTER TABLE…DISCARD TABLESPACE .
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani added a comment - 

            SELECT * FROM information_schema.innodb_sys_tablespaces where name="test/t1";
            		 
            SPACE	NAME	FLAG	ROW_FORMAT	PAGE_SIZE	ZIP_PAGE_SIZE	SPACE_TYPE	FS_BLOCK_SIZE	FILE_SIZE	ALLOCATED_SIZE
            		 
            5	test/t1	33	Dynamic	16384	16384	Single	4096	65536	65536
            		 
             
            		 
            ALTER TABLE test.t1 DISCARD TABLESPACE;
            		 
             
            		 
            SELECT * FROM information_schema.innodb_sys_tablespaces where file_size = 0;
            		 
            SPACE	NAME	FLAG	ROW_FORMAT	PAGE_SIZE	ZIP_PAGE_SIZE	SPACE_TYPE	FS_BLOCK_SIZE	FILE_SIZE	ALLOCATED_SIZE
            		 
            5	test/t1	33	Dynamic	16384	16384	Single	0	0	0

            By using file_size, I think we can find out whether the table is in discarded state. But the above query works only till 10.5. In 10.6, i_s view was changed
            to reflect the main memory structure. Prior to that, InnoDB was traversing the innodb dictionary table to create view.

            thiru Thirunarayanan Balathandayuthapani added a comment - SELECT * FROM information_schema.innodb_sys_tablespaces where name="test/t1"; SPACE NAME FLAG ROW_FORMAT PAGE_SIZE ZIP_PAGE_SIZE SPACE_TYPE FS_BLOCK_SIZE FILE_SIZE ALLOCATED_SIZE 5 test/t1 33 Dynamic 16384 16384 Single 4096 65536 65536   ALTER TABLE test.t1 DISCARD TABLESPACE;   SELECT * FROM information_schema.innodb_sys_tablespaces where file_size = 0; SPACE NAME FLAG ROW_FORMAT PAGE_SIZE ZIP_PAGE_SIZE SPACE_TYPE FS_BLOCK_SIZE FILE_SIZE ALLOCATED_SIZE 5 test/t1 33 Dynamic 16384 16384 Single 0 0 0 By using file_size, I think we can find out whether the table is in discarded state. But the above query works only till 10.5. In 10.6, i_s view was changed to reflect the main memory structure. Prior to that, InnoDB was traversing the innodb dictionary table to create view.
            thiru Thirunarayanan Balathandayuthapani made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Fix Version/s 10.4.29 [ 28510 ]
            Fix Version/s 10.5.20 [ 28512 ]
            Fix Version/s 10.6.13 [ 28514 ]
            Fix Version/s 10.8.8 [ 28518 ]
            Fix Version/s 10.9.6 [ 28520 ]
            Fix Version/s 10.10.4 [ 28522 ]
            Fix Version/s 10.11.2 [ 28523 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            thiru Thirunarayanan Balathandayuthapani made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.