List:General Discussion« Previous MessageNext Message »
From:Rick James Date:June 25 2013 3:46pm
Subject:RE: How can I drop a table that is named “logs/#sql-ib
203” and appeared after MySQL crash?
View as plain text  
If a crash occurs in the middle of an ALTER, the files may not get cleaned up.

> -----Original Message-----
> From: Reindl Harald [mailto:h.reindl@stripped]
> Sent: Thursday, June 20, 2013 12:57 PM
> To: mysql@stripped
> Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
> appeared after MySQL crash?
> 
> i know what happened but how get rid of these two bullshit files after
> *three years* not touched and used by mysqld
> 
> Am 20.06.2013 21:28, schrieb Rick James:
> > #sql files are temp tables that vanish when the ALTER (or whatever)
> finishes.  If you find one sitting around, it sounds like a crash happened
> in the middle of the ALTER.
> >
> >> -----Original Message-----
> >> From: Reindl Harald [mailto:h.reindl@stripped]
> >> Sent: Wednesday, June 19, 2013 12:19 PM
> >> To: mysql@stripped
> >> Subject: Re: How can I drop a table that is named
> “logs/#sql-ib203”
> >> and appeared after MySQL crash?
> >>
> >>
> >>
> >> Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
> >>> `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
> >>> but when trying to `ALTER` the table that was being changed during
> >>> the crash MySQL complains about the existence of the table
> >>> `logs/#sql-
> >> ib203`:
> >>>
> >>>> ERROR 1050: Table 'logs/#sql-ib203' already exists
> >>>>
> >>>> SQL Statement:
> >>>>
> >>>> ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
> >>>> `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
> >>>
> >>> DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
> >>> variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively
> >>> Error
> >> Code: 1051.
> >>> Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown
> >>> table
> >>>
> >>> I use innodb_file_per_table. There was a "mysql_datadir/logs/#sql-
> >> ib203.ibd"
> >>> file (or maybe .frm, sorry I forgot) that I deleted.
> >>>
> >>> Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
> >>
> >> interesting question, i have identical crap since 2009 also after a
> >> crash und these "blind table" has the same structure as a used
> >> existing one
> >>
> >> if i delete the #-files mysql whines every startup while they are
> >> never used and it is ridiculous that there are references in the
> >> table space to this useless crap and no mysql version from 5.1.8 to
> >> 5.5.32 is fixing this
> >>
> >> -rw-rw---- 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
> >> -rw-rw---- 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd

Thread
How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt19 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald19 Jun
    • RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Rick James20 Jun
      • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald20 Jun
        • RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Rick James25 Jun
          • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Reindl Harald25 Jun
      • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Denis Jedig20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Carsten Pedersen20 Jun
    • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Franck Dernoncourt20 Jun
  • Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?Jesper Wisborg Krogh21 Jun