Audit addon not supported on MySql 8.4

Support
,
#1

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:
Screenshot 2024-11-05 at 19.13.26

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

#2

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.

#3

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

1 Like
#4

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

#5

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