Unknown system variable 'default_storage_engine' when attempting to connect to MySQL 5.1.73 and JIRA 7.3

[Mike M]

Hi,

Attempting a fresh install of MySQL 5.1.73 and JIRA 7.3. Both are running on their own instance of Redhat Linux 6.7

I have installed and setup MySQL and JIRA as per the JIRA guide, placing the mysql-connector-java-5.1.41-bin.jar in atlassian/jira/lib

My my.cnf looks like below:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

default-storage-engine=InnoDB

max_allowed_packet=256M

innodb_log_file_size=256M

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 And the JIRA error I get when going through setup is (JIRA forums failed to let me upload an image):

Error connecting to database

Unknown system variable 'default_storage_engine'

I've tried replacing the - with _ but it made no difference. I don't have a dbconfig.xml yet as I haven't managed to get through the JIRA setup.

My mysql.properties file contains the following line:

databaseUrl=jdbc:mysql://localhost/jira?autoReconnect=true&characterEncoding=utf8&useUnicode=true&sessionVariables=storage_engine=InnoDB

which I've tried removing the storage_engine variable from the URL and also renaming to default_storage_engine but this both changes made no difference.

The catalina.out log file has this as the last error:

2017-03-31 05:32:26,408 http-nio-8080-exec-8 ERROR anonymous 332x6x1 6ho9xv xx.xx.xx.xxx/secure/SetupDatabase!connectionCheck.jspa [c.a.config.bootstrap.DefaultAtlassianBootst$

java.sql.SQLException: Unknown system variable 'default_storage_engine'

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497)

        at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)

        at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)

        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)

        at com.mysql.jdbc.ConnectionImpl.setSessionVariables(ConnectionImpl.java:4999)

        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3212)

        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2249)

        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035)

        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)

        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)

        ... 3 filtered

        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)

        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)

        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)

        at java.sql.DriverManager.getConnection(DriverManager.java:664)

        at java.sql.DriverManager.getConnection(DriverManager.java:247)

        at com.atlassian.config.bootstrap.DefaultAtlassianBootstrapManager.getTestDatabaseConnection(DefaultAtlassianBootstrapManager.java:347)

        at com.atlassian.jira.config.database.JdbcDatasource.getConnection(JdbcDatasource.java:211)

        at com.atlassian.jira.config.database.DatabaseConfig.testConnection(DatabaseConfig.java:87)

        at com.atlassian.jira.web.action.setup.SetupDatabase.testConnection(SetupDatabase.java:225)

        at com.atlassian.jira.web.action.setup.SetupDatabase.doValidation(SetupDatabase.java:189)

        at com.atlassian.jira.web.action.setup.SetupDatabase.doConnectionCheck(SetupDatabase.java:121)

        ... 3 filtered

        at java.lang.reflect.Method.invoke(Method.java:498)

        at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)

        at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)

        ... 2 filtered

        at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:63)

        ... 7 filtered

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

        ... 49 filtered

        at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:80)

        ... 1 filtered

        at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:78)

        ... 28 filtered

        at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)

        ... 27 filtered

        at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)

        ... 28 filtered

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

        at java.lang.Thread.run(Thread.java:745)

Now I know there is an issue with MySQL 5.7 and JIRA 7 relating to this, but this is MySQL 5.1.73. There was another post on here a few weeks relating to the exact same issue but the trail went cold so thought I'd post a new post as this is blocking everything for me right now.

 Any help greatly appreciated.

Thanks

[Mike M · Accepted solution]

I gave up on this, must be some incompatibility between these versions.

For anyone else who stumbles across this issue, the way I got round it was to uninstall MySQL 5.1 and install MySQL 5.6 and everything worked as it was meant to.

I believe the problem stemmed from the default yum repository that ships with Redhat 6 Enterprise. I followed the steps at this link https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/ to update the yum repository and then revert to defaulting to MySQL 5.6 (rather than the default which is MySQL 5.7). I installed everything as the rest of the link above instructs, and then continued to setup as per JIRA instructions and everything worked first time.  

Thanks

[cosinex]

The name of the variable containing the "Default Storage Engine" changed between MySQL 5.5.3 and 5.7.5 . 

Before 5.5.3, there was only "storage_engine". Starting with 5.5.3, one could use both "storage_engine" and "default_storage_engine". Beginning with 5.7.5, only "default_storage_engine" remained.

BTW: The release date of MySQL 5.5.3 was 2010-03-24, that of MySQL 5.7.5 was 2014-09-25.

[Allen Chuang]

it's work.

in MySQL 5.1 (default version on CentOS6.7) + jira 7.13.

modify dbconfig.xml

<url>jdbc:mysql://address=(protocol=tcp)(host=localhost)/jira?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=storage_engine=InnoDB</url>

donot test in config.sh , just run start-jira.sh.