To quote the docs you pointed me at:
>>>>>>>>>>>>
If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. With innodb_table_monitor you see a table whose name is #sql..., but since MySQL does not allow accessing any table with such a name, you cannot dump or drop it. The solution is to use a special mechanism available starting from version 3.23.48 of InnoDB.
If you have an orphaned table #sql... inside the tablespace, then by calling
CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb;
where the table definition is similar to the temporary table, you can make InnoDB to rename the orphaned table to `rsql..._recover_innodb_tmp_table`. Then you can dump and drop the renamed table. The backquotes around the table name are needed because a temporary table name contains the character '-'.
<<<<<<<<<<<
It was a crash during an ALTER TABLE operation, I remember it, but it was a while back so I am unable to tell what these 2 orphan table definitions are - even what the tables were called unfortunately.
According to the blurb above I need the definition to rename the orphan table. So it looks like I'm stuck.
Even so I tried a few random guesses at the table definitions without getting anywhere. It seems mysql doesn't like my backquotes - with my en_UK keyboard I have "`" which I think should be OK, so how come I get an error?
Have you got any more suggestions?
Thanks Adam
On 10/01/2003 03:30 PM Heikki Tuuri wrote:
Adam,
you can use the innodb_table_monitor
http://www.innodb.com/ibman.html#InnoDB_Monitor
and the advice at
http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict
to resolve the problem.
Best regards,
Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html
----- Alkuperäinen viesti ----- Lähettäjä: "Adam Hardy" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
Lähetetty: Wednesday, October 01, 2003 3:24 PM
Aihe: Re: can NOT drop the database
Hi Heikki,
a similar problem happened to me and I got the error:
ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30'
After reading your post, I checked in the data directory and there are two files there, both of file type data:
#sql-2b2_30.frm #sql-ffa_2.frm
I certainly didn't put them there myself - I think they must have come from mySQL automatically at some point.
Are they real tables in the database? I can't see them when I use 'show tables'. Is it a permissions problem? I have this on another database that I was trying to restore from a dump. I had to rename the database in the end.
I am using InnoDB tables for some of my data.
Regards Adam
-- Running mySQL 4.1.0 on Linux 2.4.20 RH9
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
