Attribute `db.name` is not handled correctly for Derby and Oracle tnsnames

[obs-gh-abhishekrao]

Describe the bug

Instead of producing the actual database name,

• In the derby case, we see an absolute directory path, for example: /usr/ibm/pep/was9/ibm/websphere/appserver/profiles/<REDACTED>/databases/ejbtimers/<HOST_REDACTED>/ejbtimerdb
• In the Oracle tnsnames case, we see the connection string show up as db.name, for example: ( description = (connect_timeout=90)(retry_count=20)(retry_delay=3) (transport_connect_timeout=3000) (address_list = (load_balance = on) (failover = on) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) ) (connect_data = (server = dedicated) (service_name = <REDACTED>) ) )

Steps to reproduce

Instrument a Java app that connects to Derby in directory mode

• Use opentelemetry javaagent version 2.6.0
• SDK version 1.4.0
• Instrumentation library io.opentelemetry.jdbc should be 2.6.0-alpha

Instrument a Java app that connects to Oracle database using tnsnames

• Use opentelemetry javaagent version 2.6.0
• SDK version 1.4.0
• Instrumentation library io.opentelemetry.jdbc should be 2.6.0-alpha

Expected behavior

Apache Skywalking Java agent holds a good reference implementation for these two systems

• Derby - https://github.com/apache/skywalking-java/blob/7dd7914765c06f64733ccfd214645f70bad8a583/apm-sniffer/apm-sdk-plugin/jdbc-commons/src/main/java/org/apache/skywalking/apm/plugin/jdbc/connectionurl/parser/DerbyURLParser.java
• Oracle TNS - https://github.com/apache/skywalking-java/blob/6c4af4e7d10dcda5b7c16de13356ef8b5cdb7f40/apm-sniffer/apm-sdk-plugin/jdbc-commons/src/main/java/org/apache/skywalking/apm/plugin/jdbc/connectionurl/parser/OracleURLParser.java#L114

Which if I read correctly, would result the example case I mentioned to have these values for db.name:

• Derby - ejbtimerdb
• Oracle tnsnames - Redacted, but the value for the service_name connection attribute would be set as db.name

Actual behavior

• Derby - db.name is /usr/ibm/pep/was9/ibm/websphere/appserver/profiles/<REDACTED>/databases/ejbtimers/<HOST_REDACTED>/ejbtimerdb
• Oracle tnsnames - db.name is ( description = (connect_timeout=90)(retry_count=20)(retry_delay=3) (transport_connect_timeout=3000) (address_list = (load_balance = on) (failover = on) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) (address = (protocol = tcp)(host = <REDACTED>)(port = 1521)) ) (connect_data = (server = dedicated) (service_name = <REDACTED>) ) )

Javaagent or library instrumentation version

2.6.0

Environment

Derby case

JDK: IBM Corporation IBM J9 VM 2.9
OS: Linux 3.10.0-1160.123.1.el7.x86_64

Oracle case

JDK: Oracle Corporation Java HotSpot(TM) 64-Bit Server VM 17.0.12+8-LTS-286
OS: Linux 4.18.0-553.16.1.el8_10.x86_64

Additional context

No response

[trask]

hi @obs-gh-abhishekrao!

I think a PR would be welcome for this, check out JdbcConnectionUrlParser.java and JdbcConnectionUrlParserTest.java

[jaydeluca]

@obs-gh-abhishekrao i have addressed the derby database name, but was unable to reproduce the issue with the oracle tnsnames. Would you be able to provide a small app that reproduces the issue or provide more information?

[obs-gh-abhishekrao]

@jaydeluca Thank you for the fix! I was able to look into the files Trask shared and have to say I'm sort of confused myself. The logic there seems to work for Oracle descriptors and I tried plugging different values (including multi-line formatted descriptors) into the corresponding test case and it works there just as I would expect.
Right now it's a guess for me since this was from a customer's environment, but it feels possible that the jdbcUrl somehow got classified as ORACLE_CONNECT_INFO, because:

1. db.connection_string is set to oracle:thin:, whereas the actual logic should have landed on oracle:thin://<somehost>:<someport>. This indicates builder.host and builder.port were somehow set to null, which explicitly happens in the ORACLE_CONNECT_INFO case. Subsequently, this also indicates the shortUrl logic did not go through the flow of appending //<somehost>:<someport>.
2. db.name is set to (description).. (not sure but maybe the leading whitespace is significant here and could indicate the source data had something going on at the start of the string)