KNIME Oracle Database Connection Problem

[sami_elsabagh]

Dear KNIMER’s,

I am using KNIME since 3 years. I use also the database Connection to Oracle database without Problem. Now I get this error message:
ERROR Database Reader 0:496:330 Execute failed: Maximum number of retries to get a valid connection reached

Configuration did not Change, database Routing is ok. My KNIME Installation is up to date! I Need urgently your advice!!!

C:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar

Connection string
jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = IP-Adress)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = SERVICENAME)))

[clac2188]

I am having exactly the same issue - it is on Oracle 11.2 for me and began after latest update.

[sami_elsabagh]

Thanx, So as I c, you did return to knime 3.4

[clac2188]

yes. I should have said that in my first reply to you. I returned to 3.4 and the problem disappeared. Have you had to do the same?

[sami_elsabagh]

Well I figured also this out. I am not sure, if I reset completely to an older Version, I may get Troubles with other nodes. Maybe I have to do for the meantime a Workaround and separate the data acquisition and data Analysis. Anyway I am looking Forward to get Feeback from the developers if this is a programming bug or not and how to deal with that, and when is this going to be fixed. Working Database Connections are vital for analytical Platforms!

[thor]

Can you send us the relevant part from your knime.log, please? Especially when it comes to databases there are so many things that can go wrong and the log file contains more information than just the message.
However, I can confirm that there was a change in 3.5.3 in that direction that solved a problem with another database. It’s possible that it had some undesired side-effect on Oracle, though.

[sami_elsabagh]

Dear Thor,

I have the log of the console:

ERROR Database Reader 0:496:330 Execute failed: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = IPAdresse)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = SERVICENAME)))

Did you mean this?
Regards Sami

[clac2188]

I am not very good at understanding the log, but I think this may be relevant. Does it help?

2018-04-15 07:21:00,512 : DEBUG : ModalContext : DatabaseConnectionSettings : Database Reader : 0:1039 : Invalid or closed connection found. Retry counter: 34. Retry to get valid connection for key: ConnectionKey [db-user=planning, db-name=jdbc:oracle:thin:@//oradwhsp:1535/dwhsp, wf-user=cjohnso2]
2018-04-15 07:21:00,512 : DEBUG : ModalContext : NodeContext : Database Reader : 0:1039 : Workflow user found: cjohnso2
2018-04-15 07:21:00,515 : DEBUG : ModalContext : RegisteredDriversConnectionFactory : Database Reader : 0:1039 : Invalid connection found in cache with key: ConnectionKey [db-user=planning, db-name=jdbc:oracle:thin:@//oradwhsp:1535/dwhsp, wf-user=cjohnso2]
2018-04-15 07:21:00,516 : DEBUG : ModalContext : RegisteredDriversConnectionFactory : Database Reader : 0:1039 : Closing connection with key: ConnectionKey [db-user=planning, db-name=jdbc:oracle:thin:@//oradwhsp:1535/dwhsp, wf-user=cjohnso2]
2018-04-15 07:21:00,523 : DEBUG : ModalContext : RegisteredDriversConnectionFactory : Database Reader : 0:1039 : Removing closed connection from cache with key: ConnectionKey [db-user=planning, db-name=jdbc:oracle:thin:@//oradwhsp:1535/dwhsp, wf-user=cjohnso2]
2018-04-15 07:21:00,524 : DEBUG : ModalContext : DatabaseDriverLoader : Database Reader : 0:1039 : Database driver retrieved from user defined drivers: oracle.jdbc.driver.OracleDriverDriver info: Driver class name: oracle.jdbc.driver.OracleDriver major version: 1 minor version: 0 jdbc compliant: true
2018-04-15 07:21:00,524 : DEBUG : pool-4-thread-1 : RegisteredDriversConnectionFactory : Database Reader : 0:1039 : Opening database connection to “jdbc:oracle:thin:@//oradwhsp:1535/dwhsp”…

[clac2188]

if it turns out to be a fault with 3.5.3, will it be possible to re install 3.5.2? I have reinstalled 3.4, which is missing some functionality that I like.

[clac2188]

I have found 3.5.2 on the website. I have downloaded and installed. I do not have the problem with it. My problem is definitely isolated to 3.5.3

[thor]

Can you do a fresh restart, then only execute the Database Reader and post the contents of the log file since the restart? At the beginning there is more information about how the nodes determine whether the connection is still alive, for example.

[clac2188]

updated to 3.5.3 again. Created workflow with only a Oracle database reader and got the same error as before.

attached is the log file

knime_log_20180417.txt (370.2 KB)

[sami_elsabagh]

Hi together, I have been always updating KNIME whenever I started the Platform, and never let updates pending. The release 3.5.3 is dated 4th April. My last working Oracle query was in 5th March, then after being out of Office restarted yesterday - of Course with update - then Oracle queries didn’t work.

[beginner]

I’m on 3.5.3 and not having any issue with oracle connection.

The issue is most likely your driver file. i’m using ojdbc6.jar and this also works with connection to oracle 10. ojdbc14 tells by the version number it was made for JDK 1.4 (or 4 by new versioning) so it’s very outdated and probably causes this issue due to Java / JVM incompatibility. Update the driver and it will almost certainly work again.

[sami_elsabagh]

Hey, it works! GREAT JOB! THANX alot! Very important - although I think already known - after configuring the ojdbc6.jar Driver into the preferences the platform must be restarted for the Change to take place. Maybe this is helpful that KNIME Team may place/publish such Information.

[thor]

This also explain why we didn’t see any problems in our test. We are also using the ojdbc6.jar driver and that indeed works without problems.
The problems seems to be that the old driver doesn’t understand “SELECT 1” which we use to check whether the connection is still valid.

[clac2188]

that worked for me too

thanks much for the help!