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.
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)