· 8 years ago · Aug 07, 2018, 02:36 AM
1CREATE DEFINER = 'thanhnt'@'192.168.6.31' PROCEDURE `test_out_of_memory`()
2 NOT DETERMINISTIC
3 CONTAINS SQL
4 SQL SECURITY DEFINER
5 COMMENT ''
6BEGIN
7
8SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
9
10#SET max tmp size
11SET @@tmp_table_size = 1073741824;
12
13drop table if exists tbllogging;
14create TABLE tbllogging(t TIMESTAMP, step int) ENGINE=INNODB;
15
16DROP TABLE IF EXISTS norep_campaigntmp;
17CREATE TEMPORARY TABLE `norep_campaigntmp` (
18 `campaignid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
19 `num` MEDIUMINT(9) NOT NULL DEFAULT '0'
20)ENGINE=INNODB;
21
22INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,0;
23DROP TABLE IF EXISTS norep_campaign_NB_tmp;
24CREATE TEMPORARY TABLE `norep_campaign_NB_tmp` (
25 `campaignid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
26 `num` MEDIUMINT(9) NOT NULL DEFAULT '0'
27)ENGINE=INNODB;
28
29INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,1;
30DROP TABLE IF EXISTS norep_zonebannertmp;
31CREATE TEMPORARY TABLE `norep_zonebannertmp` (
32 `zoneid` SMALLINT(6) NOT NULL DEFAULT '0' ,
33 `block` TINYINT(4) NOT NULL DEFAULT '0' ,
34 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
35 `bannerid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
36 KEY `location` (`location`),
37 KEY `bannerid` (`bannerid`),
38 KEY `block` (`block`),
39 KEY `zoneid` (`zoneid`)
40)ENGINE=INNODB;
41
42INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,2;
43DROP TABLE IF EXISTS norep_zonebannertmp_bk;
44CREATE TEMPORARY TABLE `norep_zonebannertmp_bk` (
45 `zoneid` SMALLINT(6) NOT NULL DEFAULT '0' ,
46 `block` TINYINT(4) NOT NULL DEFAULT '0' ,
47 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
48 `bannerid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
49 KEY `location` (`location`),
50 KEY `bannerid` (`bannerid`),
51 KEY `block` (`block`),
52 KEY `zoneid` (`zoneid`)
53)ENGINE=INNODB;
54
55INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,3;
56DROP TABLE IF EXISTS norep_zonebanner_delete_tmp;
57CREATE TEMPORARY TABLE `norep_zonebanner_delete_tmp` (
58 `zoneid` SMALLINT(6) NOT NULL DEFAULT '0' ,
59 `block` TINYINT(4) NOT NULL DEFAULT '0' ,
60 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
61 `bannerid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
62 KEY `location` (`location`),
63 KEY `bannerid` (`bannerid`),
64 KEY `block` (`block`),
65 KEY `zoneid` (`zoneid`)
66)ENGINE=INNODB;
67
68INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,4;
69DROP TABLE IF EXISTS norep_zonebanner_cur_tmp;
70CREATE TEMPORARY TABLE `norep_zonebanner_cur_tmp` (
71 `zoneid` SMALLINT(6) NOT NULL DEFAULT '0' ,
72 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
73 `bannerid` MEDIUMINT(9) NOT NULL DEFAULT '0' ,
74 KEY `location` (`location`),
75 KEY `bannerid` (`bannerid`),
76 KEY `zoneid` (`zoneid`)
77)ENGINE=INNODB;
78INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,5;
79DROP TABLE IF EXISTS norep_grouptmp;
80CREATE TEMPORARY TABLE `norep_grouptmp` (
81 `groupid` SMALLINT(6) NOT NULL DEFAULT '0' ,
82 `w` MEDIUMINT(9) NOT NULL DEFAULT '0'
83)ENGINE=INNODB;
84
85INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,6;
86DROP TABLE IF EXISTS norep_block_delete_tmp;
87CREATE TEMPORARY TABLE `norep_block_delete_tmp` (
88 `zoneid` INT(9) NOT NULL DEFAULT '0' ,
89 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
90 `block` TINYINT(4) NOT NULL DEFAULT '0' ,
91 KEY `zoneid` (`zoneid`),
92 KEY `location` (`location`),
93 KEY `block` (`block`)
94)ENGINE=INNODB;
95
96INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,7;
97DROP TABLE IF EXISTS norep_banner_channel_tmp;
98CREATE TEMPORARY TABLE `norep_banner_channel_tmp` (
99 `bannerid` INT(9) NOT NULL DEFAULT '0' ,
100 `channelid` INT(9) NOT NULL DEFAULT '0' ,
101 `location` TINYINT(4) NOT NULL DEFAULT '0',
102 KEY `channelid` (`channelid`)
103)ENGINE=INNODB;
104
105INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,8;
106DROP TABLE IF EXISTS norep_user_zone_tmp;
107CREATE TEMPORARY TABLE `norep_user_zone_tmp` (
108 `userid` INT(9) NOT NULL DEFAULT '0' ,
109 `zoneid` INT(9) NOT NULL DEFAULT '0' ,
110 `location` TINYINT(4) NOT NULL DEFAULT '0' ,
111 KEY `userid` (`userid`) ,
112 KEY `zoneid` (`zoneid`) ,
113 KEY `location` (`location`)
114)ENGINE=INNODB;
115
116INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,9;
117DROP TABLE IF EXISTS norep_user_zone_loc_tmp;
118CREATE TEMPORARY TABLE `norep_user_zone_loc_tmp` (
119 `userid` INT(9) NOT NULL DEFAULT '0' ,
120 `zoneid` INT(9) NOT NULL DEFAULT '0' ,
121 `loc` TINYINT(4) NOT NULL DEFAULT '0' ,
122 `num` TINYINT(4) NOT NULL DEFAULT '0'
123)ENGINE=INNODB;
124
125INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,10;
126DROP TABLE IF EXISTS norep_bannertmp_KH;
127CREATE TEMPORARY TABLE `norep_bannertmp_KH` (
128 `campaignid` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
129 `isexpire` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
130 `bannerid` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
131 KEY `bannerid` (`bannerid`) ,
132 KEY `campaignid` (`campaignid`)
133)ENGINE=INNODB;
134INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,11;
135DROP TABLE IF EXISTS norep_bannertmp_NB;
136CREATE TEMPORARY TABLE `norep_bannertmp_NB` (
137 `groupid` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
138 `campaignid` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
139 `bannerid` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
140 `isexpire` MEDIUMINT(6) NOT NULL DEFAULT '0' ,
141 KEY `bannerid` (`bannerid`) ,
142 KEY `campaignid` (`campaignid`)
143)ENGINE=INNODB;
144INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,12;
145DROP TABLE IF EXISTS norep_bannertmp1;
146CREATE TEMPORARY TABLE `norep_bannertmp1` (
147 `groupid` INT(9) NOT NULL DEFAULT '0' ,
148 `userid` INT(9) NOT NULL DEFAULT '0' ,
149 `campaignid` INT(9) NOT NULL DEFAULT '0' ,
150 `bannerid` INT(9) NOT NULL DEFAULT '0' ,
151 `location` TINYINT(4) ,
152 `typegroup` TINYINT(4) DEFAULT 0 ,
153 `w` INT DEFAULT 0 ,
154 KEY `campaignid` (`campaignid`) ,
155 KEY `bannerid` (`bannerid`) ,
156 KEY `groupid` (`groupid`) ,
157 KEY `userid` (`userid`) ,
158 KEY `location` (`location`) ,
159 KEY `typegroup` (`typegroup`)
160
161)ENGINE=INNODB;
162INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,13;
163
164DROP TABLE IF EXISTS norep_101_tmp;
165CREATE TEMPORARY TABLE `norep_101_tmp` (
166 `userid` INT ,
167 `bannerid` INT ,
168 `ctr` decimal(6,3) NOT NULL,
169 `views` INT ,
170 KEY `userid` (`userid`) ,
171 KEY `bannerid` (`bannerid`)
172)ENGINE=INNODB;
173
174INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,14;
175DROP TABLE IF EXISTS norep_banner_zone_in;
176CREATE TEMPORARY TABLE `norep_banner_zone_in` (
177 `channelid` INT ,
178 `zoneid` INT ,
179 `location` TINYINT(4) ,
180 `bannerid` INT ,
181 `CTR` DECIMAL(6,3) ,
182 `money` INT,
183 KEY `channelid` (`channelid`) ,
184 KEY `zoneid` (`zoneid`) ,
185 KEY `bannerid` (`bannerid`) ,
186 KEY `location` (`location`)
187)ENGINE=MYISAM;
188INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,15;
189DROP TABLE IF EXISTS norep_banner_zone_no_gen_tmp;
190CREATE TEMPORARY TABLE `norep_banner_zone_no_gen_tmp` (
191 `zoneid` INT ,
192 `location` TINYINT(4) ,
193 `bannerid` INT ,
194 `userid` INT ,
195 KEY `userid` (`userid`) ,
196 KEY `zoneid` (`zoneid`) ,
197 KEY `bannerid` (`bannerid`) ,
198 KEY `location` (`location`)
199)ENGINE=INNODB;
200
201INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,16;
202DROP TABLE IF EXISTS norep_banner_zone_no_gen_tmp1;
203CREATE TEMPORARY TABLE `norep_banner_zone_no_gen_tmp1` (
204 `zoneid` INT ,
205 `location` TINYINT(4) ,
206 `bannerid` INT ,
207 `userid` INT ,
208 KEY `userid` (`userid`) ,
209 KEY `zoneid` (`zoneid`) ,
210 KEY `bannerid` (`bannerid`) ,
211 KEY `location` (`location`)
212)ENGINE=INNODB;
213
214INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,17;
215DROP TABLE IF EXISTS norep_banner_zone_no_gen_today_tmp;
216CREATE TEMPORARY TABLE `norep_banner_zone_no_gen_today_tmp` (
217 `id` int(11) NOT NULL AUTO_INCREMENT,
218 `zoneid` INT ,
219 `location` TINYINT(4) ,
220 `bannerid` INT ,
221 `userid` INT ,
222 KEY `id` (`id`) ,
223 KEY `userid` (`userid`) ,
224 KEY `zoneid` (`zoneid`) ,
225 KEY `bannerid` (`bannerid`) ,
226 KEY `location` (`location`)
227)ENGINE=INNODB;
228
229INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,18;
230DROP TABLE IF EXISTS norep_banner_zone_no_gen_today_tmp1;
231CREATE TEMPORARY TABLE `norep_banner_zone_no_gen_today_tmp1` (
232 `id` int(11) NOT NULL,
233 `zoneid` INT ,
234 `location` TINYINT(4) ,
235 `bannerid` INT ,
236 `userid` INT ,
237 KEY `id` (`id`) ,
238 KEY `userid` (`userid`) ,
239 KEY `zoneid` (`zoneid`) ,
240 KEY `bannerid` (`bannerid`) ,
241 KEY `location` (`location`)
242)ENGINE=INNODB;
243
244
245INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,19;
246DROP TABLE IF EXISTS norep_ssv_tmp;
247CREATE TEMPORARY TABLE `norep_ssv_tmp` (
248 `id` int(11) NOT NULL AUTO_INCREMENT,
249 `userid` int(11) unsigned NOT NULL,
250 `bannerid` int(11) unsigned NOT NULL,
251 `money` int(11) unsigned NOT NULL,
252 `ctr` decimal(6,3) NOT NULL,
253 `createtime` datetime DEFAULT NULL,
254 `typegroup` tinyint(4) NOT NULL DEFAULT '0' ,
255 KEY `id` (`id`) ,
256 KEY `typegroup` (`typegroup`) ,
257 KEY `userid` (`userid`)
258) ENGINE=INNODB;
259INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,20;
260DROP TABLE IF EXISTS norep_ssv_tmp1;
261CREATE TEMPORARY TABLE `norep_ssv_tmp1` (
262 `id` int(11) NOT NULL,
263 `userid` int(11) unsigned NOT NULL,
264 `bannerid` int(11) unsigned NOT NULL,
265 `money` int(11) unsigned NOT NULL,
266 `ctr` decimal(6,3) NOT NULL,
267 `createtime` datetime DEFAULT NULL,
268 `typegroup` tinyint(4) NOT NULL DEFAULT '0' ,
269 KEY `typegroup` (`typegroup`) ,
270 KEY `userid` (`userid`)
271) ENGINE=INNODB;
272
273INSERT INTO tbllogging SELECT CURRENT_TIMESTAMP,21;
274
275SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
276INSERT INTO tbllogging select CURRENT_TIMESTAMP, 22;
277
278SELECT * FROM tbllogging;
279END;