[Bug]: Sync new table failed due to duplicate entry error in table_runtime table

[zhoujinsong]

What happened?

Some Iceberg tables no longer generate new self-optimizing processes as expected.

Affects Versions

master

What table formats are you seeing the problem on?

Iceberg

What engines are you seeing the problem on?

AMS

How to reproduce

• Create an Iceberg table
• Drop this new Iceberg table
• Create an Iceberg table with the same name
• No new self-optimizing processes are created in the table as expected

Relevant log output

2025-02-24 00:01:24,280 ERROR [table-explorer-executor-5] [org.apache.amoro.server.table.DefaultTableService] [] - TableExplorer sync table TableIdentity{database=thea_for_govern_277_3, tableName=yuxinghe02} error
org.apache.amoro.exception.PersistenceException: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'dlc_1304581893_catalog-thea_for_govern_277_3-yuxinghe02' 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_code, optimizing_status_start_time, optimizing_process_id, optimizer_group, table_config, pending_input, table_summary) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'dlc_1304581893_catalog-thea_for_govern_277_3-yuxinghe02' for key 'table_runtime.table_index'
        at org.apache.amoro.exception.AmoroRuntimeException.wrap(AmoroRuntimeException.java:141) ~[amoro-common-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.persistence.PersistentBase.doAs(PersistentBase.java:71) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.TableRuntime.persistTableRuntime(TableRuntime.java:446) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.TableRuntime.<init>(TableRuntime.java:111) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.DefaultTableService.triggerTableAdded(DefaultTableService.java:441) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.DefaultTableService.lambda$syncTable$18(DefaultTableService.java:421) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:948) ~[?:1.8.0_392]
        at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:647) ~[?:1.8.0_392]
        at org.apache.amoro.server.persistence.PersistentBase.doAsTransaction(PersistentBase.java:91) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.DefaultTableService.syncTable(DefaultTableService.java:411) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.table.DefaultTableService.lambda$null$7(DefaultTableService.java:317) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1640) [?:1.8.0_392]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_392]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_392]
        at java.lang.Thread.run(Thread.java:750) [?:1.8.0_392]
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'dlc_1304581893_catalog-thea_for_govern_277_3-yuxinghe02' 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_code, optimizing_status_start_time, optimizing_process_id, optimizer_group, table_config, pending_input, table_summary) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'dlc_1304581893_catalog-thea_for_govern_277_3-yuxinghe02' 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.$Proxy44.insertTableRuntime(Unknown Source) ~[?:?]
        at org.apache.amoro.server.table.TableRuntime.lambda$persistTableRuntime$16(TableRuntime.java:446) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.persistence.PersistentBase.doAs(PersistentBase.java:67) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        ... 13 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'dlc_1304581893_catalog-thea_for_govern_277_3-yuxinghe02' 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]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.9.0.jar:2.9.0]
        at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_392]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_392]
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58) ~[mybatis-3.5.15.jar:3.5.15]
        at com.sun.proxy.$Proxy39.execute(Unknown Source) ~[?:?]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.15.jar:3.5.15]
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.15.jar:3.5.15]
        at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_392]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_392]
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.15.jar:3.5.15]
        at com.sun.proxy.$Proxy36.update(Unknown Source) ~[?:?]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[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.$Proxy44.insertTableRuntime(Unknown Source) ~[?:?]
        at org.apache.amoro.server.table.TableRuntime.lambda$persistTableRuntime$16(TableRuntime.java:446) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        at org.apache.amoro.server.persistence.PersistentBase.doAs(PersistentBase.java:67) ~[amoro-ams-0.8-SNAPSHOT.jar:0.8-SNAPSHOT]
        ... 13 more

Anything else

No response

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