RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
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
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
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.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 -- 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 signature.asc Description: OpenPGP digital signature
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi Frank, On 20/06/2013 05:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `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`: It's a bit of a workaround, but you should be able to get rid of the file using the steps below. I'm using an example where I killed mysqld while it was dropping the to_date column from the salaries table in the employees sample database: mysql SHOW CREATE TABLE salaries\G *** 1. row *** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql ALTER TABLE salaries DROP COLUMN to_date; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql$ ls -1 employees/#* employees/#sql-36ab_2.frm employees/#sql-ib30.ibd 1. Create a temporary table with the same structure as the salaries table would have looked after the ALTER that failed: mysql CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN to_date; 2. Shutdown MySQL. 3. Copy the .frm file from the tmp table to have the same name as the #sql-*.ibd file: mysql$ cp employees/tmp.frm employees/#sql-ib30.frm 4. Start MySQL again. 5. Drop the #sql-ib30.frm table: mysql DROP TABLE `#mysql50##sql-ib30`; Query OK, 0 rows affected (0.01 sec) 6. Do the same for the #sql*.frm file (it'll get removed even though you get an error): mysql DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2' I know it's not very elegant, but should work. The #mysql50# prefix tells MySQL to not encode the table name when mapping to the file system (https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). Best regards, Jesper Krogh MySQL Support
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Franck, Am 19.06.2013 21:00, schrieb Franck Dernoncourt: A table `logs/#sql-ib203` appeared after a MySQL crash the #sql-ibtableID tables are temporarily created during an ALTER TABLE operation for recovery purposes. Apparently these temporary tables might stay in certain circumstances even after recovery is completed. If you already tried enclosing the table name in backticks (DROP TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success, copying the table along with all its data and dropping the original table afterwards or running `mysqldump database tablename dump.sql mysql dump.sql` for a backup/restore operation at least will help the problem of being unable to run ALTER TABLE commands for the affected main table. Regards, Denis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
This may be a naive question, but I'm not sure I can see you've covered this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the logs/ bit)? / Carsten On 19-06-2013 21:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `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 'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of these error messages display 'logs/#sql-ib203', which is the table name MySQL complains it exists when I try to do ALTER. 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`? I use MySQL 5.6.12-winx64 and InnoDB. Thanks, Franck Dernoncourt fran...@mit.edu http://francky.me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig d...@syneticon.net wrote: If you already tried enclosing the table name in backticks (DROP TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success, Yep, no success with: DROP TEMPORARY TABLE `logs/#sql-ib203`; DROP TEMPORARY TABLE `/#sql-ib203`; DROP TEMPORARY TABLE `#sql-ib203`; DROP TEMPORARY TABLE `sql-ib203`; USE logs; DROP TEMPORARY TABLE `logs/#sql-ib203`; USE logs; DROP TEMPORARY TABLE `/#sql-ib203`; USE logs; DROP TEMPORARY TABLE `#sql-ib203`; USE logs; DROP TEMPORARY TABLE `sql-ib203`; copying the table along with all its data and dropping the original table afterwards or running `mysqldump database tablename dump.sql mysql dump.sql` for a backup/restore operation at least will help the problem of being unable to run ALTER TABLE commands for the affected main table. I copied the data to a new table with a different name, but I wish there were a more subtle way to solve the issue :) I haven't dropped the original table yet, so I cannot confirm this will solve the issue but hopefully it will. On Thu, Jun 20, 2013 at 1:32 PM, Carsten Pedersen cars...@bitbybit.dkwrote: This may be a naive question, but I'm not sure I can see you've covered this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the logs/ bit)? Thanks, I tried the following, none of them worked: DROP TABLE `logs/#sql-ib203`; DROP TABLE `/#sql-ib203`; DROP TABLE `#sql-ib203`; DROP TABLE `sql-ib203`; USE logs; DROP TABLE `logs/#sql-ib203`; USE logs; DROP TABLE `/#sql-ib203`; USE logs; DROP TABLE `#sql-ib203`; USE logs; DROP TABLE `sql-ib203`; Also, I can create tables with that name: USE logs; CREATE TABLE `logs/#sql-ib203` (id int); USE logs; CREATE TABLE `/#sql-ib203` (id int); USE logs; CREATE TABLE `#sql-ib203` (id int); USE logs; CREATE TABLE `sql-ib203` (id int); It does not conflict with any existing tables. Here is an example where I CREATE and DROP `logs/#sql-ib203`: step 1 14:47:48 USE logs 0 row(s) affected 0.000 sec step 2 14:47:48 CREATE TABLE `logs/#sql-ib203` (id int) 0 row(s) affected 0.047 sec step 3 14:47:53 CREATE TABLE `logs/#sql-ib203` (id int) Error Code: 1050. Table 'logs/#sql-ib203' already exists 0.000 sec step 4 14:48:01 DROP TABLE `logs/#sql-ib203` 0 row(s) affected 0.047 sec step 5 14:48:05 DROP TABLE `logs/#sql-ib203` Error Code: 1051. Unknown table 'logs.logs/#sql-ib203' 0.000 sec step 6 14:48:30 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` Error Code: 1050. Table 'logs/#sql-ib203' already exists 0.016 sec DROP TABLE `logs/#sql-ib203` at step 4 works, which would allow me to do step 2 CREATE TABLE `logs/#sql-ib203` (id int) again, but step 6 ALTER TABLE still complains about the existence of 'logs/#sql-ib203'. One last remark: the main file `ibdata1` contains references to `logs/#sql-ib203`, which is not surprising given the error message I have when trying to ALTER the original table. Is there any way to clean the file `ibdata1` so that it only contains references to tables having an actual data file? I use InnoDB with innodb_file_per_table Thanks for your help, Franck Dernoncourt fran...@mit.edu http://francky.me
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
On Thu, Jun 20, 2013 at 3:28 PM, Rick James rja...@yahoo-inc.com wrote: #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. Yes the crash happened during an ALTER: the table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while executing the following query: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` , ADD INDEX `username_event_type_idx` (`username` ASC, `event_type` ASC) ; Shouldn't the recovery take care of cleaning the temporary tables created during the query running at the time of the crash? Or at least, if not, shouldn't the DROP be working on this temporary table?
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 signature.asc Description: OpenPGP digital signature
RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
#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
How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `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 'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of these error messages display 'logs/#sql-ib203', which is the table name MySQL complains it exists when I try to do ALTER. 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`? I use MySQL 5.6.12-winx64 and InnoDB. Thanks, Franck Dernoncourt fran...@mit.edu http://francky.me
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 signature.asc Description: OpenPGP digital signature
