1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
DROP DATABASE IF EXISTS test1;
5
drop table if exists tb1 ;
14
f16 tinyint unsigned zerofill,
16
f18 smallint unsigned,
17
f19 smallint zerofill,
18
f20 smallint unsigned zerofill,
20
f22 mediumint unsigned,
21
f23 mediumint zerofill,
22
f24 mediumint unsigned zerofill,
26
f28 int unsigned zerofill,
30
f32 bigint unsigned zerofill,
31
f33 decimal not null DEFAULT 9.9,
32
f34 decimal unsigned not null DEFAULT 9.9,
33
f35 decimal zerofill not null DEFAULT 9.9,
34
f36 decimal unsigned zerofill not null DEFAULT 9.9,
35
f37 decimal (0) not null DEFAULT 9.9,
36
f38 decimal (64) not null DEFAULT 9.9,
37
f39 decimal (0) unsigned not null DEFAULT 9.9,
38
f40 decimal (64) unsigned not null DEFAULT 9.9,
39
f41 decimal (0) zerofill not null DEFAULT 9.9,
40
f42 decimal (64) zerofill not null DEFAULT 9.9,
41
f43 decimal (0) unsigned zerofill not null DEFAULT 9.9,
42
f44 decimal (64) unsigned zerofill not null DEFAULT 9.9,
43
f45 decimal (0,0) not null DEFAULT 9.9,
44
f46 decimal (63,30) not null DEFAULT 9.9,
45
f47 decimal (0,0) unsigned not null DEFAULT 9.9,
46
f48 decimal (63,30) unsigned not null DEFAULT 9.9,
47
f49 decimal (0,0) zerofill not null DEFAULT 9.9,
48
f50 decimal (63,30) zerofill not null DEFAULT 9.9,
49
f51 decimal (0,0) unsigned zerofill not null DEFAULT 9.9,
50
f52 decimal (63,30) unsigned zerofill not null DEFAULT 9.9,
51
f53 numeric not null DEFAULT 99,
52
f54 numeric unsigned not null DEFAULT 99,
53
f55 numeric zerofill not null DEFAULT 99,
54
f56 numeric unsigned zerofill not null DEFAULT 99,
55
f57 numeric (0) not null DEFAULT 99,
56
f58 numeric (64) not null DEFAULT 99
59
Note 1265 Data truncated for column 'f33' at row 1
60
Note 1265 Data truncated for column 'f34' at row 1
61
Note 1265 Data truncated for column 'f35' at row 1
62
Note 1265 Data truncated for column 'f36' at row 1
63
Note 1265 Data truncated for column 'f37' at row 1
64
Note 1265 Data truncated for column 'f38' at row 1
65
Note 1265 Data truncated for column 'f39' at row 1
66
Note 1265 Data truncated for column 'f40' at row 1
67
Note 1265 Data truncated for column 'f41' at row 1
68
Note 1265 Data truncated for column 'f42' at row 1
69
Note 1265 Data truncated for column 'f43' at row 1
70
Note 1265 Data truncated for column 'f44' at row 1
71
Note 1265 Data truncated for column 'f45' at row 1
72
Note 1265 Data truncated for column 'f47' at row 1
73
Note 1265 Data truncated for column 'f49' at row 1
74
Note 1265 Data truncated for column 'f51' at row 1
75
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb1.txt' into table tb1 ;
76
drop table if exists tb2 ;
78
f59 numeric (0) unsigned,
79
f60 numeric (64) unsigned,
80
f61 numeric (0) zerofill,
81
f62 numeric (64) zerofill,
82
f63 numeric (0) unsigned zerofill,
83
f64 numeric (64) unsigned zerofill,
86
f67 numeric (0,0) unsigned,
87
f68 numeric (63,30) unsigned,
88
f69 numeric (0,0) zerofill,
89
f70 numeric (63,30) zerofill,
90
f71 numeric (0,0) unsigned zerofill,
91
f72 numeric (63,30) unsigned zerofill,
95
f76 real unsigned zerofill,
96
f77 double default 7.7,
97
f78 double unsigned default 7.7,
98
f79 double zerofill default 7.7,
99
f80 double unsigned zerofill default 8.8,
100
f81 float not null default 8.8,
101
f82 float unsigned not null default 8.8,
102
f83 float zerofill not null default 8.8,
103
f84 float unsigned zerofill not null default 8.8,
104
f85 float(0) not null default 8.8,
105
f86 float(23) not null default 8.8,
106
f87 float(0) unsigned not null default 8.8,
107
f88 float(23) unsigned not null default 8.8,
108
f89 float(0) zerofill not null default 8.8,
109
f90 float(23) zerofill not null default 8.8,
110
f91 float(0) unsigned zerofill not null default 8.8,
111
f92 float(23) unsigned zerofill not null default 8.8,
112
f93 float(24) not null default 8.8,
113
f94 float(53) not null default 8.8,
114
f95 float(24) unsigned not null default 8.8,
115
f96 float(53) unsigned not null default 8.8,
116
f97 float(24) zerofill not null default 8.8,
117
f98 float(53) zerofill not null default 8.8,
118
f99 float(24) unsigned zerofill not null default 8.8,
119
f100 float(53) unsigned zerofill not null default 8.8,
120
f101 date not null default '2000-01-01',
121
f102 time not null default 20,
122
f103 datetime not null default '2/2/2',
123
f104 timestamp not null default 20001231235959,
124
f105 year not null default 2000,
125
f106 year(3) not null default 2000,
126
f107 year(4) not null default 2000,
127
f108 enum("1enum","2enum") not null default "1enum",
128
f109 set("1set","2set") not null default "1set"
130
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb2.txt' into table tb2 ;
131
drop table if exists tb3;
133
f118 char not null DEFAULT 'a',
134
f119 char binary not null DEFAULT b'101',
135
f120 char ascii not null DEFAULT b'101',
138
f129 binary not null DEFAULT b'101',
139
f130 tinyint not null DEFAULT 99,
140
f131 tinyint unsigned not null DEFAULT 99,
141
f132 tinyint zerofill not null DEFAULT 99,
142
f133 tinyint unsigned zerofill not null DEFAULT 99,
143
f134 smallint not null DEFAULT 999,
144
f135 smallint unsigned not null DEFAULT 999,
145
f136 smallint zerofill not null DEFAULT 999,
146
f137 smallint unsigned zerofill not null DEFAULT 999,
147
f138 mediumint not null DEFAULT 9999,
148
f139 mediumint unsigned not null DEFAULT 9999,
149
f140 mediumint zerofill not null DEFAULT 9999,
150
f141 mediumint unsigned zerofill not null DEFAULT 9999,
151
f142 int not null DEFAULT 99999,
152
f143 int unsigned not null DEFAULT 99999,
153
f144 int zerofill not null DEFAULT 99999,
154
f145 int unsigned zerofill not null DEFAULT 99999,
155
f146 bigint not null DEFAULT 999999,
156
f147 bigint unsigned not null DEFAULT 999999,
157
f148 bigint zerofill not null DEFAULT 999999,
158
f149 bigint unsigned zerofill not null DEFAULT 999999,
159
f150 decimal not null DEFAULT 999.999,
160
f151 decimal unsigned not null DEFAULT 999.17,
161
f152 decimal zerofill not null DEFAULT 999.999,
162
f153 decimal unsigned zerofill,
165
f156 decimal (0) unsigned,
166
f157 decimal (64) unsigned,
167
f158 decimal (0) zerofill,
168
f159 decimal (64) zerofill,
169
f160 decimal (0) unsigned zerofill,
170
f161 decimal (64) unsigned zerofill,
172
f163 decimal (63,30),
173
f164 decimal (0,0) unsigned,
174
f165 decimal (63,30) unsigned,
175
f166 decimal (0,0) zerofill,
176
f167 decimal (63,30) zerofill,
177
f168 decimal (0,0) unsigned zerofill,
178
f169 decimal (63,30) unsigned zerofill,
180
f171 numeric unsigned,
181
f172 numeric zerofill,
182
f173 numeric unsigned zerofill,
187
Note 1265 Data truncated for column 'f150' at row 1
188
Note 1265 Data truncated for column 'f151' at row 1
189
Note 1265 Data truncated for column 'f152' at row 1
190
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb3.txt' into table tb3 ;
191
drop table if exists tb4 ;
193
f176 numeric (0) unsigned not null DEFAULT 9,
194
f177 numeric (64) unsigned not null DEFAULT 9,
195
f178 numeric (0) zerofill not null DEFAULT 9,
196
f179 numeric (64) zerofill not null DEFAULT 9,
197
f180 numeric (0) unsigned zerofill not null DEFAULT 9,
198
f181 numeric (64) unsigned zerofill not null DEFAULT 9,
199
f182 numeric (0,0) not null DEFAULT 9,
200
f183 numeric (63,30) not null DEFAULT 9,
201
f184 numeric (0,0) unsigned not null DEFAULT 9,
202
f185 numeric (63,30) unsigned not null DEFAULT 9,
203
f186 numeric (0,0) zerofill not null DEFAULT 9,
204
f187 numeric (63,30) zerofill not null DEFAULT 9,
205
f188 numeric (0,0) unsigned zerofill not null DEFAULT 9,
206
f189 numeric (63,30) unsigned zerofill not null DEFAULT 9,
207
f190 real not null DEFAULT 88.8,
208
f191 real unsigned not null DEFAULT 88.8,
209
f192 real zerofill not null DEFAULT 88.8,
210
f193 real unsigned zerofill not null DEFAULT 88.8,
211
f194 double not null DEFAULT 55.5,
212
f195 double unsigned not null DEFAULT 55.5,
213
f196 double zerofill not null DEFAULT 55.5,
214
f197 double unsigned zerofill not null DEFAULT 55.5,
218
f201 float unsigned zerofill,
221
f204 float(0) unsigned,
222
f205 float(23) unsigned,
223
f206 float(0) zerofill,
224
f207 float(23) zerofill,
225
f208 float(0) unsigned zerofill,
226
f209 float(23) unsigned zerofill,
229
f212 float(24) unsigned,
230
f213 float(53) unsigned,
231
f214 float(24) zerofill,
232
f215 float(53) zerofill,
233
f216 float(24) unsigned zerofill,
234
f217 float(53) unsigned zerofill,
242
f225 enum("1enum","2enum"),
243
f226 set("1set","2set"),
244
f236 char(95) unicode,
245
f241 char(255) unicode,
246
f237 char(130) binary,
247
f238 varchar(25000) binary,
249
f240 varchar(1200) unicode
251
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb4.txt' into table tb4 ;
253
drop table if exists tb2 ;
255
f59 numeric (0) unsigned,
256
f60 numeric (64) unsigned,
257
f61 numeric (0) zerofill,
258
f62 numeric (64) zerofill,
259
f63 numeric (0) unsigned zerofill,
260
f64 numeric (64) unsigned zerofill,
263
f67 numeric (0,0) unsigned,
264
f68 numeric (63,30) unsigned,
265
f69 numeric (0,0) zerofill,
266
f70 numeric (63,30) zerofill,
267
f71 numeric (0,0) unsigned zerofill,
268
f72 numeric (63,30) unsigned zerofill,
272
f76 real unsigned zerofill,
273
f77 double default 7.7,
274
f78 double unsigned default 7.7,
275
f79 double zerofill default 7.7,
276
f80 double unsigned zerofill default 8.8,
277
f81 float not null default 8.8,
278
f82 float unsigned not null default 8.8,
279
f83 float zerofill not null default 8.8,
280
f84 float unsigned zerofill not null default 8.8,
281
f85 float(0) not null default 8.8,
282
f86 float(23) not null default 8.8,
283
f87 float(0) unsigned not null default 8.8,
284
f88 float(23) unsigned not null default 8.8,
285
f89 float(0) zerofill not null default 8.8,
286
f90 float(23) zerofill not null default 8.8,
287
f91 float(0) unsigned zerofill not null default 8.8,
288
f92 float(23) unsigned zerofill not null default 8.8,
289
f93 float(24) not null default 8.8,
290
f94 float(53) not null default 8.8,
291
f95 float(24) unsigned not null default 8.8,
292
f96 float(53) unsigned not null default 8.8,
293
f97 float(24) zerofill not null default 8.8,
294
f98 float(53) zerofill not null default 8.8,
295
f99 float(24) unsigned zerofill not null default 8.8,
296
f100 float(53) unsigned zerofill not null default 8.8,
297
f101 date not null default '2000-01-01',
298
f102 time not null default 20,
299
f103 datetime not null default '2/2/2',
300
f104 timestamp not null default 20001231235959,
301
f105 year not null default 2000,
302
f106 year(3) not null default 2000,
303
f107 year(4) not null default 2000,
304
f108 enum("1enum","2enum") not null default "1enum",
305
f109 set("1set","2set") not null default "1set"
307
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb2.txt' into table tb2 ;
310
DROP TABLE IF EXISTS t1, t2, t4, t10, t11;
311
CREATE TABLE t1 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
312
CREATE TABLE t2 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
313
CREATE TABLE t4 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
314
CREATE TABLE t10 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
315
CREATE TABLE t11 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
316
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t1;
317
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t2;
318
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t4;
319
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t10;
320
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t11;
321
drop TABLE if exists t3;
322
CREATE TABLE t3 (f1 char(20), f2 char(20), f3 integer) ENGINE = MEMORY;
323
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' INTO TABLE t3;
324
drop database if exists test4;
325
CREATE database test4;
327
CREATE TABLE t6 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = MEMORY;
328
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t6;
330
drop TABLE if exists t7, t8;
331
CREATE TABLE t7 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = MEMORY;
332
CREATE TABLE t8 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = MEMORY;
333
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t7;
335
Warning 1265 Data truncated for column 'f3' at row 1
336
Warning 1265 Data truncated for column 'f3' at row 2
337
Warning 1265 Data truncated for column 'f3' at row 3
338
Warning 1265 Data truncated for column 'f3' at row 4
339
Warning 1265 Data truncated for column 'f3' at row 5
340
Warning 1265 Data truncated for column 'f3' at row 6
341
Warning 1265 Data truncated for column 'f3' at row 7
342
Warning 1265 Data truncated for column 'f3' at row 8
343
Warning 1265 Data truncated for column 'f3' at row 9
344
Warning 1265 Data truncated for column 'f3' at row 10
345
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t8;
347
Warning 1265 Data truncated for column 'f3' at row 1
348
Warning 1265 Data truncated for column 'f3' at row 2
349
Warning 1265 Data truncated for column 'f3' at row 3
350
Warning 1265 Data truncated for column 'f3' at row 4
351
Warning 1265 Data truncated for column 'f3' at row 5
352
Warning 1265 Data truncated for column 'f3' at row 6
353
Warning 1265 Data truncated for column 'f3' at row 7
354
Warning 1265 Data truncated for column 'f3' at row 8
355
Warning 1265 Data truncated for column 'f3' at row 9
356
Warning 1265 Data truncated for column 'f3' at row 10
357
drop TABLE if exists t9;
358
CREATE TABLE t9 (f1 int, f2 char(25), f3 int) ENGINE = MEMORY;
359
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' INTO TABLE t9;
360
DROP DATABASE IF EXISTS db_datadict;
361
CREATE DATABASE db_datadict;
364
IF(INSTR(table_comment,'InnoDB free') = 0
365
AND INSTR(table_comment,'number_of_replicas') = 0,
366
LENGTH(table_comment),
367
INSTR(table_comment,'InnoDB free')
368
+ INSTR(table_comment,'number_of_replicas') - 1))
370
'-----------------------------------------------------' AS "Separator"
371
FROM information_schema.tables
372
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
373
ORDER BY table_schema,table_name;
377
TABLE_TYPE BASE TABLE
384
MAX_DATA_LENGTH #MDL#
391
TABLE_COLLATION latin1_swedish_ci
396
Separator -----------------------------------------------------
400
TABLE_TYPE BASE TABLE
407
MAX_DATA_LENGTH #MDL#
414
TABLE_COLLATION latin1_swedish_ci
419
Separator -----------------------------------------------------
423
TABLE_TYPE BASE TABLE
430
MAX_DATA_LENGTH #MDL#
437
TABLE_COLLATION latin1_swedish_ci
442
Separator -----------------------------------------------------
446
TABLE_TYPE BASE TABLE
453
MAX_DATA_LENGTH #MDL#
460
TABLE_COLLATION latin1_swedish_ci
465
Separator -----------------------------------------------------
469
TABLE_TYPE BASE TABLE
476
MAX_DATA_LENGTH #MDL#
483
TABLE_COLLATION latin1_swedish_ci
488
Separator -----------------------------------------------------
492
TABLE_TYPE BASE TABLE
499
MAX_DATA_LENGTH #MDL#
506
TABLE_COLLATION latin1_swedish_ci
511
Separator -----------------------------------------------------
515
TABLE_TYPE BASE TABLE
522
MAX_DATA_LENGTH #MDL#
529
TABLE_COLLATION latin1_swedish_ci
534
Separator -----------------------------------------------------
538
TABLE_TYPE BASE TABLE
545
MAX_DATA_LENGTH #MDL#
552
TABLE_COLLATION latin1_swedish_ci
557
Separator -----------------------------------------------------
561
TABLE_TYPE BASE TABLE
568
MAX_DATA_LENGTH #MDL#
575
TABLE_COLLATION latin1_swedish_ci
580
Separator -----------------------------------------------------
584
TABLE_TYPE BASE TABLE
591
MAX_DATA_LENGTH #MDL#
598
TABLE_COLLATION latin1_swedish_ci
603
Separator -----------------------------------------------------
607
TABLE_TYPE BASE TABLE
614
MAX_DATA_LENGTH #MDL#
621
TABLE_COLLATION latin1_swedish_ci
626
Separator -----------------------------------------------------
630
TABLE_TYPE BASE TABLE
637
MAX_DATA_LENGTH #MDL#
644
TABLE_COLLATION latin1_swedish_ci
649
Separator -----------------------------------------------------
653
TABLE_TYPE BASE TABLE
660
MAX_DATA_LENGTH #MDL#
667
TABLE_COLLATION latin1_swedish_ci
672
Separator -----------------------------------------------------
676
TABLE_TYPE BASE TABLE
683
MAX_DATA_LENGTH #MDL#
690
TABLE_COLLATION latin1_swedish_ci
695
Separator -----------------------------------------------------
699
TABLE_TYPE BASE TABLE
706
MAX_DATA_LENGTH #MDL#
713
TABLE_COLLATION latin1_swedish_ci
718
Separator -----------------------------------------------------
719
DROP USER testuser1@localhost;
720
CREATE USER testuser1@localhost;
721
GRANT SELECT ON db_datadict.* TO testuser1@localhost;
722
# Establish connection testuser1 (user=testuser1)
725
IF(INSTR(table_comment,'InnoDB free') = 0
726
AND INSTR(table_comment,'number_of_replicas') = 0,
727
LENGTH(table_comment),
728
INSTR(table_comment,'InnoDB free')
729
+ INSTR(table_comment,'number_of_replicas') - 1))
731
'-----------------------------------------------------' AS "Separator"
732
FROM information_schema.tables
733
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
734
ORDER BY table_schema,table_name;
738
TABLE_TYPE BASE TABLE
745
MAX_DATA_LENGTH #MDL#
752
TABLE_COLLATION latin1_swedish_ci
757
Separator -----------------------------------------------------
761
TABLE_TYPE BASE TABLE
768
MAX_DATA_LENGTH #MDL#
775
TABLE_COLLATION latin1_swedish_ci
780
Separator -----------------------------------------------------
784
TABLE_TYPE BASE TABLE
791
MAX_DATA_LENGTH #MDL#
798
TABLE_COLLATION latin1_swedish_ci
803
Separator -----------------------------------------------------
807
TABLE_TYPE BASE TABLE
814
MAX_DATA_LENGTH #MDL#
821
TABLE_COLLATION latin1_swedish_ci
826
Separator -----------------------------------------------------
830
TABLE_TYPE BASE TABLE
837
MAX_DATA_LENGTH #MDL#
844
TABLE_COLLATION latin1_swedish_ci
849
Separator -----------------------------------------------------
853
TABLE_TYPE BASE TABLE
860
MAX_DATA_LENGTH #MDL#
867
TABLE_COLLATION latin1_swedish_ci
872
Separator -----------------------------------------------------
876
TABLE_TYPE BASE TABLE
883
MAX_DATA_LENGTH #MDL#
890
TABLE_COLLATION latin1_swedish_ci
895
Separator -----------------------------------------------------
899
TABLE_TYPE BASE TABLE
906
MAX_DATA_LENGTH #MDL#
913
TABLE_COLLATION latin1_swedish_ci
918
Separator -----------------------------------------------------
922
TABLE_TYPE BASE TABLE
929
MAX_DATA_LENGTH #MDL#
936
TABLE_COLLATION latin1_swedish_ci
941
Separator -----------------------------------------------------
945
TABLE_TYPE BASE TABLE
952
MAX_DATA_LENGTH #MDL#
959
TABLE_COLLATION latin1_swedish_ci
964
Separator -----------------------------------------------------
968
TABLE_TYPE BASE TABLE
975
MAX_DATA_LENGTH #MDL#
982
TABLE_COLLATION latin1_swedish_ci
987
Separator -----------------------------------------------------
991
TABLE_TYPE BASE TABLE
998
MAX_DATA_LENGTH #MDL#
1005
TABLE_COLLATION latin1_swedish_ci
1010
Separator -----------------------------------------------------
1014
TABLE_TYPE BASE TABLE
1019
AVG_ROW_LENGTH #ARL#
1021
MAX_DATA_LENGTH #MDL#
1028
TABLE_COLLATION latin1_swedish_ci
1033
Separator -----------------------------------------------------
1034
# Switch to connection default and close connection testuser1
1035
DROP USER testuser1@localhost;
1036
DROP DATABASE db_datadict;
1037
DROP DATABASE test1;
1038
DROP DATABASE test4;
1046
DROP TABLE test.t10;
1047
DROP TABLE test.t11;
1048
DROP TABLE test.tb1;
1049
DROP TABLE test.tb2;
1050
DROP TABLE test.tb3;
1051
DROP TABLE test.tb4;