Default page

View File Name : user_if_exists.test

# WL#8540
# Test behavior of IF [NOT] EXISTS clause
# in CREATE / ALTER / DROP USER commands.
#

# Requires privileges to be enabled
--source include/not_embedded.inc

# For already existing users in case of CREATE USER IF NOT EXISTS
# and for non-existing users in case of ALTER USER IF EXISTS,
# the warnings will be generated if default authentication plugin
# is used while writing the entries to binlog.
call mtr.add_suppression('Corresponding entry in binary log used default authentication.*');
# cleanup
DELETE FROM mysql.user WHERE user = 'wl8540';
FLUSH PRIVILEGES;

SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER wl8540@host1;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
--error ER_CANNOT_USER
CREATE USER wl8540@host1;
CREATE USER IF NOT EXISTS wl8540@host1;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER IF NOT EXISTS wl8540@host1, wl8540@host2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER IF NOT EXISTS wl8540@host1, wl8540@host2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';

SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
--error ER_CANNOT_USER
ALTER USER wl8540@host3 ACCOUNT LOCK;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
ALTER USER IF EXISTS wl8540@host3, wl8540@host4, wl8540@host1 ACCOUNT LOCK;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
ALTER USER IF EXISTS wl8540@host2 ACCOUNT LOCK;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';

SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
--error ER_CANNOT_USER
DROP USER wl8540@host3;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
DROP USER IF EXISTS wl8540@host3;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
DROP USER IF EXISTS wl8540@host3,wl8540@host2,wl8540@host4;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';

# No users exist:

DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';

# All users exist:

ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';

CREATE USER wl8540@nohost1;

# One of two users exist:

DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
SHOW WARNINGS;
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';

# Search for the warnings in the server log
let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let SEARCH_FILE= $server_log;
--echo # Search warnings in the server log

--let SEARCH_PATTERN= Following users were specified in CREATE USER IF NOT EXISTS
--source include/search_pattern_in_file.inc

# cleanup
DELETE FROM mysql.user WHERE user = 'wl8540';
FLUSH PRIVILEGES;

--echo
# QA test cases:

# CRAETE USER IF NOT EXISTS , ALTER USER IF EXISTS testing
# with --log-row=off (By default it's OFF).

#
--echo
CREATE USER user1@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';

CREATE USER IF NOT EXISTS user2@localhost
            IDENTIFIED WITH 'mysql_native_password'
            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';

query_vertical SELECT User,plugin,authentication_string,ssl_type,
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';

CREATE USER IF NOT EXISTS user2@localhost
            IDENTIFIED WITH 'sha256_password';
query_vertical SELECT User,plugin,authentication_string,ssl_type,
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';

ALTER USER IF EXISTS user2@localhost
IDENTIFIED WITH 'mysql_native_password'
            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';

query_vertical SELECT User,plugin,authentication_string,ssl_type,
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';

# CREATE USER IF NOT EXISTS statements with plain-text passwords will work
CREATE USER IF NOT EXISTS user1@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';

# Operation CREATE USER works but triggers a warning
CREATE USER IF NOT EXISTS ne_user1@localhost,user1@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';

# Operation ALTER USER works with a password too
ALTER USER IF EXISTS ne_user2@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
# Operation ALTER USER works with a password too
ALTER USER IF EXISTS user1@localhost,ne_user3@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';

# Cleanup
DROP USER IF EXISTS user1@localhost,user2@localhost,ne_user1@localhost,
                    ne_user2@localhost,ne_user3@localhost;

--echo
# CRAETE USER IF NOT EXISTS , ALTER USER IF EXISTS testing
# with --log-row=ON
--echo # Restarting the server with --log-row ON

# Write file to make mysql-test-run.pl wait for the server to stop.
let $expect_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
--exec echo "wait" > $expect_file

# Request shutdown
--send_shutdown
#--sleep 1
# Call script that will poll the server waiting for it to disapear
--source include/wait_until_disconnected.inc

--echo # Restart server.
--exec echo "restart:--log-raw=ON " > $expect_file

# Turn on reconnect
--enable_reconnect

# Call script that will poll the server waiting for it to be back online again
--source include/wait_until_connected_again.inc

# CREATE USER IF NOT EXISTS statements with plain-text passwords should not
# trigger an error if the user does exist and --log_raw=ON.
CREATE USER user1@localhost
            IDENTIFIED WITH 'mysql_native_password';

CREATE USER IF NOT EXISTS user2@localhost
            IDENTIFIED WITH 'mysql_native_password'
            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';

CREATE USER IF NOT EXISTS user1@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';

CREATE USER IF NOT EXISTS ne_user1@localhost,user1@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';

# ALTER USER IF EXISTS statements with plain-text passwords should not trigger
# an error if the user does not exist and --log_raw=ON
ALTER USER IF EXISTS ne_user2@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
ALTER USER IF EXISTS user1@localhost,ne_user3@localhost
            IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';

# Cleanup
DROP USER IF EXISTS user1@localhost,user2@localhost,ne_user1@localhost,
                    ne_user2@localhost,ne_user3@localhost;

--echo #
--echo # Bug #21807286: "CREATE USER IF NOT EXISTS" REPORTS AN ERROR
--echo #

CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'xyz';
--echo # Must not fail but return a warning
CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'xyz';
DROP USER b21807286@localhost;
--echo # Must not fail but return a warning
ALTER USER IF EXISTS b21807286@localhost IDENTIFIED BY 'xyz';

# Search for the warnings in the server log
let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let SEARCH_FILE= $server_log;
--echo # Search warnings in the server log

--let SEARCH_PATTERN= Following users were specified in CREATE USER IF NOT EXISTS
--source include/search_pattern_in_file.inc

--let SEARCH_PATTERN= Following users were specified in ALTER USER IF EXISTS
--source include/search_pattern_in_file.inc

--echo # End of 5.7 tests