Bug #9831 Possible NULL value problem with cachedrowset
Submitted: 11 Apr 2005 23:46 Modified: 31 Mar 2014 9:15
Reporter: Jeff Ferrell Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:all OS:Windows (Windows XP Pro)
Assigned to: Alexander Soklakov CPU Architecture:Any

[11 Apr 2005 23:46] Jeff Ferrell
Description:
I have been tring to debug this error for a long time now and i just can't seem to find an error in my code. Im sure its probley something simple but mabey not. 

Description : 

When using a Cachedrowset in java if i try to update an existing row on a field that does not contain null it throws exceptions. It will be eaiser to explain this by example. For example say we had the following table 

student 
id, first_name, last_name, hours_complete, major 

with values 1 , jeff , ferrell, NULL, computer science  

If i try to update major with a new value it throws syntax error but if hours_complete was not null say 67 it works or if major was NULL it will work as well. So basiclly if any fields are null before the one you are updating it will have a syntax error but only if what you are updating is not null. Very strang error. 

See my below code to try to repeat. Some of the code was in a DataBase class so if you want to use code you have to make that. Ive tryed every version of connectorj and the two newest mysql database versions 4.XX - 5.0. PLease help me find this error its killing me especially all the beer i have drank over it. 

How to repeat:
public static void main(String[] args){
       DataBase db = new DataBase();
       Connection conn = db.getMySqlConnection();
       
    try {
           
           CachedRowSet TempRowSet = new com.sun.rowset.CachedRowSetImpl();
           TempRowSet.setCommand("select * from bison where id = 'Jeff'");
           TempRowSet.execute(conn);
           TempRowSet.first();
           TempRowSet.updateDouble("NET_WORTH",45.6);
           TempRowSet.updateRow();
           TempRowSet.acceptChanges();
         
   
   
       } catch (SQLException sqle) {
           sqle.printStackTrace();
       
       }
   }

public Connection getMySqlConnection(){

       try{
           Class.forName("com.mysql.jdbc.Driver");
           Connection conn = DriverManager.getConnection(
                   "jdbc:mysql://" + this.strMySqlIP + "/" + this.strMySqlDataBase + "?useServerPrepStmts=false", this.strMySqlUserName, this.strMySqlPassword);
           return conn;
       }catch(Exception e){
           e.printStackTrace();
           return null;
       }
   }

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `animals`;
USE `animals`;
CREATE TABLE `bison` (
  `ID` varchar(20) NOT NULL,
  `SIRE` varchar(20) default NULL,
  `DAM` varchar(20) default NULL,
  `OWNER` varchar(50) default NULL,
  `ORIGIN` varchar(50) default NULL,
  `BREED` varchar(20) default NULL,
  `CCIA_NUMBER` varchar(20) default NULL,
  `CURRENT_HERD` varchar(20) default NULL,
  `SEX` char(1) default NULL,
  `DOB` date default NULL,
  `YEAR_WEIGHT_DATE` date default NULL,
  `FIRST_WEIGHT_DATE` date default NULL,
  `CURRENT_WEIGHT_DATE` date default NULL,
  `PURCHASE_DATE` date default NULL,
  `WEAN_DATE` date default NULL,
  `CURRENT_WEIGHT` double default NULL,
  `FIRST_WEIGHT` double default NULL,
  `YEAR_WEIGHT` double default NULL,
  `NET_WORTH` double default NULL,
  `ALIVE_INDICATOR` int(1) default NULL,
  `ACTIVE_INDICATOR` int(1) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB';
INSERT INTO `bison` (`ID`,`SIRE`,`DAM`,`OWNER`,`ORIGIN`,`BREED`,`CCIA_NUMBER`,`CURRENT_HERD`,`SEX`,`DOB`,`YEAR_WEIGHT_DATE`,`FIRST_WEIGHT_DATE`,`CURRENT_WEIGHT_DATE`,`PURCHASE_DATE`,`WEAN_DATE`,`CURRENT_WEIGHT`,`FIRST_WEIGHT`,`YEAR_WEIGHT`,`NET_WORTH`,`ALIVE_INDICATOR`,`ACTIVE_INDICATOR`) VALUES 
 ('Jeff','bob','tom','ky co.','temp2','test2','432','Test 1','F','2003-03-03',NULL,NULL,'2002-04-04',NULL,NULL,256,0,0,0,1,1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Suggested fix:
None that i know of.
[11 Apr 2005 23:51] Jeff Ferrell
I forgot my stack trace see below. 

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NET_WORTH = 45.6 WHERE ID = 'Jeff' AND SIRE = 'bob' AND DAM = 'tom' AND OWNER = ' at line 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1675)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2301)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2228)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1836)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1930)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1855)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1693)
	at com.sun.rowset.internal.CachedRowSetWriter.updateOriginalRow(CachedRowSetWriter.java:681)
	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:320)
	at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:835)
	at BTSBackend.DataObject.main(DataObject.java:158)
[11 Apr 2005 23:57] Jeff Ferrell
the ?useServerPrepStmts=false in the connection string can be removed and same error occurs i put that in there to see if it would be a work around. Just to let you guys know.
[12 Apr 2005 18:28] Jeff Ferrell
Just another update i am using jdk version 1.5. I guess for the time being im going to implement a stored procedure to bypass the bug or possible bug anyway later .
[12 Apr 2005 18:58] Mark Matthews
Wgat's the output if you add 'profileSQL=true' to your url?
[12 Apr 2005 19:12] Jeff Ferrell
did not change the outcome. Same error as before. Sorry.
[12 Apr 2005 19:17] Mark Matthews
I think you misunderstood. It wasn't supposed to affect the output. We need to see the SQL that Sun's code is generating. 'profileSQL=true' will log that to STDOUT. Please include that output in your reply.
[12 Apr 2005 19:50] Jeff Ferrell
ok sorry about that here is the trace for the program supplied 

"C:\Program Files\Java\jdk1.5.0_01\bin\javaw" -classpath "C:\projects\BTS\classes;C:\Documents and Settings\Jeff\Desktop\mysqlstuff\mysql-connector-java-3.2.0-alpha\mysql-connector-java-3.2.0-alpha-bin.jar;C:\Borland\JBuilder2005\lib\jdom.jar;C:\Borland\JBuilder2005\lib\xercesImpl.jar;C:\Borland\JBuilder2005\lib\xmlParserAPIs.jar;C:\Program Files\Java\jdk1.5.0_01\demo\jfc\CodePointIM\CodePointIM.jar;C:\Program Files\Java\jdk1.5.0_01\demo\jfc\Java2D\Java2Demo.jar;C:\Program Files\Java\jdk1.5.0_01\demo\plugin\jfc\CodePointIM\CodePointIM.jar;C:\Program Files\Java\jdk1.5.0_01\demo\plugin\jfc\Java2D\Java2Demo.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\im\indicim.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\im\thaiim.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\jce.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.5.0_01\jre\lib\rt.jar;C:\Program Files\Java\jdk1.5.0_01\lib\dt.jar;C:\Program Files\Java\jdk1.5.0_01\lib\htmlconverter.jar;C:\Program Files\Java\jdk1.5.0_01\lib\jconsole.jar;C:\Program Files\Java\jdk1.5.0_01\lib\tools.jar;C:\Program Files\Java\jdk1.5.0_01\sample\jnlp\servlet\jardiff.jar;C:\Program Files\Java\jdk1.5.0_01\sample\jnlp\servlet\jnlp-servlet.jar;C:\Program Files\Java\jdk1.5.0_01\sample\jnlp\servlet\jnlp.jar"  -Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_socket,address=jeff1:3628,suspend=y BTSBackend.DataObject 
Tue Apr 12 15:46:16 EDT 2005 INFO: Profiler Event: [QUERY] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET NAMES latin1
Tue Apr 12 15:46:16 EDT 2005 INFO: Profiler Event: [FETCH] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 30 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:16 EDT 2005 INFO: Profiler Event: [QUERY] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET character_set_results = NULL
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [FETCH] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [QUERY] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 1, resultset-id: 1, message: SHOW VARIABLES
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [FETCH] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 70 ms, connection-id: 0, statement-id: 1, resultset-id: 1
Tue Apr 12 15:46:18 EDT 2005 WARN: Profiler Event: [WARN] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 1, resultset-id: 1, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW VARIABLES
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [QUERY] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 2, resultset-id: 2, message: SHOW COLLATION
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [FETCH] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 10 ms, connection-id: 0, statement-id: 2, resultset-id: 2
Tue Apr 12 15:46:18 EDT 2005 WARN: Profiler Event: [WARN] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 2, resultset-id: 2, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW COLLATION
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [QUERY] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET autocommit=1
Tue Apr 12 15:46:18 EDT 2005 INFO: Profiler Event: [FETCH] at 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.internal.CachedRowSetReader.readData(CachedRowSetReader.java:137) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.internal.CachedRowSetReader.readData(CachedRowSetReader.java:137) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [PREPARE] at 	at com.sun.rowset.internal.CachedRowSetReader.readData(CachedRowSetReader.java:142) duration: 10 ms, connection-id: 0, statement-id: -1, resultset-id: -1, message: select * from bison where id = 'Jeff'
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [EXECUTE] at 	at com.sun.rowset.internal.CachedRowSetReader.readData(CachedRowSetReader.java:168) duration: 10 ms, connection-id: 0, statement-id: 3, resultset-id: -1
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 4, resultset-id: 4, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 4, resultset-id: 4
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 4, resultset-id: 4, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:679) duration: 0 ms, connection-id: 0, statement-id: 5, resultset-id: 5, message: SHOW CHARACTER SET
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:679) duration: 10 ms, connection-id: 0, statement-id: 5, resultset-id: 5
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:679) duration: 0 ms, connection-id: 0, statement-id: 5, resultset-id: 5, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW CHARACTER SET
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 6, resultset-id: 6, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: 6
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 6, resultset-id: 6, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 7, resultset-id: 7, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 7, resultset-id: 7
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 7, resultset-id: 7, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 8, resultset-id: 8, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 8, resultset-id: 8
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 8, resultset-id: 8, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 9, resultset-id: 9, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 9, resultset-id: 9
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 9, resultset-id: 9, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 10, resultset-id: 10, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 10, resultset-id: 10
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 10, resultset-id: 10, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 11, resultset-id: 11, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 11, resultset-id: 11
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 11, resultset-id: 11, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 12, resultset-id: 12, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 12, resultset-id: 12
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 12, resultset-id: 12, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 13, resultset-id: 13, message: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:35 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 10 ms, connection-id: 0, statement-id: 13, resultset-id: 13
Tue Apr 12 15:46:35 EDT 2005 WARN: Profiler Event: [WARN] at 	at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:674) duration: 0 ms, connection-id: 0, statement-id: 13, resultset-id: 13, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: SHOW FULL COLUMNS FROM `animals`.`bison`
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:255) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET autocommit=0
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:255) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [QUERY] at 	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:258) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [FETCH] at 	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:258) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [PREPARE] at 	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:281) duration: 0 ms, connection-id: 0, statement-id: -1, resultset-id: -1, message: INSERT INTO animals.bison (ID, SIRE, DAM, OWNER, ORIGIN, BREED, CCIA_NUMBER, CURRENT_HERD, SEX, DOB, YEAR_WEIGHT_DATE, FIRST_WEIGHT_DATE, CURRENT_WEIGHT_DATE, PURCHASE_DATE, WEAN_DATE, CURRENT_WEIGHT, FIRST_WEIGHT, YEAR_WEIGHT, NET_WORTH, ALIVE_INDICATOR, ACTIVE_INDICATOR) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [PREPARE] at 	at com.sun.rowset.internal.CachedRowSetWriter.updateOriginalRow(CachedRowSetWriter.java:455) duration: 0 ms, connection-id: 0, statement-id: -1, resultset-id: -1, message: SELECT ID, SIRE, DAM, OWNER, ORIGIN, BREED, CCIA_NUMBER, CURRENT_HERD, SEX, DOB, YEAR_WEIGHT_DATE, FIRST_WEIGHT_DATE, CURRENT_WEIGHT_DATE, PURCHASE_DATE, WEAN_DATE, CURRENT_WEIGHT, FIRST_WEIGHT, YEAR_WEIGHT, NET_WORTH, ALIVE_INDICATOR, ACTIVE_INDICATOR FROM animals.bison WHERE ID = ? AND SIRE = ? AND DAM = ? AND OWNER = ? AND ORIGIN = ? AND BREED = ? AND CCIA_NUMBER = ? AND CURRENT_HERD = ? AND SEX = ? AND DOB = ? AND YEAR_WEIGHT_DATE IS NULL AND FIRST_WEIGHT_DATE IS NULL AND CURRENT_WEIGHT_DATE = ? AND PURCHASE_DATE IS NULL AND WEAN_DATE IS NULL AND CURRENT_WEIGHT = ? AND FIRST_WEIGHT = ? AND YEAR_WEIGHT = ? AND NET_WORTH = ? AND ALIVE_INDICATOR = ? AND ACTIVE_INDICATOR = ? 
Tue Apr 12 15:46:56 EDT 2005 INFO: Profiler Event: [EXECUTE] at 	at com.sun.rowset.internal.CachedRowSetWriter.updateOriginalRow(CachedRowSetWriter.java:476) duration: 0 ms, connection-id: 0, statement-id: 15, resultset-id: -1
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NET_WORTH = ? WHERE ID = ? AND SIRE = ? AND DAM = ? AND OWNER = ? AND ORIGIN = ?' at line 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)
	at com.mysql.jdbc.Connection.serverPrepare(Connection.java:4932)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1312)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1284)
	at com.sun.rowset.internal.CachedRowSetWriter.updateOriginalRow(CachedRowSetWriter.java:660)
	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:320)
	at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:835)
	at BTSBackend.DataObject.main(DataObject.java:159)
[12 Apr 2005 20:02] Mark Matthews
Okay, that wasn't helpful because the update query that CachedRowset is trying to generate has a syntax error (so it doesn't get logged).

Instead try this configuration property so we can see the query Sun's code is generating:

dumpQueriesOnException=true
[12 Apr 2005 20:17] Jeff Ferrell
ok here is the new stack trace. I would look at it closer but people are pushing me right now to complete a stored proc. It appears its trying to set the dates when they were never updated. 

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NET_WORTH = ? WHERE ID = ? AND SIRE = ? AND DAM = ? AND OWNER = ? AND ORIGIN = ?' at line 1

Query being prepared when exception was thrown:

UPDATE animals.bison SET YEAR_WEIGHT_DATE = ? , FIRST_WEIGHT_DATE = ? , PURCHASE_DATE = ? , WEAN_DATE = ? NET_WORTH = ? WHERE ID = ? AND SIRE = ? AND DAM = ? AND OWNER = ? AND ORIGIN = ? AND BREED = ? AND CCIA_NUMBER = ? AND CURRENT_HERD = ? AND SEX = ? AND DOB = ? AND YEAR_WEIGHT_DATE IS NULL AND FIRST_WEIGHT_DATE IS NULL AND CURRENT_WEIGHT_DATE = ? AND PURCHASE_DATE IS NULL AND WEAN_DATE IS NULL AND CURRENT_WEIGHT = ? AND FIRST_WEIGHT = ? AND YEAR_WEIGHT = ? AND NET_WORTH = ? AND ALIVE_INDICATOR = ? AND ACTIVE_INDICATOR = ? 
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)
	at com.mysql.jdbc.Connection.serverPrepare(Connection.java:4932)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1312)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1284)
	at com.sun.rowset.internal.CachedRowSetWriter.updateOriginalRow(CachedRowSetWriter.java:660)
	at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:320)
	at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:835)
	at BTSBackend.DataObject.main(DataObject.java:159)
[12 Apr 2005 20:24] Mark Matthews
> UPDATE animals.bison SET YEAR_WEIGHT_DATE = ? , FIRST_WEIGHT_DATE = ? ,
> PURCHASE_DATE = ? , WEAN_DATE = ? NET_WORTH = ? WHERE ID = ? AND SIRE 

Notice the missing comma here                    ^^^^^^^^^

Since CachedRowset generates that SQL, looks like a Sun bug. Let me know if you think differently.
[12 Apr 2005 20:28] Mark Matthews
Looks like non-proportinal font messed up my arrows ...

Notice how the SQL that Sun's cached rowset has generated is missing a comment between
 
WEAN_DATE = ? 
  and  
NET_WORTH = ? 

close to the arrows in the previous comment.
[12 Apr 2005 20:36] Jeff Ferrell
yep i see. damn i was hoping it was a mysql bug. Sun will take years to fix it :(. Well i guess if you dont have any ideas i will have to work around it. Althought i guess i could open up suns source but that takes way to much effort. Thanks anyway.
[13 Apr 2005 20:56] Jeff Ferrell
Just to update it seems it was a sun bug. I did'nt realize i was running 1.5.1 and installed jdk 1.5.2 and the bug was resolved thanks for the advice to lead me into that direction.
[13 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".
[31 Mar 2014 9:15] Alexander Soklakov
I close this report as Not a Bug since the problem was outside of c/J code.