26 views
Skip to first unread message
hamacker
Sep 2, 2025, 1:43:19 PM (6 days ago) Sep 2
to firebird...@googlegroups.com
I have this script psql in FB5 below to transfer data from replication server(without any concurrent connections) to my local database, it's a huge table with 1,086,687 records, and when I try to execute, after 10~15 minutes:
/opt/firebird/bin/isql "localhost/8050:data.fdb" -i "/tmp/cv_itens_sub1.sql"
Statement failed, SQLSTATE = HY008
operation was cancelled
-Config level timeout expired.
After line 5 in file /tmp/cv_itens_sub1.sql
Why? How Can I resolve this?
Others scripts for other tables runs fine, but the table bellow itś a first huge table. I think that's because of the timeout from the source remote server.
<-BEGIN->
-- Data transfer for table CV_ITENS_SUB1
SET NAMES ISO8859_1;
SET STATEMENT TIMEOUT 0; -- no limit
SET LOCAL_TIMEOUT 0;
SET TERM ^ ;
EXECUTE BLOCK AS
DECLARE ins_sql VARCHAR(8000);
DECLARE vSKIP_HASDATA BOOLEAN = TRUE;
DECLARE vPODE_TRANSF BOOLEAN = TRUE;
-- source database credentials & connection
DECLARE vDB_FROM_HOST VARCHAR(255) = 'db';
DECLARE vDB_FROM_PORT VARCHAR(10) = '3050';
DECLARE vDB_FROM_DATABASE VARCHAR(255) = 'data.fdb';
DECLARE vDB_FROM_USERNAME VARCHAR(63) = 'SYSDBA';
DECLARE vDB_FROM_PASSWORD VARCHAR(63) = 'masterkey';
DECLARE vDB_FROM_CONN VARCHAR(512);
-- destination variables (primitive types per domains)
DECLARE vID_CV_ITENS_SUB1 BIGINT; -- identity PK
DECLARE vID_CV_ITEM BIGINT; -- D_ROWID
DECLARE vID_CC_PRODUTO BIGINT; -- D_ROWID
DECLARE vSEQ INTEGER; -- D_SEQUENCIA_LONGA
DECLARE vCODITEM VARCHAR(30); -- D_CODIGO30
DECLARE vQUANTIDADE NUMERIC(18,4); -- D_QUANTIDADE
DECLARE vUNIDADE VARCHAR(6); -- D_UNIDADE
DECLARE vDESCRICAO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vOBSERVACAO VARCHAR(250); -- D_DESCRICAO
DECLARE vGARANTIA_MESES INTEGER; -- D_SEQUENCIA_LONGA
DECLARE vGARANTIA_TEXTO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vGARANTIA_INICIO_DATA DATE; -- D_DATA
DECLARE vGARANTIA_TERMINO_DATA DATE; -- D_DATA
DECLARE vVL_RESPONSAVEL VARCHAR(32); -- D_CURRENT_USER
DECLARE vVL_BASE NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_BASE_MATERIAL NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_BASE_NAO_MATERIAL NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_UNITARIO NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_TOTAL_ACUM NUMERIC(18,2); -- D_VALOR2
DECLARE vBDI_VL_BASE NUMERIC(18,2); -- D_VALOR2
DECLARE vMOBIL_ORDENACAO_ID BIGINT; -- D_ROWID
DECLARE vOCULTAR_SN CHAR(1); -- D_RESPOSTA
DECLARE vOPCIONAL_SN CHAR(1); -- D_RESPOSTA
DECLARE vOPCIONAL_CODITEM VARCHAR(30); -- D_CODIGO30
DECLARE vOPCIONAL_DESCRICAO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vOPCIONAL_VL_TOTAL_DIF NUMERIC(18,2); -- D_VALOR2
DECLARE vVINCULADO_SN CHAR(1); -- D_RESPOSTA
DECLARE vINCLUIR_VINCULARES_SN CHAR(1); -- D_RESPOSTA
DECLARE vCODFIGURA VARCHAR(30); -- D_CODIGO30
DECLARE vCODFIGURA_ICO VARCHAR(30); -- D_CODIGO30
DECLARE vORIGEM VARCHAR(255); -- D_NOME_ARQUIVO
DECLARE vSTATUS CHAR(1); -- D_STATUS
DECLARE vLAST_UPDATE TIMESTAMP; -- D_CURRENT_TIME
DECLARE vLAST_OWNER VARCHAR(32); -- D_CURRENT_USER
BEGIN
-- build connection string
vDB_FROM_CONN = vDB_FROM_HOST || '/' || vDB_FROM_PORT || ':' || vDB_FROM_DATABASE;
-- skip if destination already has data
IF (vSKIP_HASDATA) THEN
BEGIN
IF (EXISTS(SELECT * FROM CV_ITENS_SUB1 ROWS 1)) THEN
BEGIN
EXIT;
END
END
-- disable timeout in the SOURCE DB session (opened via ON EXTERNAL)
EXECUTE STATEMENT 'SET STATEMENT TIMEOUT 0;'
ON EXTERNAL :vDB_FROM_CONN
AS USER :vDB_FROM_USERNAME
PASSWORD :vDB_FROM_PASSWORD;
-- PK (ID_CV_ITENS_SUB1) -> use UPDATE OR INSERT ... MATCHING
ins_sql =
'UPDATE OR INSERT INTO CV_ITENS_SUB1 ('||
' ID_CV_ITENS_SUB1,'||
' ID_CV_ITEM,'||
' ID_CC_PRODUTO,'||
' SEQ,'||
' CODITEM,'||
' QUANTIDADE,'||
' UNIDADE,'||
' DESCRICAO,'||
' OBSERVACAO,'||
' GARANTIA_MESES,'||
' GARANTIA_TEXTO,'||
' GARANTIA_INICIO_DATA,'||
' GARANTIA_TERMINO_DATA,'||
' VL_RESPONSAVEL,'||
' VL_BASE,'||
' VL_BASE_MATERIAL,'||
' VL_BASE_NAO_MATERIAL,'||
' VL_UNITARIO,'||
' VL_TOTAL_ACUM,'||
' BDI_VL_BASE,'||
' MOBIL_ORDENACAO_ID,'||
' OCULTAR_SN,'||
' OPCIONAL_SN,'||
' OPCIONAL_CODITEM,'||
' OPCIONAL_DESCRICAO,'||
' OPCIONAL_VL_TOTAL_DIF,'||
' VINCULADO_SN,'||
' INCLUIR_VINCULARES_SN,'||
' CODFIGURA,'||
' CODFIGURA_ICO,'||
' ORIGEM,'||
' STATUS,'||
' LAST_UPDATE,'||
' LAST_OWNER'||
') VALUES ('||
' :p_id_cv_itens_sub1,'||
' :p_id_cv_item,'||
' :p_id_cc_produto,'||
' :p_seq,'||
' :p_coditem,'||
' :p_quantidade,'||
' :p_unidade,'||
' :p_descricao,'||
' :p_observacao,'||
' :p_garantia_meses,'||
' :p_garantia_texto,'||
' :p_garantia_inicio_data,'||
' :p_garantia_termino_data,'||
' :p_vl_responsavel,'||
' :p_vl_base,'||
' :p_vl_base_material,'||
' :p_vl_base_nao_material,'||
' :p_vl_unitario,'||
' :p_vl_total_acum,'||
' :p_bdi_vl_base,'||
' :p_mobil_ordenacao_id,'||
' :p_ocultar_sn,'||
' :p_opcional_sn,'||
' :p_opcional_coditem,'||
' :p_opcional_descricao,'||
' :p_opcional_vl_total_dif,'||
' :p_vinculado_sn,'||
' :p_incluir_vinculares_sn,'||
' :p_codfigura,'||
' :p_codfigura_ico,'||
' :p_origem,'||
' :p_status,'||
' :p_last_update,'||
' :p_last_owner'||
') MATCHING (ID_CV_ITENS_SUB1)';
FOR
EXECUTE STATEMENT
'SELECT '||
' ID_CV_ITENS_SUB1,'||
' ID_CV_ITEM,'||
' ID_CC_PRODUTO,'||
' SEQ,'||
' CODITEM,'||
' QUANTIDADE,'||
' UNIDADE,'||
' DESCRICAO,'||
' OBSERVACAO,'||
' GARANTIA_MESES,'||
' GARANTIA_TEXTO,'||
' GARANTIA_INICIO_DATA,'||
' GARANTIA_TERMINO_DATA,'||
' VL_RESPONSAVEL,'||
' VL_BASE,'||
' VL_BASE_MATERIAL,'||
' VL_BASE_NAO_MATERIAL,'||
' VL_UNITARIO,'||
' VL_TOTAL_ACUM,'||
' BDI_VL_BASE,'||
' MOBIL_ORDENACAO_ID,'||
' OCULTAR_SN,'||
' OPCIONAL_SN,'||
' OPCIONAL_CODITEM,'||
' OPCIONAL_DESCRICAO,'||
' OPCIONAL_VL_TOTAL_DIF,'||
' VINCULADO_SN,'||
' INCLUIR_VINCULARES_SN,'||
' CODFIGURA,'||
' CODFIGURA_ICO,'||
' ORIGEM,'||
' STATUS,'||
' LAST_UPDATE,'||
' LAST_OWNER '||
'FROM CV_ITENS_SUB1'
ON EXTERNAL :vDB_FROM_CONN
AS USER :vDB_FROM_USERNAME
PASSWORD :vDB_FROM_PASSWORD
INTO
vID_CV_ITENS_SUB1,
vID_CV_ITEM,
vID_CC_PRODUTO,
vSEQ,
vCODITEM,
vQUANTIDADE,
vUNIDADE,
vDESCRICAO,
vOBSERVACAO,
vGARANTIA_MESES,
vGARANTIA_TEXTO,
vGARANTIA_INICIO_DATA,
vGARANTIA_TERMINO_DATA,
vVL_RESPONSAVEL,
vVL_BASE,
vVL_BASE_MATERIAL,
vVL_BASE_NAO_MATERIAL,
vVL_UNITARIO,
vVL_TOTAL_ACUM,
vBDI_VL_BASE,
vMOBIL_ORDENACAO_ID,
vOCULTAR_SN,
vOPCIONAL_SN,
vOPCIONAL_CODITEM,
vOPCIONAL_DESCRICAO,
vOPCIONAL_VL_TOTAL_DIF,
vVINCULADO_SN,
vINCLUIR_VINCULARES_SN,
vCODFIGURA,
vCODFIGURA_ICO,
vORIGEM,
vSTATUS,
vLAST_UPDATE,
vLAST_OWNER
DO BEGIN
vPODE_TRANSF = TRUE;
IF (vPODE_TRANSF) THEN
BEGIN
EXECUTE STATEMENT (ins_sql)
( p_id_cv_itens_sub1 := vID_CV_ITENS_SUB1,
p_id_cv_item := vID_CV_ITEM,
p_id_cc_produto := vID_CC_PRODUTO,
p_seq := vSEQ,
p_coditem := vCODITEM,
p_quantidade := vQUANTIDADE,
p_unidade := vUNIDADE,
p_descricao := vDESCRICAO,
p_observacao := vOBSERVACAO,
p_garantia_meses := vGARANTIA_MESES,
p_garantia_texto := vGARANTIA_TEXTO,
p_garantia_inicio_data := vGARANTIA_INICIO_DATA,
p_garantia_termino_data:= vGARANTIA_TERMINO_DATA,
p_vl_responsavel := vVL_RESPONSAVEL,
p_vl_base := vVL_BASE,
p_vl_base_material := vVL_BASE_MATERIAL,
p_vl_base_nao_material := vVL_BASE_NAO_MATERIAL,
p_vl_unitario := vVL_UNITARIO,
p_vl_total_acum := vVL_TOTAL_ACUM,
p_bdi_vl_base := vBDI_VL_BASE,
p_mobil_ordenacao_id := vMOBIL_ORDENACAO_ID,
p_ocultar_sn := vOCULTAR_SN,
p_opcional_sn := vOPCIONAL_SN,
p_opcional_coditem := vOPCIONAL_CODITEM,
p_opcional_descricao := vOPCIONAL_DESCRICAO,
p_opcional_vl_total_dif:= vOPCIONAL_VL_TOTAL_DIF,
p_vinculado_sn := vVINCULADO_SN,
p_incluir_vinculares_sn:= vINCLUIR_VINCULARES_SN,
p_codfigura := vCODFIGURA,
p_codfigura_ico := vCODFIGURA_ICO,
p_origem := vORIGEM,
p_status := vSTATUS,
p_last_update := vLAST_UPDATE,
p_last_owner := vLAST_OWNER
);
END
END
END^
SET TERM ; ^
<-END->
/opt/firebird/bin/isql "localhost/8050:data.fdb" -i "/tmp/cv_itens_sub1.sql"
Statement failed, SQLSTATE = HY008
operation was cancelled
-Config level timeout expired.
After line 5 in file /tmp/cv_itens_sub1.sql
Why? How Can I resolve this?
Others scripts for other tables runs fine, but the table bellow itś a first huge table. I think that's because of the timeout from the source remote server.
<-BEGIN->
-- Data transfer for table CV_ITENS_SUB1
SET NAMES ISO8859_1;
SET STATEMENT TIMEOUT 0; -- no limit
SET LOCAL_TIMEOUT 0;
SET TERM ^ ;
EXECUTE BLOCK AS
DECLARE ins_sql VARCHAR(8000);
DECLARE vSKIP_HASDATA BOOLEAN = TRUE;
DECLARE vPODE_TRANSF BOOLEAN = TRUE;
-- source database credentials & connection
DECLARE vDB_FROM_HOST VARCHAR(255) = 'db';
DECLARE vDB_FROM_PORT VARCHAR(10) = '3050';
DECLARE vDB_FROM_DATABASE VARCHAR(255) = 'data.fdb';
DECLARE vDB_FROM_USERNAME VARCHAR(63) = 'SYSDBA';
DECLARE vDB_FROM_PASSWORD VARCHAR(63) = 'masterkey';
DECLARE vDB_FROM_CONN VARCHAR(512);
-- destination variables (primitive types per domains)
DECLARE vID_CV_ITENS_SUB1 BIGINT; -- identity PK
DECLARE vID_CV_ITEM BIGINT; -- D_ROWID
DECLARE vID_CC_PRODUTO BIGINT; -- D_ROWID
DECLARE vSEQ INTEGER; -- D_SEQUENCIA_LONGA
DECLARE vCODITEM VARCHAR(30); -- D_CODIGO30
DECLARE vQUANTIDADE NUMERIC(18,4); -- D_QUANTIDADE
DECLARE vUNIDADE VARCHAR(6); -- D_UNIDADE
DECLARE vDESCRICAO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vOBSERVACAO VARCHAR(250); -- D_DESCRICAO
DECLARE vGARANTIA_MESES INTEGER; -- D_SEQUENCIA_LONGA
DECLARE vGARANTIA_TEXTO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vGARANTIA_INICIO_DATA DATE; -- D_DATA
DECLARE vGARANTIA_TERMINO_DATA DATE; -- D_DATA
DECLARE vVL_RESPONSAVEL VARCHAR(32); -- D_CURRENT_USER
DECLARE vVL_BASE NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_BASE_MATERIAL NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_BASE_NAO_MATERIAL NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_UNITARIO NUMERIC(18,2); -- D_VALOR2
DECLARE vVL_TOTAL_ACUM NUMERIC(18,2); -- D_VALOR2
DECLARE vBDI_VL_BASE NUMERIC(18,2); -- D_VALOR2
DECLARE vMOBIL_ORDENACAO_ID BIGINT; -- D_ROWID
DECLARE vOCULTAR_SN CHAR(1); -- D_RESPOSTA
DECLARE vOPCIONAL_SN CHAR(1); -- D_RESPOSTA
DECLARE vOPCIONAL_CODITEM VARCHAR(30); -- D_CODIGO30
DECLARE vOPCIONAL_DESCRICAO BLOB SUB_TYPE TEXT; -- D_MEMO
DECLARE vOPCIONAL_VL_TOTAL_DIF NUMERIC(18,2); -- D_VALOR2
DECLARE vVINCULADO_SN CHAR(1); -- D_RESPOSTA
DECLARE vINCLUIR_VINCULARES_SN CHAR(1); -- D_RESPOSTA
DECLARE vCODFIGURA VARCHAR(30); -- D_CODIGO30
DECLARE vCODFIGURA_ICO VARCHAR(30); -- D_CODIGO30
DECLARE vORIGEM VARCHAR(255); -- D_NOME_ARQUIVO
DECLARE vSTATUS CHAR(1); -- D_STATUS
DECLARE vLAST_UPDATE TIMESTAMP; -- D_CURRENT_TIME
DECLARE vLAST_OWNER VARCHAR(32); -- D_CURRENT_USER
BEGIN
-- build connection string
vDB_FROM_CONN = vDB_FROM_HOST || '/' || vDB_FROM_PORT || ':' || vDB_FROM_DATABASE;
-- skip if destination already has data
IF (vSKIP_HASDATA) THEN
BEGIN
IF (EXISTS(SELECT * FROM CV_ITENS_SUB1 ROWS 1)) THEN
BEGIN
EXIT;
END
END
-- disable timeout in the SOURCE DB session (opened via ON EXTERNAL)
EXECUTE STATEMENT 'SET STATEMENT TIMEOUT 0;'
ON EXTERNAL :vDB_FROM_CONN
AS USER :vDB_FROM_USERNAME
PASSWORD :vDB_FROM_PASSWORD;
-- PK (ID_CV_ITENS_SUB1) -> use UPDATE OR INSERT ... MATCHING
ins_sql =
'UPDATE OR INSERT INTO CV_ITENS_SUB1 ('||
' ID_CV_ITENS_SUB1,'||
' ID_CV_ITEM,'||
' ID_CC_PRODUTO,'||
' SEQ,'||
' CODITEM,'||
' QUANTIDADE,'||
' UNIDADE,'||
' DESCRICAO,'||
' OBSERVACAO,'||
' GARANTIA_MESES,'||
' GARANTIA_TEXTO,'||
' GARANTIA_INICIO_DATA,'||
' GARANTIA_TERMINO_DATA,'||
' VL_RESPONSAVEL,'||
' VL_BASE,'||
' VL_BASE_MATERIAL,'||
' VL_BASE_NAO_MATERIAL,'||
' VL_UNITARIO,'||
' VL_TOTAL_ACUM,'||
' BDI_VL_BASE,'||
' MOBIL_ORDENACAO_ID,'||
' OCULTAR_SN,'||
' OPCIONAL_SN,'||
' OPCIONAL_CODITEM,'||
' OPCIONAL_DESCRICAO,'||
' OPCIONAL_VL_TOTAL_DIF,'||
' VINCULADO_SN,'||
' INCLUIR_VINCULARES_SN,'||
' CODFIGURA,'||
' CODFIGURA_ICO,'||
' ORIGEM,'||
' STATUS,'||
' LAST_UPDATE,'||
' LAST_OWNER'||
') VALUES ('||
' :p_id_cv_itens_sub1,'||
' :p_id_cv_item,'||
' :p_id_cc_produto,'||
' :p_seq,'||
' :p_coditem,'||
' :p_quantidade,'||
' :p_unidade,'||
' :p_descricao,'||
' :p_observacao,'||
' :p_garantia_meses,'||
' :p_garantia_texto,'||
' :p_garantia_inicio_data,'||
' :p_garantia_termino_data,'||
' :p_vl_responsavel,'||
' :p_vl_base,'||
' :p_vl_base_material,'||
' :p_vl_base_nao_material,'||
' :p_vl_unitario,'||
' :p_vl_total_acum,'||
' :p_bdi_vl_base,'||
' :p_mobil_ordenacao_id,'||
' :p_ocultar_sn,'||
' :p_opcional_sn,'||
' :p_opcional_coditem,'||
' :p_opcional_descricao,'||
' :p_opcional_vl_total_dif,'||
' :p_vinculado_sn,'||
' :p_incluir_vinculares_sn,'||
' :p_codfigura,'||
' :p_codfigura_ico,'||
' :p_origem,'||
' :p_status,'||
' :p_last_update,'||
' :p_last_owner'||
') MATCHING (ID_CV_ITENS_SUB1)';
FOR
EXECUTE STATEMENT
'SELECT '||
' ID_CV_ITENS_SUB1,'||
' ID_CV_ITEM,'||
' ID_CC_PRODUTO,'||
' SEQ,'||
' CODITEM,'||
' QUANTIDADE,'||
' UNIDADE,'||
' DESCRICAO,'||
' OBSERVACAO,'||
' GARANTIA_MESES,'||
' GARANTIA_TEXTO,'||
' GARANTIA_INICIO_DATA,'||
' GARANTIA_TERMINO_DATA,'||
' VL_RESPONSAVEL,'||
' VL_BASE,'||
' VL_BASE_MATERIAL,'||
' VL_BASE_NAO_MATERIAL,'||
' VL_UNITARIO,'||
' VL_TOTAL_ACUM,'||
' BDI_VL_BASE,'||
' MOBIL_ORDENACAO_ID,'||
' OCULTAR_SN,'||
' OPCIONAL_SN,'||
' OPCIONAL_CODITEM,'||
' OPCIONAL_DESCRICAO,'||
' OPCIONAL_VL_TOTAL_DIF,'||
' VINCULADO_SN,'||
' INCLUIR_VINCULARES_SN,'||
' CODFIGURA,'||
' CODFIGURA_ICO,'||
' ORIGEM,'||
' STATUS,'||
' LAST_UPDATE,'||
' LAST_OWNER '||
'FROM CV_ITENS_SUB1'
ON EXTERNAL :vDB_FROM_CONN
AS USER :vDB_FROM_USERNAME
PASSWORD :vDB_FROM_PASSWORD
INTO
vID_CV_ITENS_SUB1,
vID_CV_ITEM,
vID_CC_PRODUTO,
vSEQ,
vCODITEM,
vQUANTIDADE,
vUNIDADE,
vDESCRICAO,
vOBSERVACAO,
vGARANTIA_MESES,
vGARANTIA_TEXTO,
vGARANTIA_INICIO_DATA,
vGARANTIA_TERMINO_DATA,
vVL_RESPONSAVEL,
vVL_BASE,
vVL_BASE_MATERIAL,
vVL_BASE_NAO_MATERIAL,
vVL_UNITARIO,
vVL_TOTAL_ACUM,
vBDI_VL_BASE,
vMOBIL_ORDENACAO_ID,
vOCULTAR_SN,
vOPCIONAL_SN,
vOPCIONAL_CODITEM,
vOPCIONAL_DESCRICAO,
vOPCIONAL_VL_TOTAL_DIF,
vVINCULADO_SN,
vINCLUIR_VINCULARES_SN,
vCODFIGURA,
vCODFIGURA_ICO,
vORIGEM,
vSTATUS,
vLAST_UPDATE,
vLAST_OWNER
DO BEGIN
vPODE_TRANSF = TRUE;
IF (vPODE_TRANSF) THEN
BEGIN
EXECUTE STATEMENT (ins_sql)
( p_id_cv_itens_sub1 := vID_CV_ITENS_SUB1,
p_id_cv_item := vID_CV_ITEM,
p_id_cc_produto := vID_CC_PRODUTO,
p_seq := vSEQ,
p_coditem := vCODITEM,
p_quantidade := vQUANTIDADE,
p_unidade := vUNIDADE,
p_descricao := vDESCRICAO,
p_observacao := vOBSERVACAO,
p_garantia_meses := vGARANTIA_MESES,
p_garantia_texto := vGARANTIA_TEXTO,
p_garantia_inicio_data := vGARANTIA_INICIO_DATA,
p_garantia_termino_data:= vGARANTIA_TERMINO_DATA,
p_vl_responsavel := vVL_RESPONSAVEL,
p_vl_base := vVL_BASE,
p_vl_base_material := vVL_BASE_MATERIAL,
p_vl_base_nao_material := vVL_BASE_NAO_MATERIAL,
p_vl_unitario := vVL_UNITARIO,
p_vl_total_acum := vVL_TOTAL_ACUM,
p_bdi_vl_base := vBDI_VL_BASE,
p_mobil_ordenacao_id := vMOBIL_ORDENACAO_ID,
p_ocultar_sn := vOCULTAR_SN,
p_opcional_sn := vOPCIONAL_SN,
p_opcional_coditem := vOPCIONAL_CODITEM,
p_opcional_descricao := vOPCIONAL_DESCRICAO,
p_opcional_vl_total_dif:= vOPCIONAL_VL_TOTAL_DIF,
p_vinculado_sn := vVINCULADO_SN,
p_incluir_vinculares_sn:= vINCLUIR_VINCULARES_SN,
p_codfigura := vCODFIGURA,
p_codfigura_ico := vCODFIGURA_ICO,
p_origem := vORIGEM,
p_status := vSTATUS,
p_last_update := vLAST_UPDATE,
p_last_owner := vLAST_OWNER
);
END
END
END^
SET TERM ; ^
<-END->
Vlad Khorsun
Sep 2, 2025, 2:43:15 PM (6 days ago) Sep 2
to firebird-support
I have this script psql in FB5 below to transfer data from replication server(without any concurrent connections) to my local database, it's a huge table with 1,086,687 records, and when I try to execute, after 10~15 minutes:
/opt/firebird/bin/isql "localhost/8050:data.fdb" -i "/tmp/cv_itens_sub1.sql"
Statement failed, SQLSTATE = HY008
operation was cancelled
-Config level timeout expired.
After line 5 in file /tmp/cv_itens_sub1.sql
Why? How Can I resolve this?
Carefully reading documentation, see doc\README.statement_timeouts:
- effective value of timeout is evaluated every time statement starts execution
(or cursor is opened) as:
- if not set at statement level, look at connection level
- if not set at connection level, look at database level
- in any case can't be greater than value set at database level
i.e. value of statement timeout could be overriden by application developer at lower
scope but it can't relax limit set by DBA (in config)
Regards,
Vlad
hamacker
Sep 2, 2025, 3:31:16 PM (6 days ago) Sep 2
to firebird...@googlegroups.com
How Can I set these parameters in script?
In source firebird.conf server I set:
StatementTimeout = 0
But it does not resolve!
In script:
SET SESSION IDLE TIMEOUT 60;
SET STATEMENT TIMEOUT 0; -- no limits
SET LOCAL_TIMEOUT 0;
In source firebird.conf server I set:
StatementTimeout = 0
But it does not resolve!
In script:
SET SESSION IDLE TIMEOUT 60;
SET STATEMENT TIMEOUT 0; -- no limits
SET LOCAL_TIMEOUT 0;
After:
SET STATEMENT TIMEOUT 1 HOUR;
SET SESSION IDLE TIMEOUT 1 HOUR;
And still the same!
SET STATEMENT TIMEOUT 1 HOUR;
SET SESSION IDLE TIMEOUT 1 HOUR;
And still the same!
Vlad Khorsun
Sep 2, 2025, 4:07:37 PM (6 days ago) Sep 2
to firebird-support
How Can I set these parameters in script?
You already tried to set it, but you should consider how effective value is evaluated.
In source firebird.conf server I set:
StatementTimeout = 0
But it does not resolve!
Did you restart Firebird after it ?
What about target (local) server ?
Looks like timeout error raised by local server, not external.In script:
SET SESSION IDLE TIMEOUT 60;
SET STATEMENT TIMEOUT 0; -- no limits
This is not "no limits". This is "statement timeout is not set at connection level".
If statement timeout is set at database or config level - it will be used.
SET LOCAL_TIMEOUT 0;After:
SET STATEMENT TIMEOUT 1 HOUR;
SET SESSION IDLE TIMEOUT 1 HOUR;
And still the same!
Check RDB$CONFIG for actual timeout value used by Firebird at config level.
Check MON$ATTACHMENTS for
timeout value at connection level.
Regards,
Vlad
hamacker
Sep 2, 2025, 4:24:17 PM (6 days ago) Sep 2
to firebird...@googlegroups.com
I moved the StatementTimeout parameter from firebird.conf to databases.conf:
vidy15.fdb = /var/fdb/vidy15.fdb
{
StatementTimeout = 0
}
It seems to have worked — the transfer now persists after 15 minutes, whereas before it used to stop around 5 minutes. I don’t really understand why the parameter inside firebird.conf didn’t work, but it did work in databases.conf.
Yes, I restarted the remote (source) server every time I made changes.
vidy15.fdb = /var/fdb/vidy15.fdb
{
StatementTimeout = 0
}
It seems to have worked — the transfer now persists after 15 minutes, whereas before it used to stop around 5 minutes. I don’t really understand why the parameter inside firebird.conf didn’t work, but it did work in databases.conf.
Yes, I restarted the remote (source) server every time I made changes.
--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
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, visit https://groups.google.com/d/msgid/firebird-support/f5cc37cb-460f-452a-8866-d24614d85b68n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages