Stored Procedure executed via SimpleJdbcCall giving error

pranav-agg commented 5 months ago

Describe the bug

An exception is thrown when trying to call a stored procedure via SimpleJdbcCall when there are multiple schemas in DB.

Driver used: software.aws.rds.jdbc.mysql.Driver URL: jdbc:mysql:aws://db-identifier.cluster-custom-XYZ.us-east-2.rds.amazonaws.com/demo-dev?sessionVariables=collation_connection=utf8mb4_0900_ai_ci

We are creating connection using HikariDatasource: `final HikariConfig config = new HikariConfig(); config.setPoolName("TITAN"); config.setMaximumPoolSize(15); config.setMinimumIdle(15); config.setLeakDetectionThreshold(30000); config.setConnectionTimeout(100_000); config.setDriverClassName(Driver.class.getName()); config.setJdbcUrl(url); config.setUsername(dbUsername); config.setPassword(dbPassword); config.setDataSourceProperties(jdbcProperties());

    return new HikariDataSource(config);

final Properties p = new Properties(); p.setProperty("socketTimeout", "0");

    // Recommended configuration from https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
    p.setProperty("cachePrepStmts", "true");
    p.setProperty("prepStmtCacheSize", "250");
    p.setProperty("prepStmtCacheSqlLimit", "2048");
    p.setProperty("useServerPrepStmts", "true");
    p.setProperty("useLocalSessionState", "true");
    p.setProperty("rewriteBatchedStatements", "true");
    p.setProperty("cacheResultSetMetadata", "true");
    p.setProperty("cacheServerConfiguration", "true");
    p.setProperty("elideSetAutoCommits", "true");
    p.setProperty("maintainTimeStats", "false");`

Expected Behavior

SimpleJdbcCall should have automatically resolved with the dbName given as part of URL and executed the stored procedures.

Current Behavior

An exception is thrown. This works when there is only single schema in DB. But when there are multiple schemas, getting below exception.

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple signatures for 'get_project_price_books': found [dev.null.get_project_price_books, demo-test.null.get_project_price_books, demo-test-uat.null.get_project_price_books, demo-dev.null.get_project_price_books, demo-ea1.null.get_project_price_books] procedures at org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.processProcedureColumns(GenericCallMetaDataProvider.java:331) at org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.initializeWithProcedureColumnMetaData(GenericCallMetaDataProvider.java:117) at org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.lambda$createMetaDataProvider$0(CallMetaDataProviderFactory.java:137) at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:360) at org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.createMetaDataProvider(CallMetaDataProviderFactory.java:78) at org.springframework.jdbc.core.metadata.CallMetaDataContext.initializeMetaData(CallMetaDataContext.java:253) at org.springframework.jdbc.core.simple.AbstractJdbcCall.compileInternal(AbstractJdbcCall.java:315) at org.springframework.jdbc.core.simple.AbstractJdbcCall.compile(AbstractJdbcCall.java:298) at org.springframework.jdbc.core.simple.AbstractJdbcCall.checkCompiled(AbstractJdbcCall.java:355) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:370) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198)

Reproduction Steps

Setup multiple schema in DB and try to call a stored procedure via SimpleJdbcCall.

Possible Solution

No response

Additional Information/Context

No response

The AWS JDBC Driver for MySQL version used

1.1.14

JDK version used

open JDK 21

Operating System and version

Windows 11

sergiyvamz commented 5 months ago

Hello @pranav-agg

Thank you for raising the issue. We understand this issue is causing inconveniences in your workflows. We'll be investigating this issue and will update you when we have a release. We appreciate your patience.

Would you also clarify some of the questions below.

Is the issue reproducible with community Mysql JDBC Connector/J ? https://github.com/mysql/mysql-connector-j
Could you please provide the driver logs? https://github.com/awslabs/aws-mysql-jdbc?tab=readme-ov-file#enable-logging
The exception mentioned in the issue description looks as an exception thrown by Spring framework. Is there any details about original exception thrown by the AWS JDBC Driver?
Can you provide a sample application that can help us to reproduce the issue?

Thank you!

davecramer commented 4 months ago

Support for this driver will be ending July 25/2024 as per the https://github.com/awslabs/aws-mysql-jdbc/blob/main/CONTRIBUTING.md document. Any new features will be implemented in https://github.com/aws/aws-advanced-jdbc-wrapper

awslabs / aws-mysql-jdbc