Jira stopt working after restarting MySQL

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...




3 answers

0 vote

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.

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

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?

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

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.

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.

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?

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)

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

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>

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.

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.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

174 views 1 17
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you