1create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)) engine=myisam; 2insert 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); 3-- Here we enable metadata just to check that the collation of the 4-- resultset is non-binary for string type. This should be changed 5-- after Bug#29394 is implemented. 6analyze table t1; 7Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 8def Table 253 512 7 Y 0 31 255 9def Op 253 40 7 Y 0 31 255 10def Msg_type 253 40 6 Y 0 31 255 11def Msg_text 250 1572864 2 Y 0 31 255 12Table Op Msg_type Msg_text 13test.t1 analyze status OK 14show index from t1; 15Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression 16t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE YES NULL 17t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE YES NULL 18t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE YES NULL 19t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE YES NULL 20-- Here we enable metadata just to check that the collation of the 21-- resultset is non-binary for string type. This should be changed 22-- after Bug#29394 is implemented. 23repair table t1; 24Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 25def Table 253 512 7 Y 0 31 255 26def Op 253 40 6 Y 0 31 255 27def Msg_type 253 40 6 Y 0 31 255 28def Msg_text 250 1572864 2 Y 0 31 255 29Table Op Msg_type Msg_text 30test.t1 repair status OK 31show index from t1; 32Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression 33t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE YES NULL 34t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE YES NULL 35t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE YES NULL 36t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE YES NULL 37drop table t1; 38create 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; 39show create table t1; 40Table Create Table 41t1 CREATE TABLE `t1` ( 42 `a` int NOT NULL, 43 `b` varchar(10) DEFAULT NULL, 44 KEY `b` (`b`) 45) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' 46alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; 47show create table t1; 48Table Create Table 49t1 CREATE TABLE `t1` ( 50 `a` int NOT NULL, 51 `b` varchar(10) DEFAULT NULL, 52 KEY `b` (`b`) 53) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' 54ALTER 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; 55show create table t1; 56Table Create Table 57t1 CREATE TABLE `t1` ( 58 `a` int NOT NULL, 59 `b` varchar(10) DEFAULT NULL, 60 KEY `b` (`b`) 61) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 62drop table t1; 63SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 64create table t1 ( 65type_bool bool not null default 0, 66type_tiny tinyint not null auto_increment primary key, 67type_short smallint(3), 68type_mediumint mediumint, 69type_bigint bigint, 70type_decimal decimal(5,2), 71type_numeric numeric(5,2), 72empty_char char(0), 73type_char char(2), 74type_varchar varchar(10), 75type_timestamp timestamp not null default current_timestamp on update current_timestamp, 76type_date date not null default '0000-00-00', 77type_time time not null default '00:00:00', 78type_datetime datetime not null default '0000-00-00 00:00:00', 79type_year year, 80type_enum enum ('red', 'green', 'blue'), 81type_set enum ('red', 'green', 'blue'), 82type_tinyblob tinyblob, 83type_blob blob, 84type_medium_blob mediumblob, 85type_long_blob longblob, 86index(type_short) 87) 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; 88Warnings: 89Warning 1681 Integer display width is deprecated and will be removed in a future release. 90show create table t1; 91Table Create Table 92t1 CREATE TABLE `t1` ( 93 `type_bool` tinyint(1) NOT NULL DEFAULT '0', 94 `type_tiny` tinyint NOT NULL AUTO_INCREMENT, 95 `type_short` smallint DEFAULT NULL, 96 `type_mediumint` mediumint DEFAULT NULL, 97 `type_bigint` bigint DEFAULT NULL, 98 `type_decimal` decimal(5,2) DEFAULT NULL, 99 `type_numeric` decimal(5,2) DEFAULT NULL, 100 `empty_char` char(0) DEFAULT NULL, 101 `type_char` char(2) DEFAULT NULL, 102 `type_varchar` varchar(10) DEFAULT NULL, 103 `type_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 104 `type_date` date NOT NULL DEFAULT '0000-00-00', 105 `type_time` time NOT NULL DEFAULT '00:00:00', 106 `type_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 107 `type_year` year DEFAULT NULL, 108 `type_enum` enum('red','green','blue') DEFAULT NULL, 109 `type_set` enum('red','green','blue') DEFAULT NULL, 110 `type_tinyblob` tinyblob, 111 `type_blob` blob, 112 `type_medium_blob` mediumblob, 113 `type_long_blob` longblob, 114 PRIMARY KEY (`type_tiny`), 115 KEY `type_short` (`type_short`) 116) 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' 117insert into t1 (type_timestamp) values ("2003-02-07 10:00:01"); 118select * from t1; 119type_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 1200 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 121drop table t1; 122SET sql_mode = default; 123CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; 124SHOW CREATE TABLE t1; 125Table Create Table 126t1 CREATE TABLE `t1` ( 127 `i` int DEFAULT NULL, 128 KEY `i` (`i`) 129) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 130DROP TABLE t1; 131CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY; 132SHOW CREATE TABLE t1; 133Table Create Table 134t1 CREATE TABLE `t1` ( 135 `i` int DEFAULT NULL, 136 KEY `i` (`i`) USING HASH 137) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 138DROP TABLE t1; 139CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY; 140SHOW CREATE TABLE t1; 141Table Create Table 142t1 CREATE TABLE `t1` ( 143 `i` int DEFAULT NULL, 144 KEY `i` (`i`) USING BTREE 145) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 146DROP TABLE t1; 147CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; 148SHOW CREATE TABLE t1; 149Table Create Table 150t1 CREATE TABLE `t1` ( 151 `i` int DEFAULT NULL, 152 KEY `i` (`i`) 153) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 154DROP TABLE t1; 155CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; 156SHOW CREATE TABLE t1; 157Table Create Table 158t1 CREATE TABLE `t1` ( 159 `i` int DEFAULT NULL, 160 KEY `i` (`i`) USING BTREE 161) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 162DROP TABLE t1; 163CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; 164SHOW CREATE TABLE t1; 165Table Create Table 166t1 CREATE TABLE `t1` ( 167 `i` int DEFAULT NULL, 168 KEY `i` (`i`) 169) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 170ALTER TABLE t1 ENGINE=MEMORY; 171SHOW CREATE TABLE t1; 172Table Create Table 173t1 CREATE TABLE `t1` ( 174 `i` int DEFAULT NULL, 175 KEY `i` (`i`) 176) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 177DROP TABLE t1; 178CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; 179SHOW CREATE TABLE t1; 180Table Create Table 181t1 CREATE TABLE `t1` ( 182 `i` int DEFAULT NULL, 183 KEY `i` (`i`) USING BTREE 184) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 185ALTER TABLE t1 ENGINE=MEMORY; 186SHOW CREATE TABLE t1; 187Table Create Table 188t1 CREATE TABLE `t1` ( 189 `i` int DEFAULT NULL, 190 KEY `i` (`i`) USING BTREE 191) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 192DROP TABLE t1; 193flush status; 194show variables like "log_queries_not_using_indexes"; 195Variable_name Value 196log_queries_not_using_indexes ON 197CREATE TABLE `tab1` ( 198`c1` int(11) default NULL, 199`c2` char(20) default NULL, 200`c3` char(20) default NULL, 201KEY `k1` (`c2`) 202) ENGINE=MyISAM DEFAULT CHARSET=latin1; 203Warnings: 204Warning 1681 Integer display width is deprecated and will be removed in a future release. 205INSERT INTO tab1 values(1, "hello", "world"); 206INSERT INTO tab1 values(2, "hello2", "world2"); 207INSERT INTO tab1 values(3, "hello3", "world3"); 208select SQL_NO_CACHE * from tab1; 209c1 c2 c3 2101 hello world 2112 hello2 world2 2123 hello3 world3 213Warnings: 214Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 215show status like 'slow_queries'; 216Variable_name Value 217Slow_queries 1 218set global log_queries_not_using_indexes=OFF; 219show variables like "log_queries_not_using_indexes"; 220Variable_name Value 221log_queries_not_using_indexes OFF 222select SQL_NO_CACHE * from tab1; 223c1 c2 c3 2241 hello world 2252 hello2 world2 2263 hello3 world3 227Warnings: 228Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 229show status like 'slow_queries'; 230Variable_name Value 231Slow_queries 1 232set global log_queries_not_using_indexes=ON; 233show variables like "log_queries_not_using_indexes"; 234Variable_name Value 235log_queries_not_using_indexes ON 236select SQL_NO_CACHE * from tab1; 237c1 c2 c3 2381 hello world 2392 hello2 world2 2403 hello3 world3 241Warnings: 242Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 243show status like 'slow_queries'; 244Variable_name Value 245Slow_queries 2 246drop table tab1; 247show variables like 'myisam_recover_options'; 248Variable_name Value 249myisam_recover_options OFF 250CREATE TABLE t1 ( 251Codigo int(10) unsigned NOT NULL auto_increment, 252Nombre varchar(255) default NULL, 253Telefono varchar(255) default NULL, 254Observaciones longtext, 255Direccion varchar(255) default NULL, 256Dni varchar(255) default NULL, 257CP int(11) default NULL, 258Provincia varchar(255) default NULL, 259Poblacion varchar(255) default NULL, 260PRIMARY KEY (Codigo) 261) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; 262Warnings: 263Warning 1681 Integer display width is deprecated and will be removed in a future release. 264Warning 1681 Integer display width is deprecated and will be removed in a future release. 265Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 266show create table t1; 267Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 268def Table 253 256 2 N 1 31 255 269def Create Table 253 4096 437 N 1 31 255 270Table Create Table 271t1 CREATE TABLE `t1` ( 272 `Codigo` int unsigned NOT NULL AUTO_INCREMENT, 273 `Nombre` varchar(255) DEFAULT NULL, 274 `Telefono` varchar(255) DEFAULT NULL, 275 `Observaciones` longtext, 276 `Direccion` varchar(255) DEFAULT NULL, 277 `Dni` varchar(255) DEFAULT NULL, 278 `CP` int DEFAULT NULL, 279 `Provincia` varchar(255) DEFAULT NULL, 280 `Poblacion` varchar(255) DEFAULT NULL, 281 PRIMARY KEY (`Codigo`) 282) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 283drop table t1; 284