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: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
SHOW GLOBAL STATUS LIKE 'Innodb%'; Then do some math -- usually dividing by Uptime. That will give you some insight in how hard the I/O is working, and how full the buffer_pool is. -Original Message- From: Rafał Radecki [mailto:radecki.ra...@gmail.com] Sent: Friday, June 21, 2013 4:59 AM To: mysql@lists.mysql.com Subject: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs? Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UPDATE_TIME for InnoDB in MySQL 5.7
Yeah, why not flush them to disk on a clean shutdown, and periodically before that? -Original Message- From: Dotan Cohen [mailto:dotanco...@gmail.com] Sent: Sunday, June 23, 2013 10:39 AM To: mysql. Subject: UPDATE_TIME for InnoDB in MySQL 5.7 The MySQL 5.7 changelog mentions: Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. This is great news! However, I would in fact need the UPDATE_TIME to persist across database server resets. Is this feature being considered or discussed? Where might I find it online? Thank you to the MySQL team and to Oracle for filling in InnoDB;s missing features! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: space gone after MyISAM REPAIR TABLE
Switch to InnoDB so you won't have to repair after crashes. Caution: InnoDB takes 2x-3x the disk space per table. Be sure to use innodb_file_per_table=1. Repair by sort. is usually much faster than repair by keycache; you probably got 'sort' because of this being big enough: myisam_sort_buffer_size = 526M -Original Message- From: nixofortune [mailto:nixofort...@gmail.com] Sent: Monday, June 24, 2013 12:35 PM To: mysql@lists.mysql.com Subject: Re: space gone after MyISAM REPAIR TABLE On 24/06/13 19:57, Reindl Harald wrote: Am 24.06.2013 18:47, schrieb Johan De Meersman: - Original Message - From: nixofortune nixofort...@gmail.com Hi guys, any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box. Oops... Can you run [show global variables like 'innodb_file_per_table';] ? I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation. While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p he spoke about MYISAM table the space on the hard drive gone down from 165 Gig to 70 Gig how can I get that space back? I can't check the table directory as I don't have root perm well, someone should look at the dadadir and error-log it is not uncommon that a repair to such large tables fails due too small myisam_sort_buffer_size and i suspect the operation failed and some temp file is laying around Thanks Reindl, It looks like Repair operation completed successfully. Overall it took 2Hours to complete with OK massage and some other message related to the index size. Repair process went through Repair by sort. myisam_sort_buffer_size = 526M. Provider runs MySQL on FreeBSD + ZFS file system. Could it be up to snapshots as well? I will ask them to look inside of datadir as we migrated this DB from Solaris just day before. This is a new DB for me and I never worked with MyISAM tables of that size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
