1
drop table if exists t1,t2;
2
drop table if exists t1aa,t2aa;
3
drop database if exists mysqltest;
4
drop database if exists mysqltest1;
5
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
6
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
7
-- Here we enable metadata just to check that the collation of the
8
-- resultset is non-binary for string type. This should be changed
9
-- after Bug#29394 is implemented.
11
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
12
def Table 8 512 7 Y 0 31 45
13
def Op 8 40 5 Y 0 31 45
14
def Msg_type 8 40 6 Y 0 31 45
15
def Msg_text 8 1020 2 Y 0 31 45
16
Table Op Msg_type Msg_text
17
test.t1 check status OK
18
insert into t1 values (5,5,5);
20
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
21
def Table 8 512 7 Y 0 31 45
22
def Op 8 40 5 Y 0 31 45
23
def Msg_type 8 40 6 Y 0 31 45
24
def Msg_text 8 1020 2 Y 0 31 45
25
Table Op Msg_type Msg_text
26
test.t1 check status OK
28
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
29
def data_dictionary show_indexes show_indexes Table Table 8 256 2 N 1 0 45
30
def data_dictionary show_indexes show_indexes Unique Unique 8 20 5 N 1 0 45
31
def data_dictionary show_indexes show_indexes Key_name Key_name 8 256 7 N 1 0 45
32
def data_dictionary show_indexes show_indexes Seq_in_index Seq_in_index 5 20 1 N 32769 0 63
33
def data_dictionary show_indexes show_indexes Column_name Column_name 8 256 1 N 1 0 45
34
Table Unique Key_name Seq_in_index Column_name
38
insert into t1 values (5,5,5);
39
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
40
-- Here we enable metadata just to check that the collation of the
41
-- resultset is non-binary for string type. This should be changed
42
-- after Bug#29394 is implemented.
43
alter table t1 engine="default";
44
alter table t1 engine="default";
46
-- Here we enable metadata just to check that the collation of the
47
-- resultset is non-binary for string type. This should be changed
48
-- after Bug#29394 is implemented.
49
show variables like "server_id%";
50
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
51
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_NAME Variable_name 8 256 9 N 1 0 45
52
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_VALUE Value 8 4096 1 N 1 0 45
55
show variables like "SERVER_id%";
56
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
57
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_NAME Variable_name 8 256 9 N 1 0 45
58
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_VALUE Value 8 4096 1 N 1 0 45
61
show variables like "this_doesn't_exists%";
62
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
63
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_NAME Variable_name 8 256 0 N 1 0 45
64
def data_dictionary session_variables SESSION_VARIABLES VARIABLE_VALUE Value 8 4096 0 N 1 0 45
66
show table status from test like "this_doesn't_exists%";
67
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
68
def data_dictionary show_table_status SHOW_TABLE_STATUS Session Session 5 20 0 N 32769 0 63
69
def data_dictionary show_table_status SHOW_TABLE_STATUS Schema Schema 8 256 0 N 1 0 45
70
def data_dictionary show_table_status SHOW_TABLE_STATUS Name Name 8 256 0 N 1 0 45
71
def data_dictionary show_table_status SHOW_TABLE_STATUS Type Type 8 256 0 N 1 0 45
72
def data_dictionary show_table_status SHOW_TABLE_STATUS Engine Engine 8 256 0 N 1 0 45
73
def data_dictionary show_table_status SHOW_TABLE_STATUS Version Version 8 256 0 N 1 0 45
74
def data_dictionary show_table_status SHOW_TABLE_STATUS Rows Rows 8 256 0 N 1 0 45
75
def data_dictionary show_table_status SHOW_TABLE_STATUS Avg_row_length Avg_row_length 8 256 0 N 1 0 45
76
def data_dictionary show_table_status SHOW_TABLE_STATUS Table_size Table_size 8 256 0 N 1 0 45
77
def data_dictionary show_table_status SHOW_TABLE_STATUS Auto_increment Auto_increment 8 256 0 N 1 0 45
78
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
80
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
81
def data_dictionary schema_names SCHEMA_NAMES SCHEMA_NAME Database 8 256 18 N 1 0 45
87
show databases like "test%";
88
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
89
def data_dictionary schema_names SCHEMA_NAMES SCHEMA_NAME Database (test%) 8 256 4 N 1 0 45
92
create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4));
93
insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1);
94
-- Here we enable metadata just to check that the collation of the
95
-- resultset is non-binary for string type. This should be changed
96
-- after Bug#29394 is implemented.
98
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
99
def Table 8 512 7 Y 0 31 45
100
def Op 8 40 7 Y 0 31 45
101
def Msg_type 8 40 6 Y 0 31 45
102
def Msg_text 8 1020 2 Y 0 31 45
103
Table Op Msg_type Msg_text
104
test.t1 analyze status OK
106
Table Unique Key_name Seq_in_index Column_name
112
Table Unique Key_name Seq_in_index Column_name
118
create temporary table t1 (a int not null);
119
show create table t1;
121
t1 CREATE TEMPORARY TABLE `t1` (
124
alter table t1 rename t2;
125
show create table t2;
127
t2 CREATE TEMPORARY TABLE `t2` (
132
name char(20) default 'O''Brien' comment 'O''Brien as default',
133
c int not null comment 'int column',
134
`c-b` int comment 'name with a minus',
135
`space 2` int comment 'name with a space'
136
) comment = 'it\'s a table' ;
137
show create table t1;
139
t1 CREATE TABLE `t1` (
140
`name` varchar(20) DEFAULT 'O''Brien' COMMENT 'O''Brien as default',
141
`c` int NOT NULL COMMENT 'int column',
142
`c-b` int DEFAULT NULL COMMENT 'name with a minus',
143
`space 2` int DEFAULT NULL COMMENT 'name with a space'
144
) ENGINE=DEFAULT COMMENT='it''s a table'
145
show columns from t1;
146
Field Type Null Default Default_is_NULL On_Update
147
name VARCHAR TRUE O'Brien FALSE
148
c INTEGER FALSE O'Brien FALSE
149
c-b INTEGER TRUE O'Brien TRUE
150
space 2 INTEGER TRUE O'Brien TRUE
152
create table t1 (a int not null, unique aa (a));
153
show create table t1;
155
t1 CREATE TABLE `t1` (
157
UNIQUE KEY `aa` (`a`)
160
create table t1 (a int not null, primary key (a));
161
show create table t1;
163
t1 CREATE TABLE `t1` (
168
create temporary table t1 (a int not null, b VARCHAR(10), INDEX (b) ) COMMENT="test" ENGINE=MYISAM ROW_FORMAT=fixed;
169
show create table t1;
171
t1 CREATE TEMPORARY TABLE `t1` (
173
`b` varchar(10) DEFAULT NULL,
175
) ENGINE=MyISAM ROW_FORMAT=FIXED COMMENT='test'
176
alter table t1 ROW_FORMAT=dynamic;
177
show create table t1;
179
t1 CREATE TEMPORARY TABLE `t1` (
181
`b` varchar(10) DEFAULT NULL,
183
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC COMMENT='test'
184
ALTER TABLE t1 COMMENT="" ROW_FORMAT=default;
185
show create table t1;
187
t1 CREATE TEMPORARY TABLE `t1` (
189
`b` varchar(10) DEFAULT NULL,
193
create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
194
show columns from t1;
195
Field Type Null Default Default_is_NULL On_Update
202
show columns from t1;
203
Field Type Null Default Default_is_NULL On_Update
211
create temporary table t1 (
214
type_decimal decimal(5,2),
215
type_numeric numeric(5,2),
218
type_varchar varchar(10),
219
type_timestamp timestamp not null,
221
type_datetime datetime,
222
type_enum enum ('red', 'green', 'blue'),
223
type_set enum ('red', 'green', 'blue'),
226
) COMMENT="test" ENGINE=MYISAM ROW_FORMAT=fixed;
227
show create table t1;
229
t1 CREATE TEMPORARY TABLE `t1` (
230
`type_int` int DEFAULT NULL,
231
`type_bigint` bigint DEFAULT NULL,
232
`type_decimal` decimal(5,2) DEFAULT NULL,
233
`type_numeric` decimal(5,2) DEFAULT NULL,
234
`empty_char` varchar(0) DEFAULT NULL,
235
`type_char` varchar(2) DEFAULT NULL,
236
`type_varchar` varchar(10) DEFAULT NULL,
237
`type_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
238
`type_date` date DEFAULT NULL,
239
`type_datetime` datetime DEFAULT NULL,
240
`type_enum` enum('red','green','blue') DEFAULT NULL,
241
`type_set` enum('red','green','blue') DEFAULT NULL,
243
KEY `type_int` (`type_int`)
244
) ENGINE=MyISAM ROW_FORMAT=FIXED COMMENT='test'
245
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
247
type_int type_bigint type_decimal type_numeric empty_char type_char type_varchar type_timestamp type_date type_datetime type_enum type_set type_blob
248
NULL NULL NULL NULL NULL NULL NULL 2003-02-07 10:00:01 NULL NULL NULL NULL NULL
250
create table t1 (a int not null);
251
create table t2 select max(a) from t1;
252
show columns from t2;
253
Field Type Null Default Default_is_NULL On_Update
254
max(a) INTEGER TRUE TRUE
256
create table t1 (c decimal, d double, f float, r real);
257
show columns from t1;
258
Field Type Null Default Default_is_NULL On_Update
264
create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
265
show columns from t1;
266
Field Type Null Default Default_is_NULL On_Update
271
CREATE TABLE ```ab``cd``` (i INT);
272
SHOW CREATE TABLE ```ab``cd```;
274
`ab`cd` CREATE TABLE ```ab``cd``` (
277
DROP TABLE ```ab``cd```;
278
CREATE TABLE ```ab````cd``` (i INT);
279
SHOW CREATE TABLE ```ab````cd```;
281
`ab``cd` CREATE TABLE ```ab````cd``` (
284
DROP TABLE ```ab````cd```;
285
CREATE TABLE ```a` (i INT);
286
SHOW CREATE TABLE ```a`;
288
`a CREATE TABLE ```a` (
292
CREATE TABLE `a.1` (i INT);
293
SHOW CREATE TABLE `a.1`;
295
a.1 CREATE TABLE `a.1` (
299
select @@max_heap_table_size;
300
@@max_heap_table_size
302
CREATE TEMPORARY TABLE t1 (
304
KEY a USING BTREE (a)
306
CREATE TEMPORARY TABLE t2 (
310
CREATE TEMPORARY TABLE t3 (
313
KEY a USING BTREE (a),
316
insert into t1 values (1),(2);
317
insert into t2 values (1),(2);
318
insert into t3 values (1,1),(2,2);
320
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
321
# test t1 TEMPORARY MEMORY # # # # #
322
# test t2 TEMPORARY MEMORY # # # # #
323
# test t3 TEMPORARY MEMORY # # # # #
324
insert into t1 values (3),(4);
325
insert into t2 values (3),(4);
326
insert into t3 values (3,3),(4,4);
328
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
329
# test t1 TEMPORARY MEMORY # # # # #
330
# test t2 TEMPORARY MEMORY # # # # #
331
# test t3 TEMPORARY MEMORY # # # # #
332
insert into t1 values (5);
333
insert into t2 values (5);
334
insert into t3 values (5,5);
336
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
337
# test t1 TEMPORARY MEMORY # # # # #
338
# test t2 TEMPORARY MEMORY # # # # #
339
# test t3 TEMPORARY MEMORY # # # # #
340
delete from t1 where a=3;
341
delete from t2 where b=3;
342
delete from t3 where a=3;
344
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
345
# test t1 TEMPORARY MEMORY # # # # #
346
# test t2 TEMPORARY MEMORY # # # # #
347
# test t3 TEMPORARY MEMORY # # # # #
352
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
353
# test t1 TEMPORARY MEMORY # # # # #
354
# test t2 TEMPORARY MEMORY # # # # #
355
# test t3 TEMPORARY MEMORY # # # # #
356
insert into t1 values (5);
357
insert into t2 values (5);
358
insert into t3 values (5,5);
360
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
361
# test t1 TEMPORARY MEMORY # # # # #
362
# test t2 TEMPORARY MEMORY # # # # #
363
# test t3 TEMPORARY MEMORY # # # # #
364
delete from t1 where a=5;
365
delete from t2 where b=5;
366
delete from t3 where a=5;
368
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
369
# test t1 TEMPORARY MEMORY # # # # #
370
# test t2 TEMPORARY MEMORY # # # # #
371
# test t3 TEMPORARY MEMORY # # # # #
372
drop table t1, t2, t3;
373
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;
374
SHOW CREATE TABLE t1;
376
t1 CREATE TEMPORARY TABLE `t1` (
377
`i` int DEFAULT NULL,
381
CREATE TEMPORARY TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;
382
SHOW CREATE TABLE t1;
384
t1 CREATE TEMPORARY TABLE `t1` (
385
`i` int DEFAULT NULL,
386
KEY `i` (`i`) USING HASH
389
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;
390
SHOW CREATE TABLE t1;
392
t1 CREATE TEMPORARY TABLE `t1` (
393
`i` int DEFAULT NULL,
394
KEY `i` (`i`) USING BTREE
397
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
398
SHOW CREATE TABLE t1;
400
t1 CREATE TEMPORARY TABLE `t1` (
401
`i` int DEFAULT NULL,
405
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
406
SHOW CREATE TABLE t1;
408
t1 CREATE TEMPORARY TABLE `t1` (
409
`i` int DEFAULT NULL,
410
KEY `i` (`i`) USING BTREE
413
CREATE TEMPORARY TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
414
SHOW CREATE TABLE t1;
416
t1 CREATE TEMPORARY TABLE `t1` (
417
`i` int DEFAULT NULL,
420
ALTER TABLE t1 ENGINE=MEMORY;
421
SHOW CREATE TABLE t1;
423
t1 CREATE TEMPORARY TABLE `t1` (
424
`i` int DEFAULT NULL,
428
CREATE TEMPORARY TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
429
SHOW CREATE TABLE t1;
431
t1 CREATE TEMPORARY TABLE `t1` (
432
`i` int DEFAULT NULL,
433
KEY `i` (`i`) USING BTREE
435
ALTER TABLE t1 ENGINE=MEMORY;
436
SHOW CREATE TABLE t1;
438
t1 CREATE TEMPORARY TABLE `t1` (
439
`i` int DEFAULT NULL,
440
KEY `i` (`i`) USING BTREE
444
field1 text NOT NULL,
445
PRIMARY KEY(field1(180))
448
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
449
def data_dictionary show_indexes show_indexes Table Table 8 256 2 N 1 0 45
450
def data_dictionary show_indexes show_indexes Unique Unique 8 20 4 N 1 0 45
451
def data_dictionary show_indexes show_indexes Key_name Key_name 8 256 7 N 1 0 45
452
def data_dictionary show_indexes show_indexes Seq_in_index Seq_in_index 5 20 1 N 32769 0 63
453
def data_dictionary show_indexes show_indexes Column_name Column_name 8 256 6 N 1 0 45
454
Table Unique Key_name Seq_in_index Column_name
455
t1 TRUE PRIMARY 1 field1
460
PRIMARY KEY USING HASH (c1),
461
INDEX USING BTREE(c2)
463
SHOW CREATE TABLE t1;
465
t1 CREATE TABLE `t1` (
468
PRIMARY KEY (`c1`) USING HASH,
469
KEY `c2` (`c2`) USING BTREE
472
SHOW TABLES FROM non_existing_database;
473
ERROR 42000: Unknown database 'non_existing_database'
475
SHOW TABLES FROM no_such_database;
476
ERROR 42000: Unknown database 'no_such_database'
477
SHOW COLUMNS FROM no_such_table;
478
ERROR 42S02: Table 'test.no_such_table' doesn't exist
479
show variables like 'myisam_recover_options';
482
create database `mysqlttest\1`;
483
create table `mysqlttest\1`.`a\b` (a int);
484
show tables from `mysqlttest\1`;
485
Tables_in_mysqlttest\1
487
show fields from `mysqlttest\1`.`a\b`;
488
Field Type Null Default Default_is_NULL On_Update
490
show columns from `a\b` from `mysqlttest\1`;
491
Field Type Null Default Default_is_NULL On_Update
493
show keys from `mysqlttest\1`.`a\b`;
494
Table Unique Key_name Seq_in_index Column_name
495
drop table `mysqlttest\1`.`a\b`;
496
drop database `mysqlttest\1`;
497
drop table if exists `été`;
498
create table `été` (field1 int);
503
show columns from `#mysql50#????????`;
504
Got one of the listed errors