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

Thread
How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt19 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald19 Jun
    • RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Rick James20 Jun
      • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald20 Jun
        • RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Rick James25 Jun
          • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald25 Jun
      • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Denis Jedig20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Carsten Pedersen20 Jun
    • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Jesper Wisborg Krogh21 Jun