mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
11f6b9d12a
--big-tables --large-page-size --storage-engine performance_schema.setup_timers (WL#10986)
2079 lines
72 KiB
Text
2079 lines
72 KiB
Text
#
|
|
# Testing of potential problems in Aria
|
|
# This code was initially taken from myisam.test
|
|
#
|
|
|
|
-- source include/have_maria.inc
|
|
-- source include/have_partition.inc
|
|
-- source include/have_sequence.inc
|
|
--source include/test_db_charset_latin1.inc
|
|
|
|
call mtr.add_suppression("Can't find record in '.*'");
|
|
|
|
select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA";
|
|
|
|
let $default_engine=`select @@global.default_storage_engine`;
|
|
let $default_checksum=`select @@global.aria_page_checksum`;
|
|
set global default_storage_engine=aria;
|
|
set session default_storage_engine=aria;
|
|
set global aria_page_checksum=0;
|
|
let $default_log_file_size=`select @@global.aria_log_file_size`;
|
|
set global aria_log_file_size=4294959104;
|
|
|
|
# Initialise
|
|
SET SQL_WARNINGS=1;
|
|
|
|
#
|
|
# Test problem with CHECK TABLE;
|
|
#
|
|
|
|
CREATE TABLE t1 (
|
|
STRING_DATA char(255) default NULL,
|
|
KEY string_data (STRING_DATA)
|
|
);
|
|
|
|
INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
|
|
INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
|
|
INSERT INTO t1 VALUES ('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF');
|
|
INSERT INTO t1 VALUES ('FGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG');
|
|
INSERT INTO t1 VALUES ('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
|
|
INSERT INTO t1 VALUES ('WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW');
|
|
CHECK TABLE t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test auto_increment warning
|
|
#
|
|
create table t1 (a int primary key auto_increment) engine=aria;
|
|
insert into t1 values (1);
|
|
update t1 set a=0 where a=1;
|
|
check table t1;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test problem with rows that are 65517-65520 bytes long
|
|
#
|
|
|
|
create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a));
|
|
|
|
let $1=100;
|
|
disable_query_log;
|
|
--disable_warnings
|
|
SET SQL_WARNINGS=0;
|
|
while ($1)
|
|
{
|
|
eval insert ignore into t1 (b) values(repeat(char(65+$1),65550-$1));
|
|
dec $1;
|
|
}
|
|
SET SQL_WARNINGS=1;
|
|
--enable_warnings
|
|
--enable_query_log
|
|
check table t1;
|
|
repair table t1;
|
|
delete from t1 where (a & 1);
|
|
check table t1;
|
|
repair table t1;
|
|
check table t1;
|
|
|
|
# FLUSH + REPAIR used to cause assertion failure in page cache
|
|
flush table t1;
|
|
repair table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test bug: Two optimize in a row reset index cardinality
|
|
#
|
|
|
|
create table t1 (a int not null auto_increment, b int not null, primary key (a), index(b));
|
|
insert into t1 (b) values (1),(2),(2),(2),(2);
|
|
optimize table t1;
|
|
show index from t1;
|
|
optimize table t1;
|
|
show index from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of how ORDER BY works when doing it on the whole table
|
|
#
|
|
|
|
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b));
|
|
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
|
|
explain select * from t1 order by a;
|
|
explain select * from t1 order by b;
|
|
explain select * from t1 order by c;
|
|
explain select a from t1 order by a;
|
|
explain select b from t1 order by b;
|
|
explain select a,b from t1 order by b;
|
|
explain select a,b from t1;
|
|
explain select a,b,c from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of OPTIMIZE of locked and modified tables
|
|
#
|
|
set autocommit=0;
|
|
begin;
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
LOCK TABLES t1 WRITE;
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
commit;
|
|
set autocommit=1;
|
|
UNLOCK TABLES;
|
|
OPTIMIZE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Test of optimize, when only mi_sort_index (but not mi_repair*) is done
|
|
# in ha_aria::repair, and index size is changed (decreased).
|
|
#
|
|
|
|
create table t1 ( t1 char(255), key(t1(250)));
|
|
insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169');
|
|
insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203');
|
|
insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767');
|
|
insert t1 values ('242624262426242624262426242624262426242607890789078907890789078907890789078907');
|
|
insert t1 values ('256025602560256025602560256025602560256011701170117011701170117011701170117011');
|
|
insert t1 values ('276027602760276027602760276027602760276001610161016101610161016101610161016101');
|
|
insert t1 values ('281528152815281528152815281528152815281564956495649564956495649564956495649564');
|
|
insert t1 values ('292129212921292129212921292129212921292102100210021002100210021002100210021002');
|
|
insert t1 values ('380638063806380638063806380638063806380634483448344834483448344834483448344834');
|
|
insert t1 values ('411641164116411641164116411641164116411616301630163016301630163016301630163016');
|
|
insert t1 values ('420842084208420842084208420842084208420899889988998899889988998899889988998899');
|
|
insert t1 values ('438443844384438443844384438443844384438482448244824482448244824482448244824482');
|
|
insert t1 values ('443244324432443244324432443244324432443239613961396139613961396139613961396139');
|
|
insert t1 values ('485448544854485448544854485448544854485477847784778477847784778477847784778477');
|
|
insert t1 values ('494549454945494549454945494549454945494555275527552755275527552755275527552755');
|
|
insert t1 values ('538647864786478647864786478647864786478688918891889188918891889188918891889188');
|
|
insert t1 values ('565556555655565556555655565556555655565554845484548454845484548454845484548454');
|
|
insert t1 values ('607860786078607860786078607860786078607856665666566656665666566656665666566656');
|
|
insert t1 values ('640164016401640164016401640164016401640141274127412741274127412741274127412741');
|
|
insert t1 values ('719471947194719471947194719471947194719478717871787178717871787178717871787178');
|
|
insert t1 values ('742574257425742574257425742574257425742549604960496049604960496049604960496049');
|
|
insert t1 values ('887088708870887088708870887088708870887035963596359635963596359635963596359635');
|
|
insert t1 values ('917791779177917791779177917791779177917773857385738573857385738573857385738573');
|
|
insert t1 values ('933293329332933293329332933293329332933278987898789878987898789878987898789878');
|
|
insert t1 values ('963896389638963896389638963896389638963877807780778077807780778077807780778077');
|
|
delete from t1 where t1>'2';
|
|
insert t1 values ('70'), ('84'), ('60'), ('20'), ('76'), ('89'), ('49'), ('50'),
|
|
('88'), ('61'), ('42'), ('98'), ('39'), ('30'), ('25'), ('66'), ('61'), ('48'),
|
|
('80'), ('84'), ('98'), ('19'), ('91'), ('42'), ('47');
|
|
optimize table t1;
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# test of aria with huge number of packed fields
|
|
#
|
|
|
|
create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8
|
|
int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17
|
|
int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int,
|
|
i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34
|
|
int, i35 int, i36 int, i37 int, i38 int, i39 int, i40 int, i41 int, i42 int,
|
|
i43 int, i44 int, i45 int, i46 int, i47 int, i48 int, i49 int, i50 int, i51
|
|
int, i52 int, i53 int, i54 int, i55 int, i56 int, i57 int, i58 int, i59 int,
|
|
i60 int, i61 int, i62 int, i63 int, i64 int, i65 int, i66 int, i67 int, i68
|
|
int, i69 int, i70 int, i71 int, i72 int, i73 int, i74 int, i75 int, i76 int,
|
|
i77 int, i78 int, i79 int, i80 int, i81 int, i82 int, i83 int, i84 int, i85
|
|
int, i86 int, i87 int, i88 int, i89 int, i90 int, i91 int, i92 int, i93 int,
|
|
i94 int, i95 int, i96 int, i97 int, i98 int, i99 int, i100 int, i101 int, i102
|
|
int, i103 int, i104 int, i105 int, i106 int, i107 int, i108 int, i109 int, i110
|
|
int, i111 int, i112 int, i113 int, i114 int, i115 int, i116 int, i117 int, i118
|
|
int, i119 int, i120 int, i121 int, i122 int, i123 int, i124 int, i125 int, i126
|
|
int, i127 int, i128 int, i129 int, i130 int, i131 int, i132 int, i133 int, i134
|
|
int, i135 int, i136 int, i137 int, i138 int, i139 int, i140 int, i141 int, i142
|
|
int, i143 int, i144 int, i145 int, i146 int, i147 int, i148 int, i149 int, i150
|
|
int, i151 int, i152 int, i153 int, i154 int, i155 int, i156 int, i157 int, i158
|
|
int, i159 int, i160 int, i161 int, i162 int, i163 int, i164 int, i165 int, i166
|
|
int, i167 int, i168 int, i169 int, i170 int, i171 int, i172 int, i173 int, i174
|
|
int, i175 int, i176 int, i177 int, i178 int, i179 int, i180 int, i181 int, i182
|
|
int, i183 int, i184 int, i185 int, i186 int, i187 int, i188 int, i189 int, i190
|
|
int, i191 int, i192 int, i193 int, i194 int, i195 int, i196 int, i197 int, i198
|
|
int, i199 int, i200 int, i201 int, i202 int, i203 int, i204 int, i205 int, i206
|
|
int, i207 int, i208 int, i209 int, i210 int, i211 int, i212 int, i213 int, i214
|
|
int, i215 int, i216 int, i217 int, i218 int, i219 int, i220 int, i221 int, i222
|
|
int, i223 int, i224 int, i225 int, i226 int, i227 int, i228 int, i229 int, i230
|
|
int, i231 int, i232 int, i233 int, i234 int, i235 int, i236 int, i237 int, i238
|
|
int, i239 int, i240 int, i241 int, i242 int, i243 int, i244 int, i245 int, i246
|
|
int, i247 int, i248 int, i249 int, i250 int, i251 int, i252 int, i253 int, i254
|
|
int, i255 int, i256 int, i257 int, i258 int, i259 int, i260 int, i261 int, i262
|
|
int, i263 int, i264 int, i265 int, i266 int, i267 int, i268 int, i269 int, i270
|
|
int, i271 int, i272 int, i273 int, i274 int, i275 int, i276 int, i277 int, i278
|
|
int, i279 int, i280 int, i281 int, i282 int, i283 int, i284 int, i285 int, i286
|
|
int, i287 int, i288 int, i289 int, i290 int, i291 int, i292 int, i293 int, i294
|
|
int, i295 int, i296 int, i297 int, i298 int, i299 int, i300 int, i301 int, i302
|
|
int, i303 int, i304 int, i305 int, i306 int, i307 int, i308 int, i309 int, i310
|
|
int, i311 int, i312 int, i313 int, i314 int, i315 int, i316 int, i317 int, i318
|
|
int, i319 int, i320 int, i321 int, i322 int, i323 int, i324 int, i325 int, i326
|
|
int, i327 int, i328 int, i329 int, i330 int, i331 int, i332 int, i333 int, i334
|
|
int, i335 int, i336 int, i337 int, i338 int, i339 int, i340 int, i341 int, i342
|
|
int, i343 int, i344 int, i345 int, i346 int, i347 int, i348 int, i349 int, i350
|
|
int, i351 int, i352 int, i353 int, i354 int, i355 int, i356 int, i357 int, i358
|
|
int, i359 int, i360 int, i361 int, i362 int, i363 int, i364 int, i365 int, i366
|
|
int, i367 int, i368 int, i369 int, i370 int, i371 int, i372 int, i373 int, i374
|
|
int, i375 int, i376 int, i377 int, i378 int, i379 int, i380 int, i381 int, i382
|
|
int, i383 int, i384 int, i385 int, i386 int, i387 int, i388 int, i389 int, i390
|
|
int, i391 int, i392 int, i393 int, i394 int, i395 int, i396 int, i397 int, i398
|
|
int, i399 int, i400 int, i401 int, i402 int, i403 int, i404 int, i405 int, i406
|
|
int, i407 int, i408 int, i409 int, i410 int, i411 int, i412 int, i413 int, i414
|
|
int, i415 int, i416 int, i417 int, i418 int, i419 int, i420 int, i421 int, i422
|
|
int, i423 int, i424 int, i425 int, i426 int, i427 int, i428 int, i429 int, i430
|
|
int, i431 int, i432 int, i433 int, i434 int, i435 int, i436 int, i437 int, i438
|
|
int, i439 int, i440 int, i441 int, i442 int, i443 int, i444 int, i445 int, i446
|
|
int, i447 int, i448 int, i449 int, i450 int, i451 int, i452 int, i453 int, i454
|
|
int, i455 int, i456 int, i457 int, i458 int, i459 int, i460 int, i461 int, i462
|
|
int, i463 int, i464 int, i465 int, i466 int, i467 int, i468 int, i469 int, i470
|
|
int, i471 int, i472 int, i473 int, i474 int, i475 int, i476 int, i477 int, i478
|
|
int, i479 int, i480 int, i481 int, i482 int, i483 int, i484 int, i485 int, i486
|
|
int, i487 int, i488 int, i489 int, i490 int, i491 int, i492 int, i493 int, i494
|
|
int, i495 int, i496 int, i497 int, i498 int, i499 int, i500 int, i501 int, i502
|
|
int, i503 int, i504 int, i505 int, i506 int, i507 int, i508 int, i509 int, i510
|
|
int, i511 int, i512 int, i513 int, i514 int, i515 int, i516 int, i517 int, i518
|
|
int, i519 int, i520 int, i521 int, i522 int, i523 int, i524 int, i525 int, i526
|
|
int, i527 int, i528 int, i529 int, i530 int, i531 int, i532 int, i533 int, i534
|
|
int, i535 int, i536 int, i537 int, i538 int, i539 int, i540 int, i541 int, i542
|
|
int, i543 int, i544 int, i545 int, i546 int, i547 int, i548 int, i549 int, i550
|
|
int, i551 int, i552 int, i553 int, i554 int, i555 int, i556 int, i557 int, i558
|
|
int, i559 int, i560 int, i561 int, i562 int, i563 int, i564 int, i565 int, i566
|
|
int, i567 int, i568 int, i569 int, i570 int, i571 int, i572 int, i573 int, i574
|
|
int, i575 int, i576 int, i577 int, i578 int, i579 int, i580 int, i581 int, i582
|
|
int, i583 int, i584 int, i585 int, i586 int, i587 int, i588 int, i589 int, i590
|
|
int, i591 int, i592 int, i593 int, i594 int, i595 int, i596 int, i597 int, i598
|
|
int, i599 int, i600 int, i601 int, i602 int, i603 int, i604 int, i605 int, i606
|
|
int, i607 int, i608 int, i609 int, i610 int, i611 int, i612 int, i613 int, i614
|
|
int, i615 int, i616 int, i617 int, i618 int, i619 int, i620 int, i621 int, i622
|
|
int, i623 int, i624 int, i625 int, i626 int, i627 int, i628 int, i629 int, i630
|
|
int, i631 int, i632 int, i633 int, i634 int, i635 int, i636 int, i637 int, i638
|
|
int, i639 int, i640 int, i641 int, i642 int, i643 int, i644 int, i645 int, i646
|
|
int, i647 int, i648 int, i649 int, i650 int, i651 int, i652 int, i653 int, i654
|
|
int, i655 int, i656 int, i657 int, i658 int, i659 int, i660 int, i661 int, i662
|
|
int, i663 int, i664 int, i665 int, i666 int, i667 int, i668 int, i669 int, i670
|
|
int, i671 int, i672 int, i673 int, i674 int, i675 int, i676 int, i677 int, i678
|
|
int, i679 int, i680 int, i681 int, i682 int, i683 int, i684 int, i685 int, i686
|
|
int, i687 int, i688 int, i689 int, i690 int, i691 int, i692 int, i693 int, i694
|
|
int, i695 int, i696 int, i697 int, i698 int, i699 int, i700 int, i701 int, i702
|
|
int, i703 int, i704 int, i705 int, i706 int, i707 int, i708 int, i709 int, i710
|
|
int, i711 int, i712 int, i713 int, i714 int, i715 int, i716 int, i717 int, i718
|
|
int, i719 int, i720 int, i721 int, i722 int, i723 int, i724 int, i725 int, i726
|
|
int, i727 int, i728 int, i729 int, i730 int, i731 int, i732 int, i733 int, i734
|
|
int, i735 int, i736 int, i737 int, i738 int, i739 int, i740 int, i741 int, i742
|
|
int, i743 int, i744 int, i745 int, i746 int, i747 int, i748 int, i749 int, i750
|
|
int, i751 int, i752 int, i753 int, i754 int, i755 int, i756 int, i757 int, i758
|
|
int, i759 int, i760 int, i761 int, i762 int, i763 int, i764 int, i765 int, i766
|
|
int, i767 int, i768 int, i769 int, i770 int, i771 int, i772 int, i773 int, i774
|
|
int, i775 int, i776 int, i777 int, i778 int, i779 int, i780 int, i781 int, i782
|
|
int, i783 int, i784 int, i785 int, i786 int, i787 int, i788 int, i789 int, i790
|
|
int, i791 int, i792 int, i793 int, i794 int, i795 int, i796 int, i797 int, i798
|
|
int, i799 int, i800 int, i801 int, i802 int, i803 int, i804 int, i805 int, i806
|
|
int, i807 int, i808 int, i809 int, i810 int, i811 int, i812 int, i813 int, i814
|
|
int, i815 int, i816 int, i817 int, i818 int, i819 int, i820 int, i821 int, i822
|
|
int, i823 int, i824 int, i825 int, i826 int, i827 int, i828 int, i829 int, i830
|
|
int, i831 int, i832 int, i833 int, i834 int, i835 int, i836 int, i837 int, i838
|
|
int, i839 int, i840 int, i841 int, i842 int, i843 int, i844 int, i845 int, i846
|
|
int, i847 int, i848 int, i849 int, i850 int, i851 int, i852 int, i853 int, i854
|
|
int, i855 int, i856 int, i857 int, i858 int, i859 int, i860 int, i861 int, i862
|
|
int, i863 int, i864 int, i865 int, i866 int, i867 int, i868 int, i869 int, i870
|
|
int, i871 int, i872 int, i873 int, i874 int, i875 int, i876 int, i877 int, i878
|
|
int, i879 int, i880 int, i881 int, i882 int, i883 int, i884 int, i885 int, i886
|
|
int, i887 int, i888 int, i889 int, i890 int, i891 int, i892 int, i893 int, i894
|
|
int, i895 int, i896 int, i897 int, i898 int, i899 int, i900 int, i901 int, i902
|
|
int, i903 int, i904 int, i905 int, i906 int, i907 int, i908 int, i909 int, i910
|
|
int, i911 int, i912 int, i913 int, i914 int, i915 int, i916 int, i917 int, i918
|
|
int, i919 int, i920 int, i921 int, i922 int, i923 int, i924 int, i925 int, i926
|
|
int, i927 int, i928 int, i929 int, i930 int, i931 int, i932 int, i933 int, i934
|
|
int, i935 int, i936 int, i937 int, i938 int, i939 int, i940 int, i941 int, i942
|
|
int, i943 int, i944 int, i945 int, i946 int, i947 int, i948 int, i949 int, i950
|
|
int, i951 int, i952 int, i953 int, i954 int, i955 int, i956 int, i957 int, i958
|
|
int, i959 int, i960 int, i961 int, i962 int, i963 int, i964 int, i965 int, i966
|
|
int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974
|
|
int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982
|
|
int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990
|
|
int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998
|
|
int, i999 int, i1000 int, b blob) row_format=dynamic;
|
|
insert into t1 values (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, 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, 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, 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, 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, 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, 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,
|
|
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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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,
|
|
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, 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, 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, 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, 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, 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, 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, "Sergei");
|
|
update t1 set b=repeat('a',256);
|
|
update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0;
|
|
check table t1;
|
|
delete from t1 where i8=1;
|
|
select i1,i2 from t1;
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of REPAIR that once failed
|
|
#
|
|
CREATE TABLE `t1` (
|
|
`post_id` mediumint(8) unsigned NOT NULL auto_increment,
|
|
`topic_id` mediumint(8) unsigned NOT NULL default '0',
|
|
`post_time` datetime NOT NULL default '0000-00-00 00:00:00',
|
|
`post_text` text NOT NULL,
|
|
`icon_url` varchar(10) NOT NULL default '',
|
|
`sign` tinyint(1) unsigned NOT NULL default '0',
|
|
`post_edit` varchar(150) NOT NULL default '',
|
|
`poster_login` varchar(35) NOT NULL default '',
|
|
`ip` varchar(15) NOT NULL default '',
|
|
PRIMARY KEY (`post_id`),
|
|
KEY `post_time` (`post_time`),
|
|
KEY `ip` (`ip`),
|
|
KEY `poster_login` (`poster_login`),
|
|
KEY `topic_id` (`topic_id`),
|
|
FULLTEXT KEY `post_text` (`post_text`)
|
|
) TRANSACTIONAL=0;
|
|
|
|
INSERT 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');
|
|
|
|
REPAIR TABLE t1;
|
|
CHECK TABLE t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of creating table with too long key
|
|
#
|
|
|
|
--error 1071
|
|
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), f varchar(255), g varchar(255), h varchar(255), i varchar(255), j varchar(255), KEY t1 (a, b, c, d, e, f, g, h, i, j));
|
|
--error 1070
|
|
CREATE 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));
|
|
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), f varchar(255), g varchar(255), h varchar(255), i varchar(255), j varchar(255));
|
|
--error 1071
|
|
ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j);
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Test of cardinality of keys with NULL
|
|
#
|
|
|
|
CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a));
|
|
INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4);
|
|
INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9);
|
|
create table t2 (a int not null, b int, c int, key(b), key(c), key(a));
|
|
INSERT into t2 values (1,1,1), (2,2,2);
|
|
optimize table t1;
|
|
check table t1;
|
|
show index from t1;
|
|
explain select * from t1,t2 where t1.a=t2.a;
|
|
explain select * from t1,t2 force index(a) where t1.a=t2.a;
|
|
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
|
|
explain select * from t1,t2 where t1.b=t2.b;
|
|
explain select * from t1,t2 force index(c) where t1.a=t2.a;
|
|
explain select * from t1 where a=0 or a=2;
|
|
explain select * from t1 force index (a) where a=0 or a=2;
|
|
explain select * from t1 where c=1;
|
|
explain select * from t1 use index() where c=1;
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Test bug when updating a split dynamic row where keys are not changed
|
|
#
|
|
|
|
create table t1 (a int not null auto_increment primary key, b varchar(255));
|
|
insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100));
|
|
update t1 set b=repeat(left(b,1),200) where a=1;
|
|
delete from t1 where (a & 1)= 0;
|
|
update t1 set b=repeat('e',200) where a=1;
|
|
flush tables;
|
|
check table t1;
|
|
|
|
#
|
|
# check updating with keys
|
|
#
|
|
|
|
disable_query_log;
|
|
let $1 = 100;
|
|
while ($1)
|
|
{
|
|
eval insert into t1 (b) values (repeat(char(($1 & 32)+65), $1));
|
|
dec $1;
|
|
}
|
|
enable_query_log;
|
|
update t1 set b=repeat(left(b,1),255) where a between 1 and 5;
|
|
update t1 set b=repeat(left(b,1),10) where a between 32 and 43;
|
|
update t1 set b=repeat(left(b,1),2) where a between 64 and 66;
|
|
update t1 set b=repeat(left(b,1),65) where a between 67 and 70;
|
|
check table t1;
|
|
insert into t1 (b) values (repeat('z',100));
|
|
update t1 set b="test" where left(b,1) > 'n';
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test space-stripping features
|
|
#
|
|
create table t1 ( a text not null, key a (a(20)));
|
|
insert into t1 values ('aaa '),('aaa'),('aa');
|
|
check table t1;
|
|
repair table t1;
|
|
select concat(a,'.') from t1 where a='aaa';
|
|
select concat(a,'.') from t1 where binary a='aaa';
|
|
update t1 set a='bbb' where a='aaa';
|
|
select concat(a,'.') from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test again but with dynamic format
|
|
#
|
|
create table t1 ( a text not null, key a (a(20))) row_format=dynamic;
|
|
insert into t1 values ('aaa '),('aaa'),('aa');
|
|
check table t1;
|
|
repair table t1;
|
|
select concat(a,'.') from t1 where a='aaa';
|
|
select concat(a,'.') from t1 where binary a='aaa';
|
|
update t1 set a='bbb' where a='aaa';
|
|
select concat(a,'.') from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# More space testing
|
|
#
|
|
|
|
create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10)));
|
|
insert into t1 values('807780', '477', '165');
|
|
insert into t1 values('807780', '477', '162');
|
|
insert into t1 values('807780', '472', '162');
|
|
select * from t1 where a='807780' and b='477' and c='165';
|
|
drop table t1;
|
|
|
|
#
|
|
# Space-stripping in prefix_search
|
|
#
|
|
|
|
CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a));
|
|
INSERT t1 VALUES ("can \tcan");
|
|
INSERT t1 VALUES ("can can");
|
|
INSERT t1 VALUES ("can");
|
|
SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Verify blob handling
|
|
#
|
|
|
|
create table t1 (a blob);
|
|
insert into t1 values('a '),('a');
|
|
select concat(a,'.') from t1 where a='a';
|
|
select concat(a,'.') from t1 where a='a ';
|
|
alter table t1 add key(a(2));
|
|
select concat(a,'.') from t1 where a='a';
|
|
select concat(a,'.') from t1 where a='a ';
|
|
drop table t1;
|
|
|
|
#
|
|
# Test text and unique
|
|
#
|
|
create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20)));
|
|
insert into t1 (b) values ('a'),('b'),('c');
|
|
select concat(b,'.') from t1;
|
|
update t1 set b='b ' where a=2;
|
|
--error ER_DUP_ENTRY
|
|
update t1 set b='b ' where a > 1;
|
|
--error ER_DUP_ENTRY
|
|
insert into t1 (b) values ('b');
|
|
select * from t1;
|
|
delete from t1 where b='b';
|
|
select a,concat(b,'.') from t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test keys with 0 segments
|
|
#
|
|
create table t1 (a int not null);
|
|
create table t2 (a int not null, primary key (a));
|
|
insert into t1 values (1);
|
|
insert into t2 values (1),(2);
|
|
select sql_big_result distinct t1.a from t1,t2 order by t2.a;
|
|
select distinct t1.a from t1,t2 order by t2.a;
|
|
select sql_big_result distinct t1.a from t1,t2;
|
|
explain select sql_big_result distinct t1.a from t1,t2 order by t2.a;
|
|
explain select distinct t1.a from t1,t2 order by t2.a;
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Test freshly imported table and LIMIT
|
|
#
|
|
create table t1 (
|
|
c1 varchar(32),
|
|
key (c1)
|
|
);
|
|
alter table t1 disable keys;
|
|
insert into t1 values ('a'), ('b');
|
|
select c1 from t1 order by c1 limit 1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test join that could miss concurrently inserted row
|
|
# Note that for the moment Aria only supports multiple writers if we have
|
|
# static or dynamic row format
|
|
#
|
|
# Partial key.
|
|
create table t1 (a int not null, primary key(a)) ROW_FORMAT=FIXED;
|
|
create table t2 (a int not null, b int not null, primary key(a,b)) ROW_FORMAT=FIXED;
|
|
insert into t1 values (1),(2),(3),(4),(5),(6);
|
|
insert into t2 values (1,1),(2,1);
|
|
set autocommit=0;
|
|
begin;
|
|
lock tables t1 read local, t2 read local;
|
|
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
|
|
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
|
|
insert into t2 values(2,0);
|
|
commit;
|
|
disconnect root;
|
|
connection default;
|
|
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
|
|
unlock tables;
|
|
drop table t1,t2;
|
|
set autocommit=1;
|
|
#
|
|
# Full key.
|
|
CREATE TABLE t1 (c1 varchar(250) NOT NULL) ROW_FORMAT=DYNAMIC;
|
|
CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)) ROW_FORMAT=DYNAMIC;
|
|
INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003');
|
|
INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004');
|
|
LOCK TABLES t1 READ LOCAL, t2 READ LOCAL;
|
|
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
|
|
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
|
|
connect (con1,localhost,root,,);
|
|
connection con1;
|
|
INSERT INTO t2 VALUES ('test000001'), ('test000005');
|
|
disconnect con1;
|
|
connection default;
|
|
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
|
|
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
|
|
unlock tables;
|
|
DROP TABLE t1,t2;
|
|
|
|
#
|
|
# Test RTREE index
|
|
#
|
|
--error ER_WRONG_ARGUMENTS
|
|
CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`));
|
|
# INSERT INTO t1 VALUES (1,1),(1,1);
|
|
# DELETE FROM rt WHERE a<1;
|
|
# DROP TABLE IF EXISTS t1;
|
|
|
|
create table t1 (a int, b varchar(200), c text not null) checksum=1;
|
|
create table t2 (a int, b varchar(200), c text not null) checksum=0;
|
|
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
|
|
insert t2 select * from t1;
|
|
checksum table t1, t2, t3 quick;
|
|
checksum table t1, t2, t3;
|
|
checksum table t1, t2, t3 extended;
|
|
#show table status;
|
|
drop table t1,t2;
|
|
|
|
create table t1 (a int, key (a));
|
|
show keys from t1;
|
|
alter table t1 disable keys;
|
|
show keys from t1;
|
|
create table t2 (a int);
|
|
let $i=1000;
|
|
set @@rand_seed1=31415926,@@rand_seed2=2718281828;
|
|
--disable_query_log
|
|
while ($i)
|
|
{
|
|
dec $i;
|
|
insert t2 values (rand()*100000);
|
|
}
|
|
--enable_query_log
|
|
insert t1 select * from t2;
|
|
show keys from t1;
|
|
alter table t1 enable keys;
|
|
show keys from t1;
|
|
alter table t1 engine=heap;
|
|
alter table t1 disable keys;
|
|
show keys from t1;
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Index search for NULL in blob
|
|
#
|
|
create table t1 ( a tinytext, b char(1), index idx (a(1),b) );
|
|
insert into t1 values (null,''), (null,'');
|
|
explain select count(*) from t1 where a is null;
|
|
insert into t1 values(1,'a');
|
|
explain select count(*) from t1 where a is null;
|
|
explain select count(*) from t1 where a = 'a';
|
|
select count(*) from t1 where a is null;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test corruption Can't open file: 'table.MYI' (errno: 145)
|
|
#
|
|
create table t1 (c1 int, c2 varchar(4) not null default '',
|
|
key(c2(3))) default charset=utf8;
|
|
insert into t1 values (1,'A'), (2, 'B'), (3, 'A');
|
|
update t1 set c2='A B' where c1=2;
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test CHECKSUM TABLE
|
|
#
|
|
|
|
create table t1 (c1 int);
|
|
insert into t1 values (1),(2),(3),(4);
|
|
checksum table t1;
|
|
delete from t1 where c1 = 1;
|
|
create table t2 as select * from t1;
|
|
# The following returns 0 with the bug in place.
|
|
checksum table t1;
|
|
# The above should give the same number as the following.
|
|
checksum table t2;
|
|
drop table t1, t2;
|
|
|
|
CREATE TABLE t1 (
|
|
twenty int(4),
|
|
hundred int(4) NOT NULL
|
|
) CHECKSUM=1;
|
|
INSERT INTO t1 VALUES (11,91);
|
|
check table t1 extended;
|
|
checksum table t1;
|
|
checksum table t1 extended;
|
|
alter table t1 row_format=fixed;
|
|
checksum table t1;
|
|
alter table t1 row_format=dynamic;
|
|
checksum table t1;
|
|
alter table t1 engine=myisam;
|
|
checksum table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# aria_stats_method variable.
|
|
#
|
|
|
|
show variables like 'aria_stats_method';
|
|
|
|
create table t1 (a int, key(a));
|
|
insert into t1 values (0),(1),(2),(3),(4);
|
|
insert into t1 select NULL from t1;
|
|
|
|
# default: NULLs considered inequal
|
|
analyze table t1;
|
|
show index from t1;
|
|
insert into t1 values (11);
|
|
delete from t1 where a=11;
|
|
check table t1;
|
|
show index from t1;
|
|
|
|
# Set nulls to be equal:
|
|
set aria_stats_method=nulls_equal;
|
|
|
|
set @save_use_stat_tables= @@use_stat_tables;
|
|
set @@use_stat_tables= COMPLEMENTARY;
|
|
|
|
show variables like 'aria_stats_method';
|
|
insert into t1 values (11);
|
|
delete from t1 where a=11;
|
|
|
|
analyze table t1;
|
|
show index from t1;
|
|
|
|
insert into t1 values (11);
|
|
delete from t1 where a=11;
|
|
|
|
check table t1;
|
|
show index from t1;
|
|
|
|
set @@use_stat_tables= @save_use_stat_tables;
|
|
# Set nulls back to be equal
|
|
set aria_stats_method=DEFAULT;
|
|
show variables like 'aria_stats_method';
|
|
insert into t1 values (11);
|
|
delete from t1 where a=11;
|
|
|
|
analyze table t1;
|
|
show index from t1;
|
|
|
|
insert into t1 values (11);
|
|
delete from t1 where a=11;
|
|
|
|
check table t1;
|
|
show index from t1;
|
|
|
|
drop table t1;
|
|
|
|
# WL#2609, CSC#XXXX: ARIA
|
|
set aria_stats_method=nulls_ignored;
|
|
show variables like 'aria_stats_method';
|
|
|
|
set @save_use_stat_tables= @@use_stat_tables;
|
|
set @@use_stat_tables= COMPLEMENTARY;
|
|
|
|
create table t1 (
|
|
a char(3), b char(4), c char(5), d char(6),
|
|
key(a,b,c,d)
|
|
);
|
|
insert into t1 values ('bcd','def1', NULL, 'zz');
|
|
insert into t1 values ('bcd','def2', NULL, 'zz');
|
|
insert into t1 values ('bce','def1', 'yuu', NULL);
|
|
insert into t1 values ('bce','def2', NULL, 'quux');
|
|
analyze table t1;
|
|
show index from t1;
|
|
delete from t1;
|
|
# This will give you different messages depending on if we are using
|
|
# row base or stmt based replication as stmt base replication will use
|
|
# truncate and row based will delete things row by row.
|
|
--replace_result "Table is already up to date" "OK"
|
|
analyze table t1;
|
|
show index from t1;
|
|
|
|
set @@use_stat_tables= @save_use_stat_tables;
|
|
set aria_stats_method=DEFAULT;
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Test key value packing for TINYBLOBs
|
|
#
|
|
|
|
create table t1(
|
|
cip INT NOT NULL,
|
|
time TIME NOT NULL,
|
|
score INT NOT NULL DEFAULT 0,
|
|
bob TINYBLOB
|
|
);
|
|
|
|
insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03');
|
|
insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'),
|
|
(6, 'c', '00:06');
|
|
select * from t1 where bob is null and cip=1;
|
|
create index bug on t1 (bob(22), cip, time);
|
|
select * from t1 where bob is null and cip=1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test COUNT(*) table with different INDEX
|
|
#
|
|
|
|
create table t1 (
|
|
id1 int not null auto_increment,
|
|
id2 int not null default '0',
|
|
t text not null,
|
|
primary key (id1),
|
|
key x (id2, t(32))
|
|
) engine=aria; # engine clause is redundant but it's to test its parsing
|
|
insert into t1 (id2, t) values
|
|
(10, 'abc'), (10, 'abc'), (10, 'abc'),
|
|
(20, 'abc'), (20, 'abc'), (20, 'def'),
|
|
(10, 'abc'), (10, 'abc');
|
|
select count(*) from t1 where id2 = 10;
|
|
select count(id1) from t1 where id2 = 10;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test MIN and MAX functions in queries
|
|
#
|
|
|
|
CREATE TABLE t1(a TINYINT, KEY(a));
|
|
INSERT INTO t1 VALUES(1);
|
|
SELECT MAX(a) FROM t1 IGNORE INDEX(a);
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
SELECT MAX(a) FROM t1;
|
|
SELECT MAX(a) FROM t1 IGNORE INDEX(a);
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Test update of table joined to self
|
|
#
|
|
CREATE TABLE t1(a CHAR(9), b VARCHAR(7));
|
|
INSERT INTO t1(a) VALUES('xxxxxxxxx'),('xxxxxxxxx');
|
|
UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb';
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# OPTIMIZE TABLE with multiple threads
|
|
#
|
|
SET @@aria_repair_threads=2;
|
|
SHOW VARIABLES LIKE 'aria_repair%';
|
|
#
|
|
# Test OPTIMIZE. This creates a new data file.
|
|
CREATE TABLE t1 (
|
|
`_id` int(11) NOT NULL default '0',
|
|
`url` text,
|
|
`email` text,
|
|
`description` text,
|
|
`loverlap` int(11) default NULL,
|
|
`roverlap` int(11) default NULL,
|
|
`lneighbor_id` int(11) default NULL,
|
|
`rneighbor_id` int(11) default NULL,
|
|
`length_` int(11) default NULL,
|
|
`sequence` mediumtext,
|
|
`name` text,
|
|
`_obj_class` text NOT NULL,
|
|
PRIMARY KEY (`_id`),
|
|
UNIQUE KEY `sequence_name_index` (`name`(50)),
|
|
KEY (`length_`)
|
|
) DEFAULT CHARSET=latin1 row_format=dynamic;
|
|
#
|
|
INSERT INTO t1 VALUES
|
|
(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),
|
|
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),
|
|
(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''),
|
|
(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''),
|
|
(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''),
|
|
(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''),
|
|
(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''),
|
|
(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''),
|
|
(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9','');
|
|
#
|
|
SELECT _id FROM t1;
|
|
DELETE FROM t1 WHERE _id < 8;
|
|
SELECT 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';
|
|
CHECK TABLE t1 EXTENDED;
|
|
OPTIMIZE TABLE t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
SELECT 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';
|
|
SELECT _id FROM t1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test REPAIR QUICK. This retains the old data file.
|
|
CREATE TABLE t1 (
|
|
`_id` int(11) NOT NULL default '0',
|
|
`url` text,
|
|
`email` text,
|
|
`description` text,
|
|
`loverlap` int(11) default NULL,
|
|
`roverlap` int(11) default NULL,
|
|
`lneighbor_id` int(11) default NULL,
|
|
`rneighbor_id` int(11) default NULL,
|
|
`length_` int(11) default NULL,
|
|
`sequence` mediumtext,
|
|
`name` text,
|
|
`_obj_class` text NOT NULL,
|
|
PRIMARY KEY (`_id`),
|
|
UNIQUE KEY `sequence_name_index` (`name`(50)),
|
|
KEY (`length_`)
|
|
) DEFAULT CHARSET=latin1 row_format=dynamic;
|
|
#
|
|
INSERT INTO t1 VALUES
|
|
(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),
|
|
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),
|
|
(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''),
|
|
(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''),
|
|
(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''),
|
|
(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''),
|
|
(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''),
|
|
(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''),
|
|
(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9','');
|
|
#
|
|
SELECT _id FROM t1;
|
|
DELETE FROM t1 WHERE _id < 8;
|
|
SELECT 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';
|
|
CHECK TABLE t1 EXTENDED;
|
|
REPAIR TABLE t1 QUICK;
|
|
CHECK TABLE t1 EXTENDED;
|
|
SELECT 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';
|
|
SELECT _id FROM t1;
|
|
DROP TABLE t1;
|
|
#
|
|
SET @@aria_repair_threads=1;
|
|
SHOW VARIABLES LIKE 'aria_repair%';
|
|
|
|
#
|
|
# Test varchar
|
|
#
|
|
|
|
source include/varchar.inc;
|
|
|
|
#
|
|
# Some errors/warnings on create
|
|
#
|
|
|
|
create table t1 (v varchar(65530), key(v));
|
|
drop table if exists t1;
|
|
set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
|
|
create table t1 (v varchar(65536));
|
|
show create table t1;
|
|
drop table t1;
|
|
set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
|
|
create table t1 (v varchar(65530) character set utf8);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
# ARIA specific varchar tests
|
|
set statement sql_mode='' for
|
|
create table t1 (v varchar(65535));
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test concurrent insert
|
|
# First with static record length
|
|
#
|
|
set @save_concurrent_insert=@@concurrent_insert;
|
|
set global concurrent_insert=1;
|
|
create table t1 (a int) ROW_FORMAT=FIXED;
|
|
insert into t1 values (1),(2),(3),(4),(5);
|
|
lock table t1 read local;
|
|
connect (con1,localhost,root,,);
|
|
connection con1;
|
|
# Insert in table without hole
|
|
insert into t1 values(6),(7);
|
|
connection default;
|
|
unlock tables;
|
|
delete from t1 where a>=3 and a<=4;
|
|
lock table t1 read local;
|
|
connection con1;
|
|
set global concurrent_insert=2;
|
|
# Insert in table with hole -> Should insert at end
|
|
insert into t1 values (8),(9);
|
|
connection default;
|
|
unlock tables;
|
|
# Insert into hole
|
|
insert into t1 values (10),(11),(12);
|
|
select * from t1;
|
|
check table t1;
|
|
drop table t1;
|
|
disconnect con1;
|
|
|
|
# Same test with dynamic record length
|
|
create table t1 (a int, b varchar(30) default "hello") ROW_FORMAT=DYNAMIC;
|
|
insert into t1 (a) values (1),(2),(3),(4),(5);
|
|
lock table t1 read local;
|
|
connect (con1,localhost,root,,);
|
|
connection con1;
|
|
# Insert in table without hole
|
|
insert into t1 (a) values(6),(7);
|
|
connection default;
|
|
unlock tables;
|
|
delete from t1 where a>=3 and a<=4;
|
|
lock table t1 read local;
|
|
connection con1;
|
|
set global concurrent_insert=2;
|
|
# Insert in table with hole -> Should insert at end
|
|
insert into t1 (a) values (8),(9);
|
|
connection default;
|
|
unlock tables;
|
|
# Insert into hole
|
|
insert into t1 (a) values (10),(11),(12);
|
|
select a from t1;
|
|
check table t1;
|
|
drop table t1;
|
|
disconnect con1;
|
|
set global concurrent_insert=@save_concurrent_insert;
|
|
|
|
#
|
|
# ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX
|
|
#
|
|
|
|
create table t1 (a int, key(a));
|
|
|
|
insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL);
|
|
analyze table t1;
|
|
analyze table t1;
|
|
show keys from t1;
|
|
|
|
alter table t1 disable keys;
|
|
alter table t1 enable keys;
|
|
show keys from t1;
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Test temporary table with data directory option
|
|
#
|
|
connect (session1,localhost,root,,);
|
|
connect (session2,localhost,root,,);
|
|
|
|
connection session1;
|
|
disable_query_log;
|
|
disable_warnings;
|
|
eval create temporary table t1 (a int) data directory="$MYSQLTEST_VARDIR/tmp" select 9 a;
|
|
enable_warnings;
|
|
enable_query_log;
|
|
disable_result_log;
|
|
show create table t1;
|
|
enable_result_log;
|
|
|
|
connection session2;
|
|
disable_query_log;
|
|
|
|
disable_warnings;
|
|
eval create temporary table t1 (a int) data directory="$MYSQLTEST_VARDIR/tmp" select 99 a;
|
|
enable_warnings;
|
|
enable_query_log;
|
|
disable_result_log;
|
|
show create table t1;
|
|
enable_result_log;
|
|
|
|
connection default;
|
|
create table t1 (a int) select 42 a;
|
|
|
|
connection session1;
|
|
select * from t1;
|
|
disconnect session1;
|
|
connection session2;
|
|
select * from t1;
|
|
disconnect session2;
|
|
connection default;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
--echo End of 4.1 tests
|
|
|
|
#
|
|
# Test if PACK_KEYS option takes values greater than 1 while creating table
|
|
#
|
|
create table t1 (c1 int) pack_keys=0;
|
|
create table t2 (c1 int) pack_keys=1;
|
|
create table t3 (c1 int) pack_keys=default;
|
|
--error ER_PARSE_ERROR
|
|
create table t4 (c1 int) pack_keys=2;
|
|
drop table t1, t2, t3;
|
|
|
|
#
|
|
# Bug#28476: force index on a disabled aria index gives error 124
|
|
#
|
|
|
|
CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b));
|
|
INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
|
|
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
|
|
SELECT a FROM t1 USE INDEX (inx) WHERE a=1;
|
|
SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1;
|
|
SELECT b FROM t1 USE INDEX (uinx) WHERE b=1;
|
|
SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1;
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Bug#4692 - DISABLE/ENABLE KEYS waste a space
|
|
#
|
|
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2));
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
INSERT INTO t1 VALUES (1,1);
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 #
|
|
SHOW TABLE STATUS LIKE 't1';
|
|
#--exec ls -log var/mysqld.1/data/test/t1
|
|
#--exec aria_chk -dvv var/mysqld.1/data/test/t1
|
|
#--exec aria_chk -iev var/mysqld.1/data/test/t1
|
|
--echo # Enable keys with parallel repair
|
|
SET @@aria_repair_threads=2;
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
SET @@aria_repair_threads=1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Test doing delete with self-join
|
|
#
|
|
|
|
CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id));
|
|
CREATE TABLE t2 LIKE t1;
|
|
|
|
INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
|
|
SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref;
|
|
SELECT * FROM t1;
|
|
DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
#
|
|
# Bug#37310: 'on update CURRENT_TIMESTAMP' option crashes the table
|
|
#
|
|
CREATE TABLE t1 (a INT) ENGINE=ARIA CHECKSUM=1 ROW_FORMAT=DYNAMIC;
|
|
INSERT INTO t1 VALUES (0);
|
|
UPDATE t1 SET a=1;
|
|
SELECT a FROM t1;
|
|
CHECK TABLE t1;
|
|
INSERT INTO t1 VALUES (0), (5), (4), (2);
|
|
UPDATE t1 SET a=2;
|
|
SELECT a FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo End of 5.0 tests
|
|
|
|
|
|
#
|
|
# Test of key_block_size
|
|
#
|
|
|
|
create table t1 (a int not null, key `a` (a) key_block_size=1024);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, key `a` (a) key_block_size=2048);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a varchar(4096), key `a` (a));
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a varchar(4096), key `a` (a) key_block_size=1024);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=1024;
|
|
show create table t1;
|
|
alter table t1 key_block_size=2048;
|
|
show create table t1;
|
|
alter table t1 add c int, add key (c);
|
|
show create table t1;
|
|
alter table t1 key_block_size=4096;
|
|
show create table t1;
|
|
alter table t1 key_block_size=0;
|
|
show create table t1;
|
|
alter table t1 add d int, add key (d);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=8192;
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b varchar(4096), key (a) key_block_size=1024, key(b)) key_block_size=8192;
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384;
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
# Test limits and errors of key_block_size
|
|
|
|
create table t1 (a int not null, key `a` (a) key_block_size=512);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a varchar(4096), key `a` (a) key_block_size=1000000000000000000);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, key `a` (a) key_block_size=1025);
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int not null, key key_block_size=1024 (a));
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int not null, key `a` key_block_size=1024 (a));
|
|
|
|
#
|
|
# Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 VARCHAR(300),
|
|
KEY (c1) KEY_BLOCK_SIZE 1024,
|
|
KEY (c2) KEY_BLOCK_SIZE 8192
|
|
);
|
|
INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),
|
|
(11, REPEAT('b', CEIL(RAND() * 300))),
|
|
(12, REPEAT('c', CEIL(RAND() * 300))),
|
|
(13, REPEAT('d', CEIL(RAND() * 300))),
|
|
(14, REPEAT('e', CEIL(RAND() * 300))),
|
|
(15, REPEAT('f', CEIL(RAND() * 300))),
|
|
(16, REPEAT('g', CEIL(RAND() * 300))),
|
|
(17, REPEAT('h', CEIL(RAND() * 300))),
|
|
(18, REPEAT('i', CEIL(RAND() * 300))),
|
|
(19, REPEAT('j', CEIL(RAND() * 300))),
|
|
(20, REPEAT('k', CEIL(RAND() * 300))),
|
|
(21, REPEAT('l', CEIL(RAND() * 300))),
|
|
(22, REPEAT('m', CEIL(RAND() * 300))),
|
|
(23, REPEAT('n', CEIL(RAND() * 300))),
|
|
(24, REPEAT('o', CEIL(RAND() * 300))),
|
|
(25, REPEAT('p', CEIL(RAND() * 300))),
|
|
(26, REPEAT('q', CEIL(RAND() * 300))),
|
|
(27, REPEAT('r', CEIL(RAND() * 300))),
|
|
(28, REPEAT('s', CEIL(RAND() * 300))),
|
|
(29, REPEAT('t', CEIL(RAND() * 300))),
|
|
(30, REPEAT('u', CEIL(RAND() * 300))),
|
|
(31, REPEAT('v', CEIL(RAND() * 300))),
|
|
(32, REPEAT('w', CEIL(RAND() * 300))),
|
|
(33, REPEAT('x', CEIL(RAND() * 300))),
|
|
(34, REPEAT('y', CEIL(RAND() * 300))),
|
|
(35, REPEAT('z', CEIL(RAND() * 300)));
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
REPAIR TABLE t1;
|
|
DELETE FROM t1 WHERE c1 >= 10;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Bug#33222 - myisam-table drops rows when column is added
|
|
# and a char-field > 128 exists
|
|
#
|
|
# Test #1 - CHECK TABLE sees wrong record, REPAR TABLE deletes it.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(130),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria;
|
|
INSERT INTO t1 VALUES(REPEAT("a",128), 'b');
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
REPAIR TABLE t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #2 - same as test #1, but using EXTENDED.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(130),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria;
|
|
INSERT INTO t1 VALUES(REPEAT("a",128), 'b');
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
REPAIR TABLE t1 EXTENDED;
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #3 - same as test #1, but using OPTIMIZE TABLE.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(130),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria;
|
|
INSERT INTO t1 VALUES(REPEAT("a",128), 'b');
|
|
# Insert more rows and delete one in the middle to force optimize.
|
|
INSERT INTO t1 VALUES('b', 'b');
|
|
INSERT INTO t1 VALUES('c', 'b');
|
|
DELETE FROM t1 WHERE c1='b';
|
|
SELECT COUNT(*) FROM t1;
|
|
OPTIMIZE TABLE t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #4 - ALTER TABLE deletes rows.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
# Using an index which can be disabled during bulk insert.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(130),
|
|
c2 VARCHAR(1),
|
|
KEY (c1)
|
|
) ENGINE=aria;
|
|
#
|
|
# Insert 100 rows. This turns bulk insert on during the copy phase of
|
|
# ALTER TABLE. Bulk insert disables keys before the insert and re-enables
|
|
# them by repair after the insert.
|
|
--disable_query_log
|
|
let $count= 100;
|
|
--echo # Insert $count rows. Query log disabled.
|
|
while ($count)
|
|
{
|
|
INSERT INTO t1 VALUES ('a', 'b');
|
|
dec $count;
|
|
}
|
|
--enable_query_log
|
|
#
|
|
# Change most of the rows into long character values with > 127 characters.
|
|
UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90;
|
|
SELECT COUNT(*) FROM t1;
|
|
ALTER TABLE t1 ENGINE=aria;
|
|
#
|
|
# With bug present, this shows that all long rows are gone.
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #5 - same as test #1 but UTF-8.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(50),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria DEFAULT CHARSET UTF8;
|
|
# Using Tamil Letter A, Unicode U+0B85
|
|
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
REPAIR TABLE t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #6 - same as test #2, but UTF-8.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(50),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria DEFAULT CHARSET UTF8;
|
|
# Using Tamil Letter A, Unicode U+0B85
|
|
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
REPAIR TABLE t1 EXTENDED;
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #7 - same as test #3, but UTF-8.
|
|
# Using a CHAR column that can have > 127 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(50),
|
|
c2 VARCHAR(1)
|
|
) ENGINE=aria DEFAULT CHARSET UTF8;
|
|
# Using Tamil Letter A, Unicode U+0B85
|
|
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b');
|
|
# Insert more rows and delete one in the middle to force optimize.
|
|
INSERT INTO t1 VALUES('b', 'b');
|
|
INSERT INTO t1 VALUES('c', 'b');
|
|
DELETE FROM t1 WHERE c1='b';
|
|
SELECT COUNT(*) FROM t1;
|
|
OPTIMIZE TABLE t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test #8 - same as test #4, but UTF-8.
|
|
# Using a CHAR column that can have > 42 UTF-8 characters.
|
|
# Using a VARCHAR to create a table with dynamic row format.
|
|
# Using an index which can be disabled during bulk insert.
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(50),
|
|
c2 VARCHAR(1),
|
|
KEY (c1)
|
|
) ENGINE=aria DEFAULT CHARSET UTF8;
|
|
#
|
|
# Insert 100 rows. This turns bulk insert on during the copy phase of
|
|
# ALTER TABLE. Bulk insert disables keys before the insert and re-enables
|
|
# them by repair after the insert.
|
|
--disable_query_log
|
|
let $count= 100;
|
|
--echo # Insert $count rows. Query log disabled.
|
|
while ($count)
|
|
{
|
|
INSERT INTO t1 VALUES ('a', 'b');
|
|
dec $count;
|
|
}
|
|
--enable_query_log
|
|
#
|
|
# Change most of the rows into long character values with > 42 characters.
|
|
# Using Tamil Letter A, Unicode U+0B85
|
|
UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90;
|
|
SELECT COUNT(*) FROM t1;
|
|
ALTER TABLE t1 ENGINE=aria;
|
|
#
|
|
# With bug present, this shows that all long rows are gone.
|
|
SELECT COUNT(*) FROM t1;
|
|
CHECK TABLE t1;
|
|
CHECK TABLE t1 EXTENDED;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Bug#29182 - MyISAMCHK reports wrong character set
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 VARCHAR(10) NOT NULL,
|
|
c2 CHAR(10) DEFAULT NULL,
|
|
c3 VARCHAR(10) NOT NULL,
|
|
KEY (c1),
|
|
KEY (c2)
|
|
) ENGINE=aria DEFAULT CHARSET=utf8 PACK_KEYS=0;
|
|
let $MYSQLD_DATADIR= `select @@datadir`;
|
|
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
|
|
--exec $MARIA_CHK -d --ignore-control-file $MYSQLD_DATADIR/test/t1
|
|
DROP TABLE t1;
|
|
|
|
# Test warnings with transactional=1 with MyISAM
|
|
#
|
|
create table t1 (n int not null, c char(1)) transactional=1;
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field
|
|
# (same content / differen checksum)
|
|
#
|
|
|
|
CREATE TABLE t1 (line LINESTRING NOT NULL) engine=aria;
|
|
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(0 0)"));
|
|
checksum table t1;
|
|
CREATE TABLE t2 (line LINESTRING NOT NULL) engine=aria;
|
|
INSERT INTO t2 VALUES (GeomFromText("LINESTRING(0 0)"));
|
|
checksum table t2;
|
|
CREATE TABLE t3 select * from t1;
|
|
checksum table t3;
|
|
drop table t1,t2,t3;
|
|
--echo End of 5.1 tests
|
|
|
|
#
|
|
# from bug37276_reduced_corruption.sql
|
|
#
|
|
create table t2(a varchar(255),key(a))engine=aria row_format=dynamic transactional=0;
|
|
insert into t2 values (repeat('o',124)), (repeat('h',226)), (repeat('i',236)),
|
|
(repeat('l',234)), (repeat('b',13)), (repeat('g',236)), (repeat('y',205)),
|
|
(repeat('c',99)), (repeat('g',145)), (repeat('o',131)), (repeat('e',63)),
|
|
(repeat('q',155)), (repeat('k',87)), (repeat('i',54)), (repeat('p',84)),
|
|
(repeat('m',119)), (repeat('c',2)), (repeat('a',174)), (repeat('g',160)),
|
|
(repeat('t',147)), (repeat('n',107));
|
|
insert into t2 values ('nupdjlafwfvuuvruxkyjxpmupihzgspkaybijztkeukgzzkrxmd');
|
|
insert into t2 values (repeat('g',40)), (repeat('i',173)), (repeat('q',126)),
|
|
(repeat('i',217)), (repeat('f',161)), (repeat('i',28)), (repeat('a',35)),
|
|
(repeat('y',27)), (repeat('o',100)), (repeat('o',175)), (repeat('f',69)),
|
|
(repeat('k',156)), (repeat('n',220)), (repeat('q',247)), (repeat('y',180)),
|
|
(repeat('v',209)), (repeat('m',169)), (repeat('y',170)), (repeat('r',151)),
|
|
(repeat('d',38)), (repeat('g',64)), (repeat('k',77)), (repeat('l',150)),
|
|
(repeat('s',150)), (repeat('u',127)), (repeat('l',15)), (repeat('m',33)),
|
|
(repeat('r',177)), (repeat('v',197)), (repeat('k',62)), (repeat('h',219)),
|
|
(repeat('u',161)), (repeat('y',118)), (repeat('i',184)), (repeat('z',202)),
|
|
(repeat('j',113)), (repeat('q',95)), (repeat('q',164)), (repeat('e',54)),
|
|
(repeat('e',60)), (repeat('l',203)), (repeat('g',77)), (repeat('y',44)),
|
|
(repeat('j',196)), (repeat('t',45)), (repeat('l',190)), (repeat('l',89)),
|
|
(repeat('q',45)), (repeat('e',191)), (repeat('t',38)), (repeat('f',148)),
|
|
(repeat('c',25)), (repeat('v',97)), (repeat('i',83)), (repeat('s',166)),
|
|
(repeat('d',96)), (repeat('v',82)), (repeat('n',127)), (repeat('i',201)),
|
|
(repeat('x',184)), (repeat('d',76)), (repeat('u',17)), (repeat('a',178));
|
|
insert into t2 values ('hwvfiavnmufgbulapzrolonwxufheqymvjncnczlzcjokzqlsvmomcjzgzwzquyxpunxdmotdczocwliaprpubwaeccsulvittgizcutxxb');
|
|
insert into t2 values (repeat('x',28)), (repeat('p',21)), (repeat('k',241)),
|
|
(repeat('i',243)), (repeat('b',172)), (repeat('z',94)), (repeat('i',218)),
|
|
(repeat('a',177)), (repeat('g',251)), (repeat('q',161)), (repeat('x',231)),
|
|
(repeat('p',51)), (repeat('f',141)), (repeat('m',28)), (repeat('r',77)),
|
|
(repeat('h',56)), (repeat('k',23)), (repeat('f',198)), (repeat('o',243)),
|
|
(repeat('d',160)), (repeat('h',82));
|
|
|
|
check table t2 extended;
|
|
drop table t2;
|
|
|
|
CREATE TABLE t1 (
|
|
col0 float DEFAULT NULL,
|
|
col1 date DEFAULT NULL,
|
|
col2 double DEFAULT NULL,
|
|
col3 decimal(10,0) DEFAULT NULL,
|
|
col4 char(218) DEFAULT NULL,
|
|
col5 year(4) DEFAULT NULL,
|
|
col6 datetime DEFAULT NULL,
|
|
col7 varchar(39) DEFAULT NULL,
|
|
col8 double DEFAULT NULL,
|
|
col9 decimal(10,0) DEFAULT NULL,
|
|
col10 enum('test1','test2','test3') DEFAULT NULL,
|
|
col11 year(4) DEFAULT NULL,
|
|
col12 tinytext,
|
|
col13 tinyblob,
|
|
col14 date DEFAULT NULL,
|
|
col15 smallint(6) DEFAULT NULL,
|
|
col16 varchar(81) DEFAULT NULL,
|
|
col17 tinytext,
|
|
col18 blob,
|
|
col19 double DEFAULT NULL,
|
|
col20 double DEFAULT NULL,
|
|
col21 varchar(216) DEFAULT NULL,
|
|
col22 enum('test1','test2','test3') DEFAULT NULL,
|
|
col23 decimal(10,0) DEFAULT NULL,
|
|
col24 text,
|
|
col25 varchar(118) DEFAULT NULL,
|
|
col26 tinytext,
|
|
col27 tinyblob,
|
|
col28 double DEFAULT NULL,
|
|
col29 tinyint(4) DEFAULT NULL,
|
|
col30 longtext,
|
|
col31 tinyint(1) DEFAULT NULL,
|
|
col32 char(212) DEFAULT NULL,
|
|
col33 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
col34 year(4) DEFAULT NULL,
|
|
col35 tinyint(1) DEFAULT NULL,
|
|
col36 enum('test1','test2','test3') DEFAULT NULL,
|
|
col37 decimal(10,0) DEFAULT NULL,
|
|
col38 tinyint(4) DEFAULT NULL,
|
|
col39 double DEFAULT NULL,
|
|
col40 decimal(10,0) DEFAULT NULL,
|
|
col41 enum('test1','test2','test3') DEFAULT NULL,
|
|
col42 longblob,
|
|
col43 text,
|
|
col44 blob,
|
|
col45 year(4) DEFAULT NULL,
|
|
col46 longtext,
|
|
col47 int(11) DEFAULT NULL,
|
|
col48 set('test1','test2','test3') DEFAULT NULL,
|
|
col49 bigint(20) DEFAULT NULL,
|
|
col50 date DEFAULT NULL,
|
|
col51 tinyblob,
|
|
col52 float DEFAULT NULL,
|
|
col53 year(4) DEFAULT NULL,
|
|
col54 decimal(10,0) DEFAULT NULL,
|
|
col55 tinyblob,
|
|
col56 float DEFAULT NULL,
|
|
col57 bigint(20) DEFAULT NULL,
|
|
col58 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
col59 enum('test1','test2','test3') DEFAULT NULL,
|
|
col60 bigint(20) DEFAULT NULL,
|
|
col61 year(4) DEFAULT NULL,
|
|
col62 year(4) DEFAULT NULL,
|
|
col63 double DEFAULT NULL,
|
|
col64 tinytext,
|
|
col65 tinyint(4) DEFAULT NULL,
|
|
col66 longtext,
|
|
col67 time DEFAULT NULL,
|
|
col68 bigint(20) DEFAULT NULL,
|
|
col69 char(142) DEFAULT NULL,
|
|
col70 longtext,
|
|
col71 time DEFAULT NULL,
|
|
col72 year(4) DEFAULT NULL,
|
|
col73 longblob,
|
|
col74 enum('test1','test2','test3') DEFAULT NULL,
|
|
col75 decimal(10,0) DEFAULT NULL,
|
|
col76 smallint(6) DEFAULT NULL,
|
|
col77 tinytext,
|
|
col78 date DEFAULT NULL,
|
|
col79 double DEFAULT NULL,
|
|
col80 tinyint(4) DEFAULT NULL,
|
|
col81 float DEFAULT NULL,
|
|
col82 bigint(20) DEFAULT NULL,
|
|
col83 double DEFAULT NULL,
|
|
col84 varchar(124) DEFAULT NULL,
|
|
col85 double DEFAULT NULL,
|
|
col86 tinyblob,
|
|
col87 tinyblob,
|
|
col88 double DEFAULT NULL,
|
|
col89 date DEFAULT NULL,
|
|
col90 decimal(10,0) DEFAULT NULL,
|
|
col91 set('test1','test2','test3') DEFAULT NULL,
|
|
col92 blob,
|
|
col93 char(174) DEFAULT NULL,
|
|
col94 double DEFAULT NULL,
|
|
col95 tinytext,
|
|
col96 decimal(10,0) DEFAULT NULL,
|
|
col97 year(4) DEFAULT NULL,
|
|
col98 tinyblob,
|
|
col99 datetime DEFAULT NULL,
|
|
col100 longblob,
|
|
col101 date DEFAULT NULL,
|
|
col102 float DEFAULT NULL,
|
|
col103 float DEFAULT NULL,
|
|
col104 int(11) DEFAULT NULL,
|
|
col105 longblob,
|
|
col106 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
col107 float DEFAULT NULL,
|
|
col108 text,
|
|
col109 float DEFAULT NULL,
|
|
col110 decimal(10,0) DEFAULT NULL,
|
|
col111 double DEFAULT NULL,
|
|
col112 double DEFAULT NULL,
|
|
col113 blob,
|
|
col114 varchar(152) DEFAULT NULL,
|
|
col115 bigint(20) DEFAULT NULL,
|
|
col116 decimal(10,0) DEFAULT NULL,
|
|
col117 mediumint(9) DEFAULT NULL,
|
|
col118 tinytext,
|
|
col119 tinyblob,
|
|
col120 int(11) DEFAULT NULL,
|
|
col121 bigint(20) DEFAULT NULL,
|
|
col122 double DEFAULT NULL,
|
|
col123 date DEFAULT NULL,
|
|
col124 longtext,
|
|
col125 longtext,
|
|
col126 double DEFAULT NULL,
|
|
col127 varchar(84) DEFAULT NULL,
|
|
col128 text,
|
|
col129 double DEFAULT NULL,
|
|
col130 enum('test1','test2','test3') DEFAULT NULL,
|
|
col131 time DEFAULT NULL,
|
|
col132 year(4) DEFAULT NULL,
|
|
col133 blob,
|
|
col134 tinytext,
|
|
col135 double DEFAULT NULL,
|
|
col136 tinytext,
|
|
col137 bigint(20) DEFAULT NULL,
|
|
col138 datetime DEFAULT NULL,
|
|
col139 double DEFAULT NULL,
|
|
col140 decimal(10,0) DEFAULT NULL,
|
|
col141 longtext,
|
|
col142 tinyint(1) DEFAULT NULL,
|
|
col143 time DEFAULT NULL,
|
|
col144 time DEFAULT NULL,
|
|
col145 float DEFAULT NULL,
|
|
col146 longblob,
|
|
col147 float DEFAULT NULL,
|
|
col148 text,
|
|
col149 mediumint(9) DEFAULT NULL,
|
|
col150 tinyblob,
|
|
col151 tinyblob,
|
|
col152 tinytext,
|
|
col153 tinyblob,
|
|
col154 tinyblob,
|
|
col155 tinytext,
|
|
col156 tinyint(1) DEFAULT NULL,
|
|
col157 tinytext,
|
|
col158 time DEFAULT NULL,
|
|
col159 date DEFAULT NULL,
|
|
col160 longtext,
|
|
col161 enum('test1','test2','test3') DEFAULT NULL,
|
|
col162 text,
|
|
col163 decimal(10,0) DEFAULT NULL,
|
|
col164 time DEFAULT NULL,
|
|
col165 longblob,
|
|
col166 tinyint(4) DEFAULT NULL,
|
|
col167 bigint(20) DEFAULT NULL,
|
|
col168 decimal(10,0) DEFAULT NULL,
|
|
col169 smallint(6) DEFAULT NULL,
|
|
col170 tinytext,
|
|
col171 tinyint(4) DEFAULT NULL,
|
|
col172 tinyint(1) DEFAULT NULL,
|
|
col173 tinytext,
|
|
col174 decimal(10,0) DEFAULT NULL,
|
|
col175 double DEFAULT NULL
|
|
) engine=aria;
|
|
|
|
insert ignore into t1 set
|
|
col10=abs(28449) % 2,
|
|
col11='1973',
|
|
col12=if(abs(-30039)%100<20,null,'forgery\'s'),
|
|
col13=if(abs(24672)%100<20,null,'adductor\'s'),
|
|
col16=if(abs(26872)%100<20,null,'0xf810e016ee0b78e1ce8b1c6cf8d2e82bf8507453768a3908dc20cecfc9a0ac0ac00079d0645a4c'),
|
|
col18=if(abs(-15854)%100<20,null,'unattractiveness'),
|
|
col19=if(abs(4287439673.9896235000)%100<20,null,25288),
|
|
col20=if(abs(4290800136.7527390000)%100<20,null,8887),
|
|
col21=if(abs(-26086)%100<20,null,'0x2334181c6068aab18b348ecc1e2600b81e1c5f821eee3e204824'),
|
|
col22=abs(-21921) % 2,
|
|
col23=if(abs(1503277.6900540178)%100<20,null,-23298),
|
|
col25=if(abs(29674)%100<20,null,'0xc8d094f888ee20c83baef8d9380a168d40f4906e742a4dc0daeacb809e64095c71d510c7c0f83a6a0a04b8d6a0d9bea2dc3d4bd44d9c5002e440707c40ead8b3eb20a100a8524b1616a338a440ea02a25a08041810a08cac087cd47b4a79f08730946c5800600ae45e1c08f637'),
|
|
col26=if(abs(28642)%100<20,null,'insubstantiality\'s'),
|
|
col27=if(abs(-3188)%100<20,null,'exine\'s'),
|
|
col29=if(abs(-47)%100<20,null,-24131),
|
|
col32=if(abs(-3658)%100<20,null,'shortener\'s'),
|
|
col34='1917',
|
|
col36=abs(27782) % 2,
|
|
col37=if(abs(4864972.0244758446)%100<20,null,32302),
|
|
col38=if(abs(97)%100<20,null,-14079),
|
|
col39=if(abs(3362872.0521256141)%100<20,null,27191),
|
|
col40=if(abs(3348292.2110660113)%100<20,null,-1163),
|
|
col41=abs(-18533) % 2,
|
|
col42=if(abs(2094)%100<20,null,'Montparnasse'),
|
|
col43=if(abs(-15983)%100<20,null,'Massasoit\'s'),
|
|
col44=if(abs(2497)%100<20,null,'lags'),
|
|
col45='2057',
|
|
col46=if(abs(-31691)%100<20,null,'miscegenation\'s'),
|
|
col47=if(abs(-1269564297)%100<20,null,1089),
|
|
col49=if(abs(-1815717335)%100<20,null,-17504),
|
|
col51=if(abs(-15263)%100<20,null,'virelay'),
|
|
col52=if(abs(2227333.3279519030)%100<20,null,-5210),
|
|
col53='2032',
|
|
col54=if(abs(791647.5947447127)%100<20,null,32576),
|
|
col55=if(abs(20293)%100<20,null,'tumblebug'),
|
|
col56=if(abs(4288698564.2967925000)%100<20,null,17141),
|
|
col57=if(abs(-2138460927)%100<20,null,14495),
|
|
col59=abs(7624) % 2,
|
|
col60=if(abs(-1500892492)%100<20,null,-68),
|
|
col63=if(abs(4290890487.3789482000)%100<20,null,-32129),
|
|
col65=if(abs(22)%100<20,null,15722),
|
|
col79=if(abs(4292420489.2606282000)%100<20,null,-23891),
|
|
col84=if(abs(-21248)%100<20,null,'0x4f9888d044435050eab83cb3dcad88b01886e434e216'),
|
|
col85=if(abs(4294260188.6230965000)%100<20,null,16867),
|
|
col86=if(abs(-11659)%100<20,null,'prewar'),
|
|
col87=if(abs(-2253)%100<20,null,'Radnorshire'),
|
|
col90=if(abs(4287254529.4026613000)%100<20,null,23506),
|
|
col92=if(abs(6472)%100<20,null,'electroplated'),
|
|
col93=if(abs(-13523)%100<20,null,'sparkiest'),
|
|
col95=if(abs(23998)%100<20,null,'Crimea'),
|
|
col96=if(abs(4287719060.2789087000)%100<20,null,20527),
|
|
col98=if(abs(-14090)%100<20,null,'firebrat'),
|
|
col99='19161023095430',
|
|
col100=if(abs(-31178)%100<20,null,'clinical'),
|
|
col102=if(abs(4407547.5205542166)%100<20,null,18226),
|
|
col103=if(abs(4286834687.5994444000)%100<20,null,27520),
|
|
col104=if(abs(-2105663477)%100<20,null,28591),
|
|
col105=if(abs(1929)%100<20,null,'renascent'),
|
|
col107=if(abs(5972348.8099917602)%100<20,null,-11408),
|
|
col108=if(abs(-11262)%100<20,null,'aircraftmen'),
|
|
col110=if(abs(6530491.4546037167)%100<20,null,-17672),
|
|
col111=if(abs(4289897795.5000763000)%100<20,null,3742),
|
|
col112=if(abs(1680557.8560441907)%100<20,null,13944),
|
|
col113=if(abs(-27195)%100<20,null,'dekameter'),
|
|
col115=if(abs(-2083419827)%100<20,null,-17272),
|
|
col117=if(abs(1704826)%100<20,null,17880),
|
|
col118=if(abs(-2848)%100<20,null,'judicatory'),
|
|
col119=if(abs(-28087)%100<20,null,'mistitles'),
|
|
col120=if(abs(-2100119097)%100<20,null,22465),
|
|
col121=if(abs(-1868777891)%100<20,null,15172),
|
|
col122=if(abs(7039857.3608508557)%100<20,null,-22154),
|
|
col125=if(abs(70)%100<20,null,'Hong\'s'),
|
|
col126=if(abs(3820673.5968199712)%100<20,null,-24185),
|
|
col127=if(abs(12331)%100<20,null,'0x674e14584e88fca3fed0a0b1488a440008228aa01454a65cf09e3f0fa0511c3ce2f8688450'),
|
|
col128=if(abs(20335)%100<20,null,'Zoroaster\'s'),
|
|
col129=if(abs(3916577.6225165562)%100<20,null,-4088),
|
|
col130=abs(-15003) % 2,
|
|
col132='2016',
|
|
col134=if(abs(-26555)%100<20,null,'Caesarea'),
|
|
col135=if(abs(4288484655.2416148000)%100<20,null,-30073),
|
|
col136=if(abs(-17577)%100<20,null,'upbraid'),
|
|
col137=if(abs(-1742797945)%100<20,null,-21651),
|
|
col138='20751113181230',
|
|
col139=if(abs(4288997063.9889216000)%100<20,null,1816),
|
|
col141=if(abs(-31448)%100<20,null,'threnodist'),
|
|
col142=if(abs(88)%100<20,null,-19748),
|
|
col143='6930607',
|
|
col144='5760250',
|
|
col145=if(abs(3591496.9625843074)%100<20,null,76),
|
|
col146=if(abs(20875)%100<20,null,'rename'),
|
|
col147=if(abs(4294789439.6773582000)%100<20,null,32314),
|
|
col148=if(abs(7072)%100<20,null,'recesses'),
|
|
col150=if(abs(-26540)%100<20,null,'cuckoo'),
|
|
col152=if(abs(23553)%100<20,null,'shortened'),
|
|
col153=if(abs(-30422)%100<20,null,'inhabitant'),
|
|
col154=if(abs(30457)%100<20,null,'Orwellian'),
|
|
col155=if(abs(-30263)%100<20,null,'Ptolemies'),
|
|
col156=if(abs(-41)%100<20,null,6382),
|
|
col157=if(abs(2557)%100<20,null,'horsewhips'),
|
|
col158='2764427',
|
|
col160=if(abs(-15872)%100<20,null,'girlhood'),
|
|
col161=abs(15378) % 2,
|
|
col163=if(abs(4286662730.9309368000)%100<20,null,-19516),
|
|
col167=if(abs(-1881918655)%100<20,null,6927),
|
|
col169=if(abs(-14442)%100<20,null,-6392),
|
|
col170=if(abs(29965)%100<20,null,'resynthesis'),
|
|
col173=if(abs(-451)%100<20,null,'Clute'),
|
|
col174=if(abs(3262594.6284981840)%100<20,null,17846);
|
|
|
|
update ignore t1 set col165=repeat('a',7000);
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug#38466 aria: range query returns no data
|
|
#
|
|
|
|
create table t1 (a char(200) primary key, b int default 12345) engine=aria;
|
|
insert t1 (a) values (repeat('0', 200));
|
|
insert t1 (a) values (repeat('1', 200)), (repeat('2', 200)), (repeat('3', 200)),
|
|
(repeat('4', 200)), (repeat('5', 200)), (repeat('6', 200)), (repeat('7', 200)),
|
|
(repeat('8', 200)), (repeat('9', 200)), (repeat('a', 200)), (repeat('b', 200)),
|
|
(repeat('c', 200)), (repeat('d', 200)), (repeat('e', 200)), (repeat('f', 200)),
|
|
(repeat('g', 200)), (repeat('h', 200)), (repeat('i', 200)), (repeat('j', 200)),
|
|
(repeat('k', 200)), (repeat('l', 200)), (repeat('m', 200)), (repeat('n', 200)),
|
|
(repeat('o', 200)), (repeat('p', 200)), (repeat('q', 200)), (repeat('r', 200)),
|
|
(repeat('s', 200)), (repeat('t', 200)), (repeat('u', 200)), (repeat('v', 200)),
|
|
(repeat('w', 200)), (repeat('x', 200)), (repeat('y', 200)), (repeat('z', 200)),
|
|
(repeat('+', 200)), (repeat('-', 200)), (repeat('=', 200)), (repeat('*', 200));
|
|
select b from t1 where a >= repeat('f', 200) and a < 'k';
|
|
drop table t1;
|
|
|
|
#
|
|
# BUG#38606 test suite
|
|
#
|
|
create table t1 (a int) engine=aria transactional=1;
|
|
insert into t1 values (1);
|
|
lock table t1 write concurrent;
|
|
# should be fixed with fully implemented versioning
|
|
--error ER_CHECK_NOT_IMPLEMENTED
|
|
delete from t1 where a>0;
|
|
--error ER_CHECK_NOT_IMPLEMENTED
|
|
delete from t1;
|
|
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
|
|
truncate t1;
|
|
unlock tables;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug#39243 SELECT WHERE does not find row
|
|
# (Problem with skip_row)
|
|
#
|
|
|
|
create table t1 (p int primary key, i int, a char(10), key k1(i), key k2(a))
|
|
engine aria;
|
|
insert into t1 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
|
|
(3, 1, 'yyyy'), (4, 3, 'zzzz');
|
|
insert into t1 values (5, 3, 'yyyy'), (6, 3, 'yyyy'), (7, 0, NULL),
|
|
(8, 0, NULL);
|
|
select * from t1 where a='zzzz';
|
|
select * from t1 where a='yyyy';
|
|
select * from t1 where a is NULL;
|
|
select * from t1;
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug39248 INSERT ON DUPLICATE KEY UPDATE gives error if using a view
|
|
# Note that this only crashes when using
|
|
# --mysqld=--binlog-format=row --ps-protocol
|
|
#
|
|
|
|
create table t1 (f1 int unique, f2 int) engine=aria;
|
|
create table t2 (f3 int, f4 int) engine=aria;
|
|
create view v1 as select * from t1, t2 where f1= f3;
|
|
insert into t1 values (1,11), (2,22);
|
|
--error 1393
|
|
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
|
|
--error 1393
|
|
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
|
|
drop table t1,t2;
|
|
drop view v1;
|
|
|
|
#
|
|
# BUG#39399 ALTER TABLE renaming column: affected_rows > 0
|
|
#
|
|
|
|
CREATE TABLE t1 (id int, c varchar(10)) engine=aria;
|
|
INSERT INTO t1 VALUES (1,"1");
|
|
--enable_info
|
|
ALTER TABLE t1 CHANGE c d varchar(10);
|
|
--disable_info
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug #39227 Aria: crash with ALTER TABLE PARTITION
|
|
#
|
|
|
|
create table t1 (s1 int);
|
|
insert into t1 values (1);
|
|
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
|
|
alter table t1 partition by list (s1) (partition p1 values in (2));
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug #40311
|
|
# Crash when aborting inserting of row with 2 blobs where first is short
|
|
#
|
|
|
|
create table t1(a int primary key, b blob, c blob) engine=aria;
|
|
set sql_mode='';
|
|
insert into t1 values(1,repeat('a',100), repeat('b',657860));
|
|
--error ER_DUP_ENTRY
|
|
insert into t1 values(1,repeat('a',100), repeat('b',657860));
|
|
set sql_mode=default;
|
|
check table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Check we can create a table with the 'Aria' alias
|
|
#
|
|
|
|
Create table t1 (a int) engine="aria";
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of LP#614265. This happens when we where using quick_range_select in
|
|
# delete
|
|
#
|
|
|
|
CREATE TABLE t1 ( f1 DOUBLE , f2 DOUBLE , f3 DOUBLE , f4 DOUBLE , v3 DOUBLE , v4 DOUBLE , KEY ( v3 ) , KEY ( v4 ) ) engine=maria;
|
|
REPLACE t1 ( f2 , f1 ) VALUES ( f2 , 56 ) ;
|
|
INSERT t1 ( f1 , f2 , f3 , f4 ) VALUES ( 0 , f2 , 8 , f3 ) ;
|
|
INSERT t1 ( f4 , f2 ) VALUES ( 4 , 92 ) ;
|
|
DELETE FROM t1 WHERE v3 = 173 OR v4 = 9 ;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test for LP#61465 Memory corruption with (M)aria storage engine and
|
|
# virtual columns
|
|
#
|
|
|
|
CREATE TABLE t1 (
|
|
f1 CHAR(255) BINARY ,
|
|
f2 CHAR(255) BINARY NOT NULL DEFAULT '0',
|
|
f3 CHAR(255) BINARY NOT NULL ,
|
|
f4 CHAR(255) BINARY NOT NULL DEFAULT '0' ,
|
|
v3 CHAR(255) BINARY AS ( ( f1 NOT LIKE '%' ) ) PERSISTENT,
|
|
KEY (v3)
|
|
) ENGINE=Maria;
|
|
INSERT INTO t1 ( f1 , f2 , f3 , f4 ) SELECT f1 , f4 , f1 , f4 FROM t1;
|
|
DELETE FROM t1;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test of problem where REPAIR finds old deleted rows.
|
|
#
|
|
|
|
create table t1 (a int not null primary key, b blob) engine=maria transactional=1;
|
|
insert into t1 values(1,repeat('a',8000));
|
|
insert into t1 values(2,repeat('b',8000));
|
|
insert into t1 values(3,repeat('c',8000));
|
|
flush tables;
|
|
delete from t1 where a>1;
|
|
--error 1062
|
|
insert into t1 values(1,repeat('d',8000*3));
|
|
flush tables;
|
|
check table t1;
|
|
# This failed by finding 2 extra rows.
|
|
repair table t1 extended;
|
|
drop table t1;
|
|
|
|
#
|
|
# Test problem with disable/enable keys
|
|
#
|
|
|
|
create table t1 (a int, b int, key (a), key(b));
|
|
alter table t1 disable keys;
|
|
insert 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);
|
|
alter table t1 enable keys;
|
|
select count(*) from t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b int, primary key (a), key(b));
|
|
alter table t1 disable keys;
|
|
insert 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);
|
|
alter table t1 enable keys;
|
|
select count(*) from t1;
|
|
drop table t1;
|
|
|
|
create table t1 (a int not null, b int, primary key (a), key(b));
|
|
lock tables t1 write;
|
|
alter table t1 disable keys;
|
|
insert 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);
|
|
alter table t1 enable keys;
|
|
unlock tables;
|
|
select count(*) from t1;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-14690: Assertion `page_link == &fake_link' failed in
|
|
--echo # pagecache_write_part
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a CHAR(8), b CHAR(8), c CHAR(8) NOT NULL DEFAULT '', f FLOAT, KEY(f)) ENGINE=Aria;
|
|
INSERT INTO t1 (a) VALUES ('foo');
|
|
DELETE FROM t1 WHERE c < 'bar';
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
INSERT INTO t1 (b) VALUES ('');
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
|
|
# Cleanup
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Start of 5.5 tests
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
id TINYINT NOT NULL AUTO_INCREMENT,
|
|
name CHAR(30) NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=ARIA;
|
|
SHOW CREATE TABLE t1;
|
|
INSERT INTO t1 (name) VALUES ('dog');
|
|
UPDATE t1 SET id=-1 WHERE id=1;
|
|
INSERT INTO t1 (name) VALUES ('cat');
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# MDEV-16682
|
|
# Assertion `(buff[7] & 7) == HEAD_PAGE' failed.
|
|
#
|
|
|
|
CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria;
|
|
INSERT INTO t1 VALUES (1,2), (2,3),(3,4);
|
|
DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk);
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # End of 5.5 tests
|
|
--echo #
|
|
|
|
#
|
|
# Check FLUSH FOR EXPORT
|
|
#
|
|
|
|
CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b));
|
|
INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'),
|
|
(6,'0'),(7,'0');
|
|
flush tables test.t1 for export;
|
|
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
|
|
insert into t1 values (8,'0');
|
|
unlock tables;
|
|
drop table t1;
|
|
|
|
#
|
|
# End of test
|
|
#
|
|
# Set defaults back
|
|
--disable_result_log
|
|
--disable_query_log
|
|
eval set global default_storage_engine=$default_engine,
|
|
aria_page_checksum=$default_checksum,
|
|
aria_log_file_size=$default_log_file_size;
|
|
--enable_result_log
|
|
--enable_query_log
|
|
|
|
--echo #
|
|
--echo # MDEV-22284 Aria table key read crash because wrong index used
|
|
--echo #
|
|
create table t1 (
|
|
a int auto_increment,
|
|
b int, c int,
|
|
key(c, a), unique(b)
|
|
) engine aria
|
|
partition by hash (b);
|
|
replace into t1 values (1, 0, 0), (2, 0, 0), (0, 0, 0);
|
|
# cleanup
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-28269 Assertion `save_errno' in maria_write or ER_GET_ERRNO
|
|
--echo #
|
|
|
|
connect (session1,localhost,root,,);
|
|
SET NAMES 'sjis';
|
|
set statement tmp_memory_table_size=0 for
|
|
SELECT 'למטה' AS a UNION SELECT 'Wetter' AS a;
|
|
connection default;
|
|
disconnect session1;
|
|
|
|
--source include/test_db_charset_restore.inc
|