7 Replies Latest reply on Jul 22, 2008 11:32 AM by jaikiran

    MySqlDS: lost all tables (and data)!

    giaulo

      Hi, I'm using Mysql as default data source. My EJB3.0 app runs and inserts correctly the data in the database, but when I restart JBoss (4.2.2.GA) all tables are destroyed (and with them all data)! Only one table continues to exists after the shutdown, and this is very strange...
      Why this appens?

      This is my /deploy/mysql-ds.xml :

      <?xml version="1.0" encoding="UTF-8"?>
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>DefaultDS</jndi-name>
       <connection-url>jdbc:mysql://192.168.0.1:3306/AddressBook</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>root</user-name>
       <password>root</password>
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>
      


      deploy-hasingleton/jms/mysql-jdbc2-service.xml :

      <?xml version="1.0" encoding="UTF-8"?>
      <server>
       <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
       <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
       <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
       <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
       </mbean>
      
      
       <mbean code="org.jboss.mq.server.MessageCache"
       name="jboss.mq:service=MessageCache">
       <attribute name="HighMemoryMark">50</attribute>
       <attribute name="MaxMemoryMark">60</attribute>
       <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
       </mbean>
      
       <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
       name="jboss.mq:service=PersistenceManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
       <attribute name="SqlProperties">
       BLOB_TYPE=BYTES_BLOB
       INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
       INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
       SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
       SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
       DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
       SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
       SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM JMS_MESSAGES WHERE DESTINATION=?
       SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
       MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
       UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
       DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?
       DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
       DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
       DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
       DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
       CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONGBLOB, PRIMARY KEY (MESSAGEID, DESTINATION))
       CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
       CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
       CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
       CREATE_TABLES_ON_STARTUP = TRUE
       </attribute>
       <!-- Uncomment to override the transaction timeout for recovery per queue/subscription, in seconds -->
       <!--attribute name="RecoveryTimeout">0</attribute-->
       <!-- The number of blobs to load at once during message recovery -->
       <attribute name="RecoverMessagesChunk">1</attribute>
       </mbean>
      
      </server>
      


      deploy-hasingleton/jms/hsqldb-jdbc-state-service.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <server>
       <!-- A Statemanager that stores state in the database -->
       <mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager"
       name="jboss.mq:service=StateManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
       <attribute name="SqlProperties">
       CREATE_TABLES_ON_STARTUP = TRUE
       CREATE_USER_TABLE = CREATE TABLE JMS_USERS (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, \
       CLIENTID VARCHAR(128) NULL, PRIMARY KEY(USERID))
       CREATE_ROLE_TABLE = CREATE TABLE JMS_ROLES (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, \
       PRIMARY KEY(USERID, ROLEID))
       CREATE_SUBSCRIPTION_TABLE = CREATE TABLE JMS_SUBSCRIPTIONS (CLIENTID VARCHAR(128) NOT NULL, \
       SUBNAME VARCHAR(128) NOT NULL, TOPIC VARCHAR(255) NOT NULL, \
       SELECTOR VARCHAR(255) NULL, PRIMARY KEY(CLIENTID, SUBNAME))
       GET_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       LOCK_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       GET_SUBSCRIPTIONS_FOR_TOPIC = SELECT CLIENTID, SUBNAME, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE TOPIC=?
       INSERT_SUBSCRIPTION = INSERT INTO JMS_SUBSCRIPTIONS (CLIENTID, SUBNAME, TOPIC, SELECTOR) VALUES(?,?,?,?)
       UPDATE_SUBSCRIPTION = UPDATE JMS_SUBSCRIPTIONS SET TOPIC=?, SELECTOR=? WHERE CLIENTID=? AND SUBNAME=?
       REMOVE_SUBSCRIPTION = DELETE FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       GET_USER_BY_CLIENTID = SELECT USERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID=?
       GET_USER = SELECT PASSWD, CLIENTID FROM JMS_USERS WHERE USERID=?
       POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
       POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
       POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
       POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
       POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
       POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
       POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
       POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
       POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
       POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
       POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
       POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
       POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
       POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')
       </attribute>
       </mbean>
      
      </server>
      


      /conf/standardjbosscmp-jbdc.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE jbosscmp-jdbc PUBLIC
       "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN"
       "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">
      
      <!-- ===================================================================== -->
      <!-- -->
      <!-- Standard JBossCMP-JDBC Configuration -->
      <!-- -->
      <!-- ===================================================================== -->
      <jbosscmp-jdbc>
      
       <defaults>
       <datasource>java:/DefaultDS</datasource>
       <!-- optional since 4.0 <datasource-mapping>Hypersonic SQL</datasource-mapping> -->
      
       <create-table>true</create-table>
       <remove-table>false</remove-table>
       <read-only>false</read-only>
       <read-time-out>300000</read-time-out>
       <row-locking>false</row-locking>
       <pk-constraint>true</pk-constraint>
       <fk-constraint>false</fk-constraint>
       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
       <read-ahead>
       <strategy>on-load</strategy>
       <page-size>1000</page-size>
       <eager-load-group>*</eager-load-group>
       </read-ahead>
       <list-cache-max>1000</list-cache-max>
       <clean-read-ahead-on-load>false</clean-read-ahead-on-load>
      


      Please help me!


        • 1. Re: MySqlDS: lost all tables (and data)!
          jaikiran

           

          My EJB3.0 app runs and inserts correctly the data in the database


          Are you using JPA (with persistence.xml)?


          • 2. Re: MySqlDS: lost all tables (and data)!
            giaulo

            Yes, I'm using JPA in my code.
            This is the persistence.xml:

            <?xml version="1.0" encoding="UTF-8"?>
            <persistence version="1.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_1_0.xsd">
             <persistence-unit name="AddressBook-ejbPU" transaction-type="JTA">
             <jta-data-source>java:/DefaultDS</jta-data-source>
             <properties>
             <property name="hibernate.hbm2ddl.auto" value="update"/>
             </properties>
             </persistence-unit>
            </persistence>


            Help me...

            • 3. Re: MySqlDS: lost all tables (and data)!
              giaulo

              I've solved removing the permission for root@<bind adddress> to "drop" tables in my database. But it's not an elegant way...

              • 4. Re: MySqlDS: lost all tables (and data)!
                jaikiran

                 

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


                When you have set this property to "update", the tables should not have been dropped. Try this, start JBoss with your application, see if the tables are created. Then stop JBoss, see if the tables are dropped. That will tell us when the drop statements are being executed.

                Also, add the hibernate.show_sql property to your persistence-unit as follows:
                <properties>
                 <property name="hibernate.hbm2ddl.auto" value="update"/>
                 <property name="hibernate.show_sql" value="true"/>
                
                 </properties>


                This will print out, in server.log, the SQLs being fired. Keep an watch on the server.log file to see when the drop statements are fired.


                • 5. Re: MySqlDS: lost all tables (and data)!
                  giaulo

                   


                  When you have set this property to "update", the tables should not have been dropped.

                  But this happens.

                  Try this, start JBoss with your application, see if the tables are created.

                  Yes.

                  Then stop JBoss, see if the tables are dropped.

                  Yes they were dropped during the shutdown.


                  • 6. Re: MySqlDS: lost all tables (and data)!
                    jaikiran

                    Can you please post these logs.

                    • 7. Re: MySqlDS: lost all tables (and data)!
                      jaikiran

                      And are you sure, the hibernate.hbm2ddl.auto is not set to create-drop, on the server instance where you are testing this? If you post the server.log contents (including the startup logs), then it might provide some inputs.