2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
5
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6
--------------------------------------------------------------------------------
7
DROP DATABASE IF EXISTS db_storedproc;
8
DROP DATABASE IF EXISTS db_storedproc_1;
9
CREATE DATABASE db_storedproc;
10
CREATE DATABASE db_storedproc_1;
12
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
13
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
14
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
16
create table t3(f1 char(20),f2 char(20),f3 integer) engine = myisam;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
18
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
21
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
22
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
24
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = myisam;
25
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
27
Warning 1265 Data truncated for column 'f3' at row 1
28
Warning 1265 Data truncated for column 'f3' at row 2
29
Warning 1265 Data truncated for column 'f3' at row 3
30
Warning 1265 Data truncated for column 'f3' at row 4
31
Warning 1265 Data truncated for column 'f3' at row 5
32
Warning 1265 Data truncated for column 'f3' at row 6
33
Warning 1265 Data truncated for column 'f3' at row 7
34
Warning 1265 Data truncated for column 'f3' at row 8
35
Warning 1265 Data truncated for column 'f3' at row 9
36
Warning 1265 Data truncated for column 'f3' at row 10
37
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = myisam;
38
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
40
Warning 1265 Data truncated for column 'f3' at row 1
41
Warning 1265 Data truncated for column 'f3' at row 2
42
Warning 1265 Data truncated for column 'f3' at row 3
43
Warning 1265 Data truncated for column 'f3' at row 4
44
Warning 1265 Data truncated for column 'f3' at row 5
45
Warning 1265 Data truncated for column 'f3' at row 6
46
Warning 1265 Data truncated for column 'f3' at row 7
47
Warning 1265 Data truncated for column 'f3' at row 8
48
Warning 1265 Data truncated for column 'f3' at row 9
49
Warning 1265 Data truncated for column 'f3' at row 10
50
create table t9(f1 int, f2 char(25), f3 int) engine = myisam;
51
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
52
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
54
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
57
Section 3.1.7 - SQL mode checks:
58
--------------------------------------------------------------------------------
63
Ensure that the sql_mode setting in effect at the time a stored procedure is
64
created is the same setting under which the stored procedure runs when it is
66
--------------------------------------------------------------------------------
67
DROP PROCEDURE IF EXISTS sp1;
68
DROP TABLE IF EXISTS temp_tbl;
69
DROP TABLE IF EXISTS result;
70
CREATE TABLE temp_tbl (f1 tinyint);
71
CREATE TABLE result (f1 text(200), f2 char(20));
72
set @@sql_mode='traditional';
73
SHOW VARIABLES LIKE 'sql_mode';
75
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
76
CREATE PROCEDURE sp1()
79
declare count_ int default 1;
80
declare continue handler for sqlstate '22003' set count_=1000;
81
SHOW VARIABLES LIKE 'sql_mode';
82
SELECT @@sql_mode into @cur_val_sql_mode;
83
insert into temp_tbl values (1000);
85
INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
87
INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
90
SHOW CREATE PROCEDURE sp1;
91
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
92
sp1 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`()
95
declare count_ int default 1;
96
declare continue handler for sqlstate '22003' set count_=1000;
97
SHOW VARIABLES LIKE 'sql_mode';
98
SELECT @@sql_mode into @cur_val_sql_mode;
99
insert into temp_tbl values (1000);
100
if count_ = 1000 THEN
101
INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
103
INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
105
END latin1 latin1_swedish_ci latin1_swedish_ci
107
SHOW VARIABLES LIKE 'sql_mode';
112
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
113
SELECT * from result;
115
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER value restored
116
SHOW VARIABLES LIKE 'sql_mode';
122
SET @@sql_mode='TRADITIONAL';
129
Ensure that if the sql_mode setting is changed when a stored procedure is run,
130
that the original setting is restored as soon as the stored procedure execution
132
--------------------------------------------------------------------------------
133
DROP PROCEDURE IF EXISTS sp2;
134
... show initial value
135
SHOW VARIABLES LIKE 'sql_mode';
137
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
138
CREATE PROCEDURE sp2()
140
SET @@sql_mode='MAXDB';
141
SHOW VARIABLES LIKE 'sql_mode';
143
SHOW CREATE PROCEDURE sp2;
144
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
145
sp2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER CREATE DEFINER=`root`@`localhost` PROCEDURE `sp2`()
147
SET @@sql_mode='MAXDB';
148
SHOW VARIABLES LIKE 'sql_mode';
149
END latin1 latin1_swedish_ci latin1_swedish_ci
150
... show value prior calling procedure
151
SHOW VARIABLES LIKE 'sql_mode';
153
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
154
... call procedure that changes sql_mode
157
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
158
... check whether old value is re-set
159
SHOW VARIABLES LIKE 'sql_mode';
161
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
164
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
165
--------------------------------------------------------------------------------
166
DROP DATABASE IF EXISTS db_storedproc;
167
DROP DATABASE IF EXISTS db_storedproc_1;
169
. +++ END OF SCRIPT +++
170
--------------------------------------------------------------------------------