| Subject | RE: Private: Help... Can't delete procedure... can't restore DB! |
|---|---|
| Author | Colin Fraser |
| Post date | 2001-03-23T02:37:33Z |
Hi Helen (and others), please find the answers inserted below...
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, 22 March 2001 6:13 pm
To: Colin Fraser
Subject: Private: Help... Can't delete procedure... can't restore DB!
At 05:30 PM 22-03-01 +1200, you wrote:
Q: What version of InterBase Server are you using?
A: We backed up in IB 5.6 and tried to restore to IB 6 and IB 5.6.
Q: Are you using the gbak and other command-line tools that belong to that
version?
A: We used the IB Server manager (5.6) to backup and restore to IB 5.6 and
IB Console to restore to IB 6.
Q: Have you shut down the server at all between the time you made the faulty
gbak and the time you tried to restore it?
A: Yes (several times?)
Q: What are you using for a connection string?
A: Server_2:D:/LabSys2/Lab_Sys_2_Clean.gdb
Q: Are any other users accessing the database?
A: Yes
Q: If Yes, are they all using the same connection string as you are using?
("Same" = "all characters present and identical").
A: Case of string maybe different.
A: Yes
Q: If Yes, can you see the generator in the Browse display?
A: No
Q: Has anyone been trying to do "maintenance" by accessing the system tables
directly, e.g. DELETE FROM RDB$GENERATORS WHERE
RDB$GENERATOR_NAME='GEN_RP_REPORT_EVENT';
A: The generator was deleted using the Borland Database Explorer, not to
sure whether it issues this statement. Then, after re adding and deleting
the generator, I used IB Expert which I think does issue such a statement...
however, the original user who deleted the generator did it with Database
Explorer.
(The deadlock message seems to suggest that there is some such DML
transaction on a system table hanging around in limbo...)
unresolved transaction, GBAK will bypass it in some circumstances.
Q: Would you be more explicit about what things are missing.
Q: If you can't restore the database, how do you know things are missing?
A: The restore starts but does not finish properly (comes up with the BLR at
offset 49 error mentioned previously). You can then go into the restored
database... Here are the differences between the databases:
First a count of the items...
Original Restored
Domains 650 660
Tables 82 82
Procedures 34 0
Functions 75 75
Generators 34 34
Exceptions 2 2
(not sure why there are more domains in the restored db...)
In the tables, all the data was copied over, plus all the primary keys, ref.
constraints and indices.
No Triggers were copied.
Something funny happened with the check constraints... the constraint text
was left out, for eg:
The constraint:
/* CheckConstraint CK_QC_QOWQ_CAPA_REQUIREMENT */
ALTER TABLE QC_QOWQ
ADD CONSTRAINT CK_QC_QOWQ_CAPA_REQUIREMENT
CHECK (CAPA_REQUIREMENT between 0 and 2)
became:
/* CheckConstraint CK_QC_QOWQ_CAPA_REQUIREMENT */
ALTER TABLE QC_QOWQ
ADD CONSTRAINT CK_QC_QOWQ_CAPA_REQUIREMENT
CHECK ()
Q: Also, is there any possibility that the database has been hitherto
running on a copy that was restored from a file backup of the gdb file (e.g.
Copy/Paste, WinZip, MSBackup or some other archiving utility) rather than
from a gbak file?
A: Ahrmmm... it is possible... I could not rule out 100% that at sometime a
file copy was not made of the database.
A: The generator created fine. But updating or trying to drop the stored
procedure still failed with the same error message (the one with the
deadlock).
Just for starters...
Cheers,
Helen
---
Thanks for that Helen, I have subscribed to the list server you mentioned...
Regards
Colin
######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, 22 March 2001 6:13 pm
To: Colin Fraser
Subject: Private: Help... Can't delete procedure... can't restore DB!
At 05:30 PM 22-03-01 +1200, you wrote:
>We have a stored procedure which used a generator... the Generator wasSome questions first...try to answer Yes or No where appropriate.
>apparently deleted even when the stored procedure used it. Deleting or
>editing the stored procedure now produces the following error:
>-----------
>This operation is not defined for system tables.
>unsuccessful metadata update.
>ERASE RDB$FIELDS failed.
>deadlock.
>------------
Q: What version of InterBase Server are you using?
A: We backed up in IB 5.6 and tried to restore to IB 6 and IB 5.6.
Q: Are you using the gbak and other command-line tools that belong to that
version?
A: We used the IB Server manager (5.6) to backup and restore to IB 5.6 and
IB Console to restore to IB 6.
Q: Have you shut down the server at all between the time you made the faulty
gbak and the time you tried to restore it?
A: Yes (several times?)
Q: What are you using for a connection string?
A: Server_2:D:/LabSys2/Lab_Sys_2_Clean.gdb
Q: Are any other users accessing the database?
A: Yes
Q: If Yes, are they all using the same connection string as you are using?
("Same" = "all characters present and identical").
A: Case of string maybe different.
>Validating the database produces no errors. Backing up the database worksQ:Can you still read the original copy of the database using IB_WISQL?
>fine. Restoring the database however fails with the following error:
>-------------
>gbak: ERROR: invalid request BLR at offset 49
>gbak: ERROR: generator GEN_RP_REPORT_EVENT is not defined
>gbak: Exiting before completion due to errors
>
>Restore exited unsuccessfully on Thu Mar 22 16:34:46 2001
>-------------
A: Yes
Q: If Yes, can you see the generator in the Browse display?
A: No
Q: Has anyone been trying to do "maintenance" by accessing the system tables
directly, e.g. DELETE FROM RDB$GENERATORS WHERE
RDB$GENERATOR_NAME='GEN_RP_REPORT_EVENT';
A: The generator was deleted using the Borland Database Explorer, not to
sure whether it issues this statement. Then, after re adding and deleting
the generator, I used IB Expert which I think does issue such a statement...
however, the original user who deleted the generator did it with Database
Explorer.
(The deadlock message seems to suggest that there is some such DML
transaction on a system table hanging around in limbo...)
>This is a bit scary to me... If I backup a database without errors, I wouldIf the corruption already exists before the backup and is involved in an
>assume that I could restore it without errors... The data does seem to be
>restored but lots of things (like procedures) are missing.
unresolved transaction, GBAK will bypass it in some circumstances.
Q: Would you be more explicit about what things are missing.
Q: If you can't restore the database, how do you know things are missing?
A: The restore starts but does not finish properly (comes up with the BLR at
offset 49 error mentioned previously). You can then go into the restored
database... Here are the differences between the databases:
First a count of the items...
Original Restored
Domains 650 660
Tables 82 82
Procedures 34 0
Functions 75 75
Generators 34 34
Exceptions 2 2
(not sure why there are more domains in the restored db...)
In the tables, all the data was copied over, plus all the primary keys, ref.
constraints and indices.
No Triggers were copied.
Something funny happened with the check constraints... the constraint text
was left out, for eg:
The constraint:
/* CheckConstraint CK_QC_QOWQ_CAPA_REQUIREMENT */
ALTER TABLE QC_QOWQ
ADD CONSTRAINT CK_QC_QOWQ_CAPA_REQUIREMENT
CHECK (CAPA_REQUIREMENT between 0 and 2)
became:
/* CheckConstraint CK_QC_QOWQ_CAPA_REQUIREMENT */
ALTER TABLE QC_QOWQ
ADD CONSTRAINT CK_QC_QOWQ_CAPA_REQUIREMENT
CHECK ()
Q: Also, is there any possibility that the database has been hitherto
running on a copy that was restored from a file backup of the gdb file (e.g.
Copy/Paste, WinZip, MSBackup or some other archiving utility) rather than
from a gbak file?
A: Ahrmmm... it is possible... I could not rule out 100% that at sometime a
file copy was not made of the database.
>(p.s. have tried re-adding a generator with the same name... but thatdidn't
>work)Q: In what way did it not work?
A: The generator created fine. But updating or trying to drop the stored
procedure still failed with the same error message (the one with the
deadlock).
Just for starters...
Cheers,
Helen
---
Thanks for that Helen, I have subscribed to the list server you mentioned...
Regards
Colin
######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################