Liquibase generate-changelog doesn't work with MySQL

General Discussion
1

I’m trying to evaluate using liquibase for my mysql databases in my organisation. In my initial evaluation I’ve come across multiple problems. I have an example to quickly illustrate all of my problems on a fresh mysql database create the following table.

SET sql_mode = '';

DROP TABLE IF EXISTS `liquibase_example_table`;
CREATE TABLE `liquibase_example_table` (
    id INT(11) NOT NULL AUTO_INCREMENT,
    enum_column ENUM('option1', 'option2', 'option3') NOT NULL,
    json_column JSON NOT NULL,
    text_column TEXT NOT NULL,
    timestamp_column TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    INDEX text_column_idx (text_column(100)),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Generate a changelog and you will get invalid SQL that can’t be used in a liquibase update

-- liquibase formatted sql

-- changeset root:1681305995631-1
CREATE TABLE liquibase_example_table (id INT AUTO_INCREMENT NOT NULL, enum_column ENUM(7) NOT NULL, json_column JSON(1073741824) NOT NULL, text_column TEXT NOT NULL, timestamp_column timestamp DEFAULT '0000-00-00 00:00:00' NOT NULL, CONSTRAINT PK_LIQUIBASE_EXAMPLE_TABLE PRIMARY KEY (id));

-- changeset root:1681305995631-2
CREATE INDEX text_column_idx ON liquibase_example_table(text_column);

The enum is wrong changed from ENUM(‘option1’, ‘option2’, ‘option3’) to ENUM(7), the json column is wrong adding an unnecessary precision, the precision of the text index is gone, the default timestamp looks correct but without the SQL mode set it wont run in correctly. All of these will error.

Testing on :
mysql server version 5.7.41
liquibase version: 4.20.0

Also tried a mariadb client this fixed the JSON issue but kept all other issues

2

Each of the issues you mentioned could likely be considered bugs and worth creating a Github issue for each. You could do that here: Sign in to GitHub · GitHub

In general, generatechangelog tries to hit most cases. Sometimes it’s wrong. Kind of like ChatGPT doesn’t always give the correct answer, but sure is confident. There will always be some cases that aren’t supported (yet). It’s not uncommon to edit the changelog manually after creating it to make db specific modifications.

For more detailed reference, the datatypes are handled by classes in the this package: liquibase/liquibase-core/src/main/java/liquibase/datatype/core at master · liquibase/liquibase · GitHub

3

I haven’t raised a bug for the sql_mode/default date because I don’t know if there is a way to pass sql_mode to the liquibase client.
Is there some way of doing that?

I’ve raised the following bugs in relation to this:

MySQL generateChangeLog adds unnecessary precision for JSON types

MySQL generateChangeLog generates ENUM types incorrectly as ENUM with a numerical precision

4

I’ve also raised this bug:
MySQL generateChangeLog does not generate index prefixes

There’s a link limit for new users.

5

Found the answer to this question