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
