6

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
João Gonçalves
  • 83
  • 4
  • 1
    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. – Nathan Voxland Oct 25 '22 at 19:47
  • @NathanVoxland are you pointing to a MySQL 8 database? What version are you using here "dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-**4.16**.xsd" ? – João Gonçalves Oct 27 '22 at 09:03
  • @NathanVoxland I tried again with version 4.17.2, still not getting correct time(3) in SQL 8.0. – João Gonçalves Dec 22 '22 at 16:21

0 Answers0