Did you mean to file this against MariaDB ColumnStore? This appears to be a MariaDB Server / API issue. If this is misfiled let me know and I can move it for you.

Andrew, you are right its a standard Mariadb server / API issue only. sorry if I did something wrong in the registration process. Nick

No problem, this has been moved to an MDEV ticket.

nick.denning@diegesis.co.uk,
(without saying yet whether it's supposed to work or not),
sorry to start from an obvious question, but are you using a storage engine which supports XA?

We have worked out how to do this.

We are using tomcat and that has the apache geronimo transaction manager included.

We needed to change our data source definitions in the context entries, or example

<Resource id="ipswichdb" type="javax.sql.DataSource">
DataSourceCreator = tomcat
JtaManaged = true
testOnBorrow = true
testOnReturn = false
testWhileIdle = false
validationQuery = SELECT 1

alternateUsernameAllowed = true
JdbcDriver = org.mariadb.jdbc.Driver
jdbcUrl = jdbc:mariadb://vmipswichdbdpe.diegesis.local:3306/ipswichdb?useSSL=true&trustServerCertificate=true
UserName = privileged_user
Password = privileged_password
</Resource>

Background references are:

XA transactions can be handled either by making explicit calls to JTA from the application Java code, or using J2EE container features to handle transactions implicitly. The latter approach seems preferable since J2EE provides those features, and this requires the minimum of application code.

A useful reference on JTA is:

https://www.progress.com/tutorials/jdbc/understanding-jta

and on Container Managed Transactions (CMT):

https://docs.oracle.com/javaee/6/tutorial/doc/bncij.html

https://examples.javacodegeeks.com/enterprise-java/ejb3/transactions/ejb-transaction-management-example/

Declaring Classes and Methods

The class containing the CMT code must be marked as an EJB class using the Stateless annotation:

@Stateless
@TransactionManagement(TransactionManagementType.CONTAINER)
public class MyBean1

REQUIRED is shown here for clarity, but is actually the default.

The data source to be used within the code is accessed using a Resource annotation, referencing the project name and the resource ID used in the resource declaration as shown above.

@Resource(name="IPXaTest/ipswichdb")
private DataSource dataSource;
The code within a method can then access the data source and perform SQL operations as follows:

Connection connection = dataSource.getConnection(USERNAME, PASSWORD);

Statement stmt = connection.createStatement();

stmt.executeUpdate("update PERSON_DETAILS set PERSON_CMM_LEVEL = " + level + " where PERSON_LOGIN_NAME = \'developer\'");

stmt.close();

connection.close();
By default if the method completes successfully then the transaction will be automatically committed when it returns.

If calls are made to other methods which have appropriate transaction attributes, e.g. REQUIRED or SUPPORTED, then they will be enrolled in the transaction, and all updates will either be committed or rolled back together when the top level method returns.

Transactions will be rolled back if a system exception is thrown, or if "setRollbackOnly" is called on the EJB context, allowing a rollback to be requested under application control.

InitialContext initialContext = new InitialContext();
EJBContext sessionContext = (SessionContext) initialContext.lookup("java:comp/EJBContext");
sessionContext.setRollbackOnly();
Methods involving JMS (ActiveMQ) queue objects may be included in a transaction in the same way, by writing methods within an EJB class and access the queue and connection factory as resources within the class.

@Resource(name = "IPXaTest/ipconnfactory")
private ConnectionFactory connFactory;

@Resource(name = "IPXaTest/ipqueue")
private Queue ipqueue;
Methods within that class can then be performed within a transaction in the same way described above.

@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void sendMessage(String text) throws JMSException

Monitoring and Troubleshooting
The following code is useful to obtain the current transaction status, which can then be output for logging purposes to verify that this is being set as expected at any given point, i.e. whether a transaction is active or marked for rollback.

TransactionSynchronizationRegistry tsr = (TransactionSynchronizationRegistry)
new InitialContext().lookup("java:comp/TransactionSynchronizationRegistry");

int status = tsr.getTransactionStatus();

switch (status)

Useful logging information can be obtained if some transaction related logging levels are set in TomEE's logging.properties file, similar to the following:

OpenEJB.level = FINE
Transaction.level = FINE
org.apache.geronimo.level = FINE

OpenEJB.handlers = 5tomee.org.apache.juli.FileHandler, org.apache.tomee.jul.formatter.AsyncConsoleHandler
Transaction.handlers = 5tomee.org.apache.juli.FileHandler, org.apache.tomee.jul.formatter.AsyncConsoleHandler
org.apache.geronimo.handlers = 5tomee.org.apache.juli.FileHandler, org.apache.tomee.jul.formatter.AsyncConsoleHandler
With this logging configuration set, helpful information is output to the log showing when JTA transactions are started and committed:

19-Jun-2018 12:55:21.550 FINE [http-nio-8080-exec-2] org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke EjbObjectProxyHandler: invoking method updatePerson on MyBean1 with identity null

19-Jun-2018 12:55:21.597 FINE [http-nio-8080-exec-2] org.apache.openejb.core.transaction.JtaTransactionPolicy.beginTransaction TX Required: Started transaction org.apache.geronimo.transaction.manager.TransactionImpl@5a7e8d5b

19-Jun-2018 12:55:21.933 FINE [http-nio-8080-exec-2] org.apache.openejb.core.transaction.JtaTransactionPolicy.completeTransaction TX Required: Committing transaction org.apache.geronimo.transaction.manager.TransactionImpl@5a7e8d5b

19-Jun-2018 12:55:21.948 FINE [http-nio-8080-exec-2] org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke EjbObjectProxyHandler: finished invoking method updatePerson. Return value:void
or rolled back:

19-Jun-2018 12:51:39.584 FINE [http-nio-8080-exec-2] org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke EjbObjectProxyHandler: invoking method updatePerson on MyBean1 with identity null

19-Jun-2018 12:51:39.622 FINE [http-nio-8080-exec-2] org.apache.openejb.core.transaction.JtaTransactionPolicy.beginTransaction TX Required: Started transaction org.apache.geronimo.transaction.manager.TransactionImpl@36f371bf

19-Jun-2018 12:51:40.050 FINE [http-nio-8080-exec-2] org.apache.openejb.core.transaction.JtaTransactionPolicy.setRollbackOnly TX Required: setRollbackOnly() on transaction org.apache.geronimo.transaction.manager.TransactionImpl@36f371bf

19-Jun-2018 12:51:40.051 FINE [http-nio-8080-exec-2] org.apache.openejb.core.transaction.JtaTransactionPolicy.rollbackTransaction TX Required: Rolling back transaction org.apache.geronimo.transaction.manager.TransactionImpl@36f371bf

19-Jun-2018 12:51:40.061 FINE [http-nio-8080-exec-2] org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke EjbObjectProxyHandler: finished invoking method updatePerson. Return value:void

Hope this all helps.

All the above investigated by my colleague Dave. Happy to close. Anyone who wants more details of an example eclipse project get in touch.

We have tried this across ActiveMQ and Mariadb as well as just two Mariadb databases.

all my previous comment was there to say was that we had fixed this and given example code of what was needed.

Nick

Thanks for writing it up. Maybe it deserves an article.