show tables能看到表却无法读写？

MySQL版本 5.7.14（社区版源码基础上增加一些debug代码）
在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的数据字典，主要的包含:


INNODB_SYS_columns
INNODB_SYS_FIELDS
INNODB_SYS_TABLES
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

其实大概步骤就是
Checks if a table is in the dictionary cache

根据dict_sys->table_hash寻找
Loads a table definition and also all its index definitions.
通过扫描字典的B+树进行加载
如果不能找到则报错
这样也就解释了为什么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 */
/--------------------------------------/

原文发布时间为：2017-09-06
本文作者：高鹏（八怪）
本文来自合作伙伴“老叶茶馆”，了解相关信息可以关注“老叶茶馆”微信公众号

时间： 2024-10-29 20:43:06

show tables能看到表却无法读写？的相关文章

MYSQL教程:检查数据表和修复数据表

数据库在运行中,会因为人为因素或一些不可抗力因素造成数据损坏.所以为了保护数据的安全和最小停机时间,我们需制定详细的备份/恢复计划,并定期对计划的有效性进行测试. 本章结合MySQL服务器的运行机制和所提供的工具,介绍如何进行数据库的备份.维护和修复. 以下是几点防范的措施: 制定一份数据库备份/恢复计划,并对计划进行仔细测试. 启动数据库服务器的二进制变更日志,该功能的系统开销很小(约为1%),我们没有理由不这样做. 定期检查数据表,防范于未燃. 定期对备份文件进行备份,以防备份文件失效. 把

mysql 锁表语句与解锁表详解

页级的典型代表引擎为BDB. 表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM. 行级的典型代表引擎为INNODB. 很多操作都是读表. 在严格条件的索引上读取和更新,当更新或者删除可以用单独的索引来读取得到时: UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; DELETE FROM tbl_name WHERE unique_key_col=key_value; SELECT 和 INSERT

将样式表加入到HTML中

样式表 有很多方法将样式表加入到HTML中,每个都带有自己的优点和缺点.新的HTML元素和属性已被加入以允许样式表与HTML文档更简易地组合起来. 连接到一个外部的样式表 嵌入一个样式表也就是输入一个样式表 内联样式 CLASS属性 ID属性 SPAN元素 DIV元素 关于认证的备注 ------------------------------------------- 连接到一个外部的样式表 一个外部的样式表可以通过HTML的LINK元素连接到HTML文档中: <LINK REL=StyleS

如何锁定MYSQL数据表

mysql|数据 在进行数据表检查或修补时,可以先将数据表锁定,可确保数据表的安全: mysql>LOCK TABLE tbl_name READ; mysql>FLUSH TABLES; 将数据表锁定后再进行检查或修补的工作.完成后再解除锁定: mysql>UNLOCK TABLES; //LOCK TABLE tbl_name READ表示要锁定成只读状态,在这个状态下用户只能读取数据表,不能写入. LOCK TABLE tbl_name WRITE则是更严格的锁定,用户不能读取也不

VC中对注册表进行操作

近来由于需要在自己写的程序中对注册表进行操作.总结些经验,并做个一个DEMO供日后使用,现在把它拿出来和大家分享- 为了使用方便,把一些操作写成了函数,以便方便调用,具体代码如下所示: 一.定义 HKEY hKey; char content[256]; //所查询注册表键值的内容 DWORD dwType=REG_SZ; //定义读取数据类型 DWORD dwLength=256; struct HKEY__*RootKey; //注册表主键名称 TCHAR *SubKey; //欲打开注册表

SQL Server游标运用：查看一个数据库所有表大小信息

一.背景 在性能调优或者需要了解某数据库表信息的时候,最直观的方式就是罗列出这个数据所有表的信息,这些信息包括:表的记录数.数据记录占用空间.索引占用空间.未使用的空间等(如Figure1所示),有了这些信息你可以简单的判断这个数据库来自数据上的压力可能是某个表造成的.因为表数据越大,对数据库性能的影响越大. 要实现某个数据库所有表的信息,可以通过游标的形式获取相应的数据,下图Figure1返回某数据库中所有表的信息: (Figure1:某数据库所有表信息) 也许你并不满足于Figure1的信息

SQLite入门之四表的增删攺查

 4.1 SQLite 存储类型 SQLite 存储类型 存储类型 描述 NULL 值是一个 NULL 值. INTEGER 值是一个带符号的整数,根据值的大小存储在 1.2.3.4.6 或 8 字节中. REAL 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字. TEXT 值是一个文本字符串,使用数据库编码(UTF-8.UTF-16BE 或 UTF-16LE)存储. BLOB 值是一个 blob 数据,完全根据它的输入存储. 4.2 创建数据库和创建表 创建了一个 COMPANY 表,

oracle表空间,角色，权限，表，索引，序列号，视图，同义词，约束条件，存储函数和过程，常用数据字典，基本数据字典信息，查看VGA信息，维护表空间，创建表空间等信息

查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_role_privs; 查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs;        结果可以是:        USERNAME                       PRIVILEGE                    

解决误删mysql表物理文件的方法

该方法只介绍了如何救回这个表名(数据不恢复) 如果想要恢复原来数据 直接用extundelete把文件恢复后放回去即可  并且是适用于平时没有全备的情况下  如果有全备 直接那全备的frm和idb文件放回去 就可以了  该方法同样适用于数据表迁移(只迁移一个表)  因为discard再import的速度 远比先dump再恢复的速度要快得多 建议: 平时备份一下表结构是非常重要的 -- 如果你直接删除了mysql的表文件 (.frm .idb)  在mysql5.6 可能你就悲剧了  可能再也用不