1

I am trying to add timeout to jdbc/query and jdbc/execute!. Somewhere in the web I found that both functions take :timeout as an option. Documention also says the options are passed to prepare-statment which takes in :timeout as an option.

My function calls look like,

(jdbc/query db-read-spec query {:timeout 2})

(jdbc/execute! db-write-spec query {:timeout 2})

Is this how it is done? If yes, How do I test this?

If there is different way of doing this which is testable, that works too.

Sarath VS
  • 71
  • 9
  • Are you asking, because it does not work? A naive test would be use an unreasonable small timeout and assume it would time out. – cfrick Mar 26 '20 at 08:46

3 Answers3

2

The :timeout option causes .setQueryTimeout to be called on the PreparedStatement used under the hood of clojure.java.jdbc. It is in seconds, not milliseconds, so your query would have to be extremely slow for a timeout of 2,000 seconds (just over half an hour) to take effect.

JDBC supports several different timeouts across several of its classes. For example, javax.sql.DataSource supports .setLoginTimeout (also in seconds), as does java.sql.DriverManager.

There are also database-specific options you can add to the connection string (which you can add as additional key/value pairs in your "db-spec") to control lower-level timeouts. For example, MySQL supports connectionTimeout and socketTimeout in the connection string -- and both of those are in milliseconds. clojure.java.jdbc allows for those to be provided in your "db-spec" hash map as :connectTimeout and :socketTimeout keys respectively.

Note that clojure.java.jdbc is considered "Stable" at this point and all current and future development effort is focused on next.jdbc at this point. next.jdbc makes it easier to use the loginTimeout since it operates on JDBC objects directly, so the whole (Java) API is available as well. It also has built-in support for connection pooling and is, overall, simpler and faster than clojure.java.jdbc.

Sean Corfield
  • 6,297
  • 22
  • 31
  • Found out the :timeout takes in seconds little later. @Sean Corfield Thanks for the detailed information. – Sarath VS Mar 29 '20 at 14:48
0

You can leverage query-hint on mysql-select-queries (time in ms)

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE....

then you can just wrap your queries:

(defn timed-query [db query t]
  (j/query db [(str (subs query 0 6)
                    (format " /*+ MAX_EXECUTION_TIME(%s) */ " t)
                    (subs query 7))]))

and test:

(deftest test-query-timeout
  (is (thrown? Exception (timed-query db "select * from Employees where id>5" 1))))

you should use much-complex queries for this to work with 1ms;

a.k
  • 1,035
  • 10
  • 27
0

I figure out a work around to test this out. Since I use postgres I could leverage select pg_sleep(time-in-seconds)

And my test looks like 

(is (thrown-with-msg? PSQLException #"ERROR: canceling statement due to user request"
                          (fetch-or-save "select pg_sleep(3)")))
Sarath VS
  • 71
  • 9