Jira crashed and will not restart due to connection problem with mysql

Question

This morning I installed RMsis (same mysql database server as Jira, but separate database). A colleague worked with RMsis without issues. I then imported a project from an external bugzilla. During the import, I received a warning that Jira was unstable (see attachment). Stopping and staring Jira cured this issue (running on 64-bit ubuntu 12.04). Jira then went down, and refused to start. I had to remove the PID file (even though I changed the owner of the directories to jira) and kill the still running jira process. A restart resulted in the error message below. I have verified that I can log into mysql (mysql -u jirrauser -p) from the command line. I have increased the values for the connection pool from 20 to 50 (what should I set them to?). I have upgraded Jira from 5.1.5 to 5.1.8. Still the same result. Google was no friend, although I found one issue that appeared closely related, but without a resolution: https://jira.atlassian.com/browse/JRA-27348?page=com.atlassian.bonfire.plugin:jira5compat-bonfire-ts-issue-tabpanel 2012-11-07 13:45:58,862 main INFO jira.config.database.DatabaseConfigurationManagerImpl Now running Database Checklist Launcher 2012-11-07 13:45:59,084 main ERROR NoModule Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:mysql://127.0.0.1:3306/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB', driverClassName='com.mysql.jdbc.Driver', username='jirauser', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=50, minSize=50, initialSize=null, maxIdle=50, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery='select 1', minEvictableTimeMillis=60000, timeBetweenEvictionRunsMillis=300000, poolPreparedStatements=null, testOnBorrow=null, testOnReturn=null, testWhileIdle=true, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedTimeout=300, validationQueryTimeout=3, defaultCatalog=null}} org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'jirauser'@'localhost' (using password: YES)) at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388) at org.apache.commons.dbcp.BasicDataSource.setLogWriter(BasicDataSource.java:1134) at org.ofbiz.core.entity.transaction.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:109) at org.ofbiz.core.entity.ConnectionFactory.tryGenericConnectionSources(ConnectionFactory.java:69) at org.ofbiz.core.entity.transaction.JNDIFactory.getConnection(JNDIFactory.java:146) at org.ofbiz.core.entity.TransactionFactory.getConnection(TransactionFactory.java:101) at org.ofbiz.core.entity.ConnectionFactory.getConnection(ConnectionFactory.java:59) at com.atlassian.jira.ofbiz.DefaultOfBizConnectionFactory.getConnection(DefaultOfBizConnectionFactory.java:28) at com.atlassian.jira.upgrade.util.UpgradeUtils.tableExists(UpgradeUtils.java:101) at com.atlassian.jira.appconsistency.db.MinimumUpgradableVersionCheck.databaseSetup(MinimumUpgradableVersionCheck.java:49) at com.atlassian.jira.appconsistency.db.MinimumUpgradableVersionCheck.isOk(MinimumUpgradableVersionCheck.java:30) at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.doStartupChecks(JiraDatabaseConfigChecklist.java:63) at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.startupOK(JiraDatabaseConfigChecklist.java:51) at com.atlassian.jira.startup.DatabaseChecklistLauncher.start(DatabaseChecklistLauncher.java:35) at com.atlassian.jira.startup.DefaultJiraLauncher$2.run(DefaultJiraLauncher.java:92) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:284) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseConfigured(DatabaseConfigurationManagerImpl.java:175) at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:87) at com.atlassian.jira.startup.DefaultJiraLauncher.access$100(DefaultJiraLauncher.java:25) at com.atlassian.jira.startup.DefaultJiraLauncher$1.run(DefaultJiraLauncher.java:62) at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:33) at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:57) at com.atlassian.jira.startup.LauncherContextListener$1.create(LauncherContextListener.java:76) at com.atlassian.jira.startup.LauncherContextListener$1.create(LauncherContextListener.java:71) at com.atlassian.multitenant.impl.MultiTenantComponentMapImpl.get(MultiTenantComponentMapImpl.java:121) at com.atlassian.multitenant.impl.MultiTenantComponentMapImpl.onTenantStart(MultiTenantComponentMapImpl.java:165) at com.atlassian.multitenant.impl.DefaultMultiTenantManager$1.consume(DefaultMultiTenantManager.java:134) at com.atlassian.multitenant.impl.DefaultMultiTenantManager$1.consume(DefaultMultiTenantManager.java:131) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.runForEachListener(DefaultMultiTenantManager.java:256) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.startTenant(DefaultMultiTenantManager.java:130) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.startAll(DefaultMultiTenantManager.java:203) at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:104) <+12> (StandardContext.java:4206) (StandardContext.java:4705) (ContainerBase.java:1057) (StandardHost.java:840) (ContainerBase.java:1057) (StandardEngine.java:463) (StandardService.java:525) (StandardServer.java:754) (Catalina.java:595) at java.lang.reflect.Method.invoke(Unknown Source) <+2> (Bootstrap.java:289) (Bootstrap.java:414) Caused by: java.sql.SQLException: Access denied for user 'jirauser'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919) <+2> at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:781) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46) <+3> at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284) at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556) at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545) ... 47 more 2012-11-07 13:45:59,103 main ERROR atlassian.jira.startup.LauncherContextListener Unable to start JIRA. com.atlassian.jira.exception.DataAccessException: java.sql.SQLException: Access denied for user 'jirauser'@'localhost' (using password: YES) at com.atlassian.jira.upgrade.util.UpgradeUtils.tableExists(UpgradeUtils.java:128) at com.atlassian.jira.appconsistency.db.MinimumUpgradableVersionCheck.databaseSetup(MinimumUpgradableVersionCheck.java:49) at com.atlassian.jira.appconsistency.db.MinimumUpgradableVersionCheck.isOk(MinimumUpgradableVersionCheck.java:30) at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.doStartupChecks(JiraDatabaseConfigChecklist.java:63) at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.startupOK(JiraDatabaseConfigChecklist.java:51) at com.atlassian.jira.startup.DatabaseChecklistLauncher.start(DatabaseChecklistLauncher.java:35) at com.atlassian.jira.startup.DefaultJiraLauncher$2.run(DefaultJiraLauncher.java:92) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:284) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseConfigured(DatabaseConfigurationManagerImpl.java:175) at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:87) at com.atlassian.jira.startup.DefaultJiraLauncher.access$100(DefaultJiraLauncher.java:25) at com.atlassian.jira.startup.DefaultJiraLauncher$1.run(DefaultJiraLauncher.java:62) at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:33) at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:57) at com.atlassian.jira.startup.LauncherContextListener$1.create(LauncherContextListener.java:76) at com.atlassian.jira.startup.LauncherContextListener$1.create(LauncherContextListener.java:71) at com.atlassian.multitenant.impl.MultiTenantComponentMapImpl.get(MultiTenantComponentMapImpl.java:121) at com.atlassian.multitenant.impl.MultiTenantComponentMapImpl.onTenantStart(MultiTenantComponentMapImpl.java:165) at com.atlassian.multitenant.impl.DefaultMultiTenantManager$1.consume(DefaultMultiTenantManager.java:134) at com.atlassian.multitenant.impl.DefaultMultiTenantManager$1.consume(DefaultMultiTenantManager.java:131) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.runForEachListener(DefaultMultiTenantManager.java:256) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.startTenant(DefaultMultiTenantManager.java:130) at com.atlassian.multitenant.impl.DefaultMultiTenantManager.startAll(DefaultMultiTenantManager.java:203) at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:104) <+12> (StandardContext.java:4206) (StandardContext.java:4705) (ContainerBase.java:1057) (StandardHost.java:840) (ContainerBase.java:1057) (StandardEngine.java:463) (StandardService.java:525) (StandardServer.java:754) (Catalina.java:595) at java.lang.reflect.Method.invoke(Unknown Source) <+2> (Bootstrap.java:289) (Bootstrap.java:414) Caused by: java.sql.SQLException: Access denied for user 'jirauser'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919) <+2> at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:781) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46) <+3> at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.ofbiz.core.entity.ConnectionFactory.tryGenericConnectionSources(ConnectionFactory.java:87) at org.ofbiz.core.entity.transaction.JNDIFactory.getConnection(JNDIFactory.java:146) at org.ofbiz.core.entity.TransactionFactory.getConnection(TransactionFactory.java:101) at org.ofbiz.core.entity.ConnectionFactory.getConnection(ConnectionFactory.java:59) at com.atlassian.jira.ofbiz.DefaultOfBizConnectionFactory.getConnection(DefaultOfBizConnectionFactory.java:28) at com.atlassian.jira.upgrade.util.UpgradeUtils.tableExists(UpgradeUtils.java:101) ... 38 more

Zenterio AB1 · Answer

My initial error report states that the fault at first was intermittent and that I could successfully use the exact same credentials as Jira is using from the mysql command line.

I was later requested by Atlassian to use their tool, /opt/atlassian/jira/bin/config.sh, which reads the very same dbconfig.xml file as Jira does, and it reported that the connection to mysql was OK.

The org.apache.commons.dbcp.SQLNestedException exception is produced by Jira.

Here is the output of the tool:

root@jira:/home/erlid# /opt/atlassian/jira/bin/config.sh
No JRE_HOME or JAVA_HOME environment variable is set - attempting to just run java command
Loading application properties from /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/jira-application.properties
Reading database configuration from /var/atlassian/application-data/jira/dbconfig.xml
No graphics display available; using console.
----------------------
JIRA Configurator v1.1
----------------------

— Main Menu —
[H] Configure JIRA Home
[D] Database Selection
[A] Advanced Settings
[S] Save and Exit
[X] Exit without Saving

Main Menu> D

— Database Selection —
Database Type : MySQL
Instance : 127.0.0.1:3306/jiradb
Connect As : jirauser / *****

[H] HSQL (not for production use)

* [M] MySQL
[O] Oracle
[P] PostgreSQL
[S] SQL Server (MS-SQL)

[X] Return to Main Menu

Database Selection [M]> 
MySql Database Configuration.
Hostname (127.0.0.1)> 
Port (3306)> 
Database (jiradb)> 
Username (jirauser)> 
Password (*****)> 
Test Connection ([Y]/N)? >

Attempting to connect to 127.0.0.1:3306/jiradb
Connection successful!

Nic Brough -Adaptavist- · Answer

The error message is clear - Jira is trying to do something for which the database does not allow it to.  Check the grants on the Jira account, and what the differences between the original and new accounts are.

Zenterio AB1 · Answer

I am stating that Jira has an access problem, no other client had an access problem. This access problem was intermittent.

Using the same username, password and database from the mysql command line causes no problems. Using the same username, password and database with the atlassian provided tool causes no problems.