Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
It would sometimes be useful to be able to manually view the space ID of an InnoDB tablespace file, especially when users see errors like the following:
2017-06-07 19:07:13 7f2be06b1820 InnoDB: Error: table 'db1/tab1'
|
InnoDB: in InnoDB data dictionary has tablespace id 409749,
|
InnoDB: but a tablespace with that id does not exist. There is
|
InnoDB: a tablespace of name db1/tab1 and id 409547, though. Have
|
InnoDB: you deleted or moved .ibd files?
|
InnoDB: Please refer to
|
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
|
InnoDB: for how to resolve the issue.
|
Currently, innochecksum does not seem to print this information, even if -i and -v are specified:
$ sudo innochecksum -v -i /var/lib/mysql/db1/tab.ibd
|
InnoDB offline file checksum utility.
|
|
|
Variables (--variable-name=value)
|
and boolean options {FALSE|TRUE} Value (after reading options)
|
--------------------------------- ----------------------------------------
|
verbose TRUE
|
debug FALSE
|
skip-corrupt FALSE
|
count FALSE
|
start-page 0
|
end-page 0
|
page 0
|
per-page-details TRUE
|
leaf FALSE
|
merge 0
|
Table is uncompressed
|
Page size is 16384
|
file /var/lib/mysql/db1/tab.ibd = 98304 bytes (6 pages)...
|
InnoChecksum; checking pages in range 0 to 5
|
page 0 FIL_PAGE_TYPE_FSP_HDR
|
page 1 FIL_PAGE_IBUF_BITMAP
|
page 2 FIL_PAGE_INODE
|
page 3 index 23 page 3 leaf 1 n_recs 2 data_bytes 56
|
page 4 FIL_PAGE_TYPE_ALLOCATED
|
page 5 FIL_PAGE_TYPE_ALLOCATED
|
0 bad checksum
|
1 FIL_PAGE_INDEX
|
0 FIL_PAGE_UNDO_LOG
|
1 FIL_PAGE_INODE
|
0 FIL_PAGE_IBUF_FREE_LIST
|
2 FIL_PAGE_TYPE_ALLOCATED
|
1 FIL_PAGE_IBUF_BITMAP
|
0 FIL_PAGE_TYPE_SYS
|
0 FIL_PAGE_TYPE_TRX_SYS
|
1 FIL_PAGE_TYPE_FSP_HDR
|
0 FIL_PAGE_TYPE_XDES
|
0 FIL_PAGE_TYPE_BLOB
|
0 FIL_PAGE_TYPE_ZBLOB
|
0 FIL_PAGE_PAGE_COMPRESSED
|
0 FIL_PAGE_PAGE_COMPRESSED_ENCRYPTED
|
0 other
|
0 max index_id
|
undo type: 0 insert, 0 update, 0 other
|
undo state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other
|
index_id #pages #leaf_pages #recs_per_page #bytes_per_page
|
23 1 1 2 56
|
|
|
index_id page_data_bytes_histgram(empty,...,oversized)
|
23 0 1 0 0 0 0 0 0 0 0 0 0
|
You can view the space ID that the data dictionary has in the information_schema.innodb_sys_tablespaces table:
MariaDB [(none)]> SELECT * FROM information_schema.innodb_sys_tablespaces WHERE NAME='db1/tab';
|
+-------+---------+------+-------------+----------------------+-----------+---------------+
|
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE |
|
+-------+---------+------+-------------+----------------------+-----------+---------------+
|
| 239 | db1/tab | 0 | Antelope | Compact or Redundant | 16384 | 0 |
|
+-------+---------+------+-------------+----------------------+-----------+---------------+
|
1 row in set (0.00 sec)
|
But as far as I understand it, the information in that table does not tell you if the space ID in the data dictionary conflicts with the one in the file itself.