MYSQL ERROR 1146 Table doesnt exist 解析

gaopengtttt發表於2017-09-04
MySqlError

原創轉載請註明出處

原始碼版本 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 Files 
 502  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 */
****/*--------------------------------------*/****

作者微信：

微信.jpg

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2144472/，如需轉載，請註明出處，否則將追究法律責任。

相關文章