Fails for SQL Server (new JDBC driver from Atlassian)

Question

Fails for SQL Server (new JDBC driver from Atlassian)

cosmocracy opened this issue 6 years ago · 14 comments

Atlassian introduced use of a new JDBC driver (the one created by Microsoft) and version 7.8.0 no longer includes the prior jTDS JDBC driver. The current docker definition needs to be revised to adjust for this.

New JDBC Driver Classname:
com.microsoft.sqlserver.jdbc.SQLServerDriver

New URL Scheme:
jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

See also:

Answer 1 · 2018-03-01T15:43:15.000Z

PR submitted for review; adjusts the driver class and JDBC URL scheme

Answer 2 · 2018-03-01T16:11:49.000Z

Thank you! Looks good!

Answer 3 · 2018-03-01T17:57:10.000Z

Still troubleshooting this some. The duplicate handling of the host/port/database (having them be in the required JIRA_DATABASE_URL as well as the component parts (e.g., JIRA_DATABASE_NAME) is throwing some of the shell scripting for a loop. Getting closer!

Answer 4 · 2018-03-01T18:00:08.000Z

Another crux of the issue is that the (new) JDBC driver URLs don't append the database name but rather use the databaseName= property in the URL. I may need to tweak/rework/bypass some of the intelligent things the script is trying to do consistently across drivers. No surprise that Microsoft wouldn't follow the de facto standard...

Answer 5 · 2018-03-01T18:13:21.000Z

To concretely document what's (not) working currently, when I launch the JIRA container using this:

docker run \
    -d \
    --name jira \
    --network *** \
    -v jiravolume:/var/atlassian/jira \
    -e "JIRA_PROXY_NAME=***" \
    -e "JIRA_PROXY_PORT=443" \
    -e "JIRA_PROXY_SCHEME=https" \
    -e "JIRA_CONTEXT_PATH=jira" \
    -e "JIRA_DATABASE_URL=sqlserver://MYSERVER:1433;databaseName=JIRA_Dev" \
    -e "JIRA_DB_USER=jira-app" \
    -e "JIRA_DB_PASSWORD=***" \
    -e "DOCKER_WAIT_HOST=MYSERVER" \
    -e "DOCKER_WAIT_PORT=1433" \
    -e "JVM_MINIMUM_MEMORY=512m" \
    -e "JVM_MAXIMUM_MEMORY=1g" \
    -v /etc/timezone:/etc/timezone:ro \
    -p 8080:8080 \
    jira-esk

When the container starts, I find this in the Atlassian log (/var/atlassian/jira/log/atlassian-jira.log):

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "JIRA_Dev/" requested by the login. The login failed. ClientConnectionId:22336fa1-c4cd-49f3-b26d-5cb029879e89
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
        at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)

Which makes sense given that the container scripting generated a malformed URL for my jdbc-datasource element (note the trailing slash, likely because it's currently treating the whole expression as part of the host_port_name, not recognizing that the URL I gave supplied properties, including the databaseName):

<?xml version="1.0" encoding="UTF-8"?>
<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mssql</database-type>
  <schema-name>dbo</schema-name>
  <jdbc-datasource>
    <url>jdbc:sqlserver://MYSERVER:1433;databaseName=JIRA_Dev/</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>jira-app</username>
    <password>***</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <validation-query>select version();</validation-query>
    <validation-query-timeout>3</validation-query-timeout>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

Answer 6 · 2018-03-01T18:16:50.000Z

Again, being non-standard, Microsoft didn't even follow the URL convention that the query portion of the URL starts with a ? and they instead jumped right in to appending additional values using abc=xyz;asdf=123 syntax instead of ?abc=xyz&asdf=123 as is standard with true URLs.

Answer 7 · 2018-03-01T18:19:09.000Z

This failing on Microsoft's part is devastating to the assumed simplicity of the scripting for this container. I'm inclined to leave most of the code "as is" (which basically makes all of the connection properties part of the host_port_name variable) and just make sure the final string assembly doesn't include that trailing /.

Answer 8 · 2018-03-01T18:33:10.000Z

Ok, when I use the given JIRA_DATABASE_URL without mangling (other than prefixing with jdbc:) things get better. Next problem is that the connection pool is failing with this error during startup:

2018-03-01 13:25:28,777 JIRA-Bootstrap ERROR      [NoModule] Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:sqlserver://MYSERVER:1433;databaseName=JIRA_Dev', driverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver', username='jira-app', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=20, minSize=20, initialSize=null, maxIdle=20, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery='select version();', minEvictableTimeMillis=60000, timeBetweenEvictionRunsMillis=300000, poolPreparedStatements=null, testOnBorrow=false, testOnReturn=null, testWhileIdle=true, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedOnBorrow=true, removeAbandonedOnMaintanance=null, removeAbandonedTimeout=300, validationQueryTimeout=3, defaultCatalog=null}}
java.sql.SQLException: Cannot create PoolableConnectionFactory ('version' is not a recognized built-in function name.)
        at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2291)
        at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2038)
        at org.apache.commons.dbcp2.BasicDataSource.setLogWriter(BasicDataSource.java:1625)
        at org.ofbiz.core.entity.transaction.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:101)

Which makes sense because the validation query shown is for PostgreSQL! This Atlassian document describes the validation queries to be used by database: https://confluence.atlassian.com/adminjiraserver071/surviving-connection-closures-802592191.html

Looks like we should dynamically rewrite this, too (another GitHub issue, perhaps) but for now I believe select 1 should work for everything (except Oracle which requires a table name (e.g., select 1 from dual))

Answer 9 · 2018-03-01T20:29:08.000Z

Hello, I accepted the last pull request in best hope that you committed tested code.

My build pipeline immediately pushes to Dockerhub, every pull-request will be published and pulled by other users.

Does this resolve this issue? Otherwise I can offer a development branch where we can resolve this issue.

Answer 10 · 2018-03-04T13:42:22.000Z

SQL driver reverted to old one. Any changes please on the new dev branch.

Answer 11 · 2018-03-06T14:40:49.000Z

Yes, @blacklabelops I believe the PR that I submitted resolved this issue. Did you revert it out?

Answer 12 · 2018-03-06T19:17:55.000Z

I don't see any build error in the CI failure, just that the build step was canceled during download of JIRA Core:

--2018-03-04 13:47:13--  https://www.atlassian.com/software/jira/downloads/binary/atlassian-jira-core-7.8.0-x64.bin
Resolving www.atlassian.com... 104.192.142.43, 104.192.142.44, 104.192.142.45
Connecting to www.atlassian.com|104.192.142.43|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://product-downloads.atlassian.com/software/jira/downloads/atlassian-jira-core-7.8.0-x64.bin [following]
--2018-03-04 13:47:13--  https://product-downloads.atlassian.com/software/jira/downloads/atlassian-jira-core-7.8.0-x64.bin
Resolving product-downloads.atlassian.com... 54.239.142.114, 2600:9000:2025:3c00:1f:ab86:b47:681, 2600:9000:2025:a400:1f:ab86:b47:681, ...
Connecting to product-downloads.atlassian.com|54.239.142.114|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 326402240 (311M) [application/octet-stream]
Saving to: '/tmp/jira.bin'

     0K .......... .......... .......... .......... ..........  0%  458K 11m36s
    50K .......... .......... .......... .......... ..........  0%  686K 9m40s
   100K .......... .......... .......... .......... ..........  0% 1.79M 7m25s
   150K .......... .......... .......... .......... ..........  0% 2.38M 6m6s
...
...
244300K .......... .......... .......... .......... .......... 76% 11.2M 3s
244350K .......... .......... .......... .......... .......... 76%  152M 3s
244400K .......... .......... .......... .......... .......... 76%  136M 3s
244450K .......... .......... .......... .......... .......... 76% 67.7M 3s
244500K .......... .......... .......... .........Step was canceled

Answer 13 · 2018-03-06T20:17:11.000Z

I changed the default branch from master to dev. You can now safely recommit the last two pull requests.

Answer 14 · 2018-03-06T20:19:58.000Z

Sounds good. Thanks for the clarification!