Hi,
I have a stability issue in the following case:
After creating and dropping temporary tables, sometimes the drop fails with an "object in use" error.
This error is handled properly and the application continues to execute SQL statements.
However, after the first time this error occurs, the application becomes unstable: creating and executing new SELECT queries works, but when releasing these query objects, an exception with the same error message "object in use" as before is raised. The error occurs when releasing the dataset of SELECT statements that are executed after the initial DROP TABLE which encounters the "object in use" error. It seems that the previous error state is not well cleared and that the error remains present somewhere in the driver or client. An access violation or an invalid pointer exception often occurs after the subsequent "object in use" errors. After executing some statements, the application will rapidly block (100% cpu or no response) and must be killed.
I don't know if this issue is related to the driver or the client.
I have tested with the 2.5 and 2.1 FB clients, the problem is very reproducible.
With the 1.5.6.5026 FB client, I did not reproduce the problem but I got a lot of silent "socket error 10061" exceptions, however, the application seems to work correctly.
I have also tested the 2.70.0.27 dbexpida driver and 3.1.2.0 demo driver. The issue is reproducible in both cases.
Can you please give me an answer for the following questions:
- is this issue related to the driver or to the client, should I post this issue on Firebird's issue tracker?
- as a temporary solution, is it safe to use the version 1.5 of fbclient with the version 2.5 of the server?
Thanks for your answer.
Environment:
- Delphi 7
- Firebird 2.5.1.26351
- dbexpida 2.70.0.27
Stability issue with Firebird 2.5
-
AndreyZ
Hello,
You should post your question to the Firebird's issue tracker, maybe this problem is known to Firebird developers. We do not recommend you to use the Firebird client library version that differs from the server version, you should ask Firebird developers about this as well. You can also try creating a small sample that demonstrates the problem with temporary tables and send it to andreyz*devart*com . This way, we will be able to investigate this problem faster.
If TSQLMonitor.Active is True and there is no DBMonitor running, the 'Socket error 10061' error is generated. You will see this error only if you run your application from an IDE. You will not see this error when you run the executable file of your application. To solve the problem, you should set the TSQLMonitor.Active property to False.
You should post your question to the Firebird's issue tracker, maybe this problem is known to Firebird developers. We do not recommend you to use the Firebird client library version that differs from the server version, you should ask Firebird developers about this as well. You can also try creating a small sample that demonstrates the problem with temporary tables and send it to andreyz*devart*com . This way, we will be able to investigate this problem faster.
If TSQLMonitor.Active is True and there is no DBMonitor running, the 'Socket error 10061' error is generated. You will see this error only if you run your application from an IDE. You will not see this error when you run the executable file of your application. To solve the problem, you should set the TSQLMonitor.Active property to False.
Hi,
I could not reproduce the stability issue on a minimal application. However, I have written an SQL script that leads to inconsistent results and that reproduces the persistent "object in use" error :
* Step 1:
Initialize the database:
CREATE TABLE test1 (FLD_TEST INTEGER);
INSERT INTO test1 VALUES (1);
CREATE TABLE test2 (FLD_TEST INTEGER);
INSERT INTO test1 VALUES (1);
* Step 2:
With a database explorer, open the table test1 in order to lock it.
* Step 3:
Execute this script with dbexpress:
SELECT * FROM test1;
SELECT * FROM test2;
DROP TABLE test1;
SELECT * FROM test1;
SELECT * FROM test2;
Here is the result that I got:
- Select test1; ok
- Select test2; ok
- Drop Table test1; error [exec]EDatabaseError: Erreur SQL Server : unsuccessful metadata update object TEST1 is in use
- Select test1; error [open]EDatabaseError: Erreur SQL Server : Dynamic SQL Error SQL error code = -204 Table unknown TEST1 At line 1, column 15
- Select test2; error [close]EIBCError: unsuccessful metadata update object TEST1 is in use
As you can see, the result is inconsistent :
- firstly, after the drop, the select behaves as if the table has effectively been dropped, which is obviously not the case,
- secondly, the error "object in use" is raised each time a dataset is closed (note that the dataset can be opened and read normally, the error only affects the close).
I have done the same test with an ODBC driver. The problem does not occur and the result is consistent : both SELECT work as expected and return the expected dataset.
As I cannot reproduce the problem with ODBC, I would rather think that the problem is related to the dbexpida driver.
I could not reproduce the stability issue on a minimal application. However, I have written an SQL script that leads to inconsistent results and that reproduces the persistent "object in use" error :
* Step 1:
Initialize the database:
CREATE TABLE test1 (FLD_TEST INTEGER);
INSERT INTO test1 VALUES (1);
CREATE TABLE test2 (FLD_TEST INTEGER);
INSERT INTO test1 VALUES (1);
* Step 2:
With a database explorer, open the table test1 in order to lock it.
* Step 3:
Execute this script with dbexpress:
SELECT * FROM test1;
SELECT * FROM test2;
DROP TABLE test1;
SELECT * FROM test1;
SELECT * FROM test2;
Here is the result that I got:
- Select test1; ok
- Select test2; ok
- Drop Table test1; error [exec]EDatabaseError: Erreur SQL Server : unsuccessful metadata update object TEST1 is in use
- Select test1; error [open]EDatabaseError: Erreur SQL Server : Dynamic SQL Error SQL error code = -204 Table unknown TEST1 At line 1, column 15
- Select test2; error [close]EIBCError: unsuccessful metadata update object TEST1 is in use
As you can see, the result is inconsistent :
- firstly, after the drop, the select behaves as if the table has effectively been dropped, which is obviously not the case,
- secondly, the error "object in use" is raised each time a dataset is closed (note that the dataset can be opened and read normally, the error only affects the close).
I have done the same test with an ODBC driver. The problem does not occur and the result is consistent : both SELECT work as expected and return the expected dataset.
As I cannot reproduce the problem with ODBC, I would rather think that the problem is related to the dbexpida driver.
-
AndreyZ
-
AndreyZ
We have investigated the problem. The problem occurs because you are using the ExecSQL method that closes cursor right after execution. When a cursor is closing, we commit a transaction that is still opened after unsuccessful attempt of deleting a table. That's why the same "object in use" error occurs again. You can call the Open method instead of the ExecSQL method for the second "SELECT * FROM test2;" query and you will not see any errors. To avoid such problems, we recommend you using explicit transactions in your application, so you will be able to roll back a transaction that was executed with errors.
Thanks for your explanations.
However, some points are not totally clear to me:
- in my tests, the SELECT statements are executed with Open and Close. The error is raised on the Close method, not on the following ExecSQL. In my real application, the statements are executed on different SQLQuery objects. In both cases, the errors are always raised on the Close method.
- the explanation about transactions sounds strange to me: if you commit after a select, the current transaction is always closed after a successful execution, the developer has no control over this. So why letting the transaction open in case of an error, and moreover, in an unusable state? Shouldn't the statement automatically been rolled back in case of an exception? I do not understand what is expected from the developer to be done in such a situation.
However, some points are not totally clear to me:
- in my tests, the SELECT statements are executed with Open and Close. The error is raised on the Close method, not on the following ExecSQL. In my real application, the statements are executed on different SQLQuery objects. In both cases, the errors are always raised on the Close method.
- the explanation about transactions sounds strange to me: if you commit after a select, the current transaction is always closed after a successful execution, the developer has no control over this. So why letting the transaction open in case of an error, and moreover, in an unusable state? Shouldn't the statement automatically been rolled back in case of an exception? I do not understand what is expected from the developer to be done in such a situation.
-
AndreyZ
Ok thanks. I have implemented a rollback in my application after the drop error. It seems to solve the problem. I had no stability issue during my few tests.
I think the stability issue is related to the failing commit in the Close method. I cannot reproduce it easily but I got 2 errors that occur more frequently:
- an invalid pointer error
- an access violation in System._IntfClear
Both seem to be related to an invalid COM interface. As they only appear after the failing commit, and did not appear any more since I have added the rollback, I think that they are caused by something in the Close method.
I hope these informations can help you.
I think the stability issue is related to the failing commit in the Close method. I cannot reproduce it easily but I got 2 errors that occur more frequently:
- an invalid pointer error
- an access violation in System._IntfClear
Both seem to be related to an invalid COM interface. As they only appear after the failing commit, and did not appear any more since I have added the rollback, I think that they are caused by something in the Close method.
I hope these informations can help you.