Description
I'm currently working with a Spring Boot 3.3.x application that connects to an Oracle Database cluster. I have set up two data sources and everything works fine when using the TNS format, but I'm encountering issues when using the EZConnect format — both use a secondary host, read: data guard.
I'm using this artifact: com.oracle.database.spring:oracle-spring-boot-starter-ucp:23.4.0 to bring all the required dependencies for Oracle UCP and the driver.
I'm getting an Invalid number format for port number error if I use jdbc:oracle:thin:@tcp://host1,host2:1521/service_name; alternatively, if I use jdbc:oracle:thin:@host1,host2:1521/service_name, the error is Invalid connection string format, a valid format is: "host:port:sid".
The reason I think this is on the Spring Boot side is because I have another application (same versions for everything) that uses only one datasource, and everything works as expected when switched to the EZConnect format — using as well the data guard.
Additional Context
The datasources configuration is done in this way:
spring:
datasource: # password, url, and username are provided at runtime
primary:
type: oracle.ucp.jdbc.PoolDataDourceImpl
secondary:
type: oracle.ucp.jdbc.PoolDataDourceImpl
@Configuration
public class Config {
@Bean
@ConfigurationProperties("spring.datasource.primary")
public DataSourceProperties dsp1() {
return new DataSourceProperties();
}
@Primary
@Bean("ds1")
public DataSource ds1() {
return dsp1().initializeDataSourceBuilder().build();
}
@Bean
@ConfigurationProperties("spring.datasource.secondary")
public DataSourceProperties dsp2() {
return new DataSourceProperties();
}
@Bean("ds2")
public DataSource ds2() {
return dsp2().initializeDataSourceBuilder().build();
}
}
Comment From: philwebb
Can you please provide the complete stacktraces for the errors. Are you providing the password, url, and username properties in YAML files?
Comment From: x80486
It would take me quite some time to type those stacktraces because the applications are in a different machine where I'm currently writing. Nevertheless, I would prefer to avoid that because it's work-related stuff and there is some real hysteria here around sharing anything.
If you really need them, I can try to obfuscate some of the class names — the typing will still be a real deal :sweat_smile:
While I was testing, I was able to replicate this by using the exact same host in the same JDBC URL for both: host1 and host2, though it only happens when two datasources are configured.
The values for password, url, and username are provided using environment variables: SPRING_DATASOURCE_PRIMARY_* and SPRING_DATASOURCE_SECONDARY_* — but I'm able to replicate the issue when declaring/providing the keys/values in the YAML file directly.
Comment From: philwebb
While I was testing, I was able to replicate this by using the exact same host in the same JDBC URL for both: host1 and host2, though it only happens when two datasources are configured.
Are you able to share the project that replicates the issue?
Comment From: wilkinsona
Failing that, @x80486, you could try diagnosing the problem yourself by placing an entry breakpoint on oracle.net.resolver.AddrResolution.resolveSimple(String) and comparing what's passed into the method to what you expect based on the JDBC URL that you're using. For example, if I deliberately misconfigure the URL (jdbc:oracle:thin:@tcp://127.0.0.1,127.0.0.1:15,21/service_name) I can see that tcp://127.0.0.1,127.0.0.1:15,21/service_name is passed into that method and it throws an exception:
java.sql.SQLException: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=xJqhRh5qQcmE006MytEwyA==)
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:498)
at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:175)
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:822)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1855)
at oracle.ucp.jdbc.PoolDataSourceImpl.access$300(PoolDataSourceImpl.java:224)
at oracle.ucp.jdbc.PoolDataSourceImpl$3.build(PoolDataSourceImpl.java:4050)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1822)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1778)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1764)
at com.example.gh_42947.Gh42947Application.lambda$0(Gh42947Application.java:16)
at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
at com.example.gh_42947.Gh42947Application.main(Gh42947Application.java:15)
Caused by: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=xJqhRh5qQcmE006MytEwyA==)
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:378)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:68)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:91)
at oracle.ucp.jdbc.oracle.OracleDriverConnectionFactoryAdapter.createConnection(OracleDriverConnectionFactoryAdapter.java:125)
at oracle.ucp.common.Database.createPooledConnection(Database.java:292)
at oracle.ucp.common.Topology.start(Topology.java:282)
at oracle.ucp.common.Core.start(Core.java:2692)
at oracle.ucp.common.UniversalConnectionPoolBase.start(UniversalConnectionPoolBase.java:738)
at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.start(OracleJDBCConnectionPool.java:133)
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:818)
... 9 more
Caused by: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=xJqhRh5qQcmE006MytEwyA==)
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:378)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:68)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:91)
at oracle.ucp.jdbc.DriverConnectionFactoryAdapter.createConnection(DriverConnectionFactoryAdapter.java:138)
at oracle.ucp.jdbc.oracle.OracleDriverConnectionFactoryAdapter.createConnection(OracleDriverConnectionFactoryAdapter.java:93)
... 15 more
Caused by: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=xJqhRh5qQcmE006MytEwyA==)
at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:902)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:707)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1094)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:89)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:732)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:648)
at oracle.ucp.jdbc.DriverConnectionFactoryAdapter.createConnection(DriverConnectionFactoryAdapter.java:132)
... 16 more
Caused by: oracle.net.ns.NetException: Invalid number format for port number (CONNECTION_ID=xJqhRh5qQcmE006MytEwyA==)
at oracle.net.resolver.AddrResolution.resolveSimple(AddrResolution.java:885)
at oracle.net.resolver.AddrResolution.resolveTNSAddress(AddrResolution.java:718)
at oracle.net.resolver.AddrResolution.initConnStrategy(AddrResolution.java:540)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:578)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:964)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:350)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:2627)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:666)
... 21 more
java.sql.SQLException: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=RvOC0UepS12x5tYzOBTPOw==)
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:498)
at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:175)
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:822)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1855)
at oracle.ucp.jdbc.PoolDataSourceImpl.access$300(PoolDataSourceImpl.java:224)
at oracle.ucp.jdbc.PoolDataSourceImpl$3.build(PoolDataSourceImpl.java:4050)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1822)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1778)
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1764)
at com.example.gh_42947.Gh42947Application.lambda$0(Gh42947Application.java:16)
at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
at com.example.gh_42947.Gh42947Application.main(Gh42947Application.java:15)
Caused by: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=RvOC0UepS12x5tYzOBTPOw==)
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:378)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:68)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:91)
at oracle.ucp.jdbc.oracle.OracleDriverConnectionFactoryAdapter.createConnection(OracleDriverConnectionFactoryAdapter.java:125)
at oracle.ucp.common.Database.createPooledConnection(Database.java:292)
at oracle.ucp.common.Topology.start(Topology.java:282)
at oracle.ucp.common.Core.start(Core.java:2692)
at oracle.ucp.common.UniversalConnectionPoolBase.start(UniversalConnectionPoolBase.java:738)
at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.start(OracleJDBCConnectionPool.java:133)
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:818)
... 9 more
Caused by: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=RvOC0UepS12x5tYzOBTPOw==)
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:378)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:68)
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:91)
at oracle.ucp.jdbc.DriverConnectionFactoryAdapter.createConnection(DriverConnectionFactoryAdapter.java:138)
at oracle.ucp.jdbc.oracle.OracleDriverConnectionFactoryAdapter.createConnection(OracleDriverConnectionFactoryAdapter.java:93)
... 15 more
Caused by: java.sql.SQLRecoverableException: IO Error: Invalid number format for port number (CONNECTION_ID=RvOC0UepS12x5tYzOBTPOw==)
at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:902)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:707)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1094)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:89)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:732)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:648)
at oracle.ucp.jdbc.DriverConnectionFactoryAdapter.createConnection(DriverConnectionFactoryAdapter.java:132)
... 16 more
Caused by: oracle.net.ns.NetException: Invalid number format for port number (CONNECTION_ID=RvOC0UepS12x5tYzOBTPOw==)
at oracle.net.resolver.AddrResolution.resolveSimple(AddrResolution.java:885)
at oracle.net.resolver.AddrResolution.resolveTNSAddress(AddrResolution.java:718)
at oracle.net.resolver.AddrResolution.initConnStrategy(AddrResolution.java:540)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:578)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:964)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:350)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:2627)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:666)
... 21 more
Comment From: x80486
I was trying a small application with gvenzl/oracle-free:23.5-slim-faststart, but it doesn't fail. Will see if I can debug the application and gather some more info — I'll post the findings if I finally see something fishy.
Thanks for the help!