MySQL Bugs: #116744: Bug system has problems

Bug #116744	Bug system has problems
Submitted:	21 Nov 2024 11:56	Modified:	10 Dec 2024 5:45
Reporter:	karry zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Interrupt optimize table, in some cases we will get the following return:

Table	Op	Msg_type	Msg_text
rpl_test.t1	optimize	Error	Query execution was interrupted
rpl_test.t1	optimize	error	Corrupt

But in fact the table is intact. Moreover, the optimize operation should not cause table corruption.

How to repeat:
Execute the following test case. You need to repeat it several times because my debug_sync location is not suitable.
====================================

--echo #
--echo # Prepare.
--echo ########################################################
--echo #
--source include/master-slave.inc
--source include/have_binlog_format_row.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc

--source include/rpl_connection_master.inc
--let $gtid_event= Gtid
if (!$gtid_mode_on)
{
  --let $gtid_event= Anonymous_Gtid
}

--echo #
--echo # 1. Make test data.
--echo ########################################################
--echo #

CREATE DATABASE rpl_test;
CREATE TABLE rpl_test.t1 (s int);

--disable_query_log
--let $i=0
while ($i < 1000)
{
  --eval INSERT INTO rpl_test.t1 values($i)
  --inc $i
}
CREATE USER test1@localhost;
GRANT ALL PRIVILEGES ON *.* TO test1@localhost;

--enable_query_log
FLUSH BINARY LOGS;
--source include/rpl_sync.inc
--source include/rpl_connection_slave.inc
FLUSH BINARY LOGS;

--echo #
--echo # 2. Optimize table and kill the query.
--echo ########################################################
--echo #
connect (test1,localhost,test1,,);
connection test1;
SET DEBUG_SYNC='ha_admin_try_alter WAIT_FOR kill_query';
--send OPTIMIZE TABLE rpl_test.t1

--source include/rpl_connection_master.inc
--disable_warnings
SELECT id INTO @id FROM information_schema.processlist WHERE user='test1' LIMIT 1;
--enable_warnings
KILL QUERY @id;
SET DEBUG_SYNC='now SIGNAL kill_query';

connection test1;
--reap
SET DEBUG_SYNC='ha_admin_open_ltable WAIT_FOR kill_query';
--send OPTIMIZE TABLE rpl_test.t1

--source include/rpl_connection_master.inc
--disable_warnings
SELECT id INTO @id FROM information_schema.processlist WHERE user='test1' LIMIT 1;
--enable_warnings
KILL QUERY @id;
SET DEBUG_SYNC='now SIGNAL kill_query';

connection test1;
--reap

==================================
You will get the following result:
==================================
#
# Prepare.
########################################################
#
include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Server\\] \\[RDS Diagnose\\] .*");
[connection master]
#
# 1. Make test data.
########################################################
#
CREATE DATABASE rpl_test;
CREATE TABLE rpl_test.t1 (s int);
FLUSH BINARY LOGS;
include/rpl_sync.inc
[connection slave]
FLUSH BINARY LOGS;
#
# 2. Optimize table and kill the query.
########################################################
#
SET DEBUG_SYNC='ha_admin_try_alter WAIT_FOR kill_query';
OPTIMIZE TABLE rpl_test.t1;
[connection master]
SELECT id INTO @id FROM information_schema.processlist WHERE user='test1' LIMIT 1;
KILL QUERY @id;
SET DEBUG_SYNC='now SIGNAL kill_query';
Table	Op	Msg_type	Msg_text
rpl_test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
rpl_test.t1	optimize	status	Operation failed
SET DEBUG_SYNC='ha_admin_open_ltable WAIT_FOR kill_query';
OPTIMIZE TABLE rpl_test.t1;
[connection master]
SELECT id INTO @id FROM information_schema.processlist WHERE user='test1' LIMIT 1;
KILL QUERY @id;
SET DEBUG_SYNC='now SIGNAL kill_query';
Table	Op	Msg_type	Msg_text
rpl_test.t1	optimize	Error	Query execution was interrupted
rpl_test.t1	optimize	error	Corrupt

Suggested fix:
This msg_txt is incorrect, fix it.

Hi Mr. zhang,

Thank you for your bug report.

We have ran your test 5 (five) times and we have got the following output every single time:

 Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	####	Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
 [connection master]
-CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Server\\] \\[RDS Diagnose\\] .*");
 [connection master]
 #
 # 1. Make test data.
@@ -39,6 +38,5 @@
 KILL QUERY @id;
 SET DEBUG_SYNC='now SIGNAL kill_query';
 Table	Op	Msg_type	Msg_text
-rpl_test.t1	optimize	Error	Query execution was interrupted
-rpl_test.t1	optimize	error	Corrupt
-
+rpl_test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+rpl_test.t1	optimize	status	Operation failed

Hi Mr. zhang,

Thank you for your bug report.

Now that we have looked closer it is quite clear that we have repeated the corruption of the table when OPTIMIZE operation was interrupted.

Hence, this is now a verified bug for the version 8.0 and all higher versions.

Thanks a lot.

Fix Interrupting optimize table will result in a corrupt error (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix.patch (application/octet-stream, text), 679 bytes.

Thank you, Mr. Zhang, for your contribution.

It seems here's something wrong with your bug system, I can't report bugs.

Hi Mr. zhang,

Reporting bugs works just fine ...

The page is here:

https://bugs.mysql.com/report.php

Hi,
  It is not ok. The page https://bugs.mysql.com/report.php can be accessed.
But if the 'Send bug report' button is clicked, HTTP 500 error is returned.