In a batch processing framework, job repository fulfills the need for storing batch job processing data, such as job instance, job execution, step execution, step partition execution, and various metrics.  JBeret implements in-memory, JDBC, MongoDB and Infinispan job repository types to meet different application requirements.  From the user feedback from last couple of years, JDBC stands out as the most commonly used job repository type.  In a previous blog post, I wrote about using PostgreSQL JDBC job repository in WildFly.  In this post, I will expand this topic to walk through how to use JDBC job repository backed by another popular open-source database product: MySQL.  MySQL 8 was released in April 2018, 2.5 years after the previous major release 5.7.  So it's a good time to try out MySQL 8 with JBeret.

 

Basic Operations with MySQL

 

First of all, let's start with some basic operations with MySQL, such as starting and stopping the database server, running the CLI mysql client program and the GUI client tool MySQL Workbench.  I'm using MySQL version 8.0.15, the latest stable release at the time of writing.  I will start it in my local machine and also connect it locally as MySQL user root without password.

 

To start MySQL database server:

$ mysql.server start
Starting MySQL
. SUCCESS!

 

To stop MySQL database server:

$ mysql.server stop
Shutting down MySQL
.. SUCCESS!

 

To perform simple client operations from CLI tool, mysql:

 

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

 

In the above mysql session, I just created a database named test, which will be used as the target database of jberet job repository.  To exit from mysql client session, type command "quite;", or simply press Ctrl-D.

 

If you prefer GUI to CLI, MySQLWorkbench offers a powerful yet easy-to-use client tool.

MySQLWorkbench

 

Configure MySQL JDBC Job Repository in JBeret Standalone in Java SE environment

 

JBeret standalone by default uses H2 database.  To change to MySQL, simply follow the following steps:

 

1, edit jberet.properties file, which is the configuration file for JBeret standalone in Java SE environment, and should reside in the class path of your batch application.  In JBeret zip distribution, it is at JBERET_INSTALL_DIR/bin/jberet.properties.

 

db-url = jdbc:mysql://localhost:3306/test
db-user = root
db-password =
db-properties = sslMode=DISABLED

 

2, include MySQL JDBC driver jar in the runtime classpath of your batch application.  For maven-based project, simply configure the following dependency on MySQL Java connector:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.15</version>
</dependency>

 

Troubleshooting

 

Some common errors you may run into while connecting to MySQL database server from your batch application:

 

1, JDBC jar not found

 

Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
        at org.jberet.repository.JdbcRepository.getConnection(JdbcRepository.java:1021)

 

The above error means MySQL Java Connector jar is not in runtime classpath, and you will need to double check the jar path and file names.

 

2, Unable to load authentication plugin

 

Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:880)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:876)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1690)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2249)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2280)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
        at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:794)
        at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)

 

The above error is usually caused by older version of MySQL Java Connector trying to talk to newer version of MySQL database server.  Upgrading your client-side MySQL Java Connector to match the server side will resolve this problem.  For more technical details, see MySQL docs.

 

3, SSLException when running with Java 11

 

javax.net.ssl.SSLException
MESSAGE: closing inbound before receiving peer's close_notify
   
STACKTRACE:
javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify
        at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:129)
        at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:308)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:264)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:255)
        at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:645)
        at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:624)
        at com.mysql.cj.protocol.a.NativeProtocol.quit(NativeProtocol.java:1319)
        at com.mysql.cj.NativeSession.quit(NativeSession.java:182)
        at com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1750)
        at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)
        at org.jberet.repository.JdbcRepository.close(JdbcRepository.java:1055)
        at org.jberet.repository.JdbcRepository.updateJobExecution(JdbcRepository.java:497)

 

The above SSLException is logged when running with Java 11 and connecting to MySQL server without disabling ssl.  It does not affect batch job execution, though.  It does not occur with Java 10.  To avoid it in Java 11, make sure the db-properties is set to "sslMode=DISABLED" in jberet.properties.

 

Query Batch Job Data with MySQL Client Tool

 

Batch application can query job execution data and perform job operations through the standard JobOperator API, which should be the perferred way of interacting with the underlying job repository.  However, directly querying job repository database can be a valuable complement to the standard JobOperator API.

 

To view available tables in postgres database:

 

mysql> show tables;
+---------------------+
| Tables_in_test      |
+---------------------+
| JOB_EXECUTION       |
| JOB_INSTANCE        |
| PARTITION_EXECUTION |
| STEP_EXECUTION      |
+---------------------+
4 rows in set (0.00 sec)

 

To display the structure of a particular table:

 

mysql> describe JOB_INSTANCE;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| JOBINSTANCEID   | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| VERSION         | int(11)      | YES  |     | NULL    |                |
| JOBNAME         | varchar(512) | YES  |     | NULL    |                |
| APPLICATIONNAME | varchar(512) | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

To query job execution data in chronological order:

 

mysql> select jobexecutionid, endtime, batchstatus from job_execution order by jobexecutionid desc limit 10;
+----------------+---------------------+-------------+
| jobexecutionid | endtime             | batchstatus |
+----------------+---------------------+-------------+
|            937 | 2019-02-24 18:17:48 | COMPLETED   |
|            936 | 2019-02-24 18:17:48 | STOPPED     |
|            935 | 2019-02-24 18:17:46 | COMPLETED   |
|            934 | 2019-02-24 18:17:46 | FAILED      |
|            933 | 2019-02-24 18:17:46 | COMPLETED   |
|            932 | 2019-02-24 18:17:46 | COMPLETED   |
|            931 | 2019-02-24 18:17:46 | COMPLETED   |
|            930 | 2019-02-24 18:17:46 | STOPPED     |
|            929 | 2019-02-24 18:17:46 | COMPLETED   |
|            928 | 2019-02-24 18:17:46 | COMPLETED   |
+----------------+---------------------+-------------+
10 rows in set (0.00 sec)

 

To view job instance data:

 

mysql> select * from job_instance order by jobinstanceid desc limit 5;
+---------------+---------+--------------------------+-----------------+
| JOBINSTANCEID | VERSION | JOBNAME                  | APPLICATIONNAME |
+---------------+---------+--------------------------+-----------------+
|           732 |    NULL | job_batchlet_longrunning | NULL            |
|           731 |    NULL | job_batchlet_longrunning | NULL            |
|           730 |    NULL | job1                     | NULL            |
|           729 |    NULL | job1                     | NULL            |
|           728 |    NULL | job1                     | NULL            |
+---------------+---------+--------------------------+-----------------+
5 rows in set (0.00 sec)

 

To view step execution data:

 

mysql> select stepexecutionid, stepname, starttime, batchstatus, commitcount from step_execution order by stepexecutionid desc limit 10;
+-----------------+----------+---------------------+-------------+-------------+
| stepexecutionid | stepname | starttime           | batchstatus | commitcount |
+-----------------+----------+---------------------+-------------+-------------+
|            1440 | step2    | 2019-02-24 18:17:48 | COMPLETED   |           0 |
|            1439 | step1    | 2019-02-24 18:17:48 | COMPLETED   |           0 |
|            1438 | step1    | 2019-02-24 18:17:46 | STOPPED     |           0 |
|            1437 | step2    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
|            1436 | step1    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
|            1435 | step1    | 2019-02-24 18:17:46 | FAILED      |           0 |
|            1434 | step1    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
|            1433 | step1    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
|            1432 | step1    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
|            1431 | step1    | 2019-02-24 18:17:46 | COMPLETED   |           0 |
+-----------------+----------+---------------------+-------------+-------------+
10 rows in set (0.00 sec)