Can't make liquibase-mssql work (loadData with IDENTITY_INSERT)

General Discussion
1

Hi,
I’m trying to use this extension on my project but I’m having issues with the “loadData” command and tables with identity columns.
The tables and views change log runs perfectly (they are created in the database just fine) but when I get to load the data into the tables it always say:

Starting Liquibase at 03:30:02 (version 4.2.0 #18 built at 2020-11-13 16:49+0000)
Unexpected error running Liquibase: Migration failed for change set changelog/1.0.0/data/20190204114519_data_bfsEndereco_tbl_Continente.xml::loadData-tbl_Continente::bfsEndereco:
     Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
For more information, please use the --logLevel flag

It looks like the extension insert is not activating when I run the “loadData” command:

<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
						http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd
						http://www.liquibase.org/xml/ns/dbchangelog-ext
						http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
	<changeSet author="bfsEndereco" id="loadData-tbl_Continente">
		<loadData schemaName="bfsEndereco" tableName="tbl_Continente" file="data_table_bfsEndereco_tbl_Continente.csv" relativeToChangelogFile="true" separator=";" quotchar="&quot;">
			<column header="c_ContinenteID" name="c_ContinenteID" type="NUMERIC"/>
			<column header="c_Nome" name="c_Nome" type="STRING"/>
			<column header="c_NomeAscii" name="c_NomeAscii" type="STRING"/>
			<column header="c_NomeEnglish" name="c_NomeEnglish" type="STRING"/>
			<column header="c_Codigo" name="c_Codigo" type="STRING"/>
			<column header="c_gnid" name="c_gnid" type="NUMERIC"/>
		</loadData>
	</changeSet>
</databaseChangeLog>

PS: I have tried with “ext:” in the tag loadData and/or , with no results.

The classpath that I’m using in the “liquibase.properties” file is:
classpath=../../Liquibase/sqljdbc_8.4/enu/mssql-jdbc-8.4.1.jre8.jar;../../Liquibase/Extension/liquibase-mssql/liquibase-mssql-4.1.1.jar

In the log file it appears to load the extension just fine:

...
[2020-11-15 03:27:24] INFO [liquibase.integration] No Liquibase Pro license key supplied. Please set liquibaseProLicenseKey on command line or in liquibase.properties to use Liquibase Pro features.
[2020-11-15 03:27:24] FINE [liquibase.integration] Adding 'file:/W:/BitBucket/BFS.Database/scripts/bfsEndereco/../../Liquibase/sqljdbc_8.4/enu/mssql-jdbc-8.4.1.jre8.jar' to the Java classpath.
[2020-11-15 03:27:24] FINE [liquibase.integration] Adding 'file:/W:/BitBucket/BFS.Database/scripts/bfsEndereco/../../Liquibase/Extension/liquibase-mssql/liquibase-mssql-4.1.1.jar' to the Java classpath.
[2020-11-15 03:27:24] FINE [liquibase.integration] Liquibase Hub URL:      https://hub.liquibase.com
[2020-11-15 03:27:24] FINE [liquibase.integration] Liquibase Hub Mode:     all
...

...
[2020-11-15 03:27:25] FINE [liquibase.servicelocator] Loaded liquibase.sqlgenerator.SqlGenerator instance com.datical.liquibase.ext.storedlogic.trigger.change.RenameTriggerGenerator
[2020-11-15 03:27:25] FINE [liquibase.servicelocator] Loaded liquibase.sqlgenerator.SqlGenerator instance liquibase.ext.mssql.sqlgenerator.InsertGenerator
[2020-11-15 03:27:25] FINE [liquibase.executor] Executing with the 'jdbc' executor
...

But I get the error below every time, I tried many different things but never work.

...
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 1 for column c_ContinenteID
[2020-11-15 03:30:05] FINE [liquibase.statement] value is numeric = 7
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 2 for column c_Nome
[2020-11-15 03:30:05] FINE [liquibase.statement] value is string/UUID/blob = Antártida
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 3 for column c_NomeAscii
[2020-11-15 03:30:05] FINE [liquibase.statement] value is string/UUID/blob = Antartida
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 4 for column c_NomeEnglish
[2020-11-15 03:30:05] FINE [liquibase.statement] value is string/UUID/blob = Antarctica
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 5 for column c_Codigo
[2020-11-15 03:30:05] FINE [liquibase.statement] value is string/UUID/blob = AN
[2020-11-15 03:30:05] FINE [liquibase.statement] Applying column parameter = 6 for column c_gnid
[2020-11-15 03:30:05] FINE [liquibase.statement] value is numeric = 6255152
[2020-11-15 03:30:05] FINE [liquibase.executor] Release Database Lock
[2020-11-15 03:30:05] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2020-11-15 03:30:05] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2020-11-15 03:30:05] INFO [liquibase.lockservice] Successfully released change log lock
[2020-11-15 03:30:05] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set changelog/1.0.0/data/20190204114519_data_bfsEndereco_tbl_Continente.xml::loadData-tbl_Continente::bfsEndereco:
     Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set changelog/1.0.0/data/20190204114519_data_bfsEndereco_tbl_Continente.xml::loadData-tbl_Continente::bfsEndereco:
     Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:124)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:275)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:274)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Liquibase.runInScope(Liquibase.java:2277)
	at liquibase.Liquibase.update(Liquibase.java:215)
	at liquibase.Liquibase.update(Liquibase.java:201)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1760)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:361)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.Scope.child(Scope.java:226)
	at liquibase.integration.commandline.Main$1.run(Main.java:360)
	at liquibase.integration.commandline.Main$1.run(Main.java:193)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:145)
	at liquibase.integration.commandline.Main.run(Main.java:193)
	at liquibase.integration.commandline.Main.main(Main.java:156)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set changelog/1.0.0/data/20190204114519_data_bfsEndereco_tbl_Continente.xml::loadData-tbl_Continente::bfsEndereco:
     Reason: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:670)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:49)
	at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:111)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:110)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.Scope.child(Scope.java:226)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
	... 28 more
Caused by: liquibase.exception.DatabaseException: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
	at liquibase.statement.ExecutablePreparedStatementBase.execute(ExecutablePreparedStatementBase.java:82)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:140)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1272)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1254)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:635)
	... 48 more
Caused by: java.sql.BatchUpdateException: Cannot insert explicit value for identity column in table 'tbl_Continente' when IDENTITY_INSERT is set to OFF.
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2088)
	at liquibase.statement.BatchDmlExecutablePreparedStatement.executePreparedStatement(BatchDmlExecutablePreparedStatement.java:64)
	at liquibase.statement.ExecutablePreparedStatementBase.execute(ExecutablePreparedStatementBase.java:80)
	... 52 more

Can anybody say what I’m doing wrong or if there is a bug in the extension?
I’m going insane with it, every place that I look says it works but it is from many years ago, and with the extension been updated recently I don’t know what is.

Thanks.

2

Hi @LazyLeecher,

I believe @MikeOlivas or @NathanVoxland would be best able to help you. If the forum is taking too long, we use Discord for chat, you can ask @MikeOlivas directly in the #developer channel. Also want to make sure you have the basic resource here.

3

Not sure if this is a bug in the extension or you may have some gaps in your data.
Based on this

or
You may have something like mentioned here

4

In addition, MS SQL Server is supported directly with liquibase core without an extension. Is there something in the extension in particular that you are using?

5

Hi @MikeOlivas,

My problem isn’t with the sql command “IDENTITY_INSERT” specifically. The liquibase extention “liquibase.mssql” was supposed to include the command automatically before and after the insert statement and isn’t doing that.

By what I could observe the jar file is being loaded as the log showed bellow but the sql command is not included when the loadData changeset is called.

[2020-11-15 03:27:24] FINE [liquibase.integration] Adding 'file:/W:/BitBucket/BFS.Database/scripts/bfsEndereco/../../Liquibase/Extension/liquibase-mssql/liquibase-mssql-4.1.1.jar' to the Java classpath.
...
[2020-11-15 03:27:25] FINE [liquibase.servicelocator] Loaded liquibase.sqlgenerator.SqlGenerator instance liquibase.ext.mssql.sqlgenerator.InsertGenerator

Giving the error:
` Cannot insert explicit value for identity column in table ‘tbl_Continente’ when IDENTITY_INSERT is set to OFF.

This loadData is the first insert command of all my changeSets, it has created all tables, functions, views, etc just fine, but when I will begin to insert the data it gives the error.

I already tried putting the tag as "<ext:loadData " but to no effect.

6

I have recreated your issue. I don’t have an answer at this point but will get back to you soon.