Adam, please Cc: [EMAIL PROTECTED], so that others can follow this discussion.
If the innodb_table_monitor does not show those #sql... tables in InnoDB's own data dictionary at all, then you can simply delete the .frm files. They are simply orphaned .frm files. You do not need to shut down mysqld for that. 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]> Lähetetty: Monday, October 06, 2003 1:14 PM Aihe: Re: can NOT drop the database > Thanks Heikki. > > In the output of innodb_table_monitor I can't actually see the name of > any orphaned tables. Anyway, it's not an issue until those versions are > released. > > Before then, I wonder if it is possible to delete the database directory > completely. Will that cause mysql problems? (Obviously I would shut down > the daemon first). > > Adam > > On 10/06/2003 09:38 AM Heikki Tuuri wrote: > > 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 > >> > > > > > > > > > > -- > 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]
