MySQL Bugs: #115189: P_S Digest table unexpectedly reports created database on replica

Bug #115189	P_S Digest table unexpectedly reports created database on replica
Submitted:	31 May 2024 19:54	Modified:	5 Jun 2024 18:30
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.4.0, 8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

when running CREATE DATABASE in a replicated environment, the SCHEMA_NAME from the table performance_schema.events_statements_summary_by_digest is unexpectedly on the replica.  It is reported as the database that was just created (how can this be, this db was created by this statement) while I would expect it to be the database in which it was run on the primary.  See How to repeat for details.

Note that for CREATE TABLE, I get expected results, see How to repeat.

How to repeat is for 8.4.0, but I get the same results for 8.0.37.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
# Cannot use "deploy replication" because 8.4 introduced incompatibilities with dbdeployer,
#   so we are creative for creating a replication test environment.
dbdeployer deploy multiple 8.4.0

./n1 <<< "RESET BINARY LOGS AND GTIDS"  # Replace by RESET MASTER for 8.0.
./n1 <<< "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'"
./n1 -u root <<< "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"

port=$(./n1 -N <<< "select @@global.port")
sql="change replication source to  SOURCE_HOST='127.0.0.1', SOURCE_PORT=$port"
sql="$sql, SOURCE_USER='repl', SOURCE_PASSWORD='password', SOURCE_SSL=1"
sql="$sql; start replica"
./n2 <<< "$sql"

# Empty the digest table.
sql2="set sql_log_bin = 0; truncate performance_schema.events_statements_summary_by_digest"
./n1 <<< "$sql2"; ./n2 <<< "$sql2"

# Create a database and check the digest table (sleep 1 to allow replication):
# - we have NULL as expected on the primary (no db specified on the command line),
# - but the database we just created on the replica, which is unexpected (I would expect NULL).
sql3="select SCHEMA_NAME, DIGEST_TEXT from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'CREATE%'"
./n1 <<< "create database test_jfg"
sleep 1; ./n1 -N <<< "$sql3"; ./n2 -N <<< "$sql3"
NULL	CREATE SCHEMA `test_jfg`
test_jfg	CREATE SCHEMA `test_jfg`

# For shorter tests.
function foo() { ./n1 <<< "$sql2"; ./n2 <<< "$sql2"; ./n1 $1 <<< "$2"; sleep 1; ./n1 -N <<< "$sql3"; ./n2 -N <<< "$sql3"; }

# Similar if not done in the NULL schema, I would expect test_jfg on the replica, not test_jfg2.
foo test_jfg "create database test_jfg2"
test_jfg	CREATE SCHEMA `test_jfg2`
test_jfg2	CREATE SCHEMA `test_jfg2`

# CREATE TABLE in NULL schema works as expected.
foo "" "create table test_jfg.t(id int)"
NULL	CREATE TABLE `test_jfg` . `t` ( `id` INTEGER )
NULL	CREATE TABLE `test_jfg` . `t` ( `id` INTEGER )

# So does CREATE TABLE in a specific schema.
foo "test_jfg" "create table t2(id int)"
test_jfg	CREATE TABLE `t2` ( `id` INTEGER )
test_jfg	CREATE TABLE `t2` ( `id` INTEGER )

foo "test_jfg2" "create table test_jfg.t3(id int)"
test_jfg2	CREATE TABLE `t3` ( `id` INTEGER )
test_jfg2	CREATE TABLE `t3` ( `id` INTEGER )

The same happen in the slow query log table for the db field (yes, found this while working on Bug#106645).

Note that we have NULL in the P_S "digest" table and the empty string in the mysql.slow_log table, I have not made my mind about this inconsistency being a bug or not.

./n1 <<< "set global log_output = 'TABLE'; set global slow_query_log=ON"
./n2 <<< "set global log_output = 'TABLE'; set global slow_query_log=ON"
./n2 <<< "stop replica; set global long_query_time = 0, log_slow_replica_statements = ON; start replica"

sql4="set sql_log_bin = 0; truncate mysql.slow_log"
./n1 <<< "$sql4"; ./n2 <<< "$sql4"

sql5="select db, sql_text from mysql.slow_log where sql_text like 'create%'"
./n1 <<< "set session long_query_time = 0; create database test_jfg3"
sleep 1; ./n1 -N <<< "$sql5"; ./n2 -N <<< "$sql5"
	create database test_jfg3
test_jfg3	create database test_jfg3

function bar() { ./n1 <<< "$sql4"; ./n2 <<< "$sql4"; ./n1 $1 <<< "$2"; sleep 1; ./n1 -N <<< "$sql5"; ./n2 -N <<< "$sql5"; }

bar test_jfg3 "set session long_query_time = 0; create database test_jfg4"
test_jfg3	create database test_jfg4
test_jfg4	create database test_jfg4

bar "" "set session long_query_time = 0; create table test_jfg3.t(id int)"
	create table test_jfg3.t(id int)
	create table test_jfg3.t(id int)

bar "test_jfg3" "set session long_query_time = 0; create table t2(id int)"
test_jfg3	create table t2(id int)
test_jfg3	create table t2(id int)

bar "test_jfg4" "set session long_query_time = 0; create table test_jfg3.t3(id int)"
test_jfg4	create table test_jfg3.t3(id int)
test_jfg4	create table test_jfg3.t3(id int)

Thanks for the report, verified as described.