Audit addon not supported on MySql 8.4

malexio · November 5, 2024, 6:15pm

Hi all!
We encountered a problem while deploying a Jmix app on a Mysql 8.4 database.

The problem is related to the liquibase changelog of the audit addon:

Caused by: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MANUAL TINYINT(1) NULL, CONSTRAINT PK_AUDIT_LOGGED_ENTITY PRIMARY KEY (ID), UNIQ' at line 1 [Failed SQL: (1064) CREATE TABLE WLB.AUDIT_LOGGED_ENTITY (ID char(36) NOT NULL, CREATE_TS datetime NULL, CREATED_BY VARCHAR(255) NULL, NAME VARCHAR(100) NULL, AUTO TINYINT(1) NULL, MANUAL TINYINT(1) NULL, CONSTRAINT PK_AUDIT_LOGGED_ENTITY PRIMARY KEY (ID), UNIQUE (NAME))]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:470) ~[liquibase-core-4.25.0.jar:na]
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77) ~[liquibase-core-4.25.0.jar:na]
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:179) ~[liquibase-core-4.25.0.jar:na]
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1291) ~[liquibase-core-4.25.0.jar:na]
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1273) ~[liquibase-core-4.25.0.jar:na]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:755) ~[liquibase-core-4.25.0.jar:na]
    ... 58 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MANUAL TINYINT(1) NULL, CONSTRAINT PK_AUDIT_LOGGED_ENTITY PRIMARY KEY (ID), UNIQ' at line 1

Follows my framework information:
Jmix version 2.3, 2.4
DBMS: Mysql ver 8.4.3

sh-5.1# mysql --version 
mysql  Ver 8.4.3 for Linux on aarch64 (MySQL Community Server - GPL)
sh-5.1#

As you can see here “MANUAL” is a reserved keyword for new MySql versions:
https://dev.mysql.com/doc/refman/8.4/en/keywords.html#keywords-8-4-detailed-M

We downgraded MySql to 8.0.x in order to let the app work, but I would like to know how we can handle these type of issues, and of course notify you about this topic!

Have a great day,
Ale

a.oblozhko · November 6, 2024, 5:19am

Hi, Alessandro

MySQL v.8 brings a lots of breaking changes. If you want to just go with modern software versions take look at MariaDB as this fork stays fully and even binary compatible to classic MySQL5 but fixes old problmes and implement new features.

a.oblozhko · November 6, 2024, 9:53am

After additional investigation we found that concrete reason of error is that field name “manual” started to be treatened as reserved word by newer version of MySQL, it a quite fresh issue in liquibase 'MANUAL' MySQL reserved word added by MalloD12 · Pull Request #6128 · liquibase/liquibase · GitHub and version 4.30 that containing fixes is not published yet.

So if you are not able to migrate to MariaDB the advise will be to postpone upgrade to MySQL 8.4 until updates(eclipselink/jdbc versions also may need updates) will be available. I created issue for that: Increase liquibase version to provide compatibility with MySQL 8.4+ · Issue #3844 · jmix-framework/jmix · GitHub

Thank you for reporting.
wbr, Aleksey

malexio · November 6, 2024, 1:03pm

Thanks a lot Aleksey!
We will continue using MySql 8.0 by now.

In case you will upgrade the audit-addons jar with the newly generated changelog.xml using the fixed liquibase version, how should we handle this ?
I mean let’s suppose we are on the 2.4 Jmix Version, and in version 2.5 you will update the changelog.xml file of the audit-addon, how should we need to migrate from 2.4 → 2.5 in such a case ?

Thanks again,
Ale

a.oblozhko · November 7, 2024, 10:32am

updates are created only with new changesets that will be applied automatically on applicaion start, becuse error will fail the process otherwise, but I hope library version updates will be enough to avoid field renaming. It must apply ecape quotations to reserved workds by default.

wbr, Aleksey

pgirala · December 30, 2025, 8:05pm

Hi. Has this problem already been fixed?

Thanks in advance.

krivopustov · December 31, 2025, 11:15am

Not yet, but we have an issue in our backlog:

github.com/jmix-framework/jmix

Cannot use Audit add-on with MySQL 8.4+

opened 01:36PM - 20 Nov 25 UTC

knstvk

type: bug size: M in: audit

### Environment Jmix version: 2.7.1 ### Bug Description In MySQL 8.4 `MANUAL`… becomes a reserved word: https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-8-4.html#keywords-8-4-detailed-M So when executing changelogs, we get the error: `SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MANUAL tinyint DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY NAME (NAME) )' at line 7` ### Steps To Reproduce Run MySQL 8.4: ``` docker run -d -p 3306:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=root mysql:8.4 ``` Create database: ``` docker exec -it mysql8 mysql -u root -p mysql> create database demo; ``` Create a Jmix project and change the database type to MySQL. Set database name to `demo`. Add `allowPublicKeyRetrieval=true` to connection parameters. Try to update database.

Regards,
Konstantin