[Database custom field] - Error obtaining connection

[Philippe Leménager]

Hello,

I'm trying to test the "Database Custom Field" plugin and I get error :

2013-01-28 10:39:54,251 ajp-bio-8009-exec-8 ERROR administrateur 639x31x1 15m2yon 172.26.3.20 /secure/QuickEditIssue!default.jspa [commons.jira.kconfig.PluginConfiguration] Database JNDI >>Referentiel<<, error obtaining connection

I've tried first with an Oracle Database and I got the same error. Here are the resources configured in context.xml :

<Context>
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Resource name="testOracle"
                auth="Container" type="javax.sql.DataSource"
                username="MyUser"
                password="MyPassword"
                driverClassName="oracle.jdbc.driver.OracleDriver"
                url="jdbc:oracle:thin:@DBServerIP:1522:DBtest"
                connectionProperties="SetBigStringTryClob=true"
        />

        <Resource name="testMySQL"
                auth="Container" type="javax.sql.DataSource"
                username="MyUser"
                password="MyPassword"
                driverClassName="com.mysql.jdbc.Driver"
                url="jdbc:mysql://DBServerIP:3306/referentiel"
        />
</Context>

Of course, I've created database users and granted them the SELECT privilege on the source database/table.

In "Database(JNDI name)" field of "Kepler parameters for all plugins" page, I've put the "Resource name" configured in context.xml.

Any idea ?

[Philippe Leménager · Accepted solution]

Finally, I've installed mysql-client on the test server and I've found that MySQL user was not correctly configured in MySQL and that's why connection was refused.

Problem solved.

[Nic Brough -Adaptavist-]

Ahh, excellent. I had a feeling it was some minor oversight like that! That's where the MySQL connection testing is so useful. Well found!

[Radu Dumitriu]

Thanks Nic for the support :) I missed this one !

[Nic Brough -Adaptavist-]

Ok, well the Oracle connection simply isn't going to work at all. You can connect to the server, but it's point-blank refusing to let you in, so you'll need to fix permissions and access.

The MySQL server looks more healthy - it's letting you in which is a start, but I think you need to know exactly what it's connected to and what you can select from the database it's connected to

I'm not sure logging will tell you much more though. You could increase logging on the sql queries, but that might only be logging core-jira queries and not the ones generated by plugins. Even if it is logging them, you might just get the same error you've already got above.

I did notice one minor thing on the MySQL connection - the error message talks about "Referentiel", but your connection is to "referentiel". I don't know if case-sensitivity is important, but I think it needs ruling out.

[Philippe Leménager]

With MySQL, I had first another error message telling that Database JNDI referentiel was not defined and in fact, you're right, it's case sensitive and the real name is Referentiel with a capital R. Then I have the error shown in my first message and it means that Database JNDI config is found and read but Jira can't obtain connection with it.

The question is : Why ?

[Nic Brough -Adaptavist-]

That's why you need to debug the database connection. Telnet proves the basics, but you need to connect to it fully next - do you have an SQL client on the Jira server you can test with? Make sure you can get into the right database using those settings? I'm trying to determine if the error is in the raw connection, or inside the plugin.

[Philippe Leménager]

There is only Jira on our Jira servers (Production and test).

[Nic Brough -Adaptavist-]

That's going to make it more difficult if you can't prove the connection works from the servers.

The next place to look is at the database logs - they should be able to tell you if the Jira process is logging in and picking up the right database. It's not working of course, but the database logs might say "right user name, right password, wrong privileges", or "login fine, but not connecting to referentiel", etc.

[Nic Brough -Adaptavist-]

Can you connect directly to the database from the Jira server? I mean using a command line sql tool at least (or even just a telnet on that port)?

[Philippe Leménager]

Thanks for your answer.

Connecting to MySQL server, it says :

Trying [DB Server IP address]...
Connected to [DB Server IP address]
Escape character is '^]'.
8
5.1.57-log��$XrS+v7N�_uNe2g4i5lCH

You can see the MySQL version 5.1.57 on the last line.

Connecting to Oracle server, it says :

Trying [DB Server IP address]...
telnet: connect to address [DB Server IP address]: Connection refused

Maybe it's due to a security configuration on our Oracle server.

As I have the same error connecting from Jira to MySQL or to Oracle, I don't think it's a communication problem.

The log in my first message comes from catalina.out. Is there another log that could mention the error with another explanation ?

One more information : MySQL server I try to connect from Database Custom Field plugin is the server that contains Jira Database. Oracle is on another server.