Specifying the schema for EJBTimerService's TIMERS database

[jonathanztaub]

I'm trying to configure all of JBoss' related services database table to reside in their own schema (jboss). The database with the name of "jboss" was created and I was able to change successfully all of the JMS' configuration files to refer to this schema when performing create/insert/delete/select operations. This was easily accomplished by adding the schema name followed by a dot before the table's name.

However, for the EJBTimerService, it doesn't seem to work:

 <!--
 A persistence policy that persists timers to a database.

 The 2 supported db persistence plugins are:
 org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin
 org.jboss.ejb.txtimer.OracleDatabasePersistencePlugin

 The table name defaults to "TIMERS". It can be overriden using the
 'TimersTable' attribute if the persistence plugin supports it.
 When overriding the timers table, an optional schema can be specified
 using the syntax [schema.]table
 -->
 <mbean code="org.jboss.ejb.txtimer.DatabasePersistencePolicy" name="jboss.ejb:service=EJBTimerService,persistencePolicy=database">
 <!-- DataSourceBinding ObjectName -->
 <depends optional-attribute-name="DataSource">jboss.jca:service=DataSourceBinding,name=MySqlDs</depends>
 <!-- The plugin that handles database persistence -->
 <attribute name="DatabasePersistencePlugin">org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin</attribute>
 <!-- The timers table name -->
 <attribute name="TimersTable">jboss.TIMERS</attribute>
 </mbean>

Upon startup, I'm getting:

17:39:46,459 WARN [ServiceController] Problem starting service jboss.ejb:service=EJBTimerService,persistencePolicy=database
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Incorrect table name 'jboss'

Any ideas?

[jaikiran]

I think the other way of doing this (at one common place) is to specify the schema name in the datasource itself. So in your case, the -ds.xml which is used to configure your datasouce:

jboss.jca:service=DataSourceBinding,name=MySqlDs

can be configured as follows:

<connection-url>jdbc:mysql://localhost:3306/jboss</connection-url>

[jaikiran]

Btw, i just read the xml contents that you posted. I has this:

When overriding the timers table, an optional schema can be specified
using the syntax [schema.]table

So try changing the TimersTable attribute to:

<attribute name="TimersTable">[jboss.]TIMERS</attribute>

[dimitris]

The brackets are there to denote the schema is optional :-)

My guess is this is not supported in mysql.

[jaikiran]

The brackets are there to denote the schema is optional :-)

Oops, my bad! :-)

[jonathanztaub]

Thanks guys but I already tried all of the above.
MySQL does support the schema.table notation.
In my mysql-ds.xml, I've configured the default database which is other than jboss. I guess I can configure the default database to be jboss and explicitly state the schema name in my data access objects.
However, it seems like it was working for all JMS related file configurations but just not for the EJBTimeService which is really annoying.
Surely I'm missing something or perhaps this is a bug.

[chrismeadows]

FWIW, we've also had many problems with this same table, and we're using oracle. Specifying the schema name appears to be redundant - it is ignored.

We found that we had to make sure the table name was unique across ALL schemas in the database. That is, we had a schema per developer in the dev environment, and we had to make sure each of us used a different table name.

Something very screwy with EJB Timer persistence