1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6[On Master] 7# 8# 9[On Master] 10# No users exist: 11call mtr.add_suppression('Corresponding entry in binary log used default authentication.*'); 12DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2; 13Warnings: 14Note 3162 User 'wl8540'@'nohost1' does not exist. 15Note 3162 User 'wl8540'@'nohost2' does not exist. 16SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 17COUNT(*) 180 19ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK; 20Warnings: 21Note 3162 User 'wl8540'@'nohost1' does not exist. 22Note 3162 User 'wl8540'@'nohost2' does not exist. 23SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 24COUNT(*) 250 26CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2; 27SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 28COUNT(*) 292 30include/sync_slave_sql_with_master.inc 31[On Slave] 32SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 33COUNT(*) 342 35[On Master] 36# All users exist: 37ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK; 38SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 39COUNT(*) 402 41CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2; 42Warnings: 43Note 3163 User 'wl8540'@'nohost1' already exists. 44Note 3163 User 'wl8540'@'nohost2' already exists. 45SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 46COUNT(*) 472 48DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2; 49SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 50COUNT(*) 510 52include/sync_slave_sql_with_master.inc 53[On Slave] 54SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 55COUNT(*) 560 57[On Master] 58CREATE USER wl8540@nohost1; 59DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2; 60Warnings: 61Note 3162 User 'wl8540'@'nohost2' does not exist. 62SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 63COUNT(*) 640 65ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK; 66Warnings: 67Note 3162 User 'wl8540'@'nohost1' does not exist. 68Note 3162 User 'wl8540'@'nohost2' does not exist. 69SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 70COUNT(*) 710 72CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2; 73SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 74COUNT(*) 752 76include/sync_slave_sql_with_master.inc 77[On Slave] 78SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540'; 79COUNT(*) 802 81CREATE USER IF NOT EXISTS slave_user1@localhost 82IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y'; 83CREATE USER IF NOT EXISTS slave_user2@localhost; 84[On MASTER] 85CREATE USER IF NOT EXISTS slave_user1@localhost 86IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y'; 87ALTER USER IF EXISTS slave_user2@localhost 88IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2; 89Warnings: 90Note 3162 User 'slave_user2'@'localhost' does not exist. 91DROP USER IF EXISTS slave_user2@localhost; 92Warnings: 93Note 3162 User 'slave_user2'@'localhost' does not exist. 94DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2, 95slave_user1@localhost,slave_user2@localhost; 96Warnings: 97Note 3162 User 'slave_user2'@'localhost' does not exist. 98FLUSH PRIVILEGES; 99include/sync_slave_sql_with_master.inc 100# Search warnings in the server log 101WL11144: Check that ALTER/CREATE/DROP USER IF [NOT] EXISTS will succesfully replicate in multi user scenarios 102[connection master] 103# All users exist on master, some on slave 104CREATE USER u1,u2; 105set @@session.sql_log_bin=0; 106CREATE USER u3,u4; 107set @@session.sql_log_bin=1; 108ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 109CREATE USER IF NOT EXISTS u1, u2, u3, u4; 110Warnings: 111Note 3163 User 'u1'@'%' already exists. 112Note 3163 User 'u2'@'%' already exists. 113Note 3163 User 'u3'@'%' already exists. 114Note 3163 User 'u4'@'%' already exists. 115DROP USER u1, u2, u3, u4; 116CREATE USER u1,u2; 117set @@session.sql_log_bin=0; 118CREATE USER u3,u4; 119set @@session.sql_log_bin=1; 120DROP USER IF EXISTS u1, u2, u3, u4; 121include/sync_slave_sql_with_master.inc 122# All users exist on master, none on slave 123[connection master] 124set @@session.sql_log_bin=0; 125CREATE USER u1,u2,u3,u4; 126set @@session.sql_log_bin=1; 127ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 128CREATE USER IF NOT EXISTS u1, u2, u3, u4; 129Warnings: 130Note 3163 User 'u1'@'%' already exists. 131Note 3163 User 'u2'@'%' already exists. 132Note 3163 User 'u3'@'%' already exists. 133Note 3163 User 'u4'@'%' already exists. 134DROP USER u1, u2, u3, u4; 135set @@session.sql_log_bin=0; 136CREATE USER u1,u2,u3,u4; 137set @@session.sql_log_bin=1; 138DROP USER IF EXISTS u1, u2, u3, u4; 139include/sync_slave_sql_with_master.inc 140# Some users exist on master, others on slave 141[connection master] 142set @@session.sql_log_bin=0; 143CREATE USER u3,u4; 144set @@session.sql_log_bin=1; 145include/sync_slave_sql_with_master.inc 146CREATE USER u1,u2; 147[connection master] 148ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 149Warnings: 150Note 3162 User 'u1'@'%' does not exist. 151Note 3162 User 'u2'@'%' does not exist. 152CREATE USER IF NOT EXISTS u1, u2, u3, u4; 153Warnings: 154Note 3163 User 'u3'@'%' already exists. 155Note 3163 User 'u4'@'%' already exists. 156set @@session.sql_log_bin=0; 157DROP USER u1,u2; 158set @@session.sql_log_bin=1; 159include/sync_slave_sql_with_master.inc 160DROP USER u3,u4; 161[connection master] 162DROP USER IF EXISTS u1, u2, u3, u4; 163Warnings: 164Note 3162 User 'u1'@'%' does not exist. 165Note 3162 User 'u2'@'%' does not exist. 166# Some users exist on master, all on slave 167include/sync_slave_sql_with_master.inc 168CREATE USER u1,u2; 169[connection master] 170CREATE USER u3,u4; 171ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 172Warnings: 173Note 3162 User 'u1'@'%' does not exist. 174Note 3162 User 'u2'@'%' does not exist. 175CREATE USER IF NOT EXISTS u1, u2, u3, u4; 176Warnings: 177Note 3163 User 'u3'@'%' already exists. 178Note 3163 User 'u4'@'%' already exists. 179set sql_log_bin=0; 180DROP USER u1,u2; 181set sql_log_bin=1; 182DROP USER IF EXISTS u1, u2, u3, u4; 183Warnings: 184Note 3162 User 'u1'@'%' does not exist. 185Note 3162 User 'u2'@'%' does not exist. 186include/sync_slave_sql_with_master.inc 187# Some users exist on master, none on slave 188[connection master] 189set @@session.sql_log_bin=0; 190CREATE USER u3,u4; 191set @@session.sql_log_bin=1; 192ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 193Warnings: 194Note 3162 User 'u1'@'%' does not exist. 195Note 3162 User 'u2'@'%' does not exist. 196CREATE USER IF NOT EXISTS u1, u2, u3, u4; 197Warnings: 198Note 3163 User 'u3'@'%' already exists. 199Note 3163 User 'u4'@'%' already exists. 200DROP USER u1, u2, u3, u4; 201set @@session.sql_log_bin=0; 202CREATE USER u3,u4; 203set @@session.sql_log_bin=1; 204DROP USER IF EXISTS u1, u2, u3, u4; 205Warnings: 206Note 3162 User 'u1'@'%' does not exist. 207Note 3162 User 'u2'@'%' does not exist. 208# No users exist on master, some on slave 209include/sync_slave_sql_with_master.inc 210CREATE USER u3,u4; 211[connection master] 212ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 213Warnings: 214Note 3162 User 'u1'@'%' does not exist. 215Note 3162 User 'u2'@'%' does not exist. 216Note 3162 User 'u3'@'%' does not exist. 217Note 3162 User 'u4'@'%' does not exist. 218CREATE USER IF NOT EXISTS u1, u2, u3, u4; 219DROP USER u1, u2; 220set @@session.sql_log_bin=0; 221DROP USER u3,u4; 222set @@session.sql_log_bin=1; 223DROP USER IF EXISTS u1, u2, u3, u4; 224Warnings: 225Note 3162 User 'u1'@'%' does not exist. 226Note 3162 User 'u2'@'%' does not exist. 227Note 3162 User 'u3'@'%' does not exist. 228Note 3162 User 'u4'@'%' does not exist. 229# No users exist on master, all on slave 230include/sync_slave_sql_with_master.inc 231CREATE USER u1,u2,u3,u4; 232[connection master] 233ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK; 234Warnings: 235Note 3162 User 'u1'@'%' does not exist. 236Note 3162 User 'u2'@'%' does not exist. 237Note 3162 User 'u3'@'%' does not exist. 238Note 3162 User 'u4'@'%' does not exist. 239CREATE USER IF NOT EXISTS u1, u2, u3, u4; 240set @@session.sql_log_bin=0; 241DROP USER u1, u2, u3, u4; 242set @@session.sql_log_bin=1; 243DROP USER IF EXISTS u1, u2, u3, u4; 244Warnings: 245Note 3162 User 'u1'@'%' does not exist. 246Note 3162 User 'u2'@'%' does not exist. 247Note 3162 User 'u3'@'%' does not exist. 248Note 3162 User 'u4'@'%' does not exist. 249CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY; 250ERROR HY000: Incorrect DAY value: '66666' 251ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY; 252ERROR HY000: Incorrect DAY value: '66666' 253CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY; 254ERROR 42000: Only integers allowed as number here near '66666888888888888888 DAY' at line 1 255ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY; 256ERROR 42000: Only integers allowed as number here near '66666888888888888888 DAY' at line 1 257include/assert.inc [assert that the above Event has not been added to binlog] 258include/assert.inc [assert that the GTID has not been generated] 259CREATE USER u3 IDENTIFIED WITH 'mysql_native_password'; 260CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'shsh',u2 IDENTIFIED WITH 'sha256_password',u3 IDENTIFIED WITH 'sha256_password'; 261ERROR HY000: Plugin 'shsh' is not loaded 262include/sync_slave_sql_with_master.inc 263include/assert.inc [assert that u3's auth_plugin has not changed] 264[connection master] 265DROP USER u2; 266CREATE USER u1; 267CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'shsh',u2 IDENTIFIED BY 'pass',u3 IDENTIFIED WITH 'sha256_password'; 268ERROR HY000: Plugin 'shsh' is not loaded 269include/sync_slave_sql_with_master.inc 270include/assert.inc [assert that u3's auth_plugin has not changed] 271[connection master] 272ALTER USER IF EXISTS u1 IDENTIFIED WITH 'shsh',u2 IDENTIFIED BY 'xyz',u3 IDENTIFIED WITH 'sha256_password'; 273ERROR HY000: Plugin 'shsh' is not loaded 274include/sync_slave_sql_with_master.inc 275include/assert.inc [assert that u3's auth_plugin has been changed by alter] 276[connection master] 277DROP USER u1,u2,u3; 278include/rpl_end.inc 279