Quartz scheduler does not execute SQL query in dropwizard application

Question

I have an application created using the dropwizard environment where I registered a quartz scheduler job that should run after each specified duration. This job runs an SQL query into the SQL Server database and iterates through the ResultSet and sets the data to the POJO class, which is then redirected to the queue.

The SQL query has a UNION that combines several tables that retrieve data for records modified in delta time using the last_modified_time column of the linked table in where where. The DB jar included in pom.xml is sqljdbc-4.4.0, and the quartz version is 2.2.1

The request is as follows:

SELECT u.last_modified_date, u.account_id, u.user_id, ud.is_active FROM user u WITH (NOLOCK) JOIN user_details ud with (NOLOCK) ON u.account_id = ud.account_id AND u.user_id = ud.user_id WHERE u.last_modifed_date > ? AND ud.last_modifed_date <= ? UNION SELECT u.last_modified_date, u.account_id, u.user_id, ud.is_active FROM user u WITH (NOLOCK) JOIN user_details ud with (NOLOCK) ON u.account_id = ud.account_id AND u.user_id = ud.user_id JOIN user_registration_details urd WITH (NOLOCK) ON urd.account_id = u.account_id AND urd.user_id = u.user_id AND urd.reg_id = ud.reg_id WHERE urd.last_modifed_date > ? AND urd.last_modifed_date <= ? 

This query is invoked by a simple join statement and a result set like this

 final ManagedDataSource datasource configuration.getDatabase().build(environment.metrics(), "sql"); // configuration is the configuration class in a drop wizard application and configuration.getDatabase() returns // the DataSourceFactory with all credentials like user, password and url set into it try (Connection conn = dataSource.getConnection()) { int resultSetType = SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY; int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY; LOGGER.info("Starting execution: "); try (PreparedStatement pstmt = conn.prepareStatement(getQuery(), resultSetType,resultSetConcurrency)) { setQueryParameters(pstmt); try (ResultSet rs = pstmt.executeQuery();) { //process results } } } catch (SQLException | IOException ex) { LOGGER.error("Error occurred " + ex); } LOGGER.info("Completed execution: "); 

In simple execution, it prints Run Run logs, and then processes the entries and prints "completed run". But sometimes at runtime, it prints the logs “Run execution” and “completed execution”, but this query does not actually run in SQL DB.

Since I did not receive the records that I modified at this delta time, I placed the profiler to check whether the query was really running and did not find this query in the database. In addition, I tried to add the log4jdbc http://code.google.com/p/log4jdbc/wiki/FAQ library to print the request in the logs, but no logs were printed for this request.

Answer 1

with (NOLOCK) not MySQL syntax. Look at the settings in the wizard and see if you specified the correct DBMS mechanism. In particular, this is similar to SQL Server syntax.

Equivalent may include setting TRANSACTION ISOLATION LEVEL to something like READ UNCOMMITTED .

Answer 2

I flipped SQL Profiler for this query and filtered it for my server to check if the query really hit the database from my application and found that the profiler can only find a rare hit in the database. So, I thought some caching at the mybatis level might happen. Then I added some logs and did a debug analysis on mybatis, including all kinds of logging and checking if there was a cache at the local level or a second level cache mybatis configuration but that was not the reason. Then I used the spy jdbc log4jdbc driver driver to register all requests, parameters and all requests for db information at the db level.

My organization uses the Spunk application to display logs from all applications deployed to different host servers. Checking the logs in splunk, I noticed that the same request was printed twice in the logs, but when I noticed that it was printing one from my instance and another execution from another instance deployed to some other server. I logged on to this server and found that the same application was deployed there, which has not been updated since. It was discovered that several instances of applications work in the same environment, but on two different servers, and I can’t understand at all that the application was deployed on several hosts.

Thanks @halfer for all the help and generosity.