Firebird 3 - internal Firebird consistency check (decompression overran buffer (179), file: sqz.cpp line: 293)

[Luciano Rodrigues Nunes Mendes]

Hi Everyone,

I am trying to make the following query, but Firebird 3.0.7 is returning the following error:

Statement failed, SQLSTATE = 42000

Execute statement error at isc_dsql_fetch :

335544333 : internal Firebird consistency check (decompression overran buffer (179), file: sqz.cpp line: 293)

Statement : SELECT CAST(ID_PHONE AS BIGINT) AS PROTOTYPE_ID,TRIM(UPPER(CAST(RESPONSIBLE AS VARCHAR(32)))) AS USER_USER_NAME,DT AS DATE_ASSIGNED FROM PHONESHISTORY ORDER BY ID

Data source : Firebird::LOCALHOST:ARAS_CIN

-At procedure 'SYNCHRONIZE_DATABASE' line: 173, col: 3

Statement failed, SQLSTATE = 42000

Execute statement error at transaction commit :

335544333 : internal Firebird consistency check (can't continue after bugcheck)

Data source : Firebird::LOCALHOST:ARAS_CIN

Statement failed, SQLSTATE = 42000

Execute statement error at transaction commit :

335544333 : internal Firebird consistency check (can't continue after bugcheck)

Data source : Firebird::LOCALHOST:ARAS_CIN

When I try to backup the database a similar error happens and the backup is not completed:

gbak:    0.212  0.000      7      0     writing data for table PHONESHISTORY

gbak:    0.230  0.018    187      0 10000 records written

gbak:    0.247  0.016    193      0 20000 records written

gbak:    0.264  0.016    199      0 30000 records written

gbak:    0.280  0.016    193      0 40000 records written

gbak:    0.296  0.016    190      0 50000 records written

gbak:    0.312  0.015    193      0 60000 records written

gbak:    0.330  0.018    192      0 70000 records written

gbak:    0.347  0.017    192      0 80000 records written

gbak:    0.364  0.016    193      0 90000 records written

gbak:    0.380  0.016    192      0 100000 records written

gbak: ERROR:internal Firebird consistency check (decompression overran buffer (179), file: sqz.cpp line: 293)

gbak: ERROR:gds_$receive failed

gbak:Exiting before completion due to errors

gbak: ERROR:internal Firebird consistency check (can't continue after bugcheck)

Has my database corrupted or is it a defect in Firebird 3.0.7?

How do I fix my database?

Best Regards,

Luciano

[Dimitry Sibiryakov]

22.02.2021 11:59, Luciano Rodrigues Nunes Mendes wrote:
> Has my database corrupted or is it a defect in Firebird 3.0.7?

Your database is corrupted. Is it result of a hardware failure or a defect in Firebird
is unknown.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

Thanks for your prompt reply!

What can I do to try to fix the database?

Any relevant information you would need to rule out the possibility of there being an unknown Firebird defect since this corruption happened without any power failure (the server has nobreak) and during low system usage (weekend)?

Best Regards,

Luciano

--
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/3e47bba8-e061-e6c3-4342-6d9925f32788%40ibphoenix.com.

[Dimitry Sibiryakov]

22.02.2021 12:13, Luciano Rodrigues Nunes Mendes wrote:
> What can I do to try to fix the database?

http://www.ibphoenix.com/services/repair

or DIY

https://translate.google.com/translate?hl=&sl=ru&tl=en&u=http%3A%2F%2Fwww.ibase.ru%2Fdb_repair%2F%23gfix

Affected records will be lost.

> Any relevant information you would need to rule out the possibility of there being an unknown Firebird defect since this corruption happened without any power failure (the server has nobreak) and during low system usage (weekend)?

List of operations that were performed during suspicious time. Especially DDL one that
failed. Such kind of corruption can happen if DDL and DML is mixed in one transaction
especially if it has been rolled back.

--
WBR, SD.

[Dimitry Sibiryakov]

22.02.2021 12:13, Luciano Rodrigues Nunes Mendes wrote:
> Any relevant information you would need to rule out the possibility of there being an
> unknown Firebird defect since this corruption happened without any power failure (the
> server has nobreak) and during low system usage (weekend)?

You also can check if it could be the case of
http://tracker.firebirdsql.org/browse/CORE-6379

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

Thank you very much for all the links to fix the database!

Here is the scenario that can help to identify the existence of a issue in Firebird 3.0.7:

Strange things started happening since January 13, 2021 when I enabled Win_Sspi authentication and started backing up this database using domain authentication:

==========

Before January 13, 2021

databases.conf

ARAS_CIN = D:\APPLICATIONS\LURONUMEN\DB\ARAS_CIN.FDB

{

AuthServer = Legacy_Auth

UserManager = Legacy_UserManager

}

Windows Task Scheduler 

"%Firebird_Bin_Folder%\gbak.exe" -user %User% -password %Password% -backup_database "%Database%" "%CD%\%YYMMDD_HHMM%_%Database%.FBK" -verify -y "%CD%\%YYMMDD_HHMM%_%Database%.LOG" -statistics tdrw

==========

After January 13, 2021

databases.conf

ARAS_CIN = D:\APPLICATIONS\LURONUMEN\DB\ARAS_CIN.FDB

{

AuthServer = Legacy_Auth, Win_Sspi

UserManager = Legacy_UserManager

}

Windows Task Scheduler 

"%Firebird_Bin_Folder%\gbak.exe" -role RDB$ADMIN -backup_database "%Database%" "%CD%\%YYMMDD_HHMM%_%Database%.FBK" -verify -y "%CD%\%YYMMDD_HHMM%_%Database%.LOG" -statistics tdrw

==========

The vast majority of times scheduled backups were completed successfully, but sometimes the following error occurred:

gbak: ERROR:Unable to perform operation.  You must be either SYSDBA or owner of the database

gbak:Exiting before completion due to erros

Until yesterday, the scheduled backup did not work twice and corruption was detected.

I have 3 other databases on the same server but these using Srp and Win_Sspi authentication instead of Legacy_Auth and Win_Sspi and I never had any problems with their scheduled backups using domain authentication to backup it:

CHRONOS_CIN = D:\APPLICATIONS\LURONUMEN\DB\CHRONOS_CIN.FDB 

{ 

AuthServer = Srp, Win_Sspi 

SecurityDatabase = CHRONOS_CIN 

UserManager = Srp 

}

I suspect that perhaps it is the combination of Legacy_Auth and Win_Sspi authentications that may be causing this issue since this database has never been corrupted in its 15 years of existence.

Best Regards,

Luciano

--
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/4898d225-15ab-2870-ca3d-021213f58a64%40ibphoenix.com.

[Dimitry Sibiryakov]

22.02.2021 12:57, Luciano Rodrigues Nunes Mendes wrote:
> I suspect that perhaps it is the combination of Legacy_Auth and Win_Sspi authentications
> that may be causing this issue

No, they cannot. Search http://tracker.firebirdsql.org/ for word "overran" to see
reasons of this bugcheck that were already identified and fixed.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

This issue happened using the Firebird 3.0.7. Is there any plan to back port the http://tracker.firebirdsql.org/browse/CORE-6379 to Firebird 3.0 branch?

Best Regards,

Luciano

--
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/9d5dfddb-692f-b401-331a-53c4715c8c2d%40ibphoenix.com.

[Dimitry Sibiryakov]

22.02.2021 13:29, Luciano Rodrigues Nunes Mendes wrote:
> This issue happened using the Firebird 3.0.7. Is there any plan to back port the
> http://tracker.firebirdsql.org/browse/CORE-6379 to Firebird 3.0 branch?

For it you should show that it was really your case in firebird-devel mail list or add
the proofs into the ticket as a comment. CORE-6379 has very specific conditions.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

I see.

Anyway, I disabled authentication via Win_Sspi and went back to making scheduled backups using a username/password. I'll see if the issue happens again.

Thank you very much for your support Dimitry!

Best Regards,

Luciano

--
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/4eee5140-197c-8eb1-75cc-0301865a0189%40ibphoenix.com.

[Karol Bieniaszewski]

>> I suspect that perhaps it is the combination of Legacy_Auth and Win_Sspi authentications that may be causing this issue since this database has never been corrupted in its 15 years of existence.

 

Did you check db regullary for errors? E.g. if it is not 24/7 i ofline hours? And do you run test backup and required step test restore?

As erros can cumulate and in some condition can create some more problematic errors.

 

regards,

Karol Bieniaszewski

[Luciano Rodrigues Nunes Mendes]

Hi Karol,

Full backups using gbak are performed automatically using Windows Task Scheduler every 5 hours and the last backup performed 5 hours before was perfect because I was able to restore the database from it.

My luck was that it happened on a weekend where probably nobody was using the system (so far nobody has complained about loss of information).

Best Regards,

Luciano

--

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/4DkjpS4LTnzlgp1b%40smtp.poczta.onet.pl.

[Dimitry Sibiryakov]

22.02.2021 16:29, Luciano Rodrigues Nunes Mendes wrote:
> Full backups using gbak are performed automatically using Windows Task Scheduler every 5
> hours and the last backup performed 5 hours before was perfect because I was able to
> restore the database from it.
> My luck was that it happened on a weekend where probably nobody was using the system (so
> far nobody has complained about loss of information).

In this case could you list scheduled task that were performed in these 5 hours, please.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Sure Dmitry!

I have attached the Windows Task Scheduler instructions (BKP_ARAS_CIN.xml) and the batch file (.bat) used for it to backup the database (BKP_ARAS_CIN.txt).

BTW, does completing a backup successfully ("closing file, committing, and finishing" at the end of the gbak verbose) mean that I will always be able to restore the database from it or do I need to test this completed backup successfully to be absolutely sure that I will be able to restore the database from it?

Best Regards,

Luciano

--
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/b4bfc3d1-9501-af69-ded3-1e78e79eeb37%40ibphoenix.com.

[Dimitry Sibiryakov]

22.02.2021 16:54, Luciano Rodrigues Nunes Mendes wrote:
> I have attached the Windows Task Scheduler instructions (BKP_ARAS_CIN.xml) and the batch
> file (.bat) used for it to backup the database (BKP_ARAS_CIN.txt).

These files are not interesting because backup has no chances to break database.
Something had to modify the database during this time to break it.
Judging from table name it is history table. Isn't it cleaned up from time to time? If
yes, didn't it happen in these 5 hours?
Could you show result of "gstat -r -d -t PHONESHISTORY"?

> BTW, does completing a backup successfully ("closing file, committing, and finishing" at
> the end of the gbak verbose) mean that I will always be able to restore the database from
> it or do I need to test this completed backup successfully to be absolutely sure that I
> will be able to restore the database from it?

You'd better to test restore explicitly. There is a kind of logical errors in database
structure that cannot be detected during read.

--
WBR, SD.

[Mark Rotteveel]

On 22-02-2021 16:54, Luciano Rodrigues Nunes Mendes wrote:
> BTW, does completing a backup successfully ("closing file, committing,
> and finishing" at the end of the gbak verbose) mean that I will always
> be able to restore the database from it or do I need to test this
> completed backup successfully to be absolutely sure that I will be able
> to restore the database from it?

No, you cannot assume that a successful backup can also be restored. As
long as you haven't verified if the backup can be restored successfully
(e.g. by restoring it to a temporary location), you're taking a risk
that the backup is unrestorable, and then you have no fallback when
things go wrong.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry

PHONESHISTORY is a table where its data is never deleted but only added. As I mentioned in previous emails, I don't know if data was added in this 5 hours time frame (but it is possible that no because it was the weekend).

The only thing I'm sure happened between the 5 hours time interval of the last successful backup of the ARAS_CIN database and the backup of the ARAS_CIN corrupted database was the query of another database (IRIS_CIN) through a procedure that synchronizes its data pulling data from the ARAS_CIN database.

I have attached the stat from the corrupted database (Stat_CorruptedDatabase.txt) and the stat from the last successful backup of the ARAS_CIN after it is restored (Stat_LastBackupRestored.txt)

Best Regards,

Luciano

--
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/e431259c-1773-7806-12ab-131f846a9564%40ibphoenix.com.

[Dimitry Sibiryakov]

22.02.2021 19:13, Luciano Rodrigues Nunes Mendes wrote:
> I have attached the stat from the corrupted database (Stat_CorruptedDatabase.txt) and the
> stat from the last successful backup of the ARAS_CIN after it is restored
> (Stat_LastBackupRestored.txt)

From these data CORE-6379 can be excluded from the list of possible reasons for such
corruption.
Perhaps, one of Firebird developers would like to look at the corrupted database. Keep
it, please.

--
WBR, SD.

[Karol Bieniaszewski]

>>    These files are not interesting because backup has no chances to break database.

>>   WBR, SD.

 

Are you sure with that?

I supposed that it is only true when -g (inhibit garbage collection) switch is included,

but maybe i do not understand fully this switch and it is applied to destination backup not to the backed up database.

 

Regards,

Karol Bieniaszewski

 

 

 

[Dimitry Sibiryakov]

23.02.2021 08:36, Karol Bieniaszewski wrote:
> Are you sure with that?

Yes for 99%. Such corruption used to be an result of unfortunate interaction between
writers. With single writer hardly something go wrong.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

If a backup completed successfully is no guarantee that the database can be restored through it and the DBA needs to perform a database restore from the backup file to be sure of the integrity of the backup file because it has not been thought of at the time. possibility of implementing an intermediate solution in gbak that would make it possible to validate the integrity of the backup file without the need for the DBA to have to restore the database from this backup file?

Restoring a database from its backup file just to validate the integrity of the backup file is extremely costly and certainly implementing an intermediary solution in gbak that would guarantee the integrity of the file of this backup would certainly be extremely important even if the backup process took longer to complete. 

Best Regards,

Luciano

--
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/012248e5-c190-0e47-523f-029a15c4912c%40ibphoenix.com.

[Dimitry Sibiryakov]

23.02.2021 14:01, Luciano Rodrigues Nunes Mendes wrote:
> possibility of implementing an intermediate solution in gbak that would make it possible
> to validate the integrity of the backup file without the need for the DBA to have to
> restore the database from this backup file?

You can try to make it.

Unrestorable backup is always result of backup from broken database. It is better to
prevent database corruption.

--
WBR, SD.

[Mark Rotteveel]

On 23-02-2021 14:01, Luciano Rodrigues Nunes Mendes wrote:
> If a backup completed successfully is no guarantee that the database can
> be restored through it and the DBA needs to perform a database restore
> from the backup file to be sure of the integrity of the backup file
> because it has not been thought of at the time. possibility of
> implementing an intermediate solution in gbak that would make it
> possible to validate the integrity of the backup file without the need
> for the DBA to have to restore the database from this backup file?
> Restoring a database from its backup file just to validate the integrity
> of the backup file is extremely costly and certainly implementing an
> intermediary solution in gbak that would guarantee the integrity of the
> file of this backup would certainly be extremely important even if the
> backup process took longer to complete.

This is not a general solvable problem, and it doesn't apply to Firebird
alone: until you have restored a backup, you haven't proven you have
something that works.

Also, how do you propose that gbak magically validates that the backup
is restorable? The only way to ensure that a backup is restorable is by
doing an actual restore, and gbak already provides the mechanism to do
that. You don't need the DBA to do that: just automatically restore to a
temporary location.

Mark
--
Mark Rotteveel