# Liquibase generate-changelog doesn't work with MySQL

**URL:** <https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059>\
**Category:** General Discussion\
**Created:** [April 12, 2023, 2:33pm UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059 "2023-04-12T14:33:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Oisin](https://avatars.discourse-cdn.com/v4/letter/o/6bbea6/32.png) [@Oisin](https://forum.liquibase.org/u/Oisin)\
**Post date:** [April 12, 2023, 2:33pm UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/1 "2023-04-12T14:33:56Z")

</div>

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.

```auto
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

```auto
-- 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

---

<div class="post-metadata">

**Author:** ![PJatLiquibase](https://avatars.discourse-cdn.com/v4/letter/p/d26b3c/32.png) [@PJatLiquibase](https://forum.liquibase.org/u/PJatLiquibase)\
**Post date:** [April 12, 2023, 6:49pm UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/2 "2023-04-12T18:49:41Z")

</div>

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](https://github.com/liquibase/liquibase/issues/new/choose)

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](https://github.com/liquibase/liquibase/tree/master/liquibase-core/src/main/java/liquibase/datatype/core)

---

<div class="post-metadata">

**Author:** ![Oisin](https://avatars.discourse-cdn.com/v4/letter/o/6bbea6/32.png) [@Oisin](https://forum.liquibase.org/u/Oisin)\
**Post date:** [April 17, 2023, 11:50am UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/3 "2023-04-17T11:50:08Z")

</div>

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](https://github.com/liquibase/liquibase/issues/4141)

[MySQL generateChangeLog generates ENUM types incorrectly as ENUM with a numerical precision](https://github.com/liquibase/liquibase/issues/4142)

---

<div class="post-metadata">

**Author:** ![Oisin](https://avatars.discourse-cdn.com/v4/letter/o/6bbea6/32.png) [@Oisin](https://forum.liquibase.org/u/Oisin)\
**Post date:** [April 17, 2023, 11:53am UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/4 "2023-04-17T11:53:57Z")

</div>

I’ve also raised this bug:  
[MySQL generateChangeLog does not generate index prefixes](https://github.com/liquibase/liquibase/issues/4144)

There’s a link limit for new users.

---

<div class="post-metadata">

**Author:** ![Oisin](https://avatars.discourse-cdn.com/v4/letter/o/6bbea6/32.png) [@Oisin](https://forum.liquibase.org/u/Oisin)\
**Post date:** [April 17, 2023, 1:50pm UTC](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/5 "2023-04-17T13:50:07Z")

</div>

Found the answer to this question

> [@For MySQL how to set Server SQL Modes for the liquibase client](https://forum.liquibase.org/t/for-mysql-how-to-set-server-sql-modes-for-the-liquibase-client/8082):
>
> Using Liquibase with MySQL I want to set the sql\_mode variable for the liquibase client. Is there a way to do this? I want to be able to set this variable so that liquibase updates can run correctly if a sql\_mode needs to be set as a pre-requisite. [https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html) This is a spin off question from another post I made: [Liquibase generate-changelog doesn't work with MySQL - #2 by PJatLiquibase](https://forum.liquibase.org/t/liquibase-generate-changelog-doesnt-work-with-mysql/8059/2)