Got a "driver: bad connection" error on db.conn.Close() due to previos EOF error on stmt.Query()

[palevi67]

Is this a normal behaviour?

[palevi67]

After further investigation, this seems related to an EOF error obtained on second execution of prepared stmt.Query()

func TestEOF(t *testing.T) {
	conn, err := sql.Open("firebirdsql", "sysdba:secreto@localhost:3050/users/pablo/tmp/hbde.fdb5")
	checkErr(t, err)
	stmt, err := conn.Prepare("select * from T2 where entero_nn>?")
	checkErr(t, err)

	for k := 1; k < 5; k++ {
		fmt.Println("k:", k)
		rows, err := stmt.Query(k) // <- this returns an EOF error the second time is executed ...
		checkErr(t, err)
		rows.Next()
		err = rows.Close()
		checkErr(t, err)
	}

	err = stmt.Close()
	checkErr(t, err)
	err = conn.Close()
	checkErr(t, err)
}

func checkErr(t *testing.T, err error) {
	if err != nil {
		t.Fatal(err)
	}
}

BTW: I'm connecting to the latest FB5 ...

[palevi67]

tested on FB 2.5.9 ... same EOF error :-(

[nakagami]

The first time stmt.Query() is executed, the driver processing is performed inside sql.Open() and conn.Prepare() for the first time.

Are there wrong connection parameters or SQL statements?

[palevi67]

I think there is no error on parameters or statements, I have updated the test and I obtain this output:

k: 1
k: 2
--- FAIL: TestEOF (0.15s)
    hbde_test.go:222: EOF
FAIL

Here is the table DDL and DML, in case you want to run the test yourself:

CREATE TABLE T2 (
	ENTERO_NN INTEGER NOT NULL,
	ENTERO INTEGER,
	TEXTO_NN VARCHAR(30) NOT NULL,
	TEXTO VARCHAR(3000),
	FECHA_NN DATE NOT NULL,
	FECHA DATE,
	HORA_NN TIME NOT NULL,
	HORA TIME,
	MOMENTO_NN TIMESTAMP NOT NULL,
	MOMENTO TIMESTAMP,
	MEMO BLOB SUB_TYPE TEXT,
	BINARIO BLOB SUB_TYPE BINARY,
	SIMPLE_NN FLOAT NOT NULL,
	SIMPLE FLOAT,
	DOBLE_NN DOUBLE PRECISION NOT NULL,
	DOBLE DOUBLE PRECISION,
	LETRAS_NN CHAR(30) NOT NULL,
	LETRAS CHAR(30),
	CONSTRAINT PK_T2 PRIMARY KEY (ENTERO_NN)
);

INSERT INTO T2
(ENTERO_NN, ENTERO, TEXTO_NN, TEXTO, FECHA_NN, FECHA, HORA_NN, HORA, MOMENTO_NN, MOMENTO, MEMO, BINARIO, SIMPLE_NN, SIMPLE, DOBLE_NN, DOBLE, LETRAS_NN, LETRAS)
VALUES(1, 1, 'uno', 'uno', '2024-06-04', '2024-06-04', '12:50:00', '12:50:00', '2024-06-04 12:50:00.000', '2024-06-04 12:50:00.000', 'memo', NULL, 1234.0, 1234.0, 12345678, 12345678, 'HOLA                          ', 'ESCAROLA                      ');
INSERT INTO T2
(ENTERO_NN, ENTERO, TEXTO_NN, TEXTO, FECHA_NN, FECHA, HORA_NN, HORA, MOMENTO_NN, MOMENTO, MEMO, BINARIO, SIMPLE_NN, SIMPLE, DOBLE_NN, DOBLE, LETRAS_NN, LETRAS)
VALUES(2, NULL, 'dos', NULL, '2024-06-04', NULL, '12:50:00', NULL, '2024-06-04 12:50:00.000', NULL, NULL, NULL, 1234.0, NULL, 12345678, NULL, 'HOLA                          ', NULL);

[palevi67]

I have isolated the firebird testing in a sigle file so you can run them easily ...

firebird_test.zip

[nakagami]

Thank you.
I could confirm the same result.

For example, the following modification is not able to gracefull clean up the error and transaction is roll backed automaically.

diff --git a/statement.go b/statement.go
index 16b7932..97bd036 100644
--- a/statement.go
+++ b/statement.go
@@ -152,7 +152,7 @@ func (stmt *firebirdsqlStmt) query(ctx context.Context, args []driver.Value) (dr
                _, _, _, err = stmt.wp.opResponse()
                done <- struct{}{}

-               if err != nil {
+               if err != nil && err.Error() != "EOF"{
                        return nil, err
                }

So I cannot find a good solution.
I assume that this behaviour is a normal behaviour.

[nakagami]

When I have time, I will find out if there is a solution.

[palevi67]

I have just found a workaround/clue for this error: you can do a second call to stmt.Query() after rows.Close() just to clear the error ... here is the modified (passing) test:

func TestEOF(t *testing.T) {

	expectedNumberOfRows := []int{2, 2, 1, 0, 0}

	conn, err := sql.Open("firebirdsql", "sysdba:secreto@localhost:3050/users/pablo/tmp/hbde.fdb5")
	require.NoError(t, err)
	stmt, err := conn.Prepare("select entero_nn from T2 where entero_nn>=?")
	require.NoError(t, err)

	for k := 0; k < 5; k++ {
		rows, err := stmt.Query(k) // <-- this returns an EOF error the second time is executed ...
		require.NoError(t, err)
		n := 0
		for rows.Next() {
			err = rows.Err()
			require.NoError(t, err)
			n++
		}
		require.Equal(t, expectedNumberOfRows[k], n)
		err = rows.Close()
		require.NoError(t, err)
		stmt.Query() // <-- workaround: this is just to clear the EOF error
	}

	err = stmt.Close()
	require.NoError(t, err)
	err = conn.Close()
	require.NoError(t, err)
}

[nakagami]

thanks

[nakagami]

Internally, the modification was such that the statement was reworked.

#173

This is unfortunate, because it would make the use of Prepare() meaningless, but I prioritized fixing the problem without breaking the current behavior.

If anyone has a fix that uses transactions and statements more efficiently, I'd be glad to hear it.