關于MySQL ERROR 1146 Table doesnt exist的解析
作者：gaopengtttt
在MYSQL使用innodb的時候我們有時候會看到如下報錯：ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist.本文主要根據innodb數據字典不包含這個表的情況進行解析。
☉源碼版本 5.7.14
在MYSQL使用innodb的時候我們有時候會看到如下報錯：
- ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist
首先總結下原因：
- 缺少frm文件
- innodb數據字典不包含這個表
我們重點討論情況2，因為情況1是顯而易見的。
在使用innodb存儲引擎的時候某些時候我們show tables能夠看到這個表,但是如果進行任何操作會報錯如下：
- mysql> show tables;
- | test1bak |
- mysql> desc test1bak ;
- ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist
也許你會說我明明能夠看到這個表啊，為什么訪問還會報錯呢?其實要清楚innodb有自己的數據字典，只要有frm 文件存在show tables就能看到，但是最終是否能夠正常打開表結構在innodb中還依賴于innodb的數據字典，主要的包含:
1、INNODB_SYS_columns
2、INNODB_SYS_FIELDS
3、INNODB_SYS_TABLES
4、INNODB_SYS_INDEXES
如果報錯出現我們需要首先查看的是INNODB_SYS_TABLES是否包含了這個表的信息。也許在這些數據字典中也許某些列并顯示并不是那么明確，比如
- mysql> select * from information_schema.innodb_sys_tables where name='test/kkkkm1';
- +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+
- | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
- +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+
- | 374 | test/kkkkm1 | 33 | 6 | 540 | Barracuda | Dynamic | 0 | Single |
- +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+
比如這里的FLAG列為33，他實際上是一個位圖表示方式，分別表示如下信息：
- /* Table and tablespace flags are generally not used for the Antelope file
- format except for the low order bit, which is used differently depending on
- where the flags are stored.
- ==================== Low order flags bit =========================
- | REDUNDANT | COMPACT | COMPRESSED and DYNAMIC
- SYS_TABLES.TYPE | 1 | 1 | 1
- dict_table_t::flags | 0 | 1 | 1
- FSP_SPACE_FLAGS | 0 | 0 | 1
- fil_space_t::flags | 0 | 0 | 1
- /** Width of the COMPACT flag */
- #define DICT_TF_WIDTH_COMPACT 1
- /** Width of the ZIP_SSIZE flag */
- #define DICT_TF_WIDTH_ZIP_SSIZE 4
- /** Width of the ATOMIC_BLOBS flag. The Antelope file formats broke up
- BLOB and TEXT fields, storing the first 768 bytes in the clustered index.
- Barracuda row formats store the whole blob or text field off-page atomically.
- Secondary indexes are created from this external data using row_ext_t
- to cache the BLOB prefixes. */
- #define DICT_TF_WIDTH_ATOMIC_BLOBS 1
- /** If a table is created with the MYSQL option DATA DIRECTORY and
- innodb-file-per-table, an older engine will not be able to find that table.
- This flag prevents older engines from attempting to open the table and
- allows InnoDB to update_create_info() accordingly. */
- #define DICT_TF_WIDTH_DATA_DIR 1
- /** Width of the SHARED tablespace flag.
- It is used to identify tables that exist inside a shared general tablespace.
- If a table is created with the TABLESPACE=tsname option, an older engine will
- not be able to find that table. This flag prevents older engines from attempting
- to open the table and allows InnoDB to quickly find the tablespace. */
- #define DICT_TF_WIDTH_SHARED_SPACE 1
接下來我們分析一下為什么是FLAG是33如下：
33的二進制為00100001從低位開始 1:從源碼注釋來看本位COMPACT/COMPRESSED/DYNAMIC均為1 0000: ZIP_SSIZE flag 這四位用于支持壓縮功能如COMPRESSED 1:ATOMIC_BLOBS flag 這一位是COMPACT和DYNAMIC主要區別所在,請看源碼注釋 0:DATA DIRECTORY and innodb-file-per-table flag為了支持DATA DIRECTORY語法 0:SHARED tablespace flag為了支持TABLESPACE語法
然后我們測試一下：
如果我們建立如下的表: CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table DATA DIRECTORY = '/root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/mysqld.1'; 其type為97二進制為 01100001:使用DATA DIRECTORY建立使用ATOMIC_BLOBS且無壓縮則DYNAMIC格式 詳見：15.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory 如果我們建立如下的表: CREATE TABLESPACE tt1 ADD DATAFILE '/root/mysql5.7.14/tt1.ibd'; CREATE TABLE tsh (c1 INT ) TABLESPACE tt1 ROW_FORMAT=COMPACT ; 其type為129二進制為 10000001:使用TABLESPACE語法建立不使用ATOMIC_BLOBS且無壓縮則為COMPACT格式 詳見：15.5.9 InnoDB General Tablespaces
我們可以看到使用8位一個字節而已就可以表示出大量的信息，這也是位圖的優勢，其他比如 MTYPE/PRTYPE也是這種表示方式
接下來我們回到主題，需要看看這個錯到底是哪里報錯來的?進行trace后如下,我們來看看主要部分：
注意這里的trace是mysql debug版本下查看函數調用的主要方法參考官方文檔26.5.1.2 Creating Trace Files502 T@2: | | | | | | | | | | | >ha_innobase::open_dict_table
- 503 T@2: | | | | | | | | | | | | >dict_table_open_on_name
- 504 T@2: | | | | | | | | | | | | | dict_table_open_on_name: table: 'test/test1bak'
- 505 T@2: | | | | | | | | | | | | | >dict_table_check_if_in_cache_low
- 506 T@2: | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table: 'test/test1bak'
- 507 T@2: | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125
- 508 T@2: | | | | | | | | | | | | | >dict_load_table
- 509 T@2: | | | | | | | | | | | | | | dict_load_table: loading table: 'test/test1bak'
- 510 T@2: | | | | | | | | | | | | | | >dict_table_check_if_in_cache_low
- 511 T@2: | | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table: 'test/test1bak'
- 512 T@2: | | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125
- 513 T@2: | | | | | | | | | | | | | | >dict_load_table_one
- 514 T@2: | | | | | | | | | | | | | | | dict_load_table_one: table: test/test1bak
- 515 T@2: | | | | | | | | | | | | | | | >dict_table_check_if_in_cache_low
- 516 T@2: | | | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table: 'SYS_TABLES'
- 517 T@2: | | | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125
- 518 T@2: | | | | | | | | | | | | | | | >btr_cur_search_to_nth_level
- 519 T@2: | | | | | | | | | | | | | | | <btr_cur_search_to_nth_level 2005
- 520 T@2: | | | | | | | | | | | | | | <dict_load_table_one 3084
- 521 T@2: | | | | | | | | | | | | | <dict_load_table 2882
- 522 T@2: | | | | | | | | | | | | <dict_table_open_on_name 1292
- 523 T@2: | | | | | | | | | | | <ha_innobase::open_dict_table 6676
- 524 T@2: | | | | | | | | | | | >sql_print_warning
- 525 T@2: | | | | | | | | | | | | >error_log_print
- 526 T@2: | | | | | | | | | | | | | >print_buffer_to_file
- 527 T@2: | | | | | | | | | | | | | | enter: buffer: InnoDB: Cannot open table test/test1bak from the internal data dictionary of InnoDB though the .frm file for the
- table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
- 528 T@2: | | | | | | | | | | | | | <print_buffer_to_file 2332
- 529 T@2: | | | | | | | | | | | | <error_log_print 2357
- 530 T@2: | | | | | | | | | | | <sql_print_warning 2384
☉其實大概步驟就是
1、Checks if a table is in the dictionary cache
根據dict_sys->table_hash尋找
2、Loads a table definition and also all its index definitions.
通過掃描字典的B+樹進行加載
3、如果不能找到則報錯
這樣也就解釋了為什么show tables能夠看到但是select卻報錯Table doesn't exist ，而從原理上講show tables只是查看了frm文件。
另外這里也提一個案列，曾經有一個朋友問我他將整個庫目錄都拷貝了，但是表能看到但是一操作就報Table doesn't exist，顯然他沒有拷貝ibdata1，數據字典的引導信息都存在這里面文件的第7個page中，其b+樹也是存在其中，用源碼解釋一下：
- /**********************************************************************//**
- Gets a pointer to the dictionary header and x-latches its page.
- @return pointer to the dictionary header, page x-latched */
- dict_hdr_t*
- dict_hdr_get(
- /*=========*/
- mtr_t* mtr) /*!< in: mtr */
- {
- buf_block_t* block;
- dict_hdr_t* header;
- block = buf_page_get(page_id_t(DICT_HDR_SPACE, DICT_HDR_PAGE_NO),
- univ_page_size, RW_X_LATCH, mtr);
- header = DICT_HDR + buf_block_get_frame(block);
- buf_block_dbg_add_level(block, SYNC_DICT_HEADER);
- return(header);
- }
注意這里的 DICT_HDR_SPACE, DICT_HDR_PAGE_NO分別是宏定義
- /* Space id and page no where the dictionary header resides */
- #define DICT_HDR_SPACE 0 /* the SYSTEM tablespace */
- #define DICT_HDR_PAGE_NO FSP_DICT_HDR_PAGE_NO
- #define FSP_DICT_HDR_PAGE_NO 7 /*!< data dictionary header
- page, in tablespace 0 */
space 0就是ibdata1的space_no,7當然就是引導塊，這哥們連ibdata1都沒拷貝，當然innodb數據字典自然不包含這些表了。其實也是上面描述的原理 。
那么正確的拷貝的方式一定是停機后，整個數據目錄進行拷貝，而不是僅僅拷貝需要的庫的目錄，否則innodb數據字典是不能正常加載的。
***附帶space 0的部分塊解釋
- /*--------------------------------------*/
- #define FSP_XDES_OFFSET 0 /* !< extent descriptor */
- #define FSP_IBUF_BITMAP_OFFSET 1 /* !< insert buffer bitmap */
- /* The ibuf bitmap pages are the ones whose
- page number is the number above plus a
- multiple of XDES_DESCRIBED_PER_PAGE */
- #define FSP_FIRST_INODE_PAGE_NO 2 /*!< in every tablespace */
- /* The following pages exist
- in the system tablespace (space 0). */
- #define FSP_IBUF_HEADER_PAGE_NO 3 /*!< insert buffer
- header page, in
- tablespace 0 */
- #define FSP_IBUF_TREE_ROOT_PAGE_NO 4 /*!< insert buffer
- B-tree root page in
- tablespace 0 */
- /* The ibuf tree root page number in
- tablespace 0; its fseg inode is on the page
- number FSP_FIRST_INODE_PAGE_NO */
- #define FSP_TRX_SYS_PAGE_NO 5 /*!< transaction
- system header, in
- tablespace 0 */
- #define FSP_FIRST_RSEG_PAGE_NO 6 /*!< first rollback segment
- page, in tablespace 0 */
- #define FSP_DICT_HDR_PAGE_NO 7 /*!< data dictionary header
- page, in tablespace 0 */
- ****/*--------------------------------------*/****
責任編輯：龐桂玉
來源：
數據庫開發
