Incorrect syntax near ‘0′ – Don’t Shoot the Messenger!
ColdFusion 8.0 added the ability to specify a “validation query” that will be run every time a connection is reused in a connection pool. This is useful in cases where database servers or firewalls sometimes drop connections without warning, as it causes a new connection to be made to the database server for the page, so that no user sees an error caused by a stale database connection. A good validation query would be something like “SELECT 1″ for SQL Server or Sybase, or “SELECT 1 FROM DUAL” for Oracle.
We’ve seen a few customers lately that have had the value “0″ sneak in as their Validation Query for datasources in the ColdFusion 8.0 Administrator. I’ve tried and failed to reproduce this, but zeros don’t magically appear in that field for me; suffice to say that unrelated people were finding “0″ as their validation query, and seeing SeeFusion report errors such as these in their coldfusion-out.log files:
2008-01-25 08:45:02 SeeFusion: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Syntax error near '0' on line 1
at ianywhere.ml.jdbcodbc.IIStatement.execute(Native Method)
at ianywhere.ml.jdbcodbc.IStatement.execute(IStatement.java:188)
at com.seefusion.zd.execute(zd.java:308)
at coldfusion.server.j2ee.sql.pool.JDBCPool.validate(JDBCPool.java:701)
at coldfusion.server.j2ee.sql.pool.JDBCPool._checkOut(JDBCPool.java:411)
at coldfusion.server.j2ee.sql.pool.JDBCPool.checkOut(JDBCPool.java:353)
at coldfusion.server.j2ee.sql.pool.JDBCPool.requestConnection(JDBCPool.java:790)
at coldfusion.server.j2ee.sql.pool.JDBCManager.requestConnection(JDBCManager.java:125)
at coldfusion.server.j2ee.sql.JRunDataSource.getConnection(JRunDataSource.java:138)
at coldfusion.sql.CFDataSource.getConnection(CFDataSource.java:54)
at coldfusion.sql.DataSrcImpl.getCachedConnection(DataSrcImpl.java:158)
at coldfusion.sql.DataSrcImpl.getConnection(DataSrcImpl.java:108)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:297)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
…or…
2008-01-08 12:39:44 SeeFusion: java.sql.SQLException:
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '0'.
at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at macromedia.jdbc.base.BaseStatement.execute(Unknown Source)
at com.seefusion.zd.execute(zd.java:308)
at coldfusion.server.j2ee.sql.pool.JDBCPool.validate(JDBCPool.java:701)
at coldfusion.server.j2ee.sql.pool.JDBCPool._checkOut(JDBCPool.java:411)
at coldfusion.server.j2ee.sql.pool.JDBCPool.checkOut(JDBCPool.java:353)
at coldfusion.server.j2ee.sql.pool.JDBCPool.requestConnection(JDBCPool.java:790)
at coldfusion.server.j2ee.sql.pool.JDBCManager.requestConnection(JDBCManager.java:125)
at coldfusion.server.j2ee.sql.JRunDataSource.getConnection(JRunDataSource.java:138)
at coldfusion.sql.CFDataSource.getConnection(CFDataSource.java:41)
at coldfusion.sql.DataSrcImpl.getCachedConnection(DataSrcImpl.java:154)
at coldfusion.sql.DataSrcImpl.getConnection(DataSrcImpl.java:108)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:297)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
…or…
2008-01-25 15:33:11 SeeFusion: java.sql.SQLException: Line 1: Incorrect syntax near '0'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
at com.seefusion.ae.execute(ae.java:308)
at coldfusion.server.j2ee.sql.pool.JDBCPool.validate(JDBCPool.java:701)
at coldfusion.server.j2ee.sql.pool.JDBCPool._checkOut(JDBCPool.java:411)
at coldfusion.server.j2ee.sql.pool.JDBCPool.checkOut(JDBCPool.java:353)
at coldfusion.server.j2ee.sql.pool.JDBCPool.requestConnection(JDBCPool.java:790)
at coldfusion.server.j2ee.sql.pool.JDBCManager.requestConnection(JDBCManager.java:125)
at coldfusion.server.j2ee.sql.JRunDataSource.getConnection(JRunDataSource.java:138)
at coldfusion.sql.CFDataSource.getConnection(CFDataSource.java:41)
at coldfusion.sql.DataSrcImpl.getCachedConnection(DataSrcImpl.java:154)
at coldfusion.sql.DataSrcImpl.getConnection(DataSrcImpl.java:108)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:297)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
Note that while SeeFusion is reporting the error, it’s not causing the error.
n.b. If you read the stack from the bottom up, it’s telling its story: QueryTag.executeQuery() calls DataSrcImpl.getConnection(), which tries to getCachedConnection(), which hits a JDBCPool object (connection pool) that does a .checkout(), followed by a .validate(). The validate() method calls (after passing through SeeFusion’s driver wrapper) the db driver’s Statement.execute() method, and that database driver throws the exception.
The deeper, more pernicious problem, is that there is no way to test validation queries in the ColdFusion administrator. And when an invalid validation query is used, connection pooling is basically disabled, forcing new connections to be made to the database for every page view.
Interestingly, when ColdFusion validates the datasource, it runs the validation query, but silently ignores the exception thrown. (We used SeeFusion to see which JDBC calls the ColdFusion Administrator is making.)
Feature request for the next ColdFusion 8 service pack: add a warning message when you save a datasource and the validation query fails!
-=-=-=-=-=-=-=-=-
Update
-=-=-=-=-=-=-=-=-
As it turns out, there’s a bug in the ColdFusion 8.0 Admin API for datasources. Any datasource you create with the Admin API will have the validationQuery set to “0″, no matter what value is actually set (and even if that value is intentionally set blank).
This problem with the validation query for when datasources are created via the Admin API has been resolved in ColdFusion 8.01, according to Part I of the release notes as issue 71199 under the fixes section.
@Daryl:
What should the validation query be set to if you don’t want to use the feature? I just checked and it looks like all my queries are set to 0. Is there a way to completely disable this feature? I would assume leaving it blank would prevent it from running, but might this just cause errors?
Comment by Dan G. Switzer, II — January 29, 2008 @ 12:00 am
I just went through all of my datasources. All of the datasources that were either migrated or added during the CF8 installation had the "validation query" set to "0".
Any of the datasources I created after the installation had blank "validation query" settings.
Comment by Dan G. Switzer, II — January 29, 2008 @ 12:00 am
@Daryl:
Thanks for the good information. I’ve confirmed that setting the validation query on my datasource has resolved the "Incorrect syntax near ‘0′" messages I was getting.
I blogged about this and the "Auto-generated keys were not requested" issues. I think these are both important issues for CF8 developers to be aware of:
http://blog.pengoworks.com/index.cfm/2008/1/30/Two-important-CF8-bugsissues-detailed-yesterday
Comment by Dan G. Switzer, II — January 30, 2008 @ 12:00 am
Thanks, Dan. And yeah, for most users, "Validation Query" should be blank. (If you don’t have problems with orphaned connections, there’s no reason to run an extra query for every page, even if it is trivial.)
Comment by Daryl Banttari — January 30, 2008 @ 12:00 am
I entered this as an issue. 71202.
Comment by Mike Collins — January 31, 2008 @ 12:00 am
Thanks, Mike! Hope Adobe is treating you well.
Comment by Daryl Banttari — February 1, 2008 @ 12:00 am
Mike, just curious to know the test case you have used to surface this defect that was not always reproducible. Specially when Daryl, also could not figure it out.
Abhijeet
http://www.lifeiscolourful.com
Comment by Abhijeet — July 17, 2008 @ 12:00 am
Some CF8 update silently put “0″ into the validation query field for our datasources, so we were pounding Oracle with new connections (plus the bad validation queries). I couldn’t find anything when I searched on it. Here is the error text (hoping google will pick up this text for the next person who has the problem):
Error Executing Database Query. Io exception: The Network Adapter could not establish the connection
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333) at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:404) at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314) at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(JDBCPool.java:589) at coldfusion.server.j2ee.sql.pool.ConnectionRunner$RunnableConnection.run(ConnectionRunner.java:67) at java.lang.Thread.run(Thread.java:619)
Thanks.
Comment by fgwenger — February 12, 2010 @ 5:35 pm