SELECT 1 from DUAL: MySQL

Question

In looking over my Query log, I see an odd pattern that I don't have an explanation for.

After practically every query, I have "select 1 from DUAL".

I have no idea where this is coming from, and I'm certainly not making the query explicitly.

The log basically looks like this:

    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    ...etc...

Has anybody encountered this problem before?

MySQL Version: 5.0.51

Driver: Java 6 app using JDBC. mysql-connector-java-5.1.6-bin.jar

Connection Pool: commons-dbcp 1.2.2

The validationQuery was set to "select 1 from DUAL" (obviously) and apparently the connection pool defaults testOnBorrow and testOnReturn to true when a validation query is non-null.

One further question that this brings up for me is whether or not I actually need to have a validation query, or if I can maybe get a performance boost by disabling it or at least reducing the frequency with which it is used. Unfortunately, the developer who wrote our "database manager" is no longer with us, so I can't ask him to justify it for me. Any input would be appreciated. I'm gonna dig through the API and google for a while and report back if I find anything worthwhile.

EDIT: added some more info

EDIT2: Added info that was asked for in the correct answer for anybody who finds this later

Answer 1

It could be coming from the connection pool your application is using. We use a simple query to test the connection.

Just had a quick look in the source to mysql-connector-j and it isn't coming from in there.

The most likely cause is the connection pool.

Common connection pools:

commons-dbcp has a configuration property validationQuery, this combined with testOnBorrow and testOnReturn could cause the statements you see.

c3p0 has preferredTestQuery, testConnectionOnCheckin, testConnectionOnCheckout and idleConnectionTestPeriod

For what's it's worth I tend to configure connection testing and checkout/borrow even if it means a little extra network chatter.