1call mtr.add_suppression("Can't find record in '.*'"); 2select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA"; 3ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS 4Aria YES Crash-safe tables with MyISAM heritage NO NO NO 5set global storage_engine=aria; 6set session storage_engine=aria; 7set global aria_page_checksum=0; 8set global aria_log_file_size=4294959104; 9drop table if exists t1,t2; 10drop view if exists v1; 11SET SQL_WARNINGS=1; 12CREATE TABLE t1 ( 13STRING_DATA char(255) default NULL, 14KEY string_data (STRING_DATA) 15); 16INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); 17INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); 18INSERT INTO t1 VALUES ('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'); 19INSERT INTO t1 VALUES ('FGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'); 20INSERT INTO t1 VALUES ('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'); 21INSERT INTO t1 VALUES ('WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'); 22CHECK TABLE t1; 23Table Op Msg_type Msg_text 24test.t1 check status OK 25drop table t1; 26create table t1 (a int primary key auto_increment) engine=aria; 27insert into t1 values (1); 28update t1 set a=0 where a=1; 29check table t1; 30Table Op Msg_type Msg_text 31test.t1 check warning Found row where the auto_increment column has the value 0 32test.t1 check status OK 33select * from t1; 34a 350 36drop table t1; 37create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); 38check table t1; 39Table Op Msg_type Msg_text 40test.t1 check status OK 41repair table t1; 42Table Op Msg_type Msg_text 43test.t1 repair status OK 44delete from t1 where (a & 1); 45check table t1; 46Table Op Msg_type Msg_text 47test.t1 check status OK 48repair table t1; 49Table Op Msg_type Msg_text 50test.t1 repair status OK 51check table t1; 52Table Op Msg_type Msg_text 53test.t1 check status OK 54flush table t1; 55repair table t1; 56Table Op Msg_type Msg_text 57test.t1 repair status OK 58drop table t1; 59create table t1 (a int not null auto_increment, b int not null, primary key (a), index(b)); 60insert into t1 (b) values (1),(2),(2),(2),(2); 61optimize table t1; 62Table Op Msg_type Msg_text 63test.t1 optimize status OK 64show index from t1; 65Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 66t1 0 PRIMARY 1 a A 5 NULL NULL BTREE 67t1 1 b 1 b A 1 NULL NULL BTREE 68optimize table t1; 69Table Op Msg_type Msg_text 70test.t1 optimize status Table is already up to date 71show index from t1; 72Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 73t1 0 PRIMARY 1 a A 5 NULL NULL BTREE 74t1 1 b 1 b A 1 NULL NULL BTREE 75drop table t1; 76create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)); 77insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); 78explain select * from t1 order by a; 79id select_type table type possible_keys key key_len ref rows Extra 801 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 81explain select * from t1 order by b; 82id select_type table type possible_keys key key_len ref rows Extra 831 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 84explain select * from t1 order by c; 85id select_type table type possible_keys key key_len ref rows Extra 861 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 87explain select a from t1 order by a; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index 90explain select b from t1 order by b; 91id select_type table type possible_keys key key_len ref rows Extra 921 SIMPLE t1 index NULL b 4 NULL 4 Using index 93explain select a,b from t1 order by b; 94id select_type table type possible_keys key key_len ref rows Extra 951 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort 96explain select a,b from t1; 97id select_type table type possible_keys key key_len ref rows Extra 981 SIMPLE t1 ALL NULL NULL NULL NULL 4 99explain select a,b,c from t1; 100id select_type table type possible_keys key key_len ref rows Extra 1011 SIMPLE t1 ALL NULL NULL NULL NULL 4 102drop table t1; 103set autocommit=0; 104begin; 105CREATE TABLE t1 (a INT); 106INSERT INTO t1 VALUES (1), (2), (3); 107LOCK TABLES t1 WRITE; 108INSERT INTO t1 VALUES (1), (2), (3); 109commit; 110set autocommit=1; 111UNLOCK TABLES; 112OPTIMIZE TABLE t1; 113Table Op Msg_type Msg_text 114test.t1 optimize status OK 115DROP TABLE t1; 116create table t1 ( t1 char(255), key(t1(250))); 117insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169'); 118insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203'); 119insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767'); 120insert t1 values ('242624262426242624262426242624262426242607890789078907890789078907890789078907'); 121insert t1 values ('256025602560256025602560256025602560256011701170117011701170117011701170117011'); 122insert t1 values ('276027602760276027602760276027602760276001610161016101610161016101610161016101'); 123insert t1 values ('281528152815281528152815281528152815281564956495649564956495649564956495649564'); 124insert t1 values ('292129212921292129212921292129212921292102100210021002100210021002100210021002'); 125insert t1 values ('380638063806380638063806380638063806380634483448344834483448344834483448344834'); 126insert t1 values ('411641164116411641164116411641164116411616301630163016301630163016301630163016'); 127insert t1 values ('420842084208420842084208420842084208420899889988998899889988998899889988998899'); 128insert t1 values ('438443844384438443844384438443844384438482448244824482448244824482448244824482'); 129insert t1 values ('443244324432443244324432443244324432443239613961396139613961396139613961396139'); 130insert t1 values ('485448544854485448544854485448544854485477847784778477847784778477847784778477'); 131insert t1 values ('494549454945494549454945494549454945494555275527552755275527552755275527552755'); 132insert t1 values ('538647864786478647864786478647864786478688918891889188918891889188918891889188'); 133insert t1 values ('565556555655565556555655565556555655565554845484548454845484548454845484548454'); 134insert t1 values ('607860786078607860786078607860786078607856665666566656665666566656665666566656'); 135insert t1 values ('640164016401640164016401640164016401640141274127412741274127412741274127412741'); 136insert t1 values ('719471947194719471947194719471947194719478717871787178717871787178717871787178'); 137insert t1 values ('742574257425742574257425742574257425742549604960496049604960496049604960496049'); 138insert t1 values ('887088708870887088708870887088708870887035963596359635963596359635963596359635'); 139insert t1 values ('917791779177917791779177917791779177917773857385738573857385738573857385738573'); 140insert t1 values ('933293329332933293329332933293329332933278987898789878987898789878987898789878'); 141insert t1 values ('963896389638963896389638963896389638963877807780778077807780778077807780778077'); 142delete from t1 where t1>'2'; 143insert t1 values ('70'), ('84'), ('60'), ('20'), ('76'), ('89'), ('49'), ('50'), 144('88'), ('61'), ('42'), ('98'), ('39'), ('30'), ('25'), ('66'), ('61'), ('48'), 145('80'), ('84'), ('98'), ('19'), ('91'), ('42'), ('47'); 146optimize table t1; 147Table Op Msg_type Msg_text 148test.t1 optimize status OK 149check table t1; 150Table Op Msg_type Msg_text 151test.t1 check status OK 152drop table t1; 153create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 154int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17 155int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int, 156i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34 157int, i35 int, i36 int, i37 int, i38 int, i39 int, i40 int, i41 int, i42 int, 158i43 int, i44 int, i45 int, i46 int, i47 int, i48 int, i49 int, i50 int, i51 159int, i52 int, i53 int, i54 int, i55 int, i56 int, i57 int, i58 int, i59 int, 160i60 int, i61 int, i62 int, i63 int, i64 int, i65 int, i66 int, i67 int, i68 161int, i69 int, i70 int, i71 int, i72 int, i73 int, i74 int, i75 int, i76 int, 162i77 int, i78 int, i79 int, i80 int, i81 int, i82 int, i83 int, i84 int, i85 163int, i86 int, i87 int, i88 int, i89 int, i90 int, i91 int, i92 int, i93 int, 164i94 int, i95 int, i96 int, i97 int, i98 int, i99 int, i100 int, i101 int, i102 165int, i103 int, i104 int, i105 int, i106 int, i107 int, i108 int, i109 int, i110 166int, i111 int, i112 int, i113 int, i114 int, i115 int, i116 int, i117 int, i118 167int, i119 int, i120 int, i121 int, i122 int, i123 int, i124 int, i125 int, i126 168int, i127 int, i128 int, i129 int, i130 int, i131 int, i132 int, i133 int, i134 169int, i135 int, i136 int, i137 int, i138 int, i139 int, i140 int, i141 int, i142 170int, i143 int, i144 int, i145 int, i146 int, i147 int, i148 int, i149 int, i150 171int, i151 int, i152 int, i153 int, i154 int, i155 int, i156 int, i157 int, i158 172int, i159 int, i160 int, i161 int, i162 int, i163 int, i164 int, i165 int, i166 173int, i167 int, i168 int, i169 int, i170 int, i171 int, i172 int, i173 int, i174 174int, i175 int, i176 int, i177 int, i178 int, i179 int, i180 int, i181 int, i182 175int, i183 int, i184 int, i185 int, i186 int, i187 int, i188 int, i189 int, i190 176int, i191 int, i192 int, i193 int, i194 int, i195 int, i196 int, i197 int, i198 177int, i199 int, i200 int, i201 int, i202 int, i203 int, i204 int, i205 int, i206 178int, i207 int, i208 int, i209 int, i210 int, i211 int, i212 int, i213 int, i214 179int, i215 int, i216 int, i217 int, i218 int, i219 int, i220 int, i221 int, i222 180int, i223 int, i224 int, i225 int, i226 int, i227 int, i228 int, i229 int, i230 181int, i231 int, i232 int, i233 int, i234 int, i235 int, i236 int, i237 int, i238 182int, i239 int, i240 int, i241 int, i242 int, i243 int, i244 int, i245 int, i246 183int, i247 int, i248 int, i249 int, i250 int, i251 int, i252 int, i253 int, i254 184int, i255 int, i256 int, i257 int, i258 int, i259 int, i260 int, i261 int, i262 185int, i263 int, i264 int, i265 int, i266 int, i267 int, i268 int, i269 int, i270 186int, i271 int, i272 int, i273 int, i274 int, i275 int, i276 int, i277 int, i278 187int, i279 int, i280 int, i281 int, i282 int, i283 int, i284 int, i285 int, i286 188int, i287 int, i288 int, i289 int, i290 int, i291 int, i292 int, i293 int, i294 189int, i295 int, i296 int, i297 int, i298 int, i299 int, i300 int, i301 int, i302 190int, i303 int, i304 int, i305 int, i306 int, i307 int, i308 int, i309 int, i310 191int, i311 int, i312 int, i313 int, i314 int, i315 int, i316 int, i317 int, i318 192int, i319 int, i320 int, i321 int, i322 int, i323 int, i324 int, i325 int, i326 193int, i327 int, i328 int, i329 int, i330 int, i331 int, i332 int, i333 int, i334 194int, i335 int, i336 int, i337 int, i338 int, i339 int, i340 int, i341 int, i342 195int, i343 int, i344 int, i345 int, i346 int, i347 int, i348 int, i349 int, i350 196int, i351 int, i352 int, i353 int, i354 int, i355 int, i356 int, i357 int, i358 197int, i359 int, i360 int, i361 int, i362 int, i363 int, i364 int, i365 int, i366 198int, i367 int, i368 int, i369 int, i370 int, i371 int, i372 int, i373 int, i374 199int, i375 int, i376 int, i377 int, i378 int, i379 int, i380 int, i381 int, i382 200int, i383 int, i384 int, i385 int, i386 int, i387 int, i388 int, i389 int, i390 201int, i391 int, i392 int, i393 int, i394 int, i395 int, i396 int, i397 int, i398 202int, i399 int, i400 int, i401 int, i402 int, i403 int, i404 int, i405 int, i406 203int, i407 int, i408 int, i409 int, i410 int, i411 int, i412 int, i413 int, i414 204int, i415 int, i416 int, i417 int, i418 int, i419 int, i420 int, i421 int, i422 205int, i423 int, i424 int, i425 int, i426 int, i427 int, i428 int, i429 int, i430 206int, i431 int, i432 int, i433 int, i434 int, i435 int, i436 int, i437 int, i438 207int, i439 int, i440 int, i441 int, i442 int, i443 int, i444 int, i445 int, i446 208int, i447 int, i448 int, i449 int, i450 int, i451 int, i452 int, i453 int, i454 209int, i455 int, i456 int, i457 int, i458 int, i459 int, i460 int, i461 int, i462 210int, i463 int, i464 int, i465 int, i466 int, i467 int, i468 int, i469 int, i470 211int, i471 int, i472 int, i473 int, i474 int, i475 int, i476 int, i477 int, i478 212int, i479 int, i480 int, i481 int, i482 int, i483 int, i484 int, i485 int, i486 213int, i487 int, i488 int, i489 int, i490 int, i491 int, i492 int, i493 int, i494 214int, i495 int, i496 int, i497 int, i498 int, i499 int, i500 int, i501 int, i502 215int, i503 int, i504 int, i505 int, i506 int, i507 int, i508 int, i509 int, i510 216int, i511 int, i512 int, i513 int, i514 int, i515 int, i516 int, i517 int, i518 217int, i519 int, i520 int, i521 int, i522 int, i523 int, i524 int, i525 int, i526 218int, i527 int, i528 int, i529 int, i530 int, i531 int, i532 int, i533 int, i534 219int, i535 int, i536 int, i537 int, i538 int, i539 int, i540 int, i541 int, i542 220int, i543 int, i544 int, i545 int, i546 int, i547 int, i548 int, i549 int, i550 221int, i551 int, i552 int, i553 int, i554 int, i555 int, i556 int, i557 int, i558 222int, i559 int, i560 int, i561 int, i562 int, i563 int, i564 int, i565 int, i566 223int, i567 int, i568 int, i569 int, i570 int, i571 int, i572 int, i573 int, i574 224int, i575 int, i576 int, i577 int, i578 int, i579 int, i580 int, i581 int, i582 225int, i583 int, i584 int, i585 int, i586 int, i587 int, i588 int, i589 int, i590 226int, i591 int, i592 int, i593 int, i594 int, i595 int, i596 int, i597 int, i598 227int, i599 int, i600 int, i601 int, i602 int, i603 int, i604 int, i605 int, i606 228int, i607 int, i608 int, i609 int, i610 int, i611 int, i612 int, i613 int, i614 229int, i615 int, i616 int, i617 int, i618 int, i619 int, i620 int, i621 int, i622 230int, i623 int, i624 int, i625 int, i626 int, i627 int, i628 int, i629 int, i630 231int, i631 int, i632 int, i633 int, i634 int, i635 int, i636 int, i637 int, i638 232int, i639 int, i640 int, i641 int, i642 int, i643 int, i644 int, i645 int, i646 233int, i647 int, i648 int, i649 int, i650 int, i651 int, i652 int, i653 int, i654 234int, i655 int, i656 int, i657 int, i658 int, i659 int, i660 int, i661 int, i662 235int, i663 int, i664 int, i665 int, i666 int, i667 int, i668 int, i669 int, i670 236int, i671 int, i672 int, i673 int, i674 int, i675 int, i676 int, i677 int, i678 237int, i679 int, i680 int, i681 int, i682 int, i683 int, i684 int, i685 int, i686 238int, i687 int, i688 int, i689 int, i690 int, i691 int, i692 int, i693 int, i694 239int, i695 int, i696 int, i697 int, i698 int, i699 int, i700 int, i701 int, i702 240int, i703 int, i704 int, i705 int, i706 int, i707 int, i708 int, i709 int, i710 241int, i711 int, i712 int, i713 int, i714 int, i715 int, i716 int, i717 int, i718 242int, i719 int, i720 int, i721 int, i722 int, i723 int, i724 int, i725 int, i726 243int, i727 int, i728 int, i729 int, i730 int, i731 int, i732 int, i733 int, i734 244int, i735 int, i736 int, i737 int, i738 int, i739 int, i740 int, i741 int, i742 245int, i743 int, i744 int, i745 int, i746 int, i747 int, i748 int, i749 int, i750 246int, i751 int, i752 int, i753 int, i754 int, i755 int, i756 int, i757 int, i758 247int, i759 int, i760 int, i761 int, i762 int, i763 int, i764 int, i765 int, i766 248int, i767 int, i768 int, i769 int, i770 int, i771 int, i772 int, i773 int, i774 249int, i775 int, i776 int, i777 int, i778 int, i779 int, i780 int, i781 int, i782 250int, i783 int, i784 int, i785 int, i786 int, i787 int, i788 int, i789 int, i790 251int, i791 int, i792 int, i793 int, i794 int, i795 int, i796 int, i797 int, i798 252int, i799 int, i800 int, i801 int, i802 int, i803 int, i804 int, i805 int, i806 253int, i807 int, i808 int, i809 int, i810 int, i811 int, i812 int, i813 int, i814 254int, i815 int, i816 int, i817 int, i818 int, i819 int, i820 int, i821 int, i822 255int, i823 int, i824 int, i825 int, i826 int, i827 int, i828 int, i829 int, i830 256int, i831 int, i832 int, i833 int, i834 int, i835 int, i836 int, i837 int, i838 257int, i839 int, i840 int, i841 int, i842 int, i843 int, i844 int, i845 int, i846 258int, i847 int, i848 int, i849 int, i850 int, i851 int, i852 int, i853 int, i854 259int, i855 int, i856 int, i857 int, i858 int, i859 int, i860 int, i861 int, i862 260int, i863 int, i864 int, i865 int, i866 int, i867 int, i868 int, i869 int, i870 261int, i871 int, i872 int, i873 int, i874 int, i875 int, i876 int, i877 int, i878 262int, i879 int, i880 int, i881 int, i882 int, i883 int, i884 int, i885 int, i886 263int, i887 int, i888 int, i889 int, i890 int, i891 int, i892 int, i893 int, i894 264int, i895 int, i896 int, i897 int, i898 int, i899 int, i900 int, i901 int, i902 265int, i903 int, i904 int, i905 int, i906 int, i907 int, i908 int, i909 int, i910 266int, i911 int, i912 int, i913 int, i914 int, i915 int, i916 int, i917 int, i918 267int, i919 int, i920 int, i921 int, i922 int, i923 int, i924 int, i925 int, i926 268int, i927 int, i928 int, i929 int, i930 int, i931 int, i932 int, i933 int, i934 269int, i935 int, i936 int, i937 int, i938 int, i939 int, i940 int, i941 int, i942 270int, i943 int, i944 int, i945 int, i946 int, i947 int, i948 int, i949 int, i950 271int, i951 int, i952 int, i953 int, i954 int, i955 int, i956 int, i957 int, i958 272int, i959 int, i960 int, i961 int, i962 int, i963 int, i964 int, i965 int, i966 273int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 274int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 275int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 276int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 277int, i999 int, i1000 int, b blob) row_format=dynamic; 278insert into t1 values (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, 1, 1, 1, 1, 1, 1, 1, 2901, 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, 2911, 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, 2921, 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, 2931, 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, 2941, 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, 2951, 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, 2961, 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, 2971, 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, 2981, 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, 2991, 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, 3001, 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, 3011, 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, 3021, 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, 3031, 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, 3041, 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, 3051, 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, 3061, 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, 3071, 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, 3081, 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, 3091, 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, 3101, 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, 3111, 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, 3121, 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, 3131, 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, 3141, 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, 3151, 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, 3161, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); 317update t1 set b=repeat('a',256); 318update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0; 319check table t1; 320Table Op Msg_type Msg_text 321test.t1 check status OK 322delete from t1 where i8=1; 323select i1,i2 from t1; 324i1 i2 325check table t1; 326Table Op Msg_type Msg_text 327test.t1 check status OK 328drop table t1; 329CREATE TABLE `t1` ( 330`post_id` mediumint(8) unsigned NOT NULL auto_increment, 331`topic_id` mediumint(8) unsigned NOT NULL default '0', 332`post_time` datetime NOT NULL default '0000-00-00 00:00:00', 333`post_text` text NOT NULL, 334`icon_url` varchar(10) NOT NULL default '', 335`sign` tinyint(1) unsigned NOT NULL default '0', 336`post_edit` varchar(150) NOT NULL default '', 337`poster_login` varchar(35) NOT NULL default '', 338`ip` varchar(15) NOT NULL default '', 339PRIMARY KEY (`post_id`), 340KEY `post_time` (`post_time`), 341KEY `ip` (`ip`), 342KEY `poster_login` (`poster_login`), 343KEY `topic_id` (`topic_id`), 344FULLTEXT KEY `post_text` (`post_text`) 345) TRANSACTIONAL=0; 346INSERT 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'); 347REPAIR TABLE t1; 348Table Op Msg_type Msg_text 349test.t1 repair status OK 350CHECK TABLE t1; 351Table Op Msg_type Msg_text 352test.t1 check status OK 353drop table t1; 354CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), KEY t1 (a, b, c, d, e)); 355ERROR 42000: Specified key was too long; max key length is 1000 bytes 356CREATE TABLE t1 (a varchar(32000), unique key(a)); 357ERROR 42000: Specified key was too long; max key length is 1000 bytes 358CREATE TABLE t1 (a varchar(1), b varchar(1), key (a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b)); 359ERROR 42000: Too many key parts specified; max 32 parts allowed 360CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255)); 361ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e); 362ERROR 42000: Specified key was too long; max key length is 1000 bytes 363DROP TABLE t1; 364CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); 365INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); 366INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); 367create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); 368INSERT into t2 values (1,1,1), (2,2,2); 369optimize table t1; 370Table Op Msg_type Msg_text 371test.t1 optimize status OK 372check table t1; 373Table Op Msg_type Msg_text 374test.t1 check status OK 375show index from t1; 376Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 377t1 1 b 1 b A 10 NULL NULL YES BTREE 378t1 1 c 1 c A 10 NULL NULL YES BTREE 379t1 1 a 1 a A 10 NULL NULL BTREE 380t1 1 a 2 b A 10 NULL NULL YES BTREE 381t1 1 c_2 1 c A 10 NULL NULL YES BTREE 382t1 1 c_2 2 a A 10 NULL NULL BTREE 383explain select * from t1,t2 where t1.a=t2.a; 384id select_type table type possible_keys key key_len ref rows Extra 3851 SIMPLE t2 ALL a NULL NULL NULL 2 3861 SIMPLE t1 ref a a 4 test.t2.a 1 387explain select * from t1,t2 force index(a) where t1.a=t2.a; 388id select_type table type possible_keys key key_len ref rows Extra 3891 SIMPLE t2 ALL a NULL NULL NULL 2 3901 SIMPLE t1 ref a a 4 test.t2.a 1 391explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; 392id select_type table type possible_keys key key_len ref rows Extra 3931 SIMPLE t2 ALL a NULL NULL NULL 2 3941 SIMPLE t1 ref a a 4 test.t2.a 1 395explain select * from t1,t2 where t1.b=t2.b; 396id select_type table type possible_keys key key_len ref rows Extra 3971 SIMPLE t2 ALL b NULL NULL NULL 2 Using where 3981 SIMPLE t1 ref b b 5 test.t2.b 1 399explain select * from t1,t2 force index(c) where t1.a=t2.a; 400id select_type table type possible_keys key key_len ref rows Extra 4011 SIMPLE t2 ALL NULL NULL NULL NULL 2 4021 SIMPLE t1 ref a a 4 test.t2.a 1 403explain select * from t1 where a=0 or a=2; 404id select_type table type possible_keys key key_len ref rows Extra 4051 SIMPLE t1 range a a 4 NULL 5 Using index condition; Using where 406explain select * from t1 force index (a) where a=0 or a=2; 407id select_type table type possible_keys key key_len ref rows Extra 4081 SIMPLE t1 range a a 4 NULL 5 Using index condition; Using where 409explain select * from t1 where c=1; 410id select_type table type possible_keys key key_len ref rows Extra 4111 SIMPLE t1 ref c,c_2 c 5 const 2 412explain select * from t1 use index() where c=1; 413id select_type table type possible_keys key key_len ref rows Extra 4141 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 415drop table t1,t2; 416create table t1 (a int not null auto_increment primary key, b varchar(255)); 417insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); 418update t1 set b=repeat(left(b,1),200) where a=1; 419delete from t1 where (a & 1)= 0; 420update t1 set b=repeat('e',200) where a=1; 421flush tables; 422check table t1; 423Table Op Msg_type Msg_text 424test.t1 check status OK 425update t1 set b=repeat(left(b,1),255) where a between 1 and 5; 426update t1 set b=repeat(left(b,1),10) where a between 32 and 43; 427update t1 set b=repeat(left(b,1),2) where a between 64 and 66; 428update t1 set b=repeat(left(b,1),65) where a between 67 and 70; 429check table t1; 430Table Op Msg_type Msg_text 431test.t1 check status OK 432insert into t1 (b) values (repeat('z',100)); 433update t1 set b="test" where left(b,1) > 'n'; 434check table t1; 435Table Op Msg_type Msg_text 436test.t1 check status OK 437drop table t1; 438create table t1 ( a text not null, key a (a(20))); 439insert into t1 values ('aaa '),('aaa'),('aa'); 440check table t1; 441Table Op Msg_type Msg_text 442test.t1 check status OK 443repair table t1; 444Table Op Msg_type Msg_text 445test.t1 repair status OK 446select concat(a,'.') from t1 where a='aaa'; 447concat(a,'.') 448aaa . 449aaa. 450select concat(a,'.') from t1 where binary a='aaa'; 451concat(a,'.') 452aaa. 453update t1 set a='bbb' where a='aaa'; 454select concat(a,'.') from t1; 455concat(a,'.') 456bbb. 457bbb. 458aa. 459drop table t1; 460create table t1 ( a text not null, key a (a(20))) row_format=dynamic; 461insert into t1 values ('aaa '),('aaa'),('aa'); 462check table t1; 463Table Op Msg_type Msg_text 464test.t1 check status OK 465repair table t1; 466Table Op Msg_type Msg_text 467test.t1 repair status OK 468select concat(a,'.') from t1 where a='aaa'; 469concat(a,'.') 470aaa . 471aaa. 472select concat(a,'.') from t1 where binary a='aaa'; 473concat(a,'.') 474aaa. 475update t1 set a='bbb' where a='aaa'; 476select concat(a,'.') from t1; 477concat(a,'.') 478bbb. 479bbb. 480aa. 481drop table t1; 482create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); 483insert into t1 values('807780', '477', '165'); 484insert into t1 values('807780', '477', '162'); 485insert into t1 values('807780', '472', '162'); 486select * from t1 where a='807780' and b='477' and c='165'; 487a b c 488807780 477 165 489drop table t1; 490CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); 491INSERT t1 VALUES ("can \tcan"); 492INSERT t1 VALUES ("can can"); 493INSERT t1 VALUES ("can"); 494SELECT * FROM t1; 495a 496can can 497can 498can can 499CHECK TABLE t1; 500Table Op Msg_type Msg_text 501test.t1 check status OK 502DROP TABLE t1; 503create table t1 (a blob); 504insert into t1 values('a '),('a'); 505select concat(a,'.') from t1 where a='a'; 506concat(a,'.') 507a. 508select concat(a,'.') from t1 where a='a '; 509concat(a,'.') 510a . 511alter table t1 add key(a(2)); 512select concat(a,'.') from t1 where a='a'; 513concat(a,'.') 514a. 515select concat(a,'.') from t1 where a='a '; 516concat(a,'.') 517a . 518drop table t1; 519create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); 520insert into t1 (b) values ('a'),('b'),('c'); 521select concat(b,'.') from t1; 522concat(b,'.') 523a. 524b. 525c. 526update t1 set b='b ' where a=2; 527update t1 set b='b ' where a > 1; 528ERROR 23000: Duplicate entry 'b ' for key 'b' 529insert into t1 (b) values ('b'); 530ERROR 23000: Duplicate entry 'b' for key 'b' 531select * from t1; 532a b 5331 a 5342 b 5353 c 536delete from t1 where b='b'; 537select a,concat(b,'.') from t1; 538a concat(b,'.') 5391 a. 5403 c. 541drop table t1; 542create table t1 (a int not null); 543create table t2 (a int not null, primary key (a)); 544insert into t1 values (1); 545insert into t2 values (1),(2); 546select sql_big_result distinct t1.a from t1,t2 order by t2.a; 547a 5481 549select distinct t1.a from t1,t2 order by t2.a; 550a 5511 552select sql_big_result distinct t1.a from t1,t2; 553a 5541 555explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; 556id select_type table type possible_keys key key_len ref rows Extra 5571 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary 5581 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index 559explain select distinct t1.a from t1,t2 order by t2.a; 560id select_type table type possible_keys key key_len ref rows Extra 5611 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary 5621 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index 563drop table t1,t2; 564create table t1 ( 565c1 varchar(32), 566key (c1) 567); 568alter table t1 disable keys; 569insert into t1 values ('a'), ('b'); 570select c1 from t1 order by c1 limit 1; 571c1 572a 573drop table t1; 574create table t1 (a int not null, primary key(a)) ROW_FORMAT=FIXED; 575create table t2 (a int not null, b int not null, primary key(a,b)) ROW_FORMAT=FIXED; 576insert into t1 values (1),(2),(3),(4),(5),(6); 577insert into t2 values (1,1),(2,1); 578set autocommit=0; 579begin; 580lock tables t1 read local, t2 read local; 581select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; 582a a b 5831 1 1 5842 2 1 585connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 586insert into t2 values(2,0); 587commit; 588disconnect root; 589connection default; 590select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; 591a a b 5921 1 1 5932 2 1 594unlock tables; 595drop table t1,t2; 596set autocommit=1; 597CREATE TABLE t1 (c1 varchar(250) NOT NULL) ROW_FORMAT=DYNAMIC; 598CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)) ROW_FORMAT=DYNAMIC; 599INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); 600INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); 601LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; 602SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 603WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; 604t1c1 t2c1 605connect con1,localhost,root,,; 606connection con1; 607INSERT INTO t2 VALUES ('test000001'), ('test000005'); 608disconnect con1; 609connection default; 610SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 611WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; 612t1c1 t2c1 613unlock tables; 614DROP TABLE t1,t2; 615CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)); 616Got one of the listed errors 617create table t1 (a int, b varchar(200), c text not null) checksum=1; 618create table t2 (a int, b varchar(200), c text not null) checksum=0; 619insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); 620insert t2 select * from t1; 621checksum table t1, t2, t3 quick; 622Table Checksum 623test.t1 3442722830 624test.t2 NULL 625test.t3 NULL 626Warnings: 627Error 1146 Table 'test.t3' doesn't exist 628checksum table t1, t2, t3; 629Table Checksum 630test.t1 3442722830 631test.t2 3442722830 632test.t3 NULL 633Warnings: 634Error 1146 Table 'test.t3' doesn't exist 635checksum table t1, t2, t3 extended; 636Table Checksum 637test.t1 3442722830 638test.t2 3442722830 639test.t3 NULL 640Warnings: 641Error 1146 Table 'test.t3' doesn't exist 642drop table t1,t2; 643create table t1 (a int, key (a)); 644show keys from t1; 645Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 646t1 1 a 1 a A NULL NULL NULL YES BTREE 647alter table t1 disable keys; 648show keys from t1; 649Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 650t1 1 a 1 a A NULL NULL NULL YES BTREE disabled 651create table t2 (a int); 652set @@rand_seed1=31415926,@@rand_seed2=2718281828; 653insert t1 select * from t2; 654show keys from t1; 655Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 656t1 1 a 1 a A NULL NULL NULL YES BTREE disabled 657alter table t1 enable keys; 658show keys from t1; 659Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 660t1 1 a 1 a A 1000 NULL NULL YES BTREE 661alter table t1 engine=heap; 662alter table t1 disable keys; 663Warnings: 664Note 1031 Storage engine MEMORY of the table `test`.`t1` doesn't have this option 665show keys from t1; 666Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 667t1 1 a 1 a NULL 500 NULL NULL YES HASH 668drop table t1,t2; 669create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); 670insert into t1 values (null,''), (null,''); 671explain select count(*) from t1 where a is null; 672id select_type table type possible_keys key key_len ref rows Extra 6731 SIMPLE t1 ref idx idx 4 const 2 Using where 674insert into t1 values(1,'a'); 675explain select count(*) from t1 where a is null; 676id select_type table type possible_keys key key_len ref rows Extra 6771 SIMPLE t1 ref idx idx 4 const 2 Using where 678explain select count(*) from t1 where a = 'a'; 679id select_type table type possible_keys key key_len ref rows Extra 6801 SIMPLE t1 ref idx idx 4 const 1 Using where 681select count(*) from t1 where a is null; 682count(*) 6832 684drop table t1; 685create table t1 (c1 int, c2 varchar(4) not null default '', 686key(c2(3))) default charset=utf8; 687insert into t1 values (1,'A'), (2, 'B'), (3, 'A'); 688update t1 set c2='A B' where c1=2; 689check table t1; 690Table Op Msg_type Msg_text 691test.t1 check status OK 692drop table t1; 693create table t1 (c1 int); 694insert into t1 values (1),(2),(3),(4); 695checksum table t1; 696Table Checksum 697test.t1 149057747 698delete from t1 where c1 = 1; 699create table t2 as select * from t1; 700checksum table t1; 701Table Checksum 702test.t1 984116287 703checksum table t2; 704Table Checksum 705test.t2 984116287 706drop table t1, t2; 707CREATE TABLE t1 ( 708twenty int(4), 709hundred int(4) NOT NULL 710) CHECKSUM=1; 711INSERT INTO t1 VALUES (11,91); 712check table t1 extended; 713Table Op Msg_type Msg_text 714test.t1 check status OK 715checksum table t1; 716Table Checksum 717test.t1 3235292310 718checksum table t1 extended; 719Table Checksum 720test.t1 3235292310 721alter table t1 row_format=fixed; 722checksum table t1; 723Table Checksum 724test.t1 3235292310 725alter table t1 row_format=dynamic; 726checksum table t1; 727Table Checksum 728test.t1 4183529555 729alter table t1 engine=myisam; 730checksum table t1; 731Table Checksum 732test.t1 4183529555 733drop table t1; 734show variables like 'aria_stats_method'; 735Variable_name Value 736aria_stats_method nulls_unequal 737create table t1 (a int, key(a)); 738insert into t1 values (0),(1),(2),(3),(4); 739insert into t1 select NULL from t1; 740analyze table t1; 741Table Op Msg_type Msg_text 742test.t1 analyze status OK 743show index from t1; 744Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 745t1 1 a 1 a A 10 NULL NULL YES BTREE 746insert into t1 values (11); 747delete from t1 where a=11; 748check table t1; 749Table Op Msg_type Msg_text 750test.t1 check status OK 751show index from t1; 752Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 753t1 1 a 1 a A 10 NULL NULL YES BTREE 754set aria_stats_method=nulls_equal; 755show variables like 'aria_stats_method'; 756Variable_name Value 757aria_stats_method nulls_equal 758insert into t1 values (11); 759delete from t1 where a=11; 760analyze table t1; 761Table Op Msg_type Msg_text 762test.t1 analyze status OK 763show index from t1; 764Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 765t1 1 a 1 a A 5 NULL NULL YES BTREE 766insert into t1 values (11); 767delete from t1 where a=11; 768check table t1; 769Table Op Msg_type Msg_text 770test.t1 check status OK 771show index from t1; 772Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 773t1 1 a 1 a A 5 NULL NULL YES BTREE 774set aria_stats_method=DEFAULT; 775show variables like 'aria_stats_method'; 776Variable_name Value 777aria_stats_method nulls_unequal 778insert into t1 values (11); 779delete from t1 where a=11; 780analyze table t1; 781Table Op Msg_type Msg_text 782test.t1 analyze status OK 783show index from t1; 784Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 785t1 1 a 1 a A 10 NULL NULL YES BTREE 786insert into t1 values (11); 787delete from t1 where a=11; 788check table t1; 789Table Op Msg_type Msg_text 790test.t1 check status OK 791show index from t1; 792Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 793t1 1 a 1 a A 10 NULL NULL YES BTREE 794drop table t1; 795set aria_stats_method=nulls_ignored; 796show variables like 'aria_stats_method'; 797Variable_name Value 798aria_stats_method nulls_ignored 799create table t1 ( 800a char(3), b char(4), c char(5), d char(6), 801key(a,b,c,d) 802); 803insert into t1 values ('bcd','def1', NULL, 'zz'); 804insert into t1 values ('bcd','def2', NULL, 'zz'); 805insert into t1 values ('bce','def1', 'yuu', NULL); 806insert into t1 values ('bce','def2', NULL, 'quux'); 807analyze table t1; 808Table Op Msg_type Msg_text 809test.t1 analyze status OK 810show index from t1; 811Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 812t1 1 a 1 a A 2 NULL NULL YES BTREE 813t1 1 a 2 b A 4 NULL NULL YES BTREE 814t1 1 a 3 c A 4 NULL NULL YES BTREE 815t1 1 a 4 d A 4 NULL NULL YES BTREE 816delete from t1; 817analyze table t1; 818Table Op Msg_type Msg_text 819test.t1 analyze status OK 820show index from t1; 821Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 822t1 1 a 1 a A 0 NULL NULL YES BTREE 823t1 1 a 2 b A 0 NULL NULL YES BTREE 824t1 1 a 3 c A 0 NULL NULL YES BTREE 825t1 1 a 4 d A 0 NULL NULL YES BTREE 826set aria_stats_method=DEFAULT; 827drop table t1; 828create table t1( 829cip INT NOT NULL, 830time TIME NOT NULL, 831score INT NOT NULL DEFAULT 0, 832bob TINYBLOB 833); 834insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); 835insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), 836(6, 'c', '00:06'); 837select * from t1 where bob is null and cip=1; 838cip time score bob 8391 00:01:00 0 NULL 840create index bug on t1 (bob(22), cip, time); 841select * from t1 where bob is null and cip=1; 842cip time score bob 8431 00:01:00 0 NULL 844drop table t1; 845create table t1 ( 846id1 int not null auto_increment, 847id2 int not null default '0', 848t text not null, 849primary key (id1), 850key x (id2, t(32)) 851) engine=aria; 852insert into t1 (id2, t) values 853(10, 'abc'), (10, 'abc'), (10, 'abc'), 854(20, 'abc'), (20, 'abc'), (20, 'def'), 855(10, 'abc'), (10, 'abc'); 856select count(*) from t1 where id2 = 10; 857count(*) 8585 859select count(id1) from t1 where id2 = 10; 860count(id1) 8615 862drop table t1; 863CREATE TABLE t1(a TINYINT, KEY(a)); 864INSERT INTO t1 VALUES(1); 865SELECT MAX(a) FROM t1 IGNORE INDEX(a); 866MAX(a) 8671 868ALTER TABLE t1 DISABLE KEYS; 869SELECT MAX(a) FROM t1; 870MAX(a) 8711 872SELECT MAX(a) FROM t1 IGNORE INDEX(a); 873MAX(a) 8741 875DROP TABLE t1; 876CREATE TABLE t1(a CHAR(9), b VARCHAR(7)); 877INSERT INTO t1(a) VALUES('xxxxxxxxx'),('xxxxxxxxx'); 878UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; 879SELECT * FROM t1; 880a b 881xxxxxxxxx bbbbbb 882xxxxxxxxx bbbbbb 883DROP TABLE t1; 884SET @@aria_repair_threads=2; 885SHOW VARIABLES LIKE 'aria_repair%'; 886Variable_name Value 887aria_repair_threads 2 888CREATE TABLE t1 ( 889`_id` int(11) NOT NULL default '0', 890`url` text, 891`email` text, 892`description` text, 893`loverlap` int(11) default NULL, 894`roverlap` int(11) default NULL, 895`lneighbor_id` int(11) default NULL, 896`rneighbor_id` int(11) default NULL, 897`length_` int(11) default NULL, 898`sequence` mediumtext, 899`name` text, 900`_obj_class` text NOT NULL, 901PRIMARY KEY (`_id`), 902UNIQUE KEY `sequence_name_index` (`name`(50)), 903KEY (`length_`) 904) DEFAULT CHARSET=latin1 row_format=dynamic; 905INSERT INTO t1 VALUES 906(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), 907(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), 908(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), 909(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), 910(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), 911(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), 912(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), 913(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), 914(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); 915SELECT _id FROM t1; 916_id 9171 9182 9193 9204 9215 9226 9237 9248 9259 926DELETE FROM t1 WHERE _id < 8; 927SELECT 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'; 928table_name engine version row_format Table_rows Data_free create_options table_comment 929t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC 930CHECK TABLE t1 EXTENDED; 931Table Op Msg_type Msg_text 932test.t1 check status OK 933OPTIMIZE TABLE t1; 934Table Op Msg_type Msg_text 935test.t1 optimize status OK 936CHECK TABLE t1 EXTENDED; 937Table Op Msg_type Msg_text 938test.t1 check status OK 939SELECT 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'; 940table_name engine version row_format Table_rows Data_free create_options table_comment 941t1 Aria 10 Dynamic 2 0 row_format=DYNAMIC 942SELECT _id FROM t1; 943_id 9448 9459 946DROP TABLE t1; 947CREATE TABLE t1 ( 948`_id` int(11) NOT NULL default '0', 949`url` text, 950`email` text, 951`description` text, 952`loverlap` int(11) default NULL, 953`roverlap` int(11) default NULL, 954`lneighbor_id` int(11) default NULL, 955`rneighbor_id` int(11) default NULL, 956`length_` int(11) default NULL, 957`sequence` mediumtext, 958`name` text, 959`_obj_class` text NOT NULL, 960PRIMARY KEY (`_id`), 961UNIQUE KEY `sequence_name_index` (`name`(50)), 962KEY (`length_`) 963) DEFAULT CHARSET=latin1 row_format=dynamic; 964INSERT INTO t1 VALUES 965(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), 966(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), 967(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), 968(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), 969(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), 970(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), 971(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), 972(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), 973(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); 974SELECT _id FROM t1; 975_id 9761 9772 9783 9794 9805 9816 9827 9838 9849 985DELETE FROM t1 WHERE _id < 8; 986SELECT 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'; 987table_name engine version row_format Table_rows Data_free create_options table_comment 988t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC 989CHECK TABLE t1 EXTENDED; 990Table Op Msg_type Msg_text 991test.t1 check status OK 992REPAIR TABLE t1 QUICK; 993Table Op Msg_type Msg_text 994test.t1 repair status OK 995CHECK TABLE t1 EXTENDED; 996Table Op Msg_type Msg_text 997test.t1 check status OK 998SELECT 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'; 999table_name engine version row_format Table_rows Data_free create_options table_comment 1000t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC 1001SELECT _id FROM t1; 1002_id 10038 10049 1005DROP TABLE t1; 1006SET @@aria_repair_threads=1; 1007SHOW VARIABLES LIKE 'aria_repair%'; 1008Variable_name Value 1009aria_repair_threads 1 1010drop table if exists t1,t2,t3; 1011--- Testing varchar --- 1012--- Testing varchar --- 1013create table t1 (v varchar(10), c char(10), t text); 1014insert into t1 values('+ ', '+ ', '+ '); 1015set @a=repeat(' ',20); 1016insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 1017Warnings: 1018Note 1265 Data truncated for column 'v' at row 1 1019select concat('*',v,'*',c,'*',t,'*') from t1; 1020concat('*',v,'*',c,'*',t,'*') 1021*+ *+*+ * 1022*+ *+*+ * 1023show create table t1; 1024Table Create Table 1025t1 CREATE TABLE `t1` ( 1026 `v` varchar(10) DEFAULT NULL, 1027 `c` char(10) DEFAULT NULL, 1028 `t` text DEFAULT NULL 1029) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1030create table t2 like t1; 1031show create table t2; 1032Table Create Table 1033t2 CREATE TABLE `t2` ( 1034 `v` varchar(10) DEFAULT NULL, 1035 `c` char(10) DEFAULT NULL, 1036 `t` text DEFAULT NULL 1037) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1038create table t3 select * from t1; 1039show create table t3; 1040Table Create Table 1041t3 CREATE TABLE `t3` ( 1042 `v` varchar(10) DEFAULT NULL, 1043 `c` char(10) DEFAULT NULL, 1044 `t` text DEFAULT NULL 1045) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1046alter table t1 modify c varchar(10); 1047show create table t1; 1048Table Create Table 1049t1 CREATE TABLE `t1` ( 1050 `v` varchar(10) DEFAULT NULL, 1051 `c` varchar(10) DEFAULT NULL, 1052 `t` text DEFAULT NULL 1053) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1054alter table t1 modify v char(10); 1055show create table t1; 1056Table Create Table 1057t1 CREATE TABLE `t1` ( 1058 `v` char(10) DEFAULT NULL, 1059 `c` varchar(10) DEFAULT NULL, 1060 `t` text DEFAULT NULL 1061) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1062alter table t1 modify t varchar(10); 1063Warnings: 1064Note 1265 Data truncated for column 't' at row 2 1065show create table t1; 1066Table Create Table 1067t1 CREATE TABLE `t1` ( 1068 `v` char(10) DEFAULT NULL, 1069 `c` varchar(10) DEFAULT NULL, 1070 `t` varchar(10) DEFAULT NULL 1071) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1072select concat('*',v,'*',c,'*',t,'*') from t1; 1073concat('*',v,'*',c,'*',t,'*') 1074*+*+*+ * 1075*+*+*+ * 1076drop table t1,t2,t3; 1077create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); 1078show create table t1; 1079Table Create Table 1080t1 CREATE TABLE `t1` ( 1081 `v` varchar(10) DEFAULT NULL, 1082 `c` char(10) DEFAULT NULL, 1083 `t` text DEFAULT NULL, 1084 KEY `v` (`v`), 1085 KEY `c` (`c`), 1086 KEY `t` (`t`(10)) 1087) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1088select count(*) from t1; 1089count(*) 1090270 1091insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 1092select count(*) from t1 where v='a'; 1093count(*) 109410 1095select count(*) from t1 where c='a'; 1096count(*) 109710 1098select count(*) from t1 where t='a'; 1099count(*) 110010 1101select count(*) from t1 where v='a '; 1102count(*) 110310 1104select count(*) from t1 where c='a '; 1105count(*) 110610 1107select count(*) from t1 where t='a '; 1108count(*) 110910 1110select count(*) from t1 where v between 'a' and 'a '; 1111count(*) 111210 1113select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1114count(*) 111510 1116select count(*) from t1 where v like 'a%'; 1117count(*) 111811 1119select count(*) from t1 where c like 'a%'; 1120count(*) 112111 1122select count(*) from t1 where t like 'a%'; 1123count(*) 112411 1125select count(*) from t1 where v like 'a %'; 1126count(*) 11279 1128explain select count(*) from t1 where v='a '; 1129id select_type table type possible_keys key key_len ref rows Extra 11301 SIMPLE t1 ref v v 13 const # Using where; Using index 1131explain select count(*) from t1 where c='a '; 1132id select_type table type possible_keys key key_len ref rows Extra 11331 SIMPLE t1 ref c c 11 const # Using where; Using index 1134explain select count(*) from t1 where t='a '; 1135id select_type table type possible_keys key key_len ref rows Extra 11361 SIMPLE t1 ref t t 13 const # Using where 1137explain select count(*) from t1 where v like 'a%'; 1138id select_type table type possible_keys key key_len ref rows Extra 11391 SIMPLE t1 range v v 13 NULL # Using where; Using index 1140explain select count(*) from t1 where v between 'a' and 'a '; 1141id select_type table type possible_keys key key_len ref rows Extra 11421 SIMPLE t1 ref v v 13 const # Using where; Using index 1143explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1144id select_type table type possible_keys key key_len ref rows Extra 11451 SIMPLE t1 ref v v 13 const # Using where; Using index 1146alter table t1 add unique(v); 1147ERROR 23000: Duplicate entry '{ ' for key 'v_2' 1148show warnings; 1149Level Code Message 1150Error 1062 Duplicate entry 'a' for key 'v_2' 1151alter table t1 add key(v); 1152Warnings: 1153Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release 1154select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 1155qq 1156*a*a*a* 1157*a *a*a * 1158*a *a*a * 1159*a *a*a * 1160*a *a*a * 1161*a *a*a * 1162*a *a*a * 1163*a *a*a * 1164*a *a*a * 1165*a *a*a * 1166explain select * from t1 where v='a'; 1167id select_type table type possible_keys key key_len ref rows Extra 11681 SIMPLE t1 ref v,v_2 # 13 const # # 1169select v,count(*) from t1 group by v limit 10; 1170v count(*) 1171a 1 1172a 10 1173b 10 1174c 10 1175d 10 1176e 10 1177f 10 1178g 10 1179h 10 1180i 10 1181select v,count(t) from t1 group by v limit 10; 1182v count(t) 1183a 1 1184a 10 1185b 10 1186c 10 1187d 10 1188e 10 1189f 10 1190g 10 1191h 10 1192i 10 1193select v,count(c) from t1 group by v limit 10; 1194v count(c) 1195a 1 1196a 10 1197b 10 1198c 10 1199d 10 1200e 10 1201f 10 1202g 10 1203h 10 1204i 10 1205select sql_big_result v,count(t) from t1 group by v limit 10; 1206v count(t) 1207a 1 1208a 10 1209b 10 1210c 10 1211d 10 1212e 10 1213f 10 1214g 10 1215h 10 1216i 10 1217select sql_big_result v,count(c) from t1 group by v limit 10; 1218v count(c) 1219a 1 1220a 10 1221b 10 1222c 10 1223d 10 1224e 10 1225f 10 1226g 10 1227h 10 1228i 10 1229select c,count(*) from t1 group by c limit 10; 1230c count(*) 1231a 1 1232a 10 1233b 10 1234c 10 1235d 10 1236e 10 1237f 10 1238g 10 1239h 10 1240i 10 1241select c,count(t) from t1 group by c limit 10; 1242c count(t) 1243a 1 1244a 10 1245b 10 1246c 10 1247d 10 1248e 10 1249f 10 1250g 10 1251h 10 1252i 10 1253select sql_big_result c,count(t) from t1 group by c limit 10; 1254c count(t) 1255a 1 1256a 10 1257b 10 1258c 10 1259d 10 1260e 10 1261f 10 1262g 10 1263h 10 1264i 10 1265select t,count(*) from t1 group by t limit 10; 1266t count(*) 1267a 1 1268a 10 1269b 10 1270c 10 1271d 10 1272e 10 1273f 10 1274g 10 1275h 10 1276i 10 1277select t,count(t) from t1 group by t limit 10; 1278t count(t) 1279a 1 1280a 10 1281b 10 1282c 10 1283d 10 1284e 10 1285f 10 1286g 10 1287h 10 1288i 10 1289select sql_big_result t,count(t) from t1 group by t limit 10; 1290t count(t) 1291a 1 1292a 10 1293b 10 1294c 10 1295d 10 1296e 10 1297f 10 1298g 10 1299h 10 1300i 10 1301alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 1302show create table t1; 1303Table Create Table 1304t1 CREATE TABLE `t1` ( 1305 `v` varchar(300) DEFAULT NULL, 1306 `c` char(10) DEFAULT NULL, 1307 `t` text DEFAULT NULL, 1308 KEY `c` (`c`), 1309 KEY `t` (`t`(10)), 1310 KEY `v` (`v`) 1311) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1312select count(*) from t1 where v='a'; 1313count(*) 131410 1315select count(*) from t1 where v='a '; 1316count(*) 131710 1318select count(*) from t1 where v between 'a' and 'a '; 1319count(*) 132010 1321select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1322count(*) 132310 1324select count(*) from t1 where v like 'a%'; 1325count(*) 132611 1327select count(*) from t1 where v like 'a %'; 1328count(*) 13299 1330explain select count(*) from t1 where v='a '; 1331id select_type table type possible_keys key key_len ref rows Extra 13321 SIMPLE t1 ref v v 303 const # Using where; Using index 1333explain select count(*) from t1 where v like 'a%'; 1334id select_type table type possible_keys key key_len ref rows Extra 13351 SIMPLE t1 range v v 303 NULL # Using where; Using index 1336explain select count(*) from t1 where v between 'a' and 'a '; 1337id select_type table type possible_keys key key_len ref rows Extra 13381 SIMPLE t1 ref v v 303 const # Using where; Using index 1339explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1340id select_type table type possible_keys key key_len ref rows Extra 13411 SIMPLE t1 ref v v 303 const # Using where; Using index 1342explain select * from t1 where v='a'; 1343id select_type table type possible_keys key key_len ref rows Extra 13441 SIMPLE t1 ref v v 303 const # # 1345select v,count(*) from t1 group by v limit 10; 1346v count(*) 1347a 1 1348a 10 1349b 10 1350c 10 1351d 10 1352e 10 1353f 10 1354g 10 1355h 10 1356i 10 1357select v,count(t) from t1 group by v limit 10; 1358v count(t) 1359a 1 1360a 10 1361b 10 1362c 10 1363d 10 1364e 10 1365f 10 1366g 10 1367h 10 1368i 10 1369select sql_big_result v,count(t) from t1 group by v limit 10; 1370v count(t) 1371a 1 1372a 10 1373b 10 1374c 10 1375d 10 1376e 10 1377f 10 1378g 10 1379h 10 1380i 10 1381alter table t1 drop key v, add key v (v(30)); 1382show create table t1; 1383Table Create Table 1384t1 CREATE TABLE `t1` ( 1385 `v` varchar(300) DEFAULT NULL, 1386 `c` char(10) DEFAULT NULL, 1387 `t` text DEFAULT NULL, 1388 KEY `c` (`c`), 1389 KEY `t` (`t`(10)), 1390 KEY `v` (`v`(30)) 1391) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1392select count(*) from t1 where v='a'; 1393count(*) 139410 1395select count(*) from t1 where v='a '; 1396count(*) 139710 1398select count(*) from t1 where v between 'a' and 'a '; 1399count(*) 140010 1401select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1402count(*) 140310 1404select count(*) from t1 where v like 'a%'; 1405count(*) 140611 1407select count(*) from t1 where v like 'a %'; 1408count(*) 14099 1410explain select count(*) from t1 where v='a '; 1411id select_type table type possible_keys key key_len ref rows Extra 14121 SIMPLE t1 ref v v 33 const # Using where 1413explain select count(*) from t1 where v like 'a%'; 1414id select_type table type possible_keys key key_len ref rows Extra 14151 SIMPLE t1 range v v 33 NULL # Using where 1416explain select count(*) from t1 where v between 'a' and 'a '; 1417id select_type table type possible_keys key key_len ref rows Extra 14181 SIMPLE t1 ref v v 33 const # Using where 1419explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 1420id select_type table type possible_keys key key_len ref rows Extra 14211 SIMPLE t1 ref v v 33 const # Using where 1422explain select * from t1 where v='a'; 1423id select_type table type possible_keys key key_len ref rows Extra 14241 SIMPLE t1 ref v v 33 const # # 1425select v,count(*) from t1 group by v limit 10; 1426v count(*) 1427a 1 1428a 10 1429b 10 1430c 10 1431d 10 1432e 10 1433f 10 1434g 10 1435h 10 1436i 10 1437select v,count(t) from t1 group by v limit 10; 1438v count(t) 1439a 1 1440a 10 1441b 10 1442c 10 1443d 10 1444e 10 1445f 10 1446g 10 1447h 10 1448i 10 1449select sql_big_result v,count(t) from t1 group by v limit 10; 1450v count(t) 1451a 1 1452a 10 1453b 10 1454c 10 1455d 10 1456e 10 1457f 10 1458g 10 1459h 10 1460i 10 1461alter table t1 modify v varchar(600), drop key v, add key v (v); 1462show create table t1; 1463Table Create Table 1464t1 CREATE TABLE `t1` ( 1465 `v` varchar(600) DEFAULT NULL, 1466 `c` char(10) DEFAULT NULL, 1467 `t` text DEFAULT NULL, 1468 KEY `c` (`c`), 1469 KEY `t` (`t`(10)), 1470 KEY `v` (`v`) 1471) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1472select v,count(*) from t1 group by v limit 10; 1473v count(*) 1474a 1 1475a 10 1476b 10 1477c 10 1478d 10 1479e 10 1480f 10 1481g 10 1482h 10 1483i 10 1484select v,count(t) from t1 group by v limit 10; 1485v count(t) 1486a 1 1487a 10 1488b 10 1489c 10 1490d 10 1491e 10 1492f 10 1493g 10 1494h 10 1495i 10 1496select sql_big_result v,count(t) from t1 group by v limit 10; 1497v count(t) 1498a 1 1499a 10 1500b 10 1501c 10 1502d 10 1503e 10 1504f 10 1505g 10 1506h 10 1507i 10 1508drop table t1; 1509create table t1 (a char(10), unique (a)); 1510insert into t1 values ('a '); 1511insert into t1 values ('a '); 1512ERROR 23000: Duplicate entry 'a' for key 'a' 1513alter table t1 modify a varchar(10); 1514insert into t1 values ('a '),('a '),('a '),('a '); 1515ERROR 23000: Duplicate entry 'a ' for key 'a' 1516insert into t1 values ('a '); 1517ERROR 23000: Duplicate entry 'a ' for key 'a' 1518insert into t1 values ('a '); 1519ERROR 23000: Duplicate entry 'a ' for key 'a' 1520insert into t1 values ('a '); 1521ERROR 23000: Duplicate entry 'a ' for key 'a' 1522update t1 set a='a ' where a like 'a%'; 1523select concat(a,'.') from t1; 1524concat(a,'.') 1525a . 1526update t1 set a='abc ' where a like 'a '; 1527select concat(a,'.') from t1; 1528concat(a,'.') 1529a . 1530update t1 set a='a ' where a like 'a %'; 1531select concat(a,'.') from t1; 1532concat(a,'.') 1533a . 1534update t1 set a='a ' where a like 'a '; 1535select concat(a,'.') from t1; 1536concat(a,'.') 1537a . 1538drop table t1; 1539create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 1540show create table t1; 1541Table Create Table 1542t1 CREATE TABLE `t1` ( 1543 `v` varchar(10) DEFAULT NULL, 1544 `c` char(10) DEFAULT NULL, 1545 `t` text DEFAULT NULL, 1546 KEY `v` (`v`(5)), 1547 KEY `c` (`c`(5)), 1548 KEY `t` (`t`(5)) 1549) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1550drop table t1; 1551create table t1 (v char(10) character set utf8); 1552show create table t1; 1553Table Create Table 1554t1 CREATE TABLE `t1` ( 1555 `v` char(10) CHARACTER SET utf8 DEFAULT NULL 1556) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1557drop table t1; 1558create table t1 (v varchar(10), c char(10)) row_format=fixed; 1559show create table t1; 1560Table Create Table 1561t1 CREATE TABLE `t1` ( 1562 `v` varchar(10) DEFAULT NULL, 1563 `c` char(10) DEFAULT NULL 1564) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=FIXED 1565insert into t1 values('a','a'),('a ','a '); 1566select concat('*',v,'*',c,'*') from t1; 1567concat('*',v,'*',c,'*') 1568*a*a* 1569*a *a* 1570drop table t1; 1571create table t1 (v varchar(65530), key(v(10))); 1572insert into t1 values(repeat('a',65530)); 1573select length(v) from t1 where v=repeat('a',65530); 1574length(v) 157565530 1576drop table t1; 1577create table t1(a int, b varchar(12), key ba(b, a)); 1578insert into t1 values (1, 'A'), (20, NULL); 1579explain select * from t1 where a=20 and b is null; 1580id select_type table type possible_keys key key_len ref rows Extra 15811 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index 1582select * from t1 where a=20 and b is null; 1583a b 158420 NULL 1585drop table t1; 1586create table t1 (v varchar(65530), key(v)); 1587Warnings: 1588Note 1071 Specified key was too long; max key length is 1000 bytes 1589drop table if exists t1; 1590set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1591create table t1 (v varchar(65536)); 1592Warnings: 1593Note 1246 Converting column 'v' from VARCHAR to TEXT 1594show create table t1; 1595Table Create Table 1596t1 CREATE TABLE `t1` ( 1597 `v` mediumtext DEFAULT NULL 1598) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1599drop table t1; 1600set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1601create table t1 (v varchar(65530) character set utf8); 1602Warnings: 1603Note 1246 Converting column 'v' from VARCHAR to TEXT 1604show create table t1; 1605Table Create Table 1606t1 CREATE TABLE `t1` ( 1607 `v` mediumtext CHARACTER SET utf8 DEFAULT NULL 1608) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1609drop table t1; 1610set statement sql_mode='' for 1611create table t1 (v varchar(65535)); 1612Warnings: 1613Note 1246 Converting column 'v' from VARCHAR to TEXT 1614show create table t1; 1615Table Create Table 1616t1 CREATE TABLE `t1` ( 1617 `v` text DEFAULT NULL 1618) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1619drop table t1; 1620set @save_concurrent_insert=@@concurrent_insert; 1621set global concurrent_insert=1; 1622create table t1 (a int) ROW_FORMAT=FIXED; 1623insert into t1 values (1),(2),(3),(4),(5); 1624lock table t1 read local; 1625connect con1,localhost,root,,; 1626connection con1; 1627insert into t1 values(6),(7); 1628connection default; 1629unlock tables; 1630delete from t1 where a>=3 and a<=4; 1631lock table t1 read local; 1632connection con1; 1633set global concurrent_insert=2; 1634insert into t1 values (8),(9); 1635connection default; 1636unlock tables; 1637insert into t1 values (10),(11),(12); 1638select * from t1; 1639a 16401 16412 164211 164310 16445 16456 16467 16478 16489 164912 1650check table t1; 1651Table Op Msg_type Msg_text 1652test.t1 check status OK 1653drop table t1; 1654disconnect con1; 1655create table t1 (a int, b varchar(30) default "hello") ROW_FORMAT=DYNAMIC; 1656insert into t1 (a) values (1),(2),(3),(4),(5); 1657lock table t1 read local; 1658connect con1,localhost,root,,; 1659connection con1; 1660insert into t1 (a) values(6),(7); 1661connection default; 1662unlock tables; 1663delete from t1 where a>=3 and a<=4; 1664lock table t1 read local; 1665connection con1; 1666set global concurrent_insert=2; 1667insert into t1 (a) values (8),(9); 1668connection default; 1669unlock tables; 1670insert into t1 (a) values (10),(11),(12); 1671select a from t1; 1672a 16731 16742 167511 167610 16775 16786 16797 16808 16819 168212 1683check table t1; 1684Table Op Msg_type Msg_text 1685test.t1 check status OK 1686drop table t1; 1687disconnect con1; 1688set global concurrent_insert=@save_concurrent_insert; 1689create table t1 (a int, key(a)); 1690insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL); 1691analyze table t1; 1692Table Op Msg_type Msg_text 1693test.t1 analyze status OK 1694analyze table t1; 1695Table Op Msg_type Msg_text 1696test.t1 analyze status Table is already up to date 1697show keys from t1; 1698Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1699t1 1 a 1 a A 8 NULL NULL YES BTREE 1700alter table t1 disable keys; 1701alter table t1 enable keys; 1702show keys from t1; 1703Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 1704t1 1 a 1 a A 8 NULL NULL YES BTREE 1705drop table t1; 1706connect session1,localhost,root,,; 1707connect session2,localhost,root,,; 1708connection session1; 1709show create table t1; 1710connection session2; 1711show create table t1; 1712connection default; 1713create table t1 (a int) select 42 a; 1714connection session1; 1715select * from t1; 1716a 17179 1718disconnect session1; 1719connection session2; 1720select * from t1; 1721a 172299 1723disconnect session2; 1724connection default; 1725select * from t1; 1726a 172742 1728drop table t1; 1729End of 4.1 tests 1730create table t1 (c1 int) pack_keys=0; 1731create table t2 (c1 int) pack_keys=1; 1732create table t3 (c1 int) pack_keys=default; 1733create table t4 (c1 int) pack_keys=2; 1734ERROR 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 1735drop table t1, t2, t3; 1736CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)); 1737INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); 1738SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1739a 17401 1741ALTER TABLE t1 DISABLE KEYS; 1742SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1743a 17441 1745SELECT a FROM t1 USE INDEX (inx) WHERE a=1; 1746a 17471 1748SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; 1749b 17501 1751SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; 1752b 17531 1754SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; 1755a 17561 1757ALTER TABLE t1 ENABLE KEYS; 1758SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; 1759a 17601 1761DROP TABLE t1; 1762CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)); 1763SHOW TABLE STATUS LIKE 't1'; 1764Name 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 1765t1 Aria 10 Page 0 # # # 8192 # # # # # # # # N 1766INSERT INTO t1 VALUES (1,1); 1767SHOW TABLE STATUS LIKE 't1'; 1768Name 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 1769t1 Aria 10 Page 1 # # # 24576 # # # # # # # # N 1770ALTER TABLE t1 DISABLE KEYS; 1771SHOW TABLE STATUS LIKE 't1'; 1772Name 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 1773t1 Aria 10 Page 1 # # # 24576 # # # # # # # # N 1774ALTER TABLE t1 ENABLE KEYS; 1775SHOW TABLE STATUS LIKE 't1'; 1776Name 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 1777t1 Aria 10 Page 1 # # # 24576 # # # # # # # # N 1778ALTER TABLE t1 DISABLE KEYS; 1779SHOW TABLE STATUS LIKE 't1'; 1780Name 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 1781t1 Aria 10 Page 1 # # # 24576 # # # # # # # # N 1782ALTER TABLE t1 ENABLE KEYS; 1783SHOW TABLE STATUS LIKE 't1'; 1784Name 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 1785t1 Aria 10 Page 1 # # # 24576 # # # # # # # # N 1786# Enable keys with parallel repair 1787SET @@aria_repair_threads=2; 1788ALTER TABLE t1 DISABLE KEYS; 1789ALTER TABLE t1 ENABLE KEYS; 1790SET @@aria_repair_threads=1; 1791CHECK TABLE t1 EXTENDED; 1792Table Op Msg_type Msg_text 1793test.t1 check status OK 1794DROP TABLE t1; 1795CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)); 1796CREATE TABLE t2 LIKE t1; 1797INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); 1798INSERT INTO t1 SELECT * FROM t2; 1799SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; 1800id ref ref 18014 4 5 1802SELECT * FROM t1; 1803id ref 18041 3 18052 1 18063 2 18074 5 18084 4 1809DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; 1810SELECT * FROM t1; 1811id ref 18121 3 18132 1 18143 2 18154 5 1816DROP TABLE t1, t2; 1817CREATE TABLE t1 (a INT) ENGINE=ARIA CHECKSUM=1 ROW_FORMAT=DYNAMIC; 1818INSERT INTO t1 VALUES (0); 1819UPDATE t1 SET a=1; 1820SELECT a FROM t1; 1821a 18221 1823CHECK TABLE t1; 1824Table Op Msg_type Msg_text 1825test.t1 check status OK 1826INSERT INTO t1 VALUES (0), (5), (4), (2); 1827UPDATE t1 SET a=2; 1828SELECT a FROM t1; 1829a 18302 18312 18322 18332 18342 1835CHECK TABLE t1; 1836Table Op Msg_type Msg_text 1837test.t1 check status OK 1838DROP TABLE t1; 1839End of 5.0 tests 1840create table t1 (a int not null, key `a` (a) key_block_size=1024); 1841show create table t1; 1842Table Create Table 1843t1 CREATE TABLE `t1` ( 1844 `a` int(11) NOT NULL, 1845 KEY `a` (`a`) KEY_BLOCK_SIZE=8192 1846) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1847drop table t1; 1848create table t1 (a int not null, key `a` (a) key_block_size=2048); 1849show create table t1; 1850Table Create Table 1851t1 CREATE TABLE `t1` ( 1852 `a` int(11) NOT NULL, 1853 KEY `a` (`a`) KEY_BLOCK_SIZE=8192 1854) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1855drop table t1; 1856create table t1 (a varchar(2048), key `a` (a)); 1857Warnings: 1858Note 1071 Specified key was too long; max key length is 1000 bytes 1859show create table t1; 1860Table Create Table 1861t1 CREATE TABLE `t1` ( 1862 `a` varchar(2048) DEFAULT NULL, 1863 KEY `a` (`a`(1000)) 1864) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1865drop table t1; 1866create table t1 (a varchar(2048), key `a` (a) key_block_size=1024); 1867Warnings: 1868Note 1071 Specified key was too long; max key length is 1000 bytes 1869show create table t1; 1870Table Create Table 1871t1 CREATE TABLE `t1` ( 1872 `a` varchar(2048) DEFAULT NULL, 1873 KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=8192 1874) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1875drop table t1; 1876create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024; 1877Warnings: 1878Note 1071 Specified key was too long; max key length is 1000 bytes 1879show create table t1; 1880Table Create Table 1881t1 CREATE TABLE `t1` ( 1882 `a` int(11) NOT NULL, 1883 `b` varchar(2048) DEFAULT NULL, 1884 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1885 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192 1886) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=1024 1887alter table t1 key_block_size=2048; 1888show create table t1; 1889Table Create Table 1890t1 CREATE TABLE `t1` ( 1891 `a` int(11) NOT NULL, 1892 `b` varchar(2048) DEFAULT NULL, 1893 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1894 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192 1895) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=2048 1896alter table t1 add c int, add key (c); 1897show create table t1; 1898Table Create Table 1899t1 CREATE TABLE `t1` ( 1900 `a` int(11) NOT NULL, 1901 `b` varchar(2048) DEFAULT NULL, 1902 `c` int(11) DEFAULT NULL, 1903 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1904 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192, 1905 KEY `c` (`c`) KEY_BLOCK_SIZE=8192 1906) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=2048 1907alter table t1 key_block_size=4096; 1908show create table t1; 1909Table Create Table 1910t1 CREATE TABLE `t1` ( 1911 `a` int(11) NOT NULL, 1912 `b` varchar(2048) DEFAULT NULL, 1913 `c` int(11) DEFAULT NULL, 1914 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1915 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192, 1916 KEY `c` (`c`) KEY_BLOCK_SIZE=8192 1917) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=4096 1918alter table t1 key_block_size=0; 1919show create table t1; 1920Table Create Table 1921t1 CREATE TABLE `t1` ( 1922 `a` int(11) NOT NULL, 1923 `b` varchar(2048) DEFAULT NULL, 1924 `c` int(11) DEFAULT NULL, 1925 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1926 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192, 1927 KEY `c` (`c`) KEY_BLOCK_SIZE=8192 1928) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1929alter table t1 add d int, add key (d); 1930show create table t1; 1931Table Create Table 1932t1 CREATE TABLE `t1` ( 1933 `a` int(11) NOT NULL, 1934 `b` varchar(2048) DEFAULT NULL, 1935 `c` int(11) DEFAULT NULL, 1936 `d` int(11) DEFAULT NULL, 1937 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1938 KEY `b` (`b`(1000)) KEY_BLOCK_SIZE=8192, 1939 KEY `c` (`c`) KEY_BLOCK_SIZE=8192, 1940 KEY `d` (`d`) 1941) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1942drop table t1; 1943create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192; 1944Warnings: 1945Note 1071 Specified key was too long; max key length is 1000 bytes 1946show create table t1; 1947Table Create Table 1948t1 CREATE TABLE `t1` ( 1949 `a` int(11) NOT NULL, 1950 `b` varchar(2048) DEFAULT NULL, 1951 KEY `a` (`a`), 1952 KEY `b` (`b`(1000)) 1953) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=8192 1954drop table t1; 1955create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192; 1956Warnings: 1957Note 1071 Specified key was too long; max key length is 1000 bytes 1958show create table t1; 1959Table Create Table 1960t1 CREATE TABLE `t1` ( 1961 `a` int(11) NOT NULL, 1962 `b` varchar(2048) DEFAULT NULL, 1963 KEY `a` (`a`), 1964 KEY `b` (`b`(1000)) 1965) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=8192 1966drop table t1; 1967create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384; 1968show create table t1; 1969Table Create Table 1970t1 CREATE TABLE `t1` ( 1971 `a` int(11) NOT NULL, 1972 `b` int(11) DEFAULT NULL, 1973 KEY `a` (`a`) KEY_BLOCK_SIZE=8192, 1974 KEY `b` (`b`) KEY_BLOCK_SIZE=8192 1975) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=16384 1976drop table t1; 1977create table t1 (a int not null, key `a` (a) key_block_size=512); 1978show create table t1; 1979Table Create Table 1980t1 CREATE TABLE `t1` ( 1981 `a` int(11) NOT NULL, 1982 KEY `a` (`a`) KEY_BLOCK_SIZE=8192 1983) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1984drop table t1; 1985create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000); 1986Warnings: 1987Note 1071 Specified key was too long; max key length is 1000 bytes 1988show create table t1; 1989Table Create Table 1990t1 CREATE TABLE `t1` ( 1991 `a` varchar(2048) DEFAULT NULL, 1992 KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=8192 1993) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 1994drop table t1; 1995create table t1 (a int not null, key `a` (a) key_block_size=1025); 1996show create table t1; 1997Table Create Table 1998t1 CREATE TABLE `t1` ( 1999 `a` int(11) NOT NULL, 2000 KEY `a` (`a`) KEY_BLOCK_SIZE=8192 2001) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 2002drop table t1; 2003create table t1 (a int not null, key key_block_size=1024 (a)); 2004ERROR 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 2005create table t1 (a int not null, key `a` key_block_size=1024 (a)); 2006ERROR 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 2007CREATE TABLE t1 ( 2008c1 INT, 2009c2 VARCHAR(300), 2010KEY (c1) KEY_BLOCK_SIZE 1024, 2011KEY (c2) KEY_BLOCK_SIZE 8192 2012); 2013INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))), 2014(11, REPEAT('b', CEIL(RAND() * 300))), 2015(12, REPEAT('c', CEIL(RAND() * 300))), 2016(13, REPEAT('d', CEIL(RAND() * 300))), 2017(14, REPEAT('e', CEIL(RAND() * 300))), 2018(15, REPEAT('f', CEIL(RAND() * 300))), 2019(16, REPEAT('g', CEIL(RAND() * 300))), 2020(17, REPEAT('h', CEIL(RAND() * 300))), 2021(18, REPEAT('i', CEIL(RAND() * 300))), 2022(19, REPEAT('j', CEIL(RAND() * 300))), 2023(20, REPEAT('k', CEIL(RAND() * 300))), 2024(21, REPEAT('l', CEIL(RAND() * 300))), 2025(22, REPEAT('m', CEIL(RAND() * 300))), 2026(23, REPEAT('n', CEIL(RAND() * 300))), 2027(24, REPEAT('o', CEIL(RAND() * 300))), 2028(25, REPEAT('p', CEIL(RAND() * 300))), 2029(26, REPEAT('q', CEIL(RAND() * 300))), 2030(27, REPEAT('r', CEIL(RAND() * 300))), 2031(28, REPEAT('s', CEIL(RAND() * 300))), 2032(29, REPEAT('t', CEIL(RAND() * 300))), 2033(30, REPEAT('u', CEIL(RAND() * 300))), 2034(31, REPEAT('v', CEIL(RAND() * 300))), 2035(32, REPEAT('w', CEIL(RAND() * 300))), 2036(33, REPEAT('x', CEIL(RAND() * 300))), 2037(34, REPEAT('y', CEIL(RAND() * 300))), 2038(35, REPEAT('z', CEIL(RAND() * 300))); 2039INSERT INTO t1 SELECT * FROM t1; 2040INSERT INTO t1 SELECT * FROM t1; 2041CHECK TABLE t1; 2042Table Op Msg_type Msg_text 2043test.t1 check status OK 2044REPAIR TABLE t1; 2045Table Op Msg_type Msg_text 2046test.t1 repair status OK 2047DELETE FROM t1 WHERE c1 >= 10; 2048CHECK TABLE t1; 2049Table Op Msg_type Msg_text 2050test.t1 check status OK 2051DROP TABLE t1; 2052CREATE TABLE t1 ( 2053c1 CHAR(130), 2054c2 VARCHAR(1) 2055) ENGINE=aria; 2056INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2057SELECT COUNT(*) FROM t1; 2058COUNT(*) 20591 2060CHECK TABLE t1; 2061Table Op Msg_type Msg_text 2062test.t1 check status OK 2063REPAIR TABLE t1; 2064Table Op Msg_type Msg_text 2065test.t1 repair status OK 2066SELECT COUNT(*) FROM t1; 2067COUNT(*) 20681 2069CHECK TABLE t1; 2070Table Op Msg_type Msg_text 2071test.t1 check status OK 2072DROP TABLE t1; 2073CREATE TABLE t1 ( 2074c1 CHAR(130), 2075c2 VARCHAR(1) 2076) ENGINE=aria; 2077INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2078SELECT COUNT(*) FROM t1; 2079COUNT(*) 20801 2081CHECK TABLE t1 EXTENDED; 2082Table Op Msg_type Msg_text 2083test.t1 check status OK 2084REPAIR TABLE t1 EXTENDED; 2085Table Op Msg_type Msg_text 2086test.t1 repair status OK 2087SELECT COUNT(*) FROM t1; 2088COUNT(*) 20891 2090CHECK TABLE t1 EXTENDED; 2091Table Op Msg_type Msg_text 2092test.t1 check status OK 2093DROP TABLE t1; 2094CREATE TABLE t1 ( 2095c1 CHAR(130), 2096c2 VARCHAR(1) 2097) ENGINE=aria; 2098INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); 2099INSERT INTO t1 VALUES('b', 'b'); 2100INSERT INTO t1 VALUES('c', 'b'); 2101DELETE FROM t1 WHERE c1='b'; 2102SELECT COUNT(*) FROM t1; 2103COUNT(*) 21042 2105OPTIMIZE TABLE t1; 2106Table Op Msg_type Msg_text 2107test.t1 optimize status OK 2108SELECT COUNT(*) FROM t1; 2109COUNT(*) 21102 2111DROP TABLE t1; 2112CREATE TABLE t1 ( 2113c1 CHAR(130), 2114c2 VARCHAR(1), 2115KEY (c1) 2116) ENGINE=aria; 2117# Insert 100 rows. Query log disabled. 2118UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; 2119SELECT COUNT(*) FROM t1; 2120COUNT(*) 2121100 2122ALTER TABLE t1 ENGINE=aria; 2123SELECT COUNT(*) FROM t1; 2124COUNT(*) 2125100 2126CHECK TABLE t1; 2127Table Op Msg_type Msg_text 2128test.t1 check status OK 2129CHECK TABLE t1 EXTENDED; 2130Table Op Msg_type Msg_text 2131test.t1 check status OK 2132DROP TABLE t1; 2133CREATE TABLE t1 ( 2134c1 CHAR(50), 2135c2 VARCHAR(1) 2136) ENGINE=aria DEFAULT CHARSET UTF8; 2137INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2138SELECT COUNT(*) FROM t1; 2139COUNT(*) 21401 2141CHECK TABLE t1; 2142Table Op Msg_type Msg_text 2143test.t1 check status OK 2144REPAIR TABLE t1; 2145Table Op Msg_type Msg_text 2146test.t1 repair status OK 2147SELECT COUNT(*) FROM t1; 2148COUNT(*) 21491 2150CHECK TABLE t1; 2151Table Op Msg_type Msg_text 2152test.t1 check status OK 2153DROP TABLE t1; 2154CREATE TABLE t1 ( 2155c1 CHAR(50), 2156c2 VARCHAR(1) 2157) ENGINE=aria DEFAULT CHARSET UTF8; 2158INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2159SELECT COUNT(*) FROM t1; 2160COUNT(*) 21611 2162CHECK TABLE t1 EXTENDED; 2163Table Op Msg_type Msg_text 2164test.t1 check status OK 2165REPAIR TABLE t1 EXTENDED; 2166Table Op Msg_type Msg_text 2167test.t1 repair status OK 2168SELECT COUNT(*) FROM t1; 2169COUNT(*) 21701 2171CHECK TABLE t1 EXTENDED; 2172Table Op Msg_type Msg_text 2173test.t1 check status OK 2174DROP TABLE t1; 2175CREATE TABLE t1 ( 2176c1 CHAR(50), 2177c2 VARCHAR(1) 2178) ENGINE=aria DEFAULT CHARSET UTF8; 2179INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); 2180INSERT INTO t1 VALUES('b', 'b'); 2181INSERT INTO t1 VALUES('c', 'b'); 2182DELETE FROM t1 WHERE c1='b'; 2183SELECT COUNT(*) FROM t1; 2184COUNT(*) 21852 2186OPTIMIZE TABLE t1; 2187Table Op Msg_type Msg_text 2188test.t1 optimize status OK 2189SELECT COUNT(*) FROM t1; 2190COUNT(*) 21912 2192DROP TABLE t1; 2193CREATE TABLE t1 ( 2194c1 CHAR(50), 2195c2 VARCHAR(1), 2196KEY (c1) 2197) ENGINE=aria DEFAULT CHARSET UTF8; 2198# Insert 100 rows. Query log disabled. 2199UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; 2200SELECT COUNT(*) FROM t1; 2201COUNT(*) 2202100 2203ALTER TABLE t1 ENGINE=aria; 2204SELECT COUNT(*) FROM t1; 2205COUNT(*) 2206100 2207CHECK TABLE t1; 2208Table Op Msg_type Msg_text 2209test.t1 check status OK 2210CHECK TABLE t1 EXTENDED; 2211Table Op Msg_type Msg_text 2212test.t1 check status OK 2213DROP TABLE t1; 2214CREATE TABLE t1 ( 2215c1 VARCHAR(10) NOT NULL, 2216c2 CHAR(10) DEFAULT NULL, 2217c3 VARCHAR(10) NOT NULL, 2218KEY (c1), 2219KEY (c2) 2220) ENGINE=aria DEFAULT CHARSET=utf8 PACK_KEYS=0; 2221Aria file: MYSQLD_DATADIR/test/t1 2222Record format: Block 2223Crashsafe: yes 2224Character set: utf8_general_ci (33) 2225Data records: 0 Deleted blocks: 0 2226Block_size: 8192 2227Recordlength: 99 2228 2229Table description: 2230Key Start Len Index Type 22311 2 30 multip. varchar 22322 33 30 multip. char NULL 2233DROP TABLE t1; 2234create table t1 (n int not null, c char(1)) transactional=1; 2235show create table t1; 2236Table Create Table 2237t1 CREATE TABLE `t1` ( 2238 `n` int(11) NOT NULL, 2239 `c` char(1) DEFAULT NULL 2240) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 TRANSACTIONAL=1 2241drop table t1; 2242CREATE TABLE t1 (line LINESTRING NOT NULL) engine=aria; 2243INSERT INTO t1 VALUES (GeomFromText("LINESTRING(0 0)")); 2244checksum table t1; 2245Table Checksum 2246test.t1 310616673 2247CREATE TABLE t2 (line LINESTRING NOT NULL) engine=aria; 2248INSERT INTO t2 VALUES (GeomFromText("LINESTRING(0 0)")); 2249checksum table t2; 2250Table Checksum 2251test.t2 310616673 2252CREATE TABLE t3 select * from t1; 2253checksum table t3; 2254Table Checksum 2255test.t3 310616673 2256drop table t1,t2,t3; 2257End of 5.1 tests 2258create table t2(a varchar(255),key(a))engine=aria row_format=dynamic transactional=0; 2259insert into t2 values (repeat('o',124)), (repeat('h',226)), (repeat('i',236)), 2260(repeat('l',234)), (repeat('b',13)), (repeat('g',236)), (repeat('y',205)), 2261(repeat('c',99)), (repeat('g',145)), (repeat('o',131)), (repeat('e',63)), 2262(repeat('q',155)), (repeat('k',87)), (repeat('i',54)), (repeat('p',84)), 2263(repeat('m',119)), (repeat('c',2)), (repeat('a',174)), (repeat('g',160)), 2264(repeat('t',147)), (repeat('n',107)); 2265insert into t2 values ('nupdjlafwfvuuvruxkyjxpmupihzgspkaybijztkeukgzzkrxmd'); 2266insert into t2 values (repeat('g',40)), (repeat('i',173)), (repeat('q',126)), 2267(repeat('i',217)), (repeat('f',161)), (repeat('i',28)), (repeat('a',35)), 2268(repeat('y',27)), (repeat('o',100)), (repeat('o',175)), (repeat('f',69)), 2269(repeat('k',156)), (repeat('n',220)), (repeat('q',247)), (repeat('y',180)), 2270(repeat('v',209)), (repeat('m',169)), (repeat('y',170)), (repeat('r',151)), 2271(repeat('d',38)), (repeat('g',64)), (repeat('k',77)), (repeat('l',150)), 2272(repeat('s',150)), (repeat('u',127)), (repeat('l',15)), (repeat('m',33)), 2273(repeat('r',177)), (repeat('v',197)), (repeat('k',62)), (repeat('h',219)), 2274(repeat('u',161)), (repeat('y',118)), (repeat('i',184)), (repeat('z',202)), 2275(repeat('j',113)), (repeat('q',95)), (repeat('q',164)), (repeat('e',54)), 2276(repeat('e',60)), (repeat('l',203)), (repeat('g',77)), (repeat('y',44)), 2277(repeat('j',196)), (repeat('t',45)), (repeat('l',190)), (repeat('l',89)), 2278(repeat('q',45)), (repeat('e',191)), (repeat('t',38)), (repeat('f',148)), 2279(repeat('c',25)), (repeat('v',97)), (repeat('i',83)), (repeat('s',166)), 2280(repeat('d',96)), (repeat('v',82)), (repeat('n',127)), (repeat('i',201)), 2281(repeat('x',184)), (repeat('d',76)), (repeat('u',17)), (repeat('a',178)); 2282insert into t2 values ('hwvfiavnmufgbulapzrolonwxufheqymvjncnczlzcjokzqlsvmomcjzgzwzquyxpunxdmotdczocwliaprpubwaeccsulvittgizcutxxb'); 2283insert into t2 values (repeat('x',28)), (repeat('p',21)), (repeat('k',241)), 2284(repeat('i',243)), (repeat('b',172)), (repeat('z',94)), (repeat('i',218)), 2285(repeat('a',177)), (repeat('g',251)), (repeat('q',161)), (repeat('x',231)), 2286(repeat('p',51)), (repeat('f',141)), (repeat('m',28)), (repeat('r',77)), 2287(repeat('h',56)), (repeat('k',23)), (repeat('f',198)), (repeat('o',243)), 2288(repeat('d',160)), (repeat('h',82)); 2289check table t2 extended; 2290Table Op Msg_type Msg_text 2291test.t2 check status OK 2292drop table t2; 2293CREATE TABLE t1 ( 2294col0 float DEFAULT NULL, 2295col1 date DEFAULT NULL, 2296col2 double DEFAULT NULL, 2297col3 decimal(10,0) DEFAULT NULL, 2298col4 char(218) DEFAULT NULL, 2299col5 year(4) DEFAULT NULL, 2300col6 datetime DEFAULT NULL, 2301col7 varchar(39) DEFAULT NULL, 2302col8 double DEFAULT NULL, 2303col9 decimal(10,0) DEFAULT NULL, 2304col10 enum('test1','test2','test3') DEFAULT NULL, 2305col11 year(4) DEFAULT NULL, 2306col12 tinytext, 2307col13 tinyblob, 2308col14 date DEFAULT NULL, 2309col15 smallint(6) DEFAULT NULL, 2310col16 varchar(81) DEFAULT NULL, 2311col17 tinytext, 2312col18 blob, 2313col19 double DEFAULT NULL, 2314col20 double DEFAULT NULL, 2315col21 varchar(216) DEFAULT NULL, 2316col22 enum('test1','test2','test3') DEFAULT NULL, 2317col23 decimal(10,0) DEFAULT NULL, 2318col24 text, 2319col25 varchar(118) DEFAULT NULL, 2320col26 tinytext, 2321col27 tinyblob, 2322col28 double DEFAULT NULL, 2323col29 tinyint(4) DEFAULT NULL, 2324col30 longtext, 2325col31 tinyint(1) DEFAULT NULL, 2326col32 char(212) DEFAULT NULL, 2327col33 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 2328col34 year(4) DEFAULT NULL, 2329col35 tinyint(1) DEFAULT NULL, 2330col36 enum('test1','test2','test3') DEFAULT NULL, 2331col37 decimal(10,0) DEFAULT NULL, 2332col38 tinyint(4) DEFAULT NULL, 2333col39 double DEFAULT NULL, 2334col40 decimal(10,0) DEFAULT NULL, 2335col41 enum('test1','test2','test3') DEFAULT NULL, 2336col42 longblob, 2337col43 text, 2338col44 blob, 2339col45 year(4) DEFAULT NULL, 2340col46 longtext, 2341col47 int(11) DEFAULT NULL, 2342col48 set('test1','test2','test3') DEFAULT NULL, 2343col49 bigint(20) DEFAULT NULL, 2344col50 date DEFAULT NULL, 2345col51 tinyblob, 2346col52 float DEFAULT NULL, 2347col53 year(4) DEFAULT NULL, 2348col54 decimal(10,0) DEFAULT NULL, 2349col55 tinyblob, 2350col56 float DEFAULT NULL, 2351col57 bigint(20) DEFAULT NULL, 2352col58 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2353col59 enum('test1','test2','test3') DEFAULT NULL, 2354col60 bigint(20) DEFAULT NULL, 2355col61 year(4) DEFAULT NULL, 2356col62 year(4) DEFAULT NULL, 2357col63 double DEFAULT NULL, 2358col64 tinytext, 2359col65 tinyint(4) DEFAULT NULL, 2360col66 longtext, 2361col67 time DEFAULT NULL, 2362col68 bigint(20) DEFAULT NULL, 2363col69 char(142) DEFAULT NULL, 2364col70 longtext, 2365col71 time DEFAULT NULL, 2366col72 year(4) DEFAULT NULL, 2367col73 longblob, 2368col74 enum('test1','test2','test3') DEFAULT NULL, 2369col75 decimal(10,0) DEFAULT NULL, 2370col76 smallint(6) DEFAULT NULL, 2371col77 tinytext, 2372col78 date DEFAULT NULL, 2373col79 double DEFAULT NULL, 2374col80 tinyint(4) DEFAULT NULL, 2375col81 float DEFAULT NULL, 2376col82 bigint(20) DEFAULT NULL, 2377col83 double DEFAULT NULL, 2378col84 varchar(124) DEFAULT NULL, 2379col85 double DEFAULT NULL, 2380col86 tinyblob, 2381col87 tinyblob, 2382col88 double DEFAULT NULL, 2383col89 date DEFAULT NULL, 2384col90 decimal(10,0) DEFAULT NULL, 2385col91 set('test1','test2','test3') DEFAULT NULL, 2386col92 blob, 2387col93 char(174) DEFAULT NULL, 2388col94 double DEFAULT NULL, 2389col95 tinytext, 2390col96 decimal(10,0) DEFAULT NULL, 2391col97 year(4) DEFAULT NULL, 2392col98 tinyblob, 2393col99 datetime DEFAULT NULL, 2394col100 longblob, 2395col101 date DEFAULT NULL, 2396col102 float DEFAULT NULL, 2397col103 float DEFAULT NULL, 2398col104 int(11) DEFAULT NULL, 2399col105 longblob, 2400col106 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 2401col107 float DEFAULT NULL, 2402col108 text, 2403col109 float DEFAULT NULL, 2404col110 decimal(10,0) DEFAULT NULL, 2405col111 double DEFAULT NULL, 2406col112 double DEFAULT NULL, 2407col113 blob, 2408col114 varchar(152) DEFAULT NULL, 2409col115 bigint(20) DEFAULT NULL, 2410col116 decimal(10,0) DEFAULT NULL, 2411col117 mediumint(9) DEFAULT NULL, 2412col118 tinytext, 2413col119 tinyblob, 2414col120 int(11) DEFAULT NULL, 2415col121 bigint(20) DEFAULT NULL, 2416col122 double DEFAULT NULL, 2417col123 date DEFAULT NULL, 2418col124 longtext, 2419col125 longtext, 2420col126 double DEFAULT NULL, 2421col127 varchar(84) DEFAULT NULL, 2422col128 text, 2423col129 double DEFAULT NULL, 2424col130 enum('test1','test2','test3') DEFAULT NULL, 2425col131 time DEFAULT NULL, 2426col132 year(4) DEFAULT NULL, 2427col133 blob, 2428col134 tinytext, 2429col135 double DEFAULT NULL, 2430col136 tinytext, 2431col137 bigint(20) DEFAULT NULL, 2432col138 datetime DEFAULT NULL, 2433col139 double DEFAULT NULL, 2434col140 decimal(10,0) DEFAULT NULL, 2435col141 longtext, 2436col142 tinyint(1) DEFAULT NULL, 2437col143 time DEFAULT NULL, 2438col144 time DEFAULT NULL, 2439col145 float DEFAULT NULL, 2440col146 longblob, 2441col147 float DEFAULT NULL, 2442col148 text, 2443col149 mediumint(9) DEFAULT NULL, 2444col150 tinyblob, 2445col151 tinyblob, 2446col152 tinytext, 2447col153 tinyblob, 2448col154 tinyblob, 2449col155 tinytext, 2450col156 tinyint(1) DEFAULT NULL, 2451col157 tinytext, 2452col158 time DEFAULT NULL, 2453col159 date DEFAULT NULL, 2454col160 longtext, 2455col161 enum('test1','test2','test3') DEFAULT NULL, 2456col162 text, 2457col163 decimal(10,0) DEFAULT NULL, 2458col164 time DEFAULT NULL, 2459col165 longblob, 2460col166 tinyint(4) DEFAULT NULL, 2461col167 bigint(20) DEFAULT NULL, 2462col168 decimal(10,0) DEFAULT NULL, 2463col169 smallint(6) DEFAULT NULL, 2464col170 tinytext, 2465col171 tinyint(4) DEFAULT NULL, 2466col172 tinyint(1) DEFAULT NULL, 2467col173 tinytext, 2468col174 decimal(10,0) DEFAULT NULL, 2469col175 double DEFAULT NULL 2470) engine=aria; 2471insert ignore into t1 set 2472col10=abs(28449) % 2, 2473col11='1973', 2474col12=if(abs(-30039)%100<20,null,'forgery\'s'), 2475col13=if(abs(24672)%100<20,null,'adductor\'s'), 2476col16=if(abs(26872)%100<20,null,'0xf810e016ee0b78e1ce8b1c6cf8d2e82bf8507453768a3908dc20cecfc9a0ac0ac00079d0645a4c'), 2477col18=if(abs(-15854)%100<20,null,'unattractiveness'), 2478col19=if(abs(4287439673.9896235000)%100<20,null,25288), 2479col20=if(abs(4290800136.7527390000)%100<20,null,8887), 2480col21=if(abs(-26086)%100<20,null,'0x2334181c6068aab18b348ecc1e2600b81e1c5f821eee3e204824'), 2481col22=abs(-21921) % 2, 2482col23=if(abs(1503277.6900540178)%100<20,null,-23298), 2483col25=if(abs(29674)%100<20,null,'0xc8d094f888ee20c83baef8d9380a168d40f4906e742a4dc0daeacb809e64095c71d510c7c0f83a6a0a04b8d6a0d9bea2dc3d4bd44d9c5002e440707c40ead8b3eb20a100a8524b1616a338a440ea02a25a08041810a08cac087cd47b4a79f08730946c5800600ae45e1c08f637'), 2484col26=if(abs(28642)%100<20,null,'insubstantiality\'s'), 2485col27=if(abs(-3188)%100<20,null,'exine\'s'), 2486col29=if(abs(-47)%100<20,null,-24131), 2487col32=if(abs(-3658)%100<20,null,'shortener\'s'), 2488col34='1917', 2489col36=abs(27782) % 2, 2490col37=if(abs(4864972.0244758446)%100<20,null,32302), 2491col38=if(abs(97)%100<20,null,-14079), 2492col39=if(abs(3362872.0521256141)%100<20,null,27191), 2493col40=if(abs(3348292.2110660113)%100<20,null,-1163), 2494col41=abs(-18533) % 2, 2495col42=if(abs(2094)%100<20,null,'Montparnasse'), 2496col43=if(abs(-15983)%100<20,null,'Massasoit\'s'), 2497col44=if(abs(2497)%100<20,null,'lags'), 2498col45='2057', 2499col46=if(abs(-31691)%100<20,null,'miscegenation\'s'), 2500col47=if(abs(-1269564297)%100<20,null,1089), 2501col49=if(abs(-1815717335)%100<20,null,-17504), 2502col51=if(abs(-15263)%100<20,null,'virelay'), 2503col52=if(abs(2227333.3279519030)%100<20,null,-5210), 2504col53='2032', 2505col54=if(abs(791647.5947447127)%100<20,null,32576), 2506col55=if(abs(20293)%100<20,null,'tumblebug'), 2507col56=if(abs(4288698564.2967925000)%100<20,null,17141), 2508col57=if(abs(-2138460927)%100<20,null,14495), 2509col59=abs(7624) % 2, 2510col60=if(abs(-1500892492)%100<20,null,-68), 2511col63=if(abs(4290890487.3789482000)%100<20,null,-32129), 2512col65=if(abs(22)%100<20,null,15722), 2513col79=if(abs(4292420489.2606282000)%100<20,null,-23891), 2514col84=if(abs(-21248)%100<20,null,'0x4f9888d044435050eab83cb3dcad88b01886e434e216'), 2515col85=if(abs(4294260188.6230965000)%100<20,null,16867), 2516col86=if(abs(-11659)%100<20,null,'prewar'), 2517col87=if(abs(-2253)%100<20,null,'Radnorshire'), 2518col90=if(abs(4287254529.4026613000)%100<20,null,23506), 2519col92=if(abs(6472)%100<20,null,'electroplated'), 2520col93=if(abs(-13523)%100<20,null,'sparkiest'), 2521col95=if(abs(23998)%100<20,null,'Crimea'), 2522col96=if(abs(4287719060.2789087000)%100<20,null,20527), 2523col98=if(abs(-14090)%100<20,null,'firebrat'), 2524col99='19161023095430', 2525col100=if(abs(-31178)%100<20,null,'clinical'), 2526col102=if(abs(4407547.5205542166)%100<20,null,18226), 2527col103=if(abs(4286834687.5994444000)%100<20,null,27520), 2528col104=if(abs(-2105663477)%100<20,null,28591), 2529col105=if(abs(1929)%100<20,null,'renascent'), 2530col107=if(abs(5972348.8099917602)%100<20,null,-11408), 2531col108=if(abs(-11262)%100<20,null,'aircraftmen'), 2532col110=if(abs(6530491.4546037167)%100<20,null,-17672), 2533col111=if(abs(4289897795.5000763000)%100<20,null,3742), 2534col112=if(abs(1680557.8560441907)%100<20,null,13944), 2535col113=if(abs(-27195)%100<20,null,'dekameter'), 2536col115=if(abs(-2083419827)%100<20,null,-17272), 2537col117=if(abs(1704826)%100<20,null,17880), 2538col118=if(abs(-2848)%100<20,null,'judicatory'), 2539col119=if(abs(-28087)%100<20,null,'mistitles'), 2540col120=if(abs(-2100119097)%100<20,null,22465), 2541col121=if(abs(-1868777891)%100<20,null,15172), 2542col122=if(abs(7039857.3608508557)%100<20,null,-22154), 2543col125=if(abs(70)%100<20,null,'Hong\'s'), 2544col126=if(abs(3820673.5968199712)%100<20,null,-24185), 2545col127=if(abs(12331)%100<20,null,'0x674e14584e88fca3fed0a0b1488a440008228aa01454a65cf09e3f0fa0511c3ce2f8688450'), 2546col128=if(abs(20335)%100<20,null,'Zoroaster\'s'), 2547col129=if(abs(3916577.6225165562)%100<20,null,-4088), 2548col130=abs(-15003) % 2, 2549col132='2016', 2550col134=if(abs(-26555)%100<20,null,'Caesarea'), 2551col135=if(abs(4288484655.2416148000)%100<20,null,-30073), 2552col136=if(abs(-17577)%100<20,null,'upbraid'), 2553col137=if(abs(-1742797945)%100<20,null,-21651), 2554col138='20751113181230', 2555col139=if(abs(4288997063.9889216000)%100<20,null,1816), 2556col141=if(abs(-31448)%100<20,null,'threnodist'), 2557col142=if(abs(88)%100<20,null,-19748), 2558col143='6930607', 2559col144='5760250', 2560col145=if(abs(3591496.9625843074)%100<20,null,76), 2561col146=if(abs(20875)%100<20,null,'rename'), 2562col147=if(abs(4294789439.6773582000)%100<20,null,32314), 2563col148=if(abs(7072)%100<20,null,'recesses'), 2564col150=if(abs(-26540)%100<20,null,'cuckoo'), 2565col152=if(abs(23553)%100<20,null,'shortened'), 2566col153=if(abs(-30422)%100<20,null,'inhabitant'), 2567col154=if(abs(30457)%100<20,null,'Orwellian'), 2568col155=if(abs(-30263)%100<20,null,'Ptolemies'), 2569col156=if(abs(-41)%100<20,null,6382), 2570col157=if(abs(2557)%100<20,null,'horsewhips'), 2571col158='2764427', 2572col160=if(abs(-15872)%100<20,null,'girlhood'), 2573col161=abs(15378) % 2, 2574col163=if(abs(4286662730.9309368000)%100<20,null,-19516), 2575col167=if(abs(-1881918655)%100<20,null,6927), 2576col169=if(abs(-14442)%100<20,null,-6392), 2577col170=if(abs(29965)%100<20,null,'resynthesis'), 2578col173=if(abs(-451)%100<20,null,'Clute'), 2579col174=if(abs(3262594.6284981840)%100<20,null,17846); 2580Warnings: 2581Warning 1265 Data truncated for column 'col25' at row 1 2582Warning 1264 Out of range value for column 'col29' at row 1 2583Warning 1265 Data truncated for column 'col36' at row 1 2584Warning 1264 Out of range value for column 'col38' at row 1 2585Warning 1265 Data truncated for column 'col59' at row 1 2586Warning 1264 Out of range value for column 'col65' at row 1 2587Warning 1264 Out of range value for column 'col142' at row 1 2588Warning 1264 Out of range value for column 'col156' at row 1 2589Warning 1265 Data truncated for column 'col161' at row 1 2590update ignore t1 set col165=repeat('a',7000); 2591check table t1; 2592Table Op Msg_type Msg_text 2593test.t1 check status OK 2594drop table t1; 2595create table t1 (a char(200) primary key, b int default 12345) engine=aria; 2596insert t1 (a) values (repeat('0', 200)); 2597insert t1 (a) values (repeat('1', 200)), (repeat('2', 200)), (repeat('3', 200)), 2598(repeat('4', 200)), (repeat('5', 200)), (repeat('6', 200)), (repeat('7', 200)), 2599(repeat('8', 200)), (repeat('9', 200)), (repeat('a', 200)), (repeat('b', 200)), 2600(repeat('c', 200)), (repeat('d', 200)), (repeat('e', 200)), (repeat('f', 200)), 2601(repeat('g', 200)), (repeat('h', 200)), (repeat('i', 200)), (repeat('j', 200)), 2602(repeat('k', 200)), (repeat('l', 200)), (repeat('m', 200)), (repeat('n', 200)), 2603(repeat('o', 200)), (repeat('p', 200)), (repeat('q', 200)), (repeat('r', 200)), 2604(repeat('s', 200)), (repeat('t', 200)), (repeat('u', 200)), (repeat('v', 200)), 2605(repeat('w', 200)), (repeat('x', 200)), (repeat('y', 200)), (repeat('z', 200)), 2606(repeat('+', 200)), (repeat('-', 200)), (repeat('=', 200)), (repeat('*', 200)); 2607select b from t1 where a >= repeat('f', 200) and a < 'k'; 2608b 260912345 261012345 261112345 261212345 261312345 2614drop table t1; 2615create table t1 (a int) engine=aria transactional=1; 2616insert into t1 values (1); 2617lock table t1 write concurrent; 2618delete from t1 where a>0; 2619ERROR 42000: The storage engine for the table doesn't support DELETE in WRITE CONCURRENT 2620delete from t1; 2621ERROR 42000: The storage engine for the table doesn't support TRUNCATE in WRITE CONCURRENT 2622truncate t1; 2623ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 2624unlock tables; 2625drop table t1; 2626create table t1 (p int primary key, i int, a char(10), key k1(i), key k2(a)) 2627engine aria; 2628insert into t1 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 2629(3, 1, 'yyyy'), (4, 3, 'zzzz'); 2630insert into t1 values (5, 3, 'yyyy'), (6, 3, 'yyyy'), (7, 0, NULL), 2631(8, 0, NULL); 2632select * from t1 where a='zzzz'; 2633p i a 26344 3 zzzz 2635select * from t1 where a='yyyy'; 2636p i a 26373 1 yyyy 26385 3 yyyy 26396 3 yyyy 2640select * from t1 where a is NULL; 2641p i a 26427 0 NULL 26438 0 NULL 2644select * from t1; 2645p i a 26461 1 qqqq 26472 1 pppp 26483 1 yyyy 26494 3 zzzz 26505 3 yyyy 26516 3 yyyy 26527 0 NULL 26538 0 NULL 2654check table t1; 2655Table Op Msg_type Msg_text 2656test.t1 check status OK 2657drop table t1; 2658create table t1 (f1 int unique, f2 int) engine=aria; 2659create table t2 (f3 int, f4 int) engine=aria; 2660create view v1 as select * from t1, t2 where f1= f3; 2661insert into t1 values (1,11), (2,22); 2662insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; 2663ERROR HY000: Can not modify more than one base table through a join view 'test.v1' 2664insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; 2665ERROR HY000: Can not modify more than one base table through a join view 'test.v1' 2666drop table t1,t2; 2667drop view v1; 2668CREATE TABLE t1 (id int, c varchar(10)) engine=aria; 2669INSERT INTO t1 VALUES (1,"1"); 2670ALTER TABLE t1 CHANGE c d varchar(10); 2671affected rows: 0 2672info: Records: 0 Duplicates: 0 Warnings: 0 2673drop table t1; 2674create table t1 (s1 int); 2675insert into t1 values (1); 2676alter table t1 partition by list (s1) (partition p1 values in (2)); 2677ERROR HY000: Table has no partition for value 1 2678drop table t1; 2679create table t1(a int primary key, b blob, c blob) engine=aria; 2680set sql_mode=''; 2681insert into t1 values(1,repeat('a',100), repeat('b',657860)); 2682Warnings: 2683Warning 1265 Data truncated for column 'c' at row 1 2684insert into t1 values(1,repeat('a',100), repeat('b',657860)); 2685ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 2686set sql_mode=default; 2687check table t1; 2688Table Op Msg_type Msg_text 2689test.t1 check status OK 2690drop table t1; 2691Create table t1 (a int) engine="aria"; 2692show create table t1; 2693Table Create Table 2694t1 CREATE TABLE `t1` ( 2695 `a` int(11) DEFAULT NULL 2696) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 2697drop table t1; 2698CREATE TABLE t1 ( f1 DOUBLE , f2 DOUBLE , f3 DOUBLE , f4 DOUBLE , v3 DOUBLE , v4 DOUBLE , KEY ( v3 ) , KEY ( v4 ) ) engine=maria; 2699REPLACE t1 ( f2 , f1 ) VALUES ( f2 , 56 ) ; 2700INSERT t1 ( f1 , f2 , f3 , f4 ) VALUES ( 0 , f2 , 8 , f3 ) ; 2701INSERT t1 ( f4 , f2 ) VALUES ( 4 , 92 ) ; 2702DELETE FROM t1 WHERE v3 = 173 OR v4 = 9 ; 2703drop table t1; 2704CREATE TABLE t1 ( 2705f1 CHAR(255) BINARY , 2706f2 CHAR(255) BINARY NOT NULL DEFAULT '0', 2707f3 CHAR(255) BINARY NOT NULL , 2708f4 CHAR(255) BINARY NOT NULL DEFAULT '0' , 2709v3 CHAR(255) BINARY AS ( ( f1 NOT LIKE '%' ) ) PERSISTENT, 2710KEY (v3) 2711) ENGINE=Maria; 2712INSERT INTO t1 ( f1 , f2 , f3 , f4 ) SELECT f1 , f4 , f1 , f4 FROM t1; 2713DELETE FROM t1; 2714drop table t1; 2715create table t1 (a int not null primary key, b blob) engine=maria transactional=1; 2716insert into t1 values(1,repeat('a',8000)); 2717insert into t1 values(2,repeat('b',8000)); 2718insert into t1 values(3,repeat('c',8000)); 2719flush tables; 2720delete from t1 where a>1; 2721insert into t1 values(1,repeat('d',8000*3)); 2722ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 2723flush tables; 2724check table t1; 2725Table Op Msg_type Msg_text 2726test.t1 check status OK 2727repair table t1 extended; 2728Table Op Msg_type Msg_text 2729test.t1 repair status OK 2730drop table t1; 2731create table t1 (a int, b int, key (a), key(b)); 2732alter table t1 disable keys; 2733insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); 2734alter table t1 enable keys; 2735select count(*) from t1; 2736count(*) 273713 2738drop table t1; 2739create table t1 (a int not null, b int, primary key (a), key(b)); 2740alter table t1 disable keys; 2741insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); 2742alter table t1 enable keys; 2743select count(*) from t1; 2744count(*) 274513 2746drop table t1; 2747create table t1 (a int not null, b int, primary key (a), key(b)); 2748lock tables t1 write; 2749alter table t1 disable keys; 2750insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); 2751alter table t1 enable keys; 2752unlock tables; 2753select count(*) from t1; 2754count(*) 275513 2756drop table t1; 2757# 2758# MDEV-14690: Assertion `page_link == &fake_link' failed in 2759# pagecache_write_part 2760# 2761CREATE TABLE t1 (a CHAR(8), b CHAR(8), c CHAR(8) NOT NULL DEFAULT '', f FLOAT, KEY(f)) ENGINE=Aria; 2762INSERT INTO t1 (a) VALUES ('foo'); 2763DELETE FROM t1 WHERE c < 'bar'; 2764ALTER TABLE t1 DISABLE KEYS; 2765INSERT INTO t1 (b) VALUES (''); 2766ALTER TABLE t1 ENABLE KEYS; 2767DROP TABLE t1; 2768# 2769# Start of 5.5 tests 2770# 2771# 2772# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA 2773# 2774CREATE TABLE t1 ( 2775id TINYINT NOT NULL AUTO_INCREMENT, 2776name CHAR(30) NOT NULL, 2777PRIMARY KEY (id) 2778) ENGINE=ARIA; 2779SHOW CREATE TABLE t1; 2780Table Create Table 2781t1 CREATE TABLE `t1` ( 2782 `id` tinyint(4) NOT NULL AUTO_INCREMENT, 2783 `name` char(30) NOT NULL, 2784 PRIMARY KEY (`id`) 2785) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 2786INSERT INTO t1 (name) VALUES ('dog'); 2787UPDATE t1 SET id=-1 WHERE id=1; 2788INSERT INTO t1 (name) VALUES ('cat'); 2789SELECT * FROM t1; 2790id name 2791-1 dog 27922 cat 2793DROP TABLE t1; 2794CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; 2795INSERT INTO t1 VALUES (1,2), (2,3),(3,4); 2796DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); 2797DROP TABLE t1; 2798# 2799# End of 5.5 tests 2800# 2801# 2802# BUG#47444 - --myisam_repair_threads > 1 can result in all index 2803# cardinalities=1 2804# 2805SET aria_repair_threads=2; 2806SET aria_sort_buffer_size=8192; 2807CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); 2808Warnings: 2809Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release 2810Note 1831 Duplicate index `a_3`. This is deprecated and will be disallowed in a future release 2811INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); 2812REPAIR TABLE t1; 2813Table Op Msg_type Msg_text 2814test.t1 repair status OK 2815SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; 2816CARDINALITY 281714 281814 281914 2820CHECK TABLE t1; 2821Table Op Msg_type Msg_text 2822test.t1 check status OK 2823DROP TABLE t1; 2824SET aria_sort_buffer_size=@@global.aria_sort_buffer_size; 2825SET aria_repair_threads=@@global.aria_repair_threads; 2826# 2827# BUG#47073 - valgrind errs, corruption,failed repair of partition, 2828# low myisam_sort_buffer_size 2829# 2830CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); 2831INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), 2832(6,'0'),(7,'0'); 2833INSERT INTO t1 SELECT a+10,b FROM t1; 2834INSERT INTO t1 SELECT a+20,b FROM t1; 2835INSERT INTO t1 SELECT a+40,b FROM t1; 2836INSERT INTO t1 SELECT a+80,b FROM t1; 2837INSERT INTO t1 SELECT a+160,b FROM t1; 2838INSERT INTO t1 SELECT a+320,b FROM t1; 2839INSERT INTO t1 SELECT a+640,b FROM t1; 2840INSERT INTO t1 SELECT a+1280,b FROM t1; 2841INSERT INTO t1 SELECT a+2560,b FROM t1; 2842INSERT INTO t1 SELECT a+5120,b FROM t1; 2843SET aria_sort_buffer_size=4096; 2844REPAIR TABLE t1; 2845Table Op Msg_type Msg_text 2846test.t1 repair error aria_sort_buffer_size is too small. X 2847test.t1 repair error Create index by sort failed 2848test.t1 repair info Retrying repair with keycache 2849test.t1 repair status OK 2850CHECK TABLE t1; 2851Table Op Msg_type Msg_text 2852test.t1 check status OK 2853SET aria_repair_threads=2; 2854REPAIR TABLE t1; 2855Table Op Msg_type Msg_text 2856test.t1 repair error aria_sort_buffer_size is too small. X 2857test.t1 repair error Create index by sort failed 2858test.t1 repair info Retrying repair with keycache 2859test.t1 repair status OK 2860CHECK TABLE t1; 2861Table Op Msg_type Msg_text 2862test.t1 check status OK 2863SET aria_repair_threads=@@global.aria_repair_threads; 2864SET aria_sort_buffer_size=@@global.aria_sort_buffer_size; 2865DROP TABLE t1; 2866CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); 2867INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), 2868(6,'0'),(7,'0'); 2869flush tables test.t1 for export; 2870insert into t1 values (8,'0'); 2871ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 2872unlock tables; 2873drop table t1; 2874# 2875# MDEV-22284 Aria table key read crash because wrong index used 2876# 2877create table t1 ( 2878a int auto_increment, 2879b int, c int, 2880key(c, a), unique(b) 2881) engine aria 2882partition by hash (b); 2883replace into t1 values (1, 0, 0), (2, 0, 0), (0, 0, 0); 2884drop table t1; 2885