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)
13
engine = <engine_to_be_tested>;
14
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16
engine = <engine_to_be_tested>;
17
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21
engine = <engine_to_be_tested>;
22
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
24
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25
engine = <engine_to_be_tested>;
26
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
28
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29
engine = <engine_to_be_tested>;
30
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
32
Warning 1265 Data truncated for column 'f3' at row 1
33
Warning 1265 Data truncated for column 'f3' at row 2
34
Warning 1265 Data truncated for column 'f3' at row 3
35
Warning 1265 Data truncated for column 'f3' at row 4
36
Warning 1265 Data truncated for column 'f3' at row 5
37
Warning 1265 Data truncated for column 'f3' at row 6
38
Warning 1265 Data truncated for column 'f3' at row 7
39
Warning 1265 Data truncated for column 'f3' at row 8
40
Warning 1265 Data truncated for column 'f3' at row 9
41
Warning 1265 Data truncated for column 'f3' at row 10
42
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43
engine = <engine_to_be_tested>;
44
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
46
Warning 1265 Data truncated for column 'f3' at row 1
47
Warning 1265 Data truncated for column 'f3' at row 2
48
Warning 1265 Data truncated for column 'f3' at row 3
49
Warning 1265 Data truncated for column 'f3' at row 4
50
Warning 1265 Data truncated for column 'f3' at row 5
51
Warning 1265 Data truncated for column 'f3' at row 6
52
Warning 1265 Data truncated for column 'f3' at row 7
53
Warning 1265 Data truncated for column 'f3' at row 8
54
Warning 1265 Data truncated for column 'f3' at row 9
55
Warning 1265 Data truncated for column 'f3' at row 10
56
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59
engine = <engine_to_be_tested>;
60
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62
engine = <engine_to_be_tested>;
63
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65
Section 3.1.7 - SQL mode checks:
66
--------------------------------------------------------------------------------
71
Ensure that the sql_mode setting in effect at the time a stored procedure is
72
created is the same setting under which the stored procedure runs when it is
74
--------------------------------------------------------------------------------
75
DROP PROCEDURE IF EXISTS sp1;
76
DROP TABLE IF EXISTS temp_tbl;
77
DROP TABLE IF EXISTS result;
78
CREATE TABLE temp_tbl (f1 tinyint);
79
CREATE TABLE result (f1 text(200), f2 char(20));
80
set @@sql_mode='traditional';
81
SHOW VARIABLES LIKE 'sql_mode';
83
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
84
CREATE PROCEDURE sp1()
87
declare count_ int default 1;
88
declare continue handler for sqlstate '22003' set count_=1000;
89
SHOW VARIABLES LIKE 'sql_mode';
90
SELECT @@sql_mode into @cur_val_sql_mode;
91
insert into temp_tbl values (1000);
93
INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
95
INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
98
SHOW CREATE PROCEDURE sp1;
99
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
100
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`()
103
declare count_ int default 1;
104
declare continue handler for sqlstate '22003' set count_=1000;
105
SHOW VARIABLES LIKE 'sql_mode';
106
SELECT @@sql_mode into @cur_val_sql_mode;
107
insert into temp_tbl values (1000);
108
if count_ = 1000 THEN
109
INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
111
INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
113
END latin1 latin1_swedish_ci latin1_swedish_ci
115
SHOW VARIABLES LIKE 'sql_mode';
120
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
121
SELECT * from result;
123
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
124
SHOW VARIABLES LIKE 'sql_mode';
130
SET @@sql_mode='TRADITIONAL';
137
Ensure that if the sql_mode setting is changed when a stored procedure is run,
138
that the original setting is restored as soon as the stored procedure execution
140
--------------------------------------------------------------------------------
141
DROP PROCEDURE IF EXISTS sp2;
142
... show initial value
143
SHOW VARIABLES LIKE 'sql_mode';
145
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
146
CREATE PROCEDURE sp2()
148
SET @@sql_mode='MAXDB';
149
SHOW VARIABLES LIKE 'sql_mode';
151
SHOW CREATE PROCEDURE sp2;
152
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
153
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`()
155
SET @@sql_mode='MAXDB';
156
SHOW VARIABLES LIKE 'sql_mode';
157
END latin1 latin1_swedish_ci latin1_swedish_ci
158
... show value prior calling procedure
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
162
... call procedure that changes sql_mode
165
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
166
... check whether old value is re-set
167
SHOW VARIABLES LIKE 'sql_mode';
169
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
172
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
173
--------------------------------------------------------------------------------
174
DROP DATABASE IF EXISTS db_storedproc;
175
DROP DATABASE IF EXISTS db_storedproc_1;
177
. +++ END OF SCRIPT +++
178
--------------------------------------------------------------------------------