Shulou(Shulou.com)05/31 Report--

This article shows you how to parse the MySQL ERROR 1146 Table doesnt exist. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

☉ source code version 5.7.14

When MYSQL uses innodb, we sometimes see the following error:

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

First of all, summarize the reasons:

Missing frm file

The innodb data dictionary does not contain this table

Let's focus on case 2, because case 1 is obvious.

At some point when using the innodb storage engine, we can see this table by show tables, but if we do anything, we will report an error as follows:

Mysql > show tables; | test1bak | mysql > desc test1bak; ERROR 1146 (42S02): Table 'test.test1bak' doesn't exist

Perhaps you will say that I can clearly see this table, ah, why does the visit still report an error? In fact, it should be clear that innodb has its own data dictionary, which can be seen by show tables as long as there is a frm file, but whether the table structure can be opened normally in innodb also depends on innodb's data dictionary, which mainly includes:

1 、 INNODB_SYS_columns

2 、 INNODB_SYS_FIELDS

3 、 INNODB_SYS_TABLES

4 、 INNODB_SYS_INDEXES

If an error occurs, the first thing we need to check is whether the INNODB_SYS_TABLES contains the information for this table. Maybe in these data dictionaries, maybe some columns are not so clear, such as

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 | +- -+

For example, the FLAG column 33 here is actually a bitmap representation, which represents the following information:

/ * 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 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

Next, let's analyze why FLAG is 33 as follows:

The binary of 33 is 00100001, starting from the low bit 1: from the source code comments, the standard COMPACT/COMPRESSED/DYNAMIC is 10000: ZIP_SSIZE flag these four bits are used to support compression functions such as COMPRESSED1:ATOMIC_BLOBS flag this bit is the main difference between COMPACT and DYNAMIC, please see source code annotation 0:DATA DIRECTORY and innodb-file-per-table flag in order to support DATA DIRECTORY syntax 0:SHARED tablespace flag to support TABLESPACE syntax

Then let's test it:

If we set up the following table: CREATE TABLE T2 (C1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table DATA DIRECTORY ='/ root/mysql5.7.14/percona-server-5.7.14-7 max MySQL Testbank varqld.1' Its type is 97 binary 01100001: using DATA DIRECTORY to build using ATOMIC_BLOBS and no compression, then DYNAMIC format is detailed in: 15.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory if we create the following table: CREATE TABLESPACE tt1 ADD DATAFILE'/ root/mysql5.7.14/tt1.ibd'; CREATE TABLE tsh (C1 INT) TABLESPACE tt1 ROW_FORMAT=COMPACT Its type is 129 binary and 10000001: use TABLESPACEsyntax to create a COMPACT format without ATOMIC_BLOBS and no compression. For more information, please see: 15.5.9 InnoDB General Tablespaces

We can see that using only 8 bits per byte can represent a lot of information, which is also the advantage of bitmaps, such as MTYPE/PRTYPE.

Next, let's get back to the subject, we need to see where this mistake came from. After trace, let's take a look at the main parts:

Note that trace is the main method to view function calls in mysql debug version. Refer to the official document 26.5.1.2 Creating Trace Files502 calls 2: | > ha_innobase::open_dict_table

503Troup2: | > dict_table_open_on_name 504Troup2: | dict_table_open_on_name: table: 'test/test1bak' 505Troup2: | > dict_table_check_if_in_cache_low 506Troup2: | | | dict_table_check_if_in_cache_low: table: 'test/test1bak' 507Troup2: | dict_load_table 509Troup2: | | dict_load_table: loading table:' test/test1bak' 510Troup2: | | > dict_table_check_if_in_cache_ | | low 511Troup2: | dict_table_check_if_in_cache_low: table: 'test/test1bak' 512Trou2: | | dict_load_table_one 514Troup2: | dict_load_table_one: table: test/test1bak 515Troup2: | | | | | > dict_table_check_if_in_cache_low 516 Troup2: | | dict_table_check_if_in_cache_low: table: 'SYS_TABLES' 517 Troup2: | btr_cur_search_to_nth_level 519 Troup2: | |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.