Affects: 4.3.14.RELEASE

Within the same database we have several (almost) duplicate schemas (dev, test, uat, etc).

SimpleJdbcInsert seems to get confused with having the same table names in different schemas.

It seems to get metadata from all tables with the matching name across all schemas and whichever table it processes last is the one it keeps in its map of columns.

This causes problems when the table schemas do not match - columns that are in one schema but not another get skipped at random when inserting values.

It should only be getting the table metadata for the active schema.

Comment From: sbrannen

What database are you using?

Also, can you please provide a minimal example application that demonstrates the behavior you are describing (for example, as a downloadable zip file or a public Git repository that we can experiment with)?

Comment From: hansdesmet

You can find an exampe application on https://github.com/hansdesmet/simplejdbcinsert.git. It uses MySQL. The project root contains the script createdatabases.sql. It creates two databases with in each a table with the name persons.

Comment From: davidhedley

We are using MySQL. After a lot of head scratching and confusion the development team tracked our bug to the SimpleJdbcInsert issue described above. We found we could work around the issue by using: jdbcInsert.withCatalogName("....."); however that added additional complications to our deployments so in the end it was just easier to remove SimpleJdbcInsert from our code and do the inserts in a different way. This issue report is more of a FYI and to alert other users in case they hit the same problem.

Comment From: hansdesmet

I have added a class ColumnNames to my example project on GitHub. It shows the wrong way to get te column names and the right way to do this. I hope this inspires the team to solve this bug.

Comment From: snicoll

See also #17221

Comment From: hansdesmet

The problem dissapears in my example project when you use withCatalogName:

@Test
    void insertPerson() {
        var insert = new SimpleJdbcInsert(dataSource)
                .withCatalogName("example")
                .withTableName("persons")
                .usingGeneratedKeyColumns("id");
        insert.executeAndReturnKey(Map.of("firstname", "joe", "lastname", "dalton"));
    }

Comment From: snicoll

Closing as a duplicate of #22015, see in particular https://github.com/spring-projects/spring-framework/issues/22015#issuecomment-453479292