来自 新葡亰数据库 2019-11-10 16:20 的文章
当前位置: 澳门新葡亰app > 新葡亰数据库 > 正文

修正表结构时遇见,若无设置innodb

 

 

 InnoDB接收按表空间(tablespace)的法子开展仓库储存数据, 暗中认可配置意况下会有叁个早前大小为10MB, 名叫ibdata1的文本, 该文件正是私下认可的表空间文件(tablespce file卡塔尔国,客户可以由此参数innodb_data_file_path对其张开设置,可以有七个数据文件,若无安装innodb_file_per_table的话, 那多少个Innodb存款和储蓄类型的表的多少都坐落这几个分享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存款和储蓄引擎类型的表就能够生出三个独立表空间,独立表空间的命名准绳为:表名.idb. 那些单独的表空间文件仅存款和储蓄该表的数量、索引和插入缓冲BITMAP等音信,此外音讯依旧存放在分享表空间中,那么怎么着辨别数据库中怎么样表是独立表空间,哪些表是分享表空间啊?

前天遇上三个关于MySQL求助的标题,修正表结构时遇上“E奥迪Q5RO福睿斯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.卡塔 尔(英语:State of Qatar),按错误日志里面包车型地铁音信提醒各种核查难点:

 

 

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

 

 

图片 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.

法定文书档案

 

 

   花了点时间精晓了风流倜傥晃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., #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.

 

 

图片 2

 

 

找到呼应的frm文件(这里是#sql-71a_40a97b.frm ),然后将其取名字为#sql-ib379.frm(数据文件为#sql-ib379.ibd卡塔 尔(英语:State of Qatar), 然后删除表(对应的文本会去除卡塔 尔(阿拉伯语:قطر‎就可以减轻地点那几个难题。

可是那几个本子有个小小的bug,对于富含特殊字符的表名,有望现身谬误情况,这几个是因为假如表名包罗特殊字符,那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了转义管理,如下所示

 

 

# mv "#sql-71a_40a97b.frm" "#sql-ib379.frm"

图片 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';

 

 

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

图片 4

Empty set (0.01 sec)

 

 

 

mysql>

方法3 :INFORMATION_SCHEMA.INNODB_SYS_TABLES判别

 

 

 

 

个人还测试了网络别的生龙活虎种艺术,正是第生机勃勃先删除#sql初始的这个文件,然后拷贝源表数据到备份表,接着删除原表,最终将备份表重命名称叫源表。增多相关索引。这种艺术也能一下子就解决了那一个主题素材。

MySQL 5.7

 

 

mysql> show index from DAY_BOOK_REPORT;

 

mysql> create table DAY_BOOK_REPORT_BK as select * from DAY_BOOK_REPORT;

 

mysql> drop table DAY_BOOK_REPORT;

 

mysql> rename table DAY_BOOK_REPORT_BK to DAY_BOOK_REPORT;

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD INDEX INDEX_NAME (column_list) --根据实际情况输入具体字段

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD UNIQUE (column_list) --根据实际情况输入具体字段

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD PRIMARY KEY (column_list) --根据实际情况输入具体字段

     假若是MySQL 5.7的话, 比MySQL 5.6有多了黄金时代种艺术,MySQL 5.7 的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE字段,然则其值全体为Single,而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了字段SPACE_TYPE, 其值有Single与System 分别代表单独表空间和分享表空间。

 

 

参谋资料:

 

 

#独立表空间

  

 

SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='Single';

 

 

 

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='YourDatabase'  AND T.ENGINE='InnoDB';

 

#分享表空间

 

SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='System';

 

 

   

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='YourDatabase' AND T.ENGINE='InnoDB'

 

 

 

 

方法4: INFORMATION_SCHEMA.INNODB_TABLES判别

 

 

MySQL 8.0

 

借使是MySLQ 8.0的话,还多了风姿浪漫种形式, 那正是通过INFORMATION_SCHEMA.INNODB_TABLES来剖断,这么些新扩大的系统表可以因此SPACE_TYPE字段来分歧分享表空间与独立表空间

 

SELECT * FROM  INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE_TYPE=’Single’;

 

 

 

参考资料

 

本文由澳门新葡亰app发布于新葡亰数据库,转载请注明出处:修正表结构时遇见,若无设置innodb

关键词: