Bug #24065 Connect/J prepareCall gives null pointer exception
Submitted: 7 Nov 2006 21:51 Modified: 11 Dec 2006 20:25
Reporter: Steve Page Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.0.4, 3.1.14 OS:Windows (Win XP)
Assigned to: CPU Architecture:Any

[7 Nov 2006 21:51] Steve Page
Description:
CallableStatement spUserUpdate = conn.prepareCall("{ CALL spTest() }");

throws

java.lang.NullPointerException
	at com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(StringUtils.java:959)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1296)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3670)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:702)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
	at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4422)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4496)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4470)
	at rpt.test.SPTest.run(SPTest.java:71)
	at rpt.test.SPTest.main(SPTest.java:34)

Tried with a standard stored procedure with 9 parms, then with a test sp with no parms that only returns a result set. Happens with/without {}, independent of capitilization, etc.

How to repeat:
Happens every time.
[7 Nov 2006 22:19] Mark Matthews
What does "SHOW CREATE PROCEDURE ..." for the stored procedures in question show?

We do hundreds of Connection.prepareCalls() in our testsuite (and the JDBC compliance testsuite), so it more than likely is related to how your procedure is written (because the driver has to parse it to get the metadata).
[8 Nov 2006 19:01] Steve Page
Mark-

Thanks for responding. I created the SP's using Navicat. Here is the SQL it says it used:

CREATE PROCEDURE `spTest`()
begin
	select * from roles;
end

-Steve
[13 Nov 2006 11:51] Tonci Grgin
Hi Steve and thanks for your problem report.
I can't repeat it on MySQL server 5.0.27BK on WinXP Pro SP2 localhost with connector/J latest sources. I tried with/without data in table and also with serverside PS turned on/off. Test case is attached. I would like to turn your attention to BUG#22297!
--
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.27-log
id=a	Income=4094.0	
id=b	Income=500.0	
id=c	Income=3462.17	
id=d	Income=500.0	
id=e	Income=600.0
[13 Nov 2006 11:52] Tonci Grgin
Test case

Attachment: TestBug24065.java (text/x-java), 1.59 KiB.

[13 Nov 2006 15:15] Steve Page
OK. I saw a posting on the Web where someone had the same problem and solved by moving his source to a different machine. I tried upgrading the JVM, but that didn't help. I suspect the problem is somehow environment related...
[13 Nov 2006 18:25] Steve Page
Changing from prepareCall to prepareStatement fixed the problem.
[26 Nov 2006 0:29] Toni Falk
Hi,

II have found some form of explanation for the phenomenon.

I have XP, Eclipse and all of the most recent versions. 
I have encountered a similar effect, a null pointer exception
on the prepareCall Statement. 

It seems to be a rights problem.

I have then tried to view the SHOW CREATE FUNCTION <name> in
the SQL command line interface as the user that I also use 
with JDBC. 
I saw, that the CREATE FUNCTION COLUMN showed NULL; 
But the same for user 'root' showed the correct definition.

I then reflected, that I had defined all those FUNCTIONS and
PROCEDURES as root. So I dropped them as root and reentered
them as the user that I also use with JDBC.

After that, both the SHOW CREATE FUNCTION <name>, entered 
as the user that I also use with JDBC and the prepareCall
(and the rest) from the Java-Application worked just
fine.

However what puzzles me, as the user that I also use with JDBC
I could execute the FUNCTIONS and PROCEDURES just fine and I
had issued a GRANT ALL as root to that user. 

So what is your recommended workflow to store procedures
and functions for use with multiple users via JDBC?
[27 Nov 2006 19:17] Steve Page
The user I used for the JDBC connection has only Execute authority to the database. It sounds like more authority is required in prepareCall to retrieve schema information than with prepareStatement...

Can this be re-opened?
[28 Nov 2006 18:31] Steve Page
I tried prepareCall using root instead of the limited access user, and everything worked fine. The limited access user has execute only authority...
[11 Dec 2006 12:09] Tonci Grgin
Steve, did you looked at:
07-26-06 - Version 5.0.3
    - Added configuration option "noAccessToProcedureBodies" which will
      cause the driver to create basic parameter metadata for
      CallableStatements when the user does not have access to procedure
      bodies via "SHOW CREATE PROCEDURE" or selecting from mysql.proc
      instead of throwing an exception. The default value for this option
      is "false".
[11 Dec 2006 20:25] Steve Page
OK, I get it now. It works fine with the noAccessToProcedureBodies property set to true.
[9 Jan 2007 16:50] Peter Andrews
I experienced exactly this bug with MySql 5.1.14-beta and Connector/J 5.04 and Java. One small difference is that my nullPointerException had no stacktrace available.

The key is that the user must only have exectute permissions on the specific database containing the sp.

I suggest you read MySQL Bug #20235 and my comment there.

It turns out that if you add a select grant to the 'mysql' table, prepareCall succeeds. As I noted in the other bug, one oddity is that 'SHOW PROCEDURE' was able to succeed even though my user did not have 'CREATE ROUTINE' or 'ALTER ROUTINE' permissions. Perhaps this is inherent is having select permission on 'mysql'?

By the way, possibly a separate bug, I added useInformationSchema=true' to my connection url but is appeared to make no difference in connector/j 5.04 behavior on the prepareCall -- with both 'true' and 'false' if issued a 'SHOW CREATE PROCEDURE'. Giving Select grant for the Information Schema database did not have any affect.
[9 Jan 2007 22:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17817
[1 Mar 2007 5:35] Brian Sperlongano
I was able to duplicate this bug exactly.  It occurs when a line such as:

/*!50020 DEFINER=`user`@`%`*/

when "user" is NOT a valid user.  This happens when you export a stored procedure from one db and move it to another with different users.

Removing the offending DEFINER lines from the creates of the stored procedures fixes it.
[1 Mar 2007 5:36] Brian Sperlongano
Note: the DEFINER issue I described above was observed in MySQL 5.0.27-community-nt