Adam, I am sorry, I tested the procedure below and noticed that there was an assertion that fails. I have now fixed the assertion to 4.0.16 and 4.1.1. You have to wait for those versions. Meanwhile, you can use the innodb_table_monitor to check what those #sql... tables are.
If mysqld crashed in the middle of an ALTER TABLE, then in crash recovery InnoDB probably rolled back all insertions to those tables, and they are now empty. Generally the procedure in >= 4.0.16 and >= 4.1.1 to recover a table whose name is #sql... is: You can use CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; with an arbitrary table definition. InnoDB ignores the table definition anyway, because internally the above is converted to a RENAME TABLE. Then delete the file rsql..._recover_innodb_tmp_table.frm and rename the file (in Linux you must enclose the file name #sql... in double quotes " ") #sql....frm to rsql..._recover_innodb_tmp_table.frm Then you should be able to access the table rsql..._recover_innodb_tmp_table Best regards, Heikki ----- Original Message ----- From: "Adam Hardy" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, October 05, 2003 11:11 PM Subject: Re: can NOT drop the database > Just got back onto this again now. > > 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]
