[Bug]: fragile bvt cases in create_retention

[aptend]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

5142544

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

2024-09-20 07:02:21 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:199][select * from mo_catalog.mo_retention;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:201 - [EXPECT RESULT]:
database_name    table_name    retention_deadline
2024-09-20 07:02:21 ERROR Executor:202 - [ACTUAL RESULT]:
database_name    table_name    retention_deadline
test01    aff01    1726815738
2024-09-20 07:02:21 ERROR RSSet:164 - The row count does not equal with each other, one is 1, the other is 2
2024-09-20 07:02:21 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:200][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
pri01
2024-09-20 07:02:21 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
aff01
pri01
2024-09-20 07:02:21 ERROR Executor:[269](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:270) - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:202][drop table pri01;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:[271](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:272) - [EXPECT RESULT]:
null
2024-09-20 07:02:21 ERROR Executor:[272](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:273) - [ACTUAL RESULT]:
internal error: can not drop table 'pri01' referenced by some foreign key constraint
2024-09-20 07:02:24 ERROR RSSet:164 - The row count does not equal with each other, one is 1, the other is 2
2024-09-20 07:02:24 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:228][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:24 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
back01
2024-09-20 07:02:24 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
back01
pri01
2024-09-20 07:02:31 ERROR RSSet:164 - The row count does not equal with each other, one is 0, the other is 1
2024-09-20 07:02:31 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:256][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:31 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
2024-09-20 07:02:31 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
pri01

Expected Behavior

No response

Steps to Reproduce

https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479

Additional information

No response

[badboynt1]

repro in https://github.com/matrixorigin/matrixone/actions/runs/11065130541/job/30744138234?pr=19055

2024-09-27 05:44:59 INFO Executor:37 - Start to execute the script file[/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_table_with_retention.sql] now, and it will take a few moment,pleas wait...... 2024-09-27 05:45:03 INFO Executor:692 - select mo_ctl('cn','synccommit','') with sys user[dump] successfully. 2024-09-27 05:45:05 ERROR RSSet:164 - The row count does not equal with each other, one is 2, the other is 1 2024-09-27 05:45:05 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_table_with_retention.sql][row:28][select * from mo_catalog.mo_retention;] was executed failed, con[id=2, user=acc0:root, * 2024-09-27 05:45:05 ERROR Executor:201 - [EXPECT RESULT]: database_name table_name retention_deadline test t1 1751969233 test t2 1726049234 2024-09-27 05:45:05 ERROR Executor:202 - [ACTUAL RESULT]: database_name table_name retention_deadline test t1 1753335903 2024-09-27 05:45:05 ERROR RSSet:164 - The row count does not equal with each other, one is 1, the other is 0 2024-09-27 05:45:05 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_table_with_retention.sql][row:31][select from mo_catalog.mo_retention;] was executed failed, con[id=2, user=acc0:root, 2024-09-27 05:45:05 ERROR Executor:201 - [EXPECT RESULT]: database_name table_name retention_deadline test t2 1726049234 2024-09-27 05:45:05 ERROR Executor:202 - [ACTUAL RESULT]: database_name table_name retention_deadline

[w-zr]

已提交pr。再观察一段时间。

[daviszhen]

https://github.com/matrixorigin/matrixone/actions/runs/11433038461/job/31804426866?pr=19484

[reusee]

repro: https://github.com/matrixorigin/matrixone/actions/runs/11471563158/job/31922675110?pr=19521

[w-zr]

repro: https://github.com/matrixorigin/matrixone/actions/runs/11471563158/job/31922675110?pr=19521

This bvt failed at restore cluster from snapshot cluster_sp; and restore account sys from snapshot sp01;. This is tracked by another issue: #18998

[daviszhen]

https://github.com/matrixorigin/matrixone/actions/runs/11475242627/job/31932809418?pr=19541

[w-zr]

https://github.com/matrixorigin/matrixone/actions/runs/11475242627/job/31932809418?pr=19541

这个bvt挂掉是create_table_with_retention.sql。目前retention相关的测试都在test/distributed/cases/ddl/create_retention_table.sql中。create_table_with_retention.sql已经被删掉了。

[w-zr]

最近已经没有复现，可以关闭了。