Camunda Platform is not compatible with MySQL (internal error after idle period)

Environment (Required on creation):

Docker on Ubuntu

Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):

Camunda lost connection to MySQL after idle for 8 hours (default wait_timeout of MySQL). Once the connection is closed by MySQL, Camunda cannot restore it and eventually run into dead.

Steps to reproduce (Required on creation):

  1. docker-compose up -d;
  2. login to http://192.168.17.140:50500/camunda/app/cockpit/default/#/processes
  3. enter “Invoice Receipt”
  4. enter arbitrary ID
  5. you will find that everything is smooth (successful scenario)
  6. close current tab and wait for 6 minutes
  7. repeat from step 2 again and you will find error message prompted (An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.)
  8. go to home and you will find 500 error

Observed Behavior (Required on creation):

Camunda prompt out error message on page and then throw 500 error on main page.

Expected behavior (Required on creation):

Camunda can still run without error.

Root Cause (Required on prioritization): MySQL closes the connection after idle for 8 hours and Camunda does not have mechanism to handle it.

Solution Ideas (Optional):

Hints (optional):

In order to make developer to test it more efficient, the --wait_timeout=300 command of MySQL service inside attached docker-compose.yml is set to 300 (5 minutes) (default is 28800 (8 hours)) so that you may let it idle for 5 minutes after startup and replay the error.

As discussed on the Jira ticket:

See: https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html

AutoReconnect can be enabled via the jdbc url, e.g.
mysql://db_user:db_user@localhost/mydb?autoReconnect=true