Thread: Regarding Vacuumdb
hi, I am getting the following error while doing vacuumdb, ERROR: mdopen: couldn't open test1: No such file or directory vacuumdb: database vacuum failed on db1. Here 'db1' is the database and 'test1' is a table. When, displaying the structure of the table 'test1', it comes correctly. But I can't drop the table. What could be wrong? Any help. Thanks shan.
On Tue, 28 Aug 2001, Bhuvaneswari wrote: > hi, > I am getting the following error while doing vacuumdb, > > ERROR: mdopen: couldn't open test1: No such file or directory > vacuumdb: database vacuum failed on db1. We got this error a lot in 6.5. Usually it means your table has somehow been corrupted, and postgres doesn't want anything to do with it. It'll show up, and you can even select from it, but doing so will crash the back-end, and you can't run a vacuum or pg_dump on that database successfully. You'll have to do a table-by-table pg_dump, destroy the DB, and reimport everything. You'll have to rebuild the corrupted table from scratch, since you might not be able to dump it. Either way, it's a lot of work. Just be careful. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
Before doing anything so drastic, you may want to check the archives for recent messages on a similar error. IIRC, this problem can be caused by rolling back a "drop table" command. The effect is that the table structure in the database remains, but the physical file is gone. The fix is to create a file with the correct name in the data directory for that database, then drop the table. If you are running version 7.1, the trick will be to figure out the correct name for that file. You can find discussion on that in the archives too. ----- Original Message ----- From: "Shaun Thomas" <sthomas@townnews.com> To: <shan@ceedees.com> Cc: <pgsql-jdbc@postgresql.org>; <pgsql-general@postgresql.org> Sent: Friday, August 31, 2001 11:48 AM Subject: Re: [GENERAL] Regarding Vacuumdb > On Tue, 28 Aug 2001, Bhuvaneswari wrote: > > > hi, > > I am getting the following error while doing vacuumdb, > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > vacuumdb: database vacuum failed on db1. > > We got this error a lot in 6.5. Usually it means your table has somehow > been corrupted, and postgres doesn't want anything to do with it. It'll > show up, and you can even select from it, but doing so will crash the > back-end, and you can't run a vacuum or pg_dump on that database > successfully. > > You'll have to do a table-by-table pg_dump, destroy the DB, and reimport > everything. You'll have to rebuild the corrupted table from scratch, > since you might not be able to dump it. > > Either way, it's a lot of work. Just be careful. > > -- > +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ > | Shaun M. Thomas INN Database Programmer | > | Phone: (309) 743-0812 Fax : (309) 743-0830 | > | Email: sthomas@townnews.com AIM : trifthen | > | Web : hamster.lee.net | > | | > | "Most of our lives are about proving something, either to | > | ourselves or to someone else." | > | -- Anonymous | > +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Shaun Thomas wrote: > > On Tue, 28 Aug 2001, Bhuvaneswari wrote: > > > hi, > > I am getting the following error while doing vacuumdb, > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > vacuumdb: database vacuum failed on db1. > > We got this error a lot in 6.5. Usually it means your table has somehow > been corrupted, and postgres doesn't want anything to do with it. It'll > show up, and you can even select from it, but doing so will crash the > back-end, and you can't run a vacuum or pg_dump on that database > successfully. Yes. This error was caused in older versions by doing something like: BEGIN; DROP TABLE test1; ABORT; The 6.5 version of PostgreSQL would remove the underlying file from the file system when DROP TABLE was executed, not when the transaction performed a commit, so the file was gone, but the entries in the system cataloge remained. This is fixed in newer versions. Hope that helps, Mike Mascari mascarm@mascari.com
On Fri, 31 Aug 2001, Shaun Thomas wrote: > On Tue, 28 Aug 2001, Bhuvaneswari wrote: > > > hi, > > I am getting the following error while doing vacuumdb, > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > vacuumdb: database vacuum failed on db1. > > We got this error a lot in 6.5. Usually it means your table has somehow > been corrupted, and postgres doesn't want anything to do with it. It'll > show up, and you can even select from it, but doing so will crash the > back-end, and you can't run a vacuum or pg_dump on that database > successfully. > > You'll have to do a table-by-table pg_dump, destroy the DB, and reimport > everything. You'll have to rebuild the corrupted table from scratch, > since you might not be able to dump it. > > Either way, it's a lot of work. Just be careful. Actually, this is probably a rolled-back drop, which means creating the file such that the db can read/write to it and doing the drop (and not rolling it back) will probably suffice, as per the responses to the first time the message went through.