Creation Index Error when restore with parallels workers

[Biduzeira]

I'm having problems restoring a db using fbsvcmgr. --> When done via gbak the problem does not occur <--.

Here are the details:

• Firebird-5.0.0.1306 and 5.0.1.1387 (last snapshot) - x86 - Superserver

• Original DB ODS version: 13.1

• Windows 11 x64

• Firebird.conf: DataTypeCompatibility = 3.0 MaxParallelWorkers = 16 ParallelWorkers = 16 RemoteServicePort = 3041 IpcName = FIREBIRD5 RemotePipeName = pipefb5

  -firebird.log: no record.

  • Command line: fbsvcmgr.exe 127.0.0.1/3041:service_mgr -user SYSDBA -password XXXXX action_restore dbname “c:\adm\wesley5.fdb” bkp_file “C:\adm\teste5.fbk” verbose res_no_shadow res_stat TDRW

  • restore log (I removed parts I thought unnecessary with ****):

    c:\ADM\Firebird5>fbsvcmgr.exe 127.0.0.1/3041:service_mgr -user SYSDBA -password XXXXX action_restore dbname "c:\adm\wesley5.fdb" bkp_file "C:\adm\teste5.fbk" verbose res_no_shadow res_stat TDRW gbak: time delta reads writes gbak: 0.000 0.000 0 0 opened file C:\adm\teste5.fbk gbak: 0.001 0.000 0 0 use up to 16 parallel workers gbak: 0.001 0.000 0 0 transportable backup -- data in XDR format gbak: 0.001 0.000 0 0 backup file is compressed gbak: 0.001 0.000 0 0 backup version is 11 gbak: 0.051 0.049 0 789 created database c:\adm\wesley5.fdb, page_size 8192 bytes gbak: 0.056 0.005 0 2 started transaction gbak: 0.057 0.000 0 0 restoring domain T_ANO gbak: 0.057 0.000 0 0 restoring domain T_CPF_CNPJ

    ---

    ---

        gbak:   16.564  0.000      2     10     activating and creating deferred index IBE$LOG_FIELDS_IDX1
        gbak:   16.564  0.000      2      9     activating and creating deferred index PK_GRE_TP_COMPLEMENTO
        gbak:   16.565  0.000      4      9     activating and creating deferred index IBE$LOG_KEYS_IDX1
                    gbak:   16.565  0.000      2      9     activating and creating deferred index PK_COM_PEDIDO
        gbak:   16.625  0.060   1651    161     activating and creating deferred index COM_PEDIDO_IDX2
        gbak:   16.683  0.057   1660    163     activating and creating deferred index COM_PEDIDO_IDX1
        gbak:   16.743  0.060   1659    236     activating and creating deferred index UNQ1_COM_PEDIDO
        gbak:   16.844  0.101   1661    746     activating and creating deferred index UNQ2_COM_PEDIDO
        gbak:   16.929  0.084   1660    107     activating and creating deferred index PK_GNF_NF_CAB
        gbak:   16.929  0.000     13      9     activating and creating deferred index PK_CONT_CNAE

    ---

    ---

        gbak:   17.546  0.000      3      9     activating and creating deferred index FK_COM_PEDIDO_ITEM_ACRESCIMO
        gbak:   18.158  0.612   1834    552     activating and creating deferred index FK_COM_PEDIDO_ITEM_LOCAL_DESTIN
        gbak:   18.363  0.204   1670    469     activating and creating deferred index FK_COM_PEDIDO_ITEM_LOCAL_ORIGEM
        gbak:   19.258  0.895   1667    553     activating and creating deferred index FK_COM_PEDIDO_ITEM_PED_ITEM_OBS
        gbak:   19.443  0.184   1709    454     activating and creating deferred index FK_COM_PEDIDO_ITEM_PEDIDO
            gbak:cannot commit index FK_COM_PEDIDO_ITEM_PEDIDO
        gbak: ERROR:violation of FOREIGN KEY constraint "FK_COM_PEDIDO_ITEM_PEDIDO" on table "COM_PEDIDO_ITEM"
        gbak: ERROR:    Foreign key reference target does not exist
        gbak: ERROR:    Problematic key value is ("ID_PEDIDO" = 113798)
        gbak:   19.604  0.160   1205      5     activating and creating deferred index FK_COM_PEDIDO_ITEM_TP_MOVIMENTO
        gbak:   20.627  1.023   1668    567     activating and creating deferred index FK_SYS_USUARIO_FILIAL_10
        gbak:   20.628  0.000     18      9     activating and creating deferred index FK_SYS_USUARIO_FILIAL_2

    ---

    ---

        gbak:   26.310  0.000      1      9     activating and creating deferred index FK_PIX_COBV_GERADO_TIPO
        gbak:   26.310  0.000      1      9     activating and creating deferred index FK_SYS_CONFIG_PIX_1
        gbak:   26.311  0.000      2     10 adjusting views dbkey length
        gbak:   26.311  0.000     15      0 updating ownership of packages, procedures and tables
        gbak:   26.330  0.019     34      0 adding missing privileges
        gbak:   26.331  0.000      1      0 adjusting system generators
        gbak:   26.506  0.175    577    229 finishing, closing, and going home
        gbak:   26.506  0.000 147439  48249 total statistics
        gbak:adjusting the ONLINE and FORCED WRITES flags
        Database is not online due to failure to activate one or more indices.
        -Run gfix -online to bring database online without active indices.

• structure of the original database: img1 and img2 below

  After the restore, I accessed the restored database and checked the COM_PEDIDO table and did the select “SELECT FROM COM_PEDIDO WHERE ID_PEDIDO = 113798” and it returned EMPTY, then I did another search without using the PK_COM_PEDIDO index “SELECT FROM COM_PEDIDO WHERE ID_PEDIDO = 113798 PLAN (COM_PEDIDO NATURAL)” and it returned 1 record!

  I recreated the database using gbak (because the problem doesn't occur), made a new backup to try a new restore via fbsvcmgr and the error persists, except that the error changed the value of the key “Problematic key value is (”ORDER_ID“ = 113774)”.

  In other words, there seems to be a problem with the creation of the PK_COM_PEDIDO primary key index when recreating via fbsvcmgr. I found this strange because it works normally in GBAK.

  I tried to extract the minimum information from the tables and objects involved to create a script to simulate it, but the error didn't occur.

  if you need any more information, I'm happy to help. thanks.

[hvlad]

What is gbak command line ? Could you try with ParallelWorkers = 1 (or using explicit switch -PAR 1 ) ?

[Biduzeira]

• gbak command line that I used and the error does not occur:
  gbak -rep -k -z -v -ST TDRW -user SYSDBA -pass XXXXX "C:\adm\teste5.fbk" "127.0.0.1/3041:c:\adm\wesley5.fdb"

• so, i also tried forcing fbsvcmgr to use 1 parallel worker (res_parallel_workers 1) and i got NO ERRORS. fbsvcmgr.exe 127.0.0.1/3041:service_mgr -user SYSDBA -password XXXXX action_restore dbname "c:\adm\wesley5.fdb" bkp_file "C:\adm\teste5.fbk" verbose res_no_shadow res_stat TDRW res_replace res_parallel_workers 1

• and i did a reverse test, i forced gbak to use more than 1 parallel worker (-PAR 16) AND THE ERROR HAPPENED :
  gbak -rep -k -z -v -ST TDRW -user SYSDBA -pass XXXXX "C:\adm\teste5.fbk" "127.0.0.1/3041:c:\adm\wesley5.fdb" -PAR 16

  • consequently, gbak with the -PAR 1 option did not return the error: gbak -rep -k -z -v -ST TDRW -user SYSDBA -pass XXXXX "C:\adm\teste5.fbk" "127.0.0.1/3041:c:\adm\wesley5.fdb" -PAR 1

[hvlad]

Could you provide me with this backup for investigation ?

[Biduzeira]

Yes, but it's a customer database. Can you give me an email so I can send you a download link? I will prepare the file for you.

[hvlad]

hvlad at users sf net Thanks!

[Biduzeira]

I just sent it to you, then confirm if you received it and were able to download it please

[hvlad]

Downloaded, thanks.

[hvlad]

Should be fixed now, check next snapshot build, please

[Biduzeira]

Thank you very much for your attention! I will test.

[hvlad]

It is fixed, I believe. If something goes wrong, we could re-open ticket.

[Biduzeira]

yes it was fixed, apologies for the delay