Configure ActiveMQ with MySQL


  1. You’ll need to create an activemq configuration file, call it my-activemq.xml say. (Just copy ./conf/activemq.xml and modify it as described here.
  2. Uncomment & update the persistenceAdapter:
    
        <persistenceAdapter>
            <journaledJDBC journalLogFiles="5"
                dataDirectory="${activemq.base}/activemq-data"
                dataSource="#mysql-ds"/>
        </persistenceAdapter>
    
  3. Uncomment the mysql-ds datasource (update the MySQL username/password):
    
      <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=""/>
        <property name="poolPreparedStatements" value="true"/>
      </bean>
    
  4. Get the MySQL Java Connector (aka the driver). Add the connector jar file
    mysql-connector-java-5.0.6-bin.jar
    to the activemq‘s classpath (just copy this jar to the activemq‘s lib/optional folder. Launch the MySQL command line program and create a database for ActiveMQ to use: create database activemq;
  5. You can now start the broker using the xbean option:
    ./bin/activemq xbean:./conf/my-activemq.xml
  6. The broker (aka JMS server) should start without a problem. You can verify that it using the database. Check the activemq database and you’ll see that 3 new tables have been created:
    
    mysql> use activemq; show tables;
    Database changed
    +--------------------+
    | Tables_in_activemq |
    +--------------------+
    | ACTIVEMQ_ACKS      |
    | ACTIVEMQ_LOCK      |
    | activemq_msgs      |
    +--------------------+
    3 rows in set (0.00 sec)
    

    Also visit the ActiveMQ’s admin console on http://localhost:8161/ to make sure that all is well.

29 Responses to “Configure ActiveMQ with MySQL”

  1. Wilson Chung Says:

    I am having difficulty, i followed all the steps, but am getting the following:
    2007-12-03 14:03:33,816 [main ] INFO BrokerService – ActiveMQ 4.1.1 JMS Message Broker (localhost) is starting
    2007-12-03 14:03:33,816 [main ] INFO BrokerService – For help or more information please see: http://incubator.apache.org/activemq/
    2007-12-03 14:03:34,269 [main ] INFO JDBCPersistenceAdapter – Database driver recognized: [mysql-ab_jdbc_driver]
    2007-12-03 14:03:34,316 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:34,863 [JMX connector ] WARN ManagementContext – Failed to start jmx connector: Cannot bind to URL [rmi://localhost:1099/jmxrmi]: javax.naming.ServiceUnavailableException [Root exception is java.rmi.ConnectException: Connection refused to host: localhost; nested exception is:
    java.net.ConnectException: Connection refused: connect]
    2007-12-03 14:03:35,316 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:36,332 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:37,332 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:38,332 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:39,332 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:40,332 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:41,348 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:42,348 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:43,348 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:44,348 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:45,348 [MQ ShutdownHook] INFO BrokerService – ActiveMQ Message Broker (localhost, null) is shutting down
    2007-12-03 14:03:45,348 [MQ ShutdownHook] INFO TransportConnector – Connector tcp://localhost:61616 Stopped
    2007-12-03 14:03:45,441 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:46,441 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker
    2007-12-03 14:03:47,441 [main ] INFO DefaultDatabaseLocker – Attempting to acquire the exclusive lock to become the Master broker

    Any ideas?

    Thanks

  2. sj Says:

    Looks like there may already be some other service running on port 1099, given the error that you’re getting: “Cannot bind to URL [rmi://localhost:1099/jmxrmi]:”. You could also turn off JMX, if you’re not planning to use it. But 1st check to see what other service is running on port 1099. You may also be able to change the port.

  3. Michael Says:

    Hi,

    thanks for your explanation.
    I managed to configure ActiveMQ with MySQL so that the 3 tables (activemq_msgs, …) are actually being created.
    But my problem is that the tables stay empty all the time. If I insert messages in a queue and restart the broker, the queued messages are lost. Do you know how to configure ActiveMQ that the database tables are actually used?

    Thanks!

    This is the configuration I use; i had to use useDatabaseLock=”false” to avoid the problem Wilson Chung described above.

  4. sj Says:

    It sounds like that your configuration is not quite right. Have you configured to use the journaledJDBC? If so, is that configured to work with MySQL? Here is a configuration that I normally use. Also what version of ActiveMQ are you using? I build my own version of 5.0. I know that there are issues with Active MQ 4.1.x with MySQL. I tried to make MySQL work with 4.1.x and I gave up. I prefer 5.0 since the admin console is very handy debugging tool and it works out of the box and does not require hours of configuration and set-up that is needed if you can ever get 4.1.x adminui to work with Tomcat. Unfortunately 5.0 is not a released product yet. But it works great.

  5. Michael Says:

    I tried ActiveMQ 5.0 and the problem stays the same.
    Here is the active mq configuration I use.
    Maybe the problem is because I use mule (here is the mule config)
    and spring within a Tomcat server. When I start the Tomcat the queues are being created.
    If I want to check if ActiveMQ saves the messages after stopping the broker, I need to shut down and restart the Tomcat as well. Then, the ActiveMQ queues are not being created before the Tomcat startup has finished. I suppose that the persistency should work anyway, but I don’t know if using Mule & Tomcat has something to do with the problems.
    Anyway, the persistency still does not work yet. I also tried the DerbyDB, but with the same effect. But with DerbyDB I don’t know yet how to monitor the database to check if the messages have been saved, so I switched to MySQL again.

  6. Michael Says:

    I had a look at the web front end of ActiveMQ to monitor the queues. It seems like the messages which arrive in the queue which is used by Mule are tagged with Persistency=”Non Persistent” whereas messages I insert in the example.A queue are tagged with Persistency=”Persistent”. It seems like I have to configure the queues in order that the messages are persisted. Do you know if and how that can be done?

  7. Michael Says:

    In the meanwhile, I solved the problem.
    If anyone is interested, if using Mule, messages sent to ActiveMQ are non-persistent by default. You have to use client.send(“jms://example.queue?persistentDelivery=true” …), when sending a message via Mule.

  8. sj Says:

    Thanks Michael. This is very useful information. On another activemq related note, turning off Jmx may help you resolve port conflicts if you’ve other J2EE server already running on the same server. In the broker element definition set the attribute useJmx=”false”.

  9. sj Says:

    Watch out for ActiveMQ and MySQL MyISAM tables. You must make sure that your ActiveMQ tables are defined to use the InnoDB storage engine.

  10. Narada Says:

    Hi. Thanks for this post.

    What version are you using?

    I tried “./bin/activemq xbean:./conf/activemq.xml” with 4.1.1 and got: “Caused by: java.io.FileNotFoundException: class path resource [conf/activemq.xml] cannot be opened because it does not exist”. I’m pretty sure it does exist. I even tried using absolute path but no luck.

    Btw your blog seems like a fantastic resource for activemq (and it’s use with mysql). Very helpful.

  11. sj Says:

    Thank you. I run the latest version of ActiveMQ 5.x. Check the file permissions for activemq.xml, perhaps you don’t have the read permission for the user that ./bin/activemq is running as.

  12. gan Says:

    Hi,

    i have configd the activemq file with mysql and it is creating the table.
    but when i try to access the http://localhost:8161/ but it gives a 404 error. what could be wrong?

  13. hm Says:

    Hi,

    Thanks for the valuable information on “ActiveMQ persistence with MySQL” !!

    James, I tried the above configurations with ActiveMQ and MySQL 5.0 and the persistence worked fine. However, after running the producer and consumer many times I still find the tables in the database empty, which makes me feel suspicious of whether the database is being used for persistence or not (or it is using simple file-based persistence). Please, let us know whether the persistent messages can be seen from the tables by normal “select” statement or not.

    Apart from the configurations that you specified, I am trying to give custom names to the tables created as follows:

    Best regards,
    hm

  14. hm Says:

    Hi,

    Just when I submitted the previous query, I was able to resolve it.
    The thing is that the messages are deleted from the database once the they are delivered to the consumer. So, in order to see the stored messages in the database, we have to make sure that they are in the “undelivered” stage.

    Best regards,
    hm

  15. Sidd Says:

    Hi,

    I tried this and I think it works. My question is is there anyway I can store the incoming messages. I am using a Chat application based on ActiveMQ and I cant seem to figure out how to store these chat messages. Please help.

    http://flexonrails.net/?p=88 is the chat application I have customized for my own use but the basics are the same.

  16. pasi haverinen Says:

    I’m trying to get the embeded broker running with following maven2 dependencies defined in my project pom-file

    with the following activemq broker config-file

    <!—->

    <!– Destination specific policies using destination names or wildcards

    ” memoryLimit=”5mb”/>
    ” memoryLimit=”5mb”>

    –>

    <!– The store and forward broker networks ActiveMQ will listen to
    –>
    <!– by default just auto discover the other brokers
    –>
    <!– Example of a static configuration:

    –>
    <!–

    –>


    <!– The maximum about of space the broker will use before slowing down producers

    –>

    <!– –>

    <!– –>


    10

    20

    127.0.0.1

    and referencing the broker

    admin
    password

    but when booting up the container the broker hangs after printing the detection of my-jdbc driver

    INFO – JDBCPersistenceAdapter – Database driver recognized: [mysql-ab_jdbc_driver]

    and even thought I remove the database presistance adapter reference it hangs also

    what do I need to do in order to get it functioning

  17. sj Says:

    Try configuring activemq so that it does not use any database. Note that in some versions, the default persistence is a journal file system PLUS Derby (a java embedded relational database). Configure it so that it uses the Kaha persistence store. FYI, I gave up on embedded broker, since apart from testing, I don’t see any advantages to it. Configuring mater/slave is easier and I think more reliable. But your deployment needs may dictate the embedded option.

  18. seleshmaster Says:

    Thanks, it was very helpful. I got it to work with MySQL.

    I am just curious if this would be the same if I want to configure with Oracle 10g? so all I need to do is put the oracle driver under activemq’s lib/optional folder, the rest would be the same? The project I am working on, i need to integrate ActiveMQ and Oracle. So I am just curious.

    what If if I need to add more columns such as message_type, producer_id in case if i have more than one producer using the queue. Sorry if my questions sound damn, I am new to JMS. thanks in advance for any help.

  19. seleshmaster Says:

    it worked with oracle too. so never mind my question

  20. Niyaz Says:

    Hi,
    I have tryed for setting up the my sql connection but its showing “” attempting to acquire the exclusive lock” service running on port 1099, given the error that you’re getting: “Cannot bind to URL [rmi://localhost:1099/jmxrmi]:”. any one tell me how to change the port where is the properti file how to stop the 1099 port for time being.. for testing purpose or how to allocate new port..

  21. sj Says:

    Hi Niyaz, I’ve not looked at ActiveMQ configurations for a while, but as a quick fix you may try trying off the JMX option. I think the option is usejmx=”false” and is set at the broker element. But search for this option. It appears that you’re already running some other Java process that is using port 1099. So if possible close the other JVM or run ActiveMQ on another machine. But turning off JMX will fix the issue, however, it comes as a cost. You may not be able to use the ActiveMQ admin web console.

  22. David Says:

    Thanks for this article. Very usefull.

  23. Tom Blich Says:

    Using ActiveMQ 5.3.2

    MySQL 5.1.40

    Trying to get JDBC working, it creates the tables, but it doesn’t seem to be happy with how they get created, and then once it starts up, I get a can’t get lock on master message

    Java Runtime: Sun Microsystems Inc. 1.6.0 /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre
    Heap sizes: current=8896k free=7185k max=466048k
    JVM args: -Xmx512M -Dorg.apache.activemq.UseDedicatedTaskRunner=true -Djava.util.logging.config.file=logging.properties -Dcom.sun.management.jmxremote -Dactivemq.classpath=/u0/apache-activemq/conf; -Dactivemq.home=/u0/apache-activemq -Dactivemq.base=/u0/apache-activemq
    ACTIVEMQ_HOME: /u0/apache-activemq
    ACTIVEMQ_BASE: /u0/apache-activemq
    Loading message broker from: xbean:activemq.xml
    INFO | Using Persistence Adapter: JDBCPersistenceAdapter(org.apache.commons.dbcp.BasicDataSource@2911ded2)
    INFO | Database adapter driver override recognized for : [mysql-ab_jdbc_driver] – adapter: class org.apache.activemq.store.jdbc.adapter.MySqlJDBCAdapter
    WARN | Could not create JDBC tables; they could already exist. Failure was: CREATE INDEX ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ) Message: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’ SQLState: HY000 Vendor code: 1598
    WARN | Failure details: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    java.sql.SQLException: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

    at org.apache.activemq.console.Main.main(Main.java:107)
    WARN | Could not create JDBC tables; they could already exist. Failure was: CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS (CONTAINER) Message: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’ SQLState: HY000 Vendor code: 1598
    WARN | Failure details: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    java.sql.SQLException: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

    WARN | Could not create JDBC tables; they could already exist. Failure was: CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION) Message: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’ SQLState: HY000 Vendor code: 1598
    WARN | Failure details: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    java.sql.SQLException: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    WARN | Could not create JDBC tables; they could already exist. Failure was: INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1) Message: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’ SQLState: HY000 Vendor code: 1598
    WARN | Failure details: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’
    java.sql.SQLException: Binary logging not possible. Message: Transaction level ‘READ-UNCOMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’

    INFO | Database lock driver override not found for : [mysql-ab_jdbc_driver]. Will use default implementation.
    INFO | Attempting to acquire the exclusive lock to become the Master broker
    INFO | Failed to acquire lock. Sleeping for 1000 milli(s) before trying again…
    INFO | Failed to acquire lock. Sleeping for 1000 milli(s) before trying again…

  24. High Availability ActiveMQ using a MySQL datastore | Barking Iguana Says:

    [...] turns out that this is really easy to setup. First, configure ActiveMQ to use MySQL then make sure you’re using InnoDB. The only change I made to these instructions was to [...]

  25. Gil Standen Says:

    Thanks Shahram!! Great post and very useful. I modified your original post to connect to Oracle (happens to be 11gR2) and it connects fine to Oracle and creates the 3 tables. The thing I don’t understand is that now I cannot get into my ActiveMQ web console (http://localhost:8161) – why is that – do you know? The only difference afaik is that I’m using Oracle instead of KahaDB. Thanks Again – thanks to your post – progress! Gil

  26. Gil Standen Says:

    Just an update to my post. I got the ActiveMQ web console working. I’m not sure exactly why it is working now. What I did was take my activemq-jdbc.xml and kind of merge it into the activemq.xml (which did have a working web console) and in so-doing somehow preserved the parts of activemq.xml which were responsible for starting the web page admin console (and the camel admin console) while also integrating in the necessary code to connect into the persistent store in the Oracle 11gR2 database. Also, now that I have the ActiveMQ web admin console, I was able to send individual messages using the web admin send messages page and the messages do appear and persist in activemq_msgs (as long as you check the “persistent” box on the message send web page!). One thing though – “create queue” page on admin web page throws exception and does not create queue. Perhaps this is because I need to create queues with pl/sql on the Oracle side and then register them with ActiveMQ? I mean, activemq_msgs from an Oracle standpoint is a “queue table” and now it needs a “queue” which references that table, and perhaps also a propagation job created on the Oracle side (next steps if I can figure that out!). Thanks again for this original post and the comments in this thread – I would not have gotten even this far with the activemq-Oracle hookup without these posts! Thanks Gil

  27. CPR Says:

    An update for 2011, see http://activemq.2283324.n4.nabble.com/Failover-using-journaled-JDBC-tp3535444p3535538.html

    persistenceAdapter becomes persistenceFactory to pass the now default validation.

  28. c++ and mysql Says:

    c++ and mysql…

    [...]Configure ActiveMQ with MySQL « n o t e 1 9 . c o m[...]…

  29. androidboost Says:

    After modify in xml .Im getting java.Runtime.Exception while starting activemq broker.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: