Indexing of processes fails in kitodo-production 3.5.0 with mysql 5.7 due to recursive query

[thomaslow]

As reported by @m-kotzyba, the indexing of processes fails with an SqlGrammarException in Kitodo-Production 3.5.0 when using MySQL 5.7.

[ERROR] 2023-02-21 11:39:17.472 [pool-38-thread-1] IndexWorker - org.hibernate.exception.SQLGrammarException: could not extract ResultSet
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.kitodo.data.database.persistence.TaskDAO.countTaskStatusForProcessAndItsAncestors(TaskDAO.java:183) ~[kitodo-data-management-3.5.0.jar:?]
...

The problem is related to pull request #5360 , which added a recursive query to improve the performance when calculating the progress of a process (vertical bar visualizing the relative number of tasks in various states). This recursive sql query is only supported in MySQL 8.

@m-kotzyba I checked MySQL 8.0.32. Indexing works fine.

Since MySQL 5.7 is pretty old (last release in 2015) and not supported by FlyWay any more, see #5330, I would suggest to make MySQL >= 8 a requirement for Kitodo >= 3.5.

@solth Could you add a note to the release notes of Kitodo 3.5?

Since this problem was somewhat expected, the pull request #5360 already contained a fallback calculation and tried to catch any generic HibernateExceptions. Unfortunately, the actual exception is wrapped as PersistenceException, which is not yet catched.

The new pull request #5562 fixes this. Then, indexing processes also works with MySQL 5.7 (just slower via the fallback calculation and with some log messages about the exception).

[solth]

@thomaslow thanks a lot for providing a fix on such a short notice! I added a corresponding note to https://github.com/kitodo/kitodo-production/releases/tag/kitodo-production-3.5.0

[m-kotzyba]

@thomaslow thanks also from my side! I can confirm that the upgrade of MySQL to ver. 8.0.32 solved the problem.