ActiveMQ Classic JDBC Master Slave persistent issue with JDBC unable to lock the MySQL DB using MySQL 8 connector

When using just JDBC as the data source you can use a master-slave approach running two brokers (i.e. one for the master and another for the slave). I'm using the MySQL 8 connector for MySQL 5.7 and 8 clients. Below is the ActiveMQ configuration file:

<beans
  xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
  http://activemq.apache.org/schema/core http://activemq.apache.org/schema/core/activemq-core.xsd">

     <broker xmlns="http://activemq.apache.org/schema/core" brokerName="localhost" dataDirectory="${activemq.data}">

      <persistenceAdapter>
                <!-- <kahaDB directory="${activemq.data}/kahadb"/> -->
                 <jdbcPersistenceAdapter dataDirectory="activemq.data" dataSource="#mysql-ds"/>
        </persistenceAdapter>
    </broker>


    <bean id="mysql-ds" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://192.xx.x.xx:3306/activemq?serverTimezone=UTC"/>
        <property name="username" value="admin"/>
        <property name="password" value="admin"/>
        <property name="poolPreparedStatements" value="true"/>
    </bean>

While starting the service when below error that unable to lock the DB.

Using Persistence Adapter: JDBCPersistenceAdapter(org.apache.commons.dbcp2.BasicDataSource@6bd16207) | org.apache.activemq.broker.BrokerService | main
Starting Persistence Adapter: JDBCPersistenceAdapter(org.apache.commons.dbcp2.BasicDataSource@6bd16207) | org.apache.activemq.broker.BrokerService | main
Database adapter driver override not found for : [mysql_connector_j].  Will use default implementation. | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
Database lock driver override not found for : [mysql_connector_j].  Will use default implementation. | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main

With mysql-connector-java-5.1.49 and activemq-jdbc-store-5.13.2 it's working without any issues. However, when I am upgrading the connector and jdbc store to mysql-connector-j-8.0.31 and activemq-jdbc-store-5.17.1 respectively I started this issue.

Solution

Below is the configuration to handle the HA with DB locking

<broker xmlns="http://activemq.apache.org/schema/core" brokerName="192.xx.xx.123" dataDirectory="${activemq.data}">    
    <persistenceAdapter>
        <jdbcPersistenceAdapter dataDirectory="${activemq.data}" dataSource="#mysql-ds" lockKeepAlivePeriod="15000">
            <locker>
                <lease-database-locker lockAcquireSleepInterval="30000"/>
            </locker>
        </jdbcPersistenceAdapter>
    </persistenceAdapter>    
</broker>
    
<bean id="mysql-ds" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://192.xx.xx.1:3306,192.xx.xx.2:3306/activemq?useJDBCCompliantTimezoneShift=true&amp;relaxAutoCommit=true&amp;failOverReadOnly=false"/>
    <property name="poolPreparedStatements" value="true"/>
</bean>