appsensor-ui cannot connect to database.

[tghosth]

I have followed the instructions here to setup the demo of AppSensor.

I have installed the following MySQL components.

The server is set up with legacy authentication (just in case the newer one would cause a problem). Otherwise it is standard configuration and I set it up using the V1_Initial_Schema_Creation.sql script as per the demo instructions. I disabled SSL in case this would cause a problem (I tried with and without and got the same error described later).

appsensor-ui runs (using the parameters specified in the demo instructions) and I can get to the login screen. (Note that I had to make the change described in PR #86 in order to get the appsensor-ui application to run.) However, both during load and when I try and login I see a long exception in the console described as:

2018-05-14 08:47:01.564 ERROR 2600 --- [nio-8084-exec-6] w.a.UsernamePasswordAuthenticationFilter : An internal error occurred while trying to authenticate the user.

org.springframework.security.authentication.InternalAuthenticationServiceException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
        at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:110)
...
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
...
etc, etc.

I tried explicitly seting the port in the mysql connection string but that also did not help.

Do you have any ideas what the problem could be?

[jtmelton]

@tghosth I genuinely don't have a good answer here. The project is not doing anything special at all when it comes to the DB connection. I think what I would try now is connecting to the DB using the standard mysql client, but using the username and password here: https://github.com/jtmelton/appsensor/blob/master/appsensor-ui/src/main/resources/db/scripts/V1_Initial_Schema_Creation.sql#L6

This is a new issue I've not heard about before, so let me know what happens and I'll do what I can to help.

[tghosth]

Fair one, I can definitely connect with those creds with the mysql workbench so I presume there is some brain-meltingly specific reason why it isn't working for me but I just wanted to check in case it was something you have seen before. I will continue fiddling and see what I can do. Thanks anyway for your help :)

[tghosth]

Ok so the frustratingly pedestrian way I managed to fix this was to uninstall MySQL 8.x and install MySQL 5.7.x instead and then everything just started working. I raised a PR (#88 ) to note this in the demo instructions in case anyone gets caught with a similar issue.

[jtmelton]

Ok, thanks @tghosth. Appreciate all the hard work.