MySQL Bugs: #1153: Mistaken sql-exception "Invalid column name"

Bug #1153	Mistaken sql-exception "Invalid column name"
Submitted:	27 Aug 2003 10:55	Modified:	3 Sep 2003 12:58
Reporter:	Ralf Hauser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.0-alpha	OS:	Linux (RedHat 9)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
If I use a wrong field name, normally I get the above exception INCLUDING the field name.

Now, I am only getting the below WITHOUT a field name:
<<
- SQLException: "Invalid column name"
- SQLState: "null"
- VendorError: "0"
>>

I print the query before I execute it.
This way, I can execute the query also with other clients and it works fine.

One suspicion is: The field name is too long for your implementation?

P.S.: happened already with mysql-connector-java-3.0.8-stable-bin.jar

How to repeat:
use field name "pay_reply_any_recipient"

Suggested fix:
??

is it possible that this happens only in SELECT, but not in UPDATE statements?

What version of MySQL are you using (4.1?). Can we have a _full_ stacktrace as well?

mysql 4.1.24

ERROR [Thread-4] (MySqlSystem.java:1331) - Invalid column name
java.sql.SQLException: Invalid column name
        at sun.jdbc.rowset.CachedRowSet.getColIdxByName(CachedRowSet.java:1383)
        at sun.jdbc.rowset.CachedRowSet.getString(CachedRowSet.java:2167)
        at com.privasphere.privalope.db.MySqlSystem.getReciPaying(MySqlSystem.ja
va:1318)
        at com.privasphere.privalope.control.action.FindUserAction.perform(Fi
ndKeyUserAction.java:81)
        at org.apache.struts.action.ActionServlet.processActionPerform(ActionSer
vlet.java:1787)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:158
6)
        at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:492)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:256)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:191)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
torBase.java:551)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve
.java:246)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:
2415)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:180)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatche
rValve.java:171)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:172)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:
509)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:174)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:22
3)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:594)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:392)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:565)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:619)
        at java.lang.Thread.run(Thread.java:534)

If you look at your stack trace, the exception is being thrown by sun.jdbc.rowset.CachedRowSet.getColIdxByName() and the package that contains Connector/J (com.mysql.jdbc) is not showing up _anywhere_ in your stacktrace, so in this case, you're not using Connector/J at all :(

apologies, my developer claimed so far that we are using the mysql connector that apparently isn't true.
When I try to switch in the MySqlSystem class, I get in eclipse the following error types:
1) Type mismatch: cannot convert from java.sql.ResultSet to com.mysql.jdbc.ResultSet ...
2) Type mismatch: cannot convert from java.sql.Connection to com.mysql.jdbc.Connection
3) Type mismatch: cannot convert from java.sql.PreparedStatement to com.mysql.jdbc.PreparedStatement

or when compiling:
    [javac] .../MySqlSystem.java:1897: incompatible types
    [javac] found   : java.sql.ResultSet
    [javac] required: com.mysql.jdbc.ResultSet
...
    [javac] .../MySqlSystem.java:1965: incompatible types
    [javac] found   : java.sql.Connection
    [javac] required: com.mysql.jdbc.Connection
...
    [javac] .../MySqlSystem.java:1991: incompatible types
    [javac] found   : java.sql.PreparedStatement
    [javac] required: com.mysql.jdbc.PreparedStatement
...
Do you have a guide for switchers?
just changing the imports to
...
import java.sql.SQLException;
...
import sun.jdbc.rowset.CachedRowSet;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
...
apparently is not enough?

just for the benefit of the interaction between mysql and Java (sun's though) in General:

The error started to show up even more and one reason I could identify was

user.setSurname(crs.getString("surname"))  // good
user.setSurname(crs.getString(" surname "))  // error with no mentioning of field name.

Also, the original error still is there!

With JDBC, you program to the interfaces in the java.sql package. You do not (and should not) import the packages and classes that make up the JDBC driver (com.mysql.jdbc in this case), as they _implement_ the interfaces in the java.sql package. There is no 'switching' concept in JDBC, other than using a different JDBC URL, and making sure the driver gets loaded before you try and connect to the URL.

See http://www.mysql.com/documentation/connector-j/index.html for more information, and consider looking at the Java tutorial on JDBC at http://java.sun.com/docs/books/tutorial/jdbc/index.html if you or your staff are unfamiliar with JDBC.

Apologies, but now I am getting increasingly confused:
1) on my redhat, the 
[root@calimero ]# echo $CLASSPATH
/usr/java/j2sdk/lib
[root@calimero lib]# ls /usr/java/j2sdk/lib
dt.jar  htmlconverter.jar  ir.idl  orb.idl  tools.jar
==> it does seem that I don't use any other jdbc than the one from mysql.

Or do I miss something? Not being a java expert: is there a "which"-command equivalent for the class-path?

2) Is it possible that I just shouldn't use "CachedRowSet" but "ResultSet" as per the example http://www.mysql.com/documentation/connector-j/index.html#id2802095 and then, everything might be fine, i.e. the mysql.com-way?

echo $CLASSPATH won't show everything that is used by your JVM. For example, .jar files in JAVA_HOME/jre/lib/ext will be used by the JVM, but not show in $CLASSPATH, and Tomcat loads .jar files from all over the place, including $TOMCAT_HOME/common/lib, WEB-INF/lib from your webapp, WEB-INF/classes from your webapp, etc, etc. Note that the MySQL Connector/J jar file doesn't show up in your echo $CLASSPATH.

I can guarantee you that Connector/J is not causing the exception when your stack trace looks like:

sun.jdbc.rowset.CachedRowSet.getColIdxByName(CachedRowSet.java:1383)
        at
sun.jdbc.rowset.CachedRowSet.getString(CachedRowSet.java:2167)

You should _really_ understand what is going on with the J2EE classpath before you go any further, or you will get _very_ confused. If you're using Tomcat, a good reference is:

http://jakarta.apache.org/tomcat/tomcat-4.1-doc/class-loader-howto.html

Mark,

dropping 

<<CachedRowSet crs = null;
crs = new CachedRowSet();
...
crs.populate(rs);>>

and directly working on ResultSet solved it - I got a decent error message and solved the problem!

Thanks for the help!

       Ralf

I am using the same sun cached rowset with Oracle back end, Oracle connection pool that uses oracles jdbc thin driver. I have the same exact problem but it is sporadic! It works perfectly sometimes and sometimes it just fails on acceptUpdates. From the look of it, it would appear there is a bug that prevents the getIdxByName method from working correctly. Try using ordinal column position indicator such as setString(1,"first column"); then calling accept changes to see if that works for you. I know exactly what you are saying....