show_check.result 24 KB
Newer Older
unknown's avatar
unknown committed
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;
unknown's avatar
unknown committed
5 6 7
delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
flush privileges;
unknown's avatar
unknown committed
8 9
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
unknown's avatar
unknown committed
10
check table t1 fast;
11 12
Table	Op	Msg_type	Msg_text
test.t1	check	status	Table is already up to date
unknown's avatar
unknown committed
13
check table t1 fast;
14 15
Table	Op	Msg_type	Msg_text
test.t1	check	status	Table is already up to date
unknown's avatar
unknown committed
16
check table t1 changed;
17 18
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
19
insert into t1 values (5,5,5);
unknown's avatar
unknown committed
20
check table t1 changed;
21 22
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
23
check table t1 medium;
24 25
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
26
check table t1 extended;
27 28
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
29
show index from t1;
30 31 32 33
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	a	A	5	NULL	NULL		BTREE	
t1	1	b	1	b	A	1	NULL	NULL		BTREE	
t1	1	b	2	c	A	5	NULL	NULL		BTREE	
unknown's avatar
unknown committed
34
insert into t1 values (5,5,5);
35
ERROR 23000: Duplicate entry '5' for key 1
unknown's avatar
unknown committed
36
optimize table t1;
37 38
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
unknown's avatar
unknown committed
39
optimize table t1;
40 41
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	Table is already up to date
unknown's avatar
unknown committed
42 43
drop table t1;
show variables like "wait_timeout%";
44 45
Variable_name	Value
wait_timeout	28800
unknown's avatar
unknown committed
46 47 48
show variables like "WAIT_timeout%";
Variable_name	Value
wait_timeout	28800
unknown's avatar
unknown committed
49
show variables like "this_doesn't_exists%";
50
Variable_name	Value
unknown's avatar
unknown committed
51
show table status from test like "this_doesn't_exists%";
52
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
unknown's avatar
unknown committed
53
show databases;
54
Database
55
information_schema
56 57
mysql
test
unknown's avatar
unknown committed
58
show databases like "test%";
59 60
Database (test%)
test
unknown's avatar
unknown committed
61 62 63
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));
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);
analyze table t1;
64 65
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
unknown's avatar
unknown committed
66
show index from t1;
67 68 69 70 71
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	f1	A	1	NULL	NULL		BTREE	
t1	0	PRIMARY	2	f2	A	3	NULL	NULL		BTREE	
t1	0	PRIMARY	3	f3	A	9	NULL	NULL		BTREE	
t1	0	PRIMARY	4	f4	A	18	NULL	NULL		BTREE	
unknown's avatar
unknown committed
72
repair table t1;
73 74
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
unknown's avatar
unknown committed
75
show index from t1;
76 77 78 79 80
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	f1	A	1	NULL	NULL		BTREE	
t1	0	PRIMARY	2	f2	A	3	NULL	NULL		BTREE	
t1	0	PRIMARY	3	f3	A	9	NULL	NULL		BTREE	
t1	0	PRIMARY	4	f4	A	18	NULL	NULL		BTREE	
unknown's avatar
unknown committed
81 82 83
drop table t1;
create temporary table t1 (a int not null);
show create table t1;
84 85
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
unknown's avatar
unknown committed
86
  `a` int(11) NOT NULL
unknown's avatar
unknown committed
87
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
88 89
alter table t1 rename t2;
show create table t2;
90 91
Table	Create Table
t2	CREATE TEMPORARY TABLE `t2` (
unknown's avatar
unknown committed
92
  `a` int(11) NOT NULL
unknown's avatar
unknown committed
93
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
94 95 96
drop table t2;
create table t1 (
test_set set( 'val1', 'val2', 'val3' ) not null default '',
unknown's avatar
unknown committed
97
name char(20) default 'O''Brien' comment 'O''Brien as default',
unknown's avatar
unknown committed
98
c int not null comment 'int column',
unknown's avatar
unknown committed
99
`c-b` int comment 'name with a minus',
unknown's avatar
unknown committed
100 101
`space 2` int comment 'name with a space'
  ) comment = 'it\'s a table' ;
unknown's avatar
unknown committed
102
show create table t1;
103 104
Table	Create Table
t1	CREATE TABLE `t1` (
105 106
  `test_set` set('val1','val2','val3') NOT NULL default '',
  `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default',
unknown's avatar
unknown committed
107
  `c` int(11) NOT NULL COMMENT 'int column',
unknown's avatar
unknown committed
108 109
  `c-b` int(11) default NULL COMMENT 'name with a minus',
  `space 2` int(11) default NULL COMMENT 'name with a space'
unknown's avatar
unknown committed
110 111 112 113 114 115
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table'
set sql_quote_show_create=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE t1 (
  test_set set('val1','val2','val3') NOT NULL default '',
unknown's avatar
unknown committed
116
  `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default',
unknown's avatar
unknown committed
117
  c int(11) NOT NULL COMMENT 'int column',
unknown's avatar
unknown committed
118 119
  `c-b` int(11) default NULL COMMENT 'name with a minus',
  `space 2` int(11) default NULL COMMENT 'name with a space'
unknown's avatar
unknown committed
120
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table'
unknown's avatar
unknown committed
121
set sql_quote_show_create=1;
unknown's avatar
unknown committed
122
show full columns from t1;
123
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
124
test_set	set('val1','val2','val3')	latin1_swedish_ci	NO				select,insert,update,references	
125
name	char(20)	latin1_swedish_ci	YES		O'Brien		select,insert,update,references	O'Brien as default
126
c	int(11)	NULL	NO				select,insert,update,references	int column
unknown's avatar
unknown committed
127 128
c-b	int(11)	NULL	YES		NULL		select,insert,update,references	name with a minus
space 2	int(11)	NULL	YES		NULL		select,insert,update,references	name with a space
unknown's avatar
unknown committed
129 130 131
drop table t1;
create table t1 (a int not null, unique aa (a));
show create table t1;
132 133
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
134
  `a` int(11) NOT NULL,
135
  UNIQUE KEY `aa` (`a`)
unknown's avatar
unknown committed
136
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
137 138 139
drop table t1;
create table t1 (a int not null, primary key (a));
show create table t1;
140 141
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
142
  `a` int(11) NOT NULL,
143
  PRIMARY KEY  (`a`)
unknown's avatar
unknown committed
144
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
145 146 147
drop table t1;
flush tables;
show open tables;
148
Database	Table	In_use	Name_locked
unknown's avatar
unknown committed
149 150 151
create table t1(n int);
insert into t1 values (1);
show open tables;
152 153
Database	Table	In_use	Name_locked
test	t1	0	0
unknown's avatar
unknown committed
154
drop table t1;
unknown's avatar
unknown committed
155
create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
unknown's avatar
unknown committed
156 157 158
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
159
  `a` int(11) NOT NULL,
160
  `b` varchar(10) default NULL,
unknown's avatar
unknown committed
161
  KEY `b` (`b`)
unknown's avatar
unknown committed
162
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
unknown's avatar
unknown committed
163 164 165 166
alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
167
  `a` int(11) NOT NULL,
168
  `b` varchar(10) default NULL,
unknown's avatar
unknown committed
169
  KEY `b` (`b`)
unknown's avatar
unknown committed
170
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
unknown's avatar
unknown committed
171 172 173 174
ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
175
  `a` int(11) NOT NULL,
176
  `b` varchar(10) default NULL,
unknown's avatar
unknown committed
177
  KEY `b` (`b`)
unknown's avatar
unknown committed
178
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
179
drop table t1;
180 181
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));
show columns from t1;
182 183 184 185 186 187 188
Field	Type	Null	Key	Default	Extra
a	decimal(9,2)	YES		NULL	
b	decimal(9,0)	YES		NULL	
e	double(9,2)	YES		NULL	
f	double(5,0)	YES		NULL	
h	float(3,2)	YES		NULL	
i	float(3,0)	YES		NULL	
unknown's avatar
unknown committed
189
show full columns from t1;
190
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
191 192 193 194 195 196
a	decimal(9,2)	NULL	YES		NULL		select,insert,update,references	
b	decimal(9,0)	NULL	YES		NULL		select,insert,update,references	
e	double(9,2)	NULL	YES		NULL		select,insert,update,references	
f	double(5,0)	NULL	YES		NULL		select,insert,update,references	
h	float(3,2)	NULL	YES		NULL		select,insert,update,references	
i	float(3,0)	NULL	YES		NULL		select,insert,update,references	
197
drop table t1;
unknown's avatar
unknown committed
198
create table t1 (
199
type_bool bool not null default 0,
unknown's avatar
unknown committed
200 201 202 203 204 205 206 207 208 209
type_tiny tinyint not null auto_increment primary key,
type_short smallint(3),
type_mediumint mediumint,
type_bigint bigint,
type_decimal decimal(5,2),
type_numeric numeric(5,2),
empty_char char(0),
type_char char(2),
type_varchar varchar(10),
type_timestamp timestamp not null,
210 211 212
type_date date not null default '0000-00-00',
type_time time not null default '00:00:00',
type_datetime datetime not null default '0000-00-00 00:00:00',
unknown's avatar
unknown committed
213 214 215 216 217 218 219 220
type_year year,
type_enum enum ('red', 'green', 'blue'),
type_set enum ('red', 'green', 'blue'),
type_tinyblob tinyblob,
type_blob blob,
type_medium_blob mediumblob,
type_long_blob longblob,
index(type_short)
unknown's avatar
unknown committed
221
) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;
unknown's avatar
unknown committed
222 223 224
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
225
  `type_bool` tinyint(1) NOT NULL default '0',
unknown's avatar
unknown committed
226 227 228 229 230 231 232 233 234
  `type_tiny` tinyint(4) NOT NULL auto_increment,
  `type_short` smallint(3) default NULL,
  `type_mediumint` mediumint(9) default NULL,
  `type_bigint` bigint(20) default NULL,
  `type_decimal` decimal(5,2) default NULL,
  `type_numeric` decimal(5,2) default NULL,
  `empty_char` char(0) default NULL,
  `type_char` char(2) default NULL,
  `type_varchar` varchar(10) default NULL,
235
  `type_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
236 237 238
  `type_date` date NOT NULL default '0000-00-00',
  `type_time` time NOT NULL default '00:00:00',
  `type_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
unknown's avatar
unknown committed
239 240 241 242 243 244 245 246 247
  `type_year` year(4) default NULL,
  `type_enum` enum('red','green','blue') default NULL,
  `type_set` enum('red','green','blue') default NULL,
  `type_tinyblob` tinyblob,
  `type_blob` blob,
  `type_medium_blob` mediumblob,
  `type_long_blob` longblob,
  PRIMARY KEY  (`type_tiny`),
  KEY `type_short` (`type_short`)
unknown's avatar
unknown committed
248
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
unknown's avatar
unknown committed
249 250 251
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
select * from t1;
type_bool	type_tiny	type_short	type_mediumint	type_bigint	type_decimal	type_numeric	empty_char	type_char	type_varchar	type_timestamp	type_date	type_time	type_datetime	type_year	type_enum	type_set	type_tinyblob	type_blob	type_medium_blob	type_long_blob
unknown's avatar
unknown committed
252
0	1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2003-02-07 10:00:01	0000-00-00	00:00:00	0000-00-00 00:00:00	NULL	NULL	NULL	NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
253
drop table t1;
254 255 256 257
create table t1 (a int not null);
create table t2 select max(a) from t1;
show columns from t2;
Field	Type	Null	Key	Default	Extra
258
max(a)	int(11)	YES		NULL	
259
drop table t1,t2;
260 261
create table t1 (c decimal, d double, f float, r real);
show columns from t1;
262 263 264 265 266
Field	Type	Null	Key	Default	Extra
c	decimal(10,0)	YES		NULL	
d	double	YES		NULL	
f	float	YES		NULL	
r	double	YES		NULL	
267 268 269
drop table t1;
create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
show columns from t1;
270
Field	Type	Null	Key	Default	Extra
271 272 273
c	decimal(3,3)	YES		NULL	
d	double(3,3)	YES		NULL	
f	float(3,3)	YES		NULL	
274
drop table t1;
275 276 277 278 279 280
SET @old_sql_mode= @@sql_mode, sql_mode= '';
SET @old_sql_quote_show_create= @@sql_quote_show_create, sql_quote_show_create= OFF;
CREATE TABLE `a/b` (i INT);
ERROR 42000: Incorrect table name 'a/b'
SET sql_mode= '';
SET sql_quote_show_create= OFF;
281 282 283 284 285 286 287 288 289 290 291 292 293 294
CREATE TABLE t1 (i INT);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE t1 (
  i int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE `table` (i INT);
SHOW CREATE TABLE `table`;
Table	Create Table
table	CREATE TABLE `table` (
  i int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE `table`;
295 296
SET sql_quote_show_create= @old_sql_quote_show_create;
SET sql_mode= @old_sql_mode;
297 298 299 300 301
select @@max_heap_table_size;
@@max_heap_table_size
1047552
CREATE TABLE t1 (
a int(11) default NULL,
302
KEY a USING BTREE (a)
303 304 305 306 307 308 309 310
) ENGINE=HEAP;
CREATE TABLE t2 (
b int(11) default NULL,
index(b)
) ENGINE=HEAP;
CREATE TABLE t3 (
a int(11) default NULL,
b int(11) default NULL,
311
KEY a USING BTREE (a),
312 313 314 315 316 317
index(b)
) ENGINE=HEAP;
insert into t1 values (1),(2);
insert into t2 values (1),(2);
insert into t3 values (1,1),(2,2);
show table status;
318
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
319 320 321
t1	MEMORY	10	Fixed	2	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	2	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	2	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
322 323 324 325
insert into t1 values (3),(4);
insert into t2 values (3),(4);
insert into t3 values (3,3),(4,4);
show table status;
326
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
327 328 329
t1	MEMORY	10	Fixed	4	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	4	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	4	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
330 331 332 333
insert into t1 values (5);
insert into t2 values (5);
insert into t3 values (5,5);
show table status;
334
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
335 336 337
t1	MEMORY	10	Fixed	5	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	5	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	5	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
338 339 340 341
delete from t1 where a=3;
delete from t2 where b=3;
delete from t3 where a=3;
show table status;
342
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
343 344 345
t1	MEMORY	10	Fixed	4	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	4	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	4	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
346 347 348 349
delete from t1;
delete from t2;
delete from t3;
show table status;
350
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
351 352 353
t1	MEMORY	10	Fixed	0	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	0	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	0	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
354 355 356 357
insert into t1 values (5);
insert into t2 values (5);
insert into t3 values (5,5);
show table status;
358
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
359 360 361
t1	MEMORY	10	Fixed	1	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	1	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	1	#	#	#	#	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
362 363 364 365
delete from t1 where a=5;
delete from t2 where b=5;
delete from t3 where a=5;
show table status;
366
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
367 368 369
t1	MEMORY	10	Fixed	0	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	0	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t3	MEMORY	10	Fixed	0	#	#	#	#	#	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
370
drop table t1, t2, t3;
371 372
create database mysqltest;
show create database mysqltest;
unknown's avatar
unknown committed
373
Database	Create Database
374 375 376 377 378 379
mysqltest	CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */
create table mysqltest.t1(a int);
insert into mysqltest.t1 values(1);
grant select on `mysqltest`.* to mysqltest_1@localhost;
grant usage  on `mysqltest`.* to mysqltest_2@localhost;
grant drop   on `mysqltest`.* to mysqltest_3@localhost;
unknown's avatar
unknown committed
380 381 382
select * from t1;
a
1
383
show create database mysqltest;
unknown's avatar
unknown committed
384
Database	Create Database
385
mysqltest	CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */
unknown's avatar
unknown committed
386
drop table t1;
unknown's avatar
unknown committed
387
ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1'
388 389 390
drop database mysqltest;
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
select * from mysqltest.t1;
unknown's avatar
unknown committed
391
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't1'
392 393 394
show create database mysqltest;
ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest'
drop table mysqltest.t1;
unknown's avatar
unknown committed
395
ERROR 42000: DROP command denied to user 'mysqltest_2'@'localhost' for table 't1'
396 397 398
drop database mysqltest;
ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest'
select * from mysqltest.t1;
unknown's avatar
unknown committed
399
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
400 401 402 403
show create database mysqltest;
ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysqltest'
drop table mysqltest.t1;
drop database mysqltest;
404
set names binary;
unknown's avatar
unknown committed
405 406 407 408 409
delete from mysql.user 
where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
delete from mysql.db   
where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
flush privileges;
410 411 412 413 414 415
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
unknown's avatar
unknown committed
416
) ENGINE=MEMORY DEFAULT CHARSET=latin1
417 418 419 420 421 422
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
unknown's avatar
unknown committed
423
  KEY `i` USING HASH (`i`)
unknown's avatar
unknown committed
424
) ENGINE=MEMORY DEFAULT CHARSET=latin1
425 426 427 428 429 430
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
unknown's avatar
unknown committed
431
  KEY `i` USING BTREE (`i`)
unknown's avatar
unknown committed
432
) ENGINE=MEMORY DEFAULT CHARSET=latin1
433 434 435 436 437 438 439 440 441 442 443 444 445 446
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
unknown's avatar
unknown committed
447
  KEY `i` USING BTREE (`i`)
448 449 450 451 452 453 454 455 456 457 458 459 460 461 462
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
unknown's avatar
unknown committed
463
) ENGINE=MEMORY DEFAULT CHARSET=latin1
464 465 466 467 468 469
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
unknown's avatar
unknown committed
470
  KEY `i` USING BTREE (`i`)
471 472 473 474 475 476
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL,
unknown's avatar
unknown committed
477
  KEY `i` USING BTREE (`i`)
unknown's avatar
unknown committed
478
) ENGINE=MEMORY DEFAULT CHARSET=latin1
479
DROP TABLE t1;
unknown's avatar
unknown committed
480 481 482 483 484 485
CREATE TABLE t1(
field1 text NOT NULL,
PRIMARY KEY(field1(1000))
);
show index from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
486 487 488 489 490 491 492 493 494 495 496 497
def			STATISTICS	TABLE_NAME	Table	253	64	2	N	1	0	63
def			STATISTICS	NON_UNIQUE	Non_unique	8	1	1	N	32769	0	63
def			STATISTICS	INDEX_NAME	Key_name	253	64	7	N	1	0	63
def			STATISTICS	SEQ_IN_INDEX	Seq_in_index	8	2	1	N	32769	0	63
def			STATISTICS	COLUMN_NAME	Column_name	253	64	6	N	1	0	63
def			STATISTICS	COLLATION	Collation	253	1	1	Y	0	0	63
def			STATISTICS	CARDINALITY	Cardinality	8	21	1	Y	32768	0	63
def			STATISTICS	SUB_PART	Sub_part	8	3	4	Y	32768	0	63
def			STATISTICS	PACKED	Packed	253	10	0	Y	0	0	63
def			STATISTICS	NULLABLE	Null	253	3	0	N	1	0	63
def			STATISTICS	INDEX_TYPE	Index_type	253	16	5	N	1	0	63
def			STATISTICS	COMMENT	Comment	253	16	0	Y	0	0	63
unknown's avatar
unknown committed
498 499 500
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	field1	A	0	1000	NULL		BTREE	
drop table t1;
501 502 503 504 505 506 507 508 509
create table t1 (
c1 int NOT NULL,
c2 int NOT NULL,
PRIMARY KEY USING HASH (c1),
INDEX USING BTREE(c2)
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
510 511
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
512 513 514 515
  PRIMARY KEY  USING HASH (`c1`),
  KEY `c2` USING BTREE (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
516 517 518 519 520 521 522
flush tables;
SHOW TABLE STATUS like 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	NULL	NULL	NULL	NULL	#	#	#	#	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Incorrect information in file: './test/t1.frm'
show create table t1;
ERROR HY000: Incorrect information in file: './test/t1.frm'
drop table t1;
523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565
CREATE TABLE txt1(a int);
CREATE TABLE tyt2(a int);
CREATE TABLE urkunde(a int);
FLUSH TABLES;
SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0;
1
SHOW OPEN TABLES;
Database	Table	In_use	Name_locked
mysql	db	0	0
test	urkunde	0	0
mysql	time_zone	0	0
mysql	user	0	0
test	txt1	0	0
mysql	proc	0	0
test	tyt2	0	0
mysql	time_zone_name	0	0
SHOW OPEN TABLES FROM mysql;
Database	Table	In_use	Name_locked
mysql	db	0	0
mysql	time_zone	0	0
mysql	user	0	0
mysql	proc	0	0
mysql	time_zone_name	0	0
SHOW OPEN TABLES FROM mysql LIKE 'u%';
Database	Table	In_use	Name_locked
mysql	user	0	0
SHOW OPEN TABLES LIKE 't%';
Database	Table	In_use	Name_locked
mysql	time_zone	0	0
test	txt1	0	0
test	tyt2	0	0
mysql	time_zone_name	0	0
SHOW OPEN TABLES LIKE '%o%';
Database	Table	In_use	Name_locked
mysql	time_zone	0	0
mysql	proc	0	0
mysql	time_zone_name	0	0
FLUSH TABLES;
SHOW OPEN TABLES;
Database	Table	In_use	Name_locked
DROP TABLE txt1;
DROP TABLE tyt2;
DROP TABLE urkunde;
566 567
SHOW TABLES FROM non_existing_database;
ERROR 42000: Unknown database 'non_existing_database'
568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627
DROP VIEW IF EXISTS v1;
DROP PROCEDURE IF EXISTS p1;
CREATE VIEW v1 AS SELECT 1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_CACHE 1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_NO_CACHE 1;
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache 1 AS `1`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `NOW()`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_CACHE NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache now() AS `NOW()`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_NO_CACHE NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()`
DROP VIEW v1;
CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()`
DROP VIEW v1;
CREATE PROCEDURE p1()
BEGIN
SET @s= 'CREATE VIEW v1 AS SELECT SQL_CACHE 1';
PREPARE stmt FROM @s;
EXECUTE stmt;
DROP PREPARE stmt;
END |
CALL p1();
SHOW CREATE VIEW v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1`
DROP PROCEDURE p1;
DROP VIEW v1;