Datasource for Confluence Sql-macro add-on pointing to JIRA db on another server

If I am populating the resource tag to set up a datasource for Confluence SQL that points to JIRA DB that is on another remote server, how should the url parameter in the resource tag look like? example on the wiki always shows 'localhost'. I am not sure what type of URL to be embedded there to point to the JIRA db above. 

1 answer

1 accepted

It would be the host:port of your database server.

That's what I found in the JDBC docs and did but still not working. Here is the full procedure I did:

  1. Installed the SQL for confluence using UPM and left all defaults. Didn't add any DataSource Profiles (not sure if they are mandatory. that could be another question!)
  2. updated the server.xml file as such:

    <Resource name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://host:3306/dbname?autoReconnect=true"
    username="username"
    password="pw"
    maxActive="25"
    maxIdle="5"
    maxWait="10000"
    validationQuery="Select 1"
    />

  3. Downloaded mysql-connector-java-5.1.39-bin.jar and added it to /srv/atlassian-confluence/lib (Currently have mysql v 5.5 installed on the server)

  4. Restarted Confluence
  5. created a wiki page, inserted a sql-query macro, didn't populate any of the values on the dialogue while inserting macro except the datasource name and the format as html and then  added this snippet for testing:
    {sql-query:dataSource=myDS|output=wiki}
    select * from myTable
    {sql-query}
  6. Nothing comes back when i hit preview. Page freezes and the "There was error loading preview. Try again later" error.

  7. All that while tailing the logs..nothing comes out there when this error happens.

Not sure where could my error be? or what I am missing/misunderstood?

Thanks for helping

 

Application data sources are part of Tomcat, so place any driver jars in the Tomcat lib directory, namely <installation>/lib. Data source configuration - application server has more details. That might be all that is needed, otherwise, see below.

If you are not experienced in this area, I recommend starting with an easier path first (no restarts required) to get your database access working and then later go to the application server defined data source. See Data Source Profiles. Also Data Sources for information on the differences. 

Thanks for the help! Zeroed in on problem. the MySQL Db on the remote server was not accepting connections from external servers. Creating a user in the target DB and opening up port 3306 for external connections solved the problem.

 

 

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
Sarah Goff-Dupont
Published 2 hours ago in Confluence

Think you know shares vs. @mentions in Confluence? Take this collab quiz.

To anyone who doubts that Atlassians are a little too obsessed with collaboration, and tools related thereto, let me describe a recent discussion we had (which took place on our internal Confluence, ...

37 views 2 3
Read article

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