How to query directly to my Jira Database?

[Oskar Granlund]

I have a rather big database and it's needs cleaning. Therefore I want to filter out which users that haven't been online for x amount of time. I want to query my Jira database directly to retrieve this information.

How do I do this?

[Nic Brough -Adaptavist-]

Look in cwd_user for the last login date.

You can use any database management tool you like to read the database, as long as it supports the database you've chosen for JIRA.  I tend to use the command line tools, because I rarely look at the raw database.

Remember to only read your database, never write to it with SQL.

[Oskar Granlund]

Ok thx byt how do I actually query the Jira database?
I've tried to setup my own mysql server and then tried to import the database with no success.

[Nic Brough -Adaptavist-]

You need a database management tool (I think you typed your comment while I was adding a missing bit to my answer)

For mysql, there are loads of options.  The basic one is the mysql command line tool of course - it's generally installed by default on the server, but you can load it up on any client and connect to the database over your network.

There are plenty of other tools you could use too.  A search for "MySQL Client" should give you a long list of options.

[Oskar Granlund]

Maybe I'm totally of track but basically what I'm trying to do is..
I installed postgres and then I tried to load this ~/saab-ldap-sync-service-TRUNK/target/jira/home/database/h2db.mv.db into my newly created postgres database which fails.

I still dont get it how do I query this h2db.mv.db file?

[Nic Brough -Adaptavist-]

You've now talked about three different databases, and although there are some migration tools around that might be able to convert one to another, we've had problems with them, so we don't recommend that at all.

The correct approach is to run your h2 based JIRA, go to admin -> backup and restore, and run the xml exporter.

Then install a new JIRA backed with postgres or mysql, and import the xml into it.