Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Bug]: internal error: table 'table_with_30c' has been changed, please reset prepare statement '__mo_stmt_id_1' error when executing delete from table during prepared inserting #20864

Open
1 task done
aressu1985 opened this issue Dec 21, 2024 · 6 comments
Assignees
Labels
kind/bug Something isn't working phase/testing severity/s0 Extreme impact: Cause the application to break down and seriously affect the use
Milestone

Comments

@aressu1985
Copy link
Contributor

Is there an existing issue for the same bug?

  • I have checked the existing issues.

Branch Name

2.0-dev

Commit ID

7a8aac5

Other Environment Information

- Hardware parameters: standlone
- OS type:
- Others:

Actual Behavior

During executing prepared inserting data to a table, if deleting from table, prepared inserting operation will failed by error
" internal error: table 'table_with_30c' has been changed, please reset prepare statement '__mo_stmt_id_1'"

2024-12-21 16:50:22 ERROR PreparedStatementExecutor:170 -
ErrorTime : 2024-12-21 16:50:22
RequestTime : 2024-12-21 16:50:22.227
TxnName : insert_30c
Statement : INSERT INTO insert_30c.table_with_30c(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26 ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
Parameters : [21, 2720, 187597485, 810982325, 120, 30165, 1030905377, 2002582184, 610304281.44, 524511299.219, JHHEzldKuNFAUhpwFAqewvsQwxNcCPyfGHzgYtuzTsVGvfFdwrCTAzdHjhnDvaRlVWDbgLRFIngEyHgPWObpivGebaAxlXCOLvoNUPDnwkceEFATYzocyaCIOwpbuKElMFuRLcaMDczmqWUtZGCagTxduwlKEhLxggsjXJuHOqZrCqOGTIQDimxYydbEGJFifYuKpzcSANWKSYSYvbYqpDYLobZeKOddrHWebbrQq, 1949-03-10, 1933-07-10, 1911-01-27 13:43:06.0, false, 1747691268.415, C, {"TFcxZaeeC":"imnHs","KAbm":"yblvuGusr","eK":"ZU","yrvmChA":"vNzBe","XBR":"nqxcTiV","isActive":false,"cGgOHjKV":"Zxrd","v":"geQJfhd","opfHpOF":"W","name":"周伟宸","CzCFlgO":"JlrremKjx","id":1729920291,"value":3.378010826538789E307,"Pm":"AcJVAE"}, T, AaOkLLi, wdlQaUmhDMqNxbQZlaKXlUqaJLCKDgpkLpuZiPpsIAqWKBoQaYZTXvzApLCLJatcltzJdfdqOdXUtblHFtvmOeudmQoVluNQNDQzgSSWyExrAQsMrBaUeSGmNBeRrWhswZQZTTHAlMSGuZMNEhTUsdqOUxMRtCJbNoLPKMNVhgYHohgXFDXLCFELpVHDeLRWPDJsDSHfJWbaWDKSaCwhLmDgvKggEIxcrbcXTrXVpast, [108.867,57.322,118.717], [75.781,6.537,86.245], [74.44,30.94,116.291], [45.911,24.308,19.707], 44b4e3d1-a195-6495-2c45-e94919ce5d3d]
ErrorCode : 20101
ErrorMessage : internal error: table 'table_with_30c' has been changed, please reset prepare statement '__mo_stmt_id_1'
Expected : false

2024-12-21 16:50:22 INFO RUN:498 - Program is shutting down,now will release the resources...
2024-12-21 16:50:22 ERROR PreparedStatementExecutor:170 -
ErrorTime : 2024-12-21 16:50:22
RequestTime : 2024-12-21 16:50:22.230
TxnName : insert_30c
Statement : INSERT INTO insert_30c.table_with_30c(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26 ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
Parameters : [65, 21489, 1180094085, 502208269, 52, 7951, 1310041027, 966554889, 1049178376.262, 2046152349.248, trAAncPEdJDSdycgeWdbtfpa, 1998-03-21, 1955-03-25, 1930-09-09 14:42:17.0, false, 1279240575.450, XzHB, {"Qr":"Ss","FiBB":"BNQzL","RLhyk":"qSxNWz","isActive":false,"bSTeIXEWY":"q","UFVXNXBI":"Q","qygKSsG":"tMY","rkgRo":"vEfr","KmvXKmegf":"BhhCcotv","MlciYVz":"VeS","name":"毛文博","Y":"vLVLGOg","id":1575594929,"value":1.6319312150042248E307}, Cgpj, PXXIgCiBFpBAHoomKHRbuqBynOdZuTPwDoZzPDFSnwWCftRhwGqTmyFcaqnSIAXajSzEkyXzCJnbnyatvCvIjdutOqyoQebZRTmxBEfXbUoLMcuHwUePWSsDQHTtALhHFqxwwkvXQidRYH, hGRZCzkIskwmOXliiSxJEcrwkttCxaihg, [91.734,17.937,8.713], [120.21,60.5,73.83], [49.805,93.329,2.9], [106.241,23.526,15.659], b010548e-91d4-64b8-5eab-d4a2f2718de2]
ErrorCode : 20101
ErrorMessage : internal error: table 'table_with_30c' has been changed, please reset prepare statement '__mo_stmt_id_1'

mo-log:

Expected Behavior

No response

Steps to Reproduce

1. create table 
create table if not exists insert_30c.table_with_30c( 
id int auto_increment, 
name varchar(20), 
age tinyint, 
gender bigint, 
col5 tinyint unsigned, 
col6 smallint unsigned, 
col7 int unsigned, 
col8 bigint unsigned, 
col9 float, 
col10 double, 
col11 varchar(255), 
col12 Date, 
col13 DateTime, 
col14 timestamp, 
col15 bool, 
col16 decimal(16,6), 
col17 text, 
col18 json, 
col19 blob, 
col20 binary(255), 
col21 varbinary(255), 
col22 vecf32(3), 
col23 vecf32(3), 
col24 vecf64(3), 
col25 vecf64(3),
col26 uuid 
);

2.prepared inserting some data
Statement : INSERT INTO insert_30c.table_with_30c(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26 ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
Parameters : [21, 2720, 187597485, 810982325, 120, 30165, 1030905377, 2002582184, 610304281.44, 524511299.219, JHHEzldKuNFAUhpwFAqewvsQwxNcCPyfGHzgYtuzTsVGvfFdwrCTAzdHjhnDvaRlVWDbgLRFIngEyHgPWObpivGebaAxlXCOLvoNUPDnwkceEFATYzocyaCIOwpbuKElMFuRLcaMDczmqWUtZGCagTxduwlKEhLxggsjXJuHOqZrCqOGTIQDimxYydbEGJFifYuKpzcSANWKSYSYvbYqpDYLobZeKOddrHWebbrQq, 1949-03-10, 1933-07-10, 1911-01-27 13:43:06.0, false, 1747691268.415, C, {"TFcxZaeeC":"imnHs","KAbm":"yblvuGusr","eK":"ZU","yrvmChA":"vNzBe","XBR":"nqxcTiV","isActive":false,"cGgOHjKV":"Zxrd","v":"geQJfhd","opfHpOF":"W","name":"周伟宸","CzCFlgO":"JlrremKjx","id":1729920291,"value":3.378010826538789E307,"Pm":"AcJVAE"}, T, AaOkLLi, wdlQaUmhDMqNxbQZlaKXlUqaJLCKDgpkLpuZiPpsIAqWKBoQaYZTXvzApLCLJatcltzJdfdqOdXUtblHFtvmOeudmQoVluNQNDQzgSSWyExrAQsMrBaUeSGmNBeRrWhswZQZTTHAlMSGuZMNEhTUsdqOUxMRtCJbNoLPKMNVhgYHohgXFDXLCFELpVHDeLRWPDJsDSHfJWbaWDKSaCwhLmDgvKggEIxcrbcXTrXVpast, [108.867,57.322,118.717], [75.781,6.537,86.245], [74.44,30.94,116.291], [45.911,24.308,19.707], 44b4e3d1-a195-6495-2c45-e94919ce5d3d]

3.delete table
delete from table_with_30c;

Additional information

No response

@aressu1985 aressu1985 added kind/bug Something isn't working needs-triage severity/s0 Extreme impact: Cause the application to break down and seriously affect the use labels Dec 21, 2024
@aressu1985 aressu1985 added this to the 2.0.2 milestone Dec 21, 2024
@sukki37 sukki37 assigned ouyuanning and unassigned matrix-meow Dec 23, 2024
@ouyuanning
Copy link
Contributor

这个是因为delete from table 且没有where子句,是被优化成truncate table。
而truncate table目前是作为DDL语句,会更改表id。所以这个时候对于原始的prepare语句,其对应的表其实是不存在了的。这个报错在这种情况下是预期行为。

如果要修改可能有2个方向
1、truncate table不要更改表id,也不要提升表的version,只是标记之前所有的s3和logtail,workspace数据被删了
2、发现表id变更。prepare语句偷偷重新build plan(但是由于我们也不知道它是不是真的drop后重建表,这种情况可能会有一些不是用户预期的行为)

@ouyuanning
Copy link
Contributor

还有一种修改
3、delete from table。 不要转成truncate table。

@allengaoo
Copy link

在关系型数据库中,delete 和truncate 的含义是不一样的。 一般来说:

  1. delete 是一个dml 语句,表示从表里面删除数据,是满足事务的各种特性。
  2. truncate 是一个ddl 语句,它用来快速的清除掉表里面的所有数据,它可能不满足事务的一些特性,例如:不能rollback。

从产品的角度讲,不建议把delete 语句优化成truncate。

@ouyuanning
Copy link
Contributor

收到。那我修改一下。
同时跟高老师和苏动同步了一下。预计
https://grafana.ci.matrixorigin.cn/d/e5fce68a-ce18-49d6-b3d5-d9fc5f00859e/write-10-million?orgId=1
中的
image
这个指标项会有较大回退

@allengaoo
Copy link

allengaoo commented Dec 23, 2024

这个地方因为会改变产品的默认行为,并且可能会有性能回退。 建议做一个配置来控制产品的行为,默认我们还是保持原有的产品行为,当需要的时候,我们把配置打开,取消掉delete 到 truncate 的优化行为。

@ouyuanning ouyuanning assigned huby2358 and unassigned ouyuanning Dec 23, 2024
@ouyuanning
Copy link
Contributor

@huby2358
辛苦波洋根据高老师建议修改一下吧。建议放到session级的变量控制即可。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/bug Something isn't working phase/testing severity/s0 Extreme impact: Cause the application to break down and seriously affect the use
Projects
None yet
Development

No branches or pull requests

6 participants