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.

Answer 2

The "dual" table/object name is an Oracle construct, which MySQL supports for compatibility - or to provide a target for queries that dont have a target but people want one to feel all warm and fuzzy. E.g.

select curdate()

can be

select curdate() from dual

Someone could be sniffing you to see if you're running Oracle.

Answer 3

I have performed 100 inserts/deltes and tested on both DBCP and C3PO.

DBCP :: testOnBorrow=true impacts the response time by more than 4 folds.

C3P0 :: testConnectionOnCheckout=true impacts the response time by more than 3 folds.

Here are the results : DBCP – BasicDataSource

Average time for 100 transactions ( insert operation ) testOnBorrow=false :: 219.01 ms testOnBorrow=true :: 1071.56 ms

Average time for 100 transactions ( delete opration ) testOnBorrow=false :: 223.4 ms testOnBorrow=true :: 1067.51 ms

C3PO – ComboPooledDataSource Average time for 100 transactions ( insert operation ) testConnectionOnCheckout=false :: 220.08 ms testConnectionOnCheckout=true :: 661.44 ms

Average time for 100 transactions ( delete opration ) testConnectionOnCheckout=false :: 216.52 ms testConnectionOnCheckout=true :: 648.29 ms

Conculsion : Setting testOnBorrow=true in DBCP or testConnectionOnCheckout=true in C3PO impacts the performance by 3-4 folds. Is there any other setting that will enhance the performance.

-Durga Prasad