If a crash occurs in the middle of an ALTER, the files may not get cleaned up.
> -----Original Message----- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Thursday, June 20, 2013 12:57 PM > To: mysql@lists.mysql.com > 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.rei...@thelounge.net] > >> Sent: Wednesday, June 19, 2013 12:19 PM > >> To: mysql@lists.mysql.com > >> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql