Jira stopt working after restarting MySQL

[Fré de Vries]

We work with Jira/Greenhopper for 6 weeks now without a problem. Suddenly it stopped working.

I restarted Jira and now i get

HTTP Status 404 -

---

type Status report

message

description The requested resource () is not available.

This is probably related to the fact that de mysql server on this machine has been restarted this morning.

Here's the Startup log from catalina.out

****************

JIRA starting...

****************

2012-10-01 13:13:49,546 main INFO [atlassian.jira.startup.JiraStartupLogger]

___ Environment _____________________________

JIRA Build : 5.1.3#782-sha1:4389c897ff46ac633147bfa0023fbc37f3cb8ca3

Build Date : Tue Aug 14 00:00:00 CEST 2012

JIRA Installation Type : Standalone

Application Server : Apache Tomcat/6.0.32 - Servlet API 2.5

Java Version : 1.6.0_26 - Sun Microsystems Inc.

Current Working Directory : /opt/atlassian/jira/bin

Maximum Allowable Memory : 682MB

Total Memory : 245MB

Free Memory : 240MB

Used Memory : 5MB

Memory Pool: Code Cache : Code Cache: init = 2555904(2496K) used = 1086784(1061K) committed = 2555904(2496K) max = 50331648(49152K)

Memory Pool: PS Eden Space : PS Eden Space: init = 67174400(65600K) used = 2696560(2633K) committed = 67174400(65600K) max = 246153216(240384K)

Memory Pool: PS Survivor Space : PS Survivor Space: init = 11141120(10880K) used = 3627360(3542K) committed = 11141120(10880K) max = 11141120(10880K)

Memory Pool: PS Old Gen : PS Old Gen: init = 178978816(174784K) used = 0(0K) committed = 178978816(174784K) max = 536870912(524288K)

Memory Pool: PS Perm Gen : PS Perm Gen: init = 21757952(21248K) used = 16384800(16000K) committed = 21757952(21248K) max = 268435456(262144K)

JVM Input Arguments : -Djava.util.logging.config.file=/opt/atlassian/jira/conf/logging.properties -XX:MaxPermSize=256m -Xms256m -Xmx768m -Djava.awt.headless=true -Datlassian.standalone=JIRA -Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true -Dmail.mime.decodeparameters=true -XX:+PrintGCDateStamps -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.endorsed.dirs=/opt/atlassian/jira/endorsed -Dcatalina.base=/opt/atlassian/jira -Dcatalina.home=/opt/atlassian/jira -Djava.io.tmpdir=/opt/atlassian/jira/temp

___ Java System Properties _________________

atlassian.standalone : JIRA

catalina.base : /opt/atlassian/jira

catalina.home : /opt/atlassian/jira

catalina.useNaming : true

common.loader : ${catalina.base}/lib,

${catalina.base}/lib/*.jar,

${catalina.home}/lib,

${catalina.home}/lib/*.jar

file.encoding : UTF-8

file.encoding.pkg : sun.io

java.awt.graphicsenv : sun.awt.X11GraphicsEnvironment

java.awt.headless : true

java.awt.printerjob : sun.print.PSPrinterJob

java.class.version : 50.0

java.home : /opt/atlassian/jira/jre

java.io.tmpdir : /opt/atlassian/jira/temp

java.naming.factory.initial : org.apache.naming.java.javaURLContextFactory

java.naming.factory.url.pkgs : org.apache.naming

java.runtime.name : Java(TM) SE Runtime Environment

java.runtime.version : 1.6.0_26-b03

java.specification.name : Java Platform API Specification

java.specification.vendor : Sun Microsystems Inc.

java.specification.version : 1.6

java.util.logging.config.file : /opt/atlassian/jira/conf/logging.properties

java.util.logging.manager : org.apache.juli.ClassLoaderLogManager

java.vendor : Sun Microsystems Inc.

java.vendor.url : http://java.sun.com/</span<>>

java.vendor.url.bug : http://java.sun.com/cgi-bin/bugreport.cgi</span<>>

java.version : 1.6.0_26

java.vm.info : mixed mode

java.vm.name : Java HotSpot(TM) 64-Bit Server VM

java.vm.specification.name : Java Virtual Machine Specification

java.vm.specification.vendor : Sun Microsystems Inc.

java.vm.specification.version : 1.0

java.vm.vendor : Sun Microsyst...

[Fré de Vries]

I created an issue at Atlassian support. The helped me out. I took half an hour to find out that mysql wasn't bound to the localhost, only to the server ip. We changed the config with config.sh and things work again.

[PauloP]

Hi All

As default behavior, JIRA is not able to properly handle database conection pool after a server failure (networking or GDB system failure). However, you may try to use validation queries in order to ask JIRA to try to restablish the connection before fail.

For further details, please use the following link as your guideline:

https://confluence.atlassian.com/display/JIRA/Surviving+Connection+Closures

Cheers,

Paulo Renato

[Fré de Vries]

Validation query is already in the config file by default is guess...

<jira-database-config>

<name>defaultDS</name>

<delegator-name>default</delegator-name>

<database-type>mysql</database-type>

<jdbc-datasource>

<url>jdbc:mysql://localhost:3306/jira?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=storage_engine=InnoDB</url>

<driver-class>com.mysql.jdbc.Driver</driver-class>

<username>jira</username>

<password>*****************</password>

<pool-min-size>20</pool-min-size>

<pool-max-size>20</pool-max-size>

<pool-max-wait>30000</pool-max-wait>

<validation-query>select 1</validation-query>

<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>

<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

<pool-max-idle>20</pool-max-idle>

<pool-remove-abandoned>true</pool-remove-abandoned>

<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>

<pool-test-while-idle>true</pool-test-while-idle>

<validation-query-timeout>3</validation-query-timeout>

</jdbc-datasource>

</jira-database-config>

[Stephen Hodgson]

I asked JIRA support about the validation query after having the same issue. Database pool connections were not being restored even when the validation query was in the config file. Atlassian's documentation would have you believe that the validation query will fix communication hiccups:

When a database server reboots or a network failure has occurred, all connections in the database connection pool are broken. To overcome this issue, JIRA would normally need restarting (or for JIRA WAR distributions, the application server running JIRA would need restarting).

However, database connections in the database connection pool can be validated by running a simple SQL query. If a broken database connection is detected in the pool, a new one is created to replace it.

[...]

You should now be able to recover from a complete loss of all connections in the database connection pool without the need to restart JIRA or the application server running JIRA.

However, Atlassian support told me that the validation query doesn't always restore database connections after a loss of communication with the database. I asked them to update the wiki article to reflect this, but I don't see any changes on the article.

[Nic Brough -Adaptavist-]

Ok, Jira doesn't handle the database disappearing very well, and you've done exactly the right thing and restarted Jira - this makes it reread the configuration and reconnect to the database cleanly.

The problem now is that the database is not responding to the connection attempt. The usual culprits of the error message you've got are incorrect settings in the connection string, network problems or a change in the database locking your user out.

For debugging all of these though, you start by checking the settings - can you connect locally with the mysql command line? As root? As the Jira user? The attempts to connect would give you a lot more information about what's wrong.

[Fré de Vries]

I can also connect locally with the msyql command line with the jira credentials and then do 'use jira' and then 'show tables'

[Fré de Vries]

Thanks Nic,

Other sites/apps using the same Mysql server still work so I assumed that its still running on port 3306.

The Jira installation is on the same server as where MySQL runs

I can login to PHPMyAdmin with the credentials from /var/atlassian/application-data/jira/dbconfig.xml

What else can i test?

[Fré de Vries]

I haven't changed anything anywhere, so that cannot be the problem.

There's nothing about a user not able to log in in the mysql log

I assume that tomcat is running because its tomcat that puts the error on the screen i think

type Status report

message

description The requested resource () is not available.

---

Apache Tomcat/6.0.32

[Nic Brough -Adaptavist-]

That's bad and good. Good because you can connect, bad because if you can connect, then Jira should be able to as well, and the log isn't telling us a lot about the failure!

I am assuming you've not touched the settings on Jira (not just the connection string - there's other stuff that might break the connection too).

One random memory - could you check that the Tomcat Jira is running under (another assumption - you're on Jira standalone, or you've installed a WAR on a Tomcat) is happy to connect over that port. Do you have anything in the policies for the Tomcat? Specifically, under the Tomcat conf directory, you may have one or more files called (somthing).policy which allow/block a connection - look for the string java.net.SocketPermission in there and see if there's anything blocking it.

I am grasping at straws on that one though - if you've not changed the settings, it should not be that.

My next step would be to try the logs for the database server - they should be logging the attempt by Jira to connect, and why it's being refused.

[Nic Brough -Adaptavist-]

Ok, I was pretty sure that you hadn't changed anything (the tomcat policy thing really was a guess based on an old memory, and as you say, if it's not changed, that's not the problem)

Tomcat itself is running, yes, because you get a Tomcat error, rather than a browser error saying something like "there's nothing there". But Tomcat is unable to start Jira because Jira requires the database connection, and it's not able to connect.

No errors in the mysql log is not helpful though - it implies that Tomcat isn't even trying to connect, but the Tomcat error message is very clearly telling you that it is, and it's not getting a connection. We need to debug that lack of connection.

The only way I know to do that is to check the database logs - can it log Jira trying to log in? Ideally, we need the database server to have increased logging, so that it logs all logins - including your command line one. Then, the logs will either it'll tell us what Jira is doing wrong, or, if it logs nothing at all, you'll know something is blocking the local connection from Jira to the database - some firewall, SELinux or something at that level.

[Fré de Vries]

When i try to login with a wrong password in Mysql, i see a 'access denied' in the mysql.log.

When i login with the right credentials, nothing is being logged.

When i refresh the jira page, nothing happens in the log, so jira is not trying to login with the wrong credentials i guess.

Is there a way to find out if jira is even trying to login?

[Nic Brough -Adaptavist-]

Sorry, I wasn't clear - Jira only tries to log into the database when it (re)starts. Once it's been rejected, it does not try again. Establishing the connection with the database is a start-up process - once completed, it's not triggered again by simple refreshes.

You'll need to restart Jira to provoke it into attempting the connection again.

In the past, I've used http://dev.mysql.com/doc/refman/5.5/en/query-log.html to enable login logging on the database side - I am NOT a DBA though, and certainly not a MySQL guru, so I really don't know if that's the best way to do it (And your SQL people may have more better ideas)