MySQL Bugs: #24065: Connect/J prepareCall gives null pointer exception

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

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.

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).

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

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

Test case

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

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...

Changing from prepareCall to prepareStatement fixed the problem.

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?

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?

I tried prepareCall using root instead of the limited access user, and everything worked fine. The limited access user has execute only authority...

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".

OK, I get it now. It works fine with the noAccessToProcedureBodies property set to true.

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.

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

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.

Note: the DEFINER issue I described above was observed in MySQL 5.0.27-community-nt