mySQL conflicts with org.hsqldb.* in JBoss 3

[twhphan]

I'm using JBoss 3.0 (got the src from the CVS), My mysql-service.xml in the deploy directory:




ConnectionURL=jdbc:mysql://localhost/db
DriverClass=org.gjt.mm.mysql.Driver
UserName=root
mySQLDS
java:/TransactionManager
<depends optional-attribute-name="ResourceAdapterName">JCA:service=RARDeployment,name=Minerva JDBC LocalTransaction ResourceAdapter
<depends optional-attribute-name="ConnectionManagerFactoryLoaderName">JCA:service=ConnectionManagerFactoryLoader,name=MinervaSharedLocalCMFactory
#
#Wed Aug 15 16:17:29 EDT 2001
BlockingTimeoutMillis=500000
IdleTimeoutMinutes=30
MaxSize=10
CleanupIntervalMinutes=10
MinSize=0
MaxIdleTimeoutPercent=1.0


org.jboss.resource.security.ManyToOnePrincipalMapping

UserName=root



I leave hsqldb-default-service.xml in the deploy directory, since the JMS is using it, but I get:

java.sql.SQLException: Table not found: MYTABLE in statement [select * from MyTable]
at org.hsqldb.Trace.getError(Trace.java:180)
at org.hsqldb.Result.(Result.java:175)
at org.hsqldb.jdbcConnection.executeHSQL(jdbcConnection.java:907)
at org.hsqldb.jdbcConnection.execute(jdbcConnection.java:718)
at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:686)
at org.hsqldb.jdbcStatement.executeQuery(jdbcStatement.java:68)
at org.jboss.resource.adapter.jdbc.local.StatementInPool.executeQuery(StatementInPool.java:710)
at esb.ejb.GenericDAOBean.getRowSet(Unknown Source)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:615)

Note that the container calls the wrong JDBC class. How may I correct this? I've an impression that the container ignore things within the tag

If I add the Password property in the tags , and , and restart JBoss, I get:

04:35:55,118 ERROR [mySQLDS] Unable to create ManagedConnection:
javax.resource.ResourceException: Unable to create DB connection for url: jdbc:mysql://localhost/db, user: myUser, exception: java.sql.SQLException: Invalid authorization specification: Access denied for user: 'myUser@localhost' (Using password: NO)
at org.jboss.resource.adapter.jdbc.local.JDBCManagedConnectionFactory.createManagedConnection(JDBCManagedConnectionFactory.java:392)

What's wrong? how may I change the above to "(Using password: YES)"? Is this due to a JDBC setting?

Thanks

[twhphan]

Forgot to mention that I'm using java:/mySQLDS (not java:/DefaultDS) in my code, and I've mm.mysql-2.0.8-bin.jar in $JBOSS_DIST/lib/ext, and changed datasource and type-mapping in standardjaws.xml, and standardjbosscmp-jdbc.xml

In addition, I get the following in server.log:
[org.jboss.resource.adapter.jdbc.local.JDBCManagedConnectionFactory.mySQLDS] Bound connection factory for resource adapter 'JCA:service=RARDeployment,name=Minerva JDBC LocalTransaction ResourceAdapter' to JNDI name 'java:/mySQLDS'

[davidjencks]

I don't know how to set up mysql to use a password. You can be sure you are using the expected ConnectionFactory by removing DefaultDS from hsql-default-service.xml, or just using a more reasonable PM for jms and removing hsqldb-default-service entirely, or setting up a no-trans ConnectionFactoryLoader using MySQL called NoTransDS for JMS to use. Anyway, if DefaultDS isn't there, you should get errors instead of accessing the wrong datasource.

[twhphan]

Thanks David,

I removed DefaultDS, then get "No resource manager found for java:/mySQLDS". But JBoss must talked to my database as it outputs a statement saying "[Sequence] Table 'Sequence' already exists" (Sequence is a CMP Entity Bean)

13:21:52,922 INFO [J2eeDeployer#Default] Deploying J2EE application: file:/opt/jboss-all/build/output/jboss-3.0.0alpha/deploy/esb.ear
13:21:53,446 INFO [ContainerFactory] Verifying file:/opt/jboss-all/build/output/jboss-3.0.0alpha/tmp/deploy/Default/esb.ear/ejb1003.jar
13:21:53,456 INFO [ContainerFactory] Deploying ScheduledJob
13:21:54,126 INFO [ContainerFactory] Deploying Sequence
13:21:54,129 INFO [ContainerFactory] Deploying GenericDAO
13:21:54,131 INFO [ContainerFactory] Deploying Logging
13:21:54,133 INFO [ContainerFactory] Deploying Mailer
13:21:54,389 WARN [StatelessSessionContainer] No resource manager found for java:/mySQLDS
13:21:57,173 WARN [StatelessSessionContainer] No resource manager found for java:/mySQLDS
13:21:57,526 INFO [EnterpriseContextCachePolicy] Cache policy scheduler started
13:21:57,969 INFO [Sequence] Table 'Sequence' already exists
13:21:58,194 INFO [ScheduledJob] Table 'ScheduledJob' already exists
13:21:58,209 INFO [J2eeDeployer#Default] Starting module esb-web.war
13:21:58,215 INFO [JettyService] Attempting to deploy: /esb
13:21:59,271 INFO [Jetty] Registered Jetty:Jetty=0,context=/esb,JBossWebApplicationContext=0

And when I access a web page that needs JDBC, I get:

13:25:58,912 ERROR [Default] java.lang.reflect.UndeclaredThrowableException:
13:25:58,913 ERROR [Default] javax.ejb.CreateException: jdbc not bound
13:25:58,913 ERROR [Default] <<no stack trace available>>
13:25:58,916 WARN [Jetty] WARNING: Servlet Exception for /esb/service/scheduledJob.jsp
java.lang.NullPointerException

[lastxit]

I can't speak for the hsql errors you're seeing, but I do have mysql resources configured separately and haven't seen any conflicts arise yet in my own testing.

A couple of notable differences in the deployment descriptor I'm using though, and I this should get you using passworded access to the mysql instance at least:

First off, in the "ManagedConnectionProperties" configuration, instead of the UserName=root syntax I have just UserName= with no defined name. I got the impression it wasn't needed at that point and it hasn't caused me any grief. Also, in that config space I have a Password= line to allow for setting the password attribute via the "PrincipalMappingProperties" config area.

Also, you mentioned adding the "Password" property to the "PrincipalMappingProperties" section, one last thing to double-check is that it's spelled "password",
following the standard naming style of "first word lower-cased" just as "UserName" is defined as "userName" (at least in the examples I based my setup off of and in my working configs, YMMV).

Apart from that I haven't done anything to the hsql configs, and they seem content to co-exist peacefully.

good luck..

[twhphan]

Thanks Marc, I got the password part working, the case does matter.

I also get the conflict solved; It was due to a mistake in my jboss-web.xml, where I put the <resource-ref /> tag in the wrong place, fixed and working now

[anc623]

> I also get the conflict solved; It was due to a
> mistake in my jboss-web.xml, where I put the
> <resource-ref /> tag in the wrong place, fixed and
> working now

I have the same problem as yours. Can you share me about your jboss-web.xml and web.xml and ejb-jar.xml.
thanks in advance!
eric
[e-mail]

[twhphan]

Hi Eric, my problem appeared only to session beans that attempted to get a data source. If u get org.hsqldb.*, this means that the resource u've in ur code, and ejb-jar.xml don't match with the resource in jboss.xml, or u didn't setup ur resource correctly. If this is the case, by default, JBoss uses the DefaultDS (no transaction one) resource, i.e. the hsql one

In my ejb-jar.xml, I've:
<resource-ref>
<res-ref-name>jdbc/mySQLDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

In my jboss.xml, I've:
<resource-manager>
<res-name>jdbc/mySQLDB</res-name>
<res-jndi-name>java:/mySQLDS</res-jndi-name>
</resource-manager>

In my code, I've:
ds = (DataSource) (new InitialContext()).lookup(java:comp/env/jdbc/mySQLDB);

Make sure u use <resource-manager/> in jboss.xml, this was the part I had problem b4