Oisin
May 8, 2023, 11:01am
1
There is an open bug for:
MySQL does not correctly apply ON UPDATE defaults #4123
This problem is with the changelog generation. Is it possible to manually edit the changlog to add “ON UPDATE” fields as a work around?
opened 02:22AM - 13 Apr 23 UTC
TypeBug
DBMySQL
### Search first
- [X] I searched and no similar issues were found
### Descrip… tion
MySQL default update statements are not reflected in changelogs.
Take this table for example with multiple columns with ON UPDATE defaults
``` SQL
CREATE TABLE `t3` (
`ts1` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`ts2` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`ts3` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`dt1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`dt2` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`dt3` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`doUpdate` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
```
The generated change log will be
``` XML
<createTable schemaName="integration_management" tableName="t3">
<column defaultValueComputed="CURRENT_TIMESTAMP" name="ts1" type="timestamp"/>
<column name="ts2" type="timestamp"/>
<column name="ts3" type="timestamp"/>
<column defaultValueComputed="CURRENT_TIMESTAMP" name="dt1" type="datetime"/>
<column defaultValueComputed="CURRENT_TIMESTAMP" name="dt2" type="datetime"/>
<column name="dt3" type="datetime"/>
<column name="doUpdate" type="TEXT"/>
</createTable>
```
or
``` SQL
CREATE TABLE integration_management.t3 (
ts1 timestamp DEFAULT NOW() NULL,
ts2 timestamp NULL,
ts3 timestamp NULL,
dt1 datetime DEFAULT NOW() NULL,
dt2 datetime DEFAULT NOW() NULL,
dt3 datetime NULL,
doUpdate TEXT NULL
);
```
### Steps To Reproduce
1. Create table with ON UPDATE
``` SQL
CREATE TABLE `t3` (
`ts1` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`ts2` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`ts3` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`dt1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`dt2` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`dt3` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`doUpdate` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
```
2. Run `liquibase generate-changelog` or `diff`
### Expected/Desired Behavior
Expect that the ON UPDATE defaults are included in the change log
``` SQL
CREATE TABLE integration_management.t3 (
ts1 timestamp DEFAULT NOW() NULL,
ts2 timestamp NULL ON UPDATE NOW(),
ts3 timestamp NULL ON UPDATE NOW(),
dt1 datetime DEFAULT NOW() NULL,
dt2 datetime DEFAULT NOW() ON UPDATE NOW(),
dt3 datetime NULL ON UPDATE NOW(),
doUpdate TEXT NULL
);
```
### Liquibase Version
4.20
### Database Vendor & Version
MySQL 8.0.31
### Liquibase Integration
CLI
### Liquibase Extensions
_No response_
### OS and/or Infrastructure Type/Provider
Windows
### Additional Context
_No response_
### Are you willing to submit a PR?
- [X] I'm willing to submit a PR (Thank you!)
Yes, you can always manually modify the generated changelogs.
Oisin
May 9, 2023, 8:18am
3
What is the xml syntax or tag?