JDBC Master Slave & MySql

[soloist <v....@gmail.com>]

Hello,
i am trying to get the JDBC M/s Configuration working using mysql. There was
a locking issue on mysql with activemq 4.x so i am using the latest
5.0-SNAPSHOT. In my testcase, Master boots up just fine but when the slave
is booting it is trying to insert some data into the db but it cannot and it
is stuck at that point. When i add useDatabaseLock="false" on slave (i know
doing this is wrong here but i am just trying to get it working) nothing
changes,when i add this to both brokers, slave boots up too but they just
act the same when a message arrives and i get a duplicate key error on db.
So i could not find any workaround for this at the moment. the following is
my master.xml

    <bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"/>

    <broker brokerName="Sauron" useJmx="true" persistent="false"
xmlns="http://activemq.org/config/1.0">
        <transportConnectors>
            <transportConnector uri="tcp://localhost:61616"/>
        </transportConnectors>

        <persistenceAdapter>
            <jdbcPersistenceAdapter dataSource="#mysql-ds" />
        </persistenceAdapter>
        
    </broker>

    <bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url"
value="jdbc:mysql://localhost/activemq?relaxAutoCommit=true"/>
        <property name="username" value="root"/>
        <property name="password" value="mysql"/>
        <property name="poolPreparedStatements" value="true"/>
    </bean>


And here is the slave:
    <bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"/>

    <broker brokerName="Saruman" useJmx="true" persistent="true"
masterConnectorURI="tcp://localhost:61616"
            shutdownOnMasterFailure="false"
            xmlns="http://activemq.org/config/1.0">

        <transportConnectors>
            <transportConnector uri="tcp://localhost:61618"/>
        </transportConnectors>

        <managementContext>
            <managementContext connectorPath="/jmxrmiSLAVE"
createMBeanServer="false"/>
        </managementContext>


        <persistenceAdapter>
            <jdbcPersistenceAdapter dataSource="#mysql-ds" />
        </persistenceAdapter>

    </broker>

    <bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url"
value="jdbc:mysql://localhost/activemq?relaxAutoCommit=true"/>
        <property name="username" value="root"/>
        <property name="password" value="mysql"/>
        <property name="poolPreparedStatements" value="true"/>
    </bean>


And here are the logs when slave wont boot up:

- ActiveMQ 5.0-SNAPSHOT JMS Message Broker (Saruman) is starting
- For help or more information please see: http://activemq.apache.org/
- JMX consoles can connect to
service:jmx:rmi:///jndi/rmi://localhost:1099/jmxrmiSLAVE
- Database driver recognized: [mysql-ab_jdbc_driver]
- Executing SQL: CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER
VARCHAR(250), MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION BIGINT,
MSG LONGBLOB, PRIMARY KEY ( ID ) )
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER VARCHAR(250),
MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION BIGINT, MSG LONGBLOB,
PRIMARY KEY ( ID ) ) Message: Table 'activemq_msgs' already exists SQLState:
42S01 Vendor code: 1050
- Executing SQL: CREATE INDEX ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS
(MSGID_PROD,MSGID_SEQ)
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE INDEX ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)
Message: Duplicate key name 'ACTIVEMQ_MSGS_MIDX' SQLState: 42000 Vendor
code: 1061
- Executing SQL: CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS
(CONTAINER)
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS (CONTAINER) Message:
Duplicate key name 'ACTIVEMQ_MSGS_CIDX' SQLState: 42000 Vendor code: 1061
- Executing SQL: CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS
(EXPIRATION)
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION) Message:
Duplicate key name 'ACTIVEMQ_MSGS_EIDX' SQLState: 42000 Vendor code: 1061
- Executing SQL: CREATE TABLE ACTIVEMQ_ACKS(CONTAINER VARCHAR(250) NOT NULL,
SUB_DEST VARCHAR(250), CLIENT_ID VARCHAR(250) NOT NULL, SUB_NAME
VARCHAR(250) NOT NULL, SELECTOR VARCHAR(250), LAST_ACKED_ID INTEGER, PRIMARY
KEY ( CONTAINER, CLIENT_ID, SUB_NAME))
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE TABLE ACTIVEMQ_ACKS(CONTAINER VARCHAR(250) NOT NULL, SUB_DEST
VARCHAR(250), CLIENT_ID VARCHAR(250) NOT NULL, SUB_NAME VARCHAR(250) NOT
NULL, SELECTOR VARCHAR(250), LAST_ACKED_ID INTEGER, PRIMARY KEY ( CONTAINER,
CLIENT_ID, SUB_NAME)) Message: Table 'activemq_acks' already exists
SQLState: 42S01 Vendor code: 1050
- Executing SQL: CREATE TABLE ACTIVEMQ_LOCK( ID BIGINT NOT NULL, TIME
BIGINT, BROKER_NAME VARCHAR(250), PRIMARY KEY (ID) )
- Could not create JDBC tables; The message table already existed. Failure
was: CREATE TABLE ACTIVEMQ_LOCK( ID BIGINT NOT NULL, TIME BIGINT,
BROKER_NAME VARCHAR(250), PRIMARY KEY (ID) ) Message: Table 'activemq_lock'
already exists SQLState: 42S01 Vendor code: 1050
- Executing SQL: INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1)


these tables are alrady created when the master boots up. Is there any
configuration i am missing here?
Sorry for the big post, and thanks in advance
Mete
-- 
View this message in context: http://www.nabble.com/JDBC-Master-Slave---MySql-tf4689654s2354.html#a13403327
Sent from the ActiveMQ - User mailing list archive at Nabble.com.