The following is a reply I made to the CFGURU mailing list describing my experience with the "No more data available to read" error in CFMX server.




At least as of Macromedia drivers bundle version 3.3, the "No more data available to read" error is generated in SQL Server datasources when an existing connection in the connection pool isn't valid anymore. A request checks out an existing connection and attempts to use it, but if the connection isn't good anymore then this error is thrown and the connection is removed from the connection pool.

The ColdFusion server may continue to generate the error until all datasource connections are removed from the pool. Then assuming the pool has 0 connections, when a request attempts to check out a connection from the pool a new connection will be attempted. At that point a new error might be thrown that is more useful, such as "Error establishing socket. No route to host: connect"

I ran a test where I made a single request to one page that ran a simple query to a SQL Server datasource. At each page end, I refreshed to run it again just confirm that the connection was established and that the pool should only have 1 connection in it. Then I pulled the network cable from the CF machine. The next request errored with "No more data available to read". Refreshing the page then showed a "Error establishing socket. No route to host: connect". Every subsequent request was responded to the same no route to host error. Plugging the cable back in and refreshing showed a successful request.

The second test was very similar to the first except that I used the F5 key for about 20 or 30 seconds to build up the requests while trying to increase the database connection pool size. I let the page catch up, the cpu went back to zero, and cfstat went down to 0 running requests. Then I pulled the cable again and made a single request, "No more data...". One by one I refreshed the page and continued getting the "No more data..." error for at least 5 or 6 times. Then the error changed to "No route to host..." again for every request. Putting the cable back in allowed requests to complete successfully.

Finally, I put the query in a transaction, leaving the isolation to the default. Repeating the first test where I tried to have just 1 connection in the pool and then pulled the plug. In that case the error was "Exception generated during deferred local transaction handling. See next exception via SQLException.getNextException for details.". The very next request returned to the familiar error "No route to host". Putting the cable back in allowed subsequent requests to complete successfully.

The conclusion I drew from this was that when an existing database connection cannot communicate by TCP to the other end of the connection then the "No more data..." error will be returned. I assume that this would be true if there were a firewall or other means of network congestion that made the remote database unreachable. The number of "No more data..." errors received will be equivalent to the number of connections for that datasource pool, perhaps multiplied by the number of datasources pointing to that database. When all existing connections are depleted a more accurate error is reported for subsequent requests. And if a transaction is used then you may not get the "No more data..." error but get an equally obtuse transaction related error followed by a more helpful error.

I have not tested what happens when the database is up and available on the network and is accepting TCP connections but is not responding the JDBC protocol. I suspect that this will simply result in a cfm request that would be observed in the socketRead0() state in a thread dump, e.g. a hanging thread and possibly hanging cfserver.