On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig <dj@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 <carsten@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
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!