|List:||General Discussion||« Previous MessageNext Message »|
|From:||Jesper Wisborg Krogh||Date:||June 21 2013 8:10am|
|Subject:||Re: How can I drop a table that|
is named “logs/#sql-ib203” and appeare
d after MySQL crash?
|View as plain text|
Hi Frank, On 20/06/2013 05:00, Franck Dernoncourt wrote: > Hi all, > > A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space > shortage while deleting some attributes in a table in the `logs` database > and adding an index. > > `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but > when trying to `ALTER` the table that was being changed during the crash > MySQL complains about the existence of the table `logs/#sql-ib203`: It's a bit of a workaround, but you should be able to get rid of the file using the steps below. I'm using an example where I killed mysqld while it was dropping the to_date column from the salaries table in the employees sample database: mysql> SHOW CREATE TABLE salaries\G *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> ALTER TABLE salaries DROP COLUMN to_date; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql$ ls -1 employees/#* employees/#sql-36ab_2.frm employees/#sql-ib30.ibd 1. Create a temporary table with the same structure as the salaries table would have looked after the ALTER that failed: mysql> CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN to_date; 2. Shutdown MySQL. 3. Copy the .frm file from the tmp table to have the same name as the #sql-*.ibd file: mysql$ cp employees/tmp.frm employees/#sql-ib30.frm 4. Start MySQL again. 5. Drop the #sql-ib30.frm table: mysql> DROP TABLE `#mysql50##sql-ib30`; Query OK, 0 rows affected (0.01 sec) 6. Do the same for the #sql*.frm file (it'll get removed even though you get an error): mysql> DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2' I know it's not very elegant, but should work. The #mysql50# prefix tells MySQL to not encode the table name when mapping to the file system (https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). Best regards, Jesper Krogh MySQL Support