What could cause Firebird to silently turn calculated fields into "normal" fields?

[Adrien Reboisson]

Hi,

I'm using Firebird 2.5.8 to store data for a software I designed.

A customer contacted me today to inform me of multiple errors that I couldn't understand, so I used IBExpert to inspect its database.

To my surprise, all the calculated fields of the database had been transformed into "standard" fields. This is clearly visible in IBExpert "DDL" tab which displays tables definition as SQL code.

For instance, the following table definition:

CREATE TABLE TVERSIONS (
   ...
    PARENTPATH COMPUTED BY (((SELECT TFILES.FILEPATH FROM TFILES WHERE ID = TVERSIONS.FILEID))),
    ....
    ISCOMPLETE COMPUTED BY ((((SELECT TBACKUPVERSIONS.ISCOMPLETE FROM TBACKUPVERSIONS WHERE ID = TVERSIONS.CVERSION)))),
    CDATE COMPUTED BY (((SELECT TBACKUPVERSIONS.SERVERSTARTDATE FROM TBACKUPVERSIONS WHERE ID = TVERSIONS.CVERSION))),
    DDATE COMPUTED BY (((SELECT TBACKUPVERSIONS.SERVERSTARTDATE FROM TBACKUPVERSIONS WHERE ID = TVERSIONS.DVERSION))),
   ...
);

has been "changed" in the client database into this:

CREATE TABLE TVERSIONS (
   ...
    PARENTPATH VARCHAR(512) CHARACTER SET UTF8 COLLATE UNICODE,
    ...
    ISCOMPLETE SMALLINT,
    CDATE TIMESTAMP,
    DDATE TIMESTAMP,
    ...
);

How can such a thing be possible?

I've been using Firebird for more than 10 years, and I've never seen such a behavior until now. Is it possible that it could be a corruption of RDB$FIELDS.RDB$COMPUTED_SOURCE fields?

What would you advise? 

Best regards,

Adrien

[Martijn Tonies (Upscene Productions)]

Hello Adrien,

 

Have you checked the COMPUTED_SOURCE and COMPUTED_BLR columns for its contents?

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/acbd5b21-970e-4dad-ac51-92346004e3d7n%40googlegroups.com.

[Adrien Reboisson]

Hi Martijn,

The following query :

select r.rdb$field_name, f.RDB$COMPUTED_BLR, f.RDB$COMPUTED_SOURCE
from rdb$relation_fields r, RDB$FIELDS f
where r.rdb$relation_name = 'TVERSIONS'
  and f.RDB$FIELD_NAME = r.RDB$FIELD_SOURCE
  and f.RDB$COMPUTED_BLR is not NULL

returns no result when ran against the "corrupted" database. 

If I run it against any other "valid" database, the 4 expected fields are returned with the computed source definition and a non null COMPUTED_BLR field.

This seems to confirm all computed fields metadata have been lost inside the "corrupted" database. I'm still wondering how such a thing would be possible...

Best regards,

Adrien

You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/zG1R6z0Ec9A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4269213BDBD84C07AC2EF897BCACB318%40MARTIJNWS.

--

Adrien Reboisson
06 70 40 04 10

[Martijn Tonies (Upscene Productions)]

Hi,

 

How was the database created, using a restore of empty template or by script?

 

Any idea since when it is faulty?

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAMonEVGbQftmXwb6aB02kcA984n_fQHr-%3DKG3Q09dVjgg--uhA%40mail.gmail.com.

[Mark Rotteveel]

On 25-10-2022 12:41, Adrien Reboisson wrote:
> This seems to confirm all computed fields metadata have been lost inside
> the "corrupted" database. I'm still wondering how such a thing would be
> possible...

Does the database actually contain any data? As was pointed out in a
comment on Stack Overflow by user13964273, gbak initially recreates the
table with the computed fields as "normal" columns, and then alters them
to computed columns. This would suggest that a gbak restore was
performed and it did not complete successfully.

I'm hazy on the details, like whether the data is initially restored
including the data of the computed columns, before they are altered to
computed columns, or not. But all this would suggest that the database
is from a failed restore.

Mark
--
Mark Rotteveel

[Dmitry Yemanov]

25.10.2022 13:41, Adrien Reboisson wrote:
>
> select r.rdb$field_name, f.RDB$COMPUTED_BLR, f.RDB$COMPUTED_SOURCE
> from rdb$relation_fields r, RDB$FIELDS f
> where r.rdb$relation_name = 'TVERSIONS'
>   and f.RDB$FIELD_NAME = r.RDB$FIELD_SOURCE
>   and f.RDB$COMPUTED_BLR is not NULL
>
> returns no result when ran against the "corrupted" database.
>
> If I run it against any other "valid" database, the 4 expected fields
> are returned with the computed source definition and a non null
> COMPUTED_BLR field.
>
> This seems to confirm all computed fields metadata have been lost inside
> the "corrupted" database. I'm still wondering how such a thing would be
> possible...

Database was recently restored with an error and nobody noticed that?

Computed fields are initially restored as regular fields and only after
data and other metadata is restored, they're changed to be computed. If
this last step failed, error is printed and database is left in the
single-user shutdown mode.


Dmitry

[Adrien Reboisson]

Thank you all for your answers.

I had a long talk with this customer, who admitted that he had run a backup/restore operation some time ago that "crashed" (probably during the restore phase) - and that he had simply ignored the error without seeking to understand the problem.

Your guess was right: an error certainly occurred just before restoring the calculated field. What's more puzzling is that I don't notice any other corruption in the file - the database doesn't seem to have been "corrupted" beyond the absence of those calculated fields.

I restored the definitions of those calculated fields, and everything seems to be working again. I did a backup/restore cycle using gbak without any errors. Do you consider the problem "solved", or would it be safer to ask this client to start over with a fresh database?

Thank you very much !

Adrien

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/zG1R6z0Ec9A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/b454828f-0d1b-1605-2fb3-624504cc336e%40lawinegevaar.nl.

[Mark Rotteveel]

On 27-10-2022 16:15, Adrien Reboisson wrote:
> I restored the definitions of those calculated fields, and everything
> seems to be working again. I did a backup/restore cycle using gbak
> without any errors. Do you consider the problem "solved", or would it be
> safer to ask this client to start over with a fresh database?

That is hard to answer definitively. I would recommend to perform a
structural comparison of the database (e.g. by exporting the DDL of the
database using ISQL or another tool and comparing it with the DDL
generated from a known good database), especially looking for missing
computed columns, constraints and indexes.

I think that your customer wouldn't want to start from scratch, so you
could consider creating a new database and pumping the data over from
the old to the new, but if the DDL compares fine, that is probably
unnecessary work.

Mark
--
Mark Rotteveel