Loaddata of CSV reports loading but the table is empty

General Discussion
1

Platform: debian 12.10, amd64, java 21.0.4, liquibase 4.31.1, assertj-db 3.0.0, apache derby 10.14.2.0

I am trying to use loaddata to fill a table with entries.

According to the log output from the changeSet loading the CSV file seems to be running

But the assertj-db assert still says that the table is empty.

The CSV file only has a single columns? Is that a problem?

Here are the changesets that sets up the table and populates the table:

    <changeSet author="sb" id="ratatoskr-1.0.0-activity-types-table">
        <createTable tableName="activity_types">
            <column autoIncrement="true" name="activity_type_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="activity_type_primary_key"/>
            </column>
            <column name="activity_type_name" type="VARCHAR(64)" />
        </createTable>
    </changeSet>

    <changeSet author="sb" id="ratatoskr-1.0.0-populate-activity-types-table" labels="data">
        <loadData file="ratatoskr-db-changelog/initial-activity-types.csv" tableName="activity_types" />
    </changeSet>

Here is the test code up to the assert that fails (the file containing both changesets is applied in ratatoskrLiquibase.createInitialSchema(datasource.getConnection()); ;

    @Test
    void testCreateSchema() throws Exception {
        var ratatoskrLiquibase = new RatatoskrLiquibase();
        var datasource = createDataSource("ratatoskr");
        var assertjConnection = AssertDbConnectionFactory.of(datasource).create();

        ratatoskrLiquibase.createInitialSchema(datasource.getConnection());

        var activityTypes = assertjConnection.table("activity_types").build();
        assertThat(activityTypes).exists().hasNumberOfRows(13);

Here is the failure message:

java.lang.AssertionError: [ACTIVITY_TYPES table] 
Expecting size (number of rows) to be equal to :
   <13>
but was:
   <0>
	at no.priv.bang.ratatoskr.db.liquibase.RatatoskrLiquibaseTest.testCreateSchema(RatatoskrLiquibaseTest.java:52)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

Here is the console output from the two changesets:

Running Changeset: ratatoskr-db-changelog/db-changelog-1.0.0.xml::ratatoskr-1.0.0-activity-types-table::sb
May 01, 2025 9:12:42 PM liquibase.changelog
INFO: Table activity_types created
May 01, 2025 9:12:42 PM liquibase.changelog
INFO: ChangeSet ratatoskr-db-changelog/db-changelog-1.0.0.xml::ratatoskr-1.0.0-activity-types-table::sb ran successfully in 8ms
Running Changeset: ratatoskr-db-changelog/db-changelog-1.0.0.xml::ratatoskr-1.0.0-populate-activity-types-table::sb
May 01, 2025 9:12:42 PM liquibase.change
WARNING: Could not snapshot table 'APP.ACTIVITY_TYPES' to get the missing column type information. It is strongly recommended that you augment your loadData operation with a full list of targeted columns and their load data types.
May 01, 2025 9:12:42 PM liquibase.change
WARNING: Could not snapshot table 'APP.ACTIVITY_TYPES' to get the missing column type information. It is strongly recommended that you augment your loadData operation with a full list of targeted columns and their load data types.
May 01, 2025 9:12:42 PM liquibase.statement
INFO: Executing JDBC DML batch was successful. 12 operations were executed, 1 individual UPDATE events were confirmed by the database.
May 01, 2025 9:12:42 PM liquibase.changelog
INFO: Data loaded from 'ratatoskr-db-changelog/initial-activity-types.csv' into table 'activity_types'
May 01, 2025 9:12:42 PM liquibase.changelog
INFO: ChangeSet ratatoskr-db-changelog/db-changelog-1.0.0.xml::ratatoskr-1.0.0-populate-activity-types-table::sb ran successfully in 59ms

Here is the contents of initial-activity-types.csv (a single column with the column name as the first row:

activity_type_name
Add
Announce
Create
Delete
Dislike
Follow
Like
Remove
Update
Accept
Ignore
Reject
2

I tried to “shake the tree” a little.

First thing I tried was to remove the first row, with the column name. And the result was actually promising:

java.lang.AssertionError: 
Expecting actual:
  "liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset ratatoskr-db-changelog/db-changelog-1.0.0.xml::ratatoskr-1.0.0-populate-activity-types-table::sb:
     Reason: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: 'Add' is not a column in table or VTI 'APP.ACTIVITY_TYPES'."
to start with:
  "java.lang.Exception"

	at no.priv.bang.ratatoskr.db.liquibase.RatatoskrLiquibaseTest.testCreateSchemaAndFailOnConnectionClose(RatatoskrLiquibaseTest.java:182)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

I.e. it means that the first row is actually used to match a column in a database table (and apparently does match a column).

So that was promising!

3

Then, for some reason, I decided to run just the test creating the schema in the eclipse test console and the result was even more promising:

java.lang.AssertionError: [ACTIVITY_TYPES table] 
Expecting size (number of rows) to be equal to :
   <13>
but was:
   <12>
	at no.priv.bang.ratatoskr.db.liquibase.RatatoskrLiquibaseTest.testCreateSchema(RatatoskrLiquibaseTest.java:52)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

I.e. still a test failure, but just one caused by me not being able to count.

So I’m thinking maybe: “test interference”…?

4

This is the interfering test: ratatoskr/ratatoskr.db.liquibase/src/test/java/no/priv/bang/ratatoskr/db/liquibase/RatatoskrLiquibaseTest.java at aa77f4dc2929d796a322d8faac34089fdb07416b · steinarb/ratatoskr · GitHub

If I disable this test the others run fine.

I thought that since I created a completely fresh memory database with a new name it should not affect other databases?

But maybe my override of close messes things up?

5

Making sure the underlying connection was properly closed fixed the test interference:

    @Test
    void testCreateSchemaAndFailOnConnectionClose() throws Exception {
        try (var realConnection = createConnection("ratatoskr2")) {
            var connection = spy(realConnection);
            doNothing().when(connection).setAutoCommit(anyBoolean());
            doThrow(Exception.class).when(connection).close();

            var ratatoskrLiquibase = new RatatoskrLiquibase();

            var ex = assertThrows(
                LiquibaseException.class,
                () -> ratatoskrLiquibase.createInitialSchema(connection));
            assertThat(ex.getMessage()).startsWith("java.lang.Exception");
        }
    }