mirror of
https://github.com/MariaDB/server.git
synced 2025-09-02 15:42:05 +02:00
bead24b7f3
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
148 lines
11 KiB
Text
148 lines
11 KiB
Text
CREATE DATABASE IF NOT EXISTS events_test;
|
|
use events_test;
|
|
CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123;
|
|
Warnings:
|
|
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
|
|
SHOW EVENTS;
|
|
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
|
|
events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
CREATE DATABASE events_test2;
|
|
CREATE USER ev_test@localhost;
|
|
GRANT ALL ON events_test.* to ev_test@localhost;
|
|
GRANT ALL ON events_test2.* to ev_test@localhost;
|
|
GRANT ALL ON test.* TO ev_test@localhost;
|
|
REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
|
|
connect ev_con1,localhost,ev_test,,events_test2;
|
|
select "NEW CONNECTION";
|
|
NEW CONNECTION
|
|
NEW CONNECTION
|
|
SELECT USER(), DATABASE();
|
|
USER() DATABASE()
|
|
ev_test@localhost events_test2
|
|
SHOW GRANTS;
|
|
Grants for ev_test@localhost
|
|
GRANT USAGE ON *.* TO `ev_test`@`localhost`
|
|
GRANT ALL PRIVILEGES ON `test`.* TO `ev_test`@`localhost`
|
|
GRANT ALL PRIVILEGES ON `events_test`.* TO `ev_test`@`localhost`
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, DELETE HISTORY, SHOW CREATE ROUTINE ON `events_test2`.* TO `ev_test`@`localhost`
|
|
"Here comes an error:";
|
|
SHOW EVENTS;
|
|
ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
|
|
USE events_test;
|
|
"We should see one event";
|
|
SHOW EVENTS;
|
|
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
|
|
events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
SELECT CONCAT("Let's create some new events from the name of ", USER());
|
|
CONCAT("Let's create some new events from the name of ", USER())
|
|
Let's create some new events from the name of ev_test@localhost
|
|
CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123;
|
|
ERROR HY000: Event 'one_event' already exists
|
|
CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123;
|
|
Warnings:
|
|
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
|
|
CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123;
|
|
Warnings:
|
|
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
|
|
"Now we should see 3 events:";
|
|
SHOW EVENTS;
|
|
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
|
|
events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
"This should show us only 2 events:";
|
|
SHOW EVENTS LIKE 't%event';
|
|
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
|
|
events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
"This should show us no events:";
|
|
SHOW EVENTS FROM test LIKE '%';
|
|
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
|
|
connection default;
|
|
GRANT EVENT ON events_test2.* TO ev_test@localhost;
|
|
connection ev_con1;
|
|
USE events_test2;
|
|
CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42;
|
|
Warnings:
|
|
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
|
|
connection default;
|
|
USE events_test;
|
|
"We should see 4 events : one_event, two_event, three_event & four_event"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
|
|
def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
|
|
def events_test2 four_event ev_test@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
|
|
DROP DATABASE events_test2;
|
|
"We should see 3 events : one_event, two_event, three_event"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
|
|
def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
|
|
connection default;
|
|
CREATE DATABASE events_test2;
|
|
USE events_test2;
|
|
CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42;
|
|
Warnings:
|
|
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
|
|
connection ev_con1;
|
|
"Should see 4 events - one, two, three & five"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
|
|
def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
|
|
def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
|
|
connection default;
|
|
REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
|
|
connection ev_con1;
|
|
USE test;
|
|
"Should see 3 events - one, two & three"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
|
|
def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
|
|
"Let's test ALTER EVENT which changes the definer"
|
|
USE events_test;
|
|
ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND;
|
|
"The definer should be ev_test@localhost"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE
|
|
connection default;
|
|
USE events_test;
|
|
ALTER EVENT one_event COMMENT "comment";
|
|
connection ev_con1;
|
|
"The definer should be root@localhost"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment
|
|
ALTER EVENT one_event DO SELECT 12;
|
|
"The definer should be ev_test@localhost"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test one_event ev_test@localhost SQL SELECT 12 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment
|
|
connection default;
|
|
"make the definer again root@localhost"
|
|
ALTER EVENT one_event COMMENT "new comment";
|
|
connection ev_con1;
|
|
"test DROP by another user"
|
|
DROP EVENT one_event;
|
|
connection default;
|
|
"One event should not be there"
|
|
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
|
|
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
|
|
def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event
|
|
def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event
|
|
def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE
|
|
disconnect ev_con1;
|
|
connection default;
|
|
DROP USER ev_test@localhost;
|
|
DROP DATABASE events_test2;
|
|
DROP DATABASE events_test;
|