Hi Hareni,

I believe I finally found a solution. In my advanced settings for the datasource I turn "Maintain connections across client requests" off and it seems to work.

My code is very similar to the code posted in this thread and my error is the same as well. If it will help I'm on CF Standard 8,0,0,176276. Changed the JVM to 1.5.0_15 for faster object creation. Using MySQL 5.0.22.

On the server where my code was working without DB errors I'm using CF Developer 8,0,0,176276 , MySQL 5.0.51a-community-nt and the same JVM.

Thanks,

Jimmy

I may be on to something. I extracted the MySQL driver c:\coldfusion8\lib\mysql-connector-java-commercial-5.0.5-bin.jar and opened the file Statement.class using a decompiler. The constructor looks like this:

public Statement(com.mysql.jdbc.Connection c, String catalog) throws SQLException
{
cancelTimeoutMutex = new Object();
wasCancelled = false;
charConverter = null;
charEncoding = null;
connection = null;
connectionId = 0L;
currentCatalog = null;
doEscapeProcessing = true;
eventSink = null;
fetchSize = 0;
isClosed = false;
lastInsertId = -1L;
maxFieldSize = MysqlIO.getMaxBuf();
maxRows = -1;
maxRowsChanged = false;
openResults = new ArrayList();
pedantic = false;
profileSQL = false;
results = null;
resultSetConcurrency = 0;
resultSetType = 0;
timeoutInMillis = 0;
updateCount = -1L;
useUsageAdvisor = false;
warningChain = null;
holdResultsOpenOverClose = false;
batchedGeneratedKeys = null;
retrieveGeneratedKeys = false;
continueBatchOnError = false;
if(c == null || c.isClosed())
throw SQLError.createSQLException(Messages.getString("Statement.0"), "08003");
connection = c;
connectionId = connection.getId();
currentCatalog = catalog;
pedantic = connection.getPedantic();
continueBatchOnError = connection.getContinueBatchOnError();
if(!connection.getDontTrackOpenResources())
connection.registerStatement(this);
if(connection != null)
{
maxFieldSize = connection.getMaxAllowedPacket();
int defaultFetchSize = connection.getDefaultFetchSize();
if(defaultFetchSize != 0)
setFetchSize(defaultFetchSize);
}
if(connection.getUseUnicode())
{
charEncoding = connection.getEncoding();
charConverter = connection.getCharsetConverter(charEncoding);
}
boolean profiling = connection.getProfileSql() || connection.getUseUsageAdvisor();
if(connection.getAutoGenerateTestcaseScript() || profiling)
statementId = statementCounter++;
if(profiling)
{
pointOfOrigin = new Throwable();
profileSQL = connection.getProfileSql();
useUsageAdvisor = connection.getUseUsageAdvisor();
eventSink = ProfileEventSink.getInstance(connection);
}
int maxRowsConn = connection.getMaxRows();
if(maxRowsConn != -1)
setMaxRows(maxRowsConn);
0
}

There appears to be an extraneous 0 at the end.

rich.thibault wrote:
I think that's an artifact from your decompiler

You are right about the decompiler error. It has been confirmed on the same decompiler, but not on two others.(Thanks, Jack).

...MySQL source code doesn't have that 0, and it would prevent compiling anyway:
I was thinking more of a bug in the binary, Statement.class, in the Coldfusion lib package. I expect the constructor to handle the case where the connection is null.

P.S.: I have received four reactions (three in an hour!) repeating your point about the MySQL source code. To clarify, I thought of a bug in Coldfusion's jar because no other MySQL users are reporting the error. It's as simple as that.

Ok, I'm able to reproduce consistently on my devbox. The culprit is the wait timeout. If I set it to 15 seconds I get the error if I wait more then 15 seconds between request. If I set it to something like 21 seconds I get the error precisely after waiting more the 21 seconds between requests.

The default coldfusion timeout is 1200 seconds. So the solution is to set the mysql wait timeout to something >1200.

You can change it via the mysql administrator->startup parameters (select mysqld if asked)->tab advanced networking.

Anyone have any insights into this? I am using dailyrazor as my host and am getting the error, exactly as described. Once in a while..refresh works fine...

java.lang.NullPointerException
at com.mysql.jdbc.Statement.setMaxRows(Statement.java:2178)
at coldfusion.server.j2ee.sql.JRunStatement.setMaxRows(JRunStatement.jav a:214)
at coldfusion.sql.Executive.executeQuery(Executive.java:1276)
at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
at coldfusion.sql.Executive.executeQuery(Executive.java:939)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)

I already had something in the connection string, so I tried to add another, with a semi-colon in between and am getting:

zeroDateTimeBehavior=convertToNull;autoReconnectForPools=true

Warning: gyro4_content: java.sql.SQLException: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'convertToNull;autoReconnectForPools=true' is not in this set.

Is a semi-colon not the value for multiple entries?

If I uncheck this value, it 'solves' the problem, but I was reading about the possible performance issues with it:

"Maintain connections across client requests"

Oh, and ensure "Maintain connections across client requests" is checked.

try zeroDateTimeBehavior=convertToNull&autoReconnectForPools=true ?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

.. if it still complains, then slap it with

autoReconnectForPools=true;zeroDateTimeBehavior=convertToNull
or
autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull

Do you mean that

zeroDateTimeBehavior=convertToNull

and

autoReconnectForPools=true

work, but

autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull

fails?

Quite odd. What if you apply no connection string in the administrator, but do the following instead.

Open the file {CF_installation_dir}/lib/neo-datasource.xml in a text editor. (Make a back-up). Locate the URL(s) which correspond to the datasources to which you want to apply the connection string.

Suppose one such URL is

jdbc:mysql://127.0.0.1:3306/my_orders?

Modify the line to

jdbc:mysql://127.0.0.1:3306/my_orders?autoReconnectForPools=true&zeroD ateTimeBehavior=convertToNull

Restart Coldfusion.

We are experiencing the same issue on Crystaltech shared ColdFusion hosting. From reviews of the Crystaltech forum, this issue seems to have cropped up since the upgrade to CF8.1? This is a serious issue. It is crippling for sites with heavy data input capabilities, because the error forces the user to re-enter all the information from the submitting page. I've created a workaround cftry/cfcatch, but this only works effectively for display pages. Any form submission page which generates the error loses all the form data. Unacceptable.

Adobe? Hello? Help us out here.

I am having this same error
=================================
java.lang.NullPointerException at com.mysql.jdbc.Statement.setMaxRows(Statement.java:2178) at coldfusion.server.j2ee.sql.JRunStatement.setMaxRows(JRunStatement.jav a:214) at... etc... etc..
=================================

Is there any help on this topic? Adobe? Please?

i have the same problem on a daily razor server. every now and then it throws a 500 error for no reason. saying there is a problem with either some system code or programming code.

then when i refresh its fine :S

Same problem for us - detailed description is at (created before I found this thread):

http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1 &catid=7&threadid=1391203&highlight_key=y&keyword1=jdbc%2EStatement%2E setMaxRows

Unchecking "maintain connections" is not a viable solution for us. We have a very heavy database environment.

Does anyone else have a solution?

Has anyone with a support contract reported it to Adobe? What's their answer.

Let's follow another strategy. Run the following query on the MySQL database

show variables;

What value do you get for wait_timeout and for interactive_timeout ? Each has a default value of 28800, that is, 8 hours, converted into seconds.

Is either of the values much lower for your MySQL server? If so, increase it or have it increased. You can do so, for example, by adding the line

wait_timeout=28800

or the line

interactive_timeout=28800

or both to the [mysqld] section of the my.ini configuration file. Restart the MySQL server for the changes to take effect.