New troubles installing ODK Sync Endpoint on Ubuntu 18.04

After successfully installing ODK Sync Endpoint on Ubuntu 18.04 on a virtual machine internal to our network for a proof of concept, we have decided to move ahead with using ODK2 for our field data collection needs. Now I am attempting to install the software on a virtual machine outside our network firewall for easy access from the field. I have run into a problem twice starting from clean installs. The only difference was the second time I installed our SSL certificate in docker prior to installing ODK Sync Endpoint, but that made no difference. When I run “mvn clean install”, all seems to go well until the following:
[INFO] — maven-failsafe-plugin:2.22.1:integration-test (integration-test) @ mysql-test —
[INFO]
[INFO] -------------------------------------------------------
[INFO] T E S T S
[INFO] -------------------------------------------------------
[INFO] Running org.opendatakit.common.persistence.TaskLockTestIT
May 16, 2019 10:58:03 AM org.opendatakit.common.persistence.engine.mysql.DatastoreImpl setDataSource
INFO: Failed to load com.mysql.jdbc.GoogleDriver Exception: java.lang.ClassNotFoundException: com.mysql.jdbc.GoogleDriver
Entry Attempt 0 Thread 18
Entry Attempt 0 Thread 20
Entry Attempt 0 Thread 15
Entry Attempt 0 Thread 16
Entry Attempt 0 Thread 17
Entry Attempt 0 Thread 19
Entry Attempt 0 Thread 21
Entry Attempt 0 Thread 22
Thu May 16 10:58:03 CDT 2019 WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
May 16, 2019 10:58:03 AM org.opendatakit.common.persistence.engine.mysql.DatastoreImpl assertRelation
WARNING: Failure: _task_lock exception: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user ‘odk_unit’@‘172.20.0.1’ (using password: YES))
org.opendatakit.common.persistence.exception.ODKDatastoreException: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user ‘odk_unit’@‘172.20.0.1’ (using password: YES))
at org.opendatakit.common.persistence.engine.mysql.DatastoreImpl.assertRelation(DatastoreImpl.java:751)
at org.opendatakit.common.persistence.engine.mysql.TaskLockImpl$TaskLockTable.assertRelation(TaskLockImpl.java:521)
at org.opendatakit.common.persistence.engine.mysql.TaskLockImpl.obtainLock(TaskLockImpl.java:388)
at org.opendatakit.common.persistence.TaskLockTestIT$TaskLockThread.run(TaskLockTestIT.java:113)
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user ‘odk_unit’@‘172.20.0.1’ (using password: YES))
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:289)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377)
at org.opendatakit.common.persistence.engine.mysql.DatastoreImpl.assertRelation(DatastoreImpl.java:584)
… 3 more
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user ‘odk_unit’@‘172.20.0.1’ (using password: YES))
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2385)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2110)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:246)
… 5 more
Caused by: java.sql.SQLException: Access denied for user ‘odk_unit’@‘172.20.0.1’ (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1714)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1224)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2199)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2230)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2025)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:778)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:291)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2395)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2381)
… 8 more

The final part of the above message, timestamped 10:58:03, appears to repeat infinitely (twice I have killed it at a little over 30 minutes).

It looks like it is having trouble talking to the database.

Two common problems:

  1. The jdbc.properties are not correction
  2. The mysql connect jar must be installed for the system to communicate to the database.

Note: by default the sync-endpoint-swarm image is configured for postgres. To configure for mysql check out this Dockerfile which copies the mysql-connector dependency into the tomcat lib which allows it to communicate.

I hadn’t thought about the anomaly of MySQL errors when it is configured for postgres. I have not done anything special to request MySQL - I would prefer this server use postgres as I did with my proof-of-concept server. All I have done after installing docker and putting it in swarm mode was the 2 git clone commands and “mvn clean install” - this error happens about 15 minutes into the last step.

Then you are likely running the tests.

Try going into the submodule “sync-endpoint-docker-swarm” and run “mvn clean install” there so you only get the docker image needed for the swarm. Then run the sync-endpoint-default-setup to get your swarm running.

I’ve been running the commands from the documentation blindly without learning what maven is or how it works. Before you wrote this I had done a clean install and stopped just before “mvn clean install”. With it at that stage I tried running “mvn clean install” inside “sync-endpoint-docker-swarm”, which failed because it couldn’t find sync-endpoint-war. Then I read some more about maven, and edited sync-endpoint/pom.xml, removing the mysql-test module and the mysql dependency. With this I was able to build successfully and proceed through the rest of the commands. Thank you.

1 Like