Environment
Liquibase Version: 3.8.9
Liquibase Integration & Version: maven liquibase plugin, also version 3.8.9; maven version 3.6.3
Liquibase Extension(s) & Version:
Database Vendor & Version: SQLServer 2012
Operating System Type & Version: Ubuntu running Docker 19.0.3
Description
Regardless of whether the attribute 'usePreparedStatements' is set to 'true' or 'false' in 'loadData' elements, Liquibase always uses prepared statements (so it behaves as if it were harcoded to 'true')
This is the cause why when we try to load a CSV file where a 'DATE' column is set to 'NULL' we get the following error:
Error setting up or running Liquibase:
[ERROR] Migration failed for change set liquibase/xxx:
[ERROR] Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.
This was already reported in https://liquibase.jira.com/browse/CORE-3399
As a workaround to overcome this problem, I've tried to set the 'usePreparedStatements' (in order to force the creation of liquibase's InsertStatements) to 'false' but it has no effect.
It all seems to be in Liquibase's 'LoadDataChange' class, in the condition that starts in line 464 from the liquibase codebase, GIT tag 3.8.9:
// end of: iterate through all the columns of a CSV line
// Try to use prepared statements if any of the two following conditions apply:
// 1. There is no other option than using a prepared statement (e.g. in cases of LOBs)
// 2. The database supports batched statements (for improved performance) AND we are not in an
// "SQL" mode (i.e. we generate an SQL file instead of actually modifying the database).
if
(
(needsPreparedStatement ||
(databaseSupportsBatchUpdates &&
!(ExecutorService.getInstance().getExecutor(database) instanceof LoggingExecutor)
)
)
&& hasPreparedStatementsImplemented()
) {
anyPreparedStatements = true;
ExecutablePreparedStatementBase stmt =
this.createPreparedStatement(
database, getCatalogName(), getSchemaName(), getTableName(), columnsFromCsv,
getChangeSet(), getResourceAccessor()
);
batchedStatements.add(stmt);
} else {
InsertStatement insertStatement =
this.createStatement(getCatalogName(), getSchemaName(), getTableName());
for (ColumnConfig column : columnsFromCsv) {
String columnName = column.getName();
Object value = column.getValueObject();
if (value == null) {
value = "NULL";
}
insertStatement.addColumnValue(columnName, value);
}
statements.add(insertStatement);
}
// end of: will we use a PreparedStatement?
}
// end of: loop for every input line from the CSV fileThe value of the flag 'needsPreparedStatement' is not taken into account in that condition, because:
- It is part of an OR condition where the second term is TRUE as long as the database supports batch updates (SQLServer does -as stated in the JDBC driver-) and
- the liquibase command run does not generate LOGs (liquibase:update does not generate logs)
So 'needsPreparedStatement' will have no effect.
Steps To Reproduce
- Given a running instance of SQL Server 2012
- And a JDK version 11 or above (e.g. openjdk 11)
- And the following
POM.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.liquibase</groupId>
<artifactId>test.database.problem</artifactId>
<version>1.0.0</version>
<name>Problem Dates in SQLServer</name>
<packaging>jar</packaging>
<properties>
<database.port>YOUR SQL SERVER PORT HERE</database.port>
<database.host>YOUR SQL SERVER HOST HERE</database.host>
<database.username>YOUR DATABASE USERNAME HERE</database.username>
<database.password>YOUR DATBASE PASSWORD HERE</database.password>
</properties>
<dependencies>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>8.3.0.jre11-preview</version>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.8.9</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.9</version>
<configuration>
<dropFirst>true</dropFirst>
<verbose>true</verbose>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<url>jdbc:sqlserver://${database.host}:${database.port}</url>
<username>${database.username}</username>
<password>${database.password}</password>
<changeLogFile>changelog-master.xml</changeLogFile>
</configuration>
</plugin>
</plugins>
</build>
</project>
- And the following
changelog-master.xml under src/main/resources
<?xml version="1.0" encoding="UTF-8"?>
<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-3.8.xsd">
<changeSet author="jdhmoreno" id="testProblemSqlServer">
<createTable remarks="Contains the column with a DATE" tableName="TABLE_WITH_DATE_COLUMN">
<column name="ID" type="bigint">
<constraints primaryKey="true" primaryKeyName="PK_TABLE"/>
</column>
<column name="DATE_COLUMN" remarks="The date column" type="DATETIME2"/>
</createTable>
<loadData relativeToChangelogFile="true" file="test_date.csv" tableName="TABLE_WITH_DATE_COLUMN" usePreparedStatements="false">
<column header="ID" name="ID" type="NUMERIC"/>
<column header="DATE_COLUMN" name="DATE_COLUMN" type="DATETIME2"/>
</loadData>
</changeSet>
</databaseChangeLog>
- And the following file
test_date.csv under src/main/resources
"ID","DATE_COLUMN"
"1","NULL"
"2","NULL"
"3","NULL"
- When I run the following
maven command
mvn clean resources:resources liquibase:update
- Then I got the following error:
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.8.9:update (default-cli) on project test.database.problem:
[ERROR] Error setting up or running Liquibase:
[ERROR] Migration failed for change set liquibase/changelog-master.xml::testProblemSqlServer::jdhmoreno:
[ERROR] Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.
[ERROR] -> [Help 1]
Expected/Desired Behavior
The idea would be that, when usePreparedStatements="false" is set in the loadData element, no PreparedStatements are used.
Proposed solution / idea
A solution might be to have the following condition in the class 'LoadDataChange', line 455
if
(
(needsPreparedStatement &&
(databaseSupportsBatchUpdates &&
!(ExecutorService.getInstance().getExecutor(database) instanceof LoggingExecutor)
)
)
&& hasPreparedStatementsImplemented()
)This way, needsPreparedStatement (which in turns depends on the 'usePreparedStatement' attribute) will drive the creation of PreparedStatements or InsertStatements.
This should help solving the 'date' issue shown above, leaving to the developer the option of enabling them or not.
Notes
A more detailed look on why SQLServer 2012 behaves that way when a SQL NULL is sent for a DATE column can be found in the following StackOverflow thread:
https://stackoverflow.com/questions/43935316/error-inserting-null-or-empty-value-in-date-column-mapped-to-localdate-type-in-j
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent