Shulou(Shulou.com)05/31 Report--

This article mainly introduces the implementation of MySQL DDL error ERROR 1050 (42S01): Table 'sms/#sql-ib752' already exist how to do, the article is very detailed, has a certain reference value, interested friends must read it!

An error was reported when executing the ALTER TABLE add field:

Mysql > ALTER TABLE who_sku_relation ADD COLUMN red_line_price DECIMAL (10jue 2) NOT NULL DEFAULT '0.00The error 1050 (42S01): Table' sms/#sql-ib75227' already exists

Reason for reporting an error:

During the alter table process, MySQL goes down, and there is an intermediate table of data changes in the data catalog. The intermediate table is a temporary table that starts with "# sql-".

The temporary table cannot be executed on the corresponding table because it is not manually deleted.

Looking at the temporary table information in the data directory, the .frm table structure file and .ibd data file were not found.

$ls-l | grep'# sql-ib75227*' | wc-l

Solution:

Manually create .frm table structure files and .ibd data files, and then delete temporary tables.

Create a test table.

Mysql > use testmysql > create table test.tmp like sms.who_sku_relation;Query OK, 0 rows affected (0.00 sec)

Copy the .frm table structure file of the test table into the .frm table structure file and .ibd data file of the temporary table.

$cp.. / test/tmp.frm "# sql-ib75227.frm" $cp.. / test/tmp.frm "# sql-ib75227.ibd" $ls-l\ # sql-ib75227*-rw-r- 1 tungsten mysql 15007 Sep 29 21:23 # sql-ib75227.frm-rw-r- 1 tungsten mysql 15007 Sep 29 21:25 # sql-ib75227.ibd

DROP temporary tables in the database and perform structural changes on the tables where there was an error in the structure of the added tables.

Mysql > drop table `# mysql50##sql- ib75227`; Query OK, 0 rows affected (0. 00 sec) mysql > ALTER TABLE who_sku_relation ADD COLUMN red_line_price DECIMAL (10 sec 2) NOT NULL DEFAULT'0. 00 error error 1813 (HY000): Tablespace for table 'sms/#sql-ib75227' exists. Please DISCARD the tablespace before IMPORT.

Delete the residual temporary table data file in the data file.

$ls-l\ # sql-ib75227*-rw-r- 1 tungsten mysql 15007 Sep 29 21:25 # sql-ib75227.ibd$ rm "# sql-ib75227.ibd"

Perform database table structure changes

Mysql > ALTER TABLE who_sku_relation ADD COLUMN red_line_price DECIMAL (10 sec 2) NOT NULL DEFAULT '0.00The query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 is all the contents of the article "MySQL 5.6 execute DDL error ERROR 1050 (42S01): what to do about Table' sms/#sql-ib752' already exist". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.