CODEWITHSUNDEEP
javasql-serverdatabasequartz-schedulerdropwizard
Aman
Aman

Reputation: 124

Quartz scheduler not executing the SQL Query in a dropwizard application

I've an application created using dropwizard framework where I've registered a quartz-scheduler job scheduled to run after every specified duration. This job fires a SQL query to SQL Server DB and iterates the ResultSet and sets the data to a POJO class which is later pushed to a queue.

The SQL query has UNION joining multiple tables which fetches the data for the records modified in a delta time using the last_modified_time column of the related table in where clause. DB jar included in pom.xml is sqljdbc-4.4.0 and quartz version is 2.2.1

The query looks like this:

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 called by simple connection statement and resultset 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 a simple execution, it prints the logs "Starting execution" and then processes the records and prints "completed execution". But sometimes during the execution, it's printing the logs "Starting execution" and "completed execution" but this query is not actually fired to the SQL DB.

As I didn't get the records which I modified in that delta time, I put the profiler to check if the query is actually fired and didn't found this query firing to the DB. Also, I've tried adding log4jdbc library http://code.google.com/p/log4jdbc/wiki/FAQ to print the query to the logs but no logs were printed for this query.

Upvotes: 5

Views: 1262

Answers (2)

Aman
Aman

Reputation: 124

I turned the SQL Profiler on this query and filtered it for my server to check if the query was actually hitting the DB from my application and found that the profiler could only find it rarely hitting the DB. So, I thought there might be some caching happening at mybatis level. Then I added more logs and performed debug analysis on mybatis by enabling all kind of logging and to check if there are any caching at local level or second level cache mybatis configuration but that wasn't the cause. Then I used spy jdbc driver log4jdbc driver to log all the queries, parameters and all db information requests at db level.

My organization is using Spunk application to display logs from all the applications deployed to the different host servers. While checking the logs in splunk, I observed the same query was printed twice in the logs but when I noticed deeply it was printing one from my instance and another execution from the different instance deployed on some other server. I logged in to that server and found the same application deployed over there not updated since months. It was found to be multiple application instances running in the same environment but on two different servers and there is no way I could figure out that an application was deployed on multiple hosts.

Thank you @halfer for all the help and bounty.

Upvotes: 0

Rick James
Rick James

Reputation: 142518

with (NOLOCK) is not MySQL syntax. Look at the settings in the wizard and see if you have specified the correct RDBMS engine. In particular, it sounds like SQL Server syntax.

The equivalent may involve setting the TRANSACTION ISOLATION LEVEL to something like READ UNCOMMITTED.

Upvotes: 2

Related Questions