Unknown system variable 'tx_isolation'

Issue

After update database service to MySQL 8 in PaaS Liferay can not connect to database due to unknown system variable 'tx_isolation' error.

[dxp] WARN  [main][PoolBase:452] HikariPool-1 - Default transaction isolation level detection failed (Unknown system variable 'tx_isolation').

[dxp] ERROR [main][HikariPool:541] HikariPool-1 - Exception during pool initialization.

java.sql.SQLException: Unknown system variable 'tx_isolation'
	at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:149)
	at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
	at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:268)
	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:296)
	at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:349)
	at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:408)
	at org.mariadb.jdbc.MySQLConnection.getTransactionIsolation(MySQLConnection.java:410)
	at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:446)
	at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:405)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:363)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443)
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514)
	at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:111)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
	at com.liferay.portal.dao.jdbc.util.DBInfoUtil.lambda$getDBInfo$0(DBInfoUtil.java:47)
	at com.liferay.petra.concurrent.ConcurrentMapperHashMap.lambda$computeIfAbsent$1(ConcurrentMapperHashMap.java:114)
	at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660)
	at com.liferay.petra.concurrent.ConcurrentMapperHashMap.computeIfAbsent(ConcurrentMapperHashMap.java:111)
	at com.liferay.portal.dao.jdbc.util.DBInfoUtil.getDBInfo(DBInfoUtil.java:44)
	at com.liferay.portal.spring.hibernate.DialectDetector.getDialect(DialectDetector.java:51)
	at com.liferay.portal.dao.init.DBInitUtil._initDataSource(DBInitUtil.java:226)

Environment

DXP 7.2
DXP 7.3 below Update 34
DXP 7.4 below U92

Resolution

Open a ticket to support team requiring this LPS-196231
An alternative, as a workaround until Liferay image fixes this error updating the mariadb driver, follow the steps below:

- Add the most up-to-date MariaDB Connector in liferay/configs/ENV/tomcat/lib/ext/mariadb.jar, the name must be mariadb.jar
- In the LCP.json file, add the following environment variable (replace "lportal" with whatever your database name is in that environment):
```
"LIFERAY_JDBC_PERIOD_DEFAULT_PERIOD_URL":"jdbc:mysql://database--route/lportal?characterEncoding=UTF-8&dontTrackOpenResources=true&holdResultsOpenOverStatementClose=true&permitMysqlScheme=true&serverTimezone=GMT&useFastDateParsing=false&useUnicode=true"
```
  (*) This step may not be necessary if the URL is already set as the default value. Please check the startup logs to confirm whether this value is already configured.