[bugfix] fix delete limit error

[Aias00]

What's changed?

fix delete limit error

2024-07-30 14:57:01 [jpa-metrics-cleaner-0] ERROR org.apache.hertzbeat.warehouse.store.history.jpa.JpaDatabaseDataStorage - expiredDataCleaner database error: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: 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 ':delNum' at line 1 Error Code: 1064 Call: delete from hzb_history limit :delNum Query: DataModifyQuery(sql="delete from hzb_history limit :delNum").

Checklist

• [x] I have read the Contributing Guide
• [x] I have written the necessary doc or comment.
• [x] I have added the necessary unit tests and all cases have passed.

Add or update API

• [x] I have added the necessary e2e tests and all cases have passed.

[pwallk]

Similar pr #2329

[tomsun28]

hi @Aias00 , how about this similar pr https://github.com/apache/hertzbeat/pull/2329 @pwallk describe

the delNum argument cannot be passed correctly and should be changed instead：delete from hzb_history limit ?1,secondly postgresql does not support delete followed by limit，Therefore, it should be deleted with nested subqueries (earlier MySQL versions did not support nested subqueries, such as 5.7, 5.6, so two layers of nesting are used).

[Aias00]

hi @Aias00 , how about this similar pr #2329 @pwallk describe

the delNum argument cannot be passed correctly and should be changed instead：delete from hzb_history limit ?1,secondly postgresql does not support delete followed by limit，Therefore, it should be deleted with nested subqueries (earlier MySQL versions did not support nested subqueries, such as 5.7, 5.6, so two layers of nesting are used).

yes, i made a mistake, postgresql truly not support this delete limit statement. @pwallk was right, i will close this pr 😂