Using mysql instead of HSQL db with Atlassian PDK

[Raju]

Right now Atlassian PDK runs the Confluence/Jira/... with HSQL db as a backend. Is there any way we can change this backend to say MySQL.

Issue with HSQL is, during runtime its locked by App and hence can't be opened to update/see things on fly from SQL Browser.

Any pointers?

Raju

[Raju · Accepted solution]

Thanks to Joseph (for atlas-create-home-zip info) and Shailesh for to get around atlas-clean issue!

Below is complete Recipe to use mysql along with Atlassian PDK.

NOTE: Do not run atlas-clean during performing following steps. Otherwise you have to start from the very first step.

***** Preparation ******
1. Create atlas-create-jira-plugin to create Jira Plugin project.
2. Execute atlas-run for first time. It will create hsql database along with "home" directory in target/jira directory.
3. Configure Jira as per need (like creation of Project, issues). This is the data you always need to pre-populate after you do atlas-clean which wipes out everything from target directory (including home directory).
4. Take Jira Data backup (System -> Import & Export -> Backup System).
5. Shutdown Jira Instance.

*** Configure mysql data source ********
6. update target/jira/dbconfig.xml by replacing hsql settings with MySQL JNDI data source.

<?xml version="1.0" encoding="UTF-8"?>
<jira-database-config>
  <name>JiraDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mysql</database-type>
  <schema-name>jira50m4db</schema-name>
  <jdbc-datasource>
    <url>jdbc:mysql://localhost:3306/jira50m4db?useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB</url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <username>username</username>
    <password>password</password>
    <pool-size>15</pool-size>
    <validation-query>select 1</validation-query>
  </jdbc-datasource>
</jira-database-config>

schema-name, url, username, password these fields needs to change to reflect your database settings.

7. Do not remove database.* files (which are hsql database files) Atlassian Maven script do refer them after clean installation. Leave them alone, After your mysql installation successful, they are useless. Don't refer them!

8. execute command atlas-create-home-zip. This will create generated-test-resources.zip file in target/jira directory.
9. Copy this file to handy location.

**** Changes to pom.xml ******
10. Add <productDataPath> configuration property and set it to this generated-test-resources.zip file in order to load the JIRA data.

<productDataPath>${basedir}/generated-test-resources.zip</productDataPath>

******** Setting up Tomcat class path for mysql drivers ********
11. To avoid mysql driver Class not found exceptions, we need to put mysql driver file (mysql-connector-java-5.1.13.jar) in tomcat/lib directory.

**** Show Time ******
12. Execute atlas-run
In the logs you will see many info messages about no tables found. Below is one example:

[INFO] [talledLocalContainer] 2011-09-20 00:56:40,801 main WARN      [core.entity.jdbc.DatabaseUtil] Entity "OSPropertyDecimal" has no table in the database

Also log will show that it's correctly picking up mysql database as we have updated in dbconfig.xml

13. Access JIRA from url as displayed in console log.
14. You will be redirected to JIRA Setup Step 2. Click on import your existing data so that we can use the earlier JIRA export. (This will help to take care of license. Developer license will get imported along with data)

15. Once setup complete message appears, log in to JIRA using your credentials as set earlier.
16. Verify if data is imported correct or not.
17. That's it. Now you are all set to use MySQL with Atlassian PDK.

***** Getting around with Atlas-clean ******
There is one drawback with above approach!

If for some reason you need to use atlas-clean, contents from tomcat directory (including mysql-connector-java-5.1.13.jar) will get deleted.

18. To avoid this, create apache-tomcat-6.0.20.zip with addition of mysql jar file. And then using "maven install" update your maven repository to use this file instead of standard tomcat zip that you downloaded from atlassian maven repository.

atlas-mvn install:install-file -DgroupId=org.apache.tomcat -DartifactId=apache-tomcat -Dversion=6.0.20 -Dfile=apache-tomcat-6.0.20.zip -Dpackaging=zip -DgeneratePom=false

**** Show Time - One more Time ****
19. Execute atlas-clean to get ready for fresh run.
20. Execute atlas-run to start JIRA using pre-populated data (as stored in generated-test-resources.zip) along with mysql database.

This time we JIRA will immediately re-direct to login screen (skipping installation / data import setps!)

*** Live Happily ****
Run atlas-create-home-zip command again if you want to use latest JIRA data / application settings.

End of Recipe!

[MattS]

Nice! A couple of comments.

Step 1. I think you mean atlas-create-jira-plugin not atlas-create-jira-template?

Step 11. Put the MySQL jar file where?

[Raju]

Matt, I've updated step 1 and 11. Thanks.

[EddieW]

Raju, mark your answer accepted..

[Joe Clark]

Great work summarising your investigations! Hopefully this will help a whole bunch of other developers, too. :-)

[Richard Simko [RefinedWiki]]

This still works perfectly except that the version of Tomcat has changed and with one addition. Even on Mac OS JIRA loads Tomcat from a file named apache-tomcat-7.0.52-windows-x64.zip. If you simply change the version in the maven command above the file will be copied to apache-tomcat-7.0.52.zip The solution was simply to manually add -windows-x64.

[Richard Simko [RefinedWiki]]

Also I feel like Atlassian should simplify the process or at least make an official guide about this, took me quite some time to find this answer and it feels very wrong to have to follow a 20-step answer on a QA-site in order to get such a simple task working.

[Chris Stamp]

In Step 6 update path to {basedir}/target/jira/home

[Joe Clark]

The Atlassian Plugin SDK by default, downloads a zipped up "Home" directory for your product that is pre-configured to connect to an in-process HSQL db.

The cool thing is that you can override this behaviour and provide a customised "Home" directory instead. The basic process for doing this is as follows:

1) Run up an instance of the target product using atlas-run

2) Configure the product the way you like

3) Shut down the product

4) run the command atlas-create-home-zip

5) Store the resultant zip file somewhere handy

6) Add a <productDataPath> configuration property to your pom.xml, that points to the zip file.

Check out the documentation for more info: https://developer.atlassian.com/display/DOCS/atlas-create-home-zip

[Raju]

Thanks Joseph.

I will follow the steps and get back with updates.

[brainicorn]

Just letting everyone know, as of SDK 4.1, you can now just specify a custom datasource in you pom.xml

see: http://blogs.atlassian.com/2012/11/introducing-atlassian-plugin-sdk-4-1-the-ultimate-edition/

[Raju]

Thanks Jonathan!

[AdrienA]

This ticket contains better details on how to use the new datasource system:

https://ecosystem.atlassian.net/browse/AMPS-737

[Jan Prokeš]

Is there any guide, that specifies what I should put into pom when I want to use MySQL?

Thanks

[Raphael Bossek]

If you follow the https://ecosystem.atlassian.net/browse/AMPS-737 link there is an reference to an example in https://bitbucket.org/aragot/amps-examples/src/22b3b08b461170957554f275fcac3e7485fd0db0/start-jira-with-datasource/pom.xml?at=master

[Joe Clark]

Also, if you are running a product other than Confluence (which has a bug that prevents this), you can run HSQL as a separate process and then configure the product to connect to it remotely by changing the connection string, for example:

jdbc:hsqldb:hsql://localhost/databasename

To start HSQLDB as a process, try this:

java -cp hsqldb.jar org.hsqldb.Server -database.0 file:/path/to/database -dbname.0 databasename

[MattS]

Useful tip, thanks.

[Bjarni Thorbjornsson]

Hi,

Why is this so complex? A recipe containing 18 steps just to connect to a different database does not sound right to me! :)

Here is an old issue that has not been resolved yet: https://studio.atlassian.com/browse/AMPS-173. I think this is just what we need, i.e. a way to define the connection parameters. Please vote for the issue and put some pressure on Atlassian to make this a simple task.

Best regards,

-Bjarni

[Raju]

Hi Bjarni,

Agreed, there are too many steps to follow. I will vote on the issue you mentioned. Also add this workaround till issue gets resolved.

cheers,

Raju

[Joe Clark]

According to the documentation (http://confluence.atlassian.com/display/JIRA/Connecting+JIRA+to+MySQL), the MySQL JDBC driver is only included with the JIRA standalone distribution. The Atlassian SDK only downloads the WAR of JIRA to run up the app, so as the documentation suggests, you'll probably need to add this library to the tomcat classpath.

[Raju]

Joseph,

To configure mysql instead of hsql datbase, I've performed steps as you mentioned.
1. After running atlas-run first time, updated dbconfig.xml (from jira/home directory) to use mysql as a Datasource.
2. Later updated productDataPath to point to zip file created by atlas-create-home-zip command.
3. Also added,
OSGI instructions to import necessary packages as follows:

<instructions>
 
                       
   <!-- OSGi intructions go there -->

   <Import-Package>com.mysql.*</Import-Package>
                 
  <Import-Pacakge>javax.naming.*</Import-Pacakge>
   
</instructions>
 and Added JNDI, mysql dependencies as below:
        <dependency>
            
            <groupId>mysql</groupId>
            
            <artifactId>mysql-connector-java</artifactId>
            
            <version>5.1.13</version>
       
       </dependency>
        
      <dependency>
            
       <groupId>javax.naming</groupId>
            
        <artifactId>jndi</artifactId>

         <version>1.2.1</version>
        
      </dependency>

All these changes are done in pom.xml

When I run atlas-run again,

it does pick the dbconfig.xml correctly from generated zip location as pointed in pom.ml. But later it throws following exception:

[INFO] [talledLocalContainer] 2011-09-19 16:08:25,009 main ERROR [atlassian.jira.startup.LauncherContextListener] Unable to start JIRA.
[INFO] [talledLocalContainer] java.lang.IllegalArgumentException: JDBC Driver class 'com.mysql.jdbc.Driver could not be loaded.'
[INFO] [talledLocalContainer] at com.atlassian.jira.config.database.JdbcDatasource.registerDriver(JdbcDatasource.java:134)
...
Not sure why it's still couldn't able to find out mysql jar file. I've tried this for Jira 4.4.1 / 5.0-m2 / 5.0-m4. But same issue persists.

Do we need to add this jar in jira.war (jira webapp lib directory) or tomcat lib directory? Since these both options are created dynamically by atlas-run, how one can configure them to add these jars.

Any idea what's still missing?
thanks,
Raju

[Shailesh Shailesh]

Check this post: http://forums.atlassian.com/thread.jspa?forumID=46&threadID=52345

[Raju]

Thanks, mysql is not part of atlassian pdk 3.4. Also I couldn't able to find hsql in current repository for pdk. Do we have to add these files manually?

[Sam Huawey]

I have problems after migrating from HSQL to existing mySQL database. All issues are in place and searchable, but JIRA cannot find issues assigned to current user, e.g. the Assigned to me gadget shows no issues. The same is true for any piece of code that uses filters like assignee = currentuser()

Any ideas?

[Sam Huawey]

Seems that in case of existing database one needs to reindex it after connecting to JIRA.

[Brendan Patterson]

Hi Everyone,

Just FYI.  If you are switching to using an actual non-HSQL database I think its MUCH easier just to develop against a 'downloaded'/'standalone' version of Confluence.  I could set up 5 of those in the time it would take to read through the steps above and likely debug them.

As you all probably know there is a decent wizard for connecting the db and really just two simple standard steps (home directory creation + pointing to it) to get it all up and running.

Just my $0.02.   The above is only worth it if its faster and easier than setting up a standalone version, which appears to be far from the case when you throw a real database into the mix at present.

Brendan

[thanhtung90]

I have simple way I did:

- Edit dbconfig.xml:

<PROJECT_FOLDER>\target\jira\tmp-resources\jira-plugin-test-resources-5.1.8\jira-home\dbconfig.xml

<PROJECT_FOLDER>\target\jira\home\dbconfig.xml

- Copy mysql-connector-java-x.x.x.jar

<PROJECT_FOLDER>\target\container\tomcat6x\apache-tomcat-6.0.20\lib

-> so when we start

[thanhtung90]

I have simple way, I did:

- Edit dbconfig.xml in:

<PROJECT_FOLDER>\target\jira\tmp-resources\jira-plugin-test-resources-5.1.8\jira-home\dbconfig.xml

<PROJECT_FOLDER>\target\jira\home\dbconfig.xml

<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&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB</url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <username>root</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>

- Copy mysql-connector-java-x.x.x.jar

<PROJECT_FOLDER>\target\container\tomcat6x\apache-tomcat-6.0.20\lib

-> ok, we run command atlas-run

Hope this will help somebody.

[Raju]

That's what I've mentioned in my detailed comment. What more it talks about is how you can have these settings even though you clean up the target.

Anyways as Jonathan mentioned above, with Atlassian SDK 4.1, this is a inbuilt feature.

[Samuel Lefever]

Hello All,

I try and try to get this work, but it s always going back to hsql.
Maybe I m wrong in the pom.xml...

...

<build>
<plugins>
<plugin>
<groupId>com.atlassian.maven.plugins</groupId>
<artifactId>maven-jira-plugin</artifactId>
<version>3.3.4</version>
<extensions>true</extensions>
<configuration>
<productVersion>${jira.version}</productVersion>
<productDataVersion>${jira.data.version}</productDataVersion>
<productDataPath>${basedir}/src/main/resources/generated-test-resources.zip</productDataPath>
</configuration>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.5</source>
<target>1.5</target>
</configuration>
</plugin>
</plugins>
</build>

...

Please help me.

[Raju]

Samuel,

Only problem that may be is location of generated-test-resources.zip.

In above pom.xml you have mentioned productDataPath as ${basedir}/src/main/resources/generated-test-resources.zip. Can you make sure it's there?

Also general practice is you copy them under test resources. so path can be ${basedir}/src/test/resources/generated-test-resources.zip

Hope this helps,

Raju