ERROR in the console when creating tables in Oracle during the 1st launch

[ediseo]

Hi,

I'm shifting my app from JBoss 6 to JBoss 7. But during the the 1st launch, when JPA using hibernate to creating tables, I'm getting the error as below:

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-4) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-1) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-5) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-6) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-3) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-8) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-2) HHH000262: Table not found: T_BMSC_EVENT

10:59:13,170 INFO  [java.sql.DatabaseMetaData] (MSC service thread 1-7) HHH000262: Table not found: T_BMSC_EVENT

10:59:14,667 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-5) HHH000388: Unsuccessful: create table T_BMSC_EVENT (id number(19,0) not null, CONTENT_GROUP_ID number(19,0), CONTENT_URI varchar2(255 char), DELIVERY_INSTANCE_ID number(19,0), DELIVERY_SESSION_ID number(19,0), DESCRIPTION varchar2(255 char), EMBMS_SESSION_ID number(19,0), EXTENSION varchar2(255 char), MD5_VALUE varchar2(255 char), NOTIFICATION_TYPE varchar2(255 char) not null, RETRY_START_TIME timestamp not null, RETRY_TIMES number(10,0) not null, RETRY_UPDATE_TIME timestamp not null, primary key (id))

10:59:14,667 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-6) HHH000388: Unsuccessful: create table T_BMSC_EVENT (id number(19,0) not null, CONTENT_GROUP_ID number(19,0), CONTENT_URI varchar2(255 char), DELIVERY_INSTANCE_ID number(19,0), DELIVERY_SESSION_ID number(19,0), DESCRIPTION varchar2(255 char), EMBMS_SESSION_ID number(19,0), EXTENSION varchar2(255 char), MD5_VALUE varchar2(255 char), NOTIFICATION_TYPE varchar2(255 char) not null, RETRY_START_TIME timestamp not null, RETRY_TIMES number(10,0) not null, RETRY_UPDATE_TIME timestamp not null, primary key (id))

10:59:14,667 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-3) HHH000388: Unsuccessful: create table T_BMSC_EVENT (id number(19,0) not null, CONTENT_GROUP_ID number(19,0), CONTENT_URI varchar2(255 char), DELIVERY_INSTANCE_ID number(19,0), DELIVERY_SESSION_ID number(19,0), DESCRIPTION varchar2(255 char), EMBMS_SESSION_ID number(19,0), EXTENSION varchar2(255 char), MD5_VALUE varchar2(255 char), NOTIFICATION_TYPE varchar2(255 char) not null, RETRY_START_TIME timestamp not null, RETRY_TIMES number(10,0) not null, RETRY_UPDATE_TIME timestamp not null, primary key (id))

10:59:14,667 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-4) HHH000388: Unsuccessful: create table T_BMSC_EVENT (id number(19,0) not null, CONTENT_GROUP_ID number(19,0), CONTENT_URI varchar2(255 char), DELIVERY_INSTANCE_ID number(19,0), DELIVERY_SESSION_ID number(19,0), DESCRIPTION varchar2(255 char), EMBMS_SESSION_ID number(19,0), EXTENSION varchar2(255 char), MD5_VALUE varchar2(255 char), NOTIFICATION_TYPE varchar2(255 char) not null, RETRY_START_TIME timestamp not null, RETRY_TIMES number(10,0) not null, RETRY_UPDATE_TIME timestamp not null, primary key (id))

10:59:14,683 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-3) ORA-00955: name is already used by an existing object

10:59:14,667 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-6) ORA-00955: name is already used by an existing object

10:59:15,104 INFO  [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-1) HHH000232: Schema update complete

It seems there're object exist conflict when some threads trying to create tables.

But actually, the database is a newly created schema.

And it's really strange that there's no trace information coming right after the "Unsuccessful:..." log, even I update the log level to DEBUG or TRACE.

Although there're so many ERRORs, the tables are created anyway.

And I noticed that the thread MSC service thread 1-1 never comes the ERROR.

I'm confused on this problem.

It seems other threads are impacted by the result of the 1st thread MSC service thread 1-1.

To verify this guess, I'd like to change the total number of MSC service thread to 1 if possible.

Could anyone help me on this problem?

1. How to change the max thread number of MSC service?

2. Why do those ERRORs come?

Thanks,

[ediseo]

I found the problem.

In my standalone.xml, a config like

<subsystem xmlns="urn:jboss:domain:datasources:1.1">

     <datasources>

                <datasource jndi-name="java:/ProvManagerDS" pool-name="ProvManagerDS" enabled="true" use-java-context="true">

                   <connection-url>jdbc:oracle:thin:@localhost:1521:embms</connection-url>

                    <driver>ojdbc6.jar</driver>

                    <pool>

                        <min-pool-size>10</min-pool-size>

                        <max-pool-size>100</max-pool-size>

                    </pool>

....

               </datasource>

     </datasources>

</subsystem>

The configure of database connection pool cause this problem.

After I change the size to 1 for both min&max value, the ERROR will NOT show anymore.

I tried to remove the config of pool, but it seems JBoss will pick a default value other than 1.

Thus the ERROR will still come.

Is this a BUG?

[erasmomarciano]

I have read the documentation

max-pool-size:	The max-pool-size element specifies the maximum number of connections for a pool. No more connections will be created in each sub-pool
min-pool-size:	The min-pool-size element specifies the minimum number of connections for a pool
pool-prefill:	Should the pool be prefilled. Changing this value can be done only on disabled datasource, requires a server restart otherwise.
pool-use-strict-min:	Specifies if the min-pool-size should be considered strictly

I don't found the mandatory tag <pool>.

If you want can you create a datasource  via web interface and redoing the test.

looked up datasouce.xsd

<xs:complexType name="poolType">

    <xs:sequence>

      <xs:element name="min-pool-size" type="xs:nonNegativeInteger" minOccurs="0">

        <xs:annotation>

          <xs:documentation>

            <![CDATA[[

              The min-pool-size element indicates the minimum number of connections

              a pool should hold. These are not created until a Subject is known from a

              request for a connection. This default to 0. Ex: <min-pool-size>1</min-pool-size>

             ]]>

          </xs:documentation>

        </xs:annotation>

      </xs:element>

      <xs:element name="max-pool-size" type="xs:nonNegativeInteger" minOccurs="0">

        <xs:annotation>

          <xs:documentation>

            <![CDATA[[

              The max-pool-size element indicates the maximum number of connections

              for a pool. No more connections will be created in each sub-pool.

              This defaults to 20.

             ]]>

          </xs:documentation>

        </xs:annotation>

[smarlow]

Show us the contents of your persistence.xml file(s).

[ediseo]

I have read the documentation

max-pool-size:	The max-pool-size element specifies the maximum number of connections for a pool. No more connections will be created in each sub-pool
min-pool-size:	The min-pool-size element specifies the minimum number of connections for a pool
pool-prefill:	Should the pool be prefilled. Changing this value can be done only on disabled datasource, requires a server restart otherwise.
pool-use-strict-min:	Specifies if the min-pool-size should be considered strictly

I don't found the mandatory tag <pool>.

If you want can you create a datasource  via web interface and redoing the test.

looked up datasouce.xsd

<xs:complexType name="poolType">

    <xs:sequence>

      <xs:element name="min-pool-size" type="xs:nonNegativeInteger" minOccurs="0">

        <xs:annotation>

          <xs:documentation>

            <![CDATA[[

              The min-pool-size element indicates the minimum number of connections

              a pool should hold. These are not created until a Subject is known from a

              request for a connection. This default to 0. Ex: <min-pool-size>1</min-pool-size>

             ]]>

          </xs:documentation>

        </xs:annotation>

      </xs:element>

      <xs:element name="max-pool-size" type="xs:nonNegativeInteger" minOccurs="0">

        <xs:annotation>

          <xs:documentation>

            <![CDATA[[

              The max-pool-size element indicates the maximum number of connections

              for a pool. No more connections will be created in each sub-pool.

              This defaults to 20.

             ]]>

          </xs:documentation>

        </xs:annotation>

Thanks for reply.

I go though the jboss-as-datasources_1_0.xsd and jboss-as-datasources_1_1.xsd, there is the definition of tag <pool>. It's not mandatory indeed.

<xs:element name="pool" type="poolType" minOccurs="0" maxOccurs="1">

        <xs:annotation>

          <xs:documentation>

            <![CDATA[[

              Specifies the pooling settings

             ]]>

          </xs:documentation>

        </xs:annotation>

      </xs:element>

As I mentioned, if I remove the <pool> part, the ERROR still comes. I guess JBoss7 will pick default value.

I also tried on JBoss 6.1.0 Final, the ERROR will come with this <pool>, but works fine if removing this config.

[ediseo]

Scott Marlow wrote:

Show us the contents of your persistence.xml file(s).

Please check the below one.

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="DeliverySessionDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.DeliverySessionEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

                              <property name="hibernate.show_sql" value="true" />

        </properties>

    </persistence-unit>

    <!---->

    <persistence-unit name="ProvConfigDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.ProvisionConfigEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

                              <property name="hibernate.show_sql" value="true" />

        </properties>

    </persistence-unit>

    <persistence-unit name="EmbmsSessionDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.EmbmsSessionEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="DeliverySessionInstanceDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.DeliverySessionInstanceEntity</class>

        <class>com.ecds.bcc.prov.data.FECInfoEntity</class>

        <class>com.ecds.bcc.prov.data.FileRepairEntity</class>

        <class>com.ecds.bcc.prov.data.ReceptionReportEntity</class>

        <class>com.ecds.bcc.prov.data.ControlFragmentEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="DeliveryContentDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.DeliveryContentEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="ContentDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.ContentEntity</class>

        <class>com.ecds.bcc.prov.data.SuccessReportEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="ServiceAreaDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.ServiceAreaEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="ProvisioningLockDatabaseManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.ProvisioningLockEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="GatewayInfoDbManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.GatewayInfoEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="BmscEventDbManager">

        <jta-data-source>java:/ProvManagerDS</jta-data-source>

        <class>com.ecds.bcc.prov.data.BmscEventEntity</class>

        <properties>

            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.generate_statistics" value="true" />

            <property name="hibernate.archive.autodetection" value="class"/>

        </properties>

    </persistence-unit>

</persistence>

[smarlow]

In AS7, each of your persistence unit definitions are being initialized from a different thread (several at the same time).  This is a change from AS6, where the persistence units were all created from the same thread (one at a time).

While the persistence unit definitions are being deployed in parallel, it looks like a common table (T_BMSC_EVENT) is being initialized by several of the persistence units.

You said that your expecting Hibernate to create the tables for you, since this is the first launch.  Does that mean that you just need to work around this error and normally, the tables will already exist during startup?

It probably wouldn't be very difficult to patch Hibernate for your situation.  I have some ideas that I could share, if you want to contribute a patch to hibernate.org for this situtation. 

One obvious workaround would be to not depend on the "hibernate.hbm2ddl.auto" property for creating the application tables (pre-create them before starting the application).

Another workaround which you already found and mentioned above, is to force the "hibernate.hbm2ddl.auto" action to be performed atomically by only one thread at a time.  That is what happened when you changed the ProvManagerDS definition to set min/max to 1 database connection.  With only one database connection being available, that ensures that only one thread is trying to create the T_BMSC_EVENT table at a time).

Scott

[ediseo]

Scott Marlow wrote:

In AS7, each of your persistence unit definitions are being initialized from a different thread (several at the same time).  This is a change from AS6, where the persistence units were all created from the same thread (one at a time).

While the persistence unit definitions are being deployed in parallel, it looks like a common table (T_BMSC_EVENT) is being initialized by several of the persistence units.

You said that your expecting Hibernate to create the tables for you, since this is the first launch.  Does that mean that you just need to work around this error and normally, the tables will already exist during startup?

It probably wouldn't be very difficult to patch Hibernate for your situation.  I have some ideas that I could share, if you want to contribute a patch to hibernate.org for this situtation. 

One obvious workaround would be to not depend on the "hibernate.hbm2ddl.auto" property for creating the application tables (pre-create them before starting the application).

Another workaround which you already found and mentioned above, is to force the "hibernate.hbm2ddl.auto" action to be performed atomically by only one thread at a time.  That is what happened when you changed the ProvManagerDS definition to set min/max to 1 database connection.  With only one database connection being available, that ensures that only one thread is trying to create the T_BMSC_EVENT table at a time).

Scott

The T_BMSC_EVENT table is not a common table. That's where the problem is.

I just cut part of my log, in fact, the ERRORs will come for all tables' creation.

And what's more, the tables will be created by one of the threads, although there're ERRORs for other threads.

It's really strange that if I just create one table in a test app, there's no such ERROR.

[smarlow]

What happens if you comment out the "hibernate.hbm2ddl.auto" property in all but one persistence unit?  Do all of the tables get created?  Or just the tables used for mapping that peristence unit entities to the database?

[ediseo]

Scott Marlow wrote:

What happens if you comment out the "hibernate.hbm2ddl.auto" property in all but one persistence unit?  Do all of the tables get created?  Or just the tables used for mapping that peristence unit entities to the database?

Amazing....

If I keep only one "hibernate.hbm2ddl.auto" for the 1st persist-unit and remove the reset, it works fine. No errors come out and all tables are created.

I'll try to make some test to verify whether those persist unit without "hibernate.hbm2ddl.auto" works if my app tries to persist some new data in the table. Should work~~

But I still feel confused. The property should belong to a single persist unit. mmmm....

[ediseo]

I just tried the persist operation, it turns out to be OK just as I guess.

Still, really confused...