MYSQL ERROR 1146 Table doesnt exist 解析

原创转载请注明出处

源码版本 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 */
****/*--------------------------------------*/****

作者微信：

时间： 2024-11-01 04:29:46

MYSQL ERROR 1146 Table doesnt exist 解析的相关文章

Mysql中大小写敏感问题导致的MySql Error 1146 Tabel doen’t exist错误_Mysql

MySql Error 1146 Tabel doen't exist,一般情况下是表不存在,或者表名写错了.权限问题. 今天遇到了这个问题,仔细核对几遍后,确认无误,又在phpMyAdmin里尝试select,可以查询出数据.又核对权限,确认该mysql用户具有所有权限.那这个错误是怎么回事呢?查询了一些资料后,发现如果mysql引擎不对可能会造成真实存在的表却无法访问,但是发现引擎都是myisam. 这下犯愁了,错误出在哪里呢?又找了半天,仍然无果.无辜的看着这段报错代码,发呆了半天,突然发

解决MySql error #1036 Table is read only 错误方法

这次网站搬家采用直接打包mysql数据库和网页文件的形式进行迁移,上传好mysql data目录里面的网站数据库至VPS上mysql存放数据库的目录里面,解压就行.我的VPS存放数据库的路径是 /usr/local/mysql/var. 上传好网站数据,解压,配置好数据库链接参数就行,网站就能正常连接上了,我本以为这已经是顺利迁移完成了,但后来操作的时候,发现只能读取数据库的内容,不能更改写入任何信息,提示#1036 – Table '* ' is read only (*号为任意表),也就是说

mysql中[ERROR] Native table &#039;performance_schema&#039;案例

环境介绍: 使用yum安装了一个mysql,因版本不符合需求,使用rpm卸载,又用rpm安装了一个版本的Mysql. 报错提示: Mysql安装完毕后,错误日志中报错: [ERROR] Native table 'performance_schema'.'xxxxx' hasthe wrong structure 解决方法: 在操作系统级别,执行mysql_upgrade 报错原因: 当前performance_schema的信息与mysql实际的情况不一致. 参考: If you instal

MySQL · 引擎特性 · DROP TABLE之binlog解析

Drop Table的特殊之处 Drop Table乍一看,与其它DDL 也没什么区别,但当你深入去研究它的时候,发现还是有很多不同.最明显的地方就是DropTable后面可以紧跟多个表,并且可以是不同类型的表,这些表还不需要显式指明其类型,比如是普通表还是临时表,是支持事务的存储引擎的表还是不支持事务的存储引擎的表等.这些特殊之处对于代码实现有什么影响呢?对于普通表,无论是创建还是删除,数据库都会产生相应的binlog日志,而对于临时表来说,记录binlog日志就不是必须的.对于采用不同存储引

ERROR OGG-00199 Table t3.T3_TAB does not exist in target database.

ERROR   OGG-00199  Table t3.T3_TAB does not exist in target database. 出现这个错误,是因为 自己是先建立好gg再导入的数据 其实应该先建立数据再见GG 就不会出现了问题.

thinkphp视图模型查询提示ERR: 1146:Table &#039;db.pr_order_view&#039; doesn&#039;t exist的解决方法_php实例

本文实例讲述了thinkphp视图模型查询失败提示:ERR: 1146:Table 'db.pr_order_view' doesn't exist解决办法.分享给大家供大家参考.具体方法如下: 一.问题描述: 想用thinkphp的视图模型进行关联查询,结果出现了这样的问题(log日志记录):ERR: 1146:Table 'db.pr_order_view' doesn't exist,我就纳闷,视图模型怎么出来的sql是这样的呢,视图模型如下: 复制代码 代码如下: class Order

thinkphp视图模型查询失败提示：ERR: 1146:Table ‘db.pr_order_view’ doesn’t exist

想用thinkphp的视图模型进行关联查询,结果出现了这样的问题(log日志记录):ERR: 1146:Table 'db.pr_order_view' doesn't exist,我就纳闷,视图模型怎么出来的sql是这样的呢,视图模型如下:  代码如下 复制代码 class OrderViewModel extends ViewModel { public $viewFields = array( 'Order' =>array( '_as'=>'Orders', //重新命名,以免与系统语

MySQL Error Code文档手册---摘自MySQL官方网站

手册 This chapter lists the errors that may appear when you call MySQL from any host language. The first list displays server error messages. The second list displays client program messages. Server error information comes from the following files: The

解决MySQL5.1安装时出现Cannot create windows service for mysql.error:0_Mysql

安装MySQL5.1过程中,我把以前MySQL5.0的GUI工具和服务器全部删掉,安装目录全部删掉,数据文件目录名字改掉,注册表用完美卸载清理了.  然后重启安装MySQL5.1(我以前遇到过服务启动不了的情况,这样做就搞定了),可配置到最后一步时出现Cannot create windows service for MySQL.error:0错误,把配置工具关掉重试几次都不行,后来百度了下,听说要在系统管理员用户下执行sc delete mysql删除MySQL服务后再启动配置工具配置,实在不