Alan McDonald wrote:

>> 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. 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?

Set