List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:June 25 2013 3:49pm
Subject:Re: How can I drop a table that
is named “logs/#sql-ib203” and appeare
d after MySQL crash?
View as plain text  
that is what we all know

but how to get rid of them?
but *why* they are not cleaned up?

* the global tablespace knows about them
* nothing is using them really
* so why can mysqld not cleanup this mess?

if you delete them all works fine but each start
the error-log is cluttered

Am 25.06.2013 17:46, schrieb Rick James:
> 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
> 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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