[Bug] Broken MyBatis mapper statements in jobhistory, manager-persistence and et-monitor #5497

Search before asking

• I searched the issues and found no similar issues.

Linkis Component

linkis-public-enhancements, linkis-computation-governance, linkis-extensions

Steps to reproduce

Three mapper XML statements no longer match their Java mapper methods, and the PostgreSQL job history mapper is missing one statement. MyBatis resolves #{...} parameters only when a statement runs, so nothing fails at startup; the calls fail instead.

1. jobhistory: undone-task ID refresh (DefaultQueryCacheManager.refreshUndoneTask)

refreshUndoneTask() runs once at startup (init()) and every day at 00:15 (ScheduledRefreshUndoneJob). It calls JobHistoryMapper.searchWithIdOrderAsc(startDate, endDate, startId, status) and, if that throws or returns nothing, JobHistoryMapper.maxID(startDate, endDate, startId).

On PostgreSQL:

1. Use PostgreSQL as the Linkis database, i.e. wds.linkis.server.mybatis.mapperLocations=classpath*:mapper/common/*.xml,classpath*:mapper/postgresql/*.xml as noted in linkis.properties.
2. Start the public service (or wait for the 00:15 job).
3. searchWithIdOrderAsc in mapper/postgresql/JobHistoryMapper.xml still filters on the old id, umUser and engineType parameters and maps whole rows, while the method now takes startDate, endDate, startId, status and returns List<Integer>:

   BindingException: Parameter 'id' not found. Available parameters are [endDate, startId, param3, param4, startDate, param1, param2, status]
   

4. The fallback maxID is not defined in the PostgreSQL mapper at all:

   BindingException: Invalid bound statement (not found): org.apache.linkis.jobhistory.dao.JobHistoryMapper.maxID
   

b039c2a (Oct 2023) changed the method parameters and the MySQL statement, and #5225 (fa008fb) changed the return type to List<Integer> and added maxID. The PostgreSQL mapper was updated in neither commit, so on PostgreSQL refreshUndoneTask() fails on every run.

On MySQL, maxID in mapper/mysql/JobHistoryMapper.xml is SELECT id ... ORDER BY created_time, which returns one row per job in the window, while the method returns a single Integer. When no job is unfinished and two or more jobs were created in the last 24 hours, the fallback fails with:

TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 4

When the refresh fails, undoneTaskMinId does not move (on PostgreSQL it stays at wds.linkis.jobhistory.undone.job.minimum.id, default 0). /jobhistory/listundonetasks and /jobhistory/listundone then keep querying from that id instead of from the oldest unfinished job, and the exception is logged at startup and by the daily job.

2. manager-persistence: ResourceManagerMapper.deleteResourceByTicketId

The method has only @Param("ticketId"), but its <delete> contains a copied select * ... where resourceType = #{resourceType} and id in (... #{instance}):

BindingException: Parameter 'resourceType' not found. Available parameters are [ticketId, param1]

The existing ResourceManagerMapperTest#deleteResourceByTicketId fails with exactly this error. It has gone unnoticed because the module's pom.xml sets <skip>true</skip> for surefire.

#3618 (67b314b) moved the SQL annotations into XML but kept @Delete("delete from linkis_cg_manager_linkis_resources where ticketId = #{ticketId}") on this method. #3901 (fedec80) then removed the annotation, which left the broken XML as the only definition. The only caller, DefaultResourceManagerPersistence.deleteExpiredTicketIdResource, is not used at the moment.

3. et-monitor: JobHistoryMapper.updateIncompleteJobStatusGivenIDList

The method is declared as (@Param("idList") List<Long> idList, @Param("targetStatus") String targetStatus), but the XML iterates <foreach collection="list">. MyBatis provides the implicit list key only for a single unannotated collection argument:

BindingException: Parameter 'list' not found. Available parameters are [idList, targetStatus, param1, param2]

This has been there since the mapper was added (65bcd36). The method has no callers yet.

Expected behavior

• On PostgreSQL, searchWithIdOrderAsc uses startDate, endDate, startId and status and returns the ids of unfinished jobs in ascending order, like the MySQL statement.
• maxID exists for PostgreSQL and returns the largest job id in the window on both databases.
• refreshUndoneTask() moves undoneTaskMinId forward on MySQL and on PostgreSQL.
• deleteResourceByTicketId(ticketId) deletes the resources of that ticket and nothing else.
• updateIncompleteJobStatusGivenIDList(idList, targetStatus) updates the given jobs.

Your environment

• Linkis version used: master @ bad400a (1.9.0)
• Environment name and version:
  • jdk 1.8.0 (Temurin), Maven 3.9
  • MyBatis 3.5.16, MyBatis-Plus 3.5.7 (as resolved by the build)
  • PostgreSQL 16.15
  • H2 2.2.220 in MySQL and PostgreSQL mode (unit tests)

Anything else

• I found these by cross-checking the mapper XML statements against their mapper interfaces. Two more methods have no statement at all: LabelManagerMapper.batchDeleteResourceByLabelIdInDirect, called from DefaultResourceLabelPersistence.removeResourceByLabels (its service method has no callers), and JobDetailMapper.queryJobHistoryDetail, called from JobHistoryDetailQueryServiceImpl (not registered as a bean). Both would fail with Invalid bound statement (not found) if called. The history doesn't show what SQL was intended, so I left them alone. I'm happy to remove them or add statements if someone can confirm what they should do.
• Building master currently needs the linkis-module compile fix from Fix Scala compilation of linkis-module #5483.

Are you willing to submit a PR?

• Yes I am willing to submit a PR!