ODK Aggregate Tomcat Deploy Error: Context initialization failed

Jul 21, 2018 6:34:31 AM org.springframework.web.context.ContextLoader initWebApplicationContext
SEVERE: Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hierarchicalRoleRelationships' defined in ServletContext resource [/WEB-INF/applicationContext-security.xml]: Invocation of init method failed; nested exception is 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 (Unable to load authentication plugin 'caching_sha2_password'.)

What ODK tool and version are you using? And on what device and operating system version?
ODK Aggregate v1.6.0
Windows Server 2008 Operating System
Dell PowerEdge T420 Server Machine

Following jar files placed in "C:\Program Files\Apache Software Foundation\Tomcat 8.5\lib" folder:

Without Secured Server configuration

JAVA PATH settings as follows:
System environment path variable settings:
path="............;C:\Program Files\Java\jdk1.8.0_181\bin;C:\Program Files\Apache Software Foundation\Tomcat 8.5\bin;C:\Program Files\MySQL\MySQL Server 8.0\bin;......."

User Variables path settings:
JAVA_HOME="C:\Program Files\Java\jdk1.8.0_181"
CATALINA_HOME="C:\Program Files\Apache Software Foundation\Tomcat 8.5"

What steps can we take to reproduce the problem?
Install the following:

  1. MySQL Community Server 8
  2. JDK 1.8
  3. Tomcat 8.5
    then configuring the ODK Aggregate by running "ODK-Aggregate-v1.6.0-Windows.exe" and the final output file will be: "ODK_Aggregate.war" and "create_db_and_user.sql". Execute the .sql file in shell: Mysql>source "C:\Users\Administrator\Downloads\ODK Aggregate\create_db_and_user.sql"; then copy-paste (deploy) the other output file "ODK_Aggregate.war" in "C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps" folder and restart the tomcat and try to access it through "http://localhost:8080/ODKAggregate/"

HTTP Status 404 – Not Found
Type Status Report
Message /ODKAggregate/
Description The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.

"The error might be because of no database created using the .sql file generated by ODKAggregate.exe", and it is found to be true after investigating the MySQL database.

This was present in .sql file:

create database odkdb2018;
create user 'odkuser1'@'localhost' identified by 'password';
grant all on odkdb2018.* to 'odkuser1'@'localhost' identified by 'password';
flush privileges;

While executing manually the above queries in mysql root shell, the following error was displayed:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ident
ified by 'password'' at line 1

some problem with syntax: grant all on odkdb2018.* to 'odkuser1'@'localhost' identified by 'password';

The following command did the work to grant privileges:
GRANT ALL PRIVILEGES ON odkdb2018.* TO 'odkuser1'@'localhost';

After doing all the above steps still couldn't able to deploy in tomcat, same errror: "HTTP Status 404-"

All this circus is to configure the server at office premise and to sync the ODK survey forms through internet.

solution or any other alternative method suggestions are welcomed.

@Vivaswan, there are a lot of moving pieces here. The major thing seems to be with your DB setup. Can you try MySQL 5.7 instead of MySQL 8 and see if that works better?

The other thing that stands out to me as potential problems is that you have two MySQL connectors (mysql-connector-java-8.0.11.jar, mysql-connector-java-5.1.46.bin.jar). But that would only manifest while you were loading Aggregate, so I'd start with trying MySQL 5.7.

Thank you very much @yanokwa installing MySQL 5.7 did the work, now its working like a charm.

Glad I could help! When you get a chance, please introduce yourself here. I'd also encourage you to add a real picture as your avatar because it helps build community!