| Server IP : 104.21.38.3 / Your IP : 162.158.189.230 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/mysql-test/suite/funcs_1/r/ |
Upload File : |
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; CREATE DATABASE db_storedproc; CREATE DATABASE db_storedproc_1; USE db_storedproc; create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1; create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3; create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4; USE db_storedproc_1; create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9; create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10; create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11; SET sql_mode = default; Section 3.1.7 - SQL mode checks: -------------------------------------------------------------------------------- USE db_storedproc; Testcase 3.1.7.1: ----------------- Ensure that the sql_mode setting in effect at the time a stored procedure is created is the same setting under which the stored procedure runs when it is called/executed. -------------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS sp1; DROP TABLE IF EXISTS temp_tbl; DROP TABLE IF EXISTS result; CREATE TABLE temp_tbl (f1 tinyint); CREATE TABLE result (f1 text(200), f2 char(20)); set @@sql_mode='traditional'; SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE PROCEDURE sp1() BEGIN declare a tinyint; declare count_ int default 1; declare continue handler for sqlstate '22003' set count_=1000; SHOW VARIABLES LIKE 'sql_mode'; SELECT @@sql_mode into @cur_val_sql_mode; insert into temp_tbl values (1000); if count_ = 1000 THEN INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored'); ELSE INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored'); END if; END// SHOW CREATE PROCEDURE sp1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation sp1 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`() BEGIN declare a tinyint; declare count_ int default 1; declare continue handler for sqlstate '22003' set count_=1000; SHOW VARIABLES LIKE 'sql_mode'; SELECT @@sql_mode into @cur_val_sql_mode; insert into temp_tbl values (1000); if count_ = 1000 THEN INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored'); ELSE INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored'); END if; END latin1 latin1_swedish_ci latin1_swedish_ci set @@sql_mode=''; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode CALL sp1(); Variable_name Value sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SELECT * from result; f1 f2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION value restored SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode SELECT @@sql_mode; @@sql_mode SET @@sql_mode='TRADITIONAL'; Warnings: Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. DROP PROCEDURE sp1; DROP TABLE temp_tbl; DROP TABLE result; Testcase 3.1.7.2: ----------------- Ensure that if the sql_mode setting is changed when a stored procedure is run, that the original setting is restored as soon as the stored procedure execution is complete. -------------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS sp2; ... show initial value SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE PROCEDURE sp2() BEGIN SET @@sql_mode='MAXDB'; SHOW VARIABLES LIKE 'sql_mode'; END// SHOW CREATE PROCEDURE sp2; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation sp2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `sp2`() BEGIN SET @@sql_mode='MAXDB'; SHOW VARIABLES LIKE 'sql_mode'; END latin1 latin1_swedish_ci latin1_swedish_ci ... show value prior calling procedure SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ... call procedure that changes sql_mode CALL sp2(); Variable_name Value sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER ... check whether old value is re-set SHOW VARIABLES LIKE 'sql_mode'; Variable_name Value sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DROP PROCEDURE sp2; --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; . +++ END OF SCRIPT +++ --------------------------------------------------------------------------------