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
Community moderators have prevented the ability to post new answers.
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&amp;characterEncoding=utf8&amp;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!
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Matt, I've updated step 1 and 11. Thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Raju, mark your answer accepted..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Great work summarising your investigations! Hopefully this will help a whole bunch of other developers, too. :-)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Joseph.
I will follow the steps and get back with updates.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Jonathan!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This ticket contains better details on how to use the new datasource system:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Is there any guide, that specifies what I should put into pom when I want to use MySQL?
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Useful tip, thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Seems that in case of existing database one needs to reindex it after connecting to JIRA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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&amp;characterEncoding=UTF8&amp;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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Community moderators have prevented the ability to post new answers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.