Monitoring of PostgreSQL won't start after upgrading to DPA 2024.2 and above

This article discusses how to resolve an issue with monitoring PostgreSQL after upgrading to version 2024.2 and above.

The database hosting the repository is MySQL, and this error appears before the monitoring stops.

java.sql.SQLException: 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 '(ORDER BY TIMESTAMP desc) AS ROW_NUM ,cps.TIMESTAMP , cps.SQLHASH , cps.NODE_ID ' at line 1
Query is: SELECT TIMESTAMP FROM ( SELECT ROW_NUMBER() OVER(ORDER BY TIMESTAMP desc) AS ROW_NUM ,cps.TIMESTAMP , cps.SQLHASH , cps.NODE_ID FROM CONPPT_3 cps WHERE SQLHASH = ? and NODE_ID = 0 ) a WHERE a.ROW_NUM = 1 , parameters [5757080240]
java thread: repositoryManager-thread-18

DPA 2024.2 and above no longer support MySQL 5.6 or 5.7.

Starting in 2024.2, DPA no longer supports hosting the repository in MySQL versions 5.7 and 5.6: DPA 2024.2 Release Notes.

This happens because DPA uses Windows Functions that were introduced in MySQL 8 and do not exist in lower versions.

Since this function is not recognized in lower versions of MySQL, it will throw a bad SQL grammar error.

To resolve this issue, upgrade MySQL to the supported versions: Repository Requirements.