First, background

The development language used by the company is PHP Laravel framework, through THE OPERATION of PHP Artisan Migrate, the database is abnormal, and then execute this SQL statement, error message is as follows:



Error: The table already exists.

Second, the processing process

2.1 Viewing the Current Fault

A file named # sqL-ib334 exists in the MySQL datadir directory.

[tom@tom_test_001 Tom]$CD /data/ data/mysql_3306/ Tom / [wenba@langyage_test_001 langyage]$ls -- L-RW-rw ---- 1 wenba Wenba 9.9K Dec 13 21:59 # SQL-227b_25.frm-RW-rw ---- 1 WENba WENba 423M Dec 13 22:00 # SQL-ib334.ibdCopy the code

I sought help from the Internet, found some articles on the Internet, and combined with the handling suggestions given on the Internet for operation, but failed (see:

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html).

During the process, I also consulted my classmate and told him that this was a temporary table and the system would recycle it by default, but the. FRM file was lost due to some problems in the system, so the file still existed when I checked it through MySQL.

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 | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 347 langyage/#sql-ib334 | 1 | 25 | 333 | Antelope | Compact | 0 |Copy the code

During this process, mysqladmin shutdown was also performed, the file was removed, and then started again, but it did not work because the key to the problem was not found.

2.2 Request a master

Consult teacher Wu of Zhidu Tang, and conduct problem investigation. Teacher Wu had a detailed understanding of my current situation, and looked at the data directory, and soon found the problem: I was missing a SQL-IB334.frm file, and gave the solution, that is, by creating a new table to restore.

mysql> use langyage
Database changed

mysql> create table abc like lyg_question_bank_detail_bak; 
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE `langyage`.`abc` ADD COLUMN `minor` TINYINT NOT NULL DEFAULT 0 AFTER `source`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0Copy the code

Copy ABC. FRM from ABC table to another directory and change the name to # sqL-ib334.frm

[wenba@langyage_test_001 ~]$ mv abc.frm  \#sql-ib334.frm
[wenba@langyage_test_001 ~]$ cp \#sql-ib334.frm  /data/data/mysql_3306/langyage/Copy the code

Pay attention to file permissions, because my data permissions are the same, so do not change

If everything is ok, you can execute the following command:

mysql> DROP TABLE `#mysql50##sql-ib334`; Mysql > SELECT * FROM information_schema. INNODB_SYS_TABLES WHERE NAME LIKE '%# SQL %';  The Empty set (0.00 SEC)Copy the code

Finally, verify that the table has been deleted and that the database field modification operation can be performed with the development validation table.

The previous #mysql50# ignores insecure encodings in files, which is supported in MySQL5.1 later.

Third, summary

When there is a problem, although I know I need to delete the table, the deletion keeps getting wrong and I don’t really get to the root of the problem and understand the process of MySQL database building tables.

This failure taught me not to make field changes directly on a large table database because of the risk of database exceptions, but to learn more about INFORMATION_SCHEMA so that similar problems can be handled in the future.

More know some awesome people, you will slowly awesome up! Thank you for this crouching tiger hidden dragon platform, thanks to Teacher Wu escort ~


The original post was published on December 24, 2017

Author: Xia Houdao

This article is from the cloud habitat community partner “Lao Ye Teahouse”. For relevant information, you can follow the wechat public account of “Lao Ye Teahouse”