From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Newsgroups: comp.databases.ingres
Subject: Re: DEC ULTRIX INGRES checkpoint problem
Date: 13 Nov 92 22:09:29 GMT

In article <1992Nov13.175814.27160@aplcen.apl.jhu.edu>, mae@aplpy.jhuapl.edu
(Mary Anne Espenshade) writes:
> I need some help with an INGRES (specifically the DEC ULTRIX SQL port)
> error that appears to be corrupting a database configuration file.
> Long and detailed explanation follows.
> ...Last
> week I ran a update and made the checkpoint as usual, neither process
> reported any errors.  When I next attempted to access the database, the
> sql command aborted with the following error message:
>       E_US0010	Database does not exist.
>
> I checked accessdb (the one forms tool that comes with this version) and
> everything appeared normal.  I looked in the actual tables in iidbdb and
> the record for the database was there.

The records in iidbdb are nothing but pointers to where the database
_really_ lives, so their existence or absence is not entirely relevant
to the health of the database itself.  The accessdb utility is the
conventional way of updating the information in the iidbdb, but some of
it can also be done directly, and the tables in the iidbdb database are
also useful for displaying information about a database.

The iidatabase and iidbextend tables in iidbdb contain pointers to
INGRES locations, which in turn point to disk areas that you enter.
When you create locations in ULTRIX (with accessdb) I guess you have to
hard-wire the directory references (in VMS, we enter the areas as VMS
logicals, which give us an extra layer of indirection).

When connecting to a database, INGRES first looks in the iidbdb in the
iidatabase table, and then looks to the iilocations table to find the
area, and then looks in the area for the ingres/data/default/my_db
directory and the aaaaaaaa.cnf file.

The configuration file then tells INGRES the rest of the story about
additional data areas (actually, they're hardwired in the configuration
file), and what version of INGRES was used to create the database, and
whether journaling is turned on or off, and all that other
database-level stuff.

A very good way of dumping the information in the config file is to use
the "infodb" utility.  That's exactly what it does--dump the config file
in a human-readable format.  Everything I know about the internals of
the config file, I've learned from reading infodb output and comparing
it to dumps of the file.

> I looked in the data directory
> and the files were all there.  The only one that had been touched by the
> checkpoint was aaaaaaaa.cnf.

That's because the config file contains counters for the checkpoint and
journaling activity.  INGRES bumps up the counter every time you take a
checkpoint, and it also records the date of the checkpoint and whether
it was online or offline.

> Going to the error log explained more of
> the problem.  For each attempt to access the database, the following
> series of errors was logged:
>
> E_DM923C_CONFIG_FORMAT_ERROR	Error in configuration file format.
> ...

This error really tells it all.  The dbms server opened the
configuration file and tried to read what was inside.  There was an
inconsistency and it couldn't continue to read it.  It quit before the
connection was complete.  Of course, this doesn't explain _how_ the
config file become corrupted.

> I didn't want to try to recover from the checkpoint.  I didn't know
> whether it would be able to find the database or if the checkpoint
> itself was corrupted.  I finally decided to try replacing the
> aaaaaaaa.cnf file with a copy from the previous day's system backup.

Good move.  I think it's pretty safe to assume that only the config file
got corrupted.  This is a good way to fix problems like this, although
it's definitely a back door and Ingres won't support it.

Don't forget to make a copy of the existing config file before you put
the one from the checkpoint in there.  Also, get a _clean_ shutdown of
INGRES before you mess with _any_ files in the data directory, because
there may be data buffered in memory that doesn't get written until
later.  Only when both the dbms server and the RCP are fully shut down
is it safe to change files in the data directory.

> Since this is a binary file, I had no way to determine what had changed
> between the two.

Try using "od".  In VMS, use "dump".  You should see the database name,
the name of the dba, all of the database locations, and descriptions of
the critical internal system catalogs: iirelation, iiattribute, and
iiindex (maybe more).  There are also binary counters for the database
id (also stored in a column in the iidatabase table in iidbdb), the
checkpoint id, the journal id, and the reltid of the next table to be
created in the database.

> This seems to have worked, since I could then access
> the database and successfully ran the scripts that read the data.

Good.  You're in luck.

> Hoping that the error was a one-time fluke, I ran the next tape update
> and made another checkpoint, again with no errors reported.  The exact
> same thing happened.  Once again I recovered from a copy of the
> configuration file I had made just before I took the checkpoint (being
> paranoid about such things), but I am left with no way to checkpoint
> this database.
>
> Two alternatives have been suggested by the system maintainers - copy
> out all the data, recreate and reload the database and start over
> (assuming that the error had something to do with the number of times
> checkpoint had been run), or stop doing checkpoints and just tar the
> database data files to tape (since the checkpoint is only really
> important if journals are tied to it).
>
> Has anyone else run into this problem?  Is it safe to practically
> ignore it this way or should I be doing something else.
> --
> Mary Anne Espenshade
> ARPA: mae@aplpy.jhuapl.edu
> UUCP: ...!allegra!mimsy!aplcen!aplpy!mae

If I were you, I would use the unloaddb utility to copy out all the
data; destroy the old database; recreate it; and reload the data.  This
is the only safe way to be sure that the config file is healthy again. 
But if you have a humungous database, this may not be very palatable.

In the meantime, you can safely checkpoint the database with tar or any
backup utility, provided you get a clean shutdown of INGRES first. This
is necessary to be sure that what is stored on disk for the database is
the same as what the dbms server thinks the state of the database.  In a
fast commit scenario, the server may keep large amounts of the database
in memory without committing them to the data directory on disk for a
long time (even hours, depending on your system configuration).  If you
backup before shutting down INGRES, you're getting a stale picture of
the database.

Another possibility is another back door.  Create a new database and
then patch its config file until the database name, dba, locations,
database id, and checkpoint, journal, and reltid counters all match your
current database (good luck in doing this exactly right).  Copy this
healthy config file into your sick database, and maybe you'll be okay
again.  Don't forget to shutdown INGRES in the appropriate places.  But
this is a _teeny_ back door--you'll have to be very sure that you get it
right, or you could totally hose your database.  Do lots of dumps and
diffs.

Hope this helps.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.