MySQL如何判别InnoDB表是独立表空间还是共享表空间

澳门新葡亰赌995577 3

 

 

 InnoDB采用按表空间(tablespace)的方式进行存储数据,
默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件,
该文件就是默认的表空间文件(tablespce
file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果没有设置innodb_file_per_table的话,
那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间,独立表空间的命名规则为:表名.idb.
这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其它信息还是存放在共享表空间中,那么如何判别数据库中哪些表是独立表空间,哪些表是共享表空间呢?

今天遇到一个关于MySQL求助的问题,修改表结构时遇到“ERROR
1050 (42S01): table xxx already exits”

 

 

 

mysql>
ALTER TABLE DAY_BOOK_REPORT  ADD
UNIT_PRICE_PCS  
DOUBLE(12,2) DEFAULT NULL;

 

ERROR
1050 (42S01): TABLE ‘INVGSP/#SQL-IB379’ ALREADY EXISTS

方法1:通过ibd文件判别

mysql>

 

 

 

检查了后,发现表DAY_BOOK_REPORT确实不存在字段UNIT_PRICE_PCS,但是给表加字段时就报这个错误,遂咨询了一下他具体的操作过程,反馈是当时在做大量数据更新,然后给这个表增加字段时,突然报“DB
connect fail”,
登录MySQL服务器检查发现MySQL服务已经挂了,MySQL版本为5.6.20-enterprise-commercial-advanced-log,检查错误日志,发现有下面错误信息:

如果表的存储引擎是InnoDB,而且表空间(tablespace)是共享表空间的话,那么数据库对应目录下面是没有”表名.ibd”文件的。独立表空间的表的话,则有”表名.ibd”文件。只是这个方法很笨,对于生产环境,大量的表通过这种方式判别,确实不是一个好方法。

 

 

2018-03-31 23:29:16 7f09c1830700 InnoDB: Error: Write to file ./INVOICE/#sql-ib379.ibd failed at offset 600834048.

InnoDB: 1048576 bytes should have been written, only 446464 were written.

InnoDB: Operating system error number 0.

InnoDB: Check that your OS and file system support files of this size.

InnoDB: Check also that the disk is not full or a disk quota exceeded.

InnoDB: Error number 0 means 'Success'.

InnoDB: Some operating system error numbers are described at

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

15:29:16 UTC - mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

 

key_buffer_size=8388608

read_buffer_size=131072

max_used_connections=120

max_threads=151

thread_count=6

connection_count=6

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68245 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

 

Thread pointer: 0x9ac95e0

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 7f09c182fe10 thread_stack 0x40000

/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x946155]

/usr/sbin/mysqld(handle_fatal_signal+0x3d8)[0x6a58c8]

/lib64/libpthread.so.0[0x3a6b60f710]

/usr/sbin/mysqld[0xa45a2b]

/usr/sbin/mysqld[0xa50f5a]

/usr/sbin/mysqld[0x9e1afd]

/usr/sbin/mysqld[0x9e55a5]

/usr/sbin/mysqld[0x96aec5]

/usr/sbin/mysqld[0x7790a5]

/usr/sbin/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x1e54)[0x77b204]

/usr/sbin/mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x4a5)[0x87fab5]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3d4f)[0x72aa4f]

/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x318)[0x72de48]

/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x11b6)[0x72f7f6]

/usr/sbin/mysqld(_Z10do_commandP3THD+0xd7)[0x7310a7]

/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6f8856]

/usr/sbin/mysqld(handle_one_connection+0x45)[0x6f8935]

/usr/sbin/mysqld(pfs_spawn_thread+0x126)[0xb153e6]

/lib64/libpthread.so.0[0x3a6b6079d1]

/lib64/libc.so.6(clone+0x6d)[0x3a6b2e89dd]

 

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (7f095e93b2e0): is an invalid pointer

Connection ID (thread ID): 4237691

Status: NOT_KILLED
mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.01 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table Independent_tablespace(name  varchar(64));

Query OK, 0 rows affected (0.03 sec)

 

mysql> exit

 

[root@DB-Server ~]# cd /data/mysql/MyDB/

[root@DB-Server MyDB]# ls -lrt Independent_tablespace*

-rw-rw---- 1 mysql mysql  8560 Aug 21 22:05 Independent_tablespace.frm

-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd

[root@DB-Server MyDB]# 

 

 

 

在配置文件my.cnf里面设置innodb_file_per_table=0,重启MySQL服务,创建表common_tablespace,你会在数据目录看到只有common_tablespace.frm文件。

从错误提示看,MySQL在往./INVGSP/#sql-ib379.ibd文件写入数据时,遇到了错误,但是最终写入成功(InnoDB:
Operating system error number
0.),按错误日志里面的信息提示排查问题:

 

 

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table common_tablespace(name varchar(64));

Query OK, 0 rows affected (0.02 sec)

 

mysql> exit

Bye

[root@DB-Server MyDB]# ls -lrt common_tablespace*

-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm

[root@DB-Server MyDB]# 

InnoDB:
Check that your OS and file system support files of this size.

 

InnoDB:
Check also that the disk is not full or a disk quota exceeded.

 

 

方法2:使用INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES来判别。

最终检查发现MySQL数据文件所在的分区已经爆了,看错误提示,很有可能是因为空间问题,导致MySQL进程Crash掉了,而MySQL在ALTER
TABLE操作过程中崩溃,那么最终可能会在InnoDB表空间中生成一个孤立的中间表(orphaned
intermediate table)。
其实#sql-ib379.ibd就是在修改DAY_BOOK_REPORT时,由于MySQL进程Crash掉后生成的孤立中间表。检查如下所示:

 

 

 

mysql> show variables like '%innodb_file_per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

|      650 | INVOICE/#sql-ib379 |    1 |     65 |   636 | Antelope    | Compact    |             0 |

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.04 sec)

 

mysql>

MySQL
5.6

 

 

澳门新葡亰赌995577 1

MySQL
5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
关于这个系统表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用),INNODB的表空间信息。

 

 

 

The
INNODB_SYS_TABLESPACES
table provides metadata about InnoDB tablespaces, equivalent to the
information in the SYS_TABLESPACES table in the InnoDB data
dictionary.

官方文档https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html关于孤立中间表的介绍如下:

 

 

  
花了点时间了解了一下INFORMATION_SCHEMA数据库下面的INNODB_SYS_TABLESPACES这个表,遂写了一个SQL来判断那些InnoDB引擎表是独立表空还是共享表空间

Orphan
Intermediate Tables

 

 

共享表空间:

If
MySQL exits in the middle of an in-place ALTER TABLE operation
(ALGORITHM=INPLACE), you may be left with an orphan intermediate table
that takes up space on your system. This section describes how to
identify and remove orphan intermediate tables.

 

Intermediate
table names begin with an #sql-ib prefix (e.g., #澳门新葡亰赌995577,sql-ib87-856498050).
The accompanying .frm file has an #sql-* prefix and is named
differently (e.g., #sql-36ab_2.frm).

 

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'共享表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

To
identify orphan intermediate tables on your system, you can view Table
Monitor output or query INFORMATION_SCHEMA.INNODB_SYS_TABLES.
Look for table names that begin with #sql. If the original table
resides in a file-per-table
tablespace, the tablespace file (the #sql-*.ibd file) for the orphan
intermediate table should be visible in the database directory.

 

 

澳门新葡亰赌995577 2

 

 

找到对应的frm文件(这里是#sql-71a_40a97b.frm
),然后将其命名为#sql-ib379.frm(数据文件为#sql-ib379.ibd),
然后删除表(对应的文件会删除)即可解决上面这个问题。

不过这个脚本有个小小的bug,对于包含特殊字符的表名,有可能出现错误情况,这个是因为如果表名包含特殊字符,那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了转义处理,如下所示

 

 

#
mv “#sql-71a_40a97b.frm” “#sql-ib379.frm”

澳门新葡亰赌995577 3

 

 

mysql>  DROP TABLE
`#mysql50##sql-ib379`

独立表空间

    -> ;

 

Query
OK, 0 rows affected (0.11 sec)

 

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'独立表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE T.TABLE_SCHEMA='MyDB'  AND T.ENGINE='InnoDB';