[Bug]: Issue with Table Optimization Due to Duplicate Insertions

[linpeibiao]

What happened?

A table with real-time data insertion and AMS auto-optimization initially performed merge operations correctly. After a resource group switch, the table stopped optimizing. It was determined that AMS repeatedly executed database insertions, causing a uniqueness constraint violation. This resulted in the table's optimizing status being stuck at "committing," preventing further operations.

Affects Versions

master/0.7.0

What table formats are you seeing the problem on?

Iceberg

What engines are you seeing the problem on?

AMS

How to reproduce

1. Enable AMS auto-optimization on a table with real-time data insertion.
2. Perform a resource group switch.

Relevant log output

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'iceberg-prod_silver-polygon_address_native_balance_mid' for key 'table_runtime.table_index'
### The error may exist in org/apache/amoro/server/persistence/mapper/TableMetaMapper.java (best guess)
### The error may involve org.apache.amoro.server.persistence.mapper.TableMetaMapper.insertTableRuntime-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO table_runtime (table_id, catalog_name, db_name, table_name, current_snapshot_id, current_change_snapshotId, last_optimized_snapshotId, last_optimized_change_snapshotId, last_major_optimizing_time, last_minor_optimizing_time, last_full_optimizing_time, optimizing_status, optimizing_status_start_time, optimizing_process_id, optimizer_group, table_config, pending_input) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'iceberg-prod_silver-polygon_address_native_balance_mid' for key 'table_runtime.table_index'
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86) ~[mybatis-3.5.15.jar:3.5.15]
        at com.sun.proxy.$Proxy40.insertTableRuntime(Unknown Source) ~[?:?]
        at org.apache.amoro.server.table.TableRuntime.lambda$persistTableRuntime$16(TableRuntime.java:366) ~[amoro-ams-server-0.7.0-incubating.jar:0.7.0-incubating]
        at org.apache.amoro.server.persistence.PersistentBase.doAs(PersistentBase.java:49) ~[amoro-ams-server-0.7.0-incubating.jar:0.7.0-incubating]
        ... 13 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'iceberg-prod_silver-polygon_address_native_balance_mid' for key 'table_runtime.table_index'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.9.0.jar:2.9.0]

Anything else

1. Other tables with resource group switches do not exhibit this issue.
2. It is unclear if the resource group switch directly causes the duplicate insertions.
3. Investigation might start with the insert into table_runtime operation.

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct