Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
spring-projects-issues opened this issue Nov 9, 2018 · 2 comments
Labels
in: data status: waiting-for-triage

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Nov 9, 2018

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

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 11, 2018

Stéphane Nicoll commented

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

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 12, 2018

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.

@spring-projects-issues spring-projects-issues added type: bug status: waiting-for-triage in: data and removed type: bug labels Jan 11, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: data status: waiting-for-triage
Projects
None yet
Development

No branches or pull requests

1 participant