List:General Discussion« Previous MessageNext Message »
From:Franck Dernoncourt Date:June 20 2013 6:53pm
Subject:Re: How can I drop a table that is named “logs/#sq
l-ib203” and appeared after MySQL crash?
View as plain text  
On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig <dj@stripped> wrote:
>
> If you already tried enclosing the table name in backticks (DROP TABLE
> `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success,


Yep, no success with:
DROP TEMPORARY TABLE `logs/#sql-ib203`;
DROP TEMPORARY TABLE `/#sql-ib203`;
DROP TEMPORARY TABLE `#sql-ib203`;
DROP TEMPORARY TABLE `sql-ib203`;
USE logs; DROP TEMPORARY TABLE `logs/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `sql-ib203`;


>  copying the table along with all its data and dropping the original table
> afterwards or running `mysqldump database tablename > dump.sql && mysql
> <
> dump.sql` for a backup/restore operation at least will help the problem of
> being unable to run ALTER TABLE commands for the affected main table.


I copied the data to a new table with a different name, but I wish there
were a more subtle way to solve the issue :)
I haven't dropped the original table yet, so I cannot confirm this will
solve the issue but hopefully it will.


On Thu, Jun 20, 2013 at 1:32 PM, Carsten Pedersen <carsten@stripped>wrote:

> This may be a naive question, but I'm not sure I can see you've covered
> this: Have you tried "USE logs" before DROP TABLE `#sql-ib203` (without the
> "logs/" bit)?
>

Thanks, I tried the following, none of them worked:

DROP TABLE `logs/#sql-ib203`;
DROP TABLE `/#sql-ib203`;
DROP TABLE `#sql-ib203`;
DROP TABLE `sql-ib203`;
USE logs; DROP TABLE `logs/#sql-ib203`;
USE logs; DROP TABLE `/#sql-ib203`;
USE logs; DROP TABLE `#sql-ib203`;
USE logs; DROP TABLE `sql-ib203`;

Also, I can create tables with that name:
USE logs; CREATE TABLE `logs/#sql-ib203` (id int);
USE logs; CREATE TABLE `/#sql-ib203` (id int);
USE logs; CREATE TABLE `#sql-ib203` (id int);
USE logs; CREATE TABLE `sql-ib203` (id int);

It does not conflict with any existing tables.

Here is an example where I CREATE and DROP `logs/#sql-ib203`:
step 1 14:47:48 USE logs 0 row(s) affected 0.000 sec
step 2  14:47:48 CREATE TABLE `logs/#sql-ib203` (id int) 0 row(s)
affected 0.047
sec
step 3 14:47:53 CREATE TABLE `logs/#sql-ib203` (id int) Error Code: 1050.
Table 'logs/#sql-ib203' already exists 0.000 sec
step 4 14:48:01 DROP TABLE `logs/#sql-ib203` 0 row(s) affected 0.047 sec
step 5 14:48:05 DROP TABLE `logs/#sql-ib203` Error Code: 1051. Unknown
table 'logs.logs/#sql-ib203' 0.000 sec
step 6 14:48:30 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` Error
Code: 1050. Table 'logs/#sql-ib203' already exists 0.016 sec

DROP TABLE `logs/#sql-ib203` at step 4 works, which would allow me to do
step 2 CREATE TABLE `logs/#sql-ib203` (id int) again, but step 6 ALTER
TABLE still complains about the existence of 'logs/#sql-ib203'.

One last remark: the main file `ibdata1` contains references to
`logs/#sql-ib203`, which is not surprising given the error message I have
when trying to ALTER the original table. Is there any way to clean the file
`ibdata1` so that it only contains references to tables having an actual
data file? I use InnoDB with innodb_file_per_table

Thanks for your help,

----
Franck Dernoncourt
francky@stripped
http://francky.me

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