There were two important blog posts I came across yesterday that detail similarly related problems in ColdFusion 8. Both issues revolve around changes to the JDBC drivers in ColdFusion 8 and they're both essentially transparent issues (unless your running SeeFusion.)
The first issue relates to a new feature in the datasource configuration called the "Validation Query." ColdFusion 8 introduced the ability to define a query that run each time a connection to a DSN is reused after a period of inactivity. This is useful in cases where the CF server might lose connection to the database server due to network issues.
As Daryl Banttari of Webapper reported yesterday, the problem is there's a bug in the Admin API that causes the "Validation Query" for each DSN it touches to be set to the value of "0". This causes ColdFusion to silently through a java.sql.SQLException each time the validation query is executed. Since this is a silent exception, you have no idea it's happening.
However, if you were using the SeeFusion JDBC wrapper you log files would start filling up with errors that looked like:
java.sql.SQLException:
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '0'-- or --
java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Syntax error near '0' on line 1
I've been able to confirm that all datasources that were migrated or created during a ColdFusion 8 installation have a validation query of "0"—which is causing silent exceptions to be thrown. In order to prevent these silent exceptions from being thrown, you need to remove the "Validation Query" from each of your datasources by setting the value to an empty string.
To remove this setting:
- Go to the ColdFusion Administrator
- Go to Data & Services > Data Sources
- Click on a datasource name
- Click on the Show Advanced Settings button
- Set the Validation Query to an empty string
You'll need to repeat these steps for each datasource configured to ensure you are not throwing an silent exceptions.
The second issue was posted by Nathan Mische and it relates to CF8's new ability to return the newly created primary key when doing an INSERT statement. Nathan's post has lots of really good information on this problem, so I recommend reading it.
Much like Nathan my cfusion-out.log file was being filled up with errors that looked like:
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Auto-generated keys were not requested, or the SQL was not a simple INSERT statement.
The problem is essentially the same as the previous issue in that ColdFusion starts silently throwing exceptions there's no generated key to return. This might happen if you're doing bulk INSERT statements or if you're doing an INSERT that has no primary key field.
When I first ran into this problem I spent a day messing around trying to debug the problem, but finally just decided it must have been a problem with the SeeFusion JDBC driver and stopped using the wrapper because they were causing me JVM Heap problems. However as I know now, SeeFusion was simply reporting these silent errors that ColdFusion was generating.
Unfortunately for this Auto-generated keys were not requested issue there does not be appear to be a way to stop ColdFusion from throwing this error. Fortunately the guys at Webapper have released SeeFusion v4.0.7 which ignore these exceptions that are thrown by CF8 so they're not logged to the coldfusion-out.log file.
I think these are two important issues to keep in mind, especially if you're experiencing weird JVM or Database performance problems.
2 Comments
Comments for this entry have been disabled.