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

[Amro Hassaan]

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. 

[Bob Swift · Accepted solution]

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

[Amro Hassaan]

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

[Bob Swift]

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. 

[Amro Hassaan]

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.