[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?

• [x] Yes I am willing to submit a PR!

Code of Conduct

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

[zhoujinsong]

It seems the bug is similar with #3444

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.

[github-actions[bot]]

This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'