MySQL Bugs: #57183: SQLException: Parameter index is out of range

Bug #57183	SQLException: Parameter index is out of range
Submitted:	1 Oct 2010 19:01	Modified:	29 Apr 2013 8:49
Reporter:	Mike Norman	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.13	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
this is related to 43576 which is supposed to be fixed. It works on
an earlier version of the driver (5.1.7):

Server-side:
MySQL 5.5.5-m3

Client-side:
MySQL-AB JDBC Driver
mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} )

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Parameter index of 2 is out of range (1, 0)
Error Code: 0
Call: { CALL emp.InOutArgsSP(?, ?, ?) }
	bind => [yuck, => U, => V]
Query: DataReadQuery()
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at 
[MWN - many lines omitted...]
Caused by: java.sql.SQLException: Parameter index of 2 is out of range (1, 0)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
	at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.checkBounds(CallableStatement.java:274)
	at com.mysql.jdbc.CallableStatement.checkParameterIndexBounds(CallableStatement.java:710)
	at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:672)
	at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1846)
	at org.eclipse.persistence.internal.databaseaccess.OutputParameterForCallableStatement.set(OutputParameterForCallableStatement.java:103)
	at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2113)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:671)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:576)
	... 39 more

How to repeat:
CREATE PROCEDURE InOutArgsSP(IN T VARCHAR(20), OUT U VARCHAR(20), OUT V
NUMERIC)
BEGIN
 set U = CONCAT('barf-' , T);
 set V = 55;
END

I have a testcase that is run as part of the nightly regession suite for EclipseLink; if a 'stripped-down' version is required, it shouldn't be
hard to convert.

I'm not sure about the severity - I don't have any workarounds other
than to use an older (not supported?) version of the driver, but
neither the client nor server hangs or crashes, so I'm reluctant to
set this to 'S1'

Hi Mike and thanks for your report.

Can you please attach complete test case along with connection string so I can check. Does this happen for underprivileged user only? Can you check if PARAMS table is in your I__S?

testcase

Attachment: Test57183.java (application/unregisterd-mime-type-to-avoid-ie-mime-sniffing, text), 8.57 KiB.

To run the testcase you will need a recent version of EclipseLink
(the RI for JPA/EJB3). An installer-zip can be found at:
http://www.eclipse.org/downloads/download.php?file=/rt/eclipselink/releases/2.1.1/eclipsel...

Extract the following files from the installer:
 - eclipselink.jar
 - eclipselink-dbwsutils.jar
 - javax.wsdl_1.6.2.v200806030405.jar
and compile the test.Test57183 class to some tempdir

To run the testcase:

propmt>java -classpath {path_to}/eclipselink.jar:\
{path_to}/eclipselink-dbwsutils.jar:\
{path_to}/javax.wsdl_1.6.2.v200806030405.jar:\
{path_to}/tempdir:\
{path_to}/mysql-connector-java-5.1.13-bin.jar \
-Ddb.user={user} -Ddb.pwd={passwd} -Ddb.url={url} test.Test57183 

The expected output will be something like:

[EL Fine]: 2010-10-04 16:23:33.457--DatabaseSessionImpl(32189467)--Connection(10272075)--Thread(Thread[main,5,main])--{ CALL emp.InOutArgsSP(?, ?, ?) }
	bind => [yuck, => U, => V]
[EL Fine]: 2010-10-04 16:23:33.489--DatabaseSessionImpl(32189467)--Thread(Thread[main,5,main])--SELECT 1
[EL Warning]: 2010-10-04 16:23:33.504--DatabaseSessionImpl(32189467)--Thread(Thread[main,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - @VERSION@.@QUALIFIER@): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Parameter index of 2 is out of range (1, 0)
Error Code: 0
Call: { CALL emp.InOutArgsSP(?, ?, ?) }
	bind => [yuck, => U, => V]
Query: ValueReadQuery()
bug 57183 strikes again!

When the older 5.1.7 driver jar is used, the test passes.

Mike, I'm sorry to knit-pick but can you make a standalone test case? I can not justify involving 3rd party SW (EclipseLink) into analysis.

simple test case

Attachment: Test57183b.java (application/unregisterd-mime-type-to-avoid-ie-mime-sniffing, text), 2.45 KiB.

I have uploaded a 'b' version of the test.Test57183 class, stripping away
all traces of EclipseLink.

In order to further simplify it, I took out the part where the Stored Procedure
is created.

The issue boils down to line 51:
    CallableStatement cStmt = conn.prepareCall("{ CALL " + STORED_PROC + "(?, ?, ?) }");

When I run the above code under either the 5.1.7 or 5.1.13 driver, everything
works.

However, if the CALL stanza includes the name of the current database schema:

    CallableStatement cStmt = conn.prepareCall("{ CALL emp." + STORED_PROC + "(?, ?, ?) }");

I get an "SQLState(42000) vendor code(1142)" error - 
SELECT command denied to user 'user'@'localhost' for table 'proc'

The difference being that the 5.1.7 driver continued execution regardless of
the error; the 5.1.13 stops and throws a second error:
Parameter index of 2 is out of range (1, 0)

I tried this on the nightly build of Connector/J - the behaviour is the same.

Is there some permission setting required to avoid the 1142 error - or
is there something that allows the CALL stanza to include the current
schema name?

Thanks in advance,
Mike

JUnit test case

Attachment: TestBug57183.java (text/x-java), 1.42 KiB.

Hi Mike,

I was not able to reproduce the error (see my testcase) and there is no feedback since Oct 2010. So I close this report as Can't repeat.
Please, feel free to reopen it if the problem still exists with current driver version.