Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
Description
Right now the best way I could come up with to check whether there might be a DISCARDed table/tablespace on a MariaDB server instance is building upon the fact that discarded tablespaces do not show up in INNODB_SYS_TABLESPACES:
SELECT T.TABLE_SCHEMA, T.TABLE_NAME
|
FROM INFORMATION_SCHEMA.TABLES T
|
LEFT OUTER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I
|
ON CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) = I.NAME
|
WHERE T.ENGINE='InnoDB'
|
AND T.CREATE_OPTIONS NOT LIKE '%partitioned%'
|
AND I.FILENAME IS NULL;
|
This obviously does not work for discarded partitions, and may also otherwise generate false positives.
A more direct and more reliable method to check for tables with discarded tablespaces would be appreciated.
Attachments
Issue Links
- is duplicated by
-
MDEV-30615 Can't read from I_S.INNODB_SYS_INDEXES when having a discarded tablesace
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
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.
This is related to
MDEV-30615. I would expect that when INNODB_SYS_TABLES or INNODB_SYS_INDEXES reports SPACE or INNODB_SYS_INDEXES.PAGE being NULL, the tablespace has been discarded or the file is corrupted.