I think I can say that SHOW COLLATION and SHOW VARIABLES is not being sent by iBATIS. It may be from the DBCP configuration that you have that validates connections.
-Richard -----Original Message----- From: Branislav Cavlin [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 12:34 PM To: [email protected] Subject: RE: Exception when connecting to DB We are using Spring framework in our configuration, and we use it in about 15 application, but this is the only one that gives us this message. The problem is that this exception is raised on SHOW COLLATION and SHOW VARIABLES statements, and not actual Query. I am not sure what can cause this. SQL map: <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd";> <sqlMapConfig> <sqlMap resource="ca/nuvoxx/btier/dao/ibatis/sql/Surveymntb.xml"/> <sqlMap resource="ca/nuvoxx/btier/dao/ibatis/sql/Transcriptions.xml"/> </sqlMapConfig> ... Surveymntb.xml: <?xml version='1.0'?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd";> <!-- WARNING: This is an autogenerated file --> <sqlMap namespace="Surveymntb"> <resultMap class="ca.nuvoxx.btier.beans.Surveymntb" id="surveymntb-result"> <result property="answer14" column="Answer14" /> <result property="answer25" column="Answer25" /> <result property="answer05" column="Answer05" /> <result property="answer02" column="Answer02" /> <result property="clid" column="CLID" /> <result property="answer04" column="Answer04" /> <result property="answer09" column="Answer09" /> <result property="certificate" column="Certificate" /> <result property="answer18" column="Answer18" /> <result property="answer21" column="Answer21" /> <result property="answer20" column="Answer20" /> <result property="timestamp" column="Timestamp" /> <result property="answer29" column="Answer29" /> <result property="answer08" column="Answer08" /> <result property="answer12" column="Answer12" /> <result property="answer28" column="Answer28" /> <result property="surveyid" column="SurveyID" /> <result property="answer06" column="Answer06" /> <result property="answer30" column="Answer30" /> <result property="answer22" column="Answer22" /> <result property="answer01" column="Answer01" /> <result property="answer23" column="Answer23" /> <result property="answer13" column="Answer13" /> <result property="statusid" column="StatusID" /> <result property="answer16" column="Answer16" /> <result property="answer07" column="Answer07" /> <result property="exportedat" column="ExportedAt" /> <result property="duration" column="Duration" /> <result property="answer10" column="Answer10" /> <result property="answer19" column="Answer19" /> <result property="answer24" column="Answer24" /> <result property="answer26" column="Answer26" /> <result property="answer11" column="Answer11" /> <result property="answer03" column="Answer03" /> <result property="answer17" column="Answer17" /> <result property="answer15" column="Answer15" /> <result property="answer27" column="Answer27" /> </resultMap> <resultMap class="java.lang.Long" id="surveymntb-primitive-result"> <result property="surveyid" column="SurveyID" /> </resultMap> <select id="getSurveymntbList" resultClass="ca.nuvoxx.btier.beans.Surveymntb" resultMap="surveymntb-result"> <![CDATA[ select * from Empathica.tbl_MnTB_200510 where ExportedAt = '0000-00-00 00:00:00'AND StatusID IN ('C', 'T') ]]> </select> ... </sqlMap> -----Original Message----- From: Yee, Richard K CTR DMDC [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 2:19 PM To: '[email protected]' Subject: RE: Exception when connecting to DB Cavlin, What do you have for your sqlmap configuration? -Richard -----Original Message----- From: Branislav Cavlin [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 8:36 AM To: [email protected] Subject: Exception when connecting to DB Hi, I am using these drivers: 10/04/2005 09:21 AM 559,366 commons-collections.jar 06/14/2006 10:34 AM 107,631 commons-dbcp.jar 10/04/2005 09:21 AM 225,375 commons-httpclient-2.0.2.jar 10/04/2005 09:21 AM 207,723 commons-lang.jar 05/09/2006 10:08 PM 52,915 commons-logging.jar 10/04/2005 09:21 AM 42,492 commons-pool.jar 05/17/2006 02:08 PM <DIR> CVS 11/30/2006 05:18 PM 383,531 ibatis-2.3.0.677.jar 08/16/2006 06:51 PM 85,587 ibatis-common-2.jar 08/16/2006 06:51 PM 322,368 ibatis-sqlmap-2.jar 09/14/2006 01:04 AM 367,444 log4j-1.2.14.jar 10/20/2006 12:17 AM 495,944 mysql-connector-java-5.0.4-bin.jar 01/08/2007 08:21 PM 2,593,850 spring.jar 02/23/2007 11:34 AM 0 test.txt 10/04/2005 09:21 AM 361,173 velocity-1.4.jar I get this exception when connection to the DB (MySQL 5.0.27): Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 20 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET NAMES latin1 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 10 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 9 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET character_set_results = NULL Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 50 ms, connection-id: 0, statement-id: 1, resultset-id: 1, message: SHOW VARIABLES Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 10 ms, connection-id: 0, statement-id: 1, resultset-id: 1 Fri Feb 23 11:28:25 EST 2007 WARN: Profiler Event: [WARN] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 50 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 Full stack trace of location where event occurred: ** BEGIN NESTED EXCEPTION ** java.lang.Throwable STACKTRACE: java.lang.Throwable at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1785) at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169) at com.mysql.jdbc.Connection.loadServerVariables(Connection.java:4317) at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3936 ) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2683) at com.mysql.jdbc.Connection.<init>(Connection.java:1531) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:26 6) at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableCon nect ionFactory.java:290) at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicD ataS ource.java:877) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource .jav a:851) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.ja va:5 40) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(Data Sour ceUtils.java:112) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.getC onne ction(TransactionAwareDataSourceProxy.java:108) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(Data Sour ceUtils.java:112) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSo urce Utils.java:77) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClient Temp late.java:182) at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResul t(Sq lMapClientTemplate.java:219) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapC lien tTemplate.java:266) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapC lien tTemplate.java:260) at ca.nuvoxx.btier.dao.ibatis.SurveymntbDAOImpl.getSurveymntbList(Surveymnt bDAO Impl.java:14) at ca.nuvoxx.btier.standalone.SurveymntbClient.run(SurveymntbClient.java:85 ) at ca.nuvoxx.btier.standalone.SurveymntbClient.main(SurveymntbClient.java:2 62) ** END NESTED EXCEPTION ** Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 29 ms, connection-id: 0, statement-id: 2, resultset-id: 2, message: SHOW COLLATION Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 2, resultset-id: 2 Fri Feb 23 11:28:25 EST 2007 WARN: Profiler Event: [WARN] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 29 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 Full stack trace of location where event occurred: ** BEGIN NESTED EXCEPTION ** java.lang.Throwable STACKTRACE: java.lang.Throwable at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1785) at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169) at com.mysql.jdbc.Connection.buildCollationMapping(Connection.java:1645) at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3938 ) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2683) at com.mysql.jdbc.Connection.<init>(Connection.java:1531) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:26 6) at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableCon nect ionFactory.java:290) at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicD ataS ource.java:877) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource .jav a:851) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.ja va:5 40) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(Data Sour ceUtils.java:112) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.getC onne ction(TransactionAwareDataSourceProxy.java:108) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(Data Sour ceUtils.java:112) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSo urce Utils.java:77) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClient Temp late.java:182) at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResul t(Sq lMapClientTemplate.java:219) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapC lien tTemplate.java:266) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapC lien tTemplate.java:260) at ca.nuvoxx.btier.dao.ibatis.SurveymntbDAOImpl.getSurveymntbList(Surveymnt bDAO Impl.java:14) at ca.nuvoxx.btier.standalone.SurveymntbClient.run(SurveymntbClient.java:85 ) at ca.nuvoxx.btier.standalone.SurveymntbClient.main(SurveymntbClient.java:2 62) ** END NESTED EXCEPTION ** Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 20 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 10 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET sql_mode='STRICT_TRANS_TABLES' Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 20 ms, connection-id: 52595, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 0 ms, connection-id: 52595, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 30 ms, connection-id: 52595, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 0 ms, connection-id: 52595, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 30 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET NAMES latin1 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 10 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET character_set_results = NULL Fri Feb 23 11:28:25 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 20 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 19 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET sql_mode='STRICT_TRANS_TABLES' Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverC onne ctionFactory.java:37) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 20 ms, connection-id: 52596, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 0 ms, connection-id: 52596, statement-id: 999, resultset-id: 0 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [PREPARE] at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(Delegating Conn ection.java:185) duration: 60 ms, connection-id: 52596, statement-id: 3, resultset-id: -1, message: select * from Empathica.tbl_MnTB_200510 where ExportedAt = '0000-00-00 00:00:00'AND StatusID IN ('C', 'T') Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [EXECUTE] at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPr epar edStatement.java:168) duration: 50 ms, connection-id: 52596, statement-id: 3, resultset-id: -1, message: select * from Empathica.tbl_MnTB_200510 where ExportedAt = '0000-00-00 00:00:00'AND StatusID IN ('C', 'T') Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [QUERY] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 20 ms, connection-id: 52596, statement-id: 999, resultset-id: 0, message: SET autocommit=1 Fri Feb 23 11:28:26 EST 2007 INFO: Profiler Event: [FETCH] at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nect ion.java:268) duration: 0 ms, connection-id: 52596, statement-id: 999, resultset-id: 0 Any help is appreciated. Thanks. -- Branislav Cavlin Canada
