Spring SimpleJdbcInsert mixes column names from different databases in MySQL [SPR-17483]

Hans Desmet opened SPR-17483 and commented

The script createDatabases.sql creates two MySQL databases: example1 and example2. Each database has as table persons: * The database example1 has as column firstname in this table. * The database example2 has as column lastname in this table.

The test SimpleJdbcInsertTest tries to insert a record in the database example1.

@RunWith(SpringRunner.class)
@JdbcTest
@AutoConfigureTestDatabase(replace = Replace.NONE)
public class SimpleJdbcInsertTest {
    @Autowired
    private DataSource dataSource;
    private SimpleJdbcInsert insert;

    @Before
    public void before() {
        insert = new SimpleJdbcInsert(dataSource);
    }

    @Test
    public void insertPerson() {
        insert.withTableName("persons");
        insert.execute(Collections.singletonMap("firstname", "joe"));
    }
}

The test fails, because SimpleJdbcInsert sends following statement to the database:

INSERT INTO persons (lastname) VALUES(?)

This statement uses the wrong column lastname from the other database: example2.

The example project uses Spring Boot 2.1.0 When you revert to Spring Boot 2.0.6, the code works.

I think the problem is caused by the way the MySQL JDBC driver 8.0.13 provides meta data has changed, compared to the driver 5.1.47. I have tried to indiciate this in the test ColumNames, provided in the project.

---

Affects: 5.1.2

Reference URL: https://github.com/desmethans/simplejdbcinsert.git

Comment From: spring-projects-issues

Stéphane Nicoll commented

Thanks for the report and the sample. That looks like a bug in TableMetaDataProviderFactory with the new MySQL driver indeed.

Comment From: spring-projects-issues

Zhang Jie commented

The default value of property nullCatalogMeansCurrent has been changed in mysql-driver 5.x and 8.x. In 5.x, the default value is true, and in 8.x false, so in 5.x DatabaseMetaData.getTables will return tables exactly from 'example1', and in 8.x DatabaseMetaData.getTables will return tables not only from 'example1' but from all databases, that's why the SQL will be changed to 'INSERT INTO persons (lastname) VALUES (?)'. Workaround, add nullCatalogMeansCurrent=true to conn url when using mysql-driver 8.x, all tests pass successfully.

Comment From: snicoll

In retrospect, I don't think there's anything we can do in the API. This is also a database-specific issue that needs to be handled accordingly.