今天遇到一個關於MySQL求助的問題，修改表結構時遇到“ERROR 1050 (42S01): table xxx already exits" mysql> ALTER TABLE DAY_BOOK_REPORT ADD UNIT_PRICE_PCS DOUBLE(12,2) DEFAULT NU... ...
今天遇到一個關於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-table tablespace, 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開頭的這些文件，然後拷貝源表數據到備份表，接著刪除原表，最後將備份表重命名為源表。添加相關索引。這種方法也能解決這個問題。
