Set,

> >> I take it that no answer means that this is a strange problem,
> >> and within the last 24 hours or so it has turned even stranger...
> >>
> >> Doing a backup and restore make the fields from the dropped table
> >> disappear from RDB$RELATION_FIELDS and apparently the database
> >> works correctly for a while. CREATEing and DROPping a new table a
> >> couple of times, then makes IB_SQL complain about multiple rows
> >> in singleton select when attempting to browse the database,
> >> whereas FlameRobin doesn't complain at all. A bit further
> >> investigation reveals that whilst the fields are gone from
> >> RDB$RELATION_FIELDS, they are still present in
> >> RDB$RELATION_CONSTRAINTS (i.e. we have a PK constraint that
> >> doesn't belong to any table!). gfix doesn't report anything.
> >> After another restore of the backedup database, the records in
> >> question can be deleted from RDB$RELATION_CONSTRAINTS (by now,
> >> we're working on a test database) and after this deletion
> >> everything appears to work as desired.
> >>
> >> Now for the real question which I hope someone can answer within
> >> the next 36 hours (Thursday we intend to try it on our production
> >> database unless advised otherwise): Is it safe to simply delete
> >> stray records from RDB$RELATION_CONSTRAINTS???
> >>
> >> Hoping for someone to enlighten us,
> >> Set
> >
> > I know you said that no manual deletion from system tables was carried

out

> > but it sure as hell sounds like someone did do this.
> > I wouldn't have gone this far fiddling with system tables but since you

have

> > already, I would definitely now, do an extract of metadata, re-create a

new

> > database, and pump the data across.
> > That wold be the cleanest and safest way to ensure that all system

tables

> > are back the way they should be.
> > Alan
>
> Thanks for your answer, Alan!
>
> There's just no way that Aage (the only person that has worked with the
> table in question) would have fiddled with system tables this way on our
> main database unless we had encountered serious problems.

Doesn't Aage use IBExpert? I'm not saying IBE caused this, but we do
know it does more with the system tables than, for example, DBW.

> He simply
> created a new table with the same structure and content as an old table,
> alter some stored procedures to point to the new table, and dropped the
> old table - the thing that we're uncertain about is in which order these
> things were done.
>
> Of course I touched the system tables when I removed two records from
> RDB$RELATION_CONSTRAINTS, but that was only on a test database created
> from a restored backup.
>
> We will consider your advice about pumping the data across, though it
> does sound as a complex and/or time consuming task (several of the
> tables contain millions of records) that I don't know whether we will
> have the time to do before the system must be back up and running (the
> system works OK at present and records are added and modified all the
> time, but administrative tools like IB_SQL and DBW complain).
>
> I guess that no-one presently on this list has encountered records
> referencing a dropped table remaining in the system tables after
> dropping it?

Nope.


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