| Bug #9288 | parameter index out of range | ||
|---|---|---|---|
| Submitted: | 18 Mar 2005 22:13 | Modified: | 20 May 2005 19:27 |
| Reporter: | Ron Yust | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 3.1.7 | OS: | Windows (Windows XP) |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
[18 Mar 2005 22:13]
Ron Yust
[18 Mar 2005 22:18]
Mark Matthews
Ron, what version of the server is this? Can you post a _full_ stack trace as well please?
[18 Mar 2005 22:37]
Ron Yust
The MySQL server version is MySQL 4.1.8-nt. Here is a stack trace: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2297) com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2321) com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:684) com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:1099) org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) server.system.CIListService.ExecuteSQL(CIListService.java:469) server.system.CIListService.Execute(CIListService.java:395) server.parts.System_Configuration.System_Configuration_List.<init>(System_Configuration_List.java:45) sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) java.lang.reflect.Constructor.newInstance(Constructor.java:494) server.system.CIUtilities.CreateService(CIUtilities.java:2658) server.system.CIUtilities.CreateServiceMessage(CIUtilities.java:2565) server.system.CIServiceFactoryRMI.ExecuteService(CIServiceFactoryRMI.java:202) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) java.lang.reflect.Method.invoke(Method.java:585) sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294) sun.rmi.transport.Transport$1.run(Transport.java:153) java.security.AccessController.doPrivileged(Native Method) sun.rmi.transport.Transport.serviceCall(Transport.java:149) sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460) sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701) java.lang.Thread.run(Thread.java:595)
[14 Apr 2005 19:10]
Ron Yust
Is this issue dead? I am unsure how to proceed to resolve the problem.
[14 May 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[18 May 2005 13:11]
Ron Yust
What information hasn't been supplied? I have provided the information that was requested. What do I need to do to get attention to this issue? The problem still exists regardless.
[18 May 2005 13:17]
MySQL Verification Team
Re-open
[18 May 2005 14:08]
Mark Matthews
Ron,
I've tried to reproduce this with the following testcase with the nightly build of 3.1, and it doesn't show the bug (note the clientPrepareStatement() just ensures that the driver will use the same code it does for MySQL-4.0, which is where your bug is isolated when it is connected to newer versions of the server that feature server-side prepared statements).
public void testBug9288() throws Exception {
String tableName = "testBug9288";
PreparedStatement pStmt = null;
try {
createTable(tableName, "(field1 VARCHAR(32), field2 INT)");
pStmt = ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement(
"SELECT COUNT(1) FROM " +
tableName + " WHERE "
+ "field1 LIKE '%' ESCAPE '\' AND "
+ "field2 > ?");
pStmt.setInt(1, 0);
this.rs = pStmt.executeQuery();
} finally {
if (this.rs != null) {
this.rs.close();
this.rs = null;
}
if (pStmt != null) {
pStmt.close();
}
}
}
Can you try the nightly of 3.1 from http://downloads.mysql.com/snapshots.php ?
Thanks,
-Mark
[18 May 2005 18:28]
Ron Yust
Mark, I downloaded the driver you indicated (and even 3.2), but I still get the same error as before. It always throws an exception on the preparedStatement.setInt() method whenever the LIKE and ESCAPE clauses are in the statement. Again, this does not happen with Oracle, SQL Server, or DB2 using the same code. What else can I try? Ron
[18 May 2005 18:32]
Mark Matthews
Ron, 1) You could try providing a complete standalone testcase that demonstrates the bug, that way we don't have to go back and forth with me saying "Nope, can't repeat it", and you saying "Yes, but it's still broken" :) This would include the statements to create the table(s) in question, the prepared statements, and the code you use to set the parameters. 2) You can make absolutely sure you're using the latest version of the driver, and not picking up some older one on your classpath (this happens more often than you'd think). If you post your latest stack traces, I can make a guess at what version is generating the error.
[18 May 2005 21:55]
Ron Yust
Mark, If you already have a test case that works, it would be quicker and simplier for me to use that instead of gutting our framework to isolate specific code. Can you send me what you tested with? I verified that I was using the latest driver because I saw new diagnostic messages appear on the console. -Ron
[18 May 2005 22:00]
Mark Matthews
If you use testsuite.BaseTestCase (it comes with the driver), and look at the code I pasted here, that's what I'm working with. You'll need to put junit.jar somewhere on your classpath, as we don't ship it with the driver.
[19 May 2005 18:50]
Ron Yust
Mark,
I found the problem, but not how to correct it.
Basically, our application allows users to enter an asterisk "*" as a wildcard in queries, but our code will convert it to a "%" prior to sending it to the preparedStatement. As I said before, this same code works fine with Oracle, SQL Server, and DB2 JDBC drivers so something is unique about the mySQL driver.
Below is an example of how you can replicate the problem. Try running it and see if you get the same error.
import java.sql.*;
public class mySQLtest {
static String id = "userid";
static String password = "password";
static String url = "jdbc:mysql://server:3306/db?useNewIO=true&useServerPrepStmts=true";
static String query = "SELECT COUNT(1) FROM System_Configuration WHERE System_Configuration.Config_Name LIKE * ESCAPE '\' AND System_Configuration.System_Configuration_Tkn > ?";
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, id, password);
PreparedStatement pStmt = conn.prepareStatement(ConvertForLikeSearch(query));
pStmt.setInt(1, 0);
ResultSet rs = pStmt.executeQuery();
rs.close();
conn.close();
} catch (Exception e) {e.printStackTrace();}
}
private static final String ConvertForLikeSearch(String value) {
value = value.replaceAll("_", '\\' + "_");
value = value.replaceAll("%", '\\' + "%");
value = value.replace('*', '%');
value = value.replace('?', '_');
return value;
}
}
[19 May 2005 19:08]
Mark Matthews
Ron, Okay, let me ask if this makes sense. Ignoring the prepared statement portion, and just running your code to regex-replace the query, you end up with this result: "SELECT COUNT(1) FROM System_Configuration WHERE System_Configuration.Config_Name LIKE % ESCAPE '' AND System_Configuration.System_Configuration_Tkn > _" I don't see a parameter marker anywhere in there :(
[19 May 2005 19:10]
Ron Yust
Sorry, didn't type in the query completely correct. Use this to see it fail: "SELECT COUNT(1) FROM System_Configuration WHERE System_Configuration.Config_Name LIKE '*' ESCAPE '\' AND System_Configuration.System_Configuration_Tkn > ?"
[19 May 2005 19:15]
Ron Yust
Hold on... my example is bad. I didn't build it right from our code. Let me rework and resend.
[19 May 2005 19:15]
Mark Matthews
Ron, not really any different (even though I tried it). Your code is still going to replace "?" with "_", which means the parameter markers go away :(
[19 May 2005 19:59]
Ron Yust
Ok Mark, I think I got it now. I was looking in the right place, but not the exact location. It is the ESCAPE clause causing the problem. Using "ESCAPE '\'" works in your driver, but not in Oracle's, but if you use "ESCAPE '\\'", it will work in all other JDBC drivers except mySQL. Try the code below:
import java.sql.*;
public class mySQLtest {
static String id = "ryust";
static String password = "667288";
static String url = "jdbc:mysql://desktop-06:3306/cw31?useNewIO=true&useServerPrepStmts=true";
static String query = "SELECT COUNT(1) FROM System_Configuration WHERE System_Configuration.Config_Name LIKE '*' ESCAPE '\\' AND System_Configuration.System_Configuration_Tkn > ?";
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, id, password);
PreparedStatement pStmt = conn.prepareStatement(query);
pStmt.setObject(1, new Integer(0));
ResultSet rs = pStmt.executeQuery();
rs.close();
conn.close();
} catch (Exception e) {e.printStackTrace();}
}
}
[19 May 2005 20:25]
Ron Yust
Mark, I've decided to remove the ESCAPE clause from our code to get by the mySQL issue. But now I am randomly getting a SQLException when running the same query multiple times. What is the meaning of "ResultSet is from UPDATE. No Data"?
[19 May 2005 23:17]
Mark Matthews
Ron, I've only seen that error _one_ other time, and it just mysteriously went away and I was never able to track it down. Do you have a reproducible version of it? For some reason the driver thinks (or the server is telling it) that you've done an UPDATE/DELETE/INSERT statement, but you've called executeQuery().
[20 May 2005 0:36]
Ron Yust
No Mark I don't have a test case for you. It occurred about 70% of the time running a query in our app. When I went back to the older 3.1.7 driver the problem went away.
[20 May 2005 1:18]
Mark Matthews
Ron, To have any hope of tracking down this "new" bug (I assume this is in one of the nightly snapshots, if so, could you please say _exactly_ what version it is you're using), we'll need some way to reproduce it. If it is at all possible for you to somehow extract something that replays the queries and can most of the time show the error, we'd be happy to take a look at it (you can even upload it privately, MySQL developers would be the only ones to see it if you choose the "Only MySQL Developers will see the file" option for the "Privacy" option on the files tab above. We can try and eliminate some factors though. What happens if you add "useReadAheadInput=false&useUnbufferedInput=true" to your URL properties? Does it still give the SQLExceptions?
[20 May 2005 19:14]
Ron Yust
Mark, This is the driver version I used when getting the new error: ySQL-AB JDBC Driver mysql-connector-java-3.1-nightly-20050518 ( $Date: 2005/05/3 18:58:37 $, $Revision: 1.1.2.1 $ ) The additional URL properties you suggested had no positive effect. I don't have a test case I can give you since our app is so large and the SQL is autogenerated by our framework. As I said the error occurs about 70% of the time on every query. You can run the query over and over and sometimes it works, but most times it doesn't. I've altered our code so as not to use the "\" as an escape character. Not sure why mySQL had a problem with Java specifiying it as "\\" within a string. Thanks for all your help. We can tell our customers they can use mySQL with our application now. Unless the new driver I tested doesn't correct the "new" bug before it goes GA. :) -Ron
[20 May 2005 19:27]
Mark Matthews
Ron, Is it _always_ the same query that has the error? If so, it would be _really_ helpful if you could give us the "flavor" of queries before the exception and the one that causes the exception so we could try and reproduce it. Unfortunately, the bug won't just magically go away for good (in most cases) if we don't have a way to reproduce it under controlled conditions so we can 1) Investigate the cause 2) Create a testcase that creates those conditions every time and 3) Then fix the bug. -Mark
[20 May 2005 20:29]
Ron Yust
Mark, It is EVERY query our framework makes regardless of the table or search criteria. I understand completely about the test case. I will attempt to retest to see if I can find the pattern that triggers it, but it is random. I am on vacation next week so I won't be able to test until early June. Let me know if you find anything in the meantime. -Ron
[12 Oct 2006 7:19]
jose che
It just happened to me,
mysql 5.0.24a-community-nt
mysql-connector-java-3.1.13-bin.jar
on windows vista rc1
with Bundled Tomcat (5.5.9)
stacktrace:
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2474)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2500)
at com.mysql.jdbc.PreparedStatement.setNull(PreparedStatement.java:2536)
at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:2685)
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.setParameters(QueryTagSupport.java:295)
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:214)
at org.apache.jsp.searchpage_jsp._jspx_meth_sql_query_0(searchpage_jsp.java:232)
at org.apache.jsp.searchpage_jsp._jspService(searchpage_jsp.java:136)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
the code:
<sql:setDataSource scope="session" var="dtasrc" url="<%= urldb %>" driver="com.mysql.jdbc.Driver"
user="<%= user %>" password="<%= pass %>" />
<c:set var="firstname" value="${firstname}" />
<c:set var="lastname" value="${lastname}" />
<sql:query var="results" dataSource="${dtasrc}">SELECT * FROM SimplePerson WHERE firstname like '%?%' and lastname like = '%?%' order by firstname, lastname
<sql:param value="${firstname}" />
<sql:param value="${lastname}" />
</sql:query>
<display:table name="${results.rows}">
<display:column property="id" title="ID" sortable="true" />
<display:column property="FirstName" title="First Name" sortable="true" />
<display:column property="LastName" title="Last Name" sortable="true" />
<display:column property="Sex" title="Sex" sortable="true" />
</display:table>
[12 Oct 2006 12:57]
Mark Matthews
Ron, Parameters markers are supposed to be "bare", (i.e. not surrounded in quotes). If you want to use '%?%', you actually just put ? and then set that _parameter_ to "%[yourstring]%". Given that I see two LIKE '%...' maybe you want to consider using a fulltext index instead (it can often times be much more efficient than the table scans you're going to end up with using a wildcard LIKE expression). -Mark
[12 Oct 2006 13:04]
Mark Matthews
Sorry, that last comment was meant for Jose.
[7 Feb 2007 19:59]
srinivas mokkarala
I noticed that this Exception occurs when paramenters are passed using ":n" syntax instead of "?" as required by JDBC. The ":n" syntax is specific to Oracle.
I was using MySQL 5.0.18 and the corresponding mysql_connector.jar
Here is the relevant portion of the stacktrace:
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2306)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2332)
at com.mysql.jdbc.PreparedStatement.setBoolean(PreparedStatement.java:1886)
at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.setBoolean(PreparedStatementWrapper.java:1
80)
Thanks,
Srinivas Mokkarala
