MySQL SQLException "Unknown system variable 'storage_engine'" on install

Development
1

I am trying to set up OpenMRS on a new computer, and I just installed the latest MySQL 5.7.9 and pulled openmrs-core fresh from github. When I run ‘mvn jetty:run’ for the first time and go through the OpenMRS installer, when it gets to the database setup I get a connection error caused by use of an unknown system variable ‘storage_engine’. Apparently that system variable is now deprecated. Has anyone run into this? Is there an OpenMRS MySQL configuration I can change that will prevent the connection from using this variable?

Full stacktrace follows:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2395) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2316) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:834) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at org.openmrs.web.filter.initialization.InitializationFilter.executeStatement(InitializationFilter.java:1153) at org.openmrs.web.filter.initialization.InitializationFilter.access$200(InitializationFilter.java:82) at org.openmrs.web.filter.initialization.InitializationFilter$InitializationCompletion$1.run(InitializationFilter.java:1388) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Unknown system variable ‘storage_engine’ at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1842) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1764) at com.mysql.jdbc.ConnectionImpl.setSessionVariables(ConnectionImpl.java:5588) at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3563) at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2358) … 16 more

1 Like
2

Yes i have seen two other people face the same problem. In your runtime properties file, change the connection.url and replace “'storage_engine” with the new value. Then restart tomcat/jetty.

3

overwrite “storage_engine” with “default_storage_engine” within the Database connection string when installing openmrs, please document this if you can on JIRA including the resultant issues that you may go on fixing. I think it should be an issue with that version of mysql/mysql-server.

2 Likes
4

I had faced the same issue earlier trying to run OpenMRS with MySQL 5.6 +

There are two fixes to the above issue:

  1. Please do not use storage-engine because it is deprecated and probably explains the error. You should use default-storage-engine - Refer link
  2. Initial setup goes through without any errors with MySQL 5.6.
5

Are you suggesting that core should be updated to use default_storage_engine as the default? Or just that you want me to document the manual workaround in JIRA?

6

Yes core should use default_storage_engine

1 Like
7

By the way I gave up on the latest MySQL 5.7.9 and went back to 5.6, due to other issues such as liquibase SQL statements that no longer work due to recent MySQL changes. I ran into more than 1 update that threw ‘Error 1093 - Can’t specify target table for update in FROM clause’. I fixed one, ran into another, and had no idea how many there might be. There are apparently server settings that can fix those issues, but I didn’t pursue that. Looks like something else that needs to be researched and documented.

8

FYI I created https://issues.openmrs.org/browse/TRUNK-4800 for the ‘default_storage_engine’ issue and PR https://github.com/openmrs/openmrs-core/pull/1625 to address

9

Merged. Thanks Kris! :slight_smile:

10

@dkayiwa I ran into this issue when deploying openmrs platform 1.11.5 using mysql 5.7. Was the change above included in this platform version?

11

@whiscard it was merged for platform 2.0 but not back ported to 1.11.x

12

@dkayiwa - Noted thanks.