1
DROP DATABASE IF EXISTS test1;
4
drop table if exists tb1 ;
21
f16 tinyint unsigned zerofill,
23
f18 smallint unsigned,
24
f19 smallint zerofill,
25
f20 smallint unsigned zerofill,
27
f22 mediumint unsigned,
28
f23 mediumint zerofill,
29
f24 mediumint unsigned zerofill,
33
f28 int unsigned zerofill,
37
f32 bigint unsigned zerofill,
41
f36 decimal unsigned zerofill not null DEFAULT 9.9,
42
f37 decimal (0) not null DEFAULT 9.9,
43
f38 decimal (64) not null DEFAULT 9.9,
44
f39 decimal (0) unsigned not null DEFAULT 9.9,
45
f40 decimal (64) unsigned not null DEFAULT 9.9,
46
f41 decimal (0) zerofill not null DEFAULT 9.9,
47
f42 decimal (64) zerofill not null DEFAULT 9.9,
48
f43 decimal (0) unsigned zerofill not null DEFAULT 9.9,
49
f44 decimal (64) unsigned zerofill not null DEFAULT 9.9,
50
f45 decimal (0,0) not null DEFAULT 9.9,
51
f46 decimal (63,30) not null DEFAULT 9.9,
52
f47 decimal (0,0) unsigned not null DEFAULT 9.9,
53
f48 decimal (63,30) unsigned not null DEFAULT 9.9,
54
f49 decimal (0,0) zerofill not null DEFAULT 9.9,
55
f50 decimal (63,30) zerofill not null DEFAULT 9.9,
56
f51 decimal (0,0) unsigned zerofill not null DEFAULT 9.9,
57
f52 decimal (63,30) unsigned zerofill not null DEFAULT 9.9,
58
f53 numeric not null DEFAULT 99,
59
f54 numeric unsigned not null DEFAULT 99,
60
f55 numeric zerofill not null DEFAULT 99,
61
f56 numeric unsigned zerofill not null DEFAULT 99,
62
f57 numeric (0) not null DEFAULT 99,
63
f58 numeric (64) not null DEFAULT 99
66
Note 1265 Data truncated for column 'f36' at row 1
67
Note 1265 Data truncated for column 'f37' at row 1
68
Note 1265 Data truncated for column 'f38' at row 1
69
Note 1265 Data truncated for column 'f39' at row 1
70
Note 1265 Data truncated for column 'f40' at row 1
71
Note 1265 Data truncated for column 'f41' at row 1
72
Note 1265 Data truncated for column 'f42' at row 1
73
Note 1265 Data truncated for column 'f43' at row 1
74
Note 1265 Data truncated for column 'f44' at row 1
75
Note 1265 Data truncated for column 'f45' at row 1
76
Note 1265 Data truncated for column 'f47' at row 1
77
Note 1265 Data truncated for column 'f49' at row 1
78
Note 1265 Data truncated for column 'f51' at row 1
79
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb1.txt' into table tb1 ;
80
drop table if exists tb2 ;
82
f59 numeric (0) unsigned,
83
f60 numeric (64) unsigned,
84
f61 numeric (0) zerofill,
85
f62 numeric (64) zerofill,
86
f63 numeric (0) unsigned zerofill,
87
f64 numeric (64) unsigned zerofill,
90
f67 numeric (0,0) unsigned,
91
f68 numeric (63,30) unsigned,
92
f69 numeric (0,0) zerofill,
93
f70 numeric (63,30) zerofill,
94
f71 numeric (0,0) unsigned zerofill,
95
f72 numeric (63,30) unsigned zerofill,
99
f76 real unsigned zerofill,
100
f77 double default 7.7,
101
f78 double unsigned default 7.7,
102
f79 double zerofill default 7.7,
103
f80 double unsigned zerofill default 8.8,
104
f81 float not null default 8.8,
105
f82 float unsigned not null default 8.8,
106
f83 float zerofill not null default 8.8,
107
f84 float unsigned zerofill not null default 8.8,
108
f85 float(0) not null default 8.8,
109
f86 float(23) not null default 8.8,
110
f87 float(0) unsigned not null default 8.8,
111
f88 float(23) unsigned not null default 8.8,
112
f89 float(0) zerofill not null default 8.8,
113
f90 float(23) zerofill not null default 8.8,
114
f91 float(0) unsigned zerofill not null default 8.8,
115
f92 float(23) unsigned zerofill not null default 8.8,
116
f93 float(24) not null default 8.8,
117
f94 float(53) not null default 8.8,
118
f95 float(24) unsigned not null default 8.8,
119
f96 float(53) unsigned not null default 8.8,
120
f97 float(24) zerofill not null default 8.8,
121
f98 float(53) zerofill not null default 8.8,
122
f99 float(24) unsigned zerofill not null default 8.8,
123
f100 float(53) unsigned zerofill not null default 8.8,
124
f101 date not null default '2000-01-01',
125
f102 time not null default 20,
126
f103 datetime not null default '2/2/2',
127
f104 timestamp not null default 20001231235959,
128
f105 year not null default 2000,
129
f106 year(3) not null default 2000,
130
f107 year(4) not null default 2000,
131
f108 enum("1enum","2enum") not null default "1enum",
132
f109 set("1set","2set") not null default "1set"
134
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb2.txt' into table tb2 ;
135
drop table if exists tb3 ;
137
f118 char not null DEFAULT 'a',
138
f119 char binary not null DEFAULT b'101',
139
f120 char ascii not null DEFAULT b'101',
143
f124 longtext unicode,
148
f129 binary not null DEFAULT b'101',
149
f130 tinyint not null DEFAULT 99,
150
f131 tinyint unsigned not null DEFAULT 99,
151
f132 tinyint zerofill not null DEFAULT 99,
152
f133 tinyint unsigned zerofill not null DEFAULT 99,
153
f134 smallint not null DEFAULT 999,
154
f135 smallint unsigned not null DEFAULT 999,
155
f136 smallint zerofill not null DEFAULT 999,
156
f137 smallint unsigned zerofill not null DEFAULT 999,
157
f138 mediumint not null DEFAULT 9999,
158
f139 mediumint unsigned not null DEFAULT 9999,
159
f140 mediumint zerofill not null DEFAULT 9999,
160
f141 mediumint unsigned zerofill not null DEFAULT 9999,
161
f142 int not null DEFAULT 99999,
162
f143 int unsigned not null DEFAULT 99999,
163
f144 int zerofill not null DEFAULT 99999,
164
f145 int unsigned zerofill not null DEFAULT 99999,
165
f146 bigint not null DEFAULT 999999,
166
f147 bigint unsigned not null DEFAULT 999999,
167
f148 bigint zerofill not null DEFAULT 999999,
168
f149 bigint unsigned zerofill not null DEFAULT 999999,
169
f150 decimal not null DEFAULT 999.999,
170
f151 decimal unsigned not null DEFAULT 999.17,
171
f152 decimal zerofill not null DEFAULT 999.999,
172
f153 decimal unsigned zerofill,
175
f156 decimal (0) unsigned,
176
f157 decimal (64) unsigned,
177
f158 decimal (0) zerofill,
178
f159 decimal (64) zerofill,
179
f160 decimal (0) unsigned zerofill,
180
f161 decimal (64) unsigned zerofill,
182
f163 decimal (63,30),
183
f164 decimal (0,0) unsigned,
184
f165 decimal (63,30) unsigned,
185
f166 decimal (0,0) zerofill,
186
f167 decimal (63,30) zerofill,
187
f168 decimal (0,0) unsigned zerofill,
188
f169 decimal (63,30) unsigned zerofill,
190
f171 numeric unsigned,
191
f172 numeric zerofill,
192
f173 numeric unsigned zerofill,
197
Note 1265 Data truncated for column 'f150' at row 1
198
Note 1265 Data truncated for column 'f151' at row 1
199
Note 1265 Data truncated for column 'f152' at row 1
200
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb3.txt' into table tb3 ;
201
drop table if exists tb4;
203
f176 numeric (0) unsigned not null DEFAULT 9,
204
f177 numeric (64) unsigned not null DEFAULT 9,
205
f178 numeric (0) zerofill not null DEFAULT 9,
206
f179 numeric (64) zerofill not null DEFAULT 9,
207
f180 numeric (0) unsigned zerofill not null DEFAULT 9,
208
f181 numeric (64) unsigned zerofill not null DEFAULT 9,
209
f182 numeric (0,0) not null DEFAULT 9,
210
f183 numeric (63,30) not null DEFAULT 9,
211
f184 numeric (0,0) unsigned not null DEFAULT 9,
212
f185 numeric (63,30) unsigned not null DEFAULT 9,
213
f186 numeric (0,0) zerofill not null DEFAULT 9,
214
f187 numeric (63,30) zerofill not null DEFAULT 9,
215
f188 numeric (0,0) unsigned zerofill not null DEFAULT 9,
216
f189 numeric (63,30) unsigned zerofill not null DEFAULT 9,
217
f190 real not null DEFAULT 88.8,
218
f191 real unsigned not null DEFAULT 88.8,
219
f192 real zerofill not null DEFAULT 88.8,
220
f193 real unsigned zerofill not null DEFAULT 88.8,
221
f194 double not null DEFAULT 55.5,
222
f195 double unsigned not null DEFAULT 55.5,
223
f196 double zerofill not null DEFAULT 55.5,
224
f197 double unsigned zerofill not null DEFAULT 55.5,
228
f201 float unsigned zerofill,
231
f204 float(0) unsigned,
232
f205 float(23) unsigned,
233
f206 float(0) zerofill,
234
f207 float(23) zerofill,
235
f208 float(0) unsigned zerofill,
236
f209 float(23) unsigned zerofill,
239
f212 float(24) unsigned,
240
f213 float(53) unsigned,
241
f214 float(24) zerofill,
242
f215 float(53) zerofill,
243
f216 float(24) unsigned zerofill,
244
f217 float(53) unsigned zerofill,
252
f225 enum("1enum","2enum"),
253
f226 set("1set","2set"),
254
f235 char(0) unicode,
256
f237 char(255) ascii,
258
f239 varchar(20000) binary,
259
f240 varchar(2000) unicode,
260
f241 char(100) unicode
262
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb4.txt' into table tb4 ;
264
drop table if exists tb2 ;
266
f59 numeric (0) unsigned,
267
f60 numeric (64) unsigned,
268
f61 numeric (0) zerofill,
269
f62 numeric (64) zerofill,
270
f63 numeric (0) unsigned zerofill,
271
f64 numeric (64) unsigned zerofill,
274
f67 numeric (0,0) unsigned,
275
f68 numeric (63,30) unsigned,
276
f69 numeric (0,0) zerofill,
277
f70 numeric (63,30) zerofill,
278
f71 numeric (0,0) unsigned zerofill,
279
f72 numeric (63,30) unsigned zerofill,
283
f76 real unsigned zerofill,
284
f77 double default 7.7,
285
f78 double unsigned default 7.7,
286
f79 double zerofill default 7.7,
287
f80 double unsigned zerofill default 8.8,
288
f81 float not null default 8.8,
289
f82 float unsigned not null default 8.8,
290
f83 float zerofill not null default 8.8,
291
f84 float unsigned zerofill not null default 8.8,
292
f85 float(0) not null default 8.8,
293
f86 float(23) not null default 8.8,
294
f87 float(0) unsigned not null default 8.8,
295
f88 float(23) unsigned not null default 8.8,
296
f89 float(0) zerofill not null default 8.8,
297
f90 float(23) zerofill not null default 8.8,
298
f91 float(0) unsigned zerofill not null default 8.8,
299
f92 float(23) unsigned zerofill not null default 8.8,
300
f93 float(24) not null default 8.8,
301
f94 float(53) not null default 8.8,
302
f95 float(24) unsigned not null default 8.8,
303
f96 float(53) unsigned not null default 8.8,
304
f97 float(24) zerofill not null default 8.8,
305
f98 float(53) zerofill not null default 8.8,
306
f99 float(24) unsigned zerofill not null default 8.8,
307
f100 float(53) unsigned zerofill not null default 8.8,
308
f101 date not null default '2000-01-01',
309
f102 time not null default 20,
310
f103 datetime not null default '2/2/2',
311
f104 timestamp not null default 20001231235959,
312
f105 year not null default 2000,
313
f106 year(3) not null default 2000,
314
f107 year(4) not null default 2000,
315
f108 enum("1enum","2enum") not null default "1enum",
316
f109 set("1set","2set") not null default "1set"
318
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb2.txt' into table tb2 ;
321
DROP TABLE IF EXISTS t1, t2, t4, t10, t11;
322
CREATE TABLE t1 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
323
CREATE TABLE t2 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
324
CREATE TABLE t4 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
325
CREATE TABLE t10 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
326
CREATE TABLE t11 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
327
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t1;
328
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t2;
329
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t4;
330
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t10;
331
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t11;
332
drop TABLE if exists t3;
333
CREATE TABLE t3 (f1 char(20), f2 char(20), f3 integer) ENGINE = InnoDB;
334
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' INTO TABLE t3;
335
drop database if exists test4;
336
CREATE database test4;
338
CREATE TABLE t6 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = InnoDB;
339
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t6;
341
drop TABLE if exists t7, t8;
342
CREATE TABLE t7 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = InnoDB;
343
CREATE TABLE t8 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = InnoDB;
344
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t7;
346
Warning 1265 Data truncated for column 'f3' at row 1
347
Warning 1265 Data truncated for column 'f3' at row 2
348
Warning 1265 Data truncated for column 'f3' at row 3
349
Warning 1265 Data truncated for column 'f3' at row 4
350
Warning 1265 Data truncated for column 'f3' at row 5
351
Warning 1265 Data truncated for column 'f3' at row 6
352
Warning 1265 Data truncated for column 'f3' at row 7
353
Warning 1265 Data truncated for column 'f3' at row 8
354
Warning 1265 Data truncated for column 'f3' at row 9
355
Warning 1265 Data truncated for column 'f3' at row 10
356
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t8;
358
Warning 1265 Data truncated for column 'f3' at row 1
359
Warning 1265 Data truncated for column 'f3' at row 2
360
Warning 1265 Data truncated for column 'f3' at row 3
361
Warning 1265 Data truncated for column 'f3' at row 4
362
Warning 1265 Data truncated for column 'f3' at row 5
363
Warning 1265 Data truncated for column 'f3' at row 6
364
Warning 1265 Data truncated for column 'f3' at row 7
365
Warning 1265 Data truncated for column 'f3' at row 8
366
Warning 1265 Data truncated for column 'f3' at row 9
367
Warning 1265 Data truncated for column 'f3' at row 10
368
drop TABLE if exists t9;
369
CREATE TABLE t9 (f1 int, f2 char(25), f3 int) ENGINE = InnoDB;
370
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' INTO TABLE t9;
371
DROP DATABASE IF EXISTS db_datadict;
372
CREATE DATABASE db_datadict;
375
IF(INSTR(table_comment,'InnoDB free') = 0
376
AND INSTR(table_comment,'number_of_replicas') = 0,
377
LENGTH(table_comment),
378
INSTR(table_comment,'InnoDB free')
379
+ INSTR(table_comment,'number_of_replicas') - 1))
381
'-----------------------------------------------------' AS "Separator"
382
FROM information_schema.tables
383
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
384
ORDER BY table_schema,table_name;
388
TABLE_TYPE BASE TABLE
395
MAX_DATA_LENGTH #MDL#
402
TABLE_COLLATION latin1_swedish_ci
407
Separator -----------------------------------------------------
411
TABLE_TYPE BASE TABLE
418
MAX_DATA_LENGTH #MDL#
425
TABLE_COLLATION latin1_swedish_ci
430
Separator -----------------------------------------------------
434
TABLE_TYPE BASE TABLE
441
MAX_DATA_LENGTH #MDL#
448
TABLE_COLLATION latin1_swedish_ci
453
Separator -----------------------------------------------------
457
TABLE_TYPE BASE TABLE
464
MAX_DATA_LENGTH #MDL#
471
TABLE_COLLATION latin1_swedish_ci
476
Separator -----------------------------------------------------
480
TABLE_TYPE BASE TABLE
487
MAX_DATA_LENGTH #MDL#
494
TABLE_COLLATION latin1_swedish_ci
499
Separator -----------------------------------------------------
503
TABLE_TYPE BASE TABLE
510
MAX_DATA_LENGTH #MDL#
517
TABLE_COLLATION latin1_swedish_ci
522
Separator -----------------------------------------------------
526
TABLE_TYPE BASE TABLE
533
MAX_DATA_LENGTH #MDL#
540
TABLE_COLLATION latin1_swedish_ci
545
Separator -----------------------------------------------------
549
TABLE_TYPE BASE TABLE
556
MAX_DATA_LENGTH #MDL#
563
TABLE_COLLATION latin1_swedish_ci
568
Separator -----------------------------------------------------
572
TABLE_TYPE BASE TABLE
579
MAX_DATA_LENGTH #MDL#
586
TABLE_COLLATION latin1_swedish_ci
591
Separator -----------------------------------------------------
595
TABLE_TYPE BASE TABLE
602
MAX_DATA_LENGTH #MDL#
609
TABLE_COLLATION latin1_swedish_ci
614
Separator -----------------------------------------------------
618
TABLE_TYPE BASE TABLE
625
MAX_DATA_LENGTH #MDL#
632
TABLE_COLLATION latin1_swedish_ci
637
Separator -----------------------------------------------------
641
TABLE_TYPE BASE TABLE
648
MAX_DATA_LENGTH #MDL#
655
TABLE_COLLATION latin1_swedish_ci
660
Separator -----------------------------------------------------
664
TABLE_TYPE BASE TABLE
671
MAX_DATA_LENGTH #MDL#
678
TABLE_COLLATION latin1_swedish_ci
683
Separator -----------------------------------------------------
687
TABLE_TYPE BASE TABLE
694
MAX_DATA_LENGTH #MDL#
701
TABLE_COLLATION latin1_swedish_ci
706
Separator -----------------------------------------------------
710
TABLE_TYPE BASE TABLE
717
MAX_DATA_LENGTH #MDL#
724
TABLE_COLLATION latin1_swedish_ci
729
Separator -----------------------------------------------------
730
DROP USER testuser1@localhost;
731
CREATE USER testuser1@localhost;
732
GRANT SELECT ON db_datadict.* TO testuser1@localhost;
733
# Establish connection testuser1 (user=testuser1)
736
IF(INSTR(table_comment,'InnoDB free') = 0
737
AND INSTR(table_comment,'number_of_replicas') = 0,
738
LENGTH(table_comment),
739
INSTR(table_comment,'InnoDB free')
740
+ INSTR(table_comment,'number_of_replicas') - 1))
742
'-----------------------------------------------------' AS "Separator"
743
FROM information_schema.tables
744
WHERE table_schema LIKE 'test%' AND table_type = 'BASE TABLE'
745
ORDER BY table_schema,table_name;
749
TABLE_TYPE BASE TABLE
756
MAX_DATA_LENGTH #MDL#
763
TABLE_COLLATION latin1_swedish_ci
768
Separator -----------------------------------------------------
772
TABLE_TYPE BASE TABLE
779
MAX_DATA_LENGTH #MDL#
786
TABLE_COLLATION latin1_swedish_ci
791
Separator -----------------------------------------------------
795
TABLE_TYPE BASE TABLE
802
MAX_DATA_LENGTH #MDL#
809
TABLE_COLLATION latin1_swedish_ci
814
Separator -----------------------------------------------------
818
TABLE_TYPE BASE TABLE
825
MAX_DATA_LENGTH #MDL#
832
TABLE_COLLATION latin1_swedish_ci
837
Separator -----------------------------------------------------
841
TABLE_TYPE BASE TABLE
848
MAX_DATA_LENGTH #MDL#
855
TABLE_COLLATION latin1_swedish_ci
860
Separator -----------------------------------------------------
864
TABLE_TYPE BASE TABLE
871
MAX_DATA_LENGTH #MDL#
878
TABLE_COLLATION latin1_swedish_ci
883
Separator -----------------------------------------------------
887
TABLE_TYPE BASE TABLE
894
MAX_DATA_LENGTH #MDL#
901
TABLE_COLLATION latin1_swedish_ci
906
Separator -----------------------------------------------------
910
TABLE_TYPE BASE TABLE
917
MAX_DATA_LENGTH #MDL#
924
TABLE_COLLATION latin1_swedish_ci
929
Separator -----------------------------------------------------
933
TABLE_TYPE BASE TABLE
940
MAX_DATA_LENGTH #MDL#
947
TABLE_COLLATION latin1_swedish_ci
952
Separator -----------------------------------------------------
956
TABLE_TYPE BASE TABLE
963
MAX_DATA_LENGTH #MDL#
970
TABLE_COLLATION latin1_swedish_ci
975
Separator -----------------------------------------------------
979
TABLE_TYPE BASE TABLE
986
MAX_DATA_LENGTH #MDL#
993
TABLE_COLLATION latin1_swedish_ci
998
Separator -----------------------------------------------------
1002
TABLE_TYPE BASE TABLE
1007
AVG_ROW_LENGTH #ARL#
1009
MAX_DATA_LENGTH #MDL#
1016
TABLE_COLLATION latin1_swedish_ci
1021
Separator -----------------------------------------------------
1025
TABLE_TYPE BASE TABLE
1030
AVG_ROW_LENGTH #ARL#
1032
MAX_DATA_LENGTH #MDL#
1039
TABLE_COLLATION latin1_swedish_ci
1044
Separator -----------------------------------------------------
1045
# Switch to connection default and close connection testuser1
1046
DROP USER testuser1@localhost;
1047
DROP DATABASE db_datadict;
1048
DROP DATABASE test1;
1049
DROP DATABASE test4;
1057
DROP TABLE test.t10;
1058
DROP TABLE test.t11;
1059
DROP TABLE test.tb1;
1060
DROP TABLE test.tb2;
1061
DROP TABLE test.tb3;
1062
DROP TABLE test.tb4;