Fails for SQL Server (new JDBC driver from Atlassian)

[cosmocracy]

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:

• https://confluence.atlassian.com/jirakb/updating-jdbc-url-for-microsoft-sql-server-934718842.html
• https://confluence.atlassian.com/jirakb/using-integrated-security-with-mssql-for-jira-939494383.html

[cosmocracy]

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

[blacklabelops]

Thank you! Looks good!

[cosmocracy]

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!

[cosmocracy]

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...

[cosmocracy]

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>

[cosmocracy]

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.

[cosmocracy]

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 /.

[cosmocracy]

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))

[blacklabelops]

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.

[blacklabelops]

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