search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 213 forks source link

Restored FB 1.5 db on FB 2.5 creates some CHECK_n triggers [CORE3118] #3496

Open firebird-automations opened 14 years ago

firebird-automations commented 14 years ago

Submitted by: Anderson Farias (afarias)

Assigned to: Claudio Valderrama C. (robocop)

Attachments: SIG_FB15.7z

Votes: 2

When restoring a FB 1.5 database on FB 2.5 some triggers named "CHECK_n" appears. Steps I've done:

1) backup 1.5 database (ods 10.1) with FB 1.5 engine/gbak (everything or metadata only) 2) restore backup with FB 2.5 engine/gbak using FIX_FSS_DATA and FIX_FSS_METADATA switches 3) connect to created DB and notice some tables (2 for instance) have empty/malforned triggers named CHECK_1 and CHECK_2

you can still backup and restore the new database. but, if you extract to SQL (isql -x) you can't create a new DB from it since there are 'empty triggers' as folow:

CREATE TRIGGER CHECK_2 FOR MY_TABLE ACTIVE AFTER DELETE POSITION 1 ^

firebird-automations commented 14 years ago

Commented by: @dyemanov

Does the same happen when you restore the same backup on v1.5? If not, can you attach a metadata-only backup for further investigation?

firebird-automations commented 14 years ago

Commented by: Anderson Farias (afarias)

Hi.

No, the same "problem" does *not* happen when restoring with FB 1.5. I'm attaching a metadata only backyp from FB 1.5 as requested so you may reproduce (I'm using FB 2.5 RC3 SuperClassic Win/32bit BTW). You may see TWO triggers created: CHECK_1 for table PROPOSTAS and CHECK_2 for table CLIFOR.

Anything else you need, let me know. Regards.

firebird-automations commented 14 years ago
Modified by: Anderson Farias (afarias) Attachment: SIG\_FB15\.7z \[ 11750 \]
firebird-automations commented 14 years ago

Commented by: @dyemanov

These triggers are automagically created by the engine to support the cascade referential integrity. And they do exist in the ODS10 database restored by Firebird 1.5 from the backup you provided. You can see other triggers similar to these two: CHECK_9 and CHECK_10. However, there's a major difference: the former two ones (which are problematic) have RDB$SYSTEM_FLAG = 0 while the other ones have RDB$SYSTEM_FLAG = 4. Only the value of 4 is correct, so ISQL skips them while creating the script. Those with the value 0 are considered being user-defined and thus included into the script. So yes, we have a problem. However, this is not a problem of v2.5. These triggers with the same values of the system flag exist in v1.5 as well, so the database became kinda "broken" somewhen in the past. In this situation, I see nothing to fix, as we have just a consequence instead of the actual issue.

firebird-automations commented 14 years ago

Commented by: Anderson Farias (afarias)

Hi Dmitry.

Yes, querying rdb$triggers on fb 1.5 I can notice the trigger with rdb$system_flag = 0. But what I don't understand is: WHY (and HOW) even that, with FB 1.5 the trigger is not listed on SHOW TABLE, it's not listed when extracting metadata (-x) and can not be listed using show trigger. How FB 1.5 even with rdb$system_flag = 0 knows how to handle these triggers right while fb 2.5 not (if this is really the problem)??

firebird-automations commented 14 years ago

Commented by: @dyemanov

You're right, the problem is exactly in ISQL. Priorly, it checked for both RDB$SYSTEM_FLAG <> 1 and the trigger being *not* referenced by RDB$CHECK_CONSTRAINTS. In v2.5, the former condition has been changed to RDB$SYSTEM_FLAG = 0 and the latter one has been removed. So system triggers are now determined using the system flag only. This is expected to be correct for any FB database. But now I doubt, whether it's also correct for legacy databases e.g. directly migrated from InterBase. They don't have the system flag values bigger than 1 used by constraints, so we may get exactly the situation you see.

firebird-automations commented 14 years ago
Modified by: @dyemanov Version: 2\.0\.6 \[ 10303 \] Version: 2\.5 RC2 \[ 10372 \] Version: 2\.5 RC1 \[ 10362 \] Version: 3\.0 Initial \[ 10301 \] Version: 2\.1\.3 \[ 10302 \] Version: 2\.5 Beta 2 \[ 10300 \] Version: 2\.5 Beta 1 \[ 10251 \] Version: 2\.1\.2 \[ 10270 \] Version: 2\.0\.5 \[ 10222 \] Version: 2\.1\.1 \[ 10223 \] Version: 2\.5 Alpha 1 \[ 10224 \] Version: 2\.0\.4 \[ 10211 \] Version: 2\.1\.0 \[ 10041 \] Version: 2\.0\.3 \[ 10200 \] Version: 2\.0\.2 \[ 10130 \] Version: 2\.0\.1 \[ 10090 \] Version: 2\.0\.0 \[ 10091 \]
firebird-automations commented 14 years ago

Commented by: @dyemanov

Claudio, it was a very old (pre-v2.0) change of yours, so please reply whether this side effect is intended or just overlooked and whether it should be treated as a bug or ignored. The problem is that some internal check triggers may have the system flag being zero instead of the values reserved by FB. And I'm not sure it's a miracle, perhaps it's a result of migration from InterBase.

firebird-automations commented 14 years ago
Modified by: @dyemanov assignee: Claudio Valderrama C\. \[ robocop \]
firebird-automations commented 14 years ago

Commented by: Anderson Farias (afarias)

Thanks for the info Dmitry. BTW... to "correct" my database I've droped those triggers... Now I'm starting to think that was a *dumb* idea.. Should I leave them alone since they are system triggers and may be of some use to the db/engine? Regards

firebird-automations commented 14 years ago
Modified by: Anderson Farias (afarias) priority: Major \[ 3 \] =\> Minor \[ 4 \]
firebird-automations commented 14 years ago

Commented by: @dyemanov

Instead, you should have been dropping and recreating two cascaded foreign keys that are implemented by those system triggers.