Re: Ouch! ibdata files deleted. Why no catastrophe?
Hi ! Jerry Schwartz wrote: I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. Yes - that is standard Unix semantics; it should be that way on any variety of Unix including Linux, BSD, and the commercial ones. You can use this for any file system object (including FIFOs) to get an automatic cleanup if your process(es) crash(es): - Create the object (file, FIFO, ...), - open it (from all processes that need it), - delete it. It will continue to exist as long as at least one process has it open, but cannot be accessed by its name any longer (so no additional open() call is possible). It will still occupy space on disk, but will not be included in any du output (because it is not accessible from any directory). If du on the root directory of some file system (done by the root user) reports less blocks than df on that file system, typically the difference are the blocks of such deleted but still open files. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Right on both items. Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
On Wed, 2007-09-26 at 10:55 -0400, Jerry Schwartz wrote: I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. It sounds like Linux is the same. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Done :) Thanks to all those who responded. I did a full backup last night and shut down mysql. At this point the innodb files were then completely deleted ( disappeared from the output of 'lsof' ) and the filesystem saw the extra space. I removed my /var/lib/mysql, reconfigured things, and imported, and everything went fine. That's good to hear :-) I am, btw, surprised that MySQL doesn't place a lock on the files. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. egards anandkl On 9/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
I cannot think of any safe alternative. Trying to recover files that are in memory strikes me as very unwise with InnoDB. Ananda Kumar wrote: Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. egards anandkl On 9/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. Well, not deleting the InnoDB files would be a good start. The files were deleted, what do you expect?? Just continue running and hoping no-one finds out? Re-creating them sure sounds the safest option to me! Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ouch! ibdata files deleted. Why no catastrophe?
I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 26, 2007 10:07 AM To: Ananda Kumar; Baron Schwartz Cc: Gary Josack; Daniel Kasak; mysql@lists.mysql.com Subject: Re: Ouch! ibdata files deleted. Why no catastrophe? Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. Well, not deleting the InnoDB files would be a good start. The files were deleted, what do you expect?? Just continue running and hoping no-one finds out? Re-creating them sure sounds the safest option to me! Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ouch! ibdata files deleted. Why no catastrophe?
On Wed, 2007-09-26 at 10:55 -0400, Jerry Schwartz wrote: I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. It sounds like Linux is the same. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Done :) Thanks to all those who responded. I did a full backup last night and shut down mysql. At this point the innodb files were then completely deleted ( disappeared from the output of 'lsof' ) and the filesystem saw the extra space. I removed my /var/lib/mysql, reconfigured things, and imported, and everything went fine. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Daniel Kasak wrote: Greetings. I've just returned from holidays, and it seems that all but 1 ibdata file ( there were 10! ) have been deleted by a co-worker. He apparently was able to delete them with nautilus ( he was looking to reclaim some space and these were 1GB files each ... and yes, the Trash was emptied as well ). I would have assumed these would be protected from being deleted while mysql was up, but this clearly wasn't the case ( or was it? perhaps they are still around? ). The astonishing thing is that mysql doesn't seem to care about this. It happened 1 week ago, and no-one has complained about any database-related problems since. I've got a nightly backup script which does a 'mysqldump' on each database, and then restarts the server ( which gives me daily transaction logs ). Tonight, I obviously plan on doing a complete reinstall from an old backup, and running the transaction logs. But, just out of curiosity ... what the hell is going on? Why is mysql not complaining bitterly, crashing, and worse? Note the lack of ibdata1 ... ibdata9 screamer mysql # ls -l total 2885424 drwx-- 2 mysql mysql 480 Nov 26 2006 Assets drwx-- 2 mysql mysql 168 Sep 21 14:57 EPricing drwx-- 2 mysql mysql 12512 Sep 25 21:04 NUS drwx-- 2 mysql mysql 656 Nov 26 2006 Timekeeper drwx-- 2 mysql mysql 1216 Jul 4 08:46 dbmail drwx-- 2 mysql mysql 648 Aug 13 14:18 dspam drwx-- 2 mysql mysql 16584 Sep 18 09:32 ebills drwx-- 2 mysql mysql 824 Sep 24 14:45 energy -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile1 lrwxrwxrwx 1 root root 15112077312 Sep 26 09:08 ibdata10 drwx-- 2 mysql mysql 1848 Nov 26 2006 mysql drwx-- 2 mysql mysql 648 Dec 20 2006 roundcubemail drwx-- 3 mysql mysql 6240 Sep 26 08:54 sales -rw-rw 1 mysql mysql 239845598 Sep 14 20:40 screamer-bin.000201 -rw-rw 1 mysql mysql 591774120 Sep 17 20:44 screamer-bin.000202 -rw-rw 1 mysql mysql 289226895 Sep 18 20:38 screamer-bin.000203 -rw-rw 1 mysql mysql 281415790 Sep 19 20:36 screamer-bin.000204 -rw-rw 1 mysql mysql 286245722 Sep 20 20:37 screamer-bin.000205 -rw-rw 1 mysql mysql 331061564 Sep 21 20:43 screamer-bin.000206 -rw-rw 1 mysql mysql 411650198 Sep 24 20:38 screamer-bin.000207 -rw-rw 1 mysql mysql 309576301 Sep 25 20:37 screamer-bin.000208 -rw-rw 1 mysql mysql 140007700 Sep 26 09:08 screamer-bin.000209 -rw-rw 1 mysql mysql 3520 Sep 25 20:37 screamer-bin.index -rw-rw 1 mysql mysql 60432327 Sep 26 09:06 screamer-slow.log drwx-- 2 mysql mysql80 Sep 20 11:16 test screamer mysql # The only way I can explain the fact that things are still working is that these files are in fact *not* deleted, and are still in use by mysql, but not visible to anything else. But that's ridiculous as well. So what's going on? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Daniel Kasak wrote: On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
On Tue, 2007-09-25 at 23:11 -0400, Gary Josack wrote: Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Yes I've been reading / thinking more about this, and I've decided against it. There's no real guarantee that MySQL will have written everything to the ibdata files at the point when I make a snapshot of them ( at least as far as I know ). Since the nightly backups are still working perfectly, I'll just shut down MySQL when the backup completes, delete /var/lib/mysql, and import the backup. That sounds a lot safer than anything else at the moment. Thanks again for your response. At least I learned about lsof and recovering deleted files :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
For future reference. The files do actually continue to be written to. I experience this all the time when people delete logs files and space keeps filling up. Daniel Kasak wrote: On Tue, 2007-09-25 at 23:11 -0400, Gary Josack wrote: Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Yes I've been reading / thinking more about this, and I've decided against it. There's no real guarantee that MySQL will have written everything to the ibdata files at the point when I make a snapshot of them ( at least as far as I know ). Since the nightly backups are still working perfectly, I'll just shut down MySQL when the backup completes, delete /var/lib/mysql, and import the backup. That sounds a lot safer than anything else at the moment. Thanks again for your response. At least I learned about lsof and recovering deleted files :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
