JdbcTaskRepository.reschedule fails on MySQL

[JanGurda]

I get following exception:
ERROR [2016-08-18 15:34:33,486] [] com.github.kagkarlsson.scheduler.Scheduler: Failed while completing execution Execution: task=sync-unknonwn-and-pending-orders, id=recurring, executionTime=2016-08-18T15:34:33.397Z, picked=true, pickedBy=jangurdaspid, lastHeartbeat=2016-08-18T15:34:33.461Z, version=2. Execution will likely remain scheduled and locked/picked. The execution should be detected as dead in PT20M, and handled according to the tasks DeadExecutionHandler. ! com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'task_name = 'XXXXX' and task_instance = 'recurring' a' at line 1 ...... ! ... 14 common frames omitted ! Causing: com.github.kagkarlsson.jdbc.SQLRuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'task_name = 'sync-unknonwn-and-pending-orders' and task_instance = 'recurring' a' at line 1 ! at com.github.kagkarlsson.jdbc.JdbcRunner.translateException(JdbcRunner.java:83) ~[micro-jdbc-0.1.jar:na] ! at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$3(JdbcRunner.java:70) ~[micro-jdbc-0.1.jar:na] ! at com.github.kagkarlsson.jdbc.JdbcRunner$$Lambda$13/1345063038.apply(Unknown Source) ~[na:na] ! at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:97) ~[micro-jdbc-0.1.jar:na] ! at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:48) ~[micro-jdbc-0.1.jar:na] ! at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:36) ~[micro-jdbc-0.1.jar:na] ! at com.github.kagkarlsson.scheduler.JdbcTaskRepository.reschedule(JdbcTaskRepository.java:117) ~[db-scheduler-1.8.jar:na]

Exception is most likely caused by query (missing space in "version = version + 1" + line). After string concatenation query looks as follows: update scheduled_tasks set picked = ?, picked_by = ?, last_heartbeat = ?, last_success = ?, last_failure = ?, execution_time = ?, version = version + 1where task_name = ? and task_instance = ? and version = ?. There is no space between "1" and "where".

[kagkarlsson]

Yes, it is indeed malformed sql causing problems for mysql. Strange that this is not also problematic for postgresql/hsql/oracle. I have fixed the issue and tested for compatibility with mysql. Will release a new version as soon as possible. For now, the fix is in master.

Thank you for reporting the issue!

[kagkarlsson]

Released version 1.9 with this fix. Please try it out and let me know if you still have issues.

[JanGurda]

Thank you very much. Works perfectly. Excellent piece of software. What's important fault tolerant. If you would like to add sample MySQL script, here is what I use:

CREATE TABLE scheduled_tasks (
  task_name varchar(512) NOT NULL,
  task_instance varchar(512) NOT NULL,
  execution_time datetime(3) NOT NULL,
  picked bit(1) NOT NULL,
  picked_by varchar(512) DEFAULT NULL,
  last_success datetime(3) DEFAULT NULL,
  last_failure datetime(3) DEFAULT NULL,
  last_heartbeat datetime(3) DEFAULT NULL,
  version bigint(20) NOT NULL,
  PRIMARY KEY (task_name,task_instance)
)

[kagkarlsson]

Good to hear :). Regarding fault tolerance, the scheduler regularly checks for "dead executions", i.e. an execution that has been picked by a scheduler, but where updates to "last_heartbeat" has stopped. After a certain time a handler will fire for the "dead execution" and typically reschedule it for execution "now".

[kagkarlsson]

Thank you for the mysql example. I will add an example to the documentation, but to be consistent with the postgresql example I think I will use TIMESTAMP instead of DATETIME. The important difference as I see it is that TIMESTAMP is a point in time (directly mappable to java.time.Instant) while DATETIME is a sort of LocalDateTime. (DATETIME loses timezone offset)

Example:

create table scheduled_tasks (
  task_name varchar(40) not null,
  task_instance varchar(40) not null,
  execution_time timestamp(6) not null,
  picked BOOLEAN not null,
  picked_by varchar(40),
  last_success timestamp(6) null,
  last_failure timestamp(6) null,
  last_heartbeat timestamp(6) null,
  version BIGINT not null,
  PRIMARY KEY (task_name, task_instance)
)

Thoughts?

[JanGurda]

Looks good. Thank you.

On Mon, Aug 22, 2016 at 7:29 AM, Gustav Karlsson notifications@github.com
wrote:

Thank you for the mysql example. I will add an example to the
documentation, but to be consistent with the postgresql example I think I
will use TIMESTAMP instead of DATETIME. The important difference as I see
it is that TIMESTAMP is a point in time (directly mappable to
java.time.Instant) while DATETIME is a sort of LocalDateTime. (DATETIME
loses timezone offset)

Example:

create table scheduled_tasks (
task_name varchar(40) not null,
task_instance varchar(40) not null,
execution_time timestamp(6) not null,
picked BOOLEAN not null,
picked_by varchar(40),
last_success timestamp(6) null,
last_failure timestamp(6) null,
last_heartbeat timestamp(6) null,
version BIGINT not null,
PRIMARY KEY (task_name, task_instance)
)

Thoughts?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
#2 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AIOpt6-89Xs04wvLHtbiKi5JYGmDbnB9ks5qiTOogaJpZM4Jnn15
.

Jan Gurda
Software Engineer
jan.gurda@schibsted.pl
www.schibsted.pl