[Defect Created]Sql{Create/Free}Session() with Oracle OLEDB

[wardies]

During confidence tests on TD6.1 SP3r we encountered the following issue with Oracle OLEDB.

After calling SqlFreeSession() on a valid session handle, the next time SqlCreateSession() is called for the same session properties, it fails with the error:

Code: Select all

Neither the isolation level nor a strengthening of it is supported.

I have attached source code for a test app. It's written in TD4.2 (to show it working) but can also be run without modification in TD6.1 to see the problem.

This problem occurs both with Oracle 9i and 11i OLEDB.

Here is an example log. As you can see, the retry attempt to connect the second time succeeds. It's always like this. For every disconnect you get a failed subsequent connect.

Code: Select all

12:30:01.28 - Preparing to connect (Provider=OraOLEDB.Oracle;Password=***;User ID=gen;Data Source=wab///)
12:30:01.39 - Connect success!
12:30:08.67 - Preparing to disconnect...
12:30:08.67 - Disconnect success!
12:30:10.94 - Preparing to connect (Provider=OraOLEDB.Oracle;Password=***;User ID=gen;Data Source=wab///)
12:30:13.80 - SqlCreateSession(...) FAILED!
12:30:13.80 - Connect FAILED!
12:30:16.41 - Preparing to connect (Provider=OraOLEDB.Oracle;Password=***;User ID=gen;Data Source=wab///)
12:30:16.45 - Connect success!

I have tried other providers, e.g. ODBC, and the native routers. All those I tested work fine. It's just Oracle OLEDB that fails in this way.

Can anyone confirm this please? Thanks.

[wardies]

It seems like this may be connected to a previously reported issue with SqlFreeSession:

[DEFECT ADDED] SqlFreeSession() does not free the session
[...] reported this as a new TD defect: TD-18818

I am seeing the same problem: SqlFreeSession() is not actually freeing the session and the session count on the Oracle server is increasing. Very quickly all sessions would be used up on the Oracle server, creating an out of service situation for our users.

Is there an ETD on the above defect being fixed? Perhaps in the upcoming SP4?

[Mike Vandine]

I can see that you have already been in contact with your salesperson. This will be fixed in the first EMP after the TD6.1 SP4 release.

Best regards,

[wardies]

Thank you for the fix. We have tested the EMP (build 38735) provided for TD6.1 SP4 and it partially solves the problem.

However, there are still remaining connection issues that appear from time to time that can cause failure to connect or aborts:

With TD6.1 PTF4 EMP (38735):

1. Run up the TestCreateSession app (as previously posted)
2. Using the Oracle OLEDB provider, enter invalid user credentials (e.g. wrong Password) and press Connect.
3. Press Continue on the pop-up warning.
4. Fix the invalid credentials and try to Connect again. The first time, it will connect successfully.
5. Now Disconnect and Reconnect, again using the valid credentials and the connect fails with "Neither the isolation level nor a strengthening of it is supported."
6. Subsequent connects and disconnects work but...
7. If something in the connection string is changed again (e.g. connect as another user) then the application crashes on trying to Connect.

With TD6.2 PTF1 (build 39136):
Similar to above but without the crash. After invalid credentials, the subsequent second connect with good credentials fails. If a different database or user is attempted, again, the second connect fails but the first and subsequent connects succeed. For each different valid user credential supplied after a failure, a database session is left hanging in the session list.

Apologies for the time it's taken to find a repro case. The above does point to there being remaining issues with the connection failure logic and these may be what is causing our module to crash or fail to connect from time to time, even though we're always providing correct credentials. It may be indicative of uninitialised variables or other memory corruption.

We now have test builds with both TD6.1 and TD6.2, so a fix for TD6.2 would also be fine for us as the latest release seems very stable otherwise. Looking forward to being able to migrate fully to TD6.2!

[Jean-Marc Gemperle]

Hi

With TD6.2 PTF1 (build 39136):
Similar to above but without the crash. After invalid credentials, the subsequent second connect with good credentials fails. If a different database or user is attempted, again, the second connect fails but the first and subsequent connects succeed. For each different valid user credential supplied after a failure, a database session is left hanging in the session list.

Yes seeing the same see attached video = > TD-20611

JM

[wardies]

Hi,

Is there any update on this issue?

We are coming to the end of some enhancements and will be looking at upgrading to TD6.2 or TD6.3 next.

I noticed that in the latest Unofficial TD6.2 Bugs spreadsheet from August it's showing TD-20611 as fixed in SP2, which doesn't seem to match the confirmed repro by JM above.

Thanks and looking forward to seeing some of you at the devcon in London.

[Ana Paula Bonani]

Hello,

TD-20611 got fixed in TD6.2 SP2 after Jean-Marc reported it in February. SP2 was released in May this year.

See you at the DevCon in London next week!

[wardies]

Hi Ana, JM,

Apologies for the delay. I can now confirm that the TD-20611 fix works for me too and no further issues to report with OLEDB.

Test VM:
Win 7 Enterprise SP1 x64
TD 6.2 SP3 UPD2 Runtime (UPD623R.2-45008)
ODAC 11.2 Release 5 (11.2.0.3.20)

Many thanks!
Marcel