Problem with restore

[tomaszdubiel18]

Hello.
I think this issue will go to @hvlad.
It turned out that still in Firebird 3.0 backup of the database can be unrestoreable, when a procedure has an error:
"Error while parsing procedure ....
Index cannot be used in the specified plan"
DB on FB 2.5, the procedure compiles. The same procedure on FB 3.0 does not compile and restore is stopped.

[dyemanov]

Looks like a duplicate for #2149.

[tomaszdubiel18]

I'm not sure. The procedure compiles without any problems on FB 2.5, backups are restoreable.
On FB 3.0 that procedure does not compile and the restore is stopped and I know @hvlad made changes so that the restore is not stopped and the database is restoreable.

[tomaszdubiel18]

So good I made a copy of the database, I reverted to FB 2.5, commented plans in this procedure and made backup one more time. :-)

[aafemt]

Two possibilities:

1. Procedure has invalid explicit plan inside which is ignored by 2.5 but rejected by 3.0.
2. Procedure has valid plan which is rejected by 3.0 for some other reason.

The former is not a bug and the latter depends on the real problem.

[tomaszdubiel18]

A body of the original procedure on FB 2.5:
SET TERM ^ ;

create or alter procedure XXX_OMS_ZI_STATUS_KONTRAH (
ID_KONTRAH integer)
returns (
NRKONTRAH integer,
NAZWASKR varchar(60) collate WIN1250,
NAZWADL varchar(240) collate WIN1250,
ULICA varchar(30) collate WIN1250,
NRDOMU varchar(10) collate WIN1250,
NRLOKALU varchar(10) collate WIN1250,
KODPOCZTOWY varchar(10) collate WIN1250,
POCZTA varchar(30) collate WIN1250,
MIEJSCOWOSC varchar(255) collate WIN1250,
NWTERM numeric(15,2),
ZWTERM numeric(15,2),
SWTERM numeric(15,2),
NPTERM numeric(15,2),
ZPTERM numeric(15,2),
SPTERM numeric(15,2),
DOPWK numeric(15,2),
DOPWKWYK numeric(15,2),
DOPWKPOZ numeric(15,2),
DOPWKPTERM numeric(15,2),
DOPWKPTERWYK numeric(15,2),
DOPWKPTERPOZ numeric(15,2),
ILEDOKK integer,
ILEDOKKWYK integer,
ILEDOKKPOZ integer,
ILEDOKPTERM integer,
ILEDOKPTERMWYK integer,
ILEDOKPTERMPOZ integer,
ILEDNIP integer,
ILEDNIPWYK integer,
ILEDNIPPOZ integer)
as
declare variable IDKONTRAH integer;
begin
select K.ID_KONTRAH, K.NRKONTRAH, K.NAZWASKR, DK.NAZWADL, DK.ULICA, DK.NRDOMU, DK.NRLOKALU, DK.KODPOCZTOWY, DK.POCZTA,
DK.MIEJSCOWOSC,
(select sum(R.KAL_DOZAPLATYPLNWN)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 0),
(select sum(R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 0),
((select sum(R.KAL_DOZAPLATYPLNWN)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 0) - (select sum(R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 0)),
(select sum(R.KAL_DOZAPLATYPLNWN)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 1),
(select sum(R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 1),
((select sum(R.KAL_DOZAPLATYPLNWN)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 1) - (select sum(R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where R.ID_KONTRAH = K.ID_KONTRAH and
R.PRZETERM = 1)), K.WARTKREDYTOG,
(select sum(R.KAL_DOZAPLATYPLNWN - R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where (R.ROZL = 0) and
(R.ID_KONTRAH = K.ID_KONTRAH) and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(MK_ROZLICZENIE_ROZL, FK_ROZLICZE_KONTRAHRO_KONTRAH))
),
(coalesce((K.WARTKREDYTOG - (select sum(R.KAL_DOZAPLATYPLNWN - R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where (R.ROZL = 0) and
(R.ID_KONTRAH = K.ID_KONTRAH) and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(MK_ROZLICZENIE_ROZL, FK_ROZLICZE_KONTRAHRO_KONTRAH))
)), 0)),
K.WARTKREDYTPRZET,
(select sum(R.KAL_DOZAPLATYPLNWN - R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where (R.ROZL = 0) and
(R.PRZETERM = 1) and
(R.ID_KONTRAH = K.ID_KONTRAH) and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(MK_ROZLICZENIE_TERMIN, FK_ROZLICZE_KONTRAHRO_KONTRAH))
),
(coalesce((K.WARTKREDYTPRZET - (select sum(R.KAL_DOZAPLATYPLNWN - R.KAL_DOZAPLATYPLNMA)
from ROZLICZENIE R
where (R.ROZL = 0) and
(R.PRZETERM = 1) and
(R.ID_KONTRAH = K.ID_KONTRAH) and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(MK_ROZLICZENIE_TERMIN, FK_ROZLICZE_KONTRAHRO_KONTRAH))
)), 0)),
K.ILOSCDOKKREDYTOG,
(select count()
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.ID_KONTRAH = K.ID_KONTRAH and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(FK_ROZLICZE_KONTRAHRO_KONTRAH))
),
(K.ILOSCDOKKREDYTOG - (select count()
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.ID_KONTRAH = K.ID_KONTRAH and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(FK_ROZLICZE_KONTRAHRO_KONTRAH))
)), K.ILOSCDOKTERMOG,
(select count()
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.PRZETERM = 1 and
R.ID_KONTRAH = K.ID_KONTRAH and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(FK_ROZLICZE_KONTRAHRO_KONTRAH))
),
(K.ILOSCDOKTERMOG - (select count()
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.PRZETERM = 1 and
R.ID_KONTRAH = K.ID_KONTRAH and
(sign(R.KWOTAWN - R.KWOTAMA) = 1)
plan(R index(FK_ROZLICZE_KONTRAHRO_KONTRAH))
)), K.ILOSCDNIPRZETERM,
(select max(R.KAL_DNIPOTERM)
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.PRZETERM = 1 and
R.ID_KONTRAH = K.ID_KONTRAH),
(K.ILOSCDNIPRZETERM - (select max(R.KAL_DNIPOTERM)
from ROZLICZENIE R
where R.ROZLICZONO = 0 and
R.ID_KONTRAH = K.ID_KONTRAH))
from KONTRAH K
join DANEKONTRAH DK on (DK.ID_KONTRAH = K.ID_KONTRAH and
DK.BAZADANEKONTRAH = 1)
where K.ID_KONTRAH = :ID_KONTRAH
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31
into :IDKONTRAH, :NRKONTRAH, :NAZWASKR, :NAZWADL, :ULICA, :NRDOMU, :NRLOKALU, :KODPOCZTOWY, :POCZTA, :MIEJSCOWOSC,
:NWTERM, ZWTERM, :SWTERM, :NPTERM, ZPTERM, :SPTERM, :DOPWK, :DOPWKWYK, :DOPWKPOZ, :DOPWKPTERM, :DOPWKPTERWYK,
:DOPWKPTERPOZ, :ILEDOKK, :ILEDOKKWYK, :ILEDOKKPOZ, :ILEDOKPTERM, :ILEDOKPTERMWYK, :ILEDOKPTERMPOZ, :ILEDNIP,
:ILEDNIPWYK, :ILEDNIPPOZ;
suspend;
end^

SET TERM ; ^

[hvlad]

Could you provide metadata-only backup of "bad" database ?

[tomaszdubiel18]

Next week.

[tomaszdubiel18]

@hvlad, I send you in a sourceforge.net a mail where there is a link to a gbk metadata file.
It's more serious issue as I checked. There are two problems still existing on FB 3.0. When you restore this database, a procedure XXX_OMS_ZI_STATUS_KONTRAH will have 8 commented plans in the body. If you uncomment it and compile that, Firebird will not report any errors. Procedure will be compiled. Then backup it and try to restore. Restore will stop and wont continue its work.

[hvlad]

Reproduced, thanks

[tomaszdubiel18]

Should I create a separate issue? Because one thing is lack of reporting about a problem when compiling a procedure and second is broken restore.

[hvlad]

Looks like a duplicate for #2149.

This is different case: #2149 was about inactive (or non-existing) index used in PLAN,
this case is about an existent but not suitable index used in PLAN.

[hvlad]

Should I create a separate issue? Because one thing is lack of reporting about a problem when compiling a procedure and second is broken restore.

Yes, I agree. Let this ticket be about error on restore and another one about successful compiling of procedure with wrong PLAN(s) used by some of its statement(s).

[tomaszdubiel18]

#7517

[hvlad]

Should be fixed now, please check next build

[tomaszdubiel18]

I checked and the restore completes successfully. Now I see a warning and "restore was performed correctly". Shouldn't be a errorlevel different than 0? Warnings usually mean there are no problems preventing the database from proper functioning. Isnt this the same case as my last reported issue for parallel restore?

[hvlad]

I see no relation with the case of parallel restore.
If you want non-zero errorlevel when database was restored with warnings, please create another ticket and explain why you think it should be this way.

[tomaszdubiel18]

I created an issue: #7523
, but to correct myself: I was reffering to this:
#7465
not to the parallel restore issue.

[hvlad]

I created an issue: #7523 ,

Thanks

but to correct myself: I was reffering to this: #7465 not to the parallel restore issue.

It was my guess too

[pavel-zotov]

@@@ QA issue @@@
Could not reproduce ERROR during gbak that was mentioned here:

If you uncomment it and compile that, Firebird will not report any errors <...>
Then backup it and try to restore. Restore will stop and wont continue its work.

Test currently checks only presense of 'gbak: WARNING' when we try to restore from such .fbk