Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_set_gtid_purge_on_sp.test

raw
1 # ==== Purpose ====
2 #
3 # This test checks we can set GTID_PURGED from inside a SP even when
4 # is executed by a user with only privileges for execution.
5 #
6 # ==== Implementation ====
7 #
8 # 0. This test starts with 2 servers in a source->replica setup
9 # 1. We create a table on server 1 for check purposes and extract its id
10 # 2. On the replica create a storage procedure that uses the `root` user
11 # security context to SET GTID_PURGED
12 # 3. Create a user with only EXECUTE privileges.
13 # Make a connection with this user
14 # 4. Use the procedure to set GTID_PURGED.
15 # Check the value was set
16 # 5. Check replication is working as expected
17 # 6. Cleanup
18 #
19 # ==== References ====
20 #
21 # Bug#31571427 SET GTID_PURGED SHOULD BE EXECUTABLE FROM STORED ROUTINES
22 #
23
24 --source include/have_binlog_format_row.inc
25 --let $rpl_gtid_utils= 1
26 --source include/master-slave.inc
27
28 --echo #
29 --echo # 1. We create a table on server 1 for check purposes and extract its id
30
31 --source include/rpl_connection_master.inc
32
33 --let $source_uuid= `SELECT @@GLOBAL.SERVER_UUID`
34
35 CREATE TABLE t1 (a INT PRIMARY KEY);
36
37 --source include/sync_slave_sql_with_master.inc
38
39 --echo #
40 --echo # 2. On the replica create a storage procedure that uses the `root` user
41 --echo # security context to SET GTID_PURGED
42
43 DROP PROCEDURE IF EXISTS set_gtid_purged_sp;
44
45 DELIMITER |;
46 CREATE DEFINER = 'root'@'localhost' PROCEDURE set_gtid_purged_sp (IN gtid TEXT)
47 SQL SECURITY DEFINER
48 DETERMINISTIC
49 BEGIN
50 SET GLOBAL gtid_purged = gtid;
51 END|
52 DELIMITER ;|
53
54 --echo #
55 --echo # 3. Create a user with only EXECUTE privileges.
56 --echo # Make a connection with this user
57
58 CREATE USER 'no_priv_user'@localhost IDENTIFIED BY '';
59 GRANT EXECUTE ON test.* TO 'no_priv_user'@localhost;
60
61 --connect (no_priv_user_con, 127.0.0.1, no_priv_user, , test, $SLAVE_MYPORT,)
62
63 --let $rpl_connection_name= no_priv_user_con
64 --source include/rpl_connection.inc
65
66 --echo #
67 --echo # 4. Use the procedure to set GTID_PURGED.
68 --echo # Check the value was set
69
70 --replace_result $source_uuid SOURCE_UUID
71 --eval CALL set_gtid_purged_sp("$source_uuid:2");
72
73 --let $assert_cond= GTID_IS_EQUAL(@@GLOBAL.GTID_PURGED, "$source_uuid:2")
74 --let $assert_text= @@GLOBAL.GTID_PURGED has changed
75 --source include/assert.inc
76
77 --echo #
78 --echo # 5. Check replication is working as expected
79
80 --source include/rpl_connection_master.inc
81
82 INSERT INTO t1 values (2);
83 INSERT INTO t1 values (3);
84
85 --source include/sync_slave_sql_with_master.inc
86
87 --let $assert_text= Only one transaction was replicated
88 --let $assert_cond = COUNT(*)=1 FROM t1
89 --source include/assert.inc
90
91 --echo #
92 --echo # 6. Cleanup
93
94 --source include/rpl_connection_master.inc
95
96 DROP TABLE t1;
97
98 --source include/rpl_connection_slave.inc
99
100 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'no_priv_user'@localhost;
101 DROP USER 'no_priv_user'@localhost;
102 DROP PROCEDURE set_gtid_purged_sp;
103
104 --source include/rpl_end.inc