On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig <d...@syneticon.net> 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 <cars...@bitbybit.dk>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 fran...@mit.edu http://francky.me
