"Time(3)" type on Liquibase with MySQL 8

General Discussion
1

Hi,

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 collum '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.

2

You can run the update-sql command to see the generated SQL specific to your DBMS platform.

You always have the option of using custom SQL in any of the file formats (xml, yaml, json) if Liquibase doesn’t support a specific syntax that you need.