SELECT 1 of DUAL: MySQL

Question

When viewing the query log, I see an odd pattern for which I have no explanation.

After almost every request, I have "select 1 from DUAL".

I have no idea where this is coming from, and I am clearly not making the request explicitly.

The magazine 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 anyone encountered this problem before?

MySQL Version: 5.0.51

Driver: Java 6 application using JDBC. Mysql-socket-java-5.1.6-bin.jar

Connection Pool: commons-dbcp 1.2.2

ValidationQuery was set to "select 1 from DUAL" (obviously), and apparently the connection pool uses testOnBorrow and testOnReturn for true if the validation request is not zero.

Another question that arises for me is whether I really need have a validation request, or if I can 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 am going to dig up the API and Google for a while and report back if I find anything useful.

EDIT: Added More Information

EDIT2: added information that was requested in the correct answer for those who find this later

Answer 1

This may come from the connection pool used by your application. We use a simple request to verify the connection.

Just looked quickly at the source on mysql-connector-j and it did not appear there.

The most likely cause is the connection pool.

Common connection pools:

commons-dbcp has the validationQuery configuration property, which, in combination with testOnBorrow and testOnReturn can trigger the statements you see.

c3p0 has preferredTestQuery , testConnectionOnCheckin , testConnectionOnCheckout and idleConnectionTestPeriod

Why am I inclined to set up connection testing and verification / borrowing, even if it means a little extra network chatter.

Answer 2

I performed 100 attachments / delta and tested on both DBCP and C3PO.

DBCP :: testOnBorrow = true affects the response time by more than 4 times.

C3P0 :: testConnectionOnCheckout = true affects the response time by more than 3 times.

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 (fingerprint removal) 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 (fingerprint removal) testConnectionOnCheckout = false :: 216.52 ms testConnectionOnCheckout = true :: 648.29 ms

Difficulty: setting testOnBorrow = true in DBCP or testConnectionOnCheckout = true in C3PO affects performance 3-4 times. Are there any other settings that will improve performance.

-Durga Prasad

Answer 3

The “double” table / object name is the Oracle construct that MySQL supports for compatibility, or to provide a target for queries that have no goal, but people want everything to be warm and fuzzy. For example.

 select curdate() 

may be

 select curdate() from dual 

Someone might sniff you if you are using Oracle.