Recent change of JDBC URL splitting delimiter from , to ; breaks connectivity to MSSQL

[dimitri-furman]

#1233 discusses a change of URL splitting delimiter from , to ; to allow connections to multiple shards. A comment from @busbey mentions that semicolons aren't present in JDBC URLs. That is not correct for MSSQL JDBC driver, which uses semicolons to delimit connection string properties. Doc reference: https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url.

Thus, it is no longer possible to specify any connection properties in the URL, including a SQL Server database name to connect to. Only connections to the master database are possible, but master is a system database that should not be used for any user workload. This also makes it impossible to use YCSB against Azure SQL where specifying database name in the connection URL is mandatory.

Proposal: add support for quoting the db.url property. If quoted, any semicolon within the URL string will be treated as part of the string, not as a shard delimiter. Example of proposed syntax for two shards:

db.url="jdbc:sqlserver://server-name.database.windows.net:1433;databaseName=ycsb1";"jdbc:sqlserver://server-name.database.windows.net:1433;databaseName=ycsb2"

Currently, when URL is
db.url=jdbc:sqlserver://server-name.database.windows.net:1433;databaseName=ycsb

connection fails as follows:

YCSB Client 0.17.0

Loading workload...
Starting test.
Adding shard node URL: jdbc:sqlserver://server-name.database.windows.net:1433
Adding shard node URL: databaseName=ycsb
Error in database operation: java.sql.SQLException: No suitable driver found for databaseName=ycsb
site.ycsb.DBException: java.sql.SQLException: No suitable driver found for databaseName=ycsb

[busbey]

that's unfortunate. We should step back and look at why we're splitting the passed string in the first place and see if there's a different approach we can take to avoid continuing to play whack-a-mole with JDBC URL contents.

[joshelser]

This one also broke Apache Phoenix's JDBC (which has extensive semi-colon use). I like Dimitri's suggestion to use quoting to avoid the problematic parsing. I'll see if I can find some time to look at this.

[LucasZhu97]

As well as H2 database which supports JDBC and uses semicolons extensively.

[RudraNirvan]

@busbey any plan for fixing this?

[busbey]

I don't think anyone has put together a PR. that would be the next step.

[olavloite]

@busbey I've added a PR for this here: #1677