Failing to connect to MySQL database

[emiliom]

sos-injector-db run with -Dmock on the NANOOS/NVS MySQL database resulted in a connection failure ("unable to open a test connection to the given database"; "invalid database address: jdbc:mysql://hostname:3306/dbname"). I'm using the jdbc.url pattern Shane provided. Also, I have no problems connecting to the database using the mysql shell client from the same server, using the same host, dbname and user & password.

What could be going wrong? Here are some pieces of the error message (I've hidden the host and db names):

'''
2014-03-17 12:35:16,322 ERROR [main] - Sos injection failed
com.axiomalaska.sos.exception.StationCreationException: Error creating station
at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:230)
at com.axiomalaska.sos.SosInjector.update(SosInjector.java:189)
at com.axiomalaska.sos.injector.db.DatabaseSosInjector.main(DatabaseSosInjector.java:39)
Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://hostname:3306/dbname, username = username.

Original Exception: ------
java.sql.SQLException: invalid database address: jdbc:mysql://hostname:3306/dbname

[srstsavage]

Can you post the url you're using with the sensitive info (host/username) replaced with other text instead of omitted? It should look something like:

jdbc:mysql://localhost:3306/sos

Which corresponds to:

jdbc:mysql://host:port/databasename

[emiliom]

jdbc.url=jdbc:mysql://ipaddress:3306/dbname
jdbc.username=uname
jdbc.password=password
(the MySQL database is located on a different server)
Thanks.

[srstsavage]

Ok, I've verified this problem. The connection pool is trying to connect to MySQL using the SQLite driver instead of determining the driver from the URL. I'll look into it, shouldn't be too difficult to fix.

[emiliom]

Great, thanks. I'll sit tight.

[srstsavage]

Ok, so there were at least two problems here, one mine and one yours.

I include multiple database drivers with sos-injector-db so that multiple platforms can be connected to. Java's DriverManager tries all of the available drivers to connect, and if it's not successful displays the error message from the first attempted driver. I know, kinda dumb. In this case, it was trying all available drivers (including MySQL) but failing on all, and showing the error message from the first (SQLite). I augmented the sos-injector-db code to remove any drivers that don't match the provided connection url, so you should now get a meaningful error message if it can't connect.

However, that means that there's something wrong with your connection, possibly that your user isn't authorized (that's the problem I had). I had to manually grant access to a database for a user, which is a MySQL idiosyncrasy which I wasn't expecting:

http://stackoverflow.com/a/11225588/193435

[srstsavage]

Oh, and an updated binary distribution with this fix is available here:

https://github.com/ioos/sos-injector-db/releases/tag/v1.0-RC3

[emiliom]

Thanks! But like I said, in my setup, the user is authorized to connect to that database, from the server where I'm running sos-injector-db. I've tested that easily by connecting with the mysql shell client with the same host-db-user on the same server where I'm running sos-injector-db.

I'll give your new jar a try in a bit to see what happens, and see if there's a more helpful error message.

[emiliom]

sos-injector-db RC3 still fails, but the message now seems more useful: (Caused by: java.sql.SQLException: No suitable driver. The MySQL driver is not installed? See the command and error messages below:

~/52nsos$ java -Dmock -jar sos-injector-db.jar config.properties
2014-03-18 14:05:20,869  INFO [main] - Mock SosInjector initialized
2014-03-18 14:05:20,870  INFO [main] - Updating mock-database-sos-injector
2014-03-18 14:05:20,900 ERROR [main] - Sos injection failed
com.axiomalaska.sos.exception.StationCreationException: Error creating station
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:233)
    at com.axiomalaska.sos.SosInjector.update(SosInjector.java:189)
    at com.axiomalaska.sos.injector.db.DatabaseSosInjector.main(DatabaseSosInjector.java:40)
Caused by: java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(Unknown Source)
    at com.axiomalaska.sos.injector.db.DatabaseConnectionHelper.getConnectionPool(DatabaseConnectionHelper.java:24)
    at com.axiomalaska.sos.injector.db.DatabaseConnectionHelper.getConnection(DatabaseConnectionHelper.java:48)
    at com.axiomalaska.sos.injector.db.DatabaseStationRetriever.getStations(DatabaseStationRetriever.java:71)
    ... 2 more

[srstsavage]

Very strange, I successfully connected to a MySQL database here. Which version of MySQL are you running?

[emiliom]

When I connect with the mysql client, I get this mysql server version:

Server version: 5.1.73-0ubuntu0.10.04.1-log (Ubuntu)

Seems pretty normal. We've never had issues connecting with it from
multiple servers, languages (PHP, Python, and probably Java, etc).

On Tue, Mar 18, 2014 at 2:25 PM, Shane StClair notifications@github.comwrote:

Very strange, I successfully connected to a MySQL database here. Which
version of MySQL are you running?

Reply to this email directly or view it on GitHubhttps://github.com//issues/3#issuecomment-37990573
.

[emiliom]

I'm not a Java programmer at all, and don't know much about jars. But it seems curious that under its "org" directory, there are subdirectories for postgresql and sqlite, but not for mysql. I have clue if that's where sos-injector-db looks for a mysql driver, but there you have it. Cheers,

[srstsavage]

Ok, think I got it this time. When the all inclusive jar was being compiled the database drivers were overwriting each other's service registry file which alerts the connection manager to their presence. Added a setting to merge these files instead of overwriting (see issue #4), making all drivers available. New binary release available with this fix at:

https://github.com/ioos/sos-injector-db/releases/tag/v1.0-RC4

Thanks for reporting and testing this out!

[emiliom]

Great, thanks. I've tested RC4, and can report that it got past database connectivity errors, to fail at data mapping and validation errors (ie, my problem). Thanks! Now go to bed :)