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

    MySqlDS: lost all tables (and data)!

    giulio massa Newbie

      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!