“Time(3)” type on Liquibase with MySQL 8

Question

I'm facing a problem using Liquibase with MySQL 8 where the following script is not putting the fraction part of type "time(3)", it only puts "time" on the type of the column. We run this script before with MySQL 5 and it worked fine.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.16.xsd"
    logicalFilePath="20220901.xml">

    <changeSet author="MyUser" id="Change column 'time' to Datatype to milliseconds">
        
         <modifyDataType
            columnName="time"
            newDataType="TIME(3)"
            schemaName="${defaultSchema}"
            tableName="table1"/>   
            
         <addNotNullConstraint 
            columnDataType="TIME(3)"
            columnName="time"
            schemaName="${defaultSchema}"
            tableName="table1" />
                  
     </changeSet>   
            
</databaseChangeLog>

I tried to update to most recent versions on maven dependencies of liquibase.core(to 4.16.1) and mysql-connector-java(to 8.0.30), the problem persists.

After multiple tests, i discover that the problem may be on liquibase generated query that not includes the fraction part "(3)", so as a workaround I used "modifySql" to change the query at the end.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.16.xsd"
    logicalFilePath="20220901.xml">

 <!-- WORK-AROUND - Liquibase was generating a query with type 'TIME' instead of 'TIME(3)' so 
 we use 'REPLACE_WITH_TIME' as auxiliary type to replace all of it's occurrences in query
 by 'TIME(3)' with 'modifySql'. -->
  <changeSet author="MyUser"
    id="Fix time column type to time(3) - 2022-10-06">
    <modifyDataType columnName="time"
      newDataType="REPLACE_WITH_TIME" schemaName="${defaultSchema}"
      tableName="table1" />

    <addNotNullConstraint columnDataType="REPLACE_WITH_TIME"
      columnName="time" schemaName="${defaultSchema}"
      tableName="table1" />

    <modifySql>
      <replace replace="REPLACE_WITH_TIME" with="TIME(3)" />
    </modifySql>
  </changeSet>
</databaseChangeLog>

It resolves the problem but its not the best solution. So i wanted to ask if anybody noticed that and knows if its actally a liquibase bug or not.

Thanks in advance.

Robbins

The "modifySql" approach is always a great fallback when the behavior is otherwise not working as you need. But it should be a fallback, not working around bugs. Trying it with the now-current 4.17.1, I'm getting the correct time(3) in the SQL. So I think the bug has been fixed recently. – Ecclesiastical

@NathanVoxland are you pointing to a MySQL 8 database? What version are you using here "dbchangelog liquibase.org/xml/ns/dbchangelog/dbchangelog-4.16.xsd" ? – Cancer

@NathanVoxland I tried again with version 4.17.2, still not getting correct time(3) in SQL 8.0. – Cancer

I still with the same problem? Can you confirm that bug has been fixed? or the problem is in my side? @NathanVoxland – Cancer