2
# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
4
# This library is free software; you can redistribute it and/or
5
# modify it under the terms of the GNU Library General Public
6
# License as published by the Free Software Foundation; either
7
# version 2 of the License, or (at your option) any later version.
9
# This library is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12
# Library General Public License for more details.
14
# You should have received a copy of the GNU Library General Public
15
# License along with this library; if not, write to the Free
16
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
19
# Written by Monty for the TCX/Monty Program/Detron benchmark suite.
20
# Empress and PostgreSQL patches by Luuk de Boer
21
# Extensions for ANSI SQL and Mimer by Bengt Gunne
22
# Some additions and corrections by Matthias Urlich
24
# This programs tries to find all limits for a sql server
25
# It gets the name from what it does to most servers :)
27
# Be sure to use --help before running this!
29
# If you want to add support for another server, add a new package for the
30
# server in server-cfg. You only have to support the 'new' and 'version'
31
# functions. new doesn't need to have any limits if one doesn't want to
36
# CMT includes types and functions which are synonyms for other types
37
# and functions, including those in SQL9x. It should label those synonyms
38
# as such, and clarify ones such as "mediumint" with comments such as
39
# "3-byte int" or "same as xxx".
46
chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
47
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
49
$opt_server="mysql"; $opt_host="localhost"; $opt_database="test";
51
$opt_debug=$opt_help=$opt_Information=$opt_restart=$opt_force=$opt_quick=0;
52
$opt_log_all_queries=$opt_fix_limit_file=$opt_batch_mode=0;
53
$opt_db_start_cmd=""; # the db server start command
54
$opt_sleep=10; # time to sleep while starting the db server
55
$limit_changed=0; # For configure file
57
$opt_comment=$opt_config_file=$opt_log_queries_to_file="";
58
$limits{'crash_me_safe'}='yes';
59
$prompts{'crash_me_safe'}='crash me safe';
60
$limits{'operating_system'}= machine();
61
$prompts{'operating_system'}='crash-me tested on';
64
GetOptions("Information","help","server=s","debug","user=s","password=s","database=s","restart","force","quick","log-all-queries","comment=s","host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","batch-mode","config-file=s","log-queries-to-file=s") || usage();
65
usage() if ($opt_help || $opt_Information);
67
$opt_config_file="$pwd/$opt_dir/$opt_server.cfg" if (length($opt_config_file) == 0);
69
if ($opt_fix_limit_file)
71
print "Fixing limit file for $opt_server\n";
74
save_all_config_data();
78
$server=get_server($opt_server,$opt_host,$opt_database);
79
$opt_server=$server->{'cmp_name'};
83
print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
84
print "I hope you didn't have anything important running on this server....\n";
86
if ($limit_changed) # Must have been restarted
88
save_config_data('crash_me_safe','no',"crash me safe");
91
if (!$opt_force && !$opt_batch_mode)
97
print "Using --force. I assume you know what you are doing...\n";
101
save_config_data('crash_me_version',$version,"crash me version");
104
save_config_data('server_version',$server_version,"server version");
106
if (length($opt_comment))
108
save_config_data('user_comment',$opt_comment,"comment");
112
if (length($opt_log_queries_to_file))
114
open(LOG,">$opt_log_queries_to_file") || die "Can't open file $opt_log_queries_to_file\n";
119
# Set up some limits that's regared as unlimited
120
# We don't want to take up all resources from the server...
123
$max_connections="+1000"; # Number of simultaneous connections
124
$max_buffer_size="+16000000"; # size of communication buffer.
125
$max_string_size="+8000000"; # Enough for this test
126
$max_name_length="+512"; # Actually 256, but ...
127
$max_keys="+64"; # Probably too big.
128
$max_join_tables="+64"; # Probably too big.
129
$max_columns="+8192"; # Probably too big.
130
$max_row_length=$max_string_size;
131
$max_key_length="+8192"; # Big enough
132
$max_order_by="+64"; # Big enough
133
$max_expressions="+10000";
134
$max_big_expressions="+100";
135
$max_stacked_expressions="+2000";
136
$query_size=$max_buffer_size;
137
$longreadlen=16000000; # For retrieval buffer
140
# First do some checks that needed for the rest of the benchmark
142
use sigtrap; # Must be removed with perl5.005_2 on Win98
143
$SIG{PIPE} = 'IGNORE';
144
$SIG{SEGV} = sub {warn('SEGFAULT')};
146
$dbh->do("drop table crash_me"); # Remove old run
147
$dbh->do("drop table crash_me2"); # Remove old run
148
$dbh->do("drop table crash_me3"); # Remove old run
149
$dbh->do("drop table crash_q"); # Remove old run
150
$dbh->do("drop table crash_q1"); # Remove old run
152
$prompt="Tables without primary key";
153
if (!safe_query(["create table crash_me (a integer not null,b char(10) not null)",
154
"insert into crash_me (a,b) values (1,'a')"]))
156
if (!safe_query(["create table crash_me (a integer not null,b char(10) not null, primary key (a))",
157
"insert into crash_me (a,b) values (1,'a')"]))
159
die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
161
save_config_data('no_primary_key',"no",$prompt);
165
save_config_data('no_primary_key',"yes",$prompt);
168
# Define strings for character NULL and numeric NULL used in expressions
170
$char_null=$server->{'char_null'};
171
$numeric_null=$server->{'numeric_null'};
172
if ($char_null eq '')
176
if ($numeric_null eq '')
178
$numeric_null="NULL";
181
print "$prompt: $limits{'no_primary_key'}\n";
183
report("SELECT without FROM",'select_without_from',"select 1");
184
if ($limits{'select_without_from'} ne "yes")
186
$end_query=" from crash_me";
187
$check_connect="select a from crash_me";
192
$check_connect="select 1";
195
assert($check_connect);
196
assert("select a from crash_me where b<'b'");
198
report("Select constants",'select_constants',"select 1 $end_query");
199
report("Select table_name.*",'table_wildcard',
200
"select crash_me.* from crash_me");
201
report("Allows \' and \" as string markers",'quote_with_"',
202
'select a from crash_me where b<"c"');
203
check_and_report("Double '' as ' in strings",'double_quotes',[],
204
"select 'Walker''s' $end_query",[],"Walker's",1);
205
check_and_report("Multiple line strings","multi_strings",[],
206
"select a from crash_me where b < 'a'\n'b'",[],"1",0);
207
check_and_report("\" as identifier quote (ANSI SQL)",'quote_ident_with_"',[],
208
'select "A" from crash_me',[],"1",0);
209
check_and_report("\` as identifier quote",'quote_ident_with_`',[],
210
'select `A` from crash_me',[],"1",0);
211
check_and_report("[] as identifier quote",'quote_ident_with_[',[],
212
'select [A] from crash_me',[],"1",0);
214
report("Column alias","column_alias","select a as ab from crash_me");
215
report("Table alias","table_alias","select b.a from crash_me as b");
216
report("Functions",'functions',"select 1+1 $end_query");
217
report("Group functions",'group_functions',"select count(*) from crash_me");
218
report("Group functions with distinct",'group_distinct_functions',
219
"select count(distinct a) from crash_me");
220
report("Group by",'group_by',"select a from crash_me group by a");
221
report("Group by position",'group_by_position',
222
"select a from crash_me group by 1");
223
report("Group by alias",'group_by_alias',
224
"select a as ab from crash_me group by ab");
225
report("Order by",'order_by',"select a from crash_me order by a");
226
report("Order by position",'order_by_position',
227
"select a from crash_me order by 1");
228
report("Order by function","order_by_function",
229
"select a from crash_me order by a+1");
230
check_and_report("Order by DESC is remembered",'order_by_remember_desc',
231
["create table crash_q (s int,s1 int)",
232
"insert into crash_q values(1,1)",
233
"insert into crash_q values(3,1)",
234
"insert into crash_q values(2,1)"],
235
"select s,s1 from crash_q order by s1 DESC,s",
236
["drop table crash_q"],[3,2,1],7,undef(),3);
237
report("Compute",'compute',
238
"select a from crash_me order by a compute sum(a) by a");
239
report("Value lists in INSERT",'multi_value_insert',
240
"create table crash_q (s char(10))",
241
"insert into crash_q values ('a'),('b')",
242
"drop table crash_q");
243
report("INSERT with set syntax",'insert_with_set',
244
"create table crash_q (a integer)",
245
"insert into crash_q SET a=1",
246
"drop table crash_q");
247
report("allows end ';'","end_colon", "select * from crash_me;");
248
try_and_report("LIMIT number of rows","select_limit",
250
"select * from crash_me limit 1"],
252
"select TOP 1 * from crash_me"]);
253
report("SELECT with LIMIT #,#","select_limit2", "select * from crash_me limit 1,1");
255
# The following alter table commands MUST be kept together!
256
if ($dbh->do("create table crash_q (a integer, b integer,c CHAR(10))"))
258
report("Alter table add column",'alter_add_col',
259
"alter table crash_q add d integer");
260
report_one("Alter table add many columns",'alter_add_multi_col',
261
[["alter table crash_q add (f integer,g integer)","yes"],
262
["alter table crash_q add f integer, add g integer","with add"],
263
["alter table crash_q add f integer,g integer","without add"]] );
264
report("Alter table change column",'alter_change_col',
265
"alter table crash_q change a e char(50)");
267
# informix can only change data type with modify
268
report_one("Alter table modify column",'alter_modify_col',
269
[["alter table crash_q modify c CHAR(20)","yes"],
270
["alter table crash_q alter c CHAR(20)","with alter"]]);
271
report("Alter table alter column default",'alter_alter_col',
272
"alter table crash_q alter b set default 10",
273
"alter table crash_q alter b set default NULL");
274
report("Alter table drop column",'alter_drop_col',
275
"alter table crash_q drop column b");
276
report("Alter table rename table",'alter_rename_table',
277
"alter table crash_q rename to crash_q1");
279
# Make sure both tables will be dropped, even if rename fails.
280
$dbh->do("drop table crash_q1");
281
$dbh->do("drop table crash_q");
283
if ($dbh->do("create table crash_q (a integer, b integer,c CHAR(10))") &&
284
$dbh->do("create table crash_q1 (a integer, b integer,c CHAR(10) not null)"))
286
report("Alter table add constraint",'alter_add_constraint',
287
"alter table crash_q add constraint c1 check(a > b)");
288
report("Alter table drop constraint",'alter_drop_constraint',
289
"alter table crash_q drop constraint c1");
290
report("Alter table add unique",'alter_add_unique',
291
"alter table crash_q add constraint u1 unique(c)");
292
try_and_report("Alter table drop unique",'alter_drop_unique',
294
"alter table crash_q drop constraint u1"],
296
"alter table crash_q drop key c"]);
297
try_and_report("Alter table add primary key",'alter_add_primary_key',
299
"alter table crash_q1 add constraint p1 primary key(c)"],
300
["with add primary key",
301
"alter table crash_q1 add primary key(c)"]);
302
report("Alter table add foreign key",'alter_add_foreign_key',
303
"alter table crash_q add constraint f1 foreign key(c) references crash_q1(c)");
304
try_and_report("Alter table drop foreign key",'alter_drop_foreign_key',
305
["with drop constraint",
306
"alter table crash_q drop constraint f1"],
307
["with drop foreign key",
308
"alter table crash_q drop foreign key f1"]);
309
try_and_report("Alter table drop primary key",'alter_drop_primary_key',
311
"alter table crash_q1 drop constraint p1 restrict"],
313
"alter table crash_q1 drop primary key"]);
315
$dbh->do("drop table crash_q");
316
$dbh->do("drop table crash_q1");
318
check_and_report("case insensitive compare","case_insensitive_strings",
319
[],"select b from crash_me where b = 'A'",[],'a',1);
320
check_and_report("ignore end space in compare","ignore_end_space",
321
[],"select b from crash_me where b = 'a '",[],'a',1);
322
check_and_report("group on column with null values",'group_by_null',
323
["create table crash_q (s char(10))",
324
"insert into crash_q values(null)",
325
"insert into crash_q values(null)"],
326
"select count(*) from crash_q group by s",
327
["drop table crash_q"],2,0);
330
if (!defined($limits{'having'}))
331
{ # Complicated because of postgreSQL
332
if (!safe_query_result("select a from crash_me group by a having a > 0",1,0))
334
if (!safe_query_result("select a from crash_me group by a having a < 0",
336
{ save_config_data("having","error",$prompt); }
338
{ save_config_data("having","yes",$prompt); }
341
{ save_config_data("having","no",$prompt); }
343
print "$prompt: $limits{'having'}\n";
345
if ($limits{'having'} eq 'yes')
347
report("Having with group function","having_with_group",
348
"select a from crash_me group by a having count(*) = 1");
351
if ($limits{'column_alias'} eq 'yes')
353
report("Order by alias",'order_by_alias',
354
"select a as ab from crash_me order by ab");
355
if ($limits{'having'} eq 'yes')
357
report("Having on alias","having_with_alias",
358
"select a as ab from crash_me group by a having ab > 0");
361
report("binary numbers (0b1001)","binary_numbers","select 0b1001 $end_query");
362
report("hex numbers (0x41)","hex_numbers","select 0x41 $end_query");
363
report("binary strings (b'0110')","binary_strings","select b'0110' $end_query");
364
report("hex strings (x'1ace')","hex_strings","select x'1ace' $end_query");
366
report_result("Value of logical operation (1=1)","logical_value",
367
"select (1=1) $end_query");
369
$logical_value= $limits{'logical_value'};
373
if ($res=safe_query("select (1=1)=true $end_query")) {
377
save_config_data('has_true_false',$result,"TRUE and FALSE");
380
# Check how many connections the server can handle:
381
# We can't test unlimited connections, because this may take down the
385
$prompt="Simultaneous connections (installation default)";
387
if (defined($limits{'connections'}))
389
print "$limits{'connections'}\n";
395
for ($i=1; $i < $max_connections ; $i++)
397
if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
398
{ PrintError => 0})))
400
print "Last connect error: $DBI::errstr\n" if ($opt_debug);
403
$dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
404
print "." if ($opt_debug);
408
save_config_data('connections',$i,$prompt);
409
foreach $dbh (@connect)
411
print "#" if ($opt_debug);
412
$dbh->disconnect || warn $dbh->errstr; # close connection
415
$#connect=-1; # Free connections
419
print "Can't connect to server: $DBI::errstr. Please start it and try again\n";
427
# Check size of communication buffer, strings...
430
$prompt="query size";
432
if (!defined($limits{'query_size'}))
436
$end=$max_buffer_size;
437
$select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';
439
assert($query . "$select$end_query");
441
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
443
if ($limits{'restart'}{'tohigh'})
445
$end = $limits{'restart'}{'tohigh'} - 1;
446
print "\nRestarting this with low limit: $first and high limit: $end\n";
447
delete $limits{'restart'};
448
$first=$first+int(($end-$first+4)/5); # Prefere lower on errors
450
for ($i=$first ; $i < $end ; $i*=2)
452
last if (!safe_query($query . (" " x ($i - length($query)-length($end_query) -1)) . "$select$end_query"));
454
save_config_data("restart",$i,"") if ($opt_restart);
458
if ($i < $max_buffer_size)
460
while ($first != $end)
462
$i=int(($first+$end+1)/2);
463
if (safe_query($query .
464
(" " x ($i - length($query)-length($end_query) -1)) .
465
"$select$end_query"))
475
save_config_data('query_size',$end,$prompt);
477
$query_size=$limits{'query_size'};
479
print "$limits{'query_size'}\n";
481
# Test database types
484
@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
487
"integer","int","smallint",
488
"numeric(9,2)","decimal(6,2)","dec(6,2)",
489
"bit", "bit(2)","bit varying(2)","float","float(8)","real",
490
"double precision", "date","time","timestamp",
491
"interval year", "interval year to month",
493
"interval day", "interval day to hour", "interval day to minute",
494
"interval day to second",
495
"interval hour", "interval hour to minute", "interval hour to second",
496
"interval minute", "interval minute to second",
498
"national character varying(20)",
499
"national character(20)","nchar(1)",
500
"national char varying(20)","nchar varying(20)",
501
"national character varying(20)",
502
"timestamp with time zone");
503
@odbc_types=("binary(1)","varbinary(1)","tinyint","bigint",
505
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
507
"long varchar(1)", "varchar2(257)",
508
"mediumint","middleint","int unsigned",
509
"int1","int2","int3","int4","int8","uint",
510
"money","smallmoney","float4","float8","smallfloat",
511
"float(6,2)","double",
512
"enum('red')","set('red')", "int(5) zerofill", "serial",
513
"char(10) binary","int not null auto_increment,unique(q)",
514
"abstime","year","datetime","smalldatetime","timespan","reltime",
516
"int not null identity,unique(q)",
518
"box","bool","circle","polygon","point","line","lseg","path",
519
"interval", "serial", "inet", "cidr", "macaddr",
522
"varchar2(16)","nvarchar2(16)","number(9,2)","number(9)",
523
"number", "long","raw(16)","long raw","rowid","mlslabel","clob",
527
@types=(["sql",\@sql_types],
528
["odbc",\@odbc_types],
529
["extra",\@extra_types]);
531
foreach $types (@types)
533
print "\nSupported $types->[0] types\n";
535
foreach $use_type (@$tmp)
538
$type =~ s/\(.*\)/(1 arg)/;
539
if (index($use_type,",")>= 0)
541
$type =~ s/\(1 arg\)/(2 arg)/;
543
if (($tmp2=index($type,",unique")) >= 0)
545
$type=substr($type,0,$tmp2);
549
$tmp2 =~ s/_not_null//g;
550
report("Type $type","type_$types->[0]_$tmp2",
551
"create table crash_q (q $use_type)",
552
"drop table crash_q");
557
# Test some type limits
560
check_and_report("Remembers end space in char()","remember_end_space",
561
["create table crash_q (a char(10))",
562
"insert into crash_q values('hello ')"],
563
"select a from crash_q where a = 'hello '",
564
["drop table crash_q"],
567
check_and_report("Remembers end space in varchar()",
568
"remember_end_space_varchar",
569
["create table crash_q (a varchar(10))",
570
"insert into crash_q values('hello ')"],
571
"select a from crash_q where a = 'hello '",
572
["drop table crash_q"],
575
check_and_report("Supports 0000-00-00 dates","date_zero",
576
["create table crash_me2 (a date not null)",
577
"insert into crash_me2 values ('0000-00-00')"],
578
"select a from crash_me2",
579
["drop table crash_me2"],
582
check_and_report("Supports 0001-01-01 dates","date_one",
583
["create table crash_me2 (a date not null)",
584
"insert into crash_me2 values (DATE '0001-01-01')"],
585
"select a from crash_me2",
586
["drop table crash_me2"],
589
check_and_report("Supports 9999-12-31 dates","date_last",
590
["create table crash_me2 (a date not null)",
591
"insert into crash_me2 values (DATE '9999-12-31')"],
592
"select a from crash_me2",
593
["drop table crash_me2"],
596
check_and_report("Supports 'infinity dates","date_infinity",
597
["create table crash_me2 (a date not null)",
598
"insert into crash_me2 values ('infinity')"],
599
"select a from crash_me2",
600
["drop table crash_me2"],
603
if (!defined($limits{'date_with_YY'}))
605
check_and_report("Supports YY-MM-DD dates","date_with_YY",
606
["create table crash_me2 (a date not null)",
607
"insert into crash_me2 values ('98-03-03')"],
608
"select a from crash_me2",
609
["drop table crash_me2"],
611
if ($limits{'date_with_YY'} eq "yes")
613
undef($limits{'date_with_YY'});
614
check_and_report("Supports YY-MM-DD 2000 compilant dates",
616
["create table crash_me2 (a date not null)",
617
"insert into crash_me2 values ('10-03-03')"],
618
"select a from crash_me2",
619
["drop table crash_me2"],
624
if (($limits{'type_extra_float(2_arg)'} eq "yes" ||
625
$limits{'type_sql_decimal(2_arg)'} eq "yes") &&
626
(!defined($limits{'storage_of_float'})))
628
my $type=$limits{'type_extra_float(2_arg)'} eq "yes" ? "float(4,1)" :
630
my $result="undefined";
631
if (execute_and_check(["create table crash_q (q1 $type)",
632
"insert into crash_q values(1.14)"],
633
"select q1 from crash_q",
634
["drop table crash_q"],1.1,0) &&
635
execute_and_check(["create table crash_q (q1 $type)",
636
"insert into crash_q values(1.16)"],
637
"select q1 from crash_q",
638
["drop table crash_q"],1.1,0))
642
elsif (execute_and_check(["create table crash_q (q1 $type)",
643
"insert into crash_q values(1.14)"],
644
"select q1 from crash_q",
645
["drop table crash_q"],1.1,0) &&
646
execute_and_check(["create table crash_q (q1 $type)",
647
"insert into crash_q values(1.16)"],
648
"select q1 from crash_q",
649
["drop table crash_q"],1.2,0))
653
elsif (execute_and_check(["create table crash_q (q1 $type)",
654
"insert into crash_q values(1.14)"],
655
"select q1 from crash_q",
656
["drop table crash_q"],1.14,0) &&
657
execute_and_check(["create table crash_q (q1 $type)",
658
"insert into crash_q values(1.16)"],
659
"select q1 from crash_q",
660
["drop table crash_q"],1.16,0))
664
$prompt="Storage of float values";
665
print "$prompt: $result\n";
666
save_config_data("storage_of_float", $result, $prompt);
669
try_and_report("Type for row id", "rowid",
671
"create table crash_q (a rowid)","drop table crash_q"],
673
"create table crash_q (a int not null auto_increment, primary key(a))","drop table crash_q"],
675
"create table crash_q (a oid, primary key(a))","drop table crash_q"],
677
"create table crash_q (a serial, primary key(a))","drop table crash_q"]);
679
try_and_report("Automatic rowid", "automatic_rowid",
681
"create table crash_q (a int not null, primary key(a))",
682
"insert into crash_q values (1)",
683
"select _rowid from crash_q",
684
"drop table crash_q"]);
691
(["+, -, * and /","+","5*3-4/2+1",14,0],
692
["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
693
["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
694
["searched CASE","searched_case","case when 1 > 2 then 'false' when 2 > 1 then 'true' end", "true",1],
695
["simple CASE","simple_case","case 2 when 1 then 'false' when 2 then 'true' end", "true",1],
696
["CAST","cast","CAST(1 as CHAR)","1",1],
697
["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
698
["CHAR_LENGTH","char_length","char_length(b)","10",0],
699
["CHAR_LENGTH(constant)","char_length(constant)","char_length('abcd')","4",0],
700
["COALESCE","coalesce","coalesce($char_null,'bcd','qwe')","bcd",1],
701
["CURRENT_DATE","current_date","current_date",0,2],
702
["CURRENT_TIME","current_time","current_time",0,2],
703
["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
704
["CURRENT_USER","current_user","current_user",0,2],
705
["EXTRACT","extract_sql","extract(minute from timestamp '2000-02-23 18:43:12.987')",43,0],
706
["LOCALTIME","localtime","localtime",0,2],
707
["LOCALTIMESTAMP","localtimestamp","localtimestamp",0,2],
708
["LOWER","lower","LOWER('ABC')","abc",1],
709
["NULLIF with strings","nullif_string","NULLIF(NULLIF('first','second'),'first')",undef(),4],
710
["NULLIF with numbers","nullif_num","NULLIF(NULLIF(1,2),1)",undef(),4],
711
["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
712
["POSITION","position","position('ll' in 'hello')",3,0],
713
["SESSION_USER","session_user","session_user",0,2],
714
["SYSTEM_USER","system_user","system_user",0,2],
715
["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",3],
716
["UPPER","upper","UPPER('abc')","ABC",1],
717
["USER","user","user"],
718
["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
722
(["ASCII", "ascii", "ASCII('A')","65",0],
723
["CHAR", "char", "CHAR(65)" ,"A",1],
724
["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
725
["DIFFERENCE()","difference","difference('abc','abe')",0,2],
726
["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
727
["LEFT","left","left('abcd',2)","ab",1],
728
["LTRIM","ltrim","ltrim(' abcd')","abcd",1],
729
["REAL LENGTH","length","length('abcd ')","5",0],
730
["ODBC LENGTH","length_without_space","length('abcd ')","4",0],
731
["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
732
["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
733
["LCASE","lcase","lcase('ABC')","abc",1],
734
["REPEAT","repeat","repeat('ab',3)","ababab",1],
735
["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
736
["RIGHT","right","right('abcd',2)","cd",1],
737
["RTRIM","rtrim","rtrim(' abcd ')"," abcd",1],
738
["SPACE","space","space(5)"," ",3],
739
["SOUNDEX","soundex","soundex('hello')",0,2],
740
["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
741
["UCASE","ucase","ucase('abc')","ABC",1],
743
["ABS","abs","abs(-5)",5,0],
744
["ACOS","acos","acos(0)","1.570796",0],
745
["ASIN","asin","asin(1)","1.570796",0],
746
["ATAN","atan","atan(1)","0.785398",0],
747
["ATAN2","atan2","atan2(1,0)","1.570796",0],
748
["CEILING","ceiling","ceiling(-4.5)",-4,0],
749
["COS","cos","cos(0)","1.00000",0],
750
["COT","cot","cot(1)","0.64209262",0],
751
["DEGREES","degrees","degrees(6.283185)","360",0],
752
["EXP","exp","exp(1)","2.718282",0],
753
["FLOOR","floor","floor(2.5)","2",0],
754
["LOG","log","log(2)","0.693147",0],
755
["LOG10","log10","log10(10)","1",0],
756
["MOD","mod","mod(11,7)","4",0],
757
["PI","pi","pi()","3.141593",0],
758
["POWER","power","power(2,4)","16",0],
759
["RAND","rand","rand(1)",0,2], # Any value is acceptable
760
["RADIANS","radians","radians(360)","6.283185",0],
761
["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
762
["SIGN","sign","sign(-5)",-1,0],
763
["SIN","sin","sin(1)","0.841471",0],
764
["SQRT","sqrt","sqrt(4)",2,0],
765
["TAN","tan","tan(1)","1.557408",0],
766
["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
767
["NOW","now","now()",0,2], # Any value is acceptable
768
["CURDATE","curdate","curdate()",0,2],
769
["DAYNAME","dayname","dayname(DATE '1997-02-01')","",2],
770
["MONTH","month","month(DATE '1997-02-01')","",2],
771
["MONTHNAME","monthname","monthname(DATE '1997-02-01')","",2],
772
["DAYOFMONTH","dayofmonth","dayofmonth(DATE '1997-02-01')",1,0],
773
["DAYOFWEEK","dayofweek","dayofweek(DATE '1997-02-01')",7,0],
774
["DAYOFYEAR","dayofyear","dayofyear(DATE '1997-02-01')",32,0],
775
["QUARTER","quarter","quarter(DATE '1997-02-01')",1,0],
776
["WEEK","week","week(DATE '1997-02-01')",5,0],
777
["YEAR","year","year(DATE '1997-02-01')",1997,0],
778
["CURTIME","curtime","curtime()",0,2],
779
["HOUR","hour","hour('12:13:14')",12,0],
780
["ANSI HOUR","hour_time","hour(TIME '12:13:14')",12,0],
781
["MINUTE","minute","minute('12:13:14')",13,0],
782
["SECOND","second","second('12:13:14')",14,0],
783
["TIMESTAMPADD","timestampadd",
784
"timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
785
"1997-01-01 00:00:01",1],
786
["TIMESTAMPDIFF","timestampdiff",
787
"timestampdiff(SQL_TSI_SECOND,'1997-01-01 00:00:02', '1997-01-01 00:00:01')","1",0],
788
["USER()","user()","user()",0,2],
789
["DATABASE","database","database()",0,2],
790
["IFNULL","ifnull","ifnull(2,3)",2,0],
791
["ODBC syntax LEFT & RIGHT", "fn_left",
792
"{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
797
["& (bitwise and)",'&',"5 & 3",1,0],
798
["| (bitwise or)",'|',"1 | 2",3,0],
799
["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
800
["<> in SELECT","<>","1<>1","0",0],
801
["=","=","(1=1)",1,$logical_value],
802
["~* (case insensitive compare)","~*","'hi' ~* 'HI'",1,$logical_value],
803
["ADD_MONTHS","add_months","add_months('1997-01-01',1)","1997-02-01",0], # oracle the date plus n months
804
["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
805
["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
806
["ASCII_CHAR", "ascii_char", "ASCII_CHAR(65)","A",1],
807
["ASCII_CODE", "ascii_code", "ASCII_CODE('A')","65",0],
808
["ATN2","atn2","atn2(1,0)","1.570796",0],
809
["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
810
["BIT_COUNT","bit_count","bit_count(5)",2,0],
811
["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
812
["CHARINDEX","charindex","charindex('a','crash')",3,0],
813
["CHR", "chr", "CHR(65)" ,"A",1], # oracle
814
["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
815
["CONVERT","convert","convert(CHAR,5)","5",1],
816
["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
817
["DATEADD","dateadd","dateadd(day,3,'Nov 30 1997')",0,2],
818
["DATEDIFF","datediff","datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
819
["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
820
["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
821
["DATE_FORMAT","date_format", "date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
822
["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
823
["ENCRYPT","encrypt","encrypt('hello')",0,2],
824
["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
825
["FORMAT","format","format(1234.5555,2)","1,234.56",1],
826
["FROM_DAYS","from_days","from_days(729024)","1996-01-01",1],
827
["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
828
["GETDATE","getdate","getdate()",0,2],
829
["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1], # oracle
830
["IF","if", "if(5,6,7)",6,0],
831
["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
832
["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
833
["INITCAP","initcap","initcap('the soap')","The Soap",1], # oracle Returns char, with the first letter of each word in uppercase
834
["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)" ,"14",0], # oracle instring
835
["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)" ,"27",0], # oracle instring in bytes
836
["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
837
["LAST_DAY","last_day","last_day('1997-04-01')","1997-04-30",0], # oracle last day of month of date
838
["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
839
["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1], # oracle
840
["LENGTHB","lengthb","lengthb('CANDIDE')","14",0], # oracle length in bytes
841
["LIKE ESCAPE in SELECT","like_escape","'%' like 'a%' escape 'a'",$logical_value,0],
842
["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
843
["LN","ln","ln(95)","4.55387689",0], # oracle natural logarithm of n
844
["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
845
["LOG(m,n)","log(m_n)","log(10,100)","2",0], # oracle logarithm, base m, of n
846
["LOGN","logn","logn(2)","0.693147",0], # informix
847
["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
848
["MDY","mdy","mdy(7,1,1998)","1998-07-01",0], # informix
849
["MOD as %","%","10%7","3",0],
850
["MONTHS_BETWEEN","months_between","months_between('1997-02-02','1997-01-01')","1.03225806",0], # oracle number of months between 2 dates
851
["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
852
["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
853
["NOT as '!' in SELECT","!","! 1",0,0],
854
["NOT in SELECT","not","not $false",$logical_value,0],
855
["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
856
["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
857
["PASSWORD","password","password('hello')",0,2],
858
["PASTE", "paste", "paste('ABCDEFG',3,2,'1234')","AB1234EFG",1],
859
["PATINDEX","patindex","patindex('%a%','crash')",3,0],
860
["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
861
["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
862
["POW","pow","pow(3,2)",9,0],
863
["RANGE","range","range(a)","0.0",0], # informix range(a) = max(a) - min(a)
864
["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
865
["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
866
["REVERSE","reverse","reverse('abcd')","dcba",1],
867
["ROOT","root","root(4)",2,0], # informix
868
["ROUND(1 arg)","round1","round(5.63)","6",0],
869
["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
870
["SEC_TO_TIME","sec_to_time","sec_to_time(5001)","01:23:21",1],
871
["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
872
["STR","str","str(123.45,5,1)",123.5,3],
873
["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
874
["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
875
["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)" ,"CD",1], # oracle substring with bytes
876
["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
877
["SUBSTRING_INDEX","substring_index","substring_index('www.tcx.se','.',-2)", "tcx.se",1],
878
["SYSDATE","sysdate","sysdate()",0,2],
879
["TAIL","tail","tail('ABCDEFG',3)","EFG",0],
880
["TANH","tanh","tanh(1)","0.462117157",0], # oracle hyperbolic tangent of n
881
["TIME_TO_SEC","time_to_sec","time_to_sec('01:23:21')","5001",0],
882
["TO_DAYS","to_days","to_days(DATE '1996-01-01')",729024,0],
883
["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
884
["TRIM; Many char extension","trim_many_char","trim(':!' FROM ':abc!')","abc",3],
885
["TRIM; Substring extension","trim_substring","trim('cb' FROM 'abccb')","abc",3],
886
["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
887
["UID","uid","uid",0,2], # oracle uid from user
888
["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
889
["USERENV","userenv","userenv",0,2], # oracle user enviroment
890
["VERSION","version","version()",0,2],
891
["WEEKDAY","weekday","weekday(DATE '1997-11-29')",5,0],
892
["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
893
["automatic string->num convert","auto_string2num","'1'+2",3,0],
894
["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
897
@sql_group_functions=
899
["AVG","avg","avg(a)",1,0],
900
["COUNT (*)","count_*","count(*)",1,0],
901
["COUNT column name","count_column","count(a)",1,0],
902
["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
903
["MAX on numbers","max","max(a)",1,0],
904
["MAX on strings","max_str","max(b)","a",1],
905
["MIN on numbers","min","min(a)",1,0],
906
["MIN on strings","min_str","min(b)","a",1],
907
["SUM","sum","sum(a)",1,0],
910
@extra_group_functions=
912
["BIT_AND",'bit_and',"bit_and(a)",1,0],
913
["BIT_OR", 'bit_or', "bit_or(a)",1,0],
914
["COUNT(DISTINCT expr,expr,...)","count_distinct_list","count(distinct a,b)",1,0],
915
["STD","std","std(a)",0,0],
916
["STDDEV","stddev","stddev(a)",0,0],
917
["VARIANCE","variance","variance(a)",0,0],
922
["= ALL","eq_all","b =all (select b from crash_me)",1,0],
923
["= ANY","eq_any","b =any (select b from crash_me)",1,0],
924
["= SOME","eq_some","b =some (select b from crash_me)",1,0],
925
["BETWEEN","between","5 between 4 and 6",1,0],
926
["EXISTS","exists","exists (select * from crash_me)",1,0],
927
["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
928
["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
929
["LIKE","like","b like 'a%'",1,0],
930
["MATCH UNIQUE","match_unique","1 match unique (select a from crash_me)",1,0],
931
["MATCH","match","1 match (select a from crash_me)",1,0],
932
["MATCHES","matches","b matcjhes 'a*'",1,0],
933
["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
934
["NOT EXISTS","not_exists","not exists (select * from crash_me where a = 2)",1,0],
935
["NOT LIKE","not_like","b not like 'b%'",1,0],
936
["NOT UNIQUE","not_unique","not unique (select * from crash_me where a = 2)",1,0],
937
["UNIQUE","unique","unique (select * from crash_me)",1,0],
940
@types=(["sql",\@sql_functions,0],
941
["odbc",\@odbc_functions,0],
942
["extra",\@extra_functions,0],
943
["where",\@where_functions,0]);
945
@group_types=(["sql",\@sql_group_functions,0],
946
["extra",\@extra_group_functions,0]);
949
foreach $types (@types)
951
print "\nSupported $types->[0] functions\n";
953
foreach $type (@$tmp)
955
if (defined($limits{"func_$types->[0]_$type->[1]"}))
959
if ($types->[0] eq "where")
961
check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
962
[],"select a from crash_me where $type->[2]",[],
963
$type->[3],$type->[4]);
965
elsif ($limits{'functions'} eq 'yes')
967
if (($type->[2] =~ /char_length\(b\)/) && (!$end_query))
970
$tmp .= " from crash_me ";
971
undef($limits{"func_$types->[0]_$type->[1]"});
972
check_and_report("Function $type->[0]",
973
"func_$types->[0]_$type->[1]",
974
[],"select $tmp ",[],
975
$type->[3],$type->[4]);
979
undef($limits{"func_$types->[0]_$type->[1]"});
980
$result = check_and_report("Function $type->[0]",
981
"func_$types->[0]_$type->[1]",
982
[],"select $type->[2] $end_query",[],
983
$type->[3],$type->[4]);
986
# check without type specifyer
987
if ($type->[2] =~ /DATE /)
991
undef($limits{"func_$types->[0]_$type->[1]"});
992
$result = check_and_report("Function $type->[0]",
993
"func_$types->[0]_$type->[1]",
994
[],"select $tmp $end_query",[],
995
$type->[3],$type->[4]);
999
if ($types->[0] eq "odbc" && ! ($type->[2] =~ /\{fn/))
1001
my $tmp= $type->[2];
1002
# Check by converting to ODBC format
1003
undef($limits{"func_$types->[0]_$type->[1]"});
1005
$tmp =~ s/('1997-\d\d-\d\d \d\d:\d\d:\d\d')/{ts $1}/g;
1006
$tmp =~ s/(DATE '1997-\d\d-\d\d')/{d $1}/g;
1007
$tmp =~ s/(TIME '12:13:14')/{t $1}/g;
1010
check_and_report("Function $type->[0]",
1011
"func_$types->[0]_$type->[1]",
1012
[],"select $tmp $end_query",[],
1013
$type->[3],$type->[4]);
1022
if ($limits{'functions'} eq 'yes')
1024
foreach $types (@group_types)
1026
print "\nSupported $types->[0] group functions\n";
1028
foreach $type (@$tmp)
1030
check_and_report("Group function $type->[0]",
1031
"group_func_$types->[0]_$type->[1]",
1032
[],"select $type->[2],a from crash_me group by a",[],
1033
$type->[3],$type->[4]);
1037
report("mixing of integer and float in expression","float_int_expr",
1038
"select 1+1.0 $end_query");
1040
check_and_report("Is 1+NULL = NULL","null_num_expr",
1041
[],"select 1+$numeric_null $end_query",[],undef(),4);
1042
$tmp=sql_concat("'a'",$char_null);
1045
check_and_report("Is $tmp = NULL", "null_concat_expr", [],
1046
"select $tmp $end_query",[], undef(),4);
1048
$prompt="Need to cast NULL for arithmetic";
1049
save_config_data("Need_cast_for_null",
1050
($numeric_null eq "NULL") ? "no" : "yes",
1059
report("LIKE on numbers","like_with_number",
1060
"create table crash_q (a int,b int)",
1061
"insert into crash_q values(10,10)",
1062
"select * from crash_q where a like '10'",
1063
"drop table crash_q");
1065
report("column LIKE column","like_with_column",
1066
"create table crash_q (a char(10),b char(10))",
1067
"insert into crash_q values('abc','abc')",
1068
"select * from crash_q where a like b",
1069
"drop table crash_q");
1071
report("update of column= -column","NEG",
1072
"create table crash_q (a integer)",
1073
"insert into crash_q values(10)",
1074
"update crash_q set a=-a",
1075
"drop table crash_q");
1077
if ($limits{'func_odbc_left'} eq 'yes' ||
1078
$limits{'func_odbc_substring'} eq 'yes')
1080
my $type= ($limits{'func_odbc_left'} eq 'yes' ?
1081
"left(a,4)" : "substring(a for 4)");
1083
check_and_report("String functions on date columns","date_as_string",
1084
["create table crash_me2 (a date not null)",
1085
"insert into crash_me2 values ('1998-03-03')"],
1086
"select $type from crash_me2",
1087
["drop table crash_me2"],
1092
$tmp=sql_concat("b","b");
1095
check_and_report("char are space filled","char_is_space_filled",
1096
[],"select $tmp from crash_me where b = 'a '",[],
1100
if (!defined($limits{'multi_table_update'}))
1102
if (check_and_report("Update with many tables","multi_table_update",
1103
["create table crash_q (a integer,b char(10))",
1104
"insert into crash_q values(1,'c')",
1105
"update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
1106
"select b from crash_q",
1107
["drop table crash_q"],
1110
check_and_report("Update with many tables","multi_table_update",
1111
["create table crash_q (a integer,b char(10))",
1112
"insert into crash_q values(1,'c')",
1113
"update crash_q,crash_me set crash_q.b=crash_me.b where crash_q.a=crash_me.a"],
1114
"select b from crash_q",
1115
["drop table crash_q"],
1121
report("DELETE FROM table1,table2...","multi_table_delete",
1122
"create table crash_q (a integer,b char(10))",
1123
"insert into crash_q values(1,'c')",
1124
"delete crash_q.* from crash_q,crash_me where crash_q.a=crash_me.a",
1125
"drop table crash_q");
1127
check_and_report("Update with sub select","select_table_update",
1128
["create table crash_q (a integer,b char(10))",
1129
"insert into crash_q values(1,'c')",
1130
"update crash_q set b= (select b from crash_me where crash_q.a = crash_me.a)"],
1131
"select b from crash_q",
1132
["drop table crash_q"],
1135
check_and_report("Calculate 1--1","minus_neg",[],
1136
"select a--1 from crash_me",[],0,2);
1138
report("ANSI SQL simple joins","simple_joins",
1139
"select crash_me.a from crash_me, crash_me t0");
1142
# Check max string size, and expression limits
1145
foreach $type (('mediumtext','text','text()','blob','long'))
1147
if ($limits{"type_extra_$type"} eq 'yes')
1153
if (defined($found))
1155
$found =~ s/\(\)/\(%d\)/;
1156
find_limit("max text or blob size","max_text_size",
1157
new query_many(["create table crash_q (q $found)",
1158
"insert into crash_q values ('%s')"],
1159
"select * from crash_q","%s",
1160
["drop table crash_q"],
1161
min($max_string_size,$limits{'query_size'}-30)));
1165
# It doesn't make lots of sense to check for string lengths much bigger than
1166
# what can be stored...
1168
find_limit(($prompt="constant string size in where"),"where_string_size",
1169
new query_repeat([],"select a from crash_me where b <'",
1171
if ($limits{'where_string_size'} == 10)
1173
save_config_data('where_string_size','nonstandard',$prompt);
1176
if ($limits{'select_constants'} eq 'yes')
1178
find_limit("constant string size in SELECT","select_string_size",
1179
new query_repeat([],"select '","","","a","","'$end_query"));
1182
goto no_functions if ($limits{'functions'} ne "yes");
1184
if ($limits{'func_odbc_repeat'} eq 'yes')
1186
find_limit("return string size from function","repeat_string_size",
1188
"select repeat('a',%d) $end_query","%s",
1190
$max_string_size,0));
1193
$tmp=find_limit("simple expressions","max_expressions",
1194
new query_repeat([],"select 1","","","+1","",$end_query,
1195
undef(),$max_expressions));
1199
$tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
1200
find_limit("big expressions", "max_big_expressions",
1201
new query_repeat([],"select 0","","","+$tmp","",$end_query,
1202
undef(),$max_big_expressions));
1205
find_limit("stacked expressions", "max_stack_expression",
1206
new query_repeat([],"select 1","","","+(1",")",$end_query,
1207
undef(),$max_stacked_expressions));
1211
if (!defined($limits{'max_conditions'}))
1213
find_limit("OR and AND in WHERE","max_conditions",
1214
new query_repeat([],
1215
"select a from crash_me where a=1 and b='a'","",
1216
"", " or a=%d and b='%d'","","","",
1217
[],($query_size-42)/29,undef,2));
1218
$limits{'max_conditions'}*=2;
1220
# The 42 is the length of the constant part.
1221
# The 29 is the length of the variable part, plus two seven-digit numbers.
1223
find_limit("tables in join", "join_tables",
1224
new query_repeat([],
1225
"select crash_me.a",",t%d.a","from crash_me",
1226
",crash_me t%d","","",[],$max_join_tables,undef,
1229
# Different CREATE TABLE options
1231
report("primary key in create table",'primary_key_in_create',
1232
"create table crash_q (q integer not null,primary key (q))",
1233
"drop table crash_q");
1235
report("unique in create table",'unique_in_create',
1236
"create table crash_q (q integer not null,unique (q))",
1237
"drop table crash_q");
1239
if ($limits{'unique_in_create'} eq 'yes')
1241
report("unique null in create",'unique_null_in_create',
1242
"create table crash_q (q integer,unique (q))",
1243
"insert into crash_q (q) values (NULL)",
1244
"insert into crash_q (q) values (NULL)",
1245
"insert into crash_q (q) values (1)",
1246
"drop table crash_q");
1249
report("default value for column",'create_default',
1250
"create table crash_q (q integer default 10 not null)",
1251
"drop table crash_q");
1253
report("default value function for column",'create_default_func',
1254
"create table crash_q (q integer not null,q1 integer default (1+1)",
1255
"drop table crash_q");
1257
report("temporary tables",'tempoary_table',
1258
"create temporary table crash_q (q integer not null)",
1259
"drop table crash_q");
1261
report("create table from select",'create_table_select',
1262
"create table crash_q SELECT * from crash_me",
1263
"drop table crash_q");
1265
report("index in create table",'index_in_create',
1266
"create table crash_q (q integer not null,index (q))",
1267
"drop table crash_q");
1269
# The following must be executed as we need the value of end_drop_keyword
1271
if (! defined($limits{'create_index'}) &&
1272
! defined($limits{'drop_index'}) )
1274
if ($res=safe_query("create index crash_q on crash_me (a)"))
1278
$end_drop_keyword="";
1279
if (!safe_query("drop index crash_q"))
1281
# Can't drop the standard way; Check if mSQL
1282
if (safe_query("drop index crash_q from crash_me"))
1284
$drop_res="with 'FROM'"; # Drop is not ANSI SQL
1285
$end_drop_keyword="drop index %i from %t";
1287
# else check if Access or MySQL
1288
elsif (safe_query("drop index crash_q on crash_me"))
1290
$drop_res="with 'ON'"; # Drop is not ANSI SQL
1291
$end_drop_keyword="drop index %i on %t";
1293
# else check if MS-SQL
1294
elsif (safe_query("drop index crash_me.crash_q"))
1296
$drop_res="with 'table.index'"; # Drop is not ANSI SQL
1297
$end_drop_keyword="drop index %t.%i";
1302
# Old MySQL 3.21 supports only the create index syntax
1303
# This means that the second create doesn't give an error.
1304
$res=safe_query(["create index crash_q on crash_me (a)",
1305
"create index crash_q on crash_me (a)",
1306
"drop index crash_q"]);
1307
$res= $res ? 'ignored' : 'yes';
1312
$drop_res=$res='no';
1314
save_config_data('create_index',$res,"create index");
1315
save_config_data('drop_index',$drop_res,"drop index");
1317
print "create index: $limits{'create_index'}\n";
1318
print "drop index: $limits{'drop_index'}\n";
1321
# check if we can have 'NULL' as a key
1322
check_and_report("null in index","null_in_index",
1323
[create_table("crash_q",["a char(10)"],["(a)"]),
1324
"insert into crash_q values (NULL)"],
1325
"select * from crash_q",
1326
["drop table crash_q"],
1329
if ($limits{'unique_in_create'} eq 'yes')
1331
report("null in unique index",'null_in_unique',
1332
create_table("crash_q",["q integer"],["unique(q)"]),
1333
"insert into crash_q (q) values(NULL)",
1334
"insert into crash_q (q) values(NULL)",
1335
"drop table crash_q");
1338
if ($limits{'null_in_unique'} eq 'yes')
1340
report("null in unique index",'multi_null_in_unique',
1341
create_table("crash_q",["q integer, x integer"],["unique(q)"]),
1342
"insert into crash_q(x) values(1)",
1343
"insert into crash_q(x) values(2)",
1344
"drop table crash_q");
1347
if ($limits{'create_index'} ne 'no')
1349
$end_drop=$end_drop_keyword;
1350
$end_drop =~ s/%i/crash_q/;
1351
$end_drop =~ s/%t/crash_me/;
1352
report("index on column part (extension)","index_parts",,
1353
"create index crash_q on crash_me (b(5))",
1355
$end_drop=$end_drop_keyword;
1356
$end_drop =~ s/%i/crash_me/;
1357
$end_drop =~ s/%t/crash_me/;
1358
report("different namespace for index",
1360
"create index crash_me on crash_me (b)",
1364
if (!report("case independent table names","table_name_case",
1365
"create table crash_q (q integer)",
1366
"drop table CRASH_Q"))
1368
safe_query("drop table crash_q");
1371
if (!report("drop table if exists","drop_if_exists",
1372
"create table crash_q (q integer)",
1373
"drop table if exists crash_q"))
1375
safe_query("drop table crash_q");
1378
report("create table if not exists","create_if_not_exists",
1379
"create table crash_q (q integer)",
1380
"create table if not exists crash_q (q integer)");
1381
safe_query("drop table crash_q");
1384
# test of different join types
1387
assert("create table crash_me2 (a integer not null,b char(10) not null, c integer)");
1388
assert("insert into crash_me2 (a,b,c) values (1,'b',1)");
1389
assert("create table crash_me3 (a integer not null,b char(10) not null)");
1390
assert("insert into crash_me3 (a,b) values (1,'b')");
1392
report("inner join","inner_join",
1393
"select crash_me.a from crash_me inner join crash_me2 ON crash_me.a=crash_me2.a");
1394
report("left outer join","left_outer_join",
1395
"select crash_me.a from crash_me left join crash_me2 ON crash_me.a=crash_me2.a");
1396
report("natural left outer join","natural_left_outer_join",
1397
"select c from crash_me natural left join crash_me2");
1398
report("left outer join using","left_outer_join_using",
1399
"select c from crash_me left join crash_me2 using (a)");
1400
report("left outer join odbc style","odbc_left_outer_join",
1401
"select crash_me.a from { oj crash_me left outer join crash_me2 ON crash_me.a=crash_me2.a }");
1402
report("right outer join","right_outer_join",
1403
"select crash_me.a from crash_me right join crash_me2 ON crash_me.a=crash_me2.a");
1404
report("full outer join","full_outer_join",
1405
"select crash_me.a from crash_me full join crash_me2 ON crash_me.a=crash_me2.a");
1406
report("cross join (same as from a,b)","cross_join",
1407
"select crash_me.a from crash_me cross join crash_me3");
1408
report("natural join","natural_join",
1409
"select * from crash_me natural join crash_me3");
1410
report("union","union",
1411
"select * from crash_me union select a,b from crash_me3");
1412
report("union all","union_all",
1413
"select * from crash_me union all select a,b from crash_me3");
1414
report("intersect","intersect",
1415
"select * from crash_me intersect select * from crash_me3");
1416
report("intersect all","intersect_all",
1417
"select * from crash_me intersect all select * from crash_me3");
1418
report("except","except",
1419
"select * from crash_me except select * from crash_me3");
1420
report("except all","except_all",
1421
"select * from crash_me except all select * from crash_me3");
1422
report("except","except",
1423
"select * from crash_me except select * from crash_me3");
1424
report("except all","except_all",
1425
"select * from crash_me except all select * from crash_me3");
1426
report("minus","minus",
1427
"select * from crash_me minus select * from crash_me3"); # oracle ...
1429
report("natural join (incompatible lists)","natural_join_incompat",
1430
"select c from crash_me natural join crash_me2");
1431
report("union (incompatible lists)","union_incompat",
1432
"select * from crash_me union select a,b from crash_me2");
1433
report("union all (incompatible lists)","union_all_incompat",
1434
"select * from crash_me union all select a,b from crash_me2");
1435
report("intersect (incompatible lists)","intersect_incompat",
1436
"select * from crash_me intersect select * from crash_me2");
1437
report("intersect all (incompatible lists)","intersect_all_incompat",
1438
"select * from crash_me intersect all select * from crash_me2");
1439
report("except (incompatible lists)","except_incompat",
1440
"select * from crash_me except select * from crash_me2");
1441
report("except all (incompatible lists)","except_all_incompat",
1442
"select * from crash_me except all select * from crash_me2");
1443
report("except (incompatible lists)","except_incompat",
1444
"select * from crash_me except select * from crash_me2");
1445
report("except all (incompatible lists)","except_all_incompat",
1446
"select * from crash_me except all select * from crash_me2");
1447
report("minus (incompatible lists)","minus_incompat",
1448
"select * from crash_me minus select * from crash_me2"); # oracle ...
1450
assert("drop table crash_me2");
1451
assert("drop table crash_me3");
1453
# somethings to be added here ....
1454
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
1456
# >ALL | ANY | SOME - EXISTS - UNIQUE
1458
if (report("subqueries","subqueries",
1459
"select a from crash_me where crash_me.a in (select max(a) from crash_me)"))
1461
$tmp=new query_repeat([],"select a from crash_me","","",
1462
" where a in (select a from crash_me",")",
1463
"",[],$max_join_tables);
1464
find_limit("recursive subqueries", "recursive_subqueries",$tmp);
1467
report("insert INTO ... SELECT ...","insert_select",
1468
"create table crash_q (a int)",
1469
"insert into crash_q (a) SELECT crash_me.a from crash_me",
1470
"drop table crash_q");
1472
report_trans("transactions","transactions",
1473
[create_table("crash_q",["a integer not null"],[]),
1474
"insert into crash_q values (1)"],
1475
"select * from crash_q",
1476
"drop table crash_q"
1479
report("atomic updates","atomic_updates",
1480
create_table("crash_q",["a integer not null"],["primary key (a)"]),
1481
"insert into crash_q values (2)",
1482
"insert into crash_q values (3)",
1483
"insert into crash_q values (1)",
1484
"update crash_q set a=a+1",
1485
"drop table crash_q");
1487
if ($limits{'atomic_updates'} eq 'yes')
1489
report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
1490
create_table("crash_q",["a integer not null"],
1491
["primary key (a)"]),
1492
"insert into crash_q values (2)",
1493
"insert into crash_q values (3)",
1494
"insert into crash_q values (1)",
1495
"update crash_q set a=a+1 where a < 3",
1496
"drop table crash_q");
1499
# To add with the views:
1500
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
1501
report("views","views",
1502
"create view crash_q as select a from crash_me",
1503
"drop view crash_q");
1505
report("foreign key syntax","foreign_key_syntax",
1506
create_table("crash_q",["a integer not null"],["primary key (a)"]),
1507
create_table("crash_q2",["a integer not null",
1508
"foreign key (a) references crash_q (a)"],
1510
"insert into crash_q values (1)",
1511
"insert into crash_q2 values (1)",
1512
"drop table crash_q2",
1513
"drop table crash_q");
1515
if ($limits{'foreign_key_syntax'} eq 'yes')
1517
report_fail("foreign keys","foreign_key",
1518
create_table("crash_q",["a integer not null"],
1519
["primary key (a)"]),
1520
create_table("crash_q2",["a integer not null",
1521
"foreign key (a) references crash_q (a)"],
1523
"insert into crash_q values (1)",
1524
"insert into crash_q2 values (2)",
1525
"drop table crash_q2",
1526
"drop table crash_q");
1529
report("Create SCHEMA","create_schema",
1530
"create schema crash_schema create table crash_q (a int) create table crash_q2(b int)",
1531
"drop schema crash_schema cascade");
1533
if ($limits{'foreign_key'} eq 'yes')
1535
if ($limits{'create_schema'} eq 'yes')
1537
report("Circular foreign keys","foreign_key_circular",
1538
"create schema crash_schema create table crash_q (a int primary key, b int, foreign key (b) references crash_q2(a)) create table crash_q2(a int, b int, primary key(a), foreign key (b) references crash_q(a))",
1539
"drop schema crash_schema cascade");
1543
report("Column constraints","constraint_check",
1544
"create table crash_q (a int check (a>0))",
1545
"drop table crash_q");
1547
report("Table constraints","constraint_check_table",
1548
"create table crash_q (a int ,b int, check (a>b))",
1549
"drop table crash_q");
1551
report("Named constraints","constraint_check",
1552
"create table crash_q (a int ,b int, constraint abc check (a>b))",
1553
"drop table crash_q");
1555
report("NULL constraint (SyBase style)","constraint_null",
1556
"create table crash_q (a int null)",
1557
"drop table crash_q");
1559
report("Triggers (ANSI SQL)","psm_trigger",
1560
"create table crash_q (a int ,b int)",
1561
"create trigger crash_trigger after insert on crash_q referencing new table as new_a when (localtime > time '18:00:00') begin atomic end",
1562
"insert into crash_q values(1,2)",
1563
"drop trigger crash_trigger",
1564
"drop table crash_q");
1566
report("PSM procedures (ANSI SQL)","psm_procedures",
1567
"create table crash_q (a int,b int)",
1568
"create procedure crash_proc(in a1 int, in b1 int) language sql modifies sql data begin declare c1 int; set c1 = a1 + b1; insert into crash_q(a,b) values (a1,c1); end",
1569
"call crash_proc(1,10)",
1570
"drop procedure crash_proc",
1571
"drop table crash_q");
1573
report("PSM modules (ANSI SQL)","psm_modules",
1574
"create table crash_q (a int,b int)",
1575
"create module crash_m declare procedure crash_proc(in a1 int, in b1 int) language sql modifies sql data begin declare c1 int; set c1 = a1 + b1; insert into crash_q(a,b) values (a1,c1); end; declare procedure crash_proc2(INOUT a int, in b int) contains sql set a = b + 10; end module",
1576
"call crash_proc(1,10)",
1577
"drop module crash_m cascade",
1578
"drop table crash_q cascade");
1580
report("PSM functions (ANSI SQL)","psm_functions",
1581
"create table crash_q (a int)",
1582
"create function crash_func(in a1 int, in b1 int) returns int language sql deterministic contains sql begin return a1 * b1; end",
1583
"insert into crash_q values(crash_func(2,4))",
1584
"select a,crash_func(a,2) from crash_q",
1585
"drop function crash_func cascade",
1586
"drop table crash_q");
1588
report("Domains (ANSI SQL)","domains",
1589
"create domain crash_d as varchar(10) default 'Empty' check (value <> 'abcd')",
1590
"create table crash_q(a crash_d, b int)",
1591
"insert into crash_q(a,b) values('xyz',10)",
1592
"insert into crash_q(b) values(10)",
1593
"drop table crash_q",
1594
"drop domain crash_d");
1597
if (!defined($limits{'lock_tables'}))
1599
report("lock table","lock_tables",
1600
"lock table crash_me READ",
1602
if ($limits{'lock_tables'} eq 'no')
1604
delete $limits{'lock_tables'};
1605
report("lock table","lock_tables",
1606
"lock table crash_me IN SHARE MODE");
1610
if (!report("many tables to drop table","multi_drop",
1611
"create table crash_q (a int)",
1612
"create table crash_q2 (a int)",
1613
"drop table crash_q,crash_q2"))
1615
$dbh->do("drop table crash_q");
1616
$dbh->do("drop table crash_q2");
1620
report("-- as comment","comment_--",
1621
"select * from crash_me -- Testing of comments");
1622
report("// as comment","comment_//",
1623
"select * from crash_me // Testing of comments");
1624
report("# as comment","comment_#",
1625
"select * from crash_me # Testing of comments");
1626
report("/* */ as comment","comment_/**/",
1627
"select * from crash_me /* Testing of comments */");
1630
# Check things that fails one some servers
1633
# Empress can't insert empty strings in a char() field
1634
report("insert empty string","insert_empty_string",
1635
create_table("crash_q",["a char(10) not null,b char(10)"],[]),
1636
"insert into crash_q values ('','')",
1637
"drop table crash_q");
1639
report("Having with alias","having_with_alias",
1640
create_table("crash_q",["a integer"],[]),
1641
"insert into crash_q values (10)",
1642
"select sum(a) as b from crash_q group by a having b > 0",
1643
"drop table crash_q");
1649
find_limit("table name length","max_table_name",
1650
new query_many(["create table crash_q%s (q integer)",
1651
"insert into crash_q%s values(1)"],
1652
"select * from crash_q%s",1,
1653
["drop table crash_q%s"],
1654
$max_name_length,7,1));
1656
find_limit("column name length","max_column_name",
1657
new query_many(["create table crash_q (q%s integer)",
1658
"insert into crash_q (q%s) values(1)"],
1659
"select q%s from crash_q",1,
1660
["drop table crash_q"],
1661
$max_name_length,1));
1663
if ($limits{'column_alias'} eq 'yes')
1665
find_limit("select alias name length","max_select_alias_name",
1666
new query_many(undef,
1667
"select b as %s from crash_me",undef,
1668
undef, $max_name_length));
1671
find_limit("table alias name length","max_table_alias_name",
1672
new query_many(undef,
1673
"select %s.b from crash_me %s",
1675
undef, $max_name_length));
1677
$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
1678
$end_drop=$end_drop_keyword;
1679
$end_drop =~ s/%i/crash_q%s/;
1680
$end_drop =~ s/%t/crash_me/;
1682
if ($limits{'create_index'} ne 'no')
1684
find_limit("index name length","max_index_name",
1685
new query_many(["create index crash_q%s on crash_me (a)"],
1688
$max_name_length,7));
1691
find_limit("max char() size","max_char_size",
1692
new query_many(["create table crash_q (q char(%d))",
1693
"insert into crash_q values ('%s')"],
1694
"select * from crash_q","%s",
1695
["drop table crash_q"],
1696
min($max_string_size,$limits{'query_size'})));
1698
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
1700
find_limit("max varchar() size","max_varchar_size",
1701
new query_many(["create table crash_q (q varchar(%d))",
1702
"insert into crash_q values ('%s')"],
1703
"select * from crash_q","%s",
1704
["drop table crash_q"],
1705
min($max_string_size,$limits{'query_size'})));
1709
foreach $type (('mediumtext','text','text()','blob','long'))
1711
if ($limits{"type_extra_$type"} eq 'yes')
1717
if (defined($found))
1719
$found =~ s/\(\)/\(%d\)/;
1720
find_limit("max text or blob size","max_text_size",
1721
new query_many(["create table crash_q (q $found)",
1722
"insert into crash_q values ('%s')"],
1723
"select * from crash_q","%s",
1724
["drop table crash_q"],
1725
min($max_string_size,$limits{'query_size'}-30)));
1729
$tmp=new query_repeat([],"create table crash_q (a integer","","",
1730
",a%d integer","",")",["drop table crash_q"],
1733
find_limit("Columns in table","max_columns",$tmp);
1735
# Make a field definition to be used when testing keys
1737
$key_definitions="q0 integer not null";
1739
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
1741
$key_definitions.=",q$i integer not null";
1742
$key_fields.=",q$i";
1744
$key_values="1," x $i;
1747
if ($limits{'unique_in_create'} eq 'yes')
1749
find_limit("unique indexes","max_unique_index",
1750
new query_table("create table crash_q (q integer",
1751
",q%d integer not null,unique (q%d)",")",
1752
["insert into crash_q (q,%f) values (1,%v)"],
1753
"select q from crash_q",1,
1754
"drop table crash_q",
1757
find_limit("index parts","max_index_parts",
1758
new query_table("create table crash_q ($key_definitions,unique (q0",
1760
["insert into crash_q ($key_fields) values ($key_values)"],
1761
"select q0 from crash_q",1,
1762
"drop table crash_q",
1765
find_limit("max index part length","max_index_part_length",
1766
new query_many(["create table crash_q (q char(%d) not null,unique(q))",
1767
"insert into crash_q (q) values ('%s')"],
1768
"select q from crash_q","%s",
1769
["drop table crash_q"],
1770
$limits{'max_char_size'},0));
1772
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
1774
find_limit("index varchar part length","max_index_varchar_part_length",
1775
new query_many(["create table crash_q (q varchar(%d) not null,unique(q))",
1776
"insert into crash_q (q) values ('%s')"],
1777
"select q from crash_q","%s",
1778
["drop table crash_q"],
1779
$limits{'max_varchar_size'},0));
1784
if ($limits{'create_index'} ne 'no')
1786
if ($limits{'create_index'} eq 'ignored' ||
1787
$limits{'unique_in_create'} eq 'yes')
1788
{ # This should be true
1789
save_config_data('max_index',$limits{'max_unique_index'},"max index");
1790
print "indexes: $limits{'max_index'}\n";
1794
if (!defined($limits{'max_index'}))
1796
assert("create table crash_q ($key_definitions)");
1797
for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
1799
last if (!safe_query("create index crash_q$i on crash_q (q$i)"));
1801
save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
1805
$end_drop=$end_drop_keyword;
1806
$end_drop =~ s/%i/crash_q$i/;
1807
$end_drop =~ s/%t/crash_q/;
1810
assert("drop table crash_q");
1812
print "indexs: $limits{'max_index'}\n";
1813
if (!defined($limits{'max_unique_index'}))
1815
assert("create table crash_q ($key_definitions)");
1816
for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
1818
last if (!safe_query("create unique index crash_q$i on crash_q (q$i)"));
1820
save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
1821
"max unique index");
1824
$end_drop=$end_drop_keyword;
1825
$end_drop =~ s/%i/crash_q$i/;
1826
$end_drop =~ s/%t/crash_q/;
1829
assert("drop table crash_q");
1831
print "unique indexes: $limits{'max_unique_index'}\n";
1832
if (!defined($limits{'max_index_parts'}))
1834
assert("create table crash_q ($key_definitions)");
1835
$end_drop=$end_drop_keyword;
1836
$end_drop =~ s/%i/crash_q1%d/;
1837
$end_drop =~ s/%t/crash_q/;
1838
find_limit("index parts","max_index_parts",
1839
new query_table("create index crash_q1%d on crash_q (q0",
1845
assert("drop table crash_q");
1849
print "index parts: $limits{'max_index_parts'}\n";
1851
$end_drop=$end_drop_keyword;
1852
$end_drop =~ s/%i/crash_q2%d/;
1853
$end_drop =~ s/%t/crash_me/;
1855
find_limit("index part length","max_index_part_length",
1856
new query_many(["create table crash_q (q char(%d))",
1857
"create index crash_q2%d on crash_q (q)",
1858
"insert into crash_q values('%s')"],
1859
"select q from crash_q",
1862
"drop table crash_q"],
1863
min($limits{'max_char_size'},"+8192")));
1867
find_limit("index length","max_index_length",
1868
new query_index_length("create table crash_q ",
1869
"drop table crash_q",
1872
find_limit("max table row length (without blobs)","max_row_length",
1873
new query_row_length("crash_q ",
1875
"drop table crash_q",
1876
min($max_row_length,
1877
$limits{'max_columns'}*
1878
min($limits{'max_char_size'},255))));
1880
find_limit("table row length with nulls (without blobs)",
1881
"max_row_length_with_null",
1882
new query_row_length("crash_q ",
1884
"drop table crash_q",
1885
$limits{'max_row_length'}*2));
1887
find_limit("number of columns in order by","columns_in_order_by",
1888
new query_many(["create table crash_q (%F)",
1889
"insert into crash_q values(%v)",
1890
"insert into crash_q values(%v)"],
1891
"select * from crash_q order by %f",
1893
["drop table crash_q"],
1896
find_limit("number of columns in group by","columns_in_group_by",
1897
new query_many(["create table crash_q (%F)",
1898
"insert into crash_q values(%v)",
1899
"insert into crash_q values(%v)"],
1900
"select %f from crash_q group by %f",
1902
["drop table crash_q"],
1909
$dbh->do("drop table crash_me"); # Remove temporary table
1911
print "crash-me safe: $limits{'crash_me_safe'}\n";
1912
print "reconnected $reconnect_count times\n";
1914
$dbh->disconnect || warn $dbh->errstr;
1915
save_all_config_data();
1923
This program tries to find all limits and capabilities for a SQL
1924
server. As it will use the server in some 'unexpected' ways, one
1925
shouldn\'t have anything important running on it at the same time this
1926
program runs! There is a slight chance that something unexpected may
1929
As all used queries are legal according to some SQL standard. any
1930
reasonable SQL server should be able to run this test without any
1933
All questions is cached in $opt_dir/'server_name'.cfg that future runs will use
1934
limits found in previous runs. Remove this file if you want to find the
1935
current limits for your version of the database server.
1937
This program uses some table names while testing things. If you have any
1938
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
1939
they will be deleted by this test!
1941
$0 takes the following options:
1943
--help or --Information
1947
Don\'t ask any questions, quit on errors.
1949
--comment='some comment'
1950
Add this comment to the crash-me limit file
1952
--database='database' (Default $opt_database)
1953
Create test tables in this database.
1956
Save crash-me output in this directory
1959
Lots of printing to help debugging if something goes wrong.
1962
Reformat the crash-me limit file. crash-me is not run!
1965
Start test at once, without a warning screen and without questions.
1966
This is a option for the very brave.
1967
Use this in your cron scripts to test your database every night.
1970
Prints all queries that are executed. Mostly used for debugging crash-me.
1972
--log-queries-to-file='filename'
1973
Log full queries to file.
1975
--host='hostname' (Default $opt_host)
1976
Run tests on this host.
1978
--password='password'
1979
Password for the current user.
1982
Save states during each limit tests. This will make it possible to continue
1983
by restarting with the same options if there is some bug in the DBI or
1984
DBD driver that caused $0 to die!
1986
--server='server name' (Default $opt_server)
1987
Run the test on the given server.
1988
Known servers names are: Access, Adabas, AdabasD, Empress, Oracle, Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid or Sybase.
1989
For others $0 can\'t report the server version.
1992
User name to log into the SQL server.
1994
--start-cmd='command to restart server'
1995
Automaticly restarts server with this command if the server dies.
1997
--sleep='time in seconds' (Default $opt_sleep)
1998
Wait this long before restarting server.
2009
print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
2010
if (lc($opt_server) eq "mysql")
2014
This test should not crash MySQL if it was distributed together with the
2015
running MySQL version.
2016
If this is the case you can probably continue without having to worry about
2017
destroying something.
2020
elsif (lc($opt_server) eq "msql")
2023
This test will take down mSQL repeatedly while finding limits.
2024
To make this test easier, start mSQL in another terminal with something like:
2026
while (true); do /usr/local/mSQL/bin/msql2d ; done
2028
You should be sure that no one is doing anything important with mSQL and that
2029
you have privileges to restart it!
2030
It may take awhile to determinate the number of joinable tables, so prepare to
2034
elsif (lc($opt_server) eq "solid")
2037
This test will take down Solid server repeatedly while finding limits.
2038
You should be sure that no one is doing anything important with Solid
2039
and that you have privileges to restart it!
2041
If you are running Solid without logging and/or backup YOU WILL LOSE!
2042
Solid does not write data from the cache often enough. So if you continue
2043
you may lose tables and data that you entered hours ago!
2045
Solid will also take a lot of memory running this test. You will nead
2048
When doing the connect test Solid server or the perl api will hang when
2049
freeing connections. Kill this program and restart it to continue with the
2050
test. You don\'t have to use --restart for this case.
2054
print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
2055
print "with --restart until all tests have completed\n";
2058
elsif (lc($opt_server) eq "pg")
2061
This test will crash postgreSQL when calculating the number of joinable tables!
2062
You should be sure that no one is doing anything important with postgreSQL
2063
and that you have privileges to restart it!
2069
This test may crash $opt_server repeatedly while finding limits!
2070
You should be sure that no one is doing anything important with $opt_server
2071
and that you have privileges to restart it!
2076
Some of the tests you are about to execute may require a lot of
2077
memory. Your tests WILL adversely affect system performance. It's
2078
not uncommon that either this crash-me test program, or the actual
2079
database back-end, will DIE with an out-of-memory error. So might
2080
any other program on your system if it requests more memory at the
2083
Note also that while crash-me tries to find limits for the database server
2084
it will make a lot of queries that can't be categorized as 'normal'. It's
2085
not unlikely that crash-me finds some limit bug in your server so if you
2086
run this test you have to be prepared that your server may die during it!
2088
We, the creators of this utility, are not responsible in any way if your
2089
database server unexpectedly crashes while this program tries to find the
2090
limitations of your server. By accepting the following question with 'yes',
2091
you agree to the above!
2093
You have been warned!
2098
# No default reply here so no one can blame us for starting the test
2103
print "Start test (yes/no) ? ";
2104
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
2105
last if ($tmp =~ /^yes$/i);
2106
exit 1 if ($tmp =~ /^n/i);
2113
$name= `uname -s -r -m`;
2116
$name= `uname -s -m`;
2130
chomp($name); $name =~ s/[\n\r]//g;
2136
# Help functions that we need
2146
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
2147
{ PrintError => 0, AutoCommit => 1})))
2149
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
2152
print "Error: $DBI::errstr; $server->{'data_source'} - '$opt_user' - '$opt_password'\n";
2153
print "I got the above error when connecting to $opt_server\n";
2154
if (defined($object) && defined($object->{'limit'}))
2156
print "This check was done with limit: $object->{'limit'}.\nNext check will be done with a smaller limit!\n";
2159
save_config_data('crash_me_safe','no',"crash me safe");
2160
if ($opt_db_start_cmd)
2162
print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
2163
system("$opt_db_start_cmd");
2164
print "Waiting $opt_sleep seconds so the server can initialize\n";
2169
exit(1) if ($opt_batch_mode);
2170
print "Can you check/restart it so I can continue testing?\n";
2173
print "Continue test (yes/no) ? [yes] ";
2174
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
2175
$tmp = "yes" if ($tmp eq "");
2176
last if (index("yes",$tmp) >= 0);
2177
exit 1 if (index("no",$tmp) >= 0);
2185
# Check if the server is upp and running. If not, ask the user to restart it
2192
print "Checking connection\n" if ($opt_log_all_queries);
2193
# The following line will not work properly with interbase
2194
return if (defined($check_connect) && defined($dbh->do($check_connect)));
2195
$dbh->disconnect || warn $dbh->errstr;
2196
print "\nreconnecting\n" if ($opt_debug);
2199
$dbh=safe_connect($object);
2203
# print query if debugging
2208
$last_error=$DBI::errstr;
2211
if (length($query) > 130)
2213
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
2215
printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
2220
# Do one or many queries. Return 1 if all was ok
2221
# Note that all rows are executed (to ensure that we execute drop table commands)
2227
my($query,$ok,$retry_ok,$retry,@tmp,$sth);
2229
if (ref($queries) ne "ARRAY")
2231
push(@tmp,$queries);
2234
foreach $query (@$queries)
2236
printf "query1: %-80.80s ...(%d - %d)\n",$query,length($query),$retry_limit if ($opt_log_all_queries);
2237
print LOG "$query;\n" if ($opt_log);
2238
if (length($query) > $query_size)
2245
for ($retry=0; $retry < $retry_limit ; $retry++)
2247
if (! ($sth=$dbh->prepare($query)))
2249
print_query($query);
2250
$retry=100 if (!$server->abort_if_fatal_error());
2251
# Force a reconnect because of Access drop table bug!
2252
if ($retry == $retry_limit-2)
2254
print "Forcing disconnect to retry query\n" if ($opt_debug);
2255
$dbh->disconnect || warn $dbh->errstr;
2257
check_connect(); # Check that server is still up
2261
if (!$sth->execute())
2263
print_query($query);
2264
$retry=100 if (!$server->abort_if_fatal_error());
2265
# Force a reconnect because of Access drop table bug!
2266
if ($retry == $retry_limit-2)
2268
print "Forcing disconnect to retry query\n" if ($opt_debug);
2269
$dbh->disconnect || warn $dbh->errstr;
2271
check_connect(); # Check that server is still up
2275
$retry = $retry_limit;
2281
$ok=0 if (!$retry_ok);
2282
if ($query =~ /create/i && $server->reconnect_on_errors())
2284
print "Forcing disconnect to retry query\n" if ($opt_debug);
2285
$dbh->disconnect || warn $dbh->errstr;
2286
$dbh=safe_connect();
2294
# Do a query on a query package object.
2299
my($object,$limit)=@_;
2300
my ($query,$result,$retry,$sth);
2302
$query=$object->query($limit);
2303
$result=safe_query($query);
2309
if (defined($query=$object->check_query()))
2311
for ($retry=0 ; $retry < $retry_limit ; $retry++)
2313
printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
2314
print LOG "$query;\n" if ($opt_log);
2315
if (($sth= $dbh->prepare($query)))
2319
$result= $object->check($sth);
2324
print_query($query);
2329
print_query($query);
2331
$retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
2332
if ($retry == $retry_limit-2)
2334
print "Forcing discoennect to retry query\n" if ($opt_debug);
2335
$dbh->disconnect || warn $dbh->errstr;
2337
check_connect($object); # Check that server is still up
2339
$result=0; # Query failed
2342
return $result; # Server couldn't handle the query
2348
my ($prompt,$limit,@queries)=@_;
2350
if (!defined($limits{$limit}))
2352
save_config_data($limit,safe_query(\@queries) ? "yes" : "no",$prompt);
2354
print "$limits{$limit}\n";
2355
return $limits{$limit} ne "no";
2360
my ($prompt,$limit,@queries)=@_;
2362
if (!defined($limits{$limit}))
2364
save_config_data($limit,safe_query(\@queries) ? "no" : "yes",$prompt);
2366
print "$limits{$limit}\n";
2367
return $limits{$limit} ne "no";
2371
# Return true if one of the queries is ok
2375
my ($prompt,$limit,$queries)=@_;
2376
my ($query,$res,$result);
2378
if (!defined($limits{$limit}))
2381
foreach $query (@$queries)
2383
if (safe_query($query->[0]))
2385
$result= $query->[1];
2389
save_config_data($limit,$result,$prompt);
2391
print "$limits{$limit}\n";
2392
return $limits{$limit} ne "no";
2396
# Execute query and save result as limit value.
2400
my ($prompt,$limit,$query)=@_;
2403
if (!defined($limits{$limit}))
2405
$error=safe_query_result($query,"1",2);
2406
save_config_data($limit,$error ? "not supported" : $last_result,$prompt);
2408
print "$limits{$limit}\n";
2409
return $limits{$limit} ne "no";
2414
my ($prompt,$limit,$queries,$check,$clear)=@_;
2416
if (!defined($limits{$limit}))
2418
eval {undef($dbh->{AutoCommit})};
2421
if (safe_query(\@$queries))
2423
$rc = $dbh->rollback;
2425
$dbh->{AutoCommit} = 1;
2426
if (safe_query_result($check,"","")) {
2427
save_config_data($limit,"yes",$prompt);
2431
$dbh->{AutoCommit} = 1;
2433
save_config_data($limit,"error",$prompt);
2436
save_config_data($limit,"error",$prompt);
2438
$dbh->{AutoCommit} = 1;
2442
save_config_data($limit,"no",$prompt);
2446
print "$limits{$limit}\n";
2447
return $limits{$limit} ne "no";
2451
sub check_and_report
2453
my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
2456
$function=0 if (!defined($function));
2458
print "$prompt: " if (!defined($skip_prompt));
2459
if (!defined($limits{$limit}))
2461
$tmp=1-safe_query(\@$pre);
2462
$tmp=safe_query_result($query,$answer,$string_type) if (!$tmp);
2463
safe_query(\@$post);
2464
if ($function == 3) # Report error as 'no'.
2469
if ($function == 0 ||
2470
$tmp != 0 && $function == 1 ||
2471
$tmp == 0 && $function== 2)
2473
save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
2475
print "$limits{$limit}\n";
2476
return $function == 0 ? $limits{$limit} eq "yes" : 0;
2478
return 1; # more things to check
2480
print "$limits{$limit}\n";
2481
return 0 if ($function);
2482
return $limits{$limit} eq "yes";
2488
my ($prompt,$limit,@tests)=@_;
2489
my ($tmp,$test,$type);
2492
if (!defined($limits{$limit}))
2494
$type="no"; # Not supported
2495
foreach $test (@tests)
2497
my $tmp_type= shift(@$test);
2498
if (safe_query(\@$test))
2505
save_config_data($limit, $type, $prompt);
2507
print "$limits{$limit}\n";
2508
return $limits{$limit} ne "no";
2512
# Just execute the query and check values; Returns 1 if ok
2515
sub execute_and_check
2517
my ($pre,$query,$post,$answer,$string_type)=@_;
2520
$tmp=safe_query(\@$pre);
2521
$tmp=safe_query_result($query,$answer,$string_type) == 0 if ($tmp);
2522
safe_query(\@$post);
2527
# returns 0 if ok, 1 if error, -1 if wrong answer
2528
# Sets $last_result to value of query
2530
sub safe_query_result
2532
my ($query,$answer,$result_type)=@_;
2533
my ($sth,$row,$result,$retry);
2534
undef($last_result);
2536
printf "\nquery3: %-80.80s\n",$query if ($opt_log_all_queries);
2537
print LOG "$query;\n" if ($opt_log);
2538
for ($retry=0; $retry < $retry_limit ; $retry++)
2540
if (!($sth=$dbh->prepare($query)))
2542
print_query($query);
2543
if ($server->abort_if_fatal_error())
2545
check_connect(); # Check that server is still up
2548
check_connect(); # Check that server is still up
2553
print_query($query);
2554
if ($server->abort_if_fatal_error())
2556
check_connect(); # Check that server is still up
2559
check_connect(); # Check that server is still up
2567
if (!($row=$sth->fetchrow_arrayref))
2569
print "\nquery: $query didn't return any result\n" if ($opt_debug);
2571
return ($result_type == 8) ? 0 : 1;
2573
if(result_type == 8) {
2578
$last_result= $row->[0]; # Save for report_result;
2579
if ($result_type == 0) # Compare numbers
2581
$row->[0] =~ s/,/,/; # Fix if ',' is used instead of '.'
2582
if ($row->[0] != $answer && (abs($row->[0]- $answer)/
2583
(abs($row->[0]) + abs($answer))) > 0.01)
2588
elsif ($result_type == 1) # Compare where end space may differ
2590
$row->[0] =~ s/\s+$//;
2591
$result=-1 if ($row->[0] ne $answer);
2593
elsif ($result_type == 3) # This should be a exact match
2595
$result= -1 if ($row->[0] ne $answer);
2597
elsif ($result_type == 4) # If results should be NULL
2599
$result= -1 if (defined($row->[0]));
2601
elsif ($result_type == 5) # Result should have given prefix
2603
$result= -1 if (length($row->[0]) < length($answer) &&
2604
substring($row->[0],1,length($answer)) ne $answer);
2606
elsif ($result_type == 6) # Exact match but ignore errors
2608
$result= 1 if ($row->[0] ne $answer);
2610
elsif ($result_type == 7) # Compare against array of numbers
2612
if ($row->[0] != $answer->[0])
2620
while (($row=$sth->fetchrow_arrayref))
2622
$value=shift(@$answer);
2623
if (!defined($value))
2625
print "\nquery: $query returned to many results\n"
2630
if ($row->[0] != $value)
2636
if ($#$answer != -1)
2638
print "\nquery: $query returned too few results\n"
2645
print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
2646
if ($opt_debug && $result && $result_type != 7);
2651
# Find limit using binary search. This is a weighed binary search that
2652
# will prefere lower limits to get the server to crash as few times as possible
2657
my ($prompt,$limit,$query)=@_;
2658
my ($first,$end,$i,$tmp);
2660
if (defined($end=$limits{$limit}))
2662
print "$end (cache)\n";
2665
if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
2667
if (!safe_query($query->{'init'}))
2674
if (!limit_query($query,1)) # This must work
2676
print "\nMaybe fatal error: Can't check '$prompt' for limit=1\nerror: $last_error\n";
2681
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
2683
if (defined($end=$limits{'restart'}{'tohigh'}))
2686
print "\nRestarting this with low limit: $first and high limit: $end\n";
2687
delete $limits{'restart'};
2688
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
2692
$end= $query->max_limit();
2693
$i=int(($end+$first)/2);
2696
unless(limit_query($query,0+$end)) {
2697
while ($first < $end)
2699
print "." if ($opt_debug);
2700
save_config_data("restart",$i,"") if ($opt_restart);
2701
if (limit_query($query,$i))
2704
$i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
2709
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
2713
$end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
2714
if ($end >= $query->{'max_limit'} &&
2715
substr($query->{'max_limit'},0,1) eq '+')
2717
$end= $query->{'max_limit'};
2720
save_config_data($limit,$end,$prompt);
2721
delete $limits{'restart'};
2726
# Check that the query works!
2733
if (!safe_query($query))
2735
$query=join("; ",@$query) if (ref($query) eq "ARRAY");
2736
print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
2742
sub read_config_data
2744
my ($key,$limit,$prompt);
2745
if (-e $opt_config_file)
2747
open(CONFIG_FILE,"+<$opt_config_file") ||
2748
die "Can't open configure file $opt_config_file\n";
2749
print "Reading old values from cache: $opt_config_file\n";
2753
open(CONFIG_FILE,"+>>$opt_config_file") ||
2754
die "Can't create configure file $opt_config_file: $!\n";
2759
while (<CONFIG_FILE>)
2762
if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
2764
$key=$1; $limit=$2 ; $prompt=$3;
2765
if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
2767
if ($key !~ /restart/i)
2769
$limits{$key}=$limit;
2770
$prompts{$key}=length($prompt) ? substr($prompt,2) : "";
2771
delete $limits{'restart'};
2776
if ($limit > $limits{'restart'}{'tohigh'})
2778
$limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
2780
$limits{'restart'}{'tohigh'} = $limit;
2784
elsif (!/^\s*$/ && !/^\#/)
2786
die "Wrong config row: $_\n";
2792
sub save_config_data
2794
my ($key,$limit,$prompt)=@_;
2795
$prompts{$key}=$prompt;
2796
return if (defined($limits{$key}) && $limits{$key} eq $limit);
2797
if (!defined($limit) || $limit eq "")
2799
die "Undefined limit for $key\n";
2801
print CONFIG_FILE "$key=$limit\t# $prompt\n";
2802
$limits{$key}=$limit;
2804
if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
2805
($limits{'operating_system'} =~ /NT/))
2807
# If perl crashes in windows, everything is lost (Wonder why? :)
2809
open(CONFIG_FILE,"+>>$opt_config_file") ||
2810
die "Can't reopen configure file $opt_config_file: $!\n";
2815
sub save_all_config_data
2819
return if (!$limit_changed);
2820
open(CONFIG_FILE,">$opt_config_file") ||
2821
die "Can't create configure file $opt_config_file: $!\n";
2825
delete $limits{'restart'};
2827
print CONFIG_FILE "#This file is automaticly generated by crash-me $version\n\n";
2828
foreach $key (sort keys %limits)
2830
$tmp="$key=$limits{$key}";
2831
print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
2832
"# $prompts{$key}\n";
2840
my ($sth,$limit)=@_;
2843
return 0 if (!($row=$sth->fetchrow_arrayref));
2844
return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
2852
for ($i=1 ; $i <= $#_; $i++)
2854
$min=$_[$i] if ($min > $_[$i]);
2862
return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
2863
return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
2864
return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
2869
# Returns a list of statements to create a table in a portable manner
2870
# but still utilizing features in the databases.
2875
my($table_name,$fields,$index) = @_;
2876
my($query,$nr,$parts,@queries,@index);
2878
$query="create table $table_name (";
2880
foreach $field (@$fields)
2882
$query.= $field . ',';
2884
foreach $index (@$index)
2886
$index =~ /\(([^\(]*)\)$/i;
2888
if ($index =~ /^primary key/)
2890
if ($limits{'primary_key_in_create'} eq 'yes')
2892
$query.= $index . ',';
2897
"create unique index ${table_name}_prim on $table_name ($parts)");
2900
elsif ($index =~ /^unique/)
2902
if ($limits{'unique_in_create'} eq 'yes')
2904
$query.= "unique ($parts),";
2910
"create unique index ${table_name}_$nr on $table_name ($parts)");
2916
if ($limits{'index_in_create'} eq 'yes')
2918
$query.= "index ($parts),";
2924
"create index ${table_name}_$nr on $table_name ($1)");
2930
unshift(@queries,$query);
2936
# This is used by some query packages to change:
2939
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
2940
# %f -> q1,q2,q3....
2941
# %F -> q1 integer,q2 integer,q3 integer....
2945
my ($query,$limit)=@_;
2948
return $query if !(defined($query));
2949
$query =~ s/%d/$limit/g;
2952
$repeat= 'a' x $limit;
2953
$query =~ s/%s/$repeat/g;
2957
$repeat= '1,' x $limit;
2959
$query =~ s/%v/$repeat/g;
2964
for ($i=1 ; $i <= $limit ; $i++)
2969
$query =~ s/%f/$repeat/g;
2974
for ($i=1 ; $i <= $limit ; $i++)
2976
$repeat.="q$i integer,";
2979
$query =~ s/%F/$repeat/g;
2986
# Different query packages
2989
package query_repeat;
2993
my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
2994
$max_limit, $check, $offset)=@_;
2996
if (defined($init) && $#$init != -1)
2998
$self->{'init'}=$init;
3000
$self->{'query'}=$query;
3001
$self->{'add1'}=$add1;
3002
$self->{'add_mid'}=$add_mid;
3003
$self->{'add'}=$add;
3004
$self->{'add_end'}=$add_end;
3005
$self->{'end_query'}=$end_query;
3006
$self->{'cleanup'}=$cleanup;
3007
$self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
3008
$self->{'check'}=$check;
3009
$self->{'offset'}=$offset;
3010
$self->{'printf'}= ($add =~ /%d/);
3016
my ($self,$limit)=@_;
3017
if (!$self->{'printf'})
3019
return $self->{'query'} . ($self->{'add'} x $limit) .
3020
($self->{'add_end'} x $limit) . $self->{'end_query'};
3022
my ($tmp,$tmp2,$tmp3,$i);
3023
$tmp=$self->{'query'};
3024
if ($self->{'add1'})
3026
for ($i=0; $i < $limit ; $i++)
3028
$tmp3 = $self->{'add1'};
3033
$tmp .= " ".$self->{'add_mid'};
3036
for ($i=0; $i < $limit ; $i++)
3038
$tmp2 = $self->{'add'};
3044
($self->{'add_end'} x $limit) . $self->{'end_query'});
3051
$tmp=int(($main::limits{"query_size"}-length($self->{'query'})
3052
-length($self->{'add_mid'})-length($self->{'end_query'}))/
3053
(length($self->{'add1'})+
3054
length($self->{'add'})+length($self->{'add_end'})));
3055
return main::min($self->{'max_limit'},$tmp);
3062
my($tmp,$statement);
3063
$tmp=$self->{'cleanup'};
3064
foreach $statement (@$tmp)
3066
main::safe_query($statement) if (defined($statement) && length($statement));
3073
my $check=$self->{'check'};
3074
return &$check($sth,$self->{'limit'}) if (defined($check));
3088
my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
3090
$self->{'query'}=$query;
3091
$self->{'end_query'}=$end_query;
3092
$self->{'cleanup'}=$cleanup;
3093
$self->{'max_limit'}=$max_limit;
3094
$self->{'check'}=$check;
3102
$self->{'limit'}=$i;
3103
return "$self->{'query'}$i$self->{'end_query'}";
3109
return $self->{'max_limit'};
3116
foreach $statement ($self->{'$cleanup'})
3118
main::safe_query($statement) if (defined($statement) && length($statement));
3126
my $check=$self->{'check'};
3127
return &$check($sth,$self->{'limit'}) if (defined($check));
3137
# This package is used when testing CREATE TABLE!
3140
package query_table;
3144
my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
3145
$cleanup, $max_limit, $offset)=@_;
3147
$self->{'query'}=$query;
3148
$self->{'add'}=$add;
3149
$self->{'end_query'}=$end_query;
3150
$self->{'extra_init'}=$extra_init;
3151
$self->{'safe_query'}=$safe_query;
3152
$self->{'check'}=$check;
3153
$self->{'cleanup'}=$cleanup;
3154
$self->{'max_limit'}=$max_limit;
3155
$self->{'offset'}=$offset;
3162
my ($self,$limit)=@_;
3163
$self->{'limit'}=$limit;
3164
$self->cleanup(); # Drop table before create
3166
my ($tmp,$tmp2,$i,$query,@res);
3167
$tmp =$self->{'query'};
3168
$tmp =~ s/%d/$limit/g;
3169
for ($i=1; $i <= $limit ; $i++)
3171
$tmp2 = $self->{'add'};
3175
push(@res,$tmp . $self->{'end_query'});
3176
$tmp=$self->{'extra_init'};
3177
foreach $query (@$tmp)
3179
push(@res,main::fix_query($query,$limit));
3188
return $self->{'max_limit'};
3195
return main::fix_query($self->{'safe_query'},$self->{'limit'});
3201
my $check=$self->{'check'};
3202
return 0 if (!($row=$sth->fetchrow_arrayref));
3203
if (defined($check))
3205
return (defined($row->[0]) &&
3206
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
3212
# Remove table before and after create table query
3217
main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
3221
# Package to do many queries with %d, and %s substitution
3228
my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
3231
$self->{'query'}=$query;
3232
$self->{'safe_query'}=$safe_query;
3233
$self->{'check'}=$check_result;
3234
$self->{'cleanup'}=$cleanup;
3235
$self->{'max_limit'}=$max_limit;
3236
$self->{'offset'}=$offset;
3237
$self->{'safe_cleanup'}=$safe_cleanup;
3244
my ($self,$limit)=@_;
3245
my ($queries,$query,@res);
3246
$self->{'limit'}=$limit;
3247
$self->cleanup() if (defined($self->{'safe_cleanup'}));
3248
$queries=$self->{'query'};
3249
foreach $query (@$queries)
3251
push(@res,main::fix_query($query,$limit));
3259
return main::fix_query($self->{'safe_query'},$self->{'limit'});
3265
my($tmp,$statement);
3266
return if (!defined($self->{'cleanup'}));
3267
$tmp=$self->{'cleanup'};
3268
foreach $statement (@$tmp)
3270
if (defined($statement) && length($statement))
3272
main::safe_query(main::fix_query($statement,$self->{'limit'}));
3282
return 0 if (!($row=$sth->fetchrow_arrayref));
3283
$check=$self->{'check'};
3284
if (defined($check))
3286
return (defined($row->[0]) &&
3287
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
3295
return $self->{'max_limit'};
3299
# Used to find max supported row length
3302
package query_row_length;
3306
my ($type,$create,$null,$drop,$max_limit)=@_;
3308
$self->{'table_name'}=$create;
3309
$self->{'null'}=$null;
3310
$self->{'cleanup'}=$drop;
3311
$self->{'max_limit'}=$max_limit;
3318
my ($self,$limit)=@_;
3319
my ($res,$values,$size,$length,$i);
3320
$self->{'limit'}=$limit;
3323
$size=main::min($main::limits{'max_char_size'},255);
3324
$size = 255 if (!$size); # Safety
3325
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
3327
$res.= "q$i char($size) $self->{'null'},";
3328
$values.="'" . ('a' x $size) . "',";
3330
if ($length < $limit)
3332
$size=$limit-$length;
3333
$res.= "q$i char($size) $self->{'null'},";
3334
$values.="'" . ('a' x $size) . "',";
3338
return ["create table " . $self->{'table_name'} . " ($res)",
3339
"insert into " . $self->{'table_name'} . " values ($values)"];
3345
return $self->{'max_limit'};
3351
main::safe_query($self->{'cleanup'});
3366
# Used to find max supported index length
3369
package query_index_length;
3373
my ($type,$create,$drop,$max_limit)=@_;
3375
$self->{'create'}=$create;
3376
$self->{'cleanup'}=$drop;
3377
$self->{'max_limit'}=$max_limit;
3384
my ($self,$limit)=@_;
3385
my ($res,$size,$length,$i,$parts,$values);
3386
$self->{'limit'}=$limit;
3388
$res=$parts=$values="";
3389
$size=main::min($main::limits{'max_index_part_length'},$main::limits{'max_char_size'});
3390
$size=1 if ($size == 0); # Avoid infinite loop errors
3391
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
3393
$res.= "q$i char($size) not null,";
3395
$values.= "'" . ('a' x $size) . "',";
3397
if ($length < $limit)
3399
$size=$limit-$length;
3400
$res.= "q$i char($size) not null,";
3402
$values.= "'" . ('a' x $size) . "',";
3407
if ($main::limits{'unique_in_create'} eq 'yes')
3409
return [$self->{'create'} . "($res,unique ($parts))",
3410
"insert into crash_q values($values)"];
3412
return [$self->{'create'} . "($res)",
3413
"create index crash_q_index on crash_q ($parts)",
3414
"insert into crash_q values($values)"];
3420
return $self->{'max_limit'};
3426
main::safe_query($self->{'cleanup'});
3442
# OID test instead of / in addition to _rowid
added
removed
sql-bench/crash-me.sh
