中国领先的IT技术网站
|
|

关于MySQL ERROR 1146 Table doesnt exist的解析

在MYSQL使用innodb的时候我们有时候会看到如下报错:ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist.本文主要根据innodb数据字典不包含这个表的情况进行解析。

作者:gaopengtttt来源:数据库开发|2017-10-11 14:00

沙龙活动 | 去哪儿、陌陌、ThoughtWorks在自动化运维中的实践!10.28不见不散!


MySQL ERROR 1146 Table doesnt exist解析

☉源码版本 5.7.14

在MYSQL使用innodb的时候我们有时候会看到如下报错:

  1. ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist 

首先总结下原因:

  1. 缺少frm文件
  2. innodb数据字典不包含这个表

我们重点讨论情况2,因为情况1是显而易见的。

在使用innodb存储引擎的时候某些时候我们show tables能够看到这个表,但是如果进行任何操作会报错如下:

  1. mysql> show tables;    
  2. | test1bak          |    
  3. mysql> desc test1bak ;    
  4. 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是否包含了这个表的信息。也许在这些数据字典中也许某些列并显示并不是那么明确,比如

  1. mysql> select * from information_schema.innodb_sys_tables where name='test/kkkkm1';    
  2. +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+    
  3. | TABLE_ID | NAME        | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |    
  4. +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+    
  5. |      374 | test/kkkkm1 |   33 |      6 |   540 | Barracuda   | Dynamic    |             0 | Single     |    
  6. +----------+-------------+------+--------+-------+-------------+------------+---------------+------------+ 

比如这里的FLAG列为33,他实际上是一个位图表示方式,分别表示如下信息:

  1. /* Table and tablespace flags are generally not used for the Antelope file    
  2. format except for the low order bit, which is used differently depending on    
  3. where the flags are stored.    
  4.  
  5. ==================== Low order flags bit =========================    
  6.                     | REDUNDANT | COMPACT | COMPRESSED and DYNAMIC    
  7. SYS_TABLES.TYPE     |     1     |    1    |     1    
  8. dict_table_t::flags |     0     |    1    |     1    
  9. FSP_SPACE_FLAGS     |     0     |    0    |     1    
  10. fil_space_t::flags  |     0     |    0    |     1    
  11.  
  12. /** Width of the COMPACT flag */    
  13. #define DICT_TF_WIDTH_COMPACT       1    
  14.  
  15. /** Width of the ZIP_SSIZE flag */    
  16. #define DICT_TF_WIDTH_ZIP_SSIZE     4    
  17.  
  18. /** Width of the ATOMIC_BLOBS flag.  The Antelope file formats broke up    
  19. BLOB and TEXT fields, storing the first 768 bytes in the clustered index.    
  20. Barracuda row formats store the whole blob or text field off-page atomically.    
  21. Secondary indexes are created from this external data using row_ext_t    
  22. to cache the BLOB prefixes. */    
  23. #define DICT_TF_WIDTH_ATOMIC_BLOBS  1    
  24.  
  25. /** If a table is created with the MYSQL option DATA DIRECTORY and    
  26. innodb-file-per-table, an older engine will not be able to find that table.    
  27. This flag prevents older engines from attempting to open the table and    
  28. allows InnoDB to update_create_info() accordingly. */    
  29. #define DICT_TF_WIDTH_DATA_DIR      1    
  30.  
  31. /** Width of the SHARED tablespace flag.    
  32. It is used to identify tables that exist inside a shared general tablespace.    
  33. If a table is created with the TABLESPACE=tsname option, an older engine will    
  34. not be able to find that table. This flag prevents older engines from attempting    
  35. to open the table and allows InnoDB to quickly find the tablespace. */    
  36. #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

  1. 503  T@2: | | | | | | | | | | | | >dict_table_open_on_name    
  2.    504  T@2: | | | | | | | | | | | | | dict_table_open_on_name: table'test/test1bak'    
  3.    505  T@2: | | | | | | | | | | | | | >dict_table_check_if_in_cache_low    
  4.    506  T@2: | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table'test/test1bak'    
  5.    507  T@2: | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125    
  6.    508  T@2: | | | | | | | | | | | | | >dict_load_table    
  7.    509  T@2: | | | | | | | | | | | | | | dict_load_table: loading table'test/test1bak'    
  8.    510  T@2: | | | | | | | | | | | | | | >dict_table_check_if_in_cache_low    
  9.    511  T@2: | | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table'test/test1bak'    
  10.    512  T@2: | | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125    
  11.    513  T@2: | | | | | | | | | | | | | | >dict_load_table_one    
  12.    514  T@2: | | | | | | | | | | | | | | | dict_load_table_one: table: test/test1bak    
  13.    515  T@2: | | | | | | | | | | | | | | | >dict_table_check_if_in_cache_low    
  14.    516  T@2: | | | | | | | | | | | | | | | | dict_table_check_if_in_cache_low: table'SYS_TABLES'    
  15.    517  T@2: | | | | | | | | | | | | | | | <dict_table_check_if_in_cache_low 125    
  16.    518  T@2: | | | | | | | | | | | | | | | >btr_cur_search_to_nth_level    
  17.    519  T@2: | | | | | | | | | | | | | | | <btr_cur_search_to_nth_level 2005    
  18.    520  T@2: | | | | | | | | | | | | | | <dict_load_table_one 3084    
  19.    521  T@2: | | | | | | | | | | | | | <dict_load_table 2882    
  20.    522  T@2: | | | | | | | | | | | | <dict_table_open_on_name 1292    
  21.    523  T@2: | | | | | | | | | | | <ha_innobase::open_dict_table 6676    
  22.    524  T@2: | | | | | | | | | | | >sql_print_warning    
  23.    525  T@2: | | | | | | | | | | | | >error_log_print    
  24.    526  T@2: | | | | | | | | | | | | | >print_buffer_to_file    
  25.    527  T@2: | | | | | | | | | | | | | | enter: buffer: InnoDB: Cannot open table test/test1bak from the internal data dictionary of InnoDB though the .frm file for the    
  26.  table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.    
  27.    528  T@2: | | | | | | | | | | | | | <print_buffer_to_file 2332    
  28.    529  T@2: | | | | | | | | | | | | <error_log_print 2357    
  29.    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+树也是存在其中,用源码解释一下:

  1. /**********************************************************************//**          
  2. Gets a pointer to the dictionary header and x-latches its page.          
  3. @return pointer to the dictionary header, page x-latched */          
  4. dict_hdr_t*          
  5. dict_hdr_get(          
  6. /*=========*/          
  7.     mtr_t*  mtr)    /*!< in: mtr */          
  8. {          
  9.     buf_block_t*    block;          
  10.     dict_hdr_t* header;          
  11.  
  12.     block = buf_page_get(page_id_t(DICT_HDR_SPACE, DICT_HDR_PAGE_NO),          
  13.                  univ_page_size, RW_X_LATCH, mtr);          
  14.     header = DICT_HDR + buf_block_get_frame(block);          
  15.  
  16.     buf_block_dbg_add_level(block, SYNC_DICT_HEADER);          
  17.  
  18.     return(header);          
  19. }   

注意这里的 DICT_HDR_SPACE, DICT_HDR_PAGE_NO分别是宏定义

  1. /* Space id and page no where the dictionary header resides */    
  2. #define DICT_HDR_SPACE      0   /* the SYSTEM tablespace */    
  3. #define DICT_HDR_PAGE_NO    FSP_DICT_HDR_PAGE_NO    
  4.  
  5. #define FSP_DICT_HDR_PAGE_NO        7   /*!< data dictionary header     
  6.                                   page, in tablespace 0 */  

space 0就是ibdata1的space_no,7当然就是引导块,这哥们连ibdata1都没拷贝,当然innodb数据字典自然不包含这些表了。其实也是上面描述的原理 。

 那么正确的拷贝的方式一定是停机后,整个数据目录进行拷贝,而不是仅仅拷贝需要的库的目录,否则innodb数据字典是不能正常加载的。

最后附带space 0的部分块解释

  1. /*--------------------------------------*/       
  2. #define FSP_XDES_OFFSET         0   /* !< extent descriptor */       
  3. #define FSP_IBUF_BITMAP_OFFSET      1   /* !< insert buffer bitmap */       
  4.                 /* The ibuf bitmap pages are the ones whose       
  5.                 page number is the number above plus a       
  6.                 multiple of XDES_DESCRIBED_PER_PAGE */       
  7. #define FSP_FIRST_INODE_PAGE_NO     2   /*!< in every tablespace */       
  8.                 /* The following pages exist       
  9.                 in the system tablespace (space 0). */       
  10. #define FSP_IBUF_HEADER_PAGE_NO     3   /*!< insert buffer       
  11.                         header page, in       
  12.                         tablespace 0 */       
  13. #define FSP_IBUF_TREE_ROOT_PAGE_NO  4   /*!< insert buffer       
  14.                         B-tree root page in       
  15.                         tablespace 0 */       
  16.                 /* The ibuf tree root page number in       
  17.                 tablespace 0; its fseg inode is on the page       
  18.                 number FSP_FIRST_INODE_PAGE_NO */       
  19. #define FSP_TRX_SYS_PAGE_NO     5   /*!< transaction       
  20.                         system header, in       
  21.                         tablespace 0 */       
  22. #define FSP_FIRST_RSEG_PAGE_NO      6   /*!< first rollback segment       
  23.                         page, in tablespace 0 */       
  24. #define FSP_DICT_HDR_PAGE_NO        7   /*!< data dictionary header       
  25.                         page, in tablespace 0 */       
  26. ****/*--------------------------------------*/****   

【编辑推荐】

  1. MySQL技能提升篇 — sqlyog高级应用
  2. 深度解读 | 阿里云新一代关系型数据库 PolarDB
  3. 如何使用简单的SQL查询在MySQL中计算中位数
  4. GitHub的MySQL基础架构自动化测试
  5. 苹果开源iOS内核,全球数据库排名MySQL三连跌
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

C#入门经典(第3版)

本书将全面介绍C#编程的所有知识,共分为5篇:第1篇是C#语言:介绍了C#语言的所有内容,从基础知识到面向对象的技术,应有尽有。第2篇是Win...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
× Python最火的编程语言