I’m deploying a new server, core 2.3.1, using the initialsetup wizard. I have a mysql server running in a remote machine and would like to connect to that server, setting up the openmrs db. Unfortunately, the wizard does not create a connection to the db. I’ve already tested the connection using mysql, on the command line, and all is well. I"m using the “Advanced Install” in the wizard. Maybe I"m not providing the correct arguments on step 1 of the wizard. I’ve provided “jdbc:mysql://:3306/openmrs?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8” The provided port is correct. I provided the db name (openmrs) along with my credentials (server user name/password) so it would create the db. On step 2 of the wizard I provided the login credentials, once more. Unfortunately, I still get the connection issue. Any help is greatly appreciated. Barry
From your logs, you are using MySQL 8 which is not supported by core 2.3.1
You can either use core 2.4.0-alpha.2, which supports MySQL 8, or switch to MySQL 5.7
[quote=“levine, post:1, topic:30833”] jdbc:mysql://:3306/openmrs?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8 quote] Hi Barry, Not sure if you have left out the server IP for privacy in a public post, but it’s missing from this. “jdbc:mysql://:3306/openmrs?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8”
Seeing you can connect via command line it looks like firewall and database security settings may be ok.
Many thanks - I’ll change the server version and check again…
And if you want the reference application module versions that run on core 2.4.0-alpha.2, here they are: https://ci.openmrs.org/build/result/artifactUrlRedirect.action?planKey=REFAPP-OMODDISTRO&buildNumber=10545&artifactName=referenceapplication-addons-snapshot
The server was changed to 5.7 I started the deploy process with a remote db using root for mysql. It successfully created the user/database but just hung at the last stage “Update the Database”, with 22% completed. I pasted the end of the catalina.out at pastebin (I’m not sure why pastebin complains about my post but it’s accessible??): https://pastebin.com/ucWbYqqF
Then, I attempted another option. I installed the openmrs db on the remote mysql. Then I pointed to the remote mysql server using mysql root again. This time it created the openmrs_user as is evident here: – user is created but fails with communication problem:
mysql> show grants for 'openmrs_user'@'%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for openmrs_user@% |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'openmrs_user'@'%' IDENTIFIED BY PASSWORD '*67BB785C37CEA79DC4B4C5420CDE0989423C5BAB' |
| GRANT ALL PRIVILEGES ON `openmrs`.* TO 'openmrs_user'@'%' |
| Database connection | jdbc:mysql://2.tcp.ngrok.io:17998/openmrs?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8 |
|---|---|
| Database name | openmrs |
| Create database | No |
| Create database user account | Yes |
| Create tables | No |
| Add demo data | No |
Here’s the pastebin with dump of catalina.out:
You do not need to manually grant any permissions to openmrs_user because the set up process should take care of that. Do you think you can share screenshots of the first three setup wizard steps/pages?
Before and after I attempted the install I logged into the mysql server as root to ensure it was running.
Here’s the relevant catalina.out dump: https://pastebin.com/TkX0DXWz
Prior to starting the initialsetup I installed the openmrs db on the remote mysql server. Following shows all of my steps:
The full db connection is: jdbc:mysql://2.tcp.ngrok.io:17998/@DBNAME@?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8
All implementation id fields are blank User was created, but it didn’t “Update the Database”, then I saw the following
For the second step of “Do you need OpenMRS to automatically create the database”, select YES.
I forgot to mention I"m using mysql 5.6.36
The second step is a bit different than what you mentioned - it indicates “Do you need OpenMRS to automatically create the tables for your current database openmrs?” However, I already have the database and all tables created. Should I indicate YES to create the tables again? In any case, I provided YES for creating the tables to get the following:
| Create database | No |
|---|---|
| Create database user account | Yes |
| Create tables | Yes |
I still get "Caused by: java.sql.SQLException: Access denied for user ‘openmrs_user’@‘localhost’ (using password: YES) "
All i want is Create database to be YES. Therefore, run the setup wizard in such a way that it creates the database instead of using an existing one.
I just tried the following, which I believe I tried before:
I dropped openmrs_user, dropped the openmrs db then provided the following db remote connection -
jdbc:mysql://2.tcp.ngrok.io:17998/@DBNAME@?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8
The last step of the wizard has:
| Database connection | jdbc:mysql://2.tcp.ngrok.io:17998/@DBNAME@?autoReconnect=true&sessionVariables=default_storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8 |
|---|---|
| Database name | openmrs |
| Create database | Yes |
| Create database user account | Yes |
| Create tables | Yes |
| Add demo data | No |
The db was created, as was the user, openmrs_usr; then, the same issue reoccured - it couldn’t connect to the db server (as was posted previously on pastebin). It’s not able to connect with the user that was created (openmrs_user):
Caused by: java.sql.SQLException: Access denied for user ‘openmrs_user’@‘localhost’ (using password: YES)
I tried logging in to mysql with the user openmrs_usr, without providing a password, and was successful in doing so both remotely and locally.
Interesting!
How about doing all the above, but let the setup wizard use the root account instead of creating a new user?
I set up a remote database server in Amazon. Then I ran the openmrs installation pointing to that db and it worked fine, very fast. I’m thinking the issue was with the target computer hosting the mysql server, which caused timeouts. The current path is we’ll set up OpenMRS, along with the db on the same machine at the remote location using Ubuntu 16.04, tomcat, etc. I expect this will work fine. @dkayiwa, thanks very much for your attention to these issues.