溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
今天就跟大家聊聊有關(guān)如何解析MYSQL ERROR 1146 Table doesnt exist ，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。
在MYSQL使用innodb的時(shí)候我們有時(shí)候會(huì)看到如下報(bào)錯(cuò)：
ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist
首先總結(jié)下原因：
缺少frm文件
innodb數(shù)據(jù)字典不包含這個(gè)表
我們重點(diǎn)討論情況2，因?yàn)榍闆r1是顯而易見(jiàn)的。
?在使用innodb存儲(chǔ)引擎的時(shí)候某些時(shí)候我們show tables能夠看到這個(gè)表,但是如果進(jìn)行任何操作會(huì)報(bào)錯(cuò)如下：
mysql> show tables; | test1bak | mysql> desc test1bak ; ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist
也許你會(huì)說(shuō)我明明能夠看到這個(gè)表啊，為什么訪問(wèn)還會(huì)報(bào)錯(cuò)呢？其實(shí)要清楚innodb有自己的數(shù)據(jù)字典，只要有frm 文件存在show tables就能看到，但是最終是否能夠正常打開(kāi)表結(jié)構(gòu)在innodb中還依賴于innodb的數(shù)據(jù)字典，主要的包含:
INNODB_SYS_columns
INNODB_SYS_FIELDS
INNODB_SYS_TABLES
INNODB_SYS_INDEXES
如果報(bào)錯(cuò)出現(xiàn)我們需要首先查看的是INNODB_SYS_TABLES是否包含了這個(gè)表的信息。也許在這些數(shù)據(jù)字典中也許某些列并顯示并不是那么明確，比如
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，他實(shí)際上是一個(gè)位圖表示方式，分別表示如下信息：
/* 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
接下來(lái)我們分析一下為什么是FLAG是33如下：
33的二進(jìn)制為00100001從低位開(kāi)始 1:從源碼注釋來(lái)看本位COMPACT/COMPRESSED/DYNAMIC均為1 0000: ZIP_SSIZE flag 這四位用于支持壓縮功能如COMPRESSED 1:ATOMIC_BLOBS flag 這一位是COMPACT和DYNAMIC主要區(qū)別所在,請(qǐng)看源碼注釋 0:DATA DIRECTORY and innodb-file-per-table flag為了支持DATA DIRECTORY語(yǔ)法 0:SHARED tablespace flag為了支持TABLESPACE語(yǔ)法
然后我們測(cè)試一下：
如果我們建立如下的表: 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二進(jìn)制為 01100001:使用DATA DIRECTORY建立使用ATOMIC_BLOBS且無(wú)壓縮則DYNAMIC格式 詳見(jiàn)：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二進(jìn)制為 10000001:使用TABLESPACE語(yǔ)法建立不使用ATOMIC_BLOBS且無(wú)壓縮則為COMPACT格式 詳見(jiàn)：15.5.9 InnoDB General Tablespaces
我們可以看到使用8位一個(gè)字節(jié)而已就可以表示出大量的信息，這也是位圖的優(yōu)勢(shì)，其他比如 MTYPE/PRTYPE也是這種表示方式
接下來(lái)我們回到主題，需要看看這個(gè)錯(cuò)到底是哪里報(bào)錯(cuò)來(lái)的？進(jìn)行trace后如下,我們來(lái)看看主要部分：
注意這里的trace是mysql debug版本下查看函數(shù)調(diào)用的主要方法參考官方文檔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
其實(shí)大概步驟就是
Checks if a table is in the dictionary cache
根據(jù)dict_sys->table_hash尋找
Loads a table definition and also all its index definitions.
通過(guò)掃描字典的B+樹(shù)進(jìn)行加載
如果不能找到則報(bào)錯(cuò)
這樣也就解釋了為什么show tables能夠看到但是select卻報(bào)錯(cuò)Table doesn't exist ，而從原理上講show tables只是查看了frm文件。
另外這里也提一個(gè)案列，曾經(jīng)有一個(gè)朋友問(wèn)我他將整個(gè)庫(kù)目錄都拷貝了，但是表能看到但是一操作就報(bào)Table doesn't exist，顯然他沒(méi)有拷貝ibdata1，數(shù)據(jù)字典的引導(dǎo)信息都存在這里面文件的第7個(gè)page中，其b+樹(shù)也是存在其中，用源碼解釋一下：
/**********************************************************************//**
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當(dāng)然就是引導(dǎo)塊，這哥們連ibdata1都沒(méi)拷貝，當(dāng)然innodb數(shù)據(jù)字典自然不包含這些表了。其實(shí)也是上面描述的原理 。
?那么正確的拷貝的方式一定是停機(jī)后，整個(gè)數(shù)據(jù)目錄進(jìn)行拷貝，而不是僅僅拷貝需要的庫(kù)的目錄，否則innodb數(shù)據(jù)字典是不能正常加載的。
最后附帶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 */ ****/*--------------------------------------*/****
看完上述內(nèi)容，你們對(duì)如何解析MYSQL ERROR 1146 Table doesnt exist 有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼