1call mtr.add_suppression("Can't find record in '.*'"); 2call mtr.add_suppression("Table 't1' is marked as crashed and should be repaired"); 3drop table if exists t1,t2,t3; 4SET SQL_WARNINGS=1; 5CREATE TABLE t1 ( 6STRING_DATA char(255) default NULL, 7KEY string_data (STRING_DATA) 8) ENGINE=MyISAM; 9INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); 10INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); 11INSERT INTO t1 VALUES ('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'); 12INSERT INTO t1 VALUES ('FGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'); 13INSERT INTO t1 VALUES ('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'); 14INSERT INTO t1 VALUES ('WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'); 15CHECK TABLE t1; 16Table Op Msg_type Msg_text 17test.t1 check status OK 18drop table t1; 19create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); 20check table t1; 21Table Op Msg_type Msg_text 22test.t1 check status OK 23repair table t1; 24Table Op Msg_type Msg_text 25test.t1 repair status OK 26delete from t1 where (a & 1); 27check table t1; 28Table Op Msg_type Msg_text 29test.t1 check status OK 30repair table t1; 31Table Op Msg_type Msg_text 32test.t1 repair status OK 33check table t1; 34Table Op Msg_type Msg_text 35test.t1 check status OK 36drop table t1; 37create table t1 (a int not null auto_increment, b int not null, primary key (a), index(b)); 38insert into t1 (b) values (1),(2),(2),(2),(2); 39optimize table t1; 40Table Op Msg_type Msg_text 41test.t1 optimize status OK 42show index from t1; 43Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 44t1 0 PRIMARY 1 a A 5 NULL NULL BTREE 45t1 1 b 1 b A 1 NULL NULL BTREE 46optimize table t1; 47Table Op Msg_type Msg_text 48test.t1 optimize status Table is already up to date 49show index from t1; 50Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 51t1 0 PRIMARY 1 a A 5 NULL NULL BTREE 52t1 1 b 1 b A 1 NULL NULL BTREE 53drop table t1; 54create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=myisam; 55insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 56explain select * from t1 order by a; 57id select_type table type possible_keys key key_len ref rows Extra 581 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 59explain select * from t1 order by b; 60id select_type table type possible_keys key key_len ref rows Extra 611 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 62explain select * from t1 order by c; 63id select_type table type possible_keys key key_len ref rows Extra 641 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 65explain select a from t1 order by a; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 68explain select b from t1 order by b; 69id select_type table type possible_keys key key_len ref rows Extra 701 SIMPLE t1 index NULL b 4 NULL 4 Using index 71explain select a,b from t1 order by b; 72id select_type table type possible_keys key key_len ref rows Extra 731 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 74explain select a,b from t1; 75id select_type table type possible_keys key key_len ref rows Extra 761 SIMPLE t1 ALL NULL NULL NULL NULL 4 77explain select a,b,c from t1; 78id select_type table type possible_keys key key_len ref rows Extra 791 SIMPLE t1 ALL NULL NULL NULL NULL 4 80drop table t1; 81CREATE TABLE t1 (a INT); 82INSERT INTO t1 VALUES (1), (2), (3); 83LOCK TABLES t1 WRITE; 84INSERT INTO t1 VALUES (1), (2), (3); 85OPTIMIZE TABLE t1; 86Table Op Msg_type Msg_text 87test.t1 optimize status OK 88DROP TABLE t1; 89create table t1 ( t1 char(255), key(t1(250))); 90insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169'); 91insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203'); 92insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767'); 93insert t1 values ('242624262426242624262426242624262426242607890789078907890789078907890789078907'); 94insert t1 values ('256025602560256025602560256025602560256011701170117011701170117011701170117011'); 95insert t1 values ('276027602760276027602760276027602760276001610161016101610161016101610161016101'); 96insert t1 values ('281528152815281528152815281528152815281564956495649564956495649564956495649564'); 97insert t1 values ('292129212921292129212921292129212921292102100210021002100210021002100210021002'); 98insert t1 values ('380638063806380638063806380638063806380634483448344834483448344834483448344834'); 99insert t1 values ('411641164116411641164116411641164116411616301630163016301630163016301630163016'); 100insert t1 values ('420842084208420842084208420842084208420899889988998899889988998899889988998899'); 101insert t1 values ('438443844384438443844384438443844384438482448244824482448244824482448244824482'); 102insert t1 values ('443244324432443244324432443244324432443239613961396139613961396139613961396139'); 103insert t1 values ('485448544854485448544854485448544854485477847784778477847784778477847784778477'); 104insert t1 values ('494549454945494549454945494549454945494555275527552755275527552755275527552755'); 105insert t1 values ('538647864786478647864786478647864786478688918891889188918891889188918891889188'); 106insert t1 values ('565556555655565556555655565556555655565554845484548454845484548454845484548454'); 107insert t1 values ('607860786078607860786078607860786078607856665666566656665666566656665666566656'); 108insert t1 values ('640164016401640164016401640164016401640141274127412741274127412741274127412741'); 109insert t1 values ('719471947194719471947194719471947194719478717871787178717871787178717871787178'); 110insert t1 values ('742574257425742574257425742574257425742549604960496049604960496049604960496049'); 111insert t1 values ('887088708870887088708870887088708870887035963596359635963596359635963596359635'); 112insert t1 values ('917791779177917791779177917791779177917773857385738573857385738573857385738573'); 113insert t1 values ('933293329332933293329332933293329332933278987898789878987898789878987898789878'); 114insert t1 values ('963896389638963896389638963896389638963877807780778077807780778077807780778077'); 115delete from t1 where t1>'2'; 116insert t1 values ('70'), ('84'), ('60'), ('20'), ('76'), ('89'), ('49'), ('50'), 117('88'), ('61'), ('42'), ('98'), ('39'), ('30'), ('25'), ('66'), ('61'), ('48'), 118('80'), ('84'), ('98'), ('19'), ('91'), ('42'), ('47'); 119optimize table t1; 120Table Op Msg_type Msg_text 121test.t1 optimize status OK 122check table t1; 123Table Op Msg_type Msg_text 124test.t1 check status OK 125drop table t1; 126create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 127int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17 128int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int, 129i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34 130int, i35 int, i36 int, i37 int, i38 int, i39 int, i40 int, i41 int, i42 int, 131i43 int, i44 int, i45 int, i46 int, i47 int, i48 int, i49 int, i50 int, i51 132int, i52 int, i53 int, i54 int, i55 int, i56 int, i57 int, i58 int, i59 int, 133i60 int, i61 int, i62 int, i63 int, i64 int, i65 int, i66 int, i67 int, i68 134int, i69 int, i70 int, i71 int, i72 int, i73 int, i74 int, i75 int, i76 int, 135i77 int, i78 int, i79 int, i80 int, i81 int, i82 int, i83 int, i84 int, i85 136int, i86 int, i87 int, i88 int, i89 int, i90 int, i91 int, i92 int, i93 int, 137i94 int, i95 int, i96 int, i97 int, i98 int, i99 int, i100 int, i101 int, i102 138int, i103 int, i104 int, i105 int, i106 int, i107 int, i108 int, i109 int, i110 139int, i111 int, i112 int, i113 int, i114 int, i115 int, i116 int, i117 int, i118 140int, i119 int, i120 int, i121 int, i122 int, i123 int, i124 int, i125 int, i126 141int, i127 int, i128 int, i129 int, i130 int, i131 int, i132 int, i133 int, i134 142int, i135 int, i136 int, i137 int, i138 int, i139 int, i140 int, i141 int, i142 143int, i143 int, i144 int, i145 int, i146 int, i147 int, i148 int, i149 int, i150 144int, i151 int, i152 int, i153 int, i154 int, i155 int, i156 int, i157 int, i158 145int, i159 int, i160 int, i161 int, i162 int, i163 int, i164 int, i165 int, i166 146int, i167 int, i168 int, i169 int, i170 int, i171 int, i172 int, i173 int, i174 147int, i175 int, i176 int, i177 int, i178 int, i179 int, i180 int, i181 int, i182 148int, i183 int, i184 int, i185 int, i186 int, i187 int, i188 int, i189 int, i190 149int, i191 int, i192 int, i193 int, i194 int, i195 int, i196 int, i197 int, i198 150int, i199 int, i200 int, i201 int, i202 int, i203 int, i204 int, i205 int, i206 151int, i207 int, i208 int, i209 int, i210 int, i211 int, i212 int, i213 int, i214 152int, i215 int, i216 int, i217 int, i218 int, i219 int, i220 int, i221 int, i222 153int, i223 int, i224 int, i225 int, i226 int, i227 int, i228 int, i229 int, i230 154int, i231 int, i232 int, i233 int, i234 int, i235 int, i236 int, i237 int, i238 155int, i239 int, i240 int, i241 int, i242 int, i243 int, i244 int, i245 int, i246 156int, i247 int, i248 int, i249 int, i250 int, i251 int, i252 int, i253 int, i254 157int, i255 int, i256 int, i257 int, i258 int, i259 int, i260 int, i261 int, i262 158int, i263 int, i264 int, i265 int, i266 int, i267 int, i268 int, i269 int, i270 159int, i271 int, i272 int, i273 int, i274 int, i275 int, i276 int, i277 int, i278 160int, i279 int, i280 int, i281 int, i282 int, i283 int, i284 int, i285 int, i286 161int, i287 int, i288 int, i289 int, i290 int, i291 int, i292 int, i293 int, i294 162int, i295 int, i296 int, i297 int, i298 int, i299 int, i300 int, i301 int, i302 163int, i303 int, i304 int, i305 int, i306 int, i307 int, i308 int, i309 int, i310 164int, i311 int, i312 int, i313 int, i314 int, i315 int, i316 int, i317 int, i318 165int, i319 int, i320 int, i321 int, i322 int, i323 int, i324 int, i325 int, i326 166int, i327 int, i328 int, i329 int, i330 int, i331 int, i332 int, i333 int, i334 167int, i335 int, i336 int, i337 int, i338 int, i339 int, i340 int, i341 int, i342 168int, i343 int, i344 int, i345 int, i346 int, i347 int, i348 int, i349 int, i350 169int, i351 int, i352 int, i353 int, i354 int, i355 int, i356 int, i357 int, i358 170int, i359 int, i360 int, i361 int, i362 int, i363 int, i364 int, i365 int, i366 171int, i367 int, i368 int, i369 int, i370 int, i371 int, i372 int, i373 int, i374 172int, i375 int, i376 int, i377 int, i378 int, i379 int, i380 int, i381 int, i382 173int, i383 int, i384 int, i385 int, i386 int, i387 int, i388 int, i389 int, i390 174int, i391 int, i392 int, i393 int, i394 int, i395 int, i396 int, i397 int, i398 175int, i399 int, i400 int, i401 int, i402 int, i403 int, i404 int, i405 int, i406 176int, i407 int, i408 int, i409 int, i410 int, i411 int, i412 int, i413 int, i414 177int, i415 int, i416 int, i417 int, i418 int, i419 int, i420 int, i421 int, i422 178int, i423 int, i424 int, i425 int, i426 int, i427 int, i428 int, i429 int, i430 179int, i431 int, i432 int, i433 int, i434 int, i435 int, i436 int, i437 int, i438 180int, i439 int, i440 int, i441 int, i442 int, i443 int, i444 int, i445 int, i446 181int, i447 int, i448 int, i449 int, i450 int, i451 int, i452 int, i453 int, i454 182int, i455 int, i456 int, i457 int, i458 int, i459 int, i460 int, i461 int, i462 183int, i463 int, i464 int, i465 int, i466 int, i467 int, i468 int, i469 int, i470 184int, i471 int, i472 int, i473 int, i474 int, i475 int, i476 int, i477 int, i478 185int, i479 int, i480 int, i481 int, i482 int, i483 int, i484 int, i485 int, i486 186int, i487 int, i488 int, i489 int, i490 int, i491 int, i492 int, i493 int, i494 187int, i495 int, i496 int, i497 int, i498 int, i499 int, i500 int, i501 int, i502 188int, i503 int, i504 int, i505 int, i506 int, i507 int, i508 int, i509 int, i510 189int, i511 int, i512 int, i513 int, i514 int, i515 int, i516 int, i517 int, i518 190int, i519 int, i520 int, i521 int, i522 int, i523 int, i524 int, i525 int, i526 191int, i527 int, i528 int, i529 int, i530 int, i531 int, i532 int, i533 int, i534 192int, i535 int, i536 int, i537 int, i538 int, i539 int, i540 int, i541 int, i542 193int, i543 int, i544 int, i545 int, i546 int, i547 int, i548 int, i549 int, i550 194int, i551 int, i552 int, i553 int, i554 int, i555 int, i556 int, i557 int, i558 195int, i559 int, i560 int, i561 int, i562 int, i563 int, i564 int, i565 int, i566 196int, i567 int, i568 int, i569 int, i570 int, i571 int, i572 int, i573 int, i574 197int, i575 int, i576 int, i577 int, i578 int, i579 int, i580 int, i581 int, i582 198int, i583 int, i584 int, i585 int, i586 int, i587 int, i588 int, i589 int, i590 199int, i591 int, i592 int, i593 int, i594 int, i595 int, i596 int, i597 int, i598 200int, i599 int, i600 int, i601 int, i602 int, i603 int, i604 int, i605 int, i606 201int, i607 int, i608 int, i609 int, i610 int, i611 int, i612 int, i613 int, i614 202int, i615 int, i616 int, i617 int, i618 int, i619 int, i620 int, i621 int, i622 203int, i623 int, i624 int, i625 int, i626 int, i627 int, i628 int, i629 int, i630 204int, i631 int, i632 int, i633 int, i634 int, i635 int, i636 int, i637 int, i638 205int, i639 int, i640 int, i641 int, i642 int, i643 int, i644 int, i645 int, i646 206int, i647 int, i648 int, i649 int, i650 int, i651 int, i652 int, i653 int, i654 207int, i655 int, i656 int, i657 int, i658 int, i659 int, i660 int, i661 int, i662 208int, i663 int, i664 int, i665 int, i666 int, i667 int, i668 int, i669 int, i670 209int, i671 int, i672 int, i673 int, i674 int, i675 int, i676 int, i677 int, i678 210int, i679 int, i680 int, i681 int, i682 int, i683 int, i684 int, i685 int, i686 211int, i687 int, i688 int, i689 int, i690 int, i691 int, i692 int, i693 int, i694 212int, i695 int, i696 int, i697 int, i698 int, i699 int, i700 int, i701 int, i702 213int, i703 int, i704 int, i705 int, i706 int, i707 int, i708 int, i709 int, i710 214int, i711 int, i712 int, i713 int, i714 int, i715 int, i716 int, i717 int, i718 215int, i719 int, i720 int, i721 int, i722 int, i723 int, i724 int, i725 int, i726 216int, i727 int, i728 int, i729 int, i730 int, i731 int, i732 int, i733 int, i734 217int, i735 int, i736 int, i737 int, i738 int, i739 int, i740 int, i741 int, i742 218int, i743 int, i744 int, i745 int, i746 int, i747 int, i748 int, i749 int, i750 219int, i751 int, i752 int, i753 int, i754 int, i755 int, i756 int, i757 int, i758 220int, i759 int, i760 int, i761 int, i762 int, i763 int, i764 int, i765 int, i766 221int, i767 int, i768 int, i769 int, i770 int, i771 int, i772 int, i773 int, i774 222int, i775 int, i776 int, i777 int, i778 int, i779 int, i780 int, i781 int, i782 223int, i783 int, i784 int, i785 int, i786 int, i787 int, i788 int, i789 int, i790 224int, i791 int, i792 int, i793 int, i794 int, i795 int, i796 int, i797 int, i798 225int, i799 int, i800 int, i801 int, i802 int, i803 int, i804 int, i805 int, i806 226int, i807 int, i808 int, i809 int, i810 int, i811 int, i812 int, i813 int, i814 227int, i815 int, i816 int, i817 int, i818 int, i819 int, i820 int, i821 int, i822 228int, i823 int, i824 int, i825 int, i826 int, i827 int, i828 int, i829 int, i830 229int, i831 int, i832 int, i833 int, i834 int, i835 int, i836 int, i837 int, i838 230int, i839 int, i840 int, i841 int, i842 int, i843 int, i844 int, i845 int, i846 231int, i847 int, i848 int, i849 int, i850 int, i851 int, i852 int, i853 int, i854 232int, i855 int, i856 int, i857 int, i858 int, i859 int, i860 int, i861 int, i862 233int, i863 int, i864 int, i865 int, i866 int, i867 int, i868 int, i869 int, i870 234int, i871 int, i872 int, i873 int, i874 int, i875 int, i876 int, i877 int, i878 235int, i879 int, i880 int, i881 int, i882 int, i883 int, i884 int, i885 int, i886 236int, i887 int, i888 int, i889 int, i890 int, i891 int, i892 int, i893 int, i894 237int, i895 int, i896 int, i897 int, i898 int, i899 int, i900 int, i901 int, i902 238int, i903 int, i904 int, i905 int, i906 int, i907 int, i908 int, i909 int, i910 239int, i911 int, i912 int, i913 int, i914 int, i915 int, i916 int, i917 int, i918 240int, i919 int, i920 int, i921 int, i922 int, i923 int, i924 int, i925 int, i926 241int, i927 int, i928 int, i929 int, i930 int, i931 int, i932 int, i933 int, i934 242int, i935 int, i936 int, i937 int, i938 int, i939 int, i940 int, i941 int, i942 243int, i943 int, i944 int, i945 int, i946 int, i947 int, i948 int, i949 int, i950 244int, i951 int, i952 int, i953 int, i954 int, i955 int, i956 int, i957 int, i958 245int, i959 int, i960 int, i961 int, i962 int, i963 int, i964 int, i965 int, i966 246int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 247int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 248int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 249int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 250int, i999 int, i1000 int, b blob) row_format=dynamic; 251insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2521, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2531, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2541, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2551, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2561, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2571, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2581, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2591, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2601, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2611, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2621, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2631, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2641, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2651, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2661, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2671, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2681, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2691, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2701, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2711, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2721, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2731, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2741, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2751, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2761, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2771, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2781, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2791, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2801, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2811, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2821, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2831, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2841, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2851, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2861, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2871, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2881, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2891, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); 290update t1 set b=repeat('a',256); 291update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0; 292check table t1; 293Table Op Msg_type Msg_text 294test.t1 check status OK 295delete from t1 where i8=1; 296select i1,i2 from t1; 297i1 i2 298check table t1; 299Table Op Msg_type Msg_text 300test.t1 check status OK 301drop table t1; 302SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 303CREATE TABLE `t1` ( 304`post_id` mediumint(8) unsigned NOT NULL auto_increment, 305`topic_id` mediumint(8) unsigned NOT NULL default '0', 306`post_time` datetime NOT NULL default '0000-00-00 00:00:00', 307`post_text` text NOT NULL, 308`icon_url` varchar(10) NOT NULL default '', 309`sign` tinyint(1) unsigned NOT NULL default '0', 310`post_edit` varchar(150) NOT NULL default '', 311`poster_login` varchar(35) NOT NULL default '', 312`ip` varchar(15) NOT NULL default '', 313PRIMARY KEY (`post_id`), 314KEY `post_time` (`post_time`), 315KEY `ip` (`ip`), 316KEY `poster_login` (`poster_login`), 317KEY `topic_id` (`topic_id`), 318FULLTEXT KEY `post_text` (`post_text`) 319) ENGINE=MyISAM; 320INSERT IGNORE INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); 321REPAIR TABLE t1; 322Table Op Msg_type Msg_text 323test.t1 repair status OK 324CHECK TABLE t1; 325Table Op Msg_type Msg_text 326test.t1 check status OK 327drop table t1; 328CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), KEY t1 (a, b, c, d, e)); 329ERROR 42000: Specified key was too long; max key length is 1000 bytes 330CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255)); 331ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e); 332ERROR 42000: Specified key was too long; max key length is 1000 bytes 333DROP TABLE t1; 334CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); 335INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); 336create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); 337INSERT into t2 values (1,1,1), (2,2,2); 338optimize table t1; 339Table Op Msg_type Msg_text 340test.t1 optimize status OK 341show index from t1; 342Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 343t1 1 b 1 b A 5 NULL NULL YES BTREE 344t1 1 c 1 c A 5 NULL NULL YES BTREE 345t1 1 a 1 a A 1 NULL NULL BTREE 346t1 1 a 2 b A 5 NULL NULL YES BTREE 347t1 1 c_2 1 c A 5 NULL NULL YES BTREE 348t1 1 c_2 2 a A 5 NULL NULL BTREE 349explain select * from t1,t2 where t1.a=t2.a; 350id select_type table type possible_keys key key_len ref rows Extra 3511 SIMPLE t2 ALL a NULL NULL NULL 2 3521 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 353explain select * from t1,t2 force index(a) where t1.a=t2.a; 354id select_type table type possible_keys key key_len ref rows Extra 3551 SIMPLE t2 ALL a NULL NULL NULL 2 3561 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 357explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; 358id select_type table type possible_keys key key_len ref rows Extra 3591 SIMPLE t2 ALL a NULL NULL NULL 2 3601 SIMPLE t1 ref a a 4 test.t2.a 3 361INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); 362optimize table t1; 363Table Op Msg_type Msg_text 364test.t1 optimize status OK 365show index from t1; 366Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 367t1 1 b 1 b A 10 NULL NULL YES BTREE 368t1 1 c 1 c A 10 NULL NULL YES BTREE 369t1 1 a 1 a A 10 NULL NULL BTREE 370t1 1 a 2 b A 10 NULL NULL YES BTREE 371t1 1 c_2 1 c A 10 NULL NULL YES BTREE 372t1 1 c_2 2 a A 10 NULL NULL BTREE 373explain select * from t1,t2 where t1.b=t2.b; 374id select_type table type possible_keys key key_len ref rows Extra 3751 SIMPLE t2 ALL b NULL NULL NULL 2 Using where 3761 SIMPLE t1 ref b b 5 test.t2.b 1 377delete from t1 where t1.a>1; 378optimize table t1; 379Table Op Msg_type Msg_text 380test.t1 optimize status OK 381show index from t1; 382Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 383t1 1 b 1 b A 5 NULL NULL YES BTREE 384t1 1 c 1 c A 5 NULL NULL YES BTREE 385t1 1 a 1 a A 1 NULL NULL BTREE 386t1 1 a 2 b A 5 NULL NULL YES BTREE 387t1 1 c_2 1 c A 5 NULL NULL YES BTREE 388t1 1 c_2 2 a A 5 NULL NULL BTREE 389explain select * from t1,t2 force index(c) where t1.a=t2.a; 390id select_type table type possible_keys key key_len ref rows Extra 3911 SIMPLE t2 ALL NULL NULL NULL NULL 2 3921 SIMPLE t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 393explain select * from t1 where a=0 or a=2; 394id select_type table type possible_keys key key_len ref rows Extra 3951 SIMPLE t1 ALL a NULL NULL NULL 5 Using where 396explain select * from t1 force index (a) where a=0 or a=2; 397id select_type table type possible_keys key key_len ref rows Extra 3981 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where 399explain select * from t1 where c=1; 400id select_type table type possible_keys key key_len ref rows Extra 4011 SIMPLE t1 ref c,c_2 c 5 const 1 402explain select * from t1 use index() where c=1; 403id select_type table type possible_keys key key_len ref rows Extra 4041 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 405drop table t1,t2; 406create table t1 (a int not null auto_increment primary key, b varchar(255)); 407insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); 408update t1 set b=repeat(left(b,1),200) where a=1; 409delete from t1 where (a & 1)= 0; 410update t1 set b=repeat('e',200) where a=1; 411flush tables; 412check table t1; 413Table Op Msg_type Msg_text 414test.t1 check status OK 415update t1 set b=repeat(left(b,1),255) where a between 1 and 5; 416update t1 set b=repeat(left(b,1),10) where a between 32 and 43; 417update t1 set b=repeat(left(b,1),2) where a between 64 and 66; 418update t1 set b=repeat(left(b,1),65) where a between 67 and 70; 419check table t1; 420Table Op Msg_type Msg_text 421test.t1 check status OK 422insert into t1 (b) values (repeat('z',100)); 423update t1 set b="test" where left(b,1) > 'n'; 424check table t1; 425Table Op Msg_type Msg_text 426test.t1 check status OK 427drop table t1; 428create table t1 ( a text not null, key a (a(20))); 429insert into t1 values ('aaa '),('aaa'),('aa'); 430check table t1; 431Table Op Msg_type Msg_text 432test.t1 check status OK 433repair table t1; 434Table Op Msg_type Msg_text 435test.t1 repair status OK 436select concat(a,'.') from t1 where a='aaa'; 437concat(a,'.') 438aaa . 439aaa. 440select concat(a,'.') from t1 where binary a='aaa'; 441concat(a,'.') 442aaa. 443update t1 set a='bbb' where a='aaa'; 444select concat(a,'.') from t1; 445concat(a,'.') 446bbb. 447bbb. 448aa. 449drop table t1; 450create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); 451insert into t1 values('807780', '477', '165'); 452insert into t1 values('807780', '477', '162'); 453insert into t1 values('807780', '472', '162'); 454select * from t1 where a='807780' and b='477' and c='165'; 455a b c 456807780 477 165 457drop table t1; 458DROP TABLE IF EXISTS t1; 459Warnings: 460Note 1051 Unknown table 'test.t1' 461CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); 462INSERT t1 VALUES ("can \tcan"); 463INSERT t1 VALUES ("can can"); 464INSERT t1 VALUES ("can"); 465SELECT * FROM t1; 466a 467can can 468can 469can can 470CHECK TABLE t1; 471Table Op Msg_type Msg_text 472test.t1 check status OK 473DROP TABLE t1; 474create table t1 (a blob); 475insert into t1 values('a '),('a'); 476select concat(a,'.') from t1 where a='a'; 477concat(a,'.') 478a. 479select concat(a,'.') from t1 where a='a '; 480concat(a,'.') 481a . 482alter table t1 add key(a(2)); 483select concat(a,'.') from t1 where a='a'; 484concat(a,'.') 485a. 486select concat(a,'.') from t1 where a='a '; 487concat(a,'.') 488a . 489drop table t1; 490create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); 491insert into t1 (b) values ('a'),('b'),('c'); 492select concat(b,'.') from t1; 493concat(b,'.') 494a. 495b. 496c. 497update t1 set b='b ' where a=2; 498update t1 set b='b ' where a > 1; 499ERROR 23000: Duplicate entry 'b ' for key 'b' 500insert into t1 (b) values ('b'); 501ERROR 23000: Duplicate entry 'b' for key 'b' 502select * from t1; 503a b 5041 a 5052 b 5063 c 507delete from t1 where b='b'; 508select a,concat(b,'.') from t1; 509a concat(b,'.') 5101 a. 5113 c. 512drop table t1; 513create table t1 (a int not null); 514create table t2 (a int not null, primary key (a)); 515insert into t1 values (1); 516insert into t2 values (1),(2); 517select sql_big_result distinct t1.a from t1,t2 order by t2.a; 518a 5191 520select distinct t1.a from t1,t2 order by t2.a; 521a 5221 523select sql_big_result distinct t1.a from t1,t2; 524a 5251 526explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; 527id select_type table type possible_keys key key_len ref rows Extra 5281 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary 5291 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index 530explain select distinct t1.a from t1,t2 order by t2.a; 531id select_type table type possible_keys key key_len ref rows Extra 5321 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary 5331 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index 534drop table t1,t2; 535create table t1 ( 536c1 varchar(32), 537key (c1) 538) engine=myisam; 539alter table t1 disable keys; 540insert into t1 values ('a'), ('b'); 541select c1 from t1 order by c1 limit 1; 542c1 543a 544drop table t1; 545create table t1 (a int not null, primary key(a)); 546create table t2 (a int not null, b int not null, primary key(a,b)); 547insert into t1 values (1),(2),(3),(4),(5),(6); 548insert into t2 values (1,1),(2,1); 549lock tables t1 read local, t2 read local; 550select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; 551a a b 5521 1 1 5532 2 1 554connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 555insert into t2 values(2,0); 556disconnect root; 557connection default; 558select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; 559a a b 5601 1 1 5612 2 1 562unlock tables; 563drop table t1,t2; 564CREATE TABLE t1 (c1 varchar(250) NOT NULL); 565CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)); 566INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); 567INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); 568LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; 569SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 570WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; 571t1c1 t2c1 572connect con1,localhost,root,,; 573connection con1; 574INSERT INTO t2 VALUES ('test000001'), ('test000005'); 575disconnect con1; 576connection default; 577SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 578WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; 579t1c1 t2c1 580UNLOCK TABLES; 581DROP TABLE t1,t2; 582CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; 583Got one of the listed errors 584create table t1 (a int, b varchar(200), c text not null) checksum=1; 585create table t2 (a int, b varchar(200), c text not null) checksum=0; 586insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 587insert t2 select * from t1; 588checksum table t1, t2, t3 quick; 589Table Checksum 590test.t1 3442722830 591test.t2 NULL 592test.t3 NULL 593Warnings: 594Error 1146 Table 'test.t3' doesn't exist 595checksum table t1, t2, t3; 596Table Checksum 597test.t1 3442722830 598test.t2 3442722830 599test.t3 NULL 600Warnings: 601Error 1146 Table 'test.t3' doesn't exist 602checksum table t1, t2, t3 extended; 603Table Checksum 604test.t1 3442722830 605test.t2 3442722830 606test.t3 NULL 607Warnings: 608Error 1146 Table 'test.t3' doesn't exist 609alter table t1 add d int default 30, add e bigint default 300000, add f decimal(30) default 442; 610checksum table t1; 611Table Checksum 612test.t1 2924214226 613drop table t1,t2; 614create table t1 (a int, key (a)); 615show keys from t1; 616Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 617t1 1 a 1 a A NULL NULL NULL YES BTREE 618alter table t1 disable keys; 619show keys from t1; 620Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 621t1 1 a 1 a A NULL NULL NULL YES BTREE disabled 622create table t2 (a int); 623set @@rand_seed1=31415926,@@rand_seed2=2718281828; 624insert t1 select * from t2; 625show keys from t1; 626Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 627t1 1 a 1 a A NULL NULL NULL YES BTREE disabled 628alter table t1 enable keys; 629show keys from t1; 630Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 631t1 1 a 1 a A 1000 NULL NULL YES BTREE 632alter table t1 engine=heap; 633alter table t1 disable keys; 634Warnings: 635Note 1031 Storage engine MEMORY of the table `test`.`t1` doesn't have this option 636show keys from t1; 637Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 638t1 1 a 1 a NULL 500 NULL NULL YES HASH 639drop table t1,t2; 640create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); 641insert into t1 values (null,''), (null,''); 642explain select count(*) from t1 where a is null; 643id select_type table type possible_keys key key_len ref rows Extra 6441 SIMPLE t1 ref idx idx 4 const 1 Using where 645select count(*) from t1 where a is null; 646count(*) 6472 648drop table t1; 649create table t1 (c1 int, c2 varchar(4) not null default '', 650key(c2(3))) default charset=utf8; 651insert into t1 values (1,'A'), (2, 'B'), (3, 'A'); 652update t1 set c2='A B' where c1=2; 653check table t1; 654Table Op Msg_type Msg_text 655test.t1 check status OK 656drop table t1; 657create table t1 (c1 int); 658insert into t1 values (1),(2),(3),(4); 659checksum table t1; 660Table Checksum 661test.t1 149057747 662delete from t1 where c1 = 1; 663create table t2 as select * from t1; 664checksum table t1; 665Table Checksum 666test.t1 984116287 667checksum table t2; 668Table Checksum 669test.t2 984116287 670drop table t1, t2; 671show variables like 'myisam_stats_method'; 672Variable_name Value 673myisam_stats_method NULLS_UNEQUAL 674create table t1 (a int, key(a)); 675insert into t1 values (0),(1),(2),(3),(4); 676insert into t1 select NULL from t1; 677analyze table t1; 678Table Op Msg_type Msg_text 679test.t1 analyze status OK 680show index from t1; 681Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 682t1 1 a 1 a A 10 NULL NULL YES BTREE 683insert into t1 values (11); 684delete from t1 where a=11; 685check table t1; 686Table Op Msg_type Msg_text 687test.t1 check status OK 688show index from t1; 689Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 690t1 1 a 1 a A 10 NULL NULL YES BTREE 691set myisam_stats_method=nulls_equal; 692show variables like 'myisam_stats_method'; 693Variable_name Value 694myisam_stats_method NULLS_EQUAL 695insert into t1 values (11); 696delete from t1 where a=11; 697analyze table t1; 698Table Op Msg_type Msg_text 699test.t1 analyze status OK 700show index from t1; 701Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 702t1 1 a 1 a A 5 NULL NULL YES BTREE 703insert into t1 values (11); 704delete from t1 where a=11; 705check table t1; 706Table Op Msg_type Msg_text 707test.t1 check status OK 708show index from t1; 709Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 710t1 1 a 1 a A 5 NULL NULL YES BTREE 711set myisam_stats_method=DEFAULT; 712show variables like 'myisam_stats_method'; 713Variable_name Value 714myisam_stats_method NULLS_UNEQUAL 715insert into t1 values (11); 716delete from t1 where a=11; 717analyze table t1; 718Table Op Msg_type Msg_text 719test.t1 analyze status OK 720show index from t1; 721Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 722t1 1 a 1 a A 10 NULL NULL YES BTREE 723insert into t1 values (11); 724delete from t1 where a=11; 725check table t1; 726Table Op Msg_type Msg_text 727test.t1 check status OK 728show index from t1; 729Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 730t1 1 a 1 a A 10 NULL NULL YES BTREE 731drop table t1; 732set myisam_stats_method=nulls_ignored; 733show variables like 'myisam_stats_method'; 734Variable_name Value 735myisam_stats_method NULLS_IGNORED 736create table t1 ( 737a char(3), b char(4), c char(5), d char(6), 738key(a,b,c,d) 739); 740insert into t1 values ('bcd','def1', NULL, 'zz'); 741insert into t1 values ('bcd','def2', NULL, 'zz'); 742insert into t1 values ('bce','def1', 'yuu', NULL); 743insert into t1 values ('bce','def2', NULL, 'quux'); 744analyze table t1; 745Table Op Msg_type Msg_text 746test.t1 analyze status OK 747show index from t1; 748Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 749t1 1 a 1 a A 2 NULL NULL YES BTREE 750t1 1 a 2 b A 4 NULL NULL YES BTREE 751t1 1 a 3 c A 4 NULL NULL YES BTREE 752t1 1 a 4 d A 4 NULL NULL YES BTREE 753delete from t1; 754analyze table t1; 755Table Op Msg_type Msg_text 756test.t1 analyze status OK 757show index from t1; 758Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 759t1 1 a 1 a A 0 NULL NULL YES BTREE 760t1 1 a 2 b A 0 NULL NULL YES BTREE 761t1 1 a 3 c A 0 NULL NULL YES BTREE 762t1 1 a 4 d A 0 NULL NULL YES BTREE 763set myisam_stats_method=DEFAULT; 764drop table t1; 765create table t1( 766cip INT NOT NULL, 767time TIME NOT NULL, 768score INT NOT NULL DEFAULT 0, 769bob TINYBLOB 770); 771insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); 772insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), 773(6, 'c', '00:06'); 774select * from t1 where bob is null and cip=1; 775cip time score bob 7761 00:01:00 0 NULL 777create index bug on t1 (bob(22), cip, time); 778select * from t1 where bob is null and cip=1; 779cip time score bob 7801 00:01:00 0 NULL 781drop table t1; 782create table t1 ( 783id1 int not null auto_increment, 784id2 int not null default '0', 785t text not null, 786primary key (id1), 787key x (id2, t(32)) 788) engine=myisam; 789insert into t1 (id2, t) values 790(10, 'abc'), (10, 'abc'), (10, 'abc'), 791(20, 'abc'), (20, 'abc'), (20, 'def'), 792(10, 'abc'), (10, 'abc'); 793select count(*) from t1 where id2 = 10; 794count(*) 7955 796select count(id1) from t1 where id2 = 10; 797count(id1) 7985 799drop table t1; 800CREATE TABLE t1(a TINYINT, KEY(a)) ENGINE=MyISAM; 801INSERT INTO t1 VALUES(1); 802SELECT MAX(a) FROM t1 IGNORE INDEX(a); 803MAX(a) 8041 805ALTER TABLE t1 DISABLE KEYS; 806SELECT MAX(a) FROM t1; 807MAX(a) 8081 809SELECT MAX(a) FROM t1 IGNORE INDEX(a); 810MAX(a) 8111 812DROP TABLE t1; 813CREATE TABLE t1(a CHAR(9), b VARCHAR(7)) ENGINE=MyISAM; 814INSERT INTO t1(a) VALUES('xxxxxxxxx'),('xxxxxxxxx'); 815UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; 816SELECT * FROM t1; 817a b 818xxxxxxxxx bbbbbb 819xxxxxxxxx bbbbbb 820DROP TABLE t1; 821SET @@myisam_repair_threads=2; 822SHOW VARIABLES LIKE 'myisam_repair%'; 823Variable_name Value 824myisam_repair_threads 2 825CREATE TABLE t1 ( 826`_id` int(11) NOT NULL default '0', 827`url` text, 828`email` text, 829`description` text, 830`loverlap` int(11) default NULL, 831`roverlap` int(11) default NULL, 832`lneighbor_id` int(11) default NULL, 833`rneighbor_id` int(11) default NULL, 834`length_` int(11) default NULL, 835`sequence` mediumtext, 836`name` text, 837`_obj_class` text NOT NULL, 838PRIMARY KEY (`_id`), 839UNIQUE KEY `sequence_name_index` (`name`(50)), 840KEY (`length_`) 841) ENGINE=MyISAM DEFAULT CHARSET=latin1; 842INSERT INTO t1 VALUES 843(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), 844(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), 845(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), 846(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), 847(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), 848(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), 849(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), 850(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), 851(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); 852SELECT _id FROM t1; 853_id 8541 8552 8563 8574 8585 8596 8607 8618 8629 863DELETE FROM t1 WHERE _id < 8; 864SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; 865table_name engine version row_format Table_rows Data_free create_options table_comment 866t1 MyISAM 10 Dynamic 2 140 867CHECK TABLE t1 EXTENDED; 868Table Op Msg_type Msg_text 869test.t1 check status OK 870OPTIMIZE TABLE t1; 871Table Op Msg_type Msg_text 872test.t1 optimize status OK 873CHECK TABLE t1 EXTENDED; 874Table Op Msg_type Msg_text 875test.t1 check status OK 876SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; 877table_name engine version row_format Table_rows Data_free create_options table_comment 878t1 MyISAM 10 Dynamic 2 0 879SELECT _id FROM t1; 880_id 8818 8829 883DROP TABLE t1; 884CREATE TABLE t1 ( 885`_id` int(11) NOT NULL default '0', 886`url` text, 887`email` text, 888`description` text, 889`loverlap` int(11) default NULL, 890`roverlap` int(11) default NULL, 891`lneighbor_id` int(11) default NULL, 892`rneighbor_id` int(11) default NULL, 893`length_` int(11) default NULL, 894`sequence` mediumtext, 895`name` text, 896`_obj_class` text NOT NULL, 897PRIMARY KEY (`_id`), 898UNIQUE KEY `sequence_name_index` (`name`(50)), 899KEY (`length_`) 900) ENGINE=MyISAM DEFAULT CHARSET=latin1; 901INSERT INTO t1 VALUES 902(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), 903(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), 904(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), 905(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), 906(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), 907(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), 908(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), 909(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), 910(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); 911SELECT _id FROM t1; 912_id 9131 9142 9153 9164 9175 9186 9197 9208 9219 922DELETE FROM t1 WHERE _id < 8; 923SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; 924table_name engine version row_format Table_rows Data_free create_options table_comment 925t1 MyISAM 10 Dynamic 2 140 926CHECK TABLE t1 EXTENDED; 927Table Op Msg_type Msg_text 928test.t1 check status OK 929REPAIR TABLE t1 QUICK; 930Table Op Msg_type Msg_text 931test.t1 repair status OK 932CHECK TABLE t1 EXTENDED; 933Table Op Msg_type Msg_text 934test.t1 check status OK 935SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; 936table_name engine version row_format Table_rows Data_free create_options table_comment 937t1 MyISAM 10 Dynamic 2 140 938SELECT _id FROM t1; 939_id 9408 9419 942DROP TABLE t1; 943SET @@myisam_repair_threads=1; 944SHOW VARIABLES LIKE 'myisam_repair%'; 945Variable_name Value 946myisam_repair_threads 1 947CREATE TABLE t1(a VARCHAR(16)); 948INSERT INTO t1 VALUES('aaaaaaaa'),(NULL); 949UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa'; 950SELECT * FROM t1; 951a 952aaaaaaaaaaaaaaaa 953aaaaaaaaaaaaaaaa 954DROP TABLE t1; 955CREATE TABLE t1(a INT); 956INSERT INTO t1 VALUES(1),(2); 957UPDATE t1,t1 AS t2 SET t1.a=t1.a+2 WHERE t1.a=t2.a-1; 958SELECT * FROM t1 ORDER BY a; 959a 9602 9613 962DROP TABLE t1; 963CREATE TABLE t1 (c1 TEXT) AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100; 964SELECT table_name, engine, version, row_format, max_data_length, max_index_length, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; 965table_name engine version row_format max_data_length max_index_length create_options table_comment 966t1 MyISAM 10 Dynamic 72057594037927935 17179868160 max_rows=4100100100 avg_row_length=70100 967DROP TABLE t1; 968CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM; 969INSERT INTO t1 VALUES 970(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 971(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 972(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 973(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 974(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 975(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 976(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 977(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 978(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 979(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 980(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 981(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 982(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 983(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 984(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 985(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 986(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 987(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 988(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 989(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 990(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 991(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 992(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 993(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 994(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 995(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 996(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 997(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 998(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 999(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1000(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1001(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1002(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1003(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1004(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1005(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1006(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1007(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1008(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1009(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1010(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1011(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1012(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1013(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1014(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1015(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1016(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1017(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1018(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1019(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1020(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1021(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1022(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1023(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1024(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1025(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1026(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1027(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1028(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1029(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1030(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1031(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1032(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1033(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1034(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1035(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1036(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1037(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1038(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1039(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1040(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1041(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1042(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1043(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1044(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1045(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1046(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1047(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1048(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1049(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1050(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1051(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1052(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1053(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1054(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1055(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1056(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1057(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1058(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1059(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1060(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1061(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1062(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1063(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1064(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1065(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1066(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1067(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1068(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1069(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1070(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1071(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1072(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1073(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1074(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1075(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1076(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1077(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1078(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1079(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1080(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1081(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1082(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1083(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1084(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1085(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1086(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1087(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1088(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1089(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1090(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1091(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1092(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1093(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1094(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1095(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1096(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1097(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), 1098(''), (''), (''), (''), 1099(' B'), (' B'), (' B'), (' B'); 1100SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = ''; 1101COUNT(*) 11024 1103SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = ''; 1104length(c1) c1 11050 1106SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; 1107COUNT(*) 11084 1109SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; 1110length(c1) c1 11110 1112SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1; 1113length(c1) c1 11142 A 11150 11162 B 1117DROP TABLE t1; 1118End of 4.1 tests 1119set storage_engine=MyISAM; 1120drop table if exists t1,t2,t3; 1121--- Testing varchar --- 1122--- Testing varchar --- 1123create table t1 (v varchar(10), c char(10), t text); 1124insert into t1 values('+ ', '+ ', '+ '); 1125set @a=repeat(' ',20); 1126insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1127Warnings: 1128Note 1265 Data truncated for column 'v' at row 1 1129select concat('*',v,'*',c,'*',t,'*') from t1; 1130concat('*',v,'*',c,'*',t,'*') 1131*+ *+*+ * 1132*+ *+*+ * 1133show create table t1; 1134Table Create Table 1135t1 CREATE TABLE `t1` ( 1136 `v` varchar(10) DEFAULT NULL, 1137 `c` char(10) DEFAULT NULL, 1138 `t` text DEFAULT NULL 1139) ENGINE=MyISAM DEFAULT CHARSET=latin1 1140create table t2 like t1; 1141show create table t2; 1142Table Create Table 1143t2 CREATE TABLE `t2` ( 1144 `v` varchar(10) DEFAULT NULL, 1145 `c` char(10) DEFAULT NULL, 1146 `t` text DEFAULT NULL 1147) ENGINE=MyISAM DEFAULT CHARSET=latin1 1148create table t3 select * from t1; 1149show create table t3; 1150Table Create Table 1151t3 CREATE TABLE `t3` ( 1152 `v` varchar(10) DEFAULT NULL, 1153 `c` char(10) DEFAULT NULL, 1154 `t` text DEFAULT NULL 1155) ENGINE=MyISAM DEFAULT CHARSET=latin1 1156alter table t1 modify c varchar(10); 1157show create table t1; 1158Table Create Table 1159t1 CREATE TABLE `t1` ( 1160 `v` varchar(10) DEFAULT NULL, 1161 `c` varchar(10) DEFAULT NULL, 1162 `t` text DEFAULT NULL 1163) ENGINE=MyISAM DEFAULT CHARSET=latin1 1164alter table t1 modify v char(10); 1165show create table t1; 1166Table Create Table 1167t1 CREATE TABLE `t1` ( 1168 `v` char(10) DEFAULT NULL, 1169 `c` varchar(10) DEFAULT NULL, 1170 `t` text DEFAULT NULL 1171) ENGINE=MyISAM DEFAULT CHARSET=latin1 1172alter table t1 modify t varchar(10); 1173Warnings: 1174Note 1265 Data truncated for column 't' at row 2 1175show create table t1; 1176Table Create Table 1177t1 CREATE TABLE `t1` ( 1178 `v` char(10) DEFAULT NULL, 1179 `c` varchar(10) DEFAULT NULL, 1180 `t` varchar(10) DEFAULT NULL 1181) ENGINE=MyISAM DEFAULT CHARSET=latin1 1182select concat('*',v,'*',c,'*',t,'*') from t1; 1183concat('*',v,'*',c,'*',t,'*') 1184*+*+*+ * 1185*+*+*+ * 1186drop table t1,t2,t3; 1187create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); 1188show create table t1; 1189Table Create Table 1190t1 CREATE TABLE `t1` ( 1191 `v` varchar(10) DEFAULT NULL, 1192 `c` char(10) DEFAULT NULL, 1193 `t` text DEFAULT NULL, 1194 KEY `v` (`v`), 1195 KEY `c` (`c`), 1196 KEY `t` (`t`(10)) 1197) ENGINE=MyISAM DEFAULT CHARSET=latin1 1198select count(*) from t1; 1199count(*) 1200270 1201insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1202select count(*) from t1 where v='a'; 1203count(*) 120410 1205select count(*) from t1 where c='a'; 1206count(*) 120710 1208select count(*) from t1 where t='a'; 1209count(*) 121010 1211select count(*) from t1 where v='a '; 1212count(*) 121310 1214select count(*) from t1 where c='a '; 1215count(*) 121610 1217select count(*) from t1 where t='a '; 1218count(*) 121910 1220select count(*) from t1 where v between 'a' and 'a '; 1221count(*) 122210 1223select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1224count(*) 122510 1226select count(*) from t1 where v like 'a%'; 1227count(*) 122811 1229select count(*) from t1 where c like 'a%'; 1230count(*) 123111 1232select count(*) from t1 where t like 'a%'; 1233count(*) 123411 1235select count(*) from t1 where v like 'a %'; 1236count(*) 12379 1238explain select count(*) from t1 where v='a '; 1239id select_type table type possible_keys key key_len ref rows Extra 12401 SIMPLE t1 ref v v 13 const # Using where; Using index 1241explain select count(*) from t1 where c='a '; 1242id select_type table type possible_keys key key_len ref rows Extra 12431 SIMPLE t1 ref c c 11 const # Using where; Using index 1244explain select count(*) from t1 where t='a '; 1245id select_type table type possible_keys key key_len ref rows Extra 12461 SIMPLE t1 ref t t 13 const # Using where 1247explain select count(*) from t1 where v like 'a%'; 1248id select_type table type possible_keys key key_len ref rows Extra 12491 SIMPLE t1 range v v 13 NULL # Using where; Using index 1250explain select count(*) from t1 where v between 'a' and 'a '; 1251id select_type table type possible_keys key key_len ref rows Extra 12521 SIMPLE t1 ref v v 13 const # Using where; Using index 1253explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1254id select_type table type possible_keys key key_len ref rows Extra 12551 SIMPLE t1 ref v v 13 const # Using where; Using index 1256alter table t1 add unique(v); 1257ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1258show warnings; 1259Level Code Message 1260Error 1062 Duplicate entry 'a' for key 'v_2' 1261alter table t1 add key(v); 1262Warnings: 1263Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release 1264select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1265qq 1266*a*a*a* 1267*a *a*a * 1268*a *a*a * 1269*a *a*a * 1270*a *a*a * 1271*a *a*a * 1272*a *a*a * 1273*a *a*a * 1274*a *a*a * 1275*a *a*a * 1276explain select * from t1 where v='a'; 1277id select_type table type possible_keys key key_len ref rows Extra 12781 SIMPLE t1 ref v,v_2 # 13 const # # 1279select v,count(*) from t1 group by v limit 10; 1280v count(*) 1281a 1 1282a 10 1283b 10 1284c 10 1285d 10 1286e 10 1287f 10 1288g 10 1289h 10 1290i 10 1291select v,count(t) from t1 group by v limit 10; 1292v count(t) 1293a 1 1294a 10 1295b 10 1296c 10 1297d 10 1298e 10 1299f 10 1300g 10 1301h 10 1302i 10 1303select v,count(c) from t1 group by v limit 10; 1304v count(c) 1305a 1 1306a 10 1307b 10 1308c 10 1309d 10 1310e 10 1311f 10 1312g 10 1313h 10 1314i 10 1315select sql_big_result v,count(t) from t1 group by v limit 10; 1316v count(t) 1317a 1 1318a 10 1319b 10 1320c 10 1321d 10 1322e 10 1323f 10 1324g 10 1325h 10 1326i 10 1327select sql_big_result v,count(c) from t1 group by v limit 10; 1328v count(c) 1329a 1 1330a 10 1331b 10 1332c 10 1333d 10 1334e 10 1335f 10 1336g 10 1337h 10 1338i 10 1339select c,count(*) from t1 group by c limit 10; 1340c count(*) 1341a 1 1342a 10 1343b 10 1344c 10 1345d 10 1346e 10 1347f 10 1348g 10 1349h 10 1350i 10 1351select c,count(t) from t1 group by c limit 10; 1352c count(t) 1353a 1 1354a 10 1355b 10 1356c 10 1357d 10 1358e 10 1359f 10 1360g 10 1361h 10 1362i 10 1363select sql_big_result c,count(t) from t1 group by c limit 10; 1364c count(t) 1365a 1 1366a 10 1367b 10 1368c 10 1369d 10 1370e 10 1371f 10 1372g 10 1373h 10 1374i 10 1375select t,count(*) from t1 group by t limit 10; 1376t count(*) 1377a 1 1378a 10 1379b 10 1380c 10 1381d 10 1382e 10 1383f 10 1384g 10 1385h 10 1386i 10 1387select t,count(t) from t1 group by t limit 10; 1388t count(t) 1389a 1 1390a 10 1391b 10 1392c 10 1393d 10 1394e 10 1395f 10 1396g 10 1397h 10 1398i 10 1399select sql_big_result t,count(t) from t1 group by t limit 10; 1400t count(t) 1401a 1 1402a 10 1403b 10 1404c 10 1405d 10 1406e 10 1407f 10 1408g 10 1409h 10 1410i 10 1411alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 1412show create table t1; 1413Table Create Table 1414t1 CREATE TABLE `t1` ( 1415 `v` varchar(300) DEFAULT NULL, 1416 `c` char(10) DEFAULT NULL, 1417 `t` text DEFAULT NULL, 1418 KEY `c` (`c`), 1419 KEY `t` (`t`(10)), 1420 KEY `v` (`v`) 1421) ENGINE=MyISAM DEFAULT CHARSET=latin1 1422select count(*) from t1 where v='a'; 1423count(*) 142410 1425select count(*) from t1 where v='a '; 1426count(*) 142710 1428select count(*) from t1 where v between 'a' and 'a '; 1429count(*) 143010 1431select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1432count(*) 143310 1434select count(*) from t1 where v like 'a%'; 1435count(*) 143611 1437select count(*) from t1 where v like 'a %'; 1438count(*) 14399 1440explain select count(*) from t1 where v='a '; 1441id select_type table type possible_keys key key_len ref rows Extra 14421 SIMPLE t1 ref v v 303 const # Using where; Using index 1443explain select count(*) from t1 where v like 'a%'; 1444id select_type table type possible_keys key key_len ref rows Extra 14451 SIMPLE t1 range v v 303 NULL # Using where; Using index 1446explain select count(*) from t1 where v between 'a' and 'a '; 1447id select_type table type possible_keys key key_len ref rows Extra 14481 SIMPLE t1 ref v v 303 const # Using where; Using index 1449explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1450id select_type table type possible_keys key key_len ref rows Extra 14511 SIMPLE t1 ref v v 303 const # Using where; Using index 1452explain select * from t1 where v='a'; 1453id select_type table type possible_keys key key_len ref rows Extra 14541 SIMPLE t1 ref v v 303 const # # 1455select v,count(*) from t1 group by v limit 10; 1456v count(*) 1457a 1 1458a 10 1459b 10 1460c 10 1461d 10 1462e 10 1463f 10 1464g 10 1465h 10 1466i 10 1467select v,count(t) from t1 group by v limit 10; 1468v count(t) 1469a 1 1470a 10 1471b 10 1472c 10 1473d 10 1474e 10 1475f 10 1476g 10 1477h 10 1478i 10 1479select sql_big_result v,count(t) from t1 group by v limit 10; 1480v count(t) 1481a 1 1482a 10 1483b 10 1484c 10 1485d 10 1486e 10 1487f 10 1488g 10 1489h 10 1490i 10 1491alter table t1 drop key v, add key v (v(30)); 1492show create table t1; 1493Table Create Table 1494t1 CREATE TABLE `t1` ( 1495 `v` varchar(300) DEFAULT NULL, 1496 `c` char(10) DEFAULT NULL, 1497 `t` text DEFAULT NULL, 1498 KEY `c` (`c`), 1499 KEY `t` (`t`(10)), 1500 KEY `v` (`v`(30)) 1501) ENGINE=MyISAM DEFAULT CHARSET=latin1 1502select count(*) from t1 where v='a'; 1503count(*) 150410 1505select count(*) from t1 where v='a '; 1506count(*) 150710 1508select count(*) from t1 where v between 'a' and 'a '; 1509count(*) 151010 1511select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1512count(*) 151310 1514select count(*) from t1 where v like 'a%'; 1515count(*) 151611 1517select count(*) from t1 where v like 'a %'; 1518count(*) 15199 1520explain select count(*) from t1 where v='a '; 1521id select_type table type possible_keys key key_len ref rows Extra 15221 SIMPLE t1 ref v v 33 const # Using where 1523explain select count(*) from t1 where v like 'a%'; 1524id select_type table type possible_keys key key_len ref rows Extra 15251 SIMPLE t1 range v v 33 NULL # Using where 1526explain select count(*) from t1 where v between 'a' and 'a '; 1527id select_type table type possible_keys key key_len ref rows Extra 15281 SIMPLE t1 ref v v 33 const # Using where 1529explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1530id select_type table type possible_keys key key_len ref rows Extra 15311 SIMPLE t1 ref v v 33 const # Using where 1532explain select * from t1 where v='a'; 1533id select_type table type possible_keys key key_len ref rows Extra 15341 SIMPLE t1 ref v v 33 const # # 1535select v,count(*) from t1 group by v limit 10; 1536v count(*) 1537a 1 1538a 10 1539b 10 1540c 10 1541d 10 1542e 10 1543f 10 1544g 10 1545h 10 1546i 10 1547select v,count(t) from t1 group by v limit 10; 1548v count(t) 1549a 1 1550a 10 1551b 10 1552c 10 1553d 10 1554e 10 1555f 10 1556g 10 1557h 10 1558i 10 1559select sql_big_result v,count(t) from t1 group by v limit 10; 1560v count(t) 1561a 1 1562a 10 1563b 10 1564c 10 1565d 10 1566e 10 1567f 10 1568g 10 1569h 10 1570i 10 1571alter table t1 modify v varchar(600), drop key v, add key v (v); 1572show create table t1; 1573Table Create Table 1574t1 CREATE TABLE `t1` ( 1575 `v` varchar(600) DEFAULT NULL, 1576 `c` char(10) DEFAULT NULL, 1577 `t` text DEFAULT NULL, 1578 KEY `c` (`c`), 1579 KEY `t` (`t`(10)), 1580 KEY `v` (`v`) 1581) ENGINE=MyISAM DEFAULT CHARSET=latin1 1582select v,count(*) from t1 group by v limit 10; 1583v count(*) 1584a 1 1585a 10 1586b 10 1587c 10 1588d 10 1589e 10 1590f 10 1591g 10 1592h 10 1593i 10 1594select v,count(t) from t1 group by v limit 10; 1595v count(t) 1596a 1 1597a 10 1598b 10 1599c 10 1600d 10 1601e 10 1602f 10 1603g 10 1604h 10 1605i 10 1606select sql_big_result v,count(t) from t1 group by v limit 10; 1607v count(t) 1608a 1 1609a 10 1610b 10 1611c 10 1612d 10 1613e 10 1614f 10 1615g 10 1616h 10 1617i 10 1618drop table t1; 1619create table t1 (a char(10), unique (a)); 1620insert into t1 values ('a '); 1621insert into t1 values ('a '); 1622ERROR 23000: Duplicate entry 'a' for key 'a' 1623alter table t1 modify a varchar(10); 1624insert into t1 values ('a '),('a '),('a '),('a '); 1625ERROR 23000: Duplicate entry 'a ' for key 'a' 1626insert into t1 values ('a '); 1627ERROR 23000: Duplicate entry 'a ' for key 'a' 1628insert into t1 values ('a '); 1629ERROR 23000: Duplicate entry 'a ' for key 'a' 1630insert into t1 values ('a '); 1631ERROR 23000: Duplicate entry 'a ' for key 'a' 1632update t1 set a='a ' where a like 'a%'; 1633select concat(a,'.') from t1; 1634concat(a,'.') 1635a . 1636update t1 set a='abc ' where a like 'a '; 1637select concat(a,'.') from t1; 1638concat(a,'.') 1639a . 1640update t1 set a='a ' where a like 'a %'; 1641select concat(a,'.') from t1; 1642concat(a,'.') 1643a . 1644update t1 set a='a ' where a like 'a '; 1645select concat(a,'.') from t1; 1646concat(a,'.') 1647a . 1648drop table t1; 1649create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 1650show create table t1; 1651Table Create Table 1652t1 CREATE TABLE `t1` ( 1653 `v` varchar(10) DEFAULT NULL, 1654 `c` char(10) DEFAULT NULL, 1655 `t` text DEFAULT NULL, 1656 KEY `v` (`v`(5)), 1657 KEY `c` (`c`(5)), 1658 KEY `t` (`t`(5)) 1659) ENGINE=MyISAM DEFAULT CHARSET=latin1 1660drop table t1; 1661create table t1 (v char(10) character set utf8); 1662show create table t1; 1663Table Create Table 1664t1 CREATE TABLE `t1` ( 1665 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 1666) ENGINE=MyISAM DEFAULT CHARSET=latin1 1667drop table t1; 1668create table t1 (v varchar(10), c char(10)) row_format=fixed; 1669show create table t1; 1670Table Create Table 1671t1 CREATE TABLE `t1` ( 1672 `v` varchar(10) DEFAULT NULL, 1673 `c` char(10) DEFAULT NULL 1674) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED 1675insert into t1 values('a','a'),('a ','a '); 1676select concat('*',v,'*',c,'*') from t1; 1677concat('*',v,'*',c,'*') 1678*a*a* 1679*a *a* 1680drop table t1; 1681create table t1 (v varchar(65530), key(v(10))); 1682insert into t1 values(repeat('a',65530)); 1683select length(v) from t1 where v=repeat('a',65530); 1684length(v) 168565530 1686drop table t1; 1687create table t1(a int, b varchar(12), key ba(b, a)); 1688insert into t1 values (1, 'A'), (20, NULL); 1689explain select * from t1 where a=20 and b is null; 1690id select_type table type possible_keys key key_len ref rows Extra 16911 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index 1692select * from t1 where a=20 and b is null; 1693a b 169420 NULL 1695drop table t1; 1696create table t1 (v varchar(65530), key(v)); 1697Warnings: 1698Note 1071 Specified key was too long; max key length is 1000 bytes 1699drop table if exists t1; 1700set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1701create table t1 (v varchar(65536)); 1702Warnings: 1703Note 1246 Converting column 'v' from VARCHAR to TEXT 1704show create table t1; 1705Table Create Table 1706t1 CREATE TABLE `t1` ( 1707 `v` mediumtext DEFAULT NULL 1708) ENGINE=MyISAM DEFAULT CHARSET=latin1 1709drop table t1; 1710set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1711create table t1 (v varchar(65530) character set utf8); 1712Warnings: 1713Note 1246 Converting column 'v' from VARCHAR to TEXT 1714show create table t1; 1715Table Create Table 1716t1 CREATE TABLE `t1` ( 1717 `v` mediumtext CHARACTER SET utf8 DEFAULT NULL 1718) ENGINE=MyISAM DEFAULT CHARSET=latin1 1719drop table t1; 1720set statement sql_mode='' for 1721create table t1 (v varchar(65535)); 1722Warnings: 1723Note 1246 Converting column 'v' from VARCHAR to TEXT 1724show create table t1; 1725Table Create Table 1726t1 CREATE TABLE `t1` ( 1727 `v` text DEFAULT NULL 1728) ENGINE=MyISAM DEFAULT CHARSET=latin1 1729drop table t1; 1730set storage_engine=MyISAM; 1731set @save_concurrent_insert=@@concurrent_insert; 1732set global concurrent_insert=1; 1733create table t1 (a int); 1734insert into t1 values (1),(2),(3),(4),(5); 1735lock table t1 read local; 1736connect con1,localhost,root,,; 1737connection con1; 1738insert into t1 values(6),(7); 1739connection default; 1740unlock tables; 1741delete from t1 where a>=3 and a<=4; 1742lock table t1 read local; 1743connection con1; 1744set global concurrent_insert=2; 1745insert into t1 values (8),(9); 1746connection default; 1747unlock tables; 1748insert into t1 values (10),(11),(12); 1749select * from t1; 1750a 17511 17522 175311 175410 17555 17566 17577 17588 17599 176012 1761check table t1; 1762Table Op Msg_type Msg_text 1763test.t1 check status OK 1764drop table t1; 1765disconnect con1; 1766create table t1 (a int, b varchar(30) default "hello"); 1767insert into t1 (a) values (1),(2),(3),(4),(5); 1768lock table t1 read local; 1769connect con1,localhost,root,,; 1770connection con1; 1771insert into t1 (a) values(6),(7); 1772connection default; 1773unlock tables; 1774delete from t1 where a>=3 and a<=4; 1775lock table t1 read local; 1776connection con1; 1777set global concurrent_insert=2; 1778insert into t1 (a) values (8),(9); 1779connection default; 1780unlock tables; 1781insert into t1 (a) values (10),(11),(12); 1782select a from t1; 1783a 17841 17852 178611 178710 17885 17896 17907 17918 17929 179312 1794check table t1; 1795Table Op Msg_type Msg_text 1796test.t1 check status OK 1797drop table t1; 1798disconnect con1; 1799set global concurrent_insert=@save_concurrent_insert; 1800create table t1 (a int, key(a)); 1801insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL); 1802analyze table t1; 1803Table Op Msg_type Msg_text 1804test.t1 analyze status OK 1805show keys from t1; 1806Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1807t1 1 a 1 a A 8 NULL NULL YES BTREE 1808alter table t1 disable keys; 1809alter table t1 enable keys; 1810show keys from t1; 1811Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1812t1 1 a 1 a A 8 NULL NULL YES BTREE 1813drop table t1; 1814create table t1 (c1 int) engine=myisam pack_keys=0; 1815create table t2 (c1 int) engine=myisam pack_keys=1; 1816create table t3 (c1 int) engine=myisam pack_keys=default; 1817create table t4 (c1 int) engine=myisam pack_keys=2; 1818ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2' at line 1 1819drop table t1, t2, t3; 1820CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM; 1821INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); 1822SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1823a 18241 1825ALTER TABLE t1 DISABLE KEYS; 1826SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1827a 18281 1829SELECT a FROM t1 USE INDEX (inx) WHERE a=1; 1830a 18311 1832SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; 1833b 18341 1835SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; 1836b 18371 1838SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; 1839a 18401 1841ALTER TABLE t1 ENABLE KEYS; 1842SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1843a 18441 1845DROP TABLE t1; 1846CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)) ENGINE=MYISAM; 1847SHOW TABLE STATUS LIKE 't1'; 1848Name 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 Max_index_length Temporary 1849t1 MyISAM 10 Fixed 0 # # # 1024 # # # # # # # # N 1850INSERT INTO t1 VALUES (1,1); 1851SHOW TABLE STATUS LIKE 't1'; 1852Name 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 Max_index_length Temporary 1853t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # # N 1854ALTER TABLE t1 DISABLE KEYS; 1855SHOW TABLE STATUS LIKE 't1'; 1856Name 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 Max_index_length Temporary 1857t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # # N 1858ALTER TABLE t1 ENABLE KEYS; 1859SHOW TABLE STATUS LIKE 't1'; 1860Name 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 Max_index_length Temporary 1861t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # # N 1862ALTER TABLE t1 DISABLE KEYS; 1863SHOW TABLE STATUS LIKE 't1'; 1864Name 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 Max_index_length Temporary 1865t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # # N 1866ALTER TABLE t1 ENABLE KEYS; 1867SHOW TABLE STATUS LIKE 't1'; 1868Name 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 Max_index_length Temporary 1869t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # # N 1870# Enable keys with parallel repair 1871SET @@myisam_repair_threads=2; 1872ALTER TABLE t1 DISABLE KEYS; 1873ALTER TABLE t1 ENABLE KEYS; 1874SET @@myisam_repair_threads=1; 1875CHECK TABLE t1 EXTENDED; 1876Table Op Msg_type Msg_text 1877test.t1 check status OK 1878DROP TABLE t1; 1879CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)) ENGINE=MyISAM; 1880CREATE TABLE t2 LIKE t1; 1881INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); 1882INSERT INTO t1 SELECT * FROM t2; 1883SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; 1884id ref ref 18854 4 5 1886SELECT * FROM t1; 1887id ref 18881 3 18892 1 18903 2 18914 5 18924 4 1893DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; 1894SELECT * FROM t1; 1895id ref 18961 3 18972 1 18983 2 18994 5 1900DROP TABLE t1, t2; 1901CREATE TABLE t1 (a INT) ENGINE=MyISAM CHECKSUM=1 ROW_FORMAT=DYNAMIC; 1902INSERT INTO t1 VALUES (0); 1903UPDATE t1 SET a=1; 1904SELECT a FROM t1; 1905a 19061 1907CHECK TABLE t1; 1908Table Op Msg_type Msg_text 1909test.t1 check status OK 1910INSERT INTO t1 VALUES (0), (5), (4), (2); 1911UPDATE t1 SET a=2; 1912SELECT a FROM t1; 1913a 19142 19152 19162 19172 19182 1919CHECK TABLE t1; 1920Table Op Msg_type Msg_text 1921test.t1 check status OK 1922DROP TABLE t1; 1923# 1924# Bug #49465: valgrind warnings and incorrect live checksum... 1925# 1926CREATE TABLE t1( 1927a VARCHAR(1), b VARCHAR(1), c VARCHAR(1), 1928f VARCHAR(1), g VARCHAR(1), h VARCHAR(1), 1929i VARCHAR(1), j VARCHAR(1), k VARCHAR(1)) CHECKSUM=1; 1930INSERT INTO t1 VALUES('', '', '', '', '', '', '', '', ''); 1931CHECKSUM TABLE t1 QUICK; 1932Table Checksum 1933test.t1 467455460 1934CHECKSUM TABLE t1 EXTENDED; 1935Table Checksum 1936test.t1 467455460 1937DROP TABLE t1; 1938# 1939# BUG#48438 - crash with error in unioned query against merge table and view... 1940# 1941SET @save_table_open_cache=@@table_open_cache; 1942SET GLOBAL table_open_cache=10; 1943CREATE TABLE t1(a INT); 1944SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4, t1 AS a5, t1 AS a6, t1 AS a7, t1 AS a8, t1 AS a9, t1 AS a10, t1 AS a11 FOR UPDATE; 19451 1946SELECT TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES 1947WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 1948TABLE_ROWS DATA_LENGTH 19490 0 1950DROP TABLE t1; 1951SET GLOBAL table_open_cache=@save_table_open_cache; 1952End of 5.0 tests 1953create table t1 (a int not null, key `a` (a) key_block_size=1024); 1954show create table t1; 1955Table Create Table 1956t1 CREATE TABLE `t1` ( 1957 `a` int(11) NOT NULL, 1958 KEY `a` (`a`) KEY_BLOCK_SIZE=1024 1959) ENGINE=MyISAM DEFAULT CHARSET=latin1 1960drop table t1; 1961create table t1 (a int not null, key `a` (a) key_block_size=2048); 1962show create table t1; 1963Table Create Table 1964t1 CREATE TABLE `t1` ( 1965 `a` int(11) NOT NULL, 1966 KEY `a` (`a`) KEY_BLOCK_SIZE=2048 1967) ENGINE=MyISAM DEFAULT CHARSET=latin1 1968drop table t1; 1969create table t1 (a varchar(2048), key `a` (a)); 1970Warnings: 1971Note 1071 Specified key was too long; max key length is 1000 bytes 1972show create table t1; 1973Table Create Table 1974t1 CREATE TABLE `t1` ( 1975 `a` varchar(2048) DEFAULT NULL, 1976 KEY `a` (`a`(1000)) 1977) ENGINE=MyISAM DEFAULT CHARSET=latin1 1978drop table t1; 1979create table t1 (a varchar(2048), key `a` (a) key_block_size=1024); 1980Warnings: 1981Note 1071 Specified key was too long; max key length is 1000 bytes 1982show create table t1; 1983Table Create Table 1984t1 CREATE TABLE `t1` ( 1985 `a` varchar(2048) DEFAULT NULL, 1986 KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=4096 1987) ENGINE=MyISAM DEFAULT CHARSET=latin1 1988drop table t1; 1989create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024; 1990Warnings: 1991Note 1071 Specified key was too long; max key length is 1000 bytes 1992show create table t1; 1993Table Create Table 1994t1 CREATE TABLE `t1` ( 1995 `a` int(11) NOT NULL, 1996 `b` varchar(2048) DEFAULT NULL, 1997 KEY `a` (`a`), 1998 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=4096 1999) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024 2000alter table t1 key_block_size=2048; 2001show create table t1; 2002Table Create Table 2003t1 CREATE TABLE `t1` ( 2004 `a` int(11) NOT NULL, 2005 `b` varchar(2048) DEFAULT NULL, 2006 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2007 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=4096 2008) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048 2009alter table t1 add c int, add key (c); 2010show create table t1; 2011Table Create Table 2012t1 CREATE TABLE `t1` ( 2013 `a` int(11) NOT NULL, 2014 `b` varchar(2048) DEFAULT NULL, 2015 `c` int(11) DEFAULT NULL, 2016 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2017 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=4096, 2018 KEY `c` (`c`) 2019) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048 2020alter table t1 key_block_size=0; 2021alter table t1 add d int, add key (d); 2022show create table t1; 2023Table Create Table 2024t1 CREATE TABLE `t1` ( 2025 `a` int(11) NOT NULL, 2026 `b` varchar(2048) DEFAULT NULL, 2027 `c` int(11) DEFAULT NULL, 2028 `d` int(11) DEFAULT NULL, 2029 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2030 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=4096, 2031 KEY `c` (`c`) KEY_BLOCK_SIZE=2048, 2032 KEY `d` (`d`) 2033) ENGINE=MyISAM DEFAULT CHARSET=latin1 2034drop table t1; 2035create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192; 2036Warnings: 2037Note 1071 Specified key was too long; max key length is 1000 bytes 2038show create table t1; 2039Table Create Table 2040t1 CREATE TABLE `t1` ( 2041 `a` int(11) NOT NULL, 2042 `b` varchar(2048) DEFAULT NULL, 2043 KEY `a` (`a`), 2044 KEY `b` (`b`(1000)) 2045) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192 2046drop table t1; 2047create table t1 (a int not null, b varchar(512), key (a), key(b)); 2048show create table t1; 2049Table Create Table 2050t1 CREATE TABLE `t1` ( 2051 `a` int(11) NOT NULL, 2052 `b` varchar(512) DEFAULT NULL, 2053 KEY `a` (`a`), 2054 KEY `b` (`b`) 2055) ENGINE=MyISAM DEFAULT CHARSET=latin1 2056alter table t1 key_block_size=2048; 2057show create table t1; 2058Table Create Table 2059t1 CREATE TABLE `t1` ( 2060 `a` int(11) NOT NULL, 2061 `b` varchar(512) DEFAULT NULL, 2062 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2063 KEY `b` (`b`) KEY_BLOCK_SIZE=3072 2064) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048 2065alter table t1 add c int, add key (c); 2066show create table t1; 2067Table Create Table 2068t1 CREATE TABLE `t1` ( 2069 `a` int(11) NOT NULL, 2070 `b` varchar(512) DEFAULT NULL, 2071 `c` int(11) DEFAULT NULL, 2072 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2073 KEY `b` (`b`) KEY_BLOCK_SIZE=4096, 2074 KEY `c` (`c`) 2075) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048 2076alter table t1 key_block_size=4096; 2077show create table t1; 2078Table Create Table 2079t1 CREATE TABLE `t1` ( 2080 `a` int(11) NOT NULL, 2081 `b` varchar(512) DEFAULT NULL, 2082 `c` int(11) DEFAULT NULL, 2083 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2084 KEY `b` (`b`), 2085 KEY `c` (`c`) KEY_BLOCK_SIZE=2048 2086) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4096 2087alter table t1 key_block_size=0; 2088show create table t1; 2089Table Create Table 2090t1 CREATE TABLE `t1` ( 2091 `a` int(11) NOT NULL, 2092 `b` varchar(512) DEFAULT NULL, 2093 `c` int(11) DEFAULT NULL, 2094 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2095 KEY `b` (`b`) KEY_BLOCK_SIZE=4096, 2096 KEY `c` (`c`) KEY_BLOCK_SIZE=2048 2097) ENGINE=MyISAM DEFAULT CHARSET=latin1 2098alter table t1 add d int, add key (d); 2099show create table t1; 2100Table Create Table 2101t1 CREATE TABLE `t1` ( 2102 `a` int(11) NOT NULL, 2103 `b` varchar(512) DEFAULT NULL, 2104 `c` int(11) DEFAULT NULL, 2105 `d` int(11) DEFAULT NULL, 2106 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2107 KEY `b` (`b`) KEY_BLOCK_SIZE=4096, 2108 KEY `c` (`c`) KEY_BLOCK_SIZE=2048, 2109 KEY `d` (`d`) 2110) ENGINE=MyISAM DEFAULT CHARSET=latin1 2111alter table t1 key_block_size=8192; 2112show create table t1; 2113Table Create Table 2114t1 CREATE TABLE `t1` ( 2115 `a` int(11) NOT NULL, 2116 `b` varchar(512) DEFAULT NULL, 2117 `c` int(11) DEFAULT NULL, 2118 `d` int(11) DEFAULT NULL, 2119 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2120 KEY `b` (`b`) KEY_BLOCK_SIZE=4096, 2121 KEY `c` (`c`) KEY_BLOCK_SIZE=2048, 2122 KEY `d` (`d`) KEY_BLOCK_SIZE=1024 2123) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192 2124drop table t1; 2125create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192; 2126Warnings: 2127Note 1071 Specified key was too long; max key length is 1000 bytes 2128show create table t1; 2129Table Create Table 2130t1 CREATE TABLE `t1` ( 2131 `a` int(11) NOT NULL, 2132 `b` varchar(2048) DEFAULT NULL, 2133 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2134 KEY `b` (`b`(1000)) 2135) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192 2136drop table t1; 2137create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384; 2138show create table t1; 2139Table Create Table 2140t1 CREATE TABLE `t1` ( 2141 `a` int(11) NOT NULL, 2142 `b` int(11) DEFAULT NULL, 2143 KEY `a` (`a`) KEY_BLOCK_SIZE=1024, 2144 KEY `b` (`b`) KEY_BLOCK_SIZE=8192 2145) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16384 2146drop table t1; 2147create table t1 (a int not null, key `a` (a) key_block_size=512); 2148show create table t1; 2149Table Create Table 2150t1 CREATE TABLE `t1` ( 2151 `a` int(11) NOT NULL, 2152 KEY `a` (`a`) KEY_BLOCK_SIZE=1024 2153) ENGINE=MyISAM DEFAULT CHARSET=latin1 2154drop table t1; 2155create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000); 2156Warnings: 2157Note 1071 Specified key was too long; max key length is 1000 bytes 2158show create table t1; 2159Table Create Table 2160t1 CREATE TABLE `t1` ( 2161 `a` varchar(2048) DEFAULT NULL, 2162 KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=4096 2163) ENGINE=MyISAM DEFAULT CHARSET=latin1 2164drop table t1; 2165create table t1 (a int not null, key `a` (a) key_block_size=1025); 2166show create table t1; 2167Table Create Table 2168t1 CREATE TABLE `t1` ( 2169 `a` int(11) NOT NULL, 2170 KEY `a` (`a`) KEY_BLOCK_SIZE=2048 2171) ENGINE=MyISAM DEFAULT CHARSET=latin1 2172drop table t1; 2173create table t1 (a int not null, key key_block_size=1024 (a)); 2174ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1024 (a))' at line 1 2175create table t1 (a int not null, key `a` key_block_size=1024 (a)); 2176ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_block_size=1024 (a))' at line 1 2177CREATE TABLE t1 ( 2178c1 INT, 2179c2 VARCHAR(300), 2180KEY (c1) KEY_BLOCK_SIZE 1024, 2181KEY (c2) KEY_BLOCK_SIZE 8192 2182); 2183INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))), 2184(11, REPEAT('b', CEIL(RAND() * 300))), 2185(12, REPEAT('c', CEIL(RAND() * 300))), 2186(13, REPEAT('d', CEIL(RAND() * 300))), 2187(14, REPEAT('e', CEIL(RAND() * 300))), 2188(15, REPEAT('f', CEIL(RAND() * 300))), 2189(16, REPEAT('g', CEIL(RAND() * 300))), 2190(17, REPEAT('h', CEIL(RAND() * 300))), 2191(18, REPEAT('i', CEIL(RAND() * 300))), 2192(19, REPEAT('j', CEIL(RAND() * 300))), 2193(20, REPEAT('k', CEIL(RAND() * 300))), 2194(21, REPEAT('l', CEIL(RAND() * 300))), 2195(22, REPEAT('m', CEIL(RAND() * 300))), 2196(23, REPEAT('n', CEIL(RAND() * 300))), 2197(24, REPEAT('o', CEIL(RAND() * 300))), 2198(25, REPEAT('p', CEIL(RAND() * 300))), 2199(26, REPEAT('q', CEIL(RAND() * 300))), 2200(27, REPEAT('r', CEIL(RAND() * 300))), 2201(28, REPEAT('s', CEIL(RAND() * 300))), 2202(29, REPEAT('t', CEIL(RAND() * 300))), 2203(30, REPEAT('u', CEIL(RAND() * 300))), 2204(31, REPEAT('v', CEIL(RAND() * 300))), 2205(32, REPEAT('w', CEIL(RAND() * 300))), 2206(33, REPEAT('x', CEIL(RAND() * 300))), 2207(34, REPEAT('y', CEIL(RAND() * 300))), 2208(35, REPEAT('z', CEIL(RAND() * 300))); 2209INSERT INTO t1 SELECT * FROM t1; 2210INSERT INTO t1 SELECT * FROM t1; 2211CHECK TABLE t1; 2212Table Op Msg_type Msg_text 2213test.t1 check status OK 2214REPAIR TABLE t1; 2215Table Op Msg_type Msg_text 2216test.t1 repair status OK 2217DELETE FROM t1 WHERE c1 >= 10; 2218CHECK TABLE t1; 2219Table Op Msg_type Msg_text 2220test.t1 check status OK 2221DROP TABLE t1; 2222CREATE TABLE t1 ( 2223c1 CHAR(130), 2224c2 VARCHAR(1) 2225) ENGINE=MyISAM; 2226INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2227SELECT COUNT(*) FROM t1; 2228COUNT(*) 22291 2230CHECK TABLE t1; 2231Table Op Msg_type Msg_text 2232test.t1 check status OK 2233REPAIR TABLE t1; 2234Table Op Msg_type Msg_text 2235test.t1 repair status OK 2236SELECT COUNT(*) FROM t1; 2237COUNT(*) 22381 2239CHECK TABLE t1; 2240Table Op Msg_type Msg_text 2241test.t1 check status OK 2242DROP TABLE t1; 2243CREATE TABLE t1 ( 2244c1 CHAR(130), 2245c2 VARCHAR(1) 2246) ENGINE=MyISAM; 2247INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2248SELECT COUNT(*) FROM t1; 2249COUNT(*) 22501 2251CHECK TABLE t1 EXTENDED; 2252Table Op Msg_type Msg_text 2253test.t1 check status OK 2254REPAIR TABLE t1 EXTENDED; 2255Table Op Msg_type Msg_text 2256test.t1 repair status OK 2257SELECT COUNT(*) FROM t1; 2258COUNT(*) 22591 2260CHECK TABLE t1 EXTENDED; 2261Table Op Msg_type Msg_text 2262test.t1 check status OK 2263DROP TABLE t1; 2264CREATE TABLE t1 ( 2265c1 CHAR(130), 2266c2 VARCHAR(1) 2267) ENGINE=MyISAM; 2268INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2269INSERT INTO t1 VALUES('b', 'b'); 2270INSERT INTO t1 VALUES('c', 'b'); 2271DELETE FROM t1 WHERE c1='b'; 2272SELECT COUNT(*) FROM t1; 2273COUNT(*) 22742 2275OPTIMIZE TABLE t1; 2276Table Op Msg_type Msg_text 2277test.t1 optimize status OK 2278SELECT COUNT(*) FROM t1; 2279COUNT(*) 22802 2281DROP TABLE t1; 2282CREATE TABLE t1 ( 2283c1 CHAR(130), 2284c2 VARCHAR(1), 2285KEY (c1) 2286) ENGINE=MyISAM; 2287# Insert 100 rows. Query log disabled. 2288UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; 2289SELECT COUNT(*) FROM t1; 2290COUNT(*) 2291100 2292ALTER TABLE t1 ENGINE=MyISAM; 2293SELECT COUNT(*) FROM t1; 2294COUNT(*) 2295100 2296CHECK TABLE t1; 2297Table Op Msg_type Msg_text 2298test.t1 check status OK 2299CHECK TABLE t1 EXTENDED; 2300Table Op Msg_type Msg_text 2301test.t1 check status OK 2302DROP TABLE t1; 2303CREATE TABLE t1 ( 2304c1 CHAR(50), 2305c2 VARCHAR(1) 2306) ENGINE=MyISAM DEFAULT CHARSET UTF8; 2307INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2308SELECT COUNT(*) FROM t1; 2309COUNT(*) 23101 2311CHECK TABLE t1; 2312Table Op Msg_type Msg_text 2313test.t1 check status OK 2314REPAIR TABLE t1; 2315Table Op Msg_type Msg_text 2316test.t1 repair status OK 2317SELECT COUNT(*) FROM t1; 2318COUNT(*) 23191 2320CHECK TABLE t1; 2321Table Op Msg_type Msg_text 2322test.t1 check status OK 2323DROP TABLE t1; 2324CREATE TABLE t1 ( 2325c1 CHAR(50), 2326c2 VARCHAR(1) 2327) ENGINE=MyISAM DEFAULT CHARSET UTF8; 2328INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2329SELECT COUNT(*) FROM t1; 2330COUNT(*) 23311 2332CHECK TABLE t1 EXTENDED; 2333Table Op Msg_type Msg_text 2334test.t1 check status OK 2335REPAIR TABLE t1 EXTENDED; 2336Table Op Msg_type Msg_text 2337test.t1 repair status OK 2338SELECT COUNT(*) FROM t1; 2339COUNT(*) 23401 2341CHECK TABLE t1 EXTENDED; 2342Table Op Msg_type Msg_text 2343test.t1 check status OK 2344DROP TABLE t1; 2345CREATE TABLE t1 ( 2346c1 CHAR(50), 2347c2 VARCHAR(1) 2348) ENGINE=MyISAM DEFAULT CHARSET UTF8; 2349INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2350INSERT INTO t1 VALUES('b', 'b'); 2351INSERT INTO t1 VALUES('c', 'b'); 2352DELETE FROM t1 WHERE c1='b'; 2353SELECT COUNT(*) FROM t1; 2354COUNT(*) 23552 2356OPTIMIZE TABLE t1; 2357Table Op Msg_type Msg_text 2358test.t1 optimize status OK 2359SELECT COUNT(*) FROM t1; 2360COUNT(*) 23612 2362DROP TABLE t1; 2363CREATE TABLE t1 ( 2364c1 CHAR(50), 2365c2 VARCHAR(1), 2366KEY (c1) 2367) ENGINE=MyISAM DEFAULT CHARSET UTF8; 2368# Insert 100 rows. Query log disabled. 2369UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; 2370SELECT COUNT(*) FROM t1; 2371COUNT(*) 2372100 2373ALTER TABLE t1 ENGINE=MyISAM; 2374SELECT COUNT(*) FROM t1; 2375COUNT(*) 2376100 2377CHECK TABLE t1; 2378Table Op Msg_type Msg_text 2379test.t1 check status OK 2380CHECK TABLE t1 EXTENDED; 2381Table Op Msg_type Msg_text 2382test.t1 check status OK 2383DROP TABLE t1; 2384CREATE TABLE t1 ( 2385c1 VARCHAR(10) NOT NULL, 2386c2 CHAR(10) DEFAULT NULL, 2387c3 VARCHAR(10) NOT NULL, 2388KEY (c1), 2389KEY (c2) 2390) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0; 2391 2392MyISAM file: MYSQLD_DATADIR/test/t1 2393Record format: Packed 2394Character set: utf8_general_ci (33) 2395Data records: 0 Deleted blocks: 0 2396Recordlength: 94 2397 2398table description: 2399Key Start Len Index Type 24001 2 30 multip. varchar 24012 33 30 multip. char NULL 2402DROP TABLE t1; 2403set statement sql_mode='' for 2404create table t1 (n int not null, c char(1)) transactional=1; 2405Warnings: 2406Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' 2407show create table t1; 2408Table Create Table 2409t1 CREATE TABLE `t1` ( 2410 `n` int(11) NOT NULL, 2411 `c` char(1) DEFAULT NULL 2412) ENGINE=MyISAM DEFAULT CHARSET=latin1 TRANSACTIONAL=1 2413drop table t1; 2414CREATE TABLE t1 (line LINESTRING NOT NULL) engine=myisam; 2415INSERT INTO t1 VALUES (GeomFromText("LINESTRING(0 0)")); 2416checksum table t1; 2417Table Checksum 2418test.t1 310616673 2419CREATE TABLE t2 (line LINESTRING NOT NULL) engine=myisam; 2420INSERT INTO t2 VALUES (GeomFromText("LINESTRING(0 0)")); 2421checksum table t2; 2422Table Checksum 2423test.t2 310616673 2424CREATE TABLE t3 select * from t1; 2425checksum table t3; 2426Table Checksum 2427test.t3 310616673 2428drop table t1,t2,t3; 2429create table t1 (a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int,a10 int,a11 int,a12 int,a13 int,a14 int,a15 int,a16 int,a17 int,a18 int,a19 int,a20 int,a21 int,a22 int,a23 int,a24 int,a25 int,a26 int,a27 int,a28 int,a29 int,a30 int,a31 int,a32 int, 2430key(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32)) engine=myisam; 2431drop table t1; 2432create table t1 (a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int,a10 int,a11 int,a12 int,a13 int,a14 int,a15 int,a16 int,a17 int,a18 int,a19 int,a20 int,a21 int,a22 int,a23 int,a24 int,a25 int,a26 int,a27 int,a28 int,a29 int,a30 int,a31 int,a32 int, a33 int, 2433key(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33)) engine=myisam; 2434ERROR 42000: Too many key parts specified; max 32 parts allowed 2435CREATE TABLE t1 ( 2436c INT, 2437d bit(1), 2438e INT, 2439f VARCHAR(1), 2440g BIT(1), 2441h BIT(1), 2442KEY (h, d, e, g) 2443); 2444INSERT INTO t1 VALUES 2445( 3, 1, 1, 'a', 0, 0 ), 2446( 3, 1, 5, 'a', 0, 0 ), 2447( 10, 1, 2, 'a', 0, 1 ), 2448( 10, 1, 3, 'a', 0, 1 ), 2449( 10, 1, 4, 'a', 0, 1 ); 2450SELECT f FROM t1 WHERE d = 1 AND e = 2 AND g = 0 AND h = 1; 2451f 2452a 2453SELECT h+0, d + 0, e, g + 0 FROM t1; 2454h+0 d + 0 e g + 0 24550 1 1 0 24560 1 5 0 24571 1 2 0 24581 1 3 0 24591 1 4 0 2460DROP TABLE t1; 2461CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); 2462INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), 2463(6,'0'),(7,'0'); 2464INSERT INTO t1 SELECT a+10,b FROM t1; 2465INSERT INTO t1 SELECT a+20,b FROM t1; 2466INSERT INTO t1 SELECT a+40,b FROM t1; 2467INSERT INTO t1 SELECT a+80,b FROM t1; 2468INSERT INTO t1 SELECT a+160,b FROM t1; 2469INSERT INTO t1 SELECT a+320,b FROM t1; 2470INSERT INTO t1 SELECT a+640,b FROM t1; 2471INSERT INTO t1 SELECT a+1280,b FROM t1; 2472INSERT INTO t1 SELECT a+2560,b FROM t1; 2473INSERT INTO t1 SELECT a+5120,b FROM t1; 2474SET myisam_sort_buffer_size=4; 2475Warnings: 2476Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' 2477REPAIR TABLE t1; 2478Table Op Msg_type Msg_text 2479test.t1 repair error myisam_sort_buffer_size is too small. X 2480test.t1 repair warning Number of rows changed from 0 to 7168 2481test.t1 repair status OK 2482SET myisam_repair_threads=2; 2483REPAIR TABLE t1; 2484SET myisam_repair_threads=@@global.myisam_repair_threads; 2485SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; 2486CHECK TABLE t1; 2487Table Op Msg_type Msg_text 2488test.t1 check status OK 2489DROP TABLE t1; 2490# 2491# BUG#49628 - corrupt table after legal SQL, LONGTEXT column 2492# 2493CREATE TABLE t1(a INT, b LONGTEXT, UNIQUE(a)); 2494REPLACE INTO t1 VALUES 2495(1, REPEAT('a', 129015)),(1, NULL), 2496(2, NULL),(3, NULL),(4, NULL),(5, NULL),(6, NULL),(7, NULL), 2497(1, REPEAT('b', 129016)),(1, NULL), 2498(1, REPEAT('c', 129015)),(1, REPEAT('d', 129015)); 2499CHECK TABLE t1; 2500Table Op Msg_type Msg_text 2501test.t1 check status OK 2502DROP TABLE t1; 2503# 2504# Bug#51304: checksum table gives different results 2505# for same data when using bit fields 2506# 2507CREATE TABLE t1(a INT, b BIT(1)); 2508INSERT INTO t1 VALUES(1, 0), (2, 1); 2509CREATE TABLE t2 SELECT * FROM t1; 2510FLUSH TABLES; 2511CHECKSUM TABLE t1 EXTENDED; 2512Table Checksum 2513test.t1 3775188275 2514CHECKSUM TABLE t2 EXTENDED; 2515Table Checksum 2516test.t2 3775188275 2517CHECKSUM TABLE t3 EXTENDED; 2518Table Checksum 2519test.t3 3775188275 2520DROP TABLE t1, t2, t3; 2521# 2522# BUG#51307 - widespread corruption with partitions and insert...select 2523# 2524CREATE TABLE t1(a CHAR(255), KEY(a)); 2525SELECT * FROM t1, t1 AS a1; 2526a a 2527SET myisam_sort_buffer_size=4; 2528Warnings: 2529Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' 2530INSERT INTO t1 VALUES 2531('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2532('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2533('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2534('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2535('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2536('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2537('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2538('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2539('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2540('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2541('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2542('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2543('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2544('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2545('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), 2546('0'),('0'),('0'),('0'),('0'),('0'),('0'); 2547Warnings: 2548Error 1034 myisam_sort_buffer_size is too small. X 2549Error 1034 Number of rows changed from 0 to 157 2550SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; 2551INSERT INTO t1 VALUES('1'); 2552SELECT * FROM t1, t1 AS a1 WHERE t1.a=1 AND a1.a=1; 2553a a 25541 1 2555DROP TABLE t1; 2556# 2557# BUG#47444 - --myisam_repair_threads>1can result in all index 2558# cardinalities=1 2559# 2560SET myisam_repair_threads=2; 2561SET myisam_sort_buffer_size=4096; 2562CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); 2563Warnings: 2564Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release 2565Note 1831 Duplicate index `a_3`. This is deprecated and will be disallowed in a future release 2566INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); 2567REPAIR TABLE t1; 2568Table Op Msg_type Msg_text 2569test.t1 repair status OK 2570SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 2571CARDINALITY 257214 257314 257414 2575DROP TABLE t1; 2576SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; 2577SET myisam_repair_threads=@@global.myisam_repair_threads; 2578# 2579# BUG#11757032 - 49030: OPTIMIZE TABLE BREAKS MYISAM TABLE WHEN 2580# USING MYISAM_USE_MMAP ON WINDOWS 2581# 2582SET GLOBAL myisam_use_mmap=1; 2583CREATE TABLE t1(a INT); 2584INSERT INTO t1 VALUES(1),(2); 2585DELETE FROM t1 WHERE a=1; 2586FLUSH TABLE t1; 2587LOCK TABLE t1 WRITE; 2588OPTIMIZE TABLE t1; 2589Table Op Msg_type Msg_text 2590test.t1 optimize status OK 2591INSERT INTO t1 VALUES(3); 2592UNLOCK TABLES; 2593SELECT * FROM t1; 2594a 25952 25963 2597CHECK TABLE t1; 2598Table Op Msg_type Msg_text 2599test.t1 check status OK 2600DROP TABLE t1; 2601SET GLOBAL myisam_use_mmap=default; 2602# 2603# BUG 11756869 - 48848: MYISAMCHK DOING SORT RECOVER IN CERTAIN 2604# CASES RESETS DATA POINTER TO SMAL 2605# 2606CREATE TABLE t1(a INT, KEY(a)); 2607ALTER TABLE t1 DISABLE KEYS; 2608SET @before:= (SELECT MAX_DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'); 2609FLUSH TABLES; 2610SET @after:= (SELECT MAX_DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'); 2611SELECT @before=@after; 2612@before=@after 26131 2614DROP TABLE t1; 2615End of 5.1 tests 2616# 2617# Bug#51327 MyISAM table is automatically repaired on ALTER 2618# even if myisam-recover is OFF 2619# 2620call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); 2621call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*root Checking table"); 2622call mtr.add_suppression(" '\..test.t1'"); 2623# Test that we can exchange a crashed partition with a table 2624SELECT @@global.myisam_recover_options; 2625@@global.myisam_recover_options 2626OFF 2627CREATE TABLE t1 (a INT, KEY (a)) ENGINE=MyISAM; 2628INSERT INTO t1 VALUES (1), (2); 2629FLUSH TABLES; 2630# replacing t.MYI with a corrupt + unclosed one created by doing: 2631# 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI 2632CHECK TABLE t1; 2633Table Op Msg_type Msg_text 2634test.t1 check warning 1 client is using or hasn't closed the table properly 2635test.t1 check error Size of indexfile is: 1024 Should be: 2048 2636test.t1 check warning Size of datafile is: 14 Should be: 7 2637test.t1 check error Corrupt 2638# Alter table should report error and not auto-repair the table. 2639ALTER TABLE t1 ENGINE = MyISAM; 2640ERROR HY000: Table 't1' is marked as crashed and should be repaired 2641CHECK TABLE t1; 2642Table Op Msg_type Msg_text 2643test.t1 check warning Table is marked as crashed 2644test.t1 check warning 1 client is using or hasn't closed the table properly 2645test.t1 check error Size of indexfile is: 1024 Should be: 2048 2646test.t1 check warning Size of datafile is: 14 Should be: 7 2647test.t1 check error Corrupt 2648DROP TABLE t1; 2649# 2650# MDEV-3870 - Valgrind warnings on OPTIMIZE MyISAM or Aria TABLE with 2651# disabled keys 2652# 2653CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; 2654INSERT INTO t1 VALUES (4),(3),(1),(0); 2655ALTER TABLE t1 DISABLE KEYS; 2656OPTIMIZE TABLE t1; 2657Table Op Msg_type Msg_text 2658test.t1 optimize status OK 2659DROP TABLE t1; 2660CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)) engine=myisam; 2661INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), 2662(6,'0'),(7,'0'); 2663flush tables test.t1 for export; 2664insert into t1 values (8,'0'); 2665ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 2666unlock tables; 2667drop table t1; 2668show variables like 'myisam_block_size'; 2669Variable_name Value 2670myisam_block_size 1024 2671select @@global.myisam_block_size; 2672@@global.myisam_block_size 26731024 2674# 2675# MDEV-20704 An index on a double column erroneously uses prefix compression 2676# 2677CREATE TABLE t1 ( 2678id INT NOT NULL PRIMARY KEY, 2679d DOUBLE, 2680KEY (d) 2681) ENGINE=MyISAM; 2682 2683MyISAM file: MYSQLD_DATADIR/test/t1 2684Record format: Fixed length 2685Character set: latin1_swedish_ci (8) 2686Data records: 0 Deleted blocks: 0 2687Recordlength: 13 2688 2689table description: 2690Key Start Len Index Type 26911 2 4 unique long 26922 6 8 multip. double NULL 2693DROP TABLE t1; 2694# 2695# End of 5.5 tests 2696# 2697