mariadb/mysql-test/suite/perfschema/t/user_var_func.test
Sergei Golubchik bead24b7f3 mariadb-test: wait on disconnect
Remove one of the major sources of race condiitons in mariadb-test.
Normally, mariadb_close() sends COM_QUIT to the server and immediately
disconnects. In mariadb-test it means the test can switch to another
connection and sends queries to the server before the server even
started parsing the COM_QUIT packet and these queries can see the
connection as fully active, as it didn't reach dispatch_command yet.

This is a major source of instability in tests and many - but not all,
still less than a half - tests employ workarounds. The correct one
is a pair count_sessions.inc/wait_until_count_sessions.inc.
Also very popular was wait_until_disconnected.inc, which was completely
useless, because it verifies that the connection is closed, and after
disconnect it always is, it didn't verify whether the server processed
COM_QUIT. Sadly the placebo was as widely used as the real thing.

Let's fix this by making mariadb-test `disconnect` command _to wait_ for
the server to confirm. This makes almost all workarounds redundant.

In some cases count_sessions.inc/wait_until_count_sessions.inc is still
needed, though, as only `disconnect` command is changed:

 * after external tools, like `exec $MYSQL`
 * after failed `connect` command
 * replication, after `STOP SLAVE`
 * Federated/CONNECT/SPIDER/etc after `DROP TABLE`

and also in some XA tests, because an XA transaction is dissociated from
the THD very late, after the server has closed the client connection.

Collateral cleanups: fix comments, remove some redundant statements:
 * DROP IF EXISTS if nothing is known to exist
 * DROP table/view before DROP DATABASE
 * REVOKE privileges before DROP USER
 etc
2025-07-16 09:14:33 +07:00

97 lines
2.4 KiB
Text

--source include/not_embedded.inc
--source include/have_perfschema.inc
set @v1a="Hello";
set @v2a="World";
set @v3a=65;
set @v4a=NULL;
select @v1a, @v2a, @v3a, @v4a, @v_unknown;
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
connect(con1, localhost, root,,);
set @v1b="Salut";
set @v2b="La compagnie";
set @v3b=12;
set @v4b=NULL;
select @v1b, @v2b, @v3b, @v4b, @v_unknown;
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
--connection default
--disconnect con1
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
# Make sure large values are not truncated
set @blob_under_16_bits=repeat("a", 65000);
set @blob_under_24_bits=repeat("a", 1000000);
# Not testable because of limitation on max_allowed_packet
# set @blob_under_32_bits=repeat("a", 20000000);
select length(@blob_under_16_bits);
select length(@blob_under_24_bits);
# select length(@blob_under_32_bits);
select variable_name, length(variable_value)
from performance_schema.user_variables_by_thread
where VARIABLE_NAME like "blob%"
order by VARIABLE_NAME;
# Check the behaviour with procedures if the values passed inside procedures gets reflected in the table performance_schema.user_variables_by_thread.
set @a=10;
DELIMITER |;
CREATE procedure test_user_val
(IN input INT,OUT output INT)
BEGIN
SELECT input + input
INTO output;
END|
DELIMITER ;|
CALL test_user_val(@a , @total);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name in ('a','total') order by VARIABLE_NAME;
# Checking behaviour around triggers if the values in performance_schema.user_variables_by_thread are updated from within trigger
create table t1 (c1 int);
DELIMITER |;
CREATE TRIGGER test_uvar_trigger
AFTER INSERT ON t1
FOR EACH ROW BEGIN
set @a=@a+@a;
END|
DELIMITER ;|
insert into t1 values(1);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name ='a' order by VARIABLE_NAME;
insert into t1 values(1);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name ='a' order by VARIABLE_NAME;
drop table t1;
drop procedure test_user_val;
#drop TRIGGER test_uvar_trigger;