Description:
I'm trying to call a stored function from connector/j with a database name in the prepared call (e.g. {? = call testdb.SampleFunction(?)}), but connector/j looks up the stored function in mysql.proc with the wrong function name and throws a NullPointerException.
Calling the function with the database name works fine in 5.1.8, so this issue was introduced sometime after that release.
How to repeat:
1. Create a database and a stored function in that database.
2. Create a java mysql connection without a database name on the jdbc url
jdbc.url=jdbc:mysql://localhost:3306
3. Call the stored function from java and specify the database name on the prepared call
conn.prepareCall("{? = call testdb.SampleFunction(?)}");
4. Mysql tries to look up the parameter list in the mysql.proc table, but includes the database name in the mysql.proc.name field instead of mysql.proc.db
Query: SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name
5. The incorrect proc name causes the query to return no rows. CallableStatement constructs a CallableStatementParamInfo with the empty result set, causing CallableStatementParamInfo to not set a value for parameterList. Finally, CallableStatement tries to iterate over the null parameterList, resulting in a NullPointerException.
MySQL query log:
33 Connect root@localhost on
33 Query /* mysql-connector-java-commercial-5.1.13 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeou
t' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' O
R Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variab
le_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
33 Query /* mysql-connector-java-commercial-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
33 Query SHOW COLLATION
33 Query SET NAMES latin1
33 Query SET character_set_results = NULL
33 Query SET autocommit=1
33 Query SET sql_mode='STRICT_TRANS_TABLES'
33 Query SELECT @@session.tx_isolation
33 Query SELECT 1
33 Query SET autocommit=0
33 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name
33 Query rollback
33 Query SET autocommit=1
33 Query SELECT 1
33 Quit
Suggested fix:
I think this fix involves two steps:
1) Parse the database name out of the mysql.proc name field and use it as the db.
For example, the proc lookup:
'SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name'
should be
'SELECT name, type, comment FROM mysql.proc WHERE name like 'SampleFunction' and db <=> 'testdb' ORDER BY name'
2) Initialize CallableStatementParamInfo.parameterList to an empty ArrayList in the constructor that takes a ResultSet even if the result set doesn't have any rows so CallableStatementParamInfo.iterator() won't throw an NPE.
Description: I'm trying to call a stored function from connector/j with a database name in the prepared call (e.g. {? = call testdb.SampleFunction(?)}), but connector/j looks up the stored function in mysql.proc with the wrong function name and throws a NullPointerException. Calling the function with the database name works fine in 5.1.8, so this issue was introduced sometime after that release. How to repeat: 1. Create a database and a stored function in that database. 2. Create a java mysql connection without a database name on the jdbc url jdbc.url=jdbc:mysql://localhost:3306 3. Call the stored function from java and specify the database name on the prepared call conn.prepareCall("{? = call testdb.SampleFunction(?)}"); 4. Mysql tries to look up the parameter list in the mysql.proc table, but includes the database name in the mysql.proc.name field instead of mysql.proc.db Query: SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name 5. The incorrect proc name causes the query to return no rows. CallableStatement constructs a CallableStatementParamInfo with the empty result set, causing CallableStatementParamInfo to not set a value for parameterList. Finally, CallableStatement tries to iterate over the null parameterList, resulting in a NullPointerException. MySQL query log: 33 Connect root@localhost on 33 Query /* mysql-connector-java-commercial-5.1.13 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeou t' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' O R Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variab le_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 33 Query /* mysql-connector-java-commercial-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 33 Query SHOW COLLATION 33 Query SET NAMES latin1 33 Query SET character_set_results = NULL 33 Query SET autocommit=1 33 Query SET sql_mode='STRICT_TRANS_TABLES' 33 Query SELECT @@session.tx_isolation 33 Query SELECT 1 33 Query SET autocommit=0 33 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name 33 Query rollback 33 Query SET autocommit=1 33 Query SELECT 1 33 Quit Suggested fix: I think this fix involves two steps: 1) Parse the database name out of the mysql.proc name field and use it as the db. For example, the proc lookup: 'SELECT name, type, comment FROM mysql.proc WHERE name like 'testdb.SampleFunction' and db <=> '' ORDER BY name' should be 'SELECT name, type, comment FROM mysql.proc WHERE name like 'SampleFunction' and db <=> 'testdb' ORDER BY name' 2) Initialize CallableStatementParamInfo.parameterList to an empty ArrayList in the constructor that takes a ResultSet even if the result set doesn't have any rows so CallableStatementParamInfo.iterator() won't throw an NPE.