sql_mode_default.test

################################# sql_mode_default.test ###############################
# Make NO_AUTO_CREATE_USER sql_mode behavior the default in 5.7                       #
#                                                                                     #
# Test for WL#8326                                                                    #
# Created : 2015-02-05                                                                #
# Author  : Lalit choudhary                                                           #
#######################################################################################

--source include/not_embedded.inc

call mtr.add_suppression(" *NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should .*");

SET sql_mode = default;
SELECT @@sql_mode;

# User creation using GRANT statement with default sql_mode
--error ER_PASSWORD_NO_MATCH
GRANT ALL ON *.* TO user2_wl8326@localhost;
SET sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
GRANT ALL ON *.* TO wl8326_user1@localhost;

# Cleanup
DROP USER wl8326_user1@localhost;

--echo
SET @@sql_mode='NO_ENGINE_SUBSTITUTION';
SELECT @@sql_mode;

# When the NO_AUTO_CREATE_USER sql mode remains unaffected by the set sql_mode query, no warning is produced
SET sql_mode = default;
SELECT @@sql_mode;
SET @@sql_mode='NO_AUTO_CREATE_USER';

# When the NO_AUTO_CREATE_USER sql mode affected by the set sql_mode query, warning is produced
SET sql_mode = '';
SELECT @@sql_mode;
SET @@sql_mode='NO_AUTO_CREATE_USER';
SELECT @@sql_mode;

--echo # Restarting server without NO_AUTO_CREATE_USER sql_mode

# 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

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

--echo # Restart server.
--exec echo "restart: --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES " > $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
--echo
# Search for perticular warning during server startup
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let SEARCH_PATTERN= \[Warning\] \'NO_AUTO_CREATE_USER\' sql mode was not set;
--source include/search_pattern_in_file.inc

SELECT @@sql_mode;

#  User creation using GRANT statement without NO_AUTO_CREATE_USER sql_mode
GRANT ALL ON *.* TO wl8326_grant@localhost;

# Cleanup
DROP USER wl8326_grant@localhost;

SET sql_mode = default;
SELECT @@sql_mode;
SET @@sql_mode='NO_AUTO_CREATE_USER';
SELECT @@sql_mode;
SET sql_mode = default;

--echo
--echo End of wl#8326 tests!
--echo

--echo #
--echo # Bug#29287785: PASSWORD IS NOT UPDATED WITHOUT ERRORS WHEN
--echo #               SQL MODE IS PAD_CHAR_TO_FULL_LENGTH
--echo #

CREATE USER 'user1'@'localhost';
SET sql_mode= 'PAD_CHAR_TO_FULL_LENGTH';
SET PASSWORD FOR 'user1'@'localhost'= PASSWORD('abc');
SELECT authentication_string FROM mysql.user WHERE user= 'user1';

#Cleanup
DROP USER 'user1'@'localhost';
SET sql_mode= DEFAULT;