Configuring MySQL

For all known product versions, no special XA configuration is required on the database side. It should work out-of-the-box.

Tomcat context.xml JNDI example

<Resource name="jdbc/YourDbPool" auth="Container" 
 driverClassName="com.mysql.jdbc.Driver" 
 password="yourdbpass" 
 type="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"  
 factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory"
 url="jdbc:mysql://server.example.com:3306/" explicitUrl="true"
 pinGlobalTxToPhysicalConnection="true"
 user="yourdbuser">
</Resource>

MySQL 8 and higher

Prior to MySQL 8.0, any user could execute the XA RECOVER statement to discover the XID values for outstanding prepared XA transactions. In MySQL 8.0, XA RECOVER is permitted only to users who have the XA_RECOVER_ADMIN privilege, which is expected to be granted only to administrative users who have need for it.

GRANT XA_RECOVER_ADMIN ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

More information: see https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_xa-recover-admin and https://stackoverflow.com/questions/56831529/configuring-a-xa-datasource-to-mysql-8-db-with-spring-boot-and-bitronix-jta-mana

Notes

Tested with Tomcat 5.5, MySQL 5.0.2, and JDBC driver MySQL Connector 5.1.5.

Calling initialContext.lookup(poolname) may return a null DataSource; use initialContext.lookupLink(poolname) instead.

-- JennyBrown - 1 Dec 2008