mysql functionality

Description

I configured a mysql resolver as follows:

<resolver:DataConnector xsi:type="db:RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc:rdbms"
id="SqlDirectory"
validationQuery="select 1">

<ApplicationManagedConnection jdbcDriver="com.mysql.jdbc.Driver"
jdbcURL="jdbc:mysql://localhost/shibtest"
jdbcUserName="shib"
jdbcPassword="shibPW" />

<QueryTemplate>
<![CDATA[
SELECT * FROM users WHERE login=${principal}
]]>
</QueryTemplate>

</resolver:DataConnector>

First, I tried a few minor variations on the SELECT line including single quotes around the principle, and including a terminating semi-colon to try and resolve my problem. When that failed, I tried many variations on the jdbcURL, including IP address (both loopback and not loopback), specifying the port number, and just mangling it completely to try and force a failure. Changes to this attribute value do not seem to induce a change in behavior, which makes me think this line is not being properly consumed/interpretted.

Finally, when I run aacli.sh, nothing happens for 3 minutes (to the dot, which probablymeans a defaulted timeout is expiring) and then I get the exception trace seen below. Without the source code, and after triple checking for obvious issues, I can't think of anything else to try. Here is the error trace:

18:01:04,107 ERROR [RDBMSDataConnector] RDBMS Data Connector SqlDirectory: Unable to execute SQL query
SELECT * FROM users WHERE login=user1
java.sql.SQLException: An SQLException was provoked by the following failure: java.lang.InterruptedException
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:62)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:531)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector.retrieveAttributesFromDatabase(RDBMSDataConnector.java:353)

Note... I of course verified I could do the specified mysql query by hand:

[shibtester@rhelidp shibaa]# mysql -h localhost -u shib -pshibPW shibtest

mysql> SELECT * FROM users WHERE login='user1';
----------------------------------------------------------------------------------------------

login

organization

title

role

permissions

contact_phone

authentication_method

----------------------------------------------------------------------------------------------

user1

GTRI

Researcher

Deployer

Data TBD

123-555-4678

none

----------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Environment

None

Activity

ChadC 
April 4, 2007 at 11:54 AM
(edited)

Great, I'm glad it's working.

I'd point out that this was not a waste of my time. The reason we're doing these early releases is to determine things like which error messages are poor and how to diagnose problems that are likely to occur. This accomplished that splendidly.

Jeffrey Krug 
April 4, 2007 at 10:52 AM
(edited)

I updated the firewall rules on my Java 6 test machine really early on, since the error looked like a timeout issue, but I never tried that on my Java 5 machine. I just tried turning the firewall off and it worked... That seemed strange to me, so I turned on the firewall and it worked. Unfortunately I can't go back and determine the firewall's configuration before now, but I suspect I didn't have the mysql port open prior to now. Additionally, it seems like the mysql client always uses the loopback interface, while the jvm is using eth0, which certainly further masked the problem.

I'm really sorry to have wasted so much time on this.

ChadC 
April 4, 2007 at 2:39 AM
(edited)

Yeah, that all looks correct.

I'm afraid I'm really at a loss as to what the issue is. We've been unable to reproduce the issue except by removing the jar from the lib directory (thereby removing it from the classpath), which is obviously not the problem here. Could it be that the JVM is binding to a different network interface than is used from the mysql command and that that interface is firewalled off from localhost?

Jeffrey Krug 
April 3, 2007 at 1:19 PM
(edited)

bcprov-jdk14-135.jar
c3p0-0.9.1.1.jar
commons-pool-1.2.jar
httpclient-3.0.1-commons-codec-1.3.jar
httpclient-3.0.1.jar
jargs-1.0.jar
javolution-4.1.0.jar
jgrapht-jdk1.5.jar
joda-time-1.4.jar
log4j-1.2.14.jar
mysql-connector-java-5.0.5-bin.jar
not-yet-commons-ssl-0.3.7.jar
opensaml-28032007T010427-jdk-1.5.jar
openws-28032007T010245-jdk-1.5.jar
shibcommon-28032007T012747-jdk-1.5.jar
spring-2.0-beans.jar
spring-2.0-commons-collections.jar
spring-2.0-commons-logging.jar
spring-2.0-context.jar
spring-2.0-core.jar
spring-2.0-support.jar
spring-2.0-web.jar
spring-2.0-webmvc.jar
velocity-dep-1.5.jar
vt-ldap-2.8.jar
xmlsec-1.4.0-commons-logging.jar
xmlsec-1.4.0.jar
xmltooling-28032007T010100-jdk-1.5.jar

(The only thing I have changed from the package I downloaded was adding mysql-connector)

ChadC 
April 3, 2007 at 2:32 AM
(edited)

There are certainly some less than ideal errors coming from the pooling library, however I'm kind of stumped on what the issue is. Everyone else we've had try this (some of which went against MySQL, others used other DBs) received the expected results.

Can you give me a list of the jars in the lib directory?

Fixed

Details

Assignee

Reporter

Components

Created March 29, 2007 at 5:25 PM
Updated June 22, 2021 at 11:22 PM
Resolved April 4, 2007 at 11:54 AM