View Issue Details

IDProjectCategoryView StatusLast Update
0038124FPCDatabasepublic2021-01-14 08:09
ReporterOndrej Pokorny Assigned ToMichael Van Canneyt  
PrioritynormalSeverityminorReproducibilityalways
Status assignedResolutionopen 
Product Version3.3.1 
Fixed in Version3.3.1 
Summary0038124: Regression after r47400: Firebird cannot CREATE or ALTER TABLE anymore
DescriptionAfter r47400 any CREATE or ALTER TABLE statement shows an error:

Project Project1 raised exception class 'EIBDatabaseError' with message:
TIBConnection : Open Cursor :
 -SQL error code = -804
 -SQLDA error
 -request synchronization error

 In file 'src\sqldb\interbase\ibconnection.pp' at line 197:
raise Exc;

0 fpc_raiseexception(0x8ff110, 0x5186d80, 0x511b148) at seh32.inc:86
1 CHECKERROR(0x511b148, 0x8ff110 'Open Cursor', 0x5186d80) at src\sqldb\interbase\ibconnection.pp:197
2 EXECUTE(0x511b148, 0x5186d50, 0x5177d40, 0x9b16b50) at src\sqldb\interbase\ibconnection.pp:982
3 DOEXECUTE(0x5186730) at src\sqldb\sqldb.pp:1064
4 EXECUTE(0x511b3b8) at src\sqldb\sqldb.pp:3032
5 EXECSQL(0x511b3b8) at src\sqldb\sqldb.pp:3201
Steps To ReproduceQ := TSQLQuery.Create(nil);
// ... set up connection and transaction ...
Q.SQL.Text := 'CREATE TABLE NewTable (ID INTEGER)';
Q.ExecSQL;
Additional InformationReverting r47400 fixes the issue.
TagsNo tags attached.
Fixed in Revision47643
FPCOldBugId
FPCTarget3.2.2
Attached Files

Relationships

child of 0037645 resolvedMichael Van Canneyt SqlDb: Calling TSQLQuery.Prepare directly, opening, closing and re opening does not work with IBConnection 

Activities

Michael Van Canneyt

2020-11-24 10:36

administrator   ~0127154

DML statements cannot use a cursor and/or prepared query, I'll need to check this.

Ondrej Pokorny

2020-11-24 12:16

developer   ~0127155

Just realized that UPDATE/DELETE may also be affected because they don't use a cursor either (I didn't test them) - but they need prepared queries for params. Thanks.

Michael Van Canneyt

2020-11-30 18:22

administrator   ~0127271

A cursor is only attached for selectable quries.
However, take care on Firebird 3; sometimes you may get a 'Attempt to reclose a closed cursor'.
I have not yet been able to determine when this exactly happens, it seems there are some issues when the transaction has closed before closing the dataset

Ondrej Pokorny

2020-12-04 17:21

developer   ~0127343

Still not fully working. After
  Query.SQL.Text := 'INSERT INTO MyTable (Name) Values ('Ondrej') RETURNING ID'; // ID is auto-increment primary key
  Query.Open;
  Query.Free; // << exception here

I get this exception in TSQLQuery.Free:
EIBDatabaseError
TIBConnection : Close Cursor :
 -Attempt to reclose a closed cursor


0 fpc_raiseexception(0x8e4b64, 0x53796d8, 0x538b0d0) at seh32.inc:86
1 CHECKERROR(0x538b0d0, 0x8e4b64 'Close Cursor', 0x53796d8) at fcl-db\src\sqldb\interbase\ibconnection.pp:199
2 FREEFLDBUFFERS(0x538b0d0, 0x53796c0) at fcl-db\src\sqldb\interbase\ibconnection.pp:970
3 FREEFLDBUFFERS(0xa109770) at fcl-db\src\sqldb\sqldb.pp:2966
4 INTERNALCLOSE(0xa109770) at fcl-db\src\sqldb\sqldb.pp:3071
5 CLOSECURSOR(0xa109770) at fcl-db\src\base\dataset.inc:208
6 SETACTIVE(0xa109770, false) at fcl-db\src\base\dataset.inc:1104
7 CLOSE(0xa109770) at fcl-db\src\base\dataset.inc:1544
8 DESTROY(0xa109770, 0x1) at fcl-db\src\sqldb\sqldb.pp:2728
9 FREE(0xa109770) at ..\inc\objpas.inc:336

Michael Van Canneyt

2020-12-04 18:45

administrator   ~0127347

I am aware of this.
I found some bugreports in firebird that seem to cause this error, but I don't see how it can be solved.

The only way I see currently is to completely disable support for prepared queries in firebird.

Ondrej Pokorny

2020-12-05 06:04

developer   ~0127350

If this is the only possibility then it is necessary. The INSERT RETURNING is a fairly important query.

Or maybe enable prepared queries support only for SELECT/UPDATE? Disable it for everything else or when in doubt?

Michael Van Canneyt

2020-12-05 09:11

administrator   ~0127351

"insert returning" is a select query, that's the whole problem.
You'll see that the cursor to fetch records is only created for selectable queries.

If we disable the cursor, then we need to find a bugfix for the original problem 0037645.

Anton Kavalenka

2020-12-29 16:36

reporter   ~0127896

Firebird or IBConnection somewhat leaks statement handles

Converting of 672,751 spectra https://mona.fiehnlab.ucdavis.edu/downloads into SQL database
works properly both with SQLIte3 and PostgreSQL but crashes with Firebird 3.0

EIBDatabaseError: TIBConnection : PrepareStatement :
 -too many open handles to database

Anton Kavalenka

2021-01-14 07:44

reporter   ~0128307

Reverting ibconnection.pp to r46756 make large number of insertions work properly.

Ondrej Pokorny

2021-01-14 08:09

developer   ~0128308

I reverted to r46756 as well to be able to run INSERT RETURNING.

Issue History

Date Modified Username Field Change
2020-11-24 10:11 Ondrej Pokorny New Issue
2020-11-24 10:11 Ondrej Pokorny Status new => assigned
2020-11-24 10:11 Ondrej Pokorny Assigned To => Michael Van Canneyt
2020-11-24 10:11 Ondrej Pokorny Relationship added child of 0037645
2020-11-24 10:12 Ondrej Pokorny Description Updated View Revisions
2020-11-24 10:12 Ondrej Pokorny FPCTarget => -
2020-11-24 10:36 Michael Van Canneyt Note Added: 0127154
2020-11-24 12:16 Ondrej Pokorny Note Added: 0127155
2020-11-30 18:22 Michael Van Canneyt Status assigned => resolved
2020-11-30 18:22 Michael Van Canneyt Resolution open => fixed
2020-11-30 18:22 Michael Van Canneyt Fixed in Version => 3.3.1
2020-11-30 18:22 Michael Van Canneyt Fixed in Revision => 47643
2020-11-30 18:22 Michael Van Canneyt FPCTarget - => 3.2.2
2020-11-30 18:22 Michael Van Canneyt Note Added: 0127271
2020-12-04 17:21 Ondrej Pokorny Status resolved => feedback
2020-12-04 17:21 Ondrej Pokorny Resolution fixed => open
2020-12-04 17:21 Ondrej Pokorny Note Added: 0127343
2020-12-04 18:45 Michael Van Canneyt Note Added: 0127347
2020-12-05 06:04 Ondrej Pokorny Note Added: 0127350
2020-12-05 06:04 Ondrej Pokorny Status feedback => assigned
2020-12-05 09:11 Michael Van Canneyt Note Added: 0127351
2020-12-29 16:36 Anton Kavalenka Note Added: 0127896
2021-01-14 07:44 Anton Kavalenka Note Added: 0128307
2021-01-14 08:09 Ondrej Pokorny Note Added: 0128308