知識星球（點選上方公眾號，可快速關註）
來源：瀟湘隱者 ，
www.cnblogs.com/kerrycode/p/8692417.html
今天遇到一個關於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
mysql>
檢查了後，發現表DAY_BOOK_REPORT確實不存在欄位UNIT_PRICE_PCS，但是給表加欄位時就報這個錯誤，遂諮詢了一下他具體的操作過程，反饋是當時在做大量資料更新，然後給這個表增加欄位時，突然報“DB connect fail”， 登入MySQL伺服器檢查發現MySQL服務已經掛了，MySQL版本為5.6.20-enterprise-commercial-advanced-log，檢查錯誤日誌，發現有下麵錯誤資訊：
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在往./INVGSP/#sql-ib379.ibd檔案寫入資料時，遇到了錯誤，但是最終寫入成功（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.
最終檢查發現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>
官方檔案
https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
關於孤立中間表的介紹如下：
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).
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-tabletablespace, the tablespace file (the #sql-*.ibd file) for the orphan intermediate table should be visible in the database directory.
找到對應的frm檔案(這裡是#sql-71a_40a97b.frm )，然後將其命名為#sql-ib379.frm（資料檔案為#sql-ib379.ibd）， 然後刪除表（對應的檔案會刪除）即可解決上面這個問題。
# mv “#sql-71a_40a97b.frm” “#sql-ib379.frm”
mysql> DROP TABLE `#mysql50##sql-ib379`
-> ;
Query OK, 0 rows affected (0.11 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE ‘%#sql%’;
Empty set (0.01 sec)
mysql>
個人還測試了網上另外一種方法，就是首先先刪除#sql開頭的這些檔案，然後複製源表資料到備份表，接著刪除原表，最後將備份表重新命名為源表。新增相關索引。這種方法也能解決這個問題。
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) –根據實際情況輸入具體欄位
參考資料：
-
https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
看完本文有收穫？請轉發分享給更多人
關註「ImportNew」，提升Java技能
