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

0 votes
Accepted answer

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
Community showcase
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,104 views 17 14
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