1drop table if exists t1,t2; 2drop table if exists t1aa,t2aa; 3drop database if exists mysqltest; 4drop database if exists mysqltest1; 5delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; 6delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; 7flush privileges; 8create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); 9insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); 10-- Here we enable metadata just to check that the collation of the 11-- resultset is non-binary for string type. This should be changed 12-- after Bug#29394 is implemented. 13check table t1 fast; 14Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 15def Table 253 128 7 Y 0 31 8 16def Op 253 10 5 Y 0 31 8 17def Msg_type 253 10 6 Y 0 31 8 18def Msg_text 250 393216 27 Y 0 31 8 19Table Op Msg_type Msg_text 20test.t1 check status Table is already up to date 21check table t1 fast; 22Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 23def Table 253 128 7 Y 0 31 8 24def Op 253 10 5 Y 0 31 8 25def Msg_type 253 10 6 Y 0 31 8 26def Msg_text 250 393216 27 Y 0 31 8 27Table Op Msg_type Msg_text 28test.t1 check status Table is already up to date 29check table t1 changed; 30Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 31def Table 253 128 7 Y 0 31 8 32def Op 253 10 5 Y 0 31 8 33def Msg_type 253 10 6 Y 0 31 8 34def Msg_text 250 393216 2 Y 0 31 8 35Table Op Msg_type Msg_text 36test.t1 check status OK 37insert into t1 values (5,5,5); 38check table t1 changed; 39Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 40def Table 253 128 7 Y 0 31 8 41def Op 253 10 5 Y 0 31 8 42def Msg_type 253 10 6 Y 0 31 8 43def Msg_text 250 393216 2 Y 0 31 8 44Table Op Msg_type Msg_text 45test.t1 check status OK 46check table t1 medium; 47Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 48def Table 253 128 7 Y 0 31 8 49def Op 253 10 5 Y 0 31 8 50def Msg_type 253 10 6 Y 0 31 8 51def Msg_text 250 393216 2 Y 0 31 8 52Table Op Msg_type Msg_text 53test.t1 check status OK 54check table t1 extended; 55Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 56def Table 253 128 7 Y 0 31 8 57def Op 253 10 5 Y 0 31 8 58def Msg_type 253 10 6 Y 0 31 8 59def Msg_text 250 393216 2 Y 0 31 8 60Table Op Msg_type Msg_text 61test.t1 check status OK 62show index from t1; 63Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 64def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 1 0 8 65def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 66def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 8 67def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 68def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 1 N 1 0 8 69def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 0 0 8 70def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 71def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 72def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 0 0 8 73def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 8 74def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 8 75def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 8 76def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 8 77Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 78t1 0 PRIMARY 1 a A 5 NULL NULL BTREE 79t1 1 b 1 b A 2 NULL NULL BTREE 80t1 1 b 2 c A 5 NULL NULL BTREE 81insert into t1 values (5,5,5); 82ERROR 23000: Duplicate entry '5' for key 'PRIMARY' 83-- Here we enable metadata just to check that the collation of the 84-- resultset is non-binary for string type. This should be changed 85-- after Bug#29394 is implemented. 86optimize table t1; 87Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 88def Table 253 128 7 Y 0 31 8 89def Op 253 10 8 Y 0 31 8 90def Msg_type 253 10 6 Y 0 31 8 91def Msg_text 250 393216 2 Y 0 31 8 92Table Op Msg_type Msg_text 93test.t1 optimize status OK 94optimize table t1; 95Table Op Msg_type Msg_text 96test.t1 optimize status Table is already up to date 97drop table t1; 98-- Here we enable metadata just to check that the collation of the 99-- resultset is non-binary for string type. This should be changed 100-- after Bug#29394 is implemented. 101show variables like "wait_timeout%"; 102Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 103def information_schema VARIABLES VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 1 0 8 104def information_schema VARIABLES VARIABLES VARIABLE_VALUE Value 253 1024 5 Y 0 0 8 105Variable_name Value 106wait_timeout 28800 107show variables like "WAIT_timeout%"; 108Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 109def information_schema VARIABLES VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 1 0 8 110def information_schema VARIABLES VARIABLES VARIABLE_VALUE Value 253 1024 5 Y 0 0 8 111Variable_name Value 112wait_timeout 28800 113show variables like "this_doesn't_exists%"; 114Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 115def information_schema VARIABLES VARIABLES VARIABLE_NAME Variable_name 253 64 0 N 1 0 8 116def information_schema VARIABLES VARIABLES VARIABLE_VALUE Value 253 1024 0 Y 0 0 8 117Variable_name Value 118show table status from test like "this_doesn't_exists%"; 119Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 120def information_schema TABLES TABLES TABLE_NAME Name 253 64 0 N 1 0 8 121def information_schema TABLES TABLES ENGINE Engine 253 64 0 Y 0 0 8 122def information_schema TABLES TABLES VERSION Version 8 21 0 Y 32800 0 63 123def information_schema TABLES TABLES ROW_FORMAT Row_format 253 20 0 Y 0 0 8 124def information_schema TABLES TABLES TABLE_ROWS Rows 8 21 0 Y 32800 0 63 125def information_schema TABLES TABLES AVG_ROW_LENGTH Avg_row_length 8 21 0 Y 32800 0 63 126def information_schema TABLES TABLES DATA_LENGTH Data_length 8 21 0 Y 32800 0 63 127def information_schema TABLES TABLES MAX_DATA_LENGTH Max_data_length 8 21 0 Y 32800 0 63 128def information_schema TABLES TABLES INDEX_LENGTH Index_length 8 21 0 Y 32800 0 63 129def information_schema TABLES TABLES DATA_FREE Data_free 8 21 0 Y 32800 0 63 130def information_schema TABLES TABLES AUTO_INCREMENT Auto_increment 8 21 0 Y 32800 0 63 131def information_schema TABLES TABLES CREATE_TIME Create_time 12 19 0 Y 128 0 63 132def information_schema TABLES TABLES UPDATE_TIME Update_time 12 19 0 Y 128 0 63 133def information_schema TABLES TABLES CHECK_TIME Check_time 12 19 0 Y 128 0 63 134def information_schema TABLES TABLES TABLE_COLLATION Collation 253 32 0 Y 0 0 8 135def information_schema TABLES TABLES CHECKSUM Checksum 8 21 0 Y 32800 0 63 136def information_schema TABLES TABLES CREATE_OPTIONS Create_options 253 255 0 Y 0 0 8 137def information_schema TABLES TABLES TABLE_COMMENT Comment 253 2048 0 N 1 0 8 138Name 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 139show databases; 140Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 141def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database 253 64 18 N 1 0 8 142Database 143information_schema 144mtr 145mysql 146performance_schema 147sys 148test 149show databases like "test%"; 150Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 151def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (test%) 253 64 4 N 1 0 8 152Database (test%) 153test 154create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)); 155insert 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); 156-- Here we enable metadata just to check that the collation of the 157-- resultset is non-binary for string type. This should be changed 158-- after Bug#29394 is implemented. 159analyze table t1; 160Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 161def Table 253 128 7 Y 0 31 8 162def Op 253 10 7 Y 0 31 8 163def Msg_type 253 10 6 Y 0 31 8 164def Msg_text 250 393216 2 Y 0 31 8 165Table Op Msg_type Msg_text 166test.t1 analyze status OK 167show index from t1; 168Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 169t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE 170t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE 171t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE 172t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE 173-- Here we enable metadata just to check that the collation of the 174-- resultset is non-binary for string type. This should be changed 175-- after Bug#29394 is implemented. 176repair table t1; 177Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 178def Table 253 128 7 Y 0 31 8 179def Op 253 10 6 Y 0 31 8 180def Msg_type 253 10 6 Y 0 31 8 181def Msg_text 250 393216 2 Y 0 31 8 182Table Op Msg_type Msg_text 183test.t1 repair status OK 184show index from t1; 185Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 186t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE 187t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE 188t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE 189t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE 190drop table t1; 191create temporary table t1 (a int not null); 192show create table t1; 193Table Create Table 194t1 CREATE TEMPORARY TABLE `t1` ( 195 `a` int(11) NOT NULL 196) ENGINE=MyISAM DEFAULT CHARSET=latin1 197alter table t1 rename t2; 198show create table t2; 199Table Create Table 200t2 CREATE TEMPORARY TABLE `t2` ( 201 `a` int(11) NOT NULL 202) ENGINE=MyISAM DEFAULT CHARSET=latin1 203drop table t2; 204create table t1 ( 205test_set set( 'val1', 'val2', 'val3' ) not null default '', 206name char(20) default 'O''Brien' comment 'O''Brien as default', 207c int not null comment 'int column', 208`c-b` int comment 'name with a minus', 209`space 2` int comment 'name with a space' 210 ) comment = 'it\'s a table' ; 211show create table t1; 212Table Create Table 213t1 CREATE TABLE `t1` ( 214 `test_set` set('val1','val2','val3') NOT NULL DEFAULT '', 215 `name` char(20) DEFAULT 'O''Brien' COMMENT 'O''Brien as default', 216 `c` int(11) NOT NULL COMMENT 'int column', 217 `c-b` int(11) DEFAULT NULL COMMENT 'name with a minus', 218 `space 2` int(11) DEFAULT NULL COMMENT 'name with a space' 219) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' 220set sql_quote_show_create=0; 221show create table t1; 222Table Create Table 223t1 CREATE TABLE t1 ( 224 test_set set('val1','val2','val3') NOT NULL DEFAULT '', 225 `name` char(20) DEFAULT 'O''Brien' COMMENT 'O''Brien as default', 226 c int(11) NOT NULL COMMENT 'int column', 227 `c-b` int(11) DEFAULT NULL COMMENT 'name with a minus', 228 `space 2` int(11) DEFAULT NULL COMMENT 'name with a space' 229) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' 230set sql_quote_show_create=1; 231show full columns from t1; 232Field Type Collation Null Key Default Extra Privileges Comment 233test_set set('val1','val2','val3') latin1_swedish_ci NO select,insert,update,references 234name char(20) latin1_swedish_ci YES O'Brien select,insert,update,references O'Brien as default 235c int(11) NULL NO NULL select,insert,update,references int column 236c-b int(11) NULL YES NULL select,insert,update,references name with a minus 237space 2 int(11) NULL YES NULL select,insert,update,references name with a space 238drop table t1; 239create table t1 (a int not null, unique aa (a)); 240show create table t1; 241Table Create Table 242t1 CREATE TABLE `t1` ( 243 `a` int(11) NOT NULL, 244 UNIQUE KEY `aa` (`a`) 245) ENGINE=MyISAM DEFAULT CHARSET=latin1 246drop table t1; 247create table t1 (a int not null, primary key (a)); 248show create table t1; 249Table Create Table 250t1 CREATE TABLE `t1` ( 251 `a` int(11) NOT NULL, 252 PRIMARY KEY (`a`) 253) ENGINE=MyISAM DEFAULT CHARSET=latin1 254drop table t1; 255flush tables; 256show open tables; 257Database Table In_use Name_locked 258mysql general_log 0 0 259create table t1(n int); 260insert into t1 values (1); 261show open tables; 262Database Table In_use Name_locked 263test t1 0 0 264mysql general_log 0 0 265drop table t1; 266create 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; 267show create table t1; 268Table Create Table 269t1 CREATE TABLE `t1` ( 270 `a` int(11) NOT NULL, 271 `b` varchar(10) DEFAULT NULL, 272 KEY `b` (`b`) 273) 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' 274alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; 275show create table t1; 276Table Create Table 277t1 CREATE TABLE `t1` ( 278 `a` int(11) NOT NULL, 279 `b` varchar(10) DEFAULT NULL, 280 KEY `b` (`b`) 281) 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' 282ALTER 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; 283show create table t1; 284Table Create Table 285t1 CREATE TABLE `t1` ( 286 `a` int(11) NOT NULL, 287 `b` varchar(10) DEFAULT NULL, 288 KEY `b` (`b`) 289) ENGINE=MyISAM DEFAULT CHARSET=latin1 290drop table t1; 291create 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)); 292show columns from t1; 293Field Type Null Key Default Extra 294a decimal(9,2) YES NULL 295b decimal(9,0) YES NULL 296e double(9,2) YES NULL 297f double(5,0) YES NULL 298h float(3,2) YES NULL 299i float(3,0) YES NULL 300show full columns from t1; 301Field Type Collation Null Key Default Extra Privileges Comment 302a decimal(9,2) NULL YES NULL select,insert,update,references 303b decimal(9,0) NULL YES NULL select,insert,update,references 304e double(9,2) NULL YES NULL select,insert,update,references 305f double(5,0) NULL YES NULL select,insert,update,references 306h float(3,2) NULL YES NULL select,insert,update,references 307i float(3,0) NULL YES NULL select,insert,update,references 308drop table t1; 309SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 310Warnings: 311Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 312create table t1 ( 313type_bool bool not null default 0, 314type_tiny tinyint not null auto_increment primary key, 315type_short smallint(3), 316type_mediumint mediumint, 317type_bigint bigint, 318type_decimal decimal(5,2), 319type_numeric numeric(5,2), 320empty_char char(0), 321type_char char(2), 322type_varchar varchar(10), 323type_timestamp timestamp not null default current_timestamp on update current_timestamp, 324type_date date not null default '0000-00-00', 325type_time time not null default '00:00:00', 326type_datetime datetime not null default '0000-00-00 00:00:00', 327type_year year, 328type_enum enum ('red', 'green', 'blue'), 329type_set enum ('red', 'green', 'blue'), 330type_tinyblob tinyblob, 331type_blob blob, 332type_medium_blob mediumblob, 333type_long_blob longblob, 334index(type_short) 335) 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; 336show create table t1; 337Table Create Table 338t1 CREATE TABLE `t1` ( 339 `type_bool` tinyint(1) NOT NULL DEFAULT '0', 340 `type_tiny` tinyint(4) NOT NULL AUTO_INCREMENT, 341 `type_short` smallint(3) DEFAULT NULL, 342 `type_mediumint` mediumint(9) DEFAULT NULL, 343 `type_bigint` bigint(20) DEFAULT NULL, 344 `type_decimal` decimal(5,2) DEFAULT NULL, 345 `type_numeric` decimal(5,2) DEFAULT NULL, 346 `empty_char` char(0) DEFAULT NULL, 347 `type_char` char(2) DEFAULT NULL, 348 `type_varchar` varchar(10) DEFAULT NULL, 349 `type_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 350 `type_date` date NOT NULL DEFAULT '0000-00-00', 351 `type_time` time NOT NULL DEFAULT '00:00:00', 352 `type_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 353 `type_year` year(4) DEFAULT NULL, 354 `type_enum` enum('red','green','blue') DEFAULT NULL, 355 `type_set` enum('red','green','blue') DEFAULT NULL, 356 `type_tinyblob` tinyblob, 357 `type_blob` blob, 358 `type_medium_blob` mediumblob, 359 `type_long_blob` longblob, 360 PRIMARY KEY (`type_tiny`), 361 KEY `type_short` (`type_short`) 362) 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' 363insert into t1 (type_timestamp) values ("2003-02-07 10:00:01"); 364select * from t1; 365type_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 3660 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 367drop table t1; 368SET sql_mode = default; 369create table t1 (a int not null); 370create table t2 select max(a) from t1; 371show columns from t2; 372Field Type Null Key Default Extra 373max(a) int(11) YES NULL 374drop table t1,t2; 375create table t1 (c decimal, d double, f float, r real); 376show columns from t1; 377Field Type Null Key Default Extra 378c decimal(10,0) YES NULL 379d double YES NULL 380f float YES NULL 381r double YES NULL 382drop table t1; 383create table t1 (c decimal(3,3), d double(3,3), f float(3,3)); 384show columns from t1; 385Field Type Null Key Default Extra 386c decimal(3,3) YES NULL 387d double(3,3) YES NULL 388f float(3,3) YES NULL 389drop table t1; 390SET @old_sql_mode= @@sql_mode, sql_mode= ''; 391Warnings: 392Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 393SET @old_sql_quote_show_create= @@sql_quote_show_create, sql_quote_show_create= OFF; 394CREATE TABLE ```ab``cd``` (i INT); 395SHOW CREATE TABLE ```ab``cd```; 396Table Create Table 397`ab`cd` CREATE TABLE ```ab``cd``` ( 398 i int(11) DEFAULT NULL 399) ENGINE=MyISAM DEFAULT CHARSET=latin1 400DROP TABLE ```ab``cd```; 401CREATE TABLE ```ab````cd``` (i INT); 402SHOW CREATE TABLE ```ab````cd```; 403Table Create Table 404`ab``cd` CREATE TABLE ```ab````cd``` ( 405 i int(11) DEFAULT NULL 406) ENGINE=MyISAM DEFAULT CHARSET=latin1 407DROP TABLE ```ab````cd```; 408CREATE TABLE ```a` (i INT); 409SHOW CREATE TABLE ```a`; 410Table Create Table 411`a CREATE TABLE ```a` ( 412 i int(11) DEFAULT NULL 413) ENGINE=MyISAM DEFAULT CHARSET=latin1 414DROP TABLE ```a`; 415CREATE TABLE `a.1` (i INT); 416SHOW CREATE TABLE `a.1`; 417Table Create Table 418a.1 CREATE TABLE `a.1` ( 419 i int(11) DEFAULT NULL 420) ENGINE=MyISAM DEFAULT CHARSET=latin1 421DROP TABLE `a.1`; 422SET sql_mode= 'ANSI_QUOTES'; 423CREATE TABLE """a" (i INT); 424SHOW CREATE TABLE """a"; 425Table Create Table 426"a CREATE TABLE """a" ( 427 i int(11) DEFAULT NULL 428) ENGINE=MyISAM DEFAULT CHARSET=latin1 429DROP TABLE """a"; 430SET sql_mode= ''; 431SET sql_quote_show_create= OFF; 432CREATE TABLE t1 (i INT); 433SHOW CREATE TABLE t1; 434Table Create Table 435t1 CREATE TABLE t1 ( 436 i int(11) DEFAULT NULL 437) ENGINE=MyISAM DEFAULT CHARSET=latin1 438DROP TABLE t1; 439CREATE TABLE `table` (i INT); 440SHOW CREATE TABLE `table`; 441Table Create Table 442table CREATE TABLE `table` ( 443 i int(11) DEFAULT NULL 444) ENGINE=MyISAM DEFAULT CHARSET=latin1 445DROP TABLE `table`; 446SET sql_quote_show_create= @old_sql_quote_show_create; 447SET sql_mode= @old_sql_mode; 448Warnings: 449Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 450select @@max_heap_table_size; 451@@max_heap_table_size 4521048576 453CREATE TABLE t1 ( 454a int(11) default NULL, 455KEY a USING BTREE (a) 456) ENGINE=HEAP; 457CREATE TABLE t2 ( 458b int(11) default NULL, 459index(b) 460) ENGINE=HEAP; 461CREATE TABLE t3 ( 462a int(11) default NULL, 463b int(11) default NULL, 464KEY a USING BTREE (a), 465index(b) 466) ENGINE=HEAP; 467insert into t1 values (1),(2); 468insert into t2 values (1),(2); 469insert into t3 values (1,1),(2,2); 470show table status; 471Name 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 472t1 MEMORY 10 Fixed 2 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 473t2 MEMORY 10 Fixed 2 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 474t3 MEMORY 10 Fixed 2 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 475insert into t1 values (3),(4); 476insert into t2 values (3),(4); 477insert into t3 values (3,3),(4,4); 478show table status; 479Name 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 480t1 MEMORY 10 Fixed 4 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 481t2 MEMORY 10 Fixed 4 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 482t3 MEMORY 10 Fixed 4 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 483insert into t1 values (5); 484insert into t2 values (5); 485insert into t3 values (5,5); 486show table status; 487Name 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 488t1 MEMORY 10 Fixed 5 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 489t2 MEMORY 10 Fixed 5 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 490t3 MEMORY 10 Fixed 5 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 491delete from t1 where a=3; 492delete from t2 where b=3; 493delete from t3 where a=3; 494show table status; 495Name 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 496t1 MEMORY 10 Fixed 4 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 497t2 MEMORY 10 Fixed 4 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 498t3 MEMORY 10 Fixed 4 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 499truncate table t1; 500truncate table t2; 501truncate table t3; 502show table status; 503Name 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 504t1 MEMORY 10 Fixed 0 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 505t2 MEMORY 10 Fixed 0 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 506t3 MEMORY 10 Fixed 0 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 507insert into t1 values (5); 508insert into t2 values (5); 509insert into t3 values (5,5); 510show table status; 511Name 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 512t1 MEMORY 10 Fixed 1 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 513t2 MEMORY 10 Fixed 1 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 514t3 MEMORY 10 Fixed 1 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 515delete from t1 where a=5; 516delete from t2 where b=5; 517delete from t3 where a=5; 518show table status; 519Name 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 520t1 MEMORY 10 Fixed 0 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 521t2 MEMORY 10 Fixed 0 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 522t3 MEMORY 10 Fixed 0 # # # # # NULL # NULL NULL latin1_swedish_ci NULL 523drop table t1, t2, t3; 524create database mysqltest; 525show create database mysqltest; 526Database Create Database 527mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ 528create table mysqltest.t1(a int); 529insert into mysqltest.t1 values(1); 530set @orig_sql_mode = @@sql_mode; 531set sql_mode=(select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); 532Warnings: 533Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 534grant select on `mysqltest`.* to mysqltest_1@localhost; 535Warnings: 536Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 537grant usage on `mysqltest`.* to mysqltest_2@localhost; 538Warnings: 539Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 540grant drop on `mysqltest`.* to mysqltest_3@localhost; 541Warnings: 542Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 543set sql_mode= @orig_sql_mode; 544Warnings: 545Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 546select * from t1; 547a 5481 549show create database mysqltest; 550Database Create Database 551mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ 552drop table t1; 553ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' 554drop database mysqltest; 555ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' 556select * from mysqltest.t1; 557ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't1' 558show create database mysqltest; 559ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' 560drop table mysqltest.t1; 561ERROR 42000: DROP command denied to user 'mysqltest_2'@'localhost' for table 't1' 562drop database mysqltest; 563ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' 564select * from mysqltest.t1; 565ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1' 566show create database mysqltest; 567Database Create Database 568mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ 569drop table mysqltest.t1; 570drop database mysqltest; 571set names binary; 572delete from mysql.user 573where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; 574delete from mysql.db 575where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; 576flush privileges; 577CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; 578SHOW CREATE TABLE t1; 579Table Create Table 580t1 CREATE TABLE `t1` ( 581 `i` int(11) DEFAULT NULL, 582 KEY `i` (`i`) 583) ENGINE=MEMORY DEFAULT CHARSET=latin1 584DROP TABLE t1; 585CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY; 586SHOW CREATE TABLE t1; 587Table Create Table 588t1 CREATE TABLE `t1` ( 589 `i` int(11) DEFAULT NULL, 590 KEY `i` (`i`) USING HASH 591) ENGINE=MEMORY DEFAULT CHARSET=latin1 592DROP TABLE t1; 593CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY; 594SHOW CREATE TABLE t1; 595Table Create Table 596t1 CREATE TABLE `t1` ( 597 `i` int(11) DEFAULT NULL, 598 KEY `i` (`i`) USING BTREE 599) ENGINE=MEMORY DEFAULT CHARSET=latin1 600DROP TABLE t1; 601CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; 602SHOW CREATE TABLE t1; 603Table Create Table 604t1 CREATE TABLE `t1` ( 605 `i` int(11) DEFAULT NULL, 606 KEY `i` (`i`) 607) ENGINE=MyISAM DEFAULT CHARSET=latin1 608DROP TABLE t1; 609CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; 610SHOW CREATE TABLE t1; 611Table Create Table 612t1 CREATE TABLE `t1` ( 613 `i` int(11) DEFAULT NULL, 614 KEY `i` (`i`) USING BTREE 615) ENGINE=MyISAM DEFAULT CHARSET=latin1 616DROP TABLE t1; 617CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; 618SHOW CREATE TABLE t1; 619Table Create Table 620t1 CREATE TABLE `t1` ( 621 `i` int(11) DEFAULT NULL, 622 KEY `i` (`i`) 623) ENGINE=MyISAM DEFAULT CHARSET=latin1 624ALTER TABLE t1 ENGINE=MEMORY; 625SHOW CREATE TABLE t1; 626Table Create Table 627t1 CREATE TABLE `t1` ( 628 `i` int(11) DEFAULT NULL, 629 KEY `i` (`i`) 630) ENGINE=MEMORY DEFAULT CHARSET=latin1 631DROP TABLE t1; 632CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; 633SHOW CREATE TABLE t1; 634Table Create Table 635t1 CREATE TABLE `t1` ( 636 `i` int(11) DEFAULT NULL, 637 KEY `i` (`i`) USING BTREE 638) ENGINE=MyISAM DEFAULT CHARSET=latin1 639ALTER TABLE t1 ENGINE=MEMORY; 640SHOW CREATE TABLE t1; 641Table Create Table 642t1 CREATE TABLE `t1` ( 643 `i` int(11) DEFAULT NULL, 644 KEY `i` (`i`) USING BTREE 645) ENGINE=MEMORY DEFAULT CHARSET=latin1 646DROP TABLE t1; 647CREATE TABLE t1( 648field1 text NOT NULL, 649PRIMARY KEY(field1(1000)) 650); 651show index from t1; 652Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 653def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 1 0 63 654def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 655def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 63 656def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 657def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 6 N 1 0 63 658def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 0 0 63 659def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 660def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 4 Y 32768 0 63 661def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 0 0 63 662def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 63 663def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 63 664def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 63 665def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 63 666Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 667t1 0 PRIMARY 1 field1 A 0 1000 NULL BTREE 668drop table t1; 669create table t1 ( 670c1 int NOT NULL, 671c2 int NOT NULL, 672PRIMARY KEY USING HASH (c1), 673INDEX USING BTREE(c2) 674); 675SHOW CREATE TABLE t1; 676Table Create Table 677t1 CREATE TABLE `t1` ( 678 `c1` int(11) NOT NULL, 679 `c2` int(11) NOT NULL, 680 PRIMARY KEY (`c1`) USING HASH, 681 KEY `c2` (`c2`) USING BTREE 682) ENGINE=MyISAM DEFAULT CHARSET=latin1 683DROP TABLE t1; 684flush tables; 685SHOW TABLE STATUS like 't1'; 686Name 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 687t1 NULL NULL NULL NULL # # # # NULL NULL # NULL NULL NULL NULL NULL Incorrect information in file: './test/t1.frm' 688Warnings: 689Warning 1033 Incorrect information in file: './test/t1.frm' 690show create table t1; 691ERROR HY000: Incorrect information in file: './test/t1.frm' 692drop table if exists t1; 693 694# Bug#12183 SHOW OPEN TABLES behavior doesn't match grammar. 695 696DROP DATABASE IF EXISTS mysqltest1; 697CREATE DATABASE mysqltest1; 698use mysqltest1; 699 700CREATE TABLE t1(a INT); 701CREATE TABLE t2(a INT); 702 703FLUSH TABLES; 704 705SELECT 1 FROM t1; 7061 707SELECT 1 FROM t2; 7081 709 710SHOW OPEN TABLES FROM mysqltest1; 711Database Table In_use Name_locked 712mysqltest1 t1 0 0 713mysqltest1 t2 0 0 714 715SHOW OPEN TABLES FROM mysqltest1 LIKE 'z%'; 716Database Table In_use Name_locked 717 718SHOW OPEN TABLES FROM mysqltest1 LIKE 't1%'; 719Database Table In_use Name_locked 720mysqltest1 t1 0 0 721 722SHOW OPEN TABLES FROM mysqltest1 LIKE '%1%'; 723Database Table In_use Name_locked 724mysqltest1 t1 0 0 725 726FLUSH TABLES; 727 728DROP DATABASE mysqltest1; 729use test; 730 731SHOW TABLES FROM non_existing_database; 732ERROR 42000: Unknown database 'non_existing_database' 733End of 4.1 tests 734DROP VIEW IF EXISTS v1; 735DROP PROCEDURE IF EXISTS p1; 736CREATE VIEW v1 AS SELECT 1; 737SHOW CREATE VIEW v1; 738View Create View character_set_client collation_connection 739v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` binary binary 740DROP VIEW v1; 741CREATE VIEW v1 AS SELECT SQL_CACHE 1; 742Warnings: 743Warning 1681 'SQL_CACHE' is deprecated and will be removed in a future release. 744SHOW CREATE VIEW v1; 745View Create View character_set_client collation_connection 746v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` binary binary 747Warnings: 748Warning 1681 'SQL_CACHE' is deprecated and will be removed in a future release. 749DROP VIEW v1; 750CREATE VIEW v1 AS SELECT SQL_NO_CACHE 1; 751Warnings: 752Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 753SHOW CREATE VIEW v1; 754View Create View character_set_client collation_connection 755v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache 1 AS `1` binary binary 756Warnings: 757Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 758DROP VIEW v1; 759CREATE VIEW v1 AS SELECT NOW(); 760SHOW CREATE VIEW v1; 761View Create View character_set_client collation_connection 762v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `NOW()` binary binary 763DROP VIEW v1; 764CREATE VIEW v1 AS SELECT SQL_CACHE NOW(); 765Warnings: 766Warning 1681 'SQL_CACHE' is deprecated and will be removed in a future release. 767SHOW CREATE VIEW v1; 768View Create View character_set_client collation_connection 769v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache now() AS `NOW()` binary binary 770Warnings: 771Warning 1681 'SQL_CACHE' is deprecated and will be removed in a future release. 772DROP VIEW v1; 773CREATE VIEW v1 AS SELECT SQL_NO_CACHE NOW(); 774Warnings: 775Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 776SHOW CREATE VIEW v1; 777View Create View character_set_client collation_connection 778v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache now() AS `NOW()` binary binary 779Warnings: 780Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 781DROP VIEW v1; 782CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW(); 783ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE 784CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW(); 785ERROR HY000: Incorrect usage of SQL_NO_CACHE and SQL_CACHE 786CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW(); 787ERROR HY000: Incorrect usage of SQL_CACHE and SQL_NO_CACHE 788CREATE PROCEDURE p1() 789BEGIN 790SET @s= 'CREATE VIEW v1 AS SELECT SQL_CACHE 1'; 791PREPARE stmt FROM @s; 792EXECUTE stmt; 793DROP PREPARE stmt; 794END | 795CALL p1(); 796SHOW CREATE VIEW v1; 797View Create View character_set_client collation_connection 798v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` binary binary 799Warnings: 800Warning 1681 'SQL_CACHE' is deprecated and will be removed in a future release. 801DROP PROCEDURE p1; 802DROP VIEW v1; 803SHOW TABLES FROM no_such_database; 804ERROR 42000: Unknown database 'no_such_database' 805SHOW COLUMNS FROM no_such_table; 806ERROR 42S02: Table 'test.no_such_table' doesn't exist 807flush status; 808show status like 'slow_queries'; 809Variable_name Value 810Slow_queries 0 811show tables; 812Tables_in_test 813show status like 'slow_queries'; 814Variable_name Value 815Slow_queries 0 816select 1 from information_schema.tables limit 1; 8171 8181 819show status like 'slow_queries'; 820Variable_name Value 821Slow_queries 1 822create table t1 (a int); 823create trigger tr1 before insert on t1 for each row 824begin 825end; 826create view v1 as select a from t1; 827create procedure p1() 828begin 829end; 830create function f1() 831returns int 832return 0; 833create event e1 on schedule every 1 year starts now() 834ends date_add(now(), interval 5 hour) do 835begin 836end; 837flush status; 838show databases; 839show tables; 840show events; 841show table status; 842show open tables; 843show plugins; 844show columns in t1; 845show slave hosts; 846show keys in t1; 847show storage engines; 848show privileges; 849show count(*) warnings; 850show count(*) errors; 851show warnings; 852show status; 853show processlist; 854show variables; 855show charset; 856show collation; 857show grants; 858show create database test; 859show create table t1; 860show create view v1; 861show master status; 862show slave status; 863show create procedure p1; 864show create function f1; 865show create trigger tr1; 866show procedure status; 867show create event e1; 868show status like 'slow_queries'; 869Variable_name Value 870Slow_queries 0 871drop view v1; 872drop table t1; 873drop procedure p1; 874drop function f1; 875drop event e1; 876DROP DATABASE IF EXISTS mysqltest1; 877DROP TABLE IF EXISTS t1; 878DROP VIEW IF EXISTS v1; 879DROP PROCEDURE IF EXISTS p1; 880DROP FUNCTION IF EXISTS f1; 881CREATE DATABASE mysqltest1; 882CREATE TABLE t1(c INT NOT NULL PRIMARY KEY); 883CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; 884CREATE VIEW v1 AS SELECT 1; 885CREATE PROCEDURE p1() SELECT 1; 886CREATE FUNCTION f1() RETURNS INT RETURN 1; 887set names utf8; 888-- Here we enable metadata just to check that the collation of the 889-- resultset is non-binary for string type. This should be changed 890-- after Bug#29394 is implemented. 891---------------------------------------------------------------- 892SHOW CHARACTER SET LIKE 'utf8'; 893Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 894def information_schema CHARACTER_SETS CHARACTER_SETS CHARACTER_SET_NAME Charset 253 96 4 N 1 0 33 895def information_schema CHARACTER_SETS CHARACTER_SETS DESCRIPTION Description 253 180 13 N 1 0 33 896def information_schema CHARACTER_SETS CHARACTER_SETS DEFAULT_COLLATE_NAME Default collation 253 96 15 N 1 0 33 897def information_schema CHARACTER_SETS CHARACTER_SETS MAXLEN Maxlen 8 3 1 N 32769 0 63 898Charset Description Default collation Maxlen 899utf8 UTF-8 Unicode utf8_general_ci 3 900---------------------------------------------------------------- 901SHOW COLLATION LIKE 'latin1_bin'; 902Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 903def information_schema COLLATIONS COLLATIONS COLLATION_NAME Collation 253 96 10 N 1 0 33 904def information_schema COLLATIONS COLLATIONS CHARACTER_SET_NAME Charset 253 96 6 N 1 0 33 905def information_schema COLLATIONS COLLATIONS ID Id 8 11 2 N 32769 0 63 906def information_schema COLLATIONS COLLATIONS IS_DEFAULT Default 253 9 0 N 1 0 33 907def information_schema COLLATIONS COLLATIONS IS_COMPILED Compiled 253 9 3 N 1 0 33 908def information_schema COLLATIONS COLLATIONS SORTLEN Sortlen 8 3 1 N 32769 0 63 909Collation Charset Id Default Compiled Sortlen 910latin1_bin latin1 47 Yes 1 911---------------------------------------------------------------- 912SHOW CREATE DATABASE mysqltest1; 913Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 914def Database 253 192 10 N 1 31 33 915def Create Database 253 3072 69 N 1 31 33 916Database Create Database 917mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 */ 918---------------------------------------------------------------- 919SHOW DATABASES LIKE 'mysqltest1'; 920Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 921def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (mysqltest1) 253 192 10 N 1 0 33 922Database (mysqltest1) 923mysqltest1 924---------------------------------------------------------------- 925SHOW CREATE TABLE t1; 926Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 927def Table 253 192 2 N 1 31 33 928def Create Table 253 3072 102 N 1 31 33 929Table Create Table 930t1 CREATE TABLE `t1` ( 931 `c` int(11) NOT NULL, 932 PRIMARY KEY (`c`) 933) ENGINE=MyISAM DEFAULT CHARSET=latin1 934---------------------------------------------------------------- 935SHOW INDEX FROM t1; 936Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 937def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 192 2 N 1 0 33 938def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 939def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 192 7 N 1 0 33 940def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 941def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 192 1 N 1 0 33 942def information_schema STATISTICS STATISTICS COLLATION Collation 253 3 1 Y 0 0 33 943def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 944def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 945def information_schema STATISTICS STATISTICS PACKED Packed 253 30 0 Y 0 0 33 946def information_schema STATISTICS STATISTICS NULLABLE Null 253 9 0 N 1 0 33 947def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 48 5 N 1 0 33 948def information_schema STATISTICS STATISTICS COMMENT Comment 253 48 0 Y 0 0 33 949def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 3072 0 N 1 0 33 950Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 951t1 0 PRIMARY 1 c A 0 NULL NULL BTREE 952---------------------------------------------------------------- 953SELECT 954TABLE_CATALOG, 955TABLE_SCHEMA, 956TABLE_NAME, 957TABLE_TYPE, 958ENGINE, 959ROW_FORMAT, 960TABLE_COLLATION, 961CREATE_OPTIONS, 962TABLE_COMMENT 963FROM INFORMATION_SCHEMA.TABLES 964WHERE table_name = 't1'; 965Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 966def information_schema TABLES TABLES TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 967def information_schema TABLES TABLES TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 968def information_schema TABLES TABLES TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 969def information_schema TABLES TABLES TABLE_TYPE TABLE_TYPE 253 192 10 N 1 0 33 970def information_schema TABLES TABLES ENGINE ENGINE 253 192 6 Y 0 0 33 971def information_schema TABLES TABLES ROW_FORMAT ROW_FORMAT 253 60 5 Y 0 0 33 972def information_schema TABLES TABLES TABLE_COLLATION TABLE_COLLATION 253 96 17 Y 0 0 33 973def information_schema TABLES TABLES CREATE_OPTIONS CREATE_OPTIONS 253 765 0 Y 0 0 33 974def information_schema TABLES TABLES TABLE_COMMENT TABLE_COMMENT 253 6144 0 N 1 0 33 975TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION CREATE_OPTIONS TABLE_COMMENT 976def test t1 BASE TABLE MyISAM Fixed latin1_swedish_ci 977---------------------------------------------------------------- 978SELECT 979TABLE_CATALOG, 980TABLE_SCHEMA, 981TABLE_NAME, 982COLUMN_NAME, 983COLUMN_DEFAULT, 984IS_NULLABLE, 985DATA_TYPE, 986CHARACTER_SET_NAME, 987COLLATION_NAME, 988COLUMN_TYPE, 989COLUMN_KEY, 990EXTRA, 991PRIVILEGES, 992COLUMN_COMMENT 993FROM INFORMATION_SCHEMA.COLUMNS 994WHERE table_name = 't1'; 995Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 996def information_schema COLUMNS COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 997def information_schema COLUMNS COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 998def information_schema COLUMNS COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 999def information_schema COLUMNS COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 1 0 33 1000def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589815 0 Y 16 0 33 1001def information_schema COLUMNS COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 1 0 33 1002def information_schema COLUMNS COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 1 0 33 1003def information_schema COLUMNS COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 96 0 Y 0 0 33 1004def information_schema COLUMNS COLUMNS COLLATION_NAME COLLATION_NAME 253 96 0 Y 0 0 33 1005def information_schema COLUMNS COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 1006def information_schema COLUMNS COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 1007def information_schema COLUMNS COLUMNS EXTRA EXTRA 253 90 0 N 1 0 33 1008def information_schema COLUMNS COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 1009def information_schema COLUMNS COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 3072 0 N 1 0 33 1010TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT 1011def test t1 c NULL NO int NULL NULL int(11) PRI select,insert,update,references 1012---------------------------------------------------------------- 1013SHOW TABLES LIKE 't1'; 1014Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1015def information_schema TABLE_NAMES TABLE_NAMES TABLE_NAME Tables_in_test (t1) 253 192 2 N 1 0 33 1016Tables_in_test (t1) 1017t1 1018---------------------------------------------------------------- 1019SHOW COLUMNS FROM t1; 1020Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1021def information_schema COLUMNS COLUMNS COLUMN_NAME Field 253 192 1 N 1 0 33 1022def information_schema COLUMNS COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 1023def information_schema COLUMNS COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 1024def information_schema COLUMNS COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 1025def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 1026def information_schema COLUMNS COLUMNS EXTRA Extra 253 90 0 N 1 0 33 1027Field Type Null Key Default Extra 1028c int(11) NO PRI NULL 1029---------------------------------------------------------------- 1030SHOW TRIGGERS LIKE 't1'; 1031Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1032def information_schema TRIGGERS TRIGGERS TRIGGER_NAME Trigger 253 192 5 N 1 0 33 1033def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION Event 253 18 6 N 1 0 33 1034def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 1 0 33 1035def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 17 0 33 1036def information_schema TRIGGERS TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 1037def information_schema TRIGGERS TRIGGERS CREATED Created 12 22 22 Y 128 2 63 1038def information_schema TRIGGERS TRIGGERS SQL_MODE sql_mode 253 24576 137 N 1 0 33 1039def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 279 14 N 1 0 33 1040def information_schema TRIGGERS TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 1 0 33 1041def information_schema TRIGGERS TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 1 0 33 1042def information_schema TRIGGERS TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 1043Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 1044t1_bi INSERT t1 SET @a = 1 BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost binary binary latin1_swedish_ci 1045---------------------------------------------------------------- 1046SELECT 1047TRIGGER_CATALOG, 1048TRIGGER_SCHEMA, 1049TRIGGER_NAME, 1050EVENT_MANIPULATION, 1051EVENT_OBJECT_CATALOG, 1052EVENT_OBJECT_SCHEMA, 1053EVENT_OBJECT_TABLE, 1054ACTION_CONDITION, 1055ACTION_STATEMENT, 1056ACTION_ORIENTATION, 1057ACTION_TIMING, 1058ACTION_REFERENCE_OLD_TABLE, 1059ACTION_REFERENCE_NEW_TABLE, 1060ACTION_REFERENCE_OLD_ROW, 1061ACTION_REFERENCE_NEW_ROW, 1062SQL_MODE, 1063DEFINER 1064FROM INFORMATION_SCHEMA.TRIGGERS 1065WHERE trigger_name = 't1_bi'; 1066Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1067def information_schema TRIGGERS TRIGGERS TRIGGER_CATALOG TRIGGER_CATALOG 253 1536 3 N 1 0 33 1068def information_schema TRIGGERS TRIGGERS TRIGGER_SCHEMA TRIGGER_SCHEMA 253 192 4 N 1 0 33 1069def information_schema TRIGGERS TRIGGERS TRIGGER_NAME TRIGGER_NAME 253 192 5 N 1 0 33 1070def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION EVENT_MANIPULATION 253 18 6 N 1 0 33 1071def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_CATALOG EVENT_OBJECT_CATALOG 253 1536 3 N 1 0 33 1072def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_SCHEMA 253 192 4 N 1 0 33 1073def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE EVENT_OBJECT_TABLE 253 192 2 N 1 0 33 1074def information_schema TRIGGERS TRIGGERS ACTION_CONDITION ACTION_CONDITION 252 589815 0 Y 16 0 33 1075def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT ACTION_STATEMENT 252 589815 10 N 17 0 33 1076def information_schema TRIGGERS TRIGGERS ACTION_ORIENTATION ACTION_ORIENTATION 253 27 3 N 1 0 33 1077def information_schema TRIGGERS TRIGGERS ACTION_TIMING ACTION_TIMING 253 18 6 N 1 0 33 1078def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_OLD_TABLE 253 192 0 Y 0 0 33 1079def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_NEW_TABLE 253 192 0 Y 0 0 33 1080def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 1 0 33 1081def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 1 0 33 1082def information_schema TRIGGERS TRIGGERS SQL_MODE SQL_MODE 253 24576 137 N 1 0 33 1083def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 279 14 N 1 0 33 1084TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER 1085def test t1_bi INSERT def test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost 1086---------------------------------------------------------------- 1087SHOW CREATE VIEW v1; 1088Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1089def View 253 192 2 N 1 31 33 1090def Create View 253 3072 103 N 1 31 33 1091def character_set_client 253 96 6 N 1 31 33 1092def collation_connection 253 96 6 N 1 31 33 1093View Create View character_set_client collation_connection 1094v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` binary binary 1095---------------------------------------------------------------- 1096SELECT * 1097FROM INFORMATION_SCHEMA.VIEWS 1098WHERE table_name = 'v1'; 1099Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1100def information_schema VIEWS VIEWS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 1101def information_schema VIEWS VIEWS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 1102def information_schema VIEWS VIEWS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 1103def information_schema VIEWS VIEWS VIEW_DEFINITION VIEW_DEFINITION 252 589815 15 N 17 0 33 1104def information_schema VIEWS VIEWS CHECK_OPTION CHECK_OPTION 253 24 4 N 1 0 33 1105def information_schema VIEWS VIEWS IS_UPDATABLE IS_UPDATABLE 253 9 2 N 1 0 33 1106def information_schema VIEWS VIEWS DEFINER DEFINER 253 279 14 N 1 0 33 1107def information_schema VIEWS VIEWS SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 1108def information_schema VIEWS VIEWS CHARACTER_SET_CLIENT CHARACTER_SET_CLIENT 253 96 6 N 1 0 33 1109def information_schema VIEWS VIEWS COLLATION_CONNECTION COLLATION_CONNECTION 253 96 6 N 1 0 33 1110TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION 1111def test v1 select 1 AS `1` NONE NO root@localhost DEFINER binary binary 1112---------------------------------------------------------------- 1113SHOW CREATE PROCEDURE p1; 1114Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1115def Procedure 253 192 2 N 1 31 33 1116def sql_mode 253 411 137 N 1 31 33 1117def Create Procedure 253 3072 59 Y 0 31 33 1118def character_set_client 253 96 6 N 1 31 33 1119def collation_connection 253 96 6 N 1 31 33 1120def Database Collation 253 96 17 N 1 31 33 1121Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1122p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 1123SELECT 1 binary binary latin1_swedish_ci 1124---------------------------------------------------------------- 1125SELECT 1126SPECIFIC_NAME, 1127ROUTINE_CATALOG, 1128ROUTINE_SCHEMA, 1129ROUTINE_NAME, 1130ROUTINE_TYPE, 1131DTD_IDENTIFIER, 1132ROUTINE_BODY, 1133ROUTINE_DEFINITION, 1134EXTERNAL_NAME, 1135EXTERNAL_LANGUAGE, 1136PARAMETER_STYLE, 1137IS_DETERMINISTIC, 1138SQL_DATA_ACCESS, 1139SQL_PATH, 1140SECURITY_TYPE, 1141SQL_MODE, 1142ROUTINE_COMMENT, 1143DEFINER 1144FROM INFORMATION_SCHEMA.ROUTINES 1145WHERE routine_name = 'p1'; 1146Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1147def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 1148def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 1 0 33 1149def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 1150def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 1151def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 27 9 N 1 0 33 1152def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 0 Y 16 0 33 1153def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 1154def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 1155def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 1156def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 1157def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 1158def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 1159def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 1160def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 1161def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 1162def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 137 N 1 0 33 1163def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 1164def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 279 14 N 1 0 33 1165SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER 1166p1 def test p1 PROCEDURE NULL SQL SELECT 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost 1167---------------------------------------------------------------- 1168SHOW CREATE FUNCTION f1; 1169Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1170def Function 253 192 2 N 1 31 33 1171def sql_mode 253 411 137 N 1 31 33 1172def Create Function 253 3072 74 Y 0 31 33 1173def character_set_client 253 96 6 N 1 31 33 1174def collation_connection 253 96 6 N 1 31 33 1175def Database Collation 253 96 17 N 1 31 33 1176Function sql_mode Create Function character_set_client collation_connection Database Collation 1177f1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) 1178RETURN 1 binary binary latin1_swedish_ci 1179---------------------------------------------------------------- 1180SELECT 1181SPECIFIC_NAME, 1182ROUTINE_CATALOG, 1183ROUTINE_SCHEMA, 1184ROUTINE_NAME, 1185ROUTINE_TYPE, 1186DTD_IDENTIFIER, 1187ROUTINE_BODY, 1188ROUTINE_DEFINITION, 1189EXTERNAL_NAME, 1190EXTERNAL_LANGUAGE, 1191PARAMETER_STYLE, 1192IS_DETERMINISTIC, 1193SQL_DATA_ACCESS, 1194SQL_PATH, 1195SECURITY_TYPE, 1196SQL_MODE, 1197ROUTINE_COMMENT, 1198DEFINER 1199FROM INFORMATION_SCHEMA.ROUTINES 1200WHERE routine_name = 'f1'; 1201Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1202def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 1203def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 1 0 33 1204def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 1205def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 1206def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 27 8 N 1 0 33 1207def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 7 Y 16 0 33 1208def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 1209def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 1210def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 1211def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 1212def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 1213def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 1214def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 1215def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 1216def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 1217def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 137 N 1 0 33 1218def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 1219def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 279 14 N 1 0 33 1220SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER 1221f1 def test f1 FUNCTION int(11) SQL RETURN 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost 1222---------------------------------------------------------------- 1223DROP DATABASE mysqltest1; 1224DROP TABLE t1; 1225DROP VIEW v1; 1226DROP PROCEDURE p1; 1227DROP FUNCTION f1; 1228set names koi8r; 1229DROP DATABASE IF EXISTS mysqltest1; 1230CREATE DATABASE mysqltest1; 1231use mysqltest1; 1232CREATE TABLE t1(�������1 INT); 1233 1234---> Dumping mysqltest1 to outfile1 1235 1236 1237DROP DATABASE mysqltest1; 1238 1239 1240---> Restoring mysqltest1... 1241SHOW CREATE TABLE mysqltest1.t1; 1242Table Create Table 1243t1 CREATE TABLE `t1` ( 1244 `�������1` int(11) DEFAULT NULL 1245) ENGINE=MyISAM DEFAULT CHARSET=latin1 1246DROP DATABASE mysqltest1; 1247use test; 1248flush status; 1249show variables like "log_queries_not_using_indexes"; 1250Variable_name Value 1251log_queries_not_using_indexes ON 1252select 1 from information_schema.tables limit 1; 12531 12541 1255show status like 'slow_queries'; 1256Variable_name Value 1257Slow_queries 1 1258set global log_queries_not_using_indexes=OFF; 1259show variables like "log_queries_not_using_indexes"; 1260Variable_name Value 1261log_queries_not_using_indexes OFF 1262select 1 from information_schema.tables limit 1; 12631 12641 1265show status like 'slow_queries'; 1266Variable_name Value 1267Slow_queries 1 1268set global log_queries_not_using_indexes=ON; 1269show variables like "log_queries_not_using_indexes"; 1270Variable_name Value 1271log_queries_not_using_indexes ON 1272select 1 from information_schema.tables limit 1; 12731 12741 1275show status like 'slow_queries'; 1276Variable_name Value 1277Slow_queries 2 1278show variables like 'myisam_recover_options'; 1279Variable_name Value 1280myisam_recover_options OFF 1281CREATE TABLE t1 ( 1282Codigo int(10) unsigned NOT NULL auto_increment, 1283Nombre varchar(255) default NULL, 1284Telefono varchar(255) default NULL, 1285Observaciones longtext, 1286Direccion varchar(255) default NULL, 1287Dni varchar(255) default NULL, 1288CP int(11) default NULL, 1289Provincia varchar(255) default NULL, 1290Poblacion varchar(255) default NULL, 1291PRIMARY KEY (Codigo) 1292) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; 1293show create table t1; 1294Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1295def Table 253 64 2 N 1 31 7 1296def Create Table 253 1024 445 N 1 31 7 1297Table Create Table 1298t1 CREATE TABLE `t1` ( 1299 `Codigo` int(10) unsigned NOT NULL AUTO_INCREMENT, 1300 `Nombre` varchar(255) DEFAULT NULL, 1301 `Telefono` varchar(255) DEFAULT NULL, 1302 `Observaciones` longtext, 1303 `Direccion` varchar(255) DEFAULT NULL, 1304 `Dni` varchar(255) DEFAULT NULL, 1305 `CP` int(11) DEFAULT NULL, 1306 `Provincia` varchar(255) DEFAULT NULL, 1307 `Poblacion` varchar(255) DEFAULT NULL, 1308 PRIMARY KEY (`Codigo`) 1309) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 1310drop table t1; 1311End of 5.0 tests 1312create database mysqltest; 1313show create database mysqltest; 1314Database Create Database 1315mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ 1316create table mysqltest.t1(a int); 1317insert into mysqltest.t1 values(1); 1318set @orig_sql_mode = @@sql_mode; 1319set sql_mode=''; 1320Warnings: 1321Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 1322grant select on `mysqltest`.`t1` to mysqltest_4@localhost; 1323Warnings: 1324Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1325set sql_mode= @orig_sql_mode; 1326Warnings: 1327Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 1328show create database mysqltest; 1329Database Create Database 1330mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ 1331delete from mysql.user where user='mysqltest_4'; 1332delete from mysql.db where user='mysqltest_4'; 1333delete from mysql.tables_priv where user='mysqltest_4'; 1334flush privileges; 1335drop database mysqltest; 1336show plugins; 1337create database `mysqlttest\1`; 1338create table `mysqlttest\1`.`a\b` (a int); 1339show tables from `mysqlttest\1`; 1340Tables_in_mysqlttest\1 1341a\b 1342show fields from `mysqlttest\1`.`a\b`; 1343Field Type Null Key Default Extra 1344a int(11) YES NULL 1345show columns from `a\b` from `mysqlttest\1`; 1346Field Type Null Key Default Extra 1347a int(11) YES NULL 1348show keys from `mysqlttest\1`.`a\b`; 1349Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1350drop table `mysqlttest\1`.`a\b`; 1351drop database `mysqlttest\1`; 1352show engine foobar status; 1353ERROR 42000: Unknown storage engine 'foobar' 1354show engine foobar logs; 1355ERROR 42000: Unknown storage engine 'foobar' 1356show engine foobar mutex; 1357ERROR 42000: Unknown storage engine 'foobar' 1358show engine mutex status; 1359ERROR 42000: Unknown storage engine 'mutex' 1360show engine csv status; 1361Type Name Status 1362show engine csv logs; 1363Type Name Status 1364show engine csv mutex; 1365Type Name Status 1366set names utf8; 1367drop table if exists `été`; 1368create table `été` (field1 int); 1369show full tables; 1370Tables_in_test Table_type 1371été BASE TABLE 1372drop table `été`; 1373set names latin1; 1374# 1375# Bug#4374 SHOW TABLE STATUS FROM ignores collation_connection 1376# Character set: Latin-1 (ISO-8859-1) 1377# 1378SET NAMES latin1; 1379CREATE DATABASE `�`; 1380CREATE TABLE `�`.`�` (a int) ENGINE=Memory; 1381SHOW TABLE STATUS FROM `�` LIKE '�'; 1382Name 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 1383� MEMORY 10 Fixed 0 # # # # 0 NULL # NULL NULL latin1_swedish_ci NULL 1384DROP DATABASE `�`; 1385show columns from `#mysql50#????????`; 1386Got one of the listed errors 1387call mtr.add_suppression("Can.t find file: '.\\\\test\\\\\\?{8}.frm'"); 1388DROP TABLE IF EXISTS t1; 1389DROP PROCEDURE IF EXISTS p1; 1390CREATE TABLE t1(c1 INT); 1391CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; 1392SHOW CREATE TRIGGER t1_bi; 1393Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1394t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1395CREATE PROCEDURE p1() SHOW CREATE TRIGGER t1_bi; 1396CALL p1(); 1397Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1398t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1399CALL p1(); 1400Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1401t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1402CALL p1(); 1403Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1404t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1405CALL p1(); 1406Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1407t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1408CALL p1(); 1409Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1410t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1411CALL p1(); 1412Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1413t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1414CALL p1(); 1415Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1416t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1417CALL p1(); 1418Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1419t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1420CALL p1(); 1421Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1422t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1423CALL p1(); 1424Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1425t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1426PREPARE stmt1 FROM 'SHOW CREATE TRIGGER t1_bi'; 1427EXECUTE stmt1; 1428Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1429t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1430EXECUTE stmt1; 1431Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1432t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1433EXECUTE stmt1; 1434Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1435t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1436EXECUTE stmt1; 1437Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1438t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1439EXECUTE stmt1; 1440Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1441t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1442EXECUTE stmt1; 1443Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1444t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1445EXECUTE stmt1; 1446Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1447t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1448EXECUTE stmt1; 1449Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1450t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1451EXECUTE stmt1; 1452Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1453t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1454EXECUTE stmt1; 1455Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1456t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # 1457DROP TABLE t1; 1458DROP PROCEDURE p1; 1459DEALLOCATE PREPARE stmt1; 1460set names koi8r; 1461DROP VIEW IF EXISTS v1; 1462DROP PROCEDURE IF EXISTS p1; 1463DROP FUNCTION IF EXISTS f1; 1464DROP TABLE IF EXISTS t1; 1465DROP EVENT IF EXISTS ev1; 1466CREATE VIEW v1 AS SELECT '����' AS test; 1467CREATE PROCEDURE p1() SELECT '����' AS test; 1468CREATE FUNCTION f1() RETURNS CHAR(10) RETURN '����'; 1469CREATE TABLE t1(c1 CHAR(10)); 1470CREATE TRIGGER t1_bi BEFORE INSERT ON t1 1471FOR EACH ROW 1472SET NEW.c1 = '����'; 1473CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT '����' AS test; 1474set names utf8; 1475SHOW CREATE VIEW v1; 1476View Create View character_set_client collation_connection 1477v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `test` koi8r koi8r_general_ci 1478SHOW CREATE PROCEDURE p1; 1479Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1480p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 1481SELECT 'тест' AS test koi8r koi8r_general_ci latin1_swedish_ci 1482SHOW CREATE FUNCTION f1; 1483Function sql_mode Create Function character_set_client collation_connection Database Collation 1484f1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1 1485RETURN 'тест' koi8r koi8r_general_ci latin1_swedish_ci 1486SHOW CREATE TRIGGER t1_bi; 1487Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1488t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 1489FOR EACH ROW 1490SET NEW.c1 = 'тест' koi8r koi8r_general_ci latin1_swedish_ci # 1491SHOW CREATE EVENT ev1; 1492Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation 1493ev1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 'тест' AS test koi8r koi8r_general_ci latin1_swedish_ci 1494DROP VIEW v1; 1495DROP PROCEDURE p1; 1496DROP FUNCTION f1; 1497DROP TABLE t1; 1498DROP EVENT ev1; 1499SHOW STORAGE ENGINES; 1500CREATE USER test_u@localhost; 1501GRANT PROCESS ON *.* TO test_u@localhost; 1502SHOW ENGINE MYISAM MUTEX; 1503SHOW ENGINE MYISAM STATUS; 1504DROP USER test_u@localhost; 1505# 1506# Bug #48985: show create table crashes if previous access to the table 1507# was killed 1508# 1509SHOW CREATE TABLE non_existent; 1510ERROR 42S02: Table 'test.non_existent' doesn't exist 1511End of 5.1 tests 1512# 1513# Bug#52593 SHOW CREATE TABLE is blocked if table is locked 1514# for write by another connection 1515DROP TABLE IF EXISTS t1; 1516CREATE TABLE t1 (i INT PRIMARY KEY); 1517LOCK TABLE t1 WRITE; 1518# Switching to connection 'con1'. 1519# This statement used to be blocked. 1520SHOW CREATE TABLE t1; 1521Table Create Table 1522t1 CREATE TABLE `t1` ( 1523 `i` int(11) NOT NULL, 1524 PRIMARY KEY (`i`) 1525) ENGINE=MyISAM DEFAULT CHARSET=latin1 1526# Switching to connection 'default'. 1527UNLOCK TABLES; 1528DROP TABLE t1; 1529# 1530# Bug#54905 Connection with WRITE lock cannot ALTER table due to 1531# concurrent SHOW CREATE 1532# 1533DROP TABLE IF EXISTS t1; 1534CREATE TABLE t1(a INT); 1535# Connection con1 1536LOCK TABLE t1 WRITE; 1537# Connection default 1538START TRANSACTION; 1539SHOW CREATE TABLE t1; 1540Table Create Table 1541t1 CREATE TABLE `t1` ( 1542 `a` int(11) DEFAULT NULL 1543) ENGINE=MyISAM DEFAULT CHARSET=latin1 1544# Connection con1 1545ALTER TABLE t1 CHARACTER SET = utf8; 1546UNLOCK TABLES; 1547# Connection default 1548COMMIT; 1549DROP TABLE t1; 1550# 1551# Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. 1552# 1553DROP TABLE IF EXISTS t1; 1554CREATE TABLE t1 (a INT); 1555CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; 1556# Test 1: SHOW CREATE TRIGGER with WRITE locked table. 1557# Connection con1 1558LOCK TABLE t1 WRITE; 1559# Connection default 1560SHOW CREATE TRIGGER t1_bi; 1561Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1562t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci # 1563# Connection con1 1564UNLOCK TABLES; 1565# Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction 1566# Connection default 1567START TRANSACTION; 1568SHOW CREATE TRIGGER t1_bi; 1569Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1570t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci # 1571# Connection con1 1572ALTER TABLE t1 CHARACTER SET = utf8; 1573# Connection default 1574COMMIT; 1575DROP TRIGGER t1_bi; 1576DROP TABLE t1; 1577# 1578# Bug#57306 SHOW PROCESSLIST does not display string literals well. 1579# 1580SET NAMES latin1; 1581SELECT GET_LOCK('t', 1000); 1582GET_LOCK('t', 1000) 15831 1584SET NAMES latin1; 1585SELECT GET_LOCK('t',1000) AS 'óóóó';; 1586SHOW PROCESSLIST; 1587Id User Host db Command Time State Info Rows_sent Rows_examined 1588### root ### test Query ### ### SELECT GET_LOCK('t',1000) AS 'óóóó' ### ### 1589### root ### test Query ### ### SHOW PROCESSLIST ### ### 1590SET NAMES utf8; 1591SHOW PROCESSLIST; 1592Id User Host db Command Time State Info Rows_sent Rows_examined 1593### root ### test Query ### ### SELECT GET_LOCK('t',1000) AS 'óóóó' ### ### 1594### root ### test Query ### ### SHOW PROCESSLIST ### ### 1595SELECT RELEASE_LOCK('t'); 1596RELEASE_LOCK('t') 15971 1598óóóó 15991 1600SET NAMES latin1; 1601# 1602# Bug#58996 SHOW CREATE TRIGGER fails when subject table is shadowed by 1603# a temporary table. 1604# 1605DROP TABLE IF EXISTS t1; 1606CREATE TABLE t1 (a INT); 1607CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END; 1608SHOW CREATE TRIGGER t1_bi; 1609Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1610t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END latin1 latin1_swedish_ci latin1_swedish_ci # 1611CREATE TEMPORARY TABLE t1 (b INT); 1612SHOW CREATE TRIGGER t1_bi; 1613Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 1614t1_bi ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END latin1 latin1_swedish_ci latin1_swedish_ci # 1615DROP TEMPORARY TABLE t1; 1616DROP TABLE t1; 1617# 1618# WL#6454: Deprecate SHOW AUTHORS and SHOW CONTRIBUTORS 1619# 1620# These statements were deprecated in 5.5 and removed in 5.6. 1621# They should therefore give ER_PARSE_ERROR here. 1622SHOW AUTHORS; 1623ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTHORS' at line 1 1624SHOW CONTRIBUTORS; 1625ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONTRIBUTORS' at line 1 1626# 1627# Bug#20709462: GENERATED COLUMNS NOT PRINTED CORRECTLY IN 1628# SHOW CREATE TABLE 1629# 1630CREATE TABLE t1 (i CHAR(3), 1631n CHAR(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i))); 1632# Use SHOW CREATE TABLE output below unmodified to re-create table 1633SHOW CREATE TABLE t1; 1634Table Create Table 1635t1 CREATE TABLE `t1` ( 1636 `i` char(3) DEFAULT NULL, 1637 `n` char(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(`i`)) VIRTUAL 1638) ENGINE=MyISAM DEFAULT CHARSET=latin1 1639DROP TABLE t1; 1640CREATE TABLE `t1` ( 1641`i` char(3) DEFAULT NULL, 1642`n` char(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i)) VIRTUAL 1643) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1644DROP TABLE t1; 1645