MySQL Bugs: #111822: Sporadic failures in main.sp-threads test

Bug #111822	Sporadic failures in main.sp-threads test
Submitted:	19 Jul 2023 20:12	Modified:	24 Aug 2023 7:53
Reporter:	Daniel Lenski (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Tools: MTR / mysql-test-run	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mtr

Description:
The `main.sp-threads` MTR test sporadic fails.

https://github.com/mysql/mysql-server/blob/ea1efa9822d81044b726aab20c857d5e1b7e046a/mysql-...

This test fails both sporadically and systematically, due to timing issues
and due to dumping extraneous information the .result file:

1. The test needs to wait until the thread under test enters the expected state, otherwise it may fail sporadically.

→ Fixed by using `include/wait_condition.inc` appropriately, rather than a hard-coded 2 second wait.

2. The test expects not to find any rows from users other than `root`
in the `show processlist` output. Depending on exactly how the
test server has been prepared, this may not be accurate; for
example, there may be threads with `user='event_scheduler'` if
the event scheduler has been enabled.

→ Fixed by not dumping unnecessary rows to the .result file.

It turns out that a patch for PRECISELY THESE TWO PROBLEMS has existed in
MariaDB for many years:
https://github.com/mariaDB/server/commit/618e300bce133cd4b4379b9f456d65144ae4c62b

I have very lightly adapted these (GPLv2-licensed) changes for MySQL, in https://github.com/dlenski/mysql-server/commit/308e20355abc76130040e922146471a0482f6cda

How to repeat:
1. Run the `main.sp-threads` test repeatedly. It will sporadically fail.

2. Enable the event scheduler in the test server before running the `main.sp-threads` test. It will consistently fail.

Suggested fix:
Apply the changes from:

https://github.com/dlenski/mysql-server/commit/308e20355abc76130040e922146471a0482f6cda.pa...

(Originally written in 2016 by Sergey Vojtovich <svoj@mariadb.org>, and lightly adapted by me to apply on MySQL 8.0 source.)

Hello Daniel,

Thank you for the report and feedback.
I attempted to reproduce with 8.0.34(binary tarball build) but not seeing any issues. If you are seeing this on a source build then may I request you to share cmake options used for the build? Thank you.

-- Attempted some 20+ times using release/debug builds with no luck so far

ushastry@ubuntu-22:~/Downloads/mysql-8.0.34/mysql-test$ ./mtr main.sp-threads --mysqld='--event-scheduler=ON' --debug-server

Logging: ./mtr  main.sp-threads --mysqld=--event-scheduler=ON --debug-server

MySQL Version 8.0.34

Checking supported features

 - Binaries are debug compiled

Using 'all' suites

Collecting tests

Checking leftover processes

Removing old var directory

Creating var directory '/home/ushastry/Downloads/mysql-8.0.34/mysql-test/var'

Installing system database

Using parallel: 1

==============================================================================

                  TEST NAME                       RESULT  TIME (ms) COMMENT

------------------------------------------------------------------------------

[ 50%] main.sp-threads                           [ pass ]   4939

[100%] shutdown_report                           [ pass ]       

------------------------------------------------------------------------------

The servers were restarted 0 times
The servers were reinitialized 0 times

Spent 4.939 of 230 seconds executing testcases

Completed: All 2 tests were successful.

ushastry@ubuntu-22:~/Downloads/mysql-8.0.34/mysql-test$ ./mtr main.sp-threads --mysqld='--event-scheduler=ON' 

Logging: ./mtr  main.sp-threads --mysqld=--event-scheduler=ON

MySQL Version 8.0.34

Checking supported features

Using 'all' suites

Collecting tests

Checking leftover processes

Removing old var directory

Creating var directory '/home/ushastry/Downloads/mysql-8.0.34/mysql-test/var'

Installing system database

Using parallel: 1

==============================================================================

                  TEST NAME                       RESULT  TIME (ms) COMMENT

------------------------------------------------------------------------------

[ 50%] main.sp-threads                           [ pass ]   4656

[100%] shutdown_report                           [ pass ]       

------------------------------------------------------------------------------

The servers were restarted 0 times

The servers were reinitialized 0 times

Spent 4.656 of 42 seconds executing testcases

Completed: All 2 tests were successful.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I wrote:

> How to repeat:
> 1. Run the `main.sp-threads` test repeatedly. It will sporadically fail.
> 
> 2. Enable the event scheduler in the test server before running the `main.sp-threads` test. It will consistently fail.

MySQL verification team wrote:

> Thank you for the report and feedback.
> I attempted to reproduce with 8.0.34(binary tarball build) but not seeing any issues.

Did you actually test *both* scenarios I describe above?

Even if you were not able to directly reproduce, it should be pretty clear from the test code…

- Case 1. Why the race condition is *possible*

- Case 2. Why enabling the event scheduler will cause a consistent failure. If the event scheduler is enabled, there are GUARANTEED to be extra rows in the output of `show processlist`.

My initial report of this was based on a source build of MySQL 8.0.32.

I do not have access to the exact `cmake` options I used when building and testing for this initial report.

I can attempt to reproduce it again on 8.0.34.

Thank you for the feedback.
Actually, if you see I tried *both* scenarios as you mentioned.
Verifying based on the cases (1) and (2), with event scheduler enabled surely there will be a process for non-root user.

#       event_scheduler localhost       NULL    Daemon  #       Waiting on empty queue  NULL
#       root    localhost       test    Query   #       STATE   show processlist