3
# Copyright (C) 2000-2006 MySQL AB
5
# This library is free software; you can redistribute it and/or
6
# modify it under the terms of the GNU Library General Public
7
# License as published by the Free Software Foundation; version 2
10
# This library is distributed in the hope that it will be useful,
11
# but WITHOUT ANY WARRANTY; without even the implied warranty of
12
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13
# Library General Public License for more details.
15
# You should have received a copy of the GNU Library General Public
16
# License along with this library; if not, write to the Free
17
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
20
# Written by Monty for the TCX/Monty Program/Detron benchmark suite.
21
# Empress and PostgreSQL patches by Luuk de Boer
22
# Extensions for ANSI SQL and Mimer by Bengt Gunne
23
# Some additions and corrections by Matthias Urlich
25
# This programs tries to find all limits for a sql server
26
# It gets the name from what it does to most servers :)
28
# Be sure to use --help before running this!
30
# If you want to add support for another server, add a new package for the
31
# server in server-cfg. You only have to support the 'new' and 'version'
32
# functions. new doesn't need to have any limits if one doesn't want to
37
# CMT includes types and functions which are synonyms for other types
38
# and functions, including those in SQL9x. It should label those synonyms
39
# as such, and clarify ones such as "mediumint" with comments such as
40
# "3-byte int" or "same as xxx".
48
$pwd = cwd(); $pwd = "." if ($pwd eq '');
49
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
51
our $opt_batch_mode=0;
52
our $opt_check_server=0; # Check if server is alive before each query
54
our $opt_config_file="";
55
our $opt_connect_options="";
56
our $opt_database="test";
57
our $opt_db_start_cmd=""; # the db server start command
59
our $opt_dir="limits";
60
our $opt_fix_limit_file=0;
63
our $opt_host="localhost";
64
our $opt_Information=0;
65
our $opt_log_all_queries=0;
66
our $opt_log_queries_to_file="";
72
our $opt_server="mysql";
73
our $opt_sleep=10; # time to sleep while starting the db server
74
our $opt_socket=""; # time to sleep while starting the db server
80
$limit_changed=0; # For configure file
82
$limits{'crash_me_safe'}='yes';
83
$prompts{'crash_me_safe'}='crash me safe';
84
$limits{'operating_system'}= machine();
85
$prompts{'operating_system'}='crash-me tested on';
88
GetOptions("Information","help","server=s","debug","user=s","password=s",
89
"database=s","restart","force","quick","log-all-queries","comment=s",
90
"host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","suffix=s",
91
"batch-mode","config-file=s","log-queries-to-file=s","check-server",
92
"version", "connect-options=s", "machine=s", "socket=s",
93
"verbose!" => \$opt_verbose) || usage();
96
# Find limit using binary search. This is a weighed binary search that
97
# will prefere lower limits to get the server to crash as
98
# few times as possible
101
my ($prompt,$limit,$query)=@_;
102
my ($first,$end,$i,$tmp,@tmp_array, $queries);
104
if (defined($end=$limits{$limit}))
106
print "$end (cache)\n";
109
save_incomplete($limit,$prompt);
110
add_log($limit,"We are trying (example with N=5):");
111
$queries = $query->query(5);
112
if (ref($queries) ne "ARRAY")
114
push(@tmp_array,$queries);
115
$queries= \@tmp_array;
117
foreach $tmp (@$queries)
118
{ add_log($limit,repr_query($tmp)); }
120
if (defined($queries = $query->check_query()))
122
if (ref($queries) ne "ARRAY")
125
push(@tmp_array,$queries);
126
$queries= \@tmp_array;
128
foreach $tmp (@$queries)
129
{ add_log($limit,repr_query($tmp)); }
131
if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
133
if (!safe_query_l($limit,$query->{'init'}))
140
if (!limit_query($query,1)) # This must work
142
print "\nMaybe fatal error: Can't check '$prompt' for limit=1\n".
143
"error: $last_error\n";
148
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
150
if (defined($end=$limits{'restart'}{'tohigh'}))
153
print "\nRestarting this with low limit: $first and high limit: $end\n";
154
delete $limits{'restart'};
155
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
159
$end= $query->max_limit();
160
$i=int(($end+$first)/2);
163
unless(limit_query($query,0+$end)) {
164
while ($first < $end)
166
print "." if ($opt_debug);
167
save_config_data("restart",$i,"") if ($opt_restart);
168
if (limit_query($query,$i))
171
$log_str .= " $i:OK";
172
$i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
177
$log_str .= " $i:FAIL";
178
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
182
$end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
183
if ($end >= $query->{'max_limit'} &&
184
substr($query->{'max_limit'},0,1) eq '+')
186
$end= $query->{'max_limit'};
189
add_log($limit,$log_str);
190
save_config_data($limit,$end,$prompt);
191
delete $limits{'restart'};
195
usage() if ($opt_help || $opt_Information);
197
version() && exit(0) if ($opt_version);
199
$opt_suffix = '-'.$opt_suffix if (length($opt_suffix) != 0);
200
$opt_config_file = "$pwd/$opt_dir/$opt_server$opt_suffix.cfg"
201
if (length($opt_config_file) == 0);
203
$safe_query_result_log='';
208
if ($opt_fix_limit_file)
210
print "Fixing limit file for $opt_server\n";
213
save_all_config_data();
217
$server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc, machine_part(), $opt_socket, $opt_connect_options);
218
$opt_server=$server->{'cmp_name'};
220
$|=1; # For debugging
222
print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
223
print "I hope you didn't have anything important running on this server....\n";
225
if ($limit_changed) # Must have been restarted
227
save_config_data('crash_me_safe','no',"crash me safe");
230
if (!$opt_force && !$opt_batch_mode)
236
print "Using --force. I assume you know what you are doing...\n";
240
save_config_data('crash_me_version',$version,"crash me version");
243
save_config_data('server_version',$server_version,"server version");
245
if (length($opt_comment))
247
save_config_data('user_comment',$opt_comment,"comment");
251
if (length($opt_log_queries_to_file))
253
open(LOG,">$opt_log_queries_to_file") ||
254
die "Can't open file $opt_log_queries_to_file\n";
259
# Set up some limits that's regared as unlimited
260
# We don't want to take up all resources from the server...
263
$max_connections="+1000"; # Number of simultaneous connections
264
$max_buffer_size="+16000000"; # size of communication buffer.
265
$max_string_size="+8000000"; # Enough for this test
266
$max_name_length="+512"; # Actually 256, but ...
267
$max_keys="+64"; # Probably too big.
268
$max_join_tables="+64"; # Probably too big.
269
$max_columns="+8192"; # Probably too big.
270
$max_row_length=$max_string_size;
271
$max_key_length="+8192"; # Big enough
272
$max_order_by="+64"; # Big enough
273
$max_expressions="+10000";
274
$max_big_expressions="+100";
275
$max_stacked_expressions="+2000";
276
$query_size=$max_buffer_size;
277
$longreadlen=16000000; # For retrieval buffer
281
# First do some checks that needed for the rest of the benchmark
283
use sigtrap; # Must be removed with perl5.005_2 on Win98
284
$SIG{PIPE} = 'IGNORE';
287
$problem_counter +=1;
288
if ($problem_counter >= 100) {
289
die("Too many problems, try to restart");
297
# Test if the database require RESTRICT/CASCADE after DROP TABLE
300
# Really remove the crash_me table
301
$prompt="drop table require cascade/restrict";
303
$dbh->do("drop table crash_me");
304
$dbh->do("drop table crash_me cascade");
305
if (!safe_query_l('drop_requires_cascade',
306
["create table crash_me (a integer not null)",
307
"drop table crash_me"]))
309
$dbh->do("drop table crash_me cascade");
310
if (safe_query_l('drop_requires_cascade',
311
["create table crash_me (a integer not null)",
312
"drop table crash_me cascade"]))
314
save_config_data('drop_requires_cascade',"yes","$prompt");
315
$drop_attr="cascade";
319
die "Can't create and drop table 'crash_me'\n";
324
save_config_data('drop_requires_cascade',"no","$prompt");
328
# Remove tables from old runs
329
$dbh->do("drop table crash_me $drop_attr");
330
$dbh->do("drop table crash_me2 $drop_attr");
331
$dbh->do("drop table crash_me3 $drop_attr");
332
$dbh->do("drop table crash_q $drop_attr");
333
$dbh->do("drop table crash_q1 $drop_attr");
335
$prompt="Tables without primary key";
336
if (!safe_query_l('no_primary_key',
337
["create table crash_me (a integer not null,b char(10) not null)",
338
"insert into crash_me (a,b) values (1,'a')"]))
340
if (!safe_query_l('no_primary_key',
341
["create table crash_me (a integer not null,b char(10) not null".
342
", primary key (a))",
343
"insert into crash_me (a,b) values (1,'a')"]))
345
die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
347
save_config_data('no_primary_key',"no",$prompt);
351
save_config_data('no_primary_key',"yes",$prompt);
355
# Define strings for character NULL and numeric NULL used in expressions
357
$char_null=$server->{'char_null'};
358
$numeric_null=$server->{'numeric_null'};
359
if ($char_null eq '')
363
if ($numeric_null eq '')
365
$numeric_null="NULL";
368
print "$prompt: $limits{'no_primary_key'}\n";
370
report("SELECT without FROM",'select_without_from',"select 1");
371
if ($limits{'select_without_from'} ne "yes")
373
$end_query=" from crash_me";
374
$check_connect="select a from crash_me";
379
$check_connect="select 1";
382
assert_crash($check_connect);
383
assert_crash("select a from crash_me where b<'b'");
385
report("Select constants",'select_constants',"select 1 $end_query");
386
report("Select table_name.*",'table_wildcard',
387
"select crash_me.* from crash_me");
388
report("Allows \' and \" as string markers",'quote_with_"',
389
'select a from crash_me where b<"c"');
390
check_and_report("Double '' as ' in strings",'double_quotes',[],
391
"select 'Walker''s' $end_query",[],"Walker's",1);
392
check_and_report("Multiple line strings","multi_strings",[],
393
"select a from crash_me where b < 'a'\n'b'",[],"1",0);
394
check_and_report("\" as identifier quote (ANSI SQL)",'quote_ident_with_"',[],
395
'select "A" from crash_me',[],"A",0);
396
check_and_report("\` as identifier quote",'quote_ident_with_`',[],
397
'select `A` from crash_me',[],"1",0);
398
check_and_report("[] as identifier quote",'quote_ident_with_[',[],
399
'select [A] from crash_me',[],"1",0);
400
report('Double "" in identifiers as "','quote_ident_with_dbl_"',
401
'create table crash_me1 ("abc""d" integer)',
402
'drop table crash_me1');
404
report("Column alias","column_alias","select a as ab from crash_me");
405
report("Table alias","table_alias","select b.a from crash_me as b");
406
report("Functions",'functions',"select 1+1 $end_query");
407
report("Group functions",'group_functions',"select count(*) from crash_me");
408
report("Group functions with distinct",'group_distinct_functions',
409
"select count(distinct a) from crash_me");
410
report("Group functions with several distinct",'group_many_distinct_functions',
411
"select count(distinct a), count(distinct b) from crash_me");
412
report("Group by",'group_by',"select a from crash_me group by a");
413
report("Group by position",'group_by_position',
414
"select a from crash_me group by 1");
415
report("Group by alias",'group_by_alias',
416
"select a as ab from crash_me group by ab");
417
report("Group on unused column",'group_on_unused',
418
"select count(*) from crash_me group by a");
420
report("Order by",'order_by',"select a from crash_me order by a");
421
report("Order by position",'order_by_position',
422
"select a from crash_me order by 1");
423
report("Order by function","order_by_function",
424
"select a from crash_me order by a+1");
425
report("Order by on unused column",'order_on_unused',
426
"select b from crash_me order by a");
427
# little bit deprecated
428
#check_and_report("Order by DESC is remembered",'order_by_remember_desc',
429
# ["create table crash_q (s int,s1 int)",
430
# "insert into crash_q values(1,1)",
431
# "insert into crash_q values(3,1)",
432
# "insert into crash_q values(2,1)"],
433
# "select s,s1 from crash_q order by s1 DESC,s",
434
# ["drop table crash_q $drop_attr"],[3,2,1],7,undef(),3);
435
report("Compute",'compute',
436
"select a from crash_me order by a compute sum(a) by a");
437
report("INSERT with Value lists",'insert_multi_value',
438
"create table crash_q (s char(10))",
439
"insert into crash_q values ('a'),('b')",
440
"drop table crash_q $drop_attr");
441
report("INSERT with set syntax",'insert_with_set',
442
"create table crash_q (a integer)",
443
"insert into crash_q SET a=1",
444
"drop table crash_q $drop_attr");
445
report("INSERT with DEFAULT","insert_with_default",
446
"create table crash_me_q (a int)",
447
"insert into crash_me_q (a) values (DEFAULT)",
448
"drop table crash_me_q $drop_attr");
450
report("INSERT with empty value list","insert_with_empty_value_list",
451
"create table crash_me_q (a int)",
452
"insert into crash_me_q (a) values ()",
453
"drop table crash_me_q $drop_attr");
455
report("INSERT DEFAULT VALUES","insert_default_values",
456
"create table crash_me_q (a int)",
457
"insert into crash_me_q DEFAULT VALUES",
458
"drop table crash_me_q $drop_attr");
460
report("allows end ';'","end_colon", "select * from crash_me;");
461
try_and_report("LIMIT number of rows","select_limit",
463
"select * from crash_me limit 1"],
465
"select TOP 1 * from crash_me"]);
466
report("SELECT with LIMIT #,#","select_limit2",
467
"select * from crash_me limit 1,1");
468
report("SELECT with LIMIT # OFFSET #",
469
"select_limit3", "select * from crash_me limit 1 offset 1");
471
# The following alter table commands MUST be kept together!
472
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))"))
474
report("Alter table add column",'alter_add_col',
475
"alter table crash_q add d integer");
476
report_one("Alter table add many columns",'alter_add_multi_col',
477
[["alter table crash_q add (f integer,g integer)","yes"],
478
["alter table crash_q add f integer, add g integer","with add"],
479
["alter table crash_q add f integer,g integer","without add"]] );
480
report("Alter table change column",'alter_change_col',
481
"alter table crash_q change a e char(50)");
483
# informix can only change data type with modify
484
report_one("Alter table modify column",'alter_modify_col',
485
[["alter table crash_q modify c1 CHAR(20)","yes"],
486
["alter table crash_q alter c1 CHAR(20)","with alter"]]);
487
report("Alter table alter column default",'alter_alter_col',
488
"alter table crash_q alter b set default 10");
489
report_one("Alter table drop column",'alter_drop_col',
490
[["alter table crash_q drop column b","yes"],
491
["alter table crash_q drop column b restrict",
492
"with restrict/cascade"]]);
493
report("Alter table rename table",'alter_rename_table',
494
"alter table crash_q rename to crash_q1");
496
# Make sure both tables will be dropped, even if rename fails.
497
$dbh->do("drop table crash_q1 $drop_attr");
498
$dbh->do("drop table crash_q $drop_attr");
500
report("rename table","rename_table",
501
"create table crash_q (a integer, b integer,c1 CHAR(10))",
502
"rename table crash_q to crash_q1",
503
"drop table crash_q1 $drop_attr");
504
# Make sure both tables will be dropped, even if rename fails.
505
$dbh->do("drop table crash_q1 $drop_attr");
506
$dbh->do("drop table crash_q $drop_attr");
508
report("truncate","truncate_table",
509
"create table crash_q (a integer, b integer,c1 CHAR(10))",
510
"truncate table crash_q",
511
"drop table crash_q $drop_attr");
513
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))") &&
514
$dbh->do("create table crash_q1 (a integer, b integer,c1 CHAR(10) not null)"))
516
report("Alter table add constraint",'alter_add_constraint',
517
"alter table crash_q add constraint c2 check(a > b)");
518
report_one("Alter table drop constraint",'alter_drop_constraint',
519
[["alter table crash_q drop constraint c2","yes"],
520
["alter table crash_q drop constraint c2 restrict",
521
"with restrict/cascade"]]);
522
report("Alter table add unique",'alter_add_unique',
523
"alter table crash_q add constraint u1 unique(c1)");
524
try_and_report("Alter table drop unique",'alter_drop_unique',
526
"alter table crash_q drop constraint u1"],
527
["with constraint and restrict/cascade",
528
"alter table crash_q drop constraint u1 restrict"],
530
"alter table crash_q drop key u1"]);
531
try_and_report("Alter table add primary key",'alter_add_primary_key',
533
"alter table crash_q1 add constraint p1 primary key(c1)"],
534
["with add primary key",
535
"alter table crash_q1 add primary key(c1)"]);
536
report("Alter table add foreign key",'alter_add_foreign_key',
537
"alter table crash_q add constraint f1 foreign key(c1) references crash_q1(c1)");
538
try_and_report("Alter table drop foreign key",'alter_drop_foreign_key',
539
["with drop constraint",
540
"alter table crash_q drop constraint f1"],
541
["with drop constraint and restrict/cascade",
542
"alter table crash_q drop constraint f1 restrict"],
543
["with drop foreign key",
544
"alter table crash_q drop foreign key f1"]);
545
try_and_report("Alter table drop primary key",'alter_drop_primary_key',
547
"alter table crash_q1 drop constraint p1 restrict"],
549
"alter table crash_q1 drop primary key"]);
551
$dbh->do("drop table crash_q $drop_attr");
552
$dbh->do("drop table crash_q1 $drop_attr");
554
check_and_report("Case insensitive compare","case_insensitive_strings",
555
[],"select b from crash_me where b = 'A'",[],'a',1);
556
check_and_report("Ignore end space in compare","ignore_end_space",
557
[],"select b from crash_me where b = 'a '",[],'a',1);
558
check_and_report("Group on column with null values",'group_by_null',
559
["create table crash_q (s char(10))",
560
"insert into crash_q values(null)",
561
"insert into crash_q values(null)"],
562
"select count(*),s from crash_q group by s",
563
["drop table crash_q $drop_attr"],2,0);
566
if (!defined($limits{'having'}))
567
{ # Complicated because of postgreSQL
568
if (!safe_query_result_l("having",
569
"select a from crash_me group by a having a > 0",1,0))
571
if (!safe_query_result_l("having",
572
"select a from crash_me group by a having a < 0",
574
{ save_config_data("having","error",$prompt); }
576
{ save_config_data("having","yes",$prompt); }
579
{ save_config_data("having","no",$prompt); }
581
print "$prompt: $limits{'having'}\n";
583
if ($limits{'having'} eq 'yes')
585
report("Having with group function","having_with_group",
586
"select a from crash_me group by a having count(*) = 1");
589
if ($limits{'column_alias'} eq 'yes')
591
report("Order by alias",'order_by_alias',
592
"select a as ab from crash_me order by ab");
593
if ($limits{'having'} eq 'yes')
595
report("Having on alias","having_with_alias",
596
"select a as ab from crash_me group by a having ab > 0");
599
report("binary numbers (0b1001)","binary_numbers","select 0b1001 $end_query");
600
report("hex numbers (0x41)","hex_numbers","select 0x41 $end_query");
601
report("binary strings (b'0110')","binary_strings","select b'0110' $end_query");
602
report("hex strings (x'1ace')","hex_strings","select x'1ace' $end_query");
604
report_result("Value of logical operation (1=1)","logical_value",
605
"select (1=1) $end_query");
607
report_result("Value of TRUE","value_of_true","select TRUE $end_query");
608
report_result("Value of FALSE","value_of_false","select FALSE $end_query");
610
$logical_value= $limits{'logical_value'};
614
if ($res=safe_query_l('has_true_false',"select (1=1)=true $end_query")) {
618
save_config_data('has_true_false',$result,"TRUE and FALSE");
621
# Check how many connections the server can handle:
622
# We can't test unlimited connections, because this may take down the
626
$prompt="Simultaneous connections (installation default)";
628
if (defined($limits{'connections'}))
630
print "$limits{'connections'}\n";
636
for ($i=1; $i < $max_connections ; $i++)
638
if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
639
{ PrintError => 0})))
641
print "Last connect error: $DBI::errstr\n" if ($opt_debug);
644
$dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
645
print "." if ($opt_debug);
649
save_config_data('connections',$i,$prompt);
650
foreach $dbh (@connect)
652
print "#" if ($opt_debug);
653
$dbh->disconnect || warn $dbh->errstr; # close connection
656
$#connect=-1; # Free connections
660
print "Can't connect to server: $DBI::errstr.".
661
" Please start it and try again\n";
664
$dbh=retry_connect();
669
# Check size of communication buffer, strings...
672
$prompt="query size";
674
if (!defined($limits{'query_size'}))
678
$end=$max_buffer_size;
679
$select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';
681
assert_crash($query . "$select$end_query");
683
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
685
if ($limits{'restart'}{'tohigh'})
687
$end = $limits{'restart'}{'tohigh'} - 1;
688
print "\nRestarting this with low limit: $first and high limit: $end\n";
689
delete $limits{'restart'};
690
$first=$first+int(($end-$first+4)/5); # Prefere lower on errors
692
for ($i=$first ; $i < $end ; $i*=2)
694
#printf "Trying query %s %d with %d spaces\n",$query,$select,($i - length($query)-length($end_query) -1);
695
last if (!safe_query($query .
696
(" " x ($i - length($query)-length($end_query) -1))
697
. "$select$end_query"));
699
save_config_data("restart",$i,"") if ($opt_restart);
703
if ($i < $max_buffer_size)
705
while ($first != $end)
707
$i=int(($first+$end+1)/2);
708
if (safe_query($query .
709
(" " x ($i - length($query)-length($end_query) -1)) .
710
"$select$end_query"))
720
save_config_data('query_size',$end,$prompt);
722
$query_size=$limits{'query_size'};
724
print "$limits{'query_size'}\n";
727
# Check for reserved words
730
check_reserved_words($dbh);
733
# Test database types
736
@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
739
"integer","int","smallint",
740
"numeric(9,2)","decimal(6,2)","dec(6,2)",
741
"bit", "bit(2)","bit varying(2)","float","float(8)","real",
742
"double precision", "date","time","timestamp",
743
"interval year", "interval year to month",
745
"interval day", "interval day to hour", "interval day to minute",
746
"interval day to second",
747
"interval hour", "interval hour to minute",
748
"interval hour to second",
749
"interval minute", "interval minute to second",
751
"national character varying(20)",
752
"national character(20)","nchar(1)",
753
"national char varying(20)","nchar varying(20)",
754
"national character varying(20)",
755
"timestamp with time zone");
756
@odbc_types=("binary(1)","varbinary(1)","tinyint","bigint",
758
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
760
"long varchar(1)", "varchar2(257)",
761
"mediumint","middleint","int unsigned",
762
"int1","int2","int3","int4","int8","uint",
763
"money","smallmoney","float4","float8","smallfloat",
764
"float(6,2)","double",
765
"enum('red')","set('red')", "int(5) zerofill", "serial",
766
"char(10) binary","int not null auto_increment,unique(q)",
767
"abstime","year","datetime","smalldatetime","timespan","reltime",
769
"int not null identity,unique(q)",
771
"box","bool","circle","polygon","point","line","lseg","path",
772
"interval", "inet", "cidr", "macaddr",
775
"varchar2(16)","nvarchar2(16)","number(9,2)","number(9)",
776
"number", "long","raw(16)","long raw","rowid","mlslabel","clob",
780
@types=(["sql",\@sql_types],
781
["odbc",\@odbc_types],
782
["extra",\@extra_types]);
784
foreach $types (@types)
786
print "\nSupported $types->[0] types\n";
788
foreach $use_type (@$tmp)
791
$type =~ s/\(.*\)/(1 arg)/;
792
if (index($use_type,",")>= 0)
794
$type =~ s/\(1 arg\)/(2 arg)/;
796
if (($tmp2=index($type,",unique")) >= 0)
798
$type=substr($type,0,$tmp2);
802
$tmp2 =~ s/_not_null//g;
803
report("Type $type","type_$types->[0]_$tmp2",
804
"create table crash_q (q $use_type)",
805
"drop table crash_q $drop_attr");
810
# Test some type limits
814
check_and_report("Remembers end space in char()","remember_end_space",
815
["create table crash_q (a char(10))",
816
"insert into crash_q values('hello ')"],
817
"select a from crash_q where a = 'hello '",
818
["drop table crash_q $drop_attr"],
821
check_and_report("Remembers end space in varchar()",
822
"remember_end_space_varchar",
823
["create table crash_q (a varchar(10))",
824
"insert into crash_q values('hello ')"],
825
"select a from crash_q where a = 'hello '",
826
["drop table crash_q $drop_attr"],
829
if (($limits{'type_extra_float(2_arg)'} eq "yes" ||
830
$limits{'type_sql_decimal(2_arg)'} eq "yes") &&
831
(!defined($limits{'storage_of_float'})))
833
my $type=$limits{'type_extra_float(2_arg)'} eq "yes" ? "float(4,1)" :
835
my $result="undefined";
836
if (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
837
"insert into crash_q values(1.14)"],
838
"select q1 from crash_q",
839
["drop table crash_q $drop_attr"],1.1,0) &&
840
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
841
"insert into crash_q values(1.16)"],
842
"select q1 from crash_q",
843
["drop table crash_q $drop_attr"],1.1,0))
847
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
848
"insert into crash_q values(1.14)"],
849
"select q1 from crash_q",
850
["drop table crash_q $drop_attr"],1.1,0) &&
851
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
852
"insert into crash_q values(1.16)"],
853
"select q1 from crash_q",
854
["drop table crash_q $drop_attr"],1.2,0))
858
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
859
"insert into crash_q values(1.14)"],
860
"select q1 from crash_q",
861
["drop table crash_q $drop_attr"],1.14,0) &&
862
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
863
"insert into crash_q values(1.16)"],
864
"select q1 from crash_q",
865
["drop table crash_q $drop_attr"],1.16,0))
869
$prompt="Storage of float values";
870
print "$prompt: $result\n";
871
save_config_data("storage_of_float", $result, $prompt);
874
try_and_report("Type for row id", "rowid",
876
"create table crash_q (a rowid)",
877
"drop table crash_q $drop_attr"],
879
"create table crash_q (a int not null auto_increment".
880
", primary key(a))","drop table crash_q $drop_attr"],
882
"create table crash_q (a oid, primary key(a))",
883
"drop table crash_q $drop_attr"],
885
"create table crash_q (a serial, primary key(a))",
886
"drop table crash_q $drop_attr"]);
888
try_and_report("Automatic row id", "automatic_rowid",
890
"create table crash_q (a int not null, primary key(a))",
891
"insert into crash_q values (1)",
892
"select _rowid from crash_q",
893
"drop table crash_q $drop_attr"]);
900
(["+, -, * and /","+","5*3-4/2+1",14,0],
901
["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
902
["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
903
["searched CASE","searched_case",
904
"case when 1 > 2 then 'false' when 2 > 1 then 'true' end", "true",1],
905
["simple CASE","simple_case",
906
"case 2 when 1 then 'false' when 2 then 'true' end", "true",1],
907
["CAST","cast","CAST(1 as CHAR)","1",1],
908
["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
909
["CHAR_LENGTH","char_length","char_length(b)","1",0],
910
["CHAR_LENGTH(constant)","char_length(constant)",
911
"char_length('abcd')","4",0],
912
["COALESCE","coalesce","coalesce($char_null,'bcd','qwe')","bcd",1],
913
["CURRENT_DATE","current_date","current_date",0,2],
914
["CURRENT_TIME","current_time","current_time",0,2],
915
["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
916
["EXTRACT","extract_sql",
917
"extract(minute from timestamp '2000-02-23 18:43:12.987')",43,0],
918
["LOCALTIME","localtime","localtime",0,2],
919
["LOCALTIMESTAMP","localtimestamp","localtimestamp",0,2],
920
["LOWER","lower","LOWER('ABC')","abc",1],
921
["NULLIF with strings","nullif_string",
922
"NULLIF(NULLIF('first','second'),'first')",undef(),4],
923
["NULLIF with numbers","nullif_num","NULLIF(NULLIF(1,2),1)",undef(),4],
924
["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
925
["POSITION","position","position('ll' in 'hello')",3,0],
926
["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",3],
927
["UPPER","upper","UPPER('abc')","ABC",1],
928
["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
932
(["ASCII", "ascii", "ASCII('A')","65",0],
933
["CHAR", "char", "CHAR(65)" ,"A",1],
934
["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
935
["DIFFERENCE()","difference","difference('abc','abe')",3,0],
936
["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
937
["LEFT","left","left('abcd',2)","ab",1],
938
["LTRIM","ltrim","ltrim(' abcd')","abcd",1],
939
["REAL LENGTH","length","length('abcd ')","5",0],
940
["ODBC LENGTH","length_without_space","length('abcd')","4",0],
941
["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
942
["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
943
["LCASE","lcase","lcase('ABC')","abc",1],
944
["REPEAT","repeat","repeat('ab',3)","ababab",1],
945
["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
946
["RIGHT","right","right('abcd',2)","cd",1],
947
["RTRIM","rtrim","rtrim(' abcd ')"," abcd",1],
948
["SPACE","space","space(5)"," ",3],
949
["SOUNDEX","soundex","soundex('hello')",0,2],
950
["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
951
["UCASE","ucase","ucase('abc')","ABC",1],
953
["ABS","abs","abs(-5)",5,0],
954
["ACOS","acos","acos(0)","1.570796",0],
955
["ASIN","asin","asin(1)","1.570796",0],
956
["ATAN","atan","atan(1)","0.785398",0],
957
["ATAN2","atan2","atan2(1,0)","1.570796",0],
958
["CEILING","ceiling","ceiling(-4.5)",-4,0],
959
["COS","cos","cos(0)","1.00000",0],
960
["COT","cot","cot(1)","0.64209262",0],
961
["DEGREES","degrees","degrees(6.283185)","360",0],
962
["EXP","exp","exp(1.0)","2.718282",0],
963
["FLOOR","floor","floor(2.5)","2",0],
964
["LOG","log","log(2)","0.693147",0],
965
["LOG10","log10","log10(10)","1",0],
966
["MOD","mod","mod(11,7)","4",0],
967
["PI","pi","pi()","3.141593",0],
968
["POWER","power","power(2,4)","16",0],
969
["RAND","rand","rand(1)",0,2], # Any value is acceptable
970
["RADIANS","radians","radians(360)","6.283185",0],
971
["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
972
["SIGN","sign","sign(-5)",-1,0],
973
["SIN","sin","sin(1)","0.841471",0],
974
["SQRT","sqrt","sqrt(4)",2,0],
975
["TAN","tan","tan(1)","1.557408",0],
976
["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
977
["NOW","now","now()",0,2], # Any value is acceptable
978
["CURDATE","curdate","curdate()",0,2],
979
["CURTIME","curtime","curtime()",0,2],
980
# Drizzle is different than MySQL in displaying timestamps
981
# ["TIMESTAMPADD","timestampadd",
982
# "timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
983
# "1997-01-01 00:00:01",1],
984
["TIMESTAMPADD","timestampadd",
985
"timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
986
"1997-01-01 00:00:01.000000",1],
987
["TIMESTAMPDIFF","timestampdiff",
988
"timestampdiff(SQL_TSI_SECOND,'1997-01-01 00:00:01',".
989
" '1997-01-01 00:00:02')","1",0],
990
["USER()","user()","user()",0,2],
991
["DATABASE","database","database()",0,2],
992
["IFNULL","ifnull","ifnull(2,3)",2,0],
993
["ODBC syntax LEFT & RIGHT", "fn_left",
994
"{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
999
["& (bitwise and)",'&',"5 & 3",1,0],
1000
["| (bitwise or)",'|',"1 | 2",3,0],
1001
["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
1002
["<> in SELECT","<>","1<>1","0",0],
1003
["=","=","(1=1)",1,$logical_value],
1004
["~* (case insensitive compare)","~*","'hi' ~* 'HI'",1,$logical_value],
1005
["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
1006
["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
1007
["ASCII_CHAR", "ascii_char", "ASCII_CHAR(65)","A",1],
1008
["ASCII_CODE", "ascii_code", "ASCII_CODE('A')","65",0],
1009
["ATN2","atn2","atn2(1,0)","1.570796",0],
1010
["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
1011
["BIT_COUNT","bit_count","bit_count(5)",2,0],
1012
["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
1013
["CHARINDEX","charindex","charindex('a','crash')",3,0],
1014
["CHR", "chr", "CHR(65)" ,"A",1], # oracle
1015
["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
1016
["CONVERT","convert","convert(CHAR,5)","5",1],
1017
["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
1018
["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
1019
["ENCRYPT","encrypt","encrypt('hello')",0,2],
1020
["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
1021
["FORMAT","format","format(1234.5555,2)","1,234.56",1],
1022
["GETDATE","getdate","getdate()",0,2],
1023
["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1],
1024
["IF","if", "if(5,6,7)",6,0],
1025
["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
1026
["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
1027
["INITCAP","initcap","initcap('the soap')","The Soap",1],
1028
# oracle Returns char, with the first letter of each word in uppercase
1029
["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)" ,"14",0], # oracle instring
1030
["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)" ,"27",0],
1031
# oracle instring in bytes
1032
["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
1033
["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
1034
["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1],
1036
["LENGTHB","lengthb","lengthb('CANDIDE')","14",0],
1037
# oracle length in bytes
1038
["LIKE ESCAPE in SELECT","like_escape",
1039
"'%' like 'a%' escape 'a'",$logical_value,0],
1040
["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
1041
["LN","ln","ln(95)","4.55387689",0],
1042
# oracle natural logarithm of n
1043
["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
1044
["LOG(m,n)","log(m_n)","log(10,100)","2",0],
1045
# oracle logarithm, base m, of n
1046
["LOGN","logn","logn(2)","0.693147",0],
1048
["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
1049
["MOD as %","%","10%7","3",0],
1050
["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
1051
["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
1052
["NOT as '!' in SELECT","!","! 1",0,0],
1053
["NOT in SELECT","not","not $false",$logical_value,0],
1054
["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
1055
["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
1056
["PASSWORD","password","password('hello')",0,2],
1057
["PASTE", "paste", "paste('ABCDEFG',3,2,'1234')","AB1234EFG",1],
1058
["PATINDEX","patindex","patindex('%a%','crash')",3,0],
1059
["POW","pow","pow(3,2)",9,0],
1060
["RANGE","range","range(a)","0.0",0],
1061
# informix range(a) = max(a) - min(a)
1062
["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
1063
["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
1064
["REVERSE","reverse","reverse('abcd')","dcba",1],
1065
["ROOT","root","root(4)",2,0], # informix
1066
["ROUND(1 arg)","round1","round(5.63)","6",0],
1067
["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
1068
["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
1069
["STR","str","str(123.45,5,1)",123.5,3],
1070
["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
1071
["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
1072
["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)" ,"CD",1],
1073
# oracle substring with bytes
1074
["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
1075
["SUBSTRING_INDEX","substring_index",
1076
"substring_index('www.tcx.se','.',-2)", "tcx.se",1],
1077
["SYSDATE","sysdate","sysdate()",0,2],
1078
["TAIL","tail","tail('ABCDEFG',3)","EFG",0],
1079
["TANH","tanh","tanh(1)","0.462117157",0],
1080
# oracle hyperbolic tangent of n
1081
["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
1082
["TRIM; Leading char extension", "trim_leading_char","trim(LEADING ':' FROM ':abc!')","abc!",3],
1083
["TRIM; Trailing char extension", "trim_trailing_char","trim(TRAILING'!' FROM ':abc!')",":abc",3],
1084
["TRIM; Substring extension",
1085
"trim_substring","trim('cb' FROM 'abccb')","abc",3],
1086
["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
1087
["UID","uid","uid",0,2], # oracle uid from user
1088
["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
1089
["USERENV","userenv","userenv",0,2], # oracle user enviroment
1090
["VERSION","version","version()",0,2],
1091
["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
1092
["automatic string->num convert","auto_string2num","'1'+2",3,0],
1093
["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
1094
["SUBSTR (2 arg)",'substr2arg',"substr('abcd',2)",'bcd',1], #sapdb func
1095
["SUBSTR (3 arg)",'substr3arg',"substr('abcd',2,2)",'bc',1],
1096
["LFILL (3 arg)",'lfill3arg',"lfill('abcd','.',6)",'..abcd',1],
1097
["RFILL (3 arg)",'rfill3arg',"rfill('abcd','.',6)",'abcd..',1],
1098
["RPAD (4 arg)",'rpad4arg',"rpad('abcd',2,'+-',8)",'abcd+-+-',1],
1099
["LPAD (4 arg)",'rpad4arg',"lpad('abcd',2,'+-',8)",'+-+-abcd',1],
1100
["TRIM (1 arg)",'trim1arg',"trim(' abcd ')",'abcd',1],
1101
["TRIM (2 arg)",'trim2arg',"trim('..abcd..','.')",'abcd',1],
1102
["LTRIM (2 arg)",'ltrim2arg',"ltrim('..abcd..','.')",'abcd..',1],
1103
["RTRIM (2 arg)",'rtrim2arg',"rtrim('..abcd..','.')",'..abcd',1],
1104
["EXPAND",'expand2arg',"expand('abcd',6)",'abcd ',0],
1105
["REPLACE (2 arg) ",'replace2arg',"replace('AbCd','bC')",'Ad',1],
1106
["MAPCHAR",'mapchar',"mapchar('A�')",'Aa',1],
1107
["ALPHA",'alpha',"alpha('A�',2)",'AA',1],
1108
["ASCII in string cast",'ascii_string',"ascii('a')",'97',1],
1109
["EBCDIC in string cast",'ebcdic_string',"ebcdic('a')",'a',1],
1110
["TRUNC (1 arg)",'trunc1arg',"trunc(222.6)",222,0],
1111
["FIXED",'fixed',"fixed(222.6666,10,2)",'222.67',0],
1112
["FLOAT",'float',"float(6666.66,4)",6667,0],
1113
["LENGTH",'length',"length(1)",1,0],
1114
["INDEX",'index',"index('abcdefg','cd',1,1)",3,0],
1115
["MICROSECOND",'microsecond',
1116
"MICROSECOND('19630816200212111111')",'111111',0],
1117
["TIMESTAMP",'timestamp',
1118
"timestamp('19630816','00200212')",'19630816200212000000',0],
1119
["VALUE",'value',"value(NULL,'WALRUS')",'WALRUS',0],
1120
["DECODE",'decode',"DECODE('S-103','T72',1,'S-103',2,'Leopard',3)",2,0],
1121
["NUM",'num',"NUM('2123')",2123,0],
1122
["CHR (any type to string)",'chr_str',"CHR(67)",'67',0],
1123
["HEX",'hex',"HEX('A')",41,0],
1127
@sql_group_functions=
1129
["AVG","avg","avg(a)",1,0],
1130
["COUNT (*)","count_*","count(*)",1,0],
1131
["COUNT column name","count_column","count(a)",1,0],
1132
["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
1133
["MAX on numbers","max","max(a)",1,0],
1134
["MAX on strings","max_str","max(b)","a",1],
1135
["MIN on numbers","min","min(a)",1,0],
1136
["MIN on strings","min_str","min(b)","a",1],
1137
["SUM","sum","sum(a)",1,0],
1138
["ANY","any","any(a)",$logical_value,0],
1139
["EVERY","every","every(a)",$logical_value,0],
1140
["SOME","some","some(a)",$logical_value,0],
1143
@extra_group_functions=
1145
["BIT_AND",'bit_and',"bit_and(a)",1,0],
1146
["BIT_OR", 'bit_or', "bit_or(a)",1,0],
1147
["COUNT(DISTINCT expr,expr,...)",
1148
"count_distinct_list","count(distinct a,b)",1,0],
1149
["STD","std","std(a)",0,0],
1150
["STDDEV","stddev","stddev(a)",0,0],
1151
["VARIANCE","variance","variance(a)",0,0],
1156
["= ALL","eq_all","b =all (select b from crash_me)",1,0],
1157
["= ANY","eq_any","b =any (select b from crash_me)",1,0],
1158
["= SOME","eq_some","b =some (select b from crash_me)",1,0],
1159
["BETWEEN","between","5 between 4 and 6",1,0],
1160
["EXISTS","exists","exists (select * from crash_me)",1,0],
1161
["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
1162
["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
1163
["LIKE","like","b like 'a%'",1,0],
1164
["MATCH UNIQUE","match_unique",
1165
"1 match unique (select a from crash_me)",1,0],
1166
["MATCH","match","1 match (select a from crash_me)",1,0],
1167
["MATCHES","matches","b matches 'a*'",1,0],
1168
["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
1169
["NOT EXISTS","not_exists",
1170
"not exists (select * from crash_me where a = 2)",1,0],
1171
["NOT LIKE","not_like","b not like 'b%'",1,0],
1172
["NOT UNIQUE","not_unique",
1173
"not unique (select * from crash_me where a = 2)",1,0],
1174
["UNIQUE","unique","unique (select * from crash_me)",1,0],
1177
@types=(["sql",\@sql_functions,0],
1178
["odbc",\@odbc_functions,0],
1179
["extra",\@extra_functions,0],
1180
["where",\@where_functions,0]);
1182
@group_types=(["sql",\@sql_group_functions,0],
1183
["extra",\@extra_group_functions,0]);
1186
foreach $types (@types)
1188
print "\nSupported $types->[0] functions\n";
1190
foreach $type (@$tmp)
1192
if (defined($limits{"func_$types->[0]_$type->[1]"}))
1196
if ($types->[0] eq "where")
1198
check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
1199
[],"select a from crash_me where $type->[2]",[],
1200
$type->[3],$type->[4]);
1202
elsif ($limits{'functions'} eq 'yes')
1204
if (($type->[2] =~ /char_length\(b\)/) && (!$end_query))
1206
my $tmp= $type->[2];
1207
$tmp .= " from crash_me ";
1208
undef($limits{"func_$types->[0]_$type->[1]"});
1209
check_and_report("Function $type->[0]",
1210
"func_$types->[0]_$type->[1]",
1211
[],"select $tmp ",[],
1212
$type->[3],$type->[4]);
1216
undef($limits{"func_$types->[0]_$type->[1]"});
1217
$result = check_and_report("Function $type->[0]",
1218
"func_$types->[0]_$type->[1]",
1219
[],"select $type->[2] $end_query",[],
1220
$type->[3],$type->[4]);
1223
# check without type specifyer
1224
if ($type->[2] =~ /DATE /)
1226
my $tmp= $type->[2];
1228
undef($limits{"func_$types->[0]_$type->[1]"});
1229
$result = check_and_report("Function $type->[0]",
1230
"func_$types->[0]_$type->[1]",
1231
[],"select $tmp $end_query",[],
1232
$type->[3],$type->[4]);
1236
if ($types->[0] eq "odbc" && ! ($type->[2] =~ /\{fn/))
1238
my $tmp= $type->[2];
1239
# Check by converting to ODBC format
1240
undef($limits{"func_$types->[0]_$type->[1]"});
1242
$tmp =~ s/('1997-\d\d-\d\d \d\d:\d\d:\d\d')/{ts $1}/g;
1243
$tmp =~ s/(DATE '1997-\d\d-\d\d')/{d $1}/g;
1244
$tmp =~ s/(TIME '12:13:14')/{t $1}/g;
1247
check_and_report("Function $type->[0]",
1248
"func_$types->[0]_$type->[1]",
1249
[],"select $tmp $end_query",[],
1250
$type->[3],$type->[4]);
1259
if ($limits{'functions'} eq 'yes')
1261
foreach $types (@group_types)
1263
print "\nSupported $types->[0] group functions\n";
1265
foreach $type (@$tmp)
1267
check_and_report("Group function $type->[0]",
1268
"group_func_$types->[0]_$type->[1]",
1269
[],"select $type->[2],a from crash_me group by a",[],
1270
$type->[3],$type->[4]);
1274
report("mixing of integer and float in expression","float_int_expr",
1275
"select 1+1.0 $end_query");
1276
if ($limits{'func_odbc_exp'} eq 'yes')
1278
report("No need to cast from integer to float",
1279
"dont_require_cast_to_float", "select exp(1) $end_query");
1281
check_and_report("Is 1+NULL = NULL","null_num_expr",
1282
[],"select 1+$numeric_null $end_query",[],undef(),4);
1283
$tmp=sql_concat("'a'",$char_null);
1286
check_and_report("Is $tmp = NULL", "null_concat_expr", [],
1287
"select $tmp $end_query",[], undef(),4);
1289
$prompt="Need to cast NULL for arithmetic";
1290
add_log("Need_cast_for_null",
1291
" Check if numeric_null ($numeric_null) is 'NULL'");
1292
save_config_data("Need_cast_for_null",
1293
($numeric_null eq "NULL") ? "no" : "yes",
1304
my $result = 'undefined';
1307
save_incomplete('func_extra_noround','Function NOROUND');
1309
# 1) check if noround() function is supported
1310
$error = safe_query_l('func_extra_noround',"select noround(22.6) $end_query");
1311
if ($error ne 1) # syntax error -- noround is not supported
1315
else # Ok, now check if it really works
1317
$error=safe_query_l('func_extra_noround',
1318
["create table crash_me_nr (a int)",
1319
"insert into crash_me_nr values(noround(10.2))",
1320
"drop table crash_me_nr $drop_attr"]);
1323
$result= "syntax only";
1331
save_config_data('func_extra_noround',$result,"Function NOROUND");
1334
check_parenthesis("func_sql_","CURRENT_USER");
1335
check_parenthesis("func_sql_","SESSION_USER");
1336
check_parenthesis("func_sql_","SYSTEM_USER");
1337
check_parenthesis("func_sql_","USER");
1340
if ($limits{'type_sql_date'} eq 'yes')
1342
# Checking the format of date in result.
1344
safe_query("drop table crash_me_d $drop_attr");
1345
assert_crash("create table crash_me_d (a date)");
1346
# find the example of date
1348
if ($limits{'func_extra_sysdate'} eq 'yes') {
1349
$dateexample=' sysdate() ';
1351
elsif ($limits{'func_sql_current_date'} eq 'yes') {
1352
$dateexample='CURRENT_DATE';
1354
elsif ($limits{'func_odbc_curdate'} eq 'yes') {
1355
$dateexample='curdate()';
1357
elsif ($limits{'func_extra_getdate'} eq 'yes') {
1358
$dateexample='getdate()';
1360
elsif ($limits{'func_odbc_now'} eq 'yes') {
1361
$dateexample='now()';
1364
$dateexample="DATE '1963-08-16'";
1367
my $key = 'date_format_inresult';
1368
my $prompt = "Date format in result";
1369
if (! safe_query_l('date_format_inresult',
1370
"insert into crash_me_d values($dateexample) "))
1372
die "Cannot insert date ($dateexample):".$last_error;
1374
my $sth= $dbh->prepare("select a from crash_me_d");
1375
add_log('date_format_inresult',"< select a from crash_me_d");
1377
$_= $sth->fetchrow_array;
1378
add_log('date_format_inresult',"> $_");
1379
safe_query_l($key,"delete from crash_me_d");
1380
if (/\d{4}-\d{2}-\d{2}/){ save_config_data($key,"iso",$prompt);}
1381
elsif (/\d{2}-\d{2}-\d{2}/){ save_config_data($key,"short iso",$prompt);}
1382
elsif (/\d{2}\.\d{2}\.\d{4}/){ save_config_data($key,"euro",$prompt);}
1383
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"short euro",$prompt);}
1384
elsif (/\d{2}\/\d{2}\/\d{4}/){ save_config_data($key,"usa",$prompt);}
1385
elsif (/\d{2}\/\d{2}\/\d{2}/){ save_config_data($key,"short usa",$prompt);}
1386
elsif (/\d*/){ save_config_data($key,"YYYYMMDD",$prompt);}
1387
else { save_config_data($key,"unknown",$prompt);};
1390
check_and_report("Supports YYYY-MM-DD (ISO) format","date_format_ISO",
1391
[ "insert into crash_me_d(a) values ('1963-08-16')"],
1392
"select a from crash_me_d",
1393
["delete from crash_me_d"],
1394
make_date_r(1963,8,16),1);
1396
check_and_report("Supports DATE 'YYYY-MM-DD' (ISO) format",
1397
"date_format_ISO_with_date",
1398
[ "insert into crash_me_d(a) values (DATE '1963-08-16')"],
1399
"select a from crash_me_d",
1400
["delete from crash_me_d"],
1401
make_date_r(1963,8,16),1);
1403
check_and_report("Supports DD.MM.YYYY (EUR) format","date_format_EUR",
1404
[ "insert into crash_me_d(a) values ('16.08.1963')"],
1405
"select a from crash_me_d",
1406
["delete from crash_me_d"],
1407
make_date_r(1963,8,16),1);
1408
check_and_report("Supports DATE 'DD.MM.YYYY' (EUR) format",
1409
"date_format_EUR_with_date",
1410
[ "insert into crash_me_d(a) values (DATE '16.08.1963')"],
1411
"select a from crash_me_d",
1412
["delete from crash_me_d"],
1413
make_date_r(1963,8,16),1);
1415
check_and_report("Supports YYYYMMDD format",
1416
"date_format_YYYYMMDD",
1417
[ "insert into crash_me_d(a) values ('19630816')"],
1418
"select a from crash_me_d",
1419
["delete from crash_me_d"],
1420
make_date_r(1963,8,16),1);
1421
check_and_report("Supports DATE 'YYYYMMDD' format",
1422
"date_format_YYYYMMDD_with_date",
1423
[ "insert into crash_me_d(a) values (DATE '19630816')"],
1424
"select a from crash_me_d",
1425
["delete from crash_me_d"],
1426
make_date_r(1963,8,16),1);
1428
check_and_report("Supports MM/DD/YYYY format",
1430
[ "insert into crash_me_d(a) values ('08/16/1963')"],
1431
"select a from crash_me_d",
1432
["delete from crash_me_d"],
1433
make_date_r(1963,8,16),1);
1434
check_and_report("Supports DATE 'MM/DD/YYYY' format",
1435
"date_format_USA_with_date",
1436
[ "insert into crash_me_d(a) values (DATE '08/16/1963')"],
1437
"select a from crash_me_d",
1438
["delete from crash_me_d"],
1439
make_date_r(1963,8,16),1);
1444
check_and_report("Supports 0000-00-00 dates","date_zero",
1445
["create table crash_me2 (a date not null)",
1446
"insert into crash_me2 values (".make_date(0,0,0).")"],
1447
"select a from crash_me2",
1448
["drop table crash_me2 $drop_attr"],
1449
make_date_r(0,0,0),1);
1451
check_and_report("Supports 0001-01-01 dates","date_one",
1452
["create table crash_me2 (a date not null)",
1453
"insert into crash_me2 values (".make_date(1,1,1).")"],
1454
"select a from crash_me2",
1455
["drop table crash_me2 $drop_attr"],
1456
make_date_r(1,1,1),1);
1458
check_and_report("Supports 9999-12-31 dates","date_last",
1459
["create table crash_me2 (a date not null)",
1460
"insert into crash_me2 values (".make_date(9999,12,31).")"],
1461
"select a from crash_me2",
1462
["drop table crash_me2 $drop_attr"],
1463
make_date_r(9999,12,31),1);
1465
check_and_report("Supports 'infinity dates","date_infinity",
1466
["create table crash_me2 (a date not null)",
1467
"insert into crash_me2 values ('infinity')"],
1468
"select a from crash_me2",
1469
["drop table crash_me2 $drop_attr"],
1472
if (!defined($limits{'date_with_YY'}))
1474
check_and_report("Supports YY-MM-DD dates","date_with_YY",
1475
["create table crash_me2 (a date not null)",
1476
"insert into crash_me2 values ('98-03-03')"],
1477
"select a from crash_me2",
1478
["drop table crash_me2 $drop_attr"],
1479
make_date_r(1998,3,3),5);
1480
if ($limits{'date_with_YY'} eq "yes")
1482
undef($limits{'date_with_YY'});
1483
check_and_report("Supports YY-MM-DD 2000 compilant dates",
1485
["create table crash_me2 (a date not null)",
1486
"insert into crash_me2 values ('10-03-03')"],
1487
"select a from crash_me2",
1488
["drop table crash_me2 $drop_attr"],
1489
make_date_r(2010,3,3),5);
1498
save_incomplete('func_odbc_week','WEEK');
1499
$error = safe_query_result_l('func_odbc_week',
1500
"select week(".make_date(1997,2,1).") $end_query",5,0);
1501
# actually this query must return 4 or 5 in the $last_result,
1502
# $error can be 1 (not supported at all) , -1 ( probably USA weeks)
1503
# and 0 - EURO weeks
1505
if ($last_result == 4) {
1509
add_log('func_odbc_week',
1510
" must return 4 or 5, but $last_result");
1512
} elsif ($error == 0) {
1516
save_config_data('func_odbc_week',$result,"WEEK");
1519
my $insert_query ='insert into crash_me_d values('.
1520
make_date(1997,2,1).')';
1521
safe_query($insert_query);
1524
["DAYNAME","dayname","dayname(a)","",2],
1525
["MONTH","month","month(a)","",2],
1526
["MONTHNAME","monthname","monthname(a)","",2],
1527
["DAYOFMONTH","dayofmonth","dayofmonth(a)",1,0],
1528
["DAYOFWEEK","dayofweek","dayofweek(a)",7,0],
1529
["DAYOFYEAR","dayofyear","dayofyear(a)",32,0],
1530
["QUARTER","quarter","quarter(a)",1,0],
1531
["YEAR","year","year(a)",1997,0]))
1533
$prompt='Function '.$fn->[0];
1534
$key='func_odbc_'.$fn->[1];
1535
add_log($key,"< ".$insert_query);
1536
check_and_report($prompt,$key,
1537
[],"select ".$fn->[2]." from crash_me_d",[],
1542
safe_query(['delete from crash_me_d',
1543
'insert into crash_me_d values('.make_date(1963,8,16).')']);
1545
["DATEADD","dateadd","dateadd(day,3,make_date(1997,11,30))",0,2],
1546
["MDY","mdy","mdy(7,1,1998)","make_date_r(1998,07,01)",0], # informix
1547
["DATEDIFF","datediff",
1548
"datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
1549
["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
1550
["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
1551
["DATE_FORMAT","date_format",
1552
"date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
1553
["FROM_DAYS","from_days",
1554
"from_days(729024)","make_date_r(1996,1,1)",1],
1555
["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
1556
["MONTHS_BETWEEN","months_between",
1557
"months_between(make_date(1997,2,2),make_date(1997,1,1))",
1558
"1.03225806",0], # oracle number of months between 2 dates
1559
["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
1560
["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
1561
["WEEKDAY","weekday","weekday(make_date(1997,11,29))",5,0],
1562
["ADDDATE",'adddate',
1563
"ADDDATE(make_date(2002,12,01),3)",'make_date_r(2002,12,04)',0],
1564
["SUBDATE",'subdate',
1565
"SUBDATE(make_date(2002,12,04),3)",'make_date_r(2002,12,01)',0],
1566
["DATEDIFF (2 arg)",'datediff2arg',
1567
"DATEDIFF(make_date(2002,12,04),make_date(2002,12,01))",'3',0],
1568
["WEEKOFYEAR",'weekofyear',
1569
"WEEKOFYEAR(make_date(1963,08,16))",'33',0],
1570
# table crash_me_d must contain record with 1963-08-16 (for CHAR)
1571
["CHAR (conversation date)",'char_date',
1572
"CHAR(a,EUR)",'16.08.1963',0],
1573
["MAKEDATE",'makedate',"MAKEDATE(1963,228)"
1574
,'make_date_r(1963,08,16)',0],
1575
# Drizzle is different than MySQL in handling TO_DAYS
1576
# Drizzle calculates the Julian Day Number
1577
# ["TO_DAYS","to_days",
1578
# "to_days(make_date(1996,01,01))",729024,0],
1579
["TO_DAYS","to_days",
1580
"to_days(make_date(1996,01,01))",2450084,0],
1581
["ADD_MONTHS","add_months",
1582
"add_months(make_date(1997,01,01),1)","make_date_r(1997,02,01)",0],
1583
# oracle the date plus n months
1584
["LAST_DAY","last_day",
1585
"last_day(make_date(1997,04,01))","make_date_r(1997,04,30)",0],
1586
# oracle last day of month of date
1587
["DATE",'date',"date(make_date(1963,8,16))",
1588
'make_date_r(1963,8,16)',0],
1589
["DAY",'day',"DAY(make_date(2002,12,01))",1,0]))
1591
$prompt='Function '.$fn->[0];
1592
$key='func_extra_'.$fn->[1];
1593
my $qry="select ".$fn->[2]." from crash_me_d";
1594
while( $qry =~ /^(.*)make_date\((\d+),(\d+),(\d+)\)(.*)$/)
1596
my $dt= &make_date($2,$3,$4);
1599
my $result=$fn->[3];
1600
while( $result =~ /^(.*)make_date_r\((\d+),(\d+),(\d+)\)(.*)$/)
1602
my $dt= &make_date_r($2,$3,$4);
1605
check_and_report($prompt,$key,
1612
safe_query("drop table crash_me_d $drop_attr");
1616
if ($limits{'type_sql_time'} eq 'yes')
1618
# Checking the format of date in result.
1620
safe_query("drop table crash_me_t $drop_attr");
1621
assert_crash("create table crash_me_t (a time)");
1622
# find the example of time
1624
if ($limits{'func_sql_current_time'} eq 'yes') {
1625
$timeexample='CURRENT_TIME';
1627
elsif ($limits{'func_odbc_curtime'} eq 'yes') {
1628
$timeexample='curtime()';
1630
elsif ($limits{'func_sql_localtime'} eq 'yes') {
1631
$timeexample='localtime';
1633
elsif ($limits{'func_odbc_now'} eq 'yes') {
1634
$timeexample='now()';
1637
$timeexample="'02:55:12'";
1640
my $key = 'time_format_inresult';
1641
my $prompt = "Time format in result";
1642
if (! safe_query_l('time_format_inresult',
1643
"insert into crash_me_t values($timeexample) "))
1645
die "Cannot insert time ($timeexample):".$last_error;
1647
my $sth= $dbh->prepare("select a from crash_me_t");
1648
add_log('time_format_inresult',"< select a from crash_me_t");
1650
$_= $sth->fetchrow_array;
1651
add_log('time_format_inresult',"> $_");
1652
safe_query_l($key,"delete from crash_me_t");
1653
if (/\d{2}:\d{2}:\d{2}/){ save_config_data($key,"iso",$prompt);}
1654
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"euro",$prompt);}
1655
elsif (/\d{2}:\d{2}\s+(AM|PM)/i){ save_config_data($key,"usa",$prompt);}
1656
elsif (/\d{8}$/){ save_config_data($key,"HHHHMMSS",$prompt);}
1657
elsif (/\d{4}$/){ save_config_data($key,"HHMMSS",$prompt);}
1658
else { save_config_data($key,"unknown",$prompt);};
1661
check_and_report("Supports HH:MM:SS (ISO) time format","time_format_ISO",
1662
[ "insert into crash_me_t(a) values ('20:08:16')"],
1663
"select a from crash_me_t",
1664
["delete from crash_me_t"],
1665
make_time_r(20,8,16),1);
1667
check_and_report("Supports HH.MM.SS (EUR) time format","time_format_EUR",
1668
[ "insert into crash_me_t(a) values ('20.08.16')"],
1669
"select a from crash_me_t",
1670
["delete from crash_me_t"],
1671
make_time_r(20,8,16),1);
1673
check_and_report("Supports HHHHmmSS time format",
1674
"time_format_HHHHMMSS",
1675
[ "insert into crash_me_t(a) values ('00200816')"],
1676
"select a from crash_me_t",
1677
["delete from crash_me_t"],
1678
make_time_r(20,8,16),1);
1680
check_and_report("Supports HHmmSS time format",
1681
"time_format_HHHHMMSS",
1682
[ "insert into crash_me_t(a) values ('200816')"],
1683
"select a from crash_me_t",
1684
["delete from crash_me_t"],
1685
make_time_r(20,8,16),1);
1687
check_and_report("Supports HH:MM:SS (AM|PM) time format",
1689
[ "insert into crash_me_t(a) values ('08:08:16 PM')"],
1690
"select a from crash_me_t",
1691
["delete from crash_me_t"],
1692
make_time_r(20,8,16),1);
1694
my $insert_query ='insert into crash_me_t values('.
1695
make_time(20,8,16).')';
1696
safe_query($insert_query);
1699
["HOUR","hour","hour('".make_time(12,13,14)."')",12,0],
1700
["ANSI HOUR","hour_time","hour(TIME '".make_time(12,13,14)."')",12,0],
1701
["MINUTE","minute","minute('".make_time(12,13,14)."')",13,0],
1702
["SECOND","second","second('".make_time(12,13,14)."')",14,0]
1706
$prompt='Function '.$fn->[0];
1707
$key='func_odbc_'.$fn->[1];
1708
add_log($key,"< ".$insert_query);
1709
check_and_report($prompt,$key,
1710
[],"select ".$fn->[2]." $end_query",[],
1715
# safe_query(['delete from crash_me_t',
1716
# 'insert into crash_me_t values('.make_time(20,8,16).')']);
1718
["TIME_TO_SEC","time_to_sec","time_to_sec('".
1719
make_time(1,23,21)."')","5001",0],
1720
["SEC_TO_TIME","sec_to_time","sec_to_time(5001)",
1721
make_time_r(01,23,21),1],
1722
["ADDTIME",'addtime',"ADDTIME('".make_time(20,2,12).
1723
"','".make_time(0,0,3)."')",make_time_r(20,2,15),0],
1724
["SUBTIME",'subtime',"SUBTIME('".make_time(20,2,15)
1725
."','".make_time(0,0,3)."')",make_time_r(20,2,12),0],
1726
["TIMEDIFF",'timediff',"TIMEDIFF('".make_time(20,2,15)."','".
1727
make_time(20,2,12)."')",make_time_r(0,0,3),0],
1728
["MAKETIME",'maketime',"MAKETIME(20,02,12)",make_time_r(20,2,12),0],
1729
["TIME",'time',"time('".make_time(20,2,12)."')",make_time_r(20,2,12),0]
1732
$prompt='Function '.$fn->[0];
1733
$key='func_extra_'.$fn->[1];
1734
my $qry="select ".$fn->[2]." $end_query";
1735
my $result=$fn->[3];
1736
check_and_report($prompt,$key,
1743
safe_query("drop table crash_me_t $drop_attr");
1748
# NOT id BETWEEN a and b
1749
if ($limits{'func_where_not_between'} eq 'yes')
1751
my $result = 'error';
1753
my $key='not_id_between';
1754
my $prompt='NOT ID BETWEEN interprets as ID NOT BETWEEN';
1756
save_incomplete($key,$prompt);
1757
safe_query_l($key,["create table crash_me_b (i int)",
1758
"insert into crash_me_b values(2)",
1759
"insert into crash_me_b values(5)"]);
1760
$err =safe_query_result_l($key,
1761
"select i from crash_me_b where not i between 1 and 3",
1764
if (not defined($last_result)) {
1771
safe_query_l($key,["drop table crash_me_b"]);
1772
save_config_data($key,$result,$prompt);
1779
report("LIKE on numbers","like_with_number",
1780
"create table crash_q (a int,b int)",
1781
"insert into crash_q values(10,10)",
1782
"select * from crash_q where a like '10'",
1783
"drop table crash_q $drop_attr");
1785
report("column LIKE column","like_with_column",
1786
"create table crash_q (a char(10),b char(10))",
1787
"insert into crash_q values('abc','abc')",
1788
"select * from crash_q where a like b",
1789
"drop table crash_q $drop_attr");
1791
report("update of column= -column","NEG",
1792
"create table crash_q (a integer)",
1793
"insert into crash_q values(10)",
1794
"update crash_q set a=-a",
1795
"drop table crash_q $drop_attr");
1797
if ($limits{'func_odbc_left'} eq 'yes' ||
1798
$limits{'func_odbc_substring'} eq 'yes')
1800
my $type= ($limits{'func_odbc_left'} eq 'yes' ?
1801
"left(a,4)" : "substring(a for 4)");
1803
check_and_report("String functions on date columns","date_as_string",
1804
["create table crash_me2 (a date not null)",
1805
"insert into crash_me2 values ('1998-03-03')"],
1806
"select $type from crash_me2",
1807
["drop table crash_me2 $drop_attr"],
1812
$tmp=sql_concat("b","b");
1815
check_and_report("char are space filled","char_is_space_filled",
1816
[],"select $tmp from crash_me where b = 'a '",[],
1820
if (!defined($limits{'multi_table_update'}))
1822
if (check_and_report("Update with many tables","multi_table_update",
1823
["create table crash_q (a integer,b char(10))",
1824
"insert into crash_q values(1,'c')",
1825
"update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
1826
"select b from crash_q",
1827
["drop table crash_q $drop_attr"],
1830
check_and_report("Update with many tables","multi_table_update",
1831
["create table crash_q (a integer,b char(10))",
1832
"insert into crash_q values(1,'c')",
1833
"update crash_q,crash_me set crash_q.b=crash_me.b ".
1834
"where crash_q.a=crash_me.a"],
1835
"select b from crash_q",
1836
["drop table crash_q $drop_attr"],
1842
report("DELETE FROM table1,table2...","multi_table_delete",
1843
"create table crash_q (a integer,b char(10))",
1844
"insert into crash_q values(1,'c')",
1845
"delete crash_q.* from crash_q,crash_me where crash_q.a=crash_me.a",
1846
"drop table crash_q $drop_attr");
1848
check_and_report("Update with sub select","select_table_update",
1849
["create table crash_q (a integer,b char(10))",
1850
"insert into crash_q values(1,'c')",
1851
"update crash_q set b= ".
1852
"(select b from crash_me where crash_q.a = crash_me.a)"],
1853
"select b from crash_q",
1854
["drop table crash_q $drop_attr"],
1857
check_and_report("Calculate 1--1","minus_neg",[],
1858
"select a--1 from crash_me",[],0,2);
1860
report("ANSI SQL simple joins","simple_joins",
1861
"select crash_me.a from crash_me, crash_me t0");
1864
# Check max string size, and expression limits
1867
foreach $type (('mediumtext','text','text()','blob','long'))
1869
if ($limits{"type_extra_$type"} eq 'yes')
1875
if (defined($found))
1877
$found =~ s/\(\)/\(%d\)/;
1878
find_limit("max text or blob size","max_text_size",
1879
new query_many(["create table crash_q (q $found)",
1880
"insert into crash_q values ('%s')"],
1881
"select * from crash_q","%s",
1882
["drop table crash_q $drop_attr"],
1883
min($max_string_size,$limits{'query_size'}-30)));
1887
# It doesn't make lots of sense to check for string lengths much bigger than
1888
# what can be stored...
1890
find_limit(($prompt="constant string size in where"),"where_string_size",
1891
new query_repeat([],"select a from crash_me where b >='",
1893
if ($limits{'where_string_size'} == 10)
1895
save_config_data('where_string_size','nonstandard',$prompt);
1898
if ($limits{'select_constants'} eq 'yes')
1900
find_limit("constant string size in SELECT","select_string_size",
1901
new query_repeat([],"select '","","","a","","'$end_query"));
1904
goto no_functions if ($limits{'functions'} ne "yes");
1906
if ($limits{'func_odbc_repeat'} eq 'yes')
1908
find_limit("return string size from function","repeat_string_size",
1910
"select repeat('a',%d) $end_query","%s",
1912
$max_string_size,0));
1915
$tmp=find_limit("simple expressions","max_expressions",
1916
new query_repeat([],"select 1","","","+1","",$end_query,
1917
undef(),$max_expressions));
1921
$tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
1922
find_limit("big expressions", "max_big_expressions",
1923
new query_repeat([],"select 0","","","+$tmp","",$end_query,
1924
undef(),$max_big_expressions));
1927
find_limit("stacked expressions", "max_stack_expression",
1928
new query_repeat([],"select 1","","","+(1",")",$end_query,
1929
undef(),$max_stacked_expressions));
1933
if (!defined($limits{'max_conditions'}))
1935
find_limit("OR and AND in WHERE","max_conditions",
1936
new query_repeat([],
1937
"select a from crash_me where a=1 and b='a'","",
1938
"", " or a=%d and b='%d'","","","",
1939
[],($query_size-42)/29,undef,2));
1940
$limits{'max_conditions'}*=2;
1942
# The 42 is the length of the constant part.
1943
# The 29 is the length of the variable part, plus two seven-digit numbers.
1945
find_limit("tables in join", "join_tables",
1946
new query_repeat([],
1947
"select crash_me.a",",t%d.a","from crash_me",
1948
",crash_me t%d","","",[],$max_join_tables,undef,
1951
# Different CREATE TABLE options
1953
report("primary key in create table",'primary_key_in_create',
1954
"create table crash_q (q integer not null,primary key (q))",
1955
"drop table crash_q $drop_attr");
1957
report("unique in create table",'unique_in_create',
1958
"create table crash_q (q integer not null,unique (q))",
1959
"drop table crash_q $drop_attr");
1961
if ($limits{'unique_in_create'} eq 'yes')
1963
report("unique null in create",'unique_null_in_create',
1964
"create table crash_q (q integer,unique (q))",
1965
"insert into crash_q (q) values (NULL)",
1966
"insert into crash_q (q) values (NULL)",
1967
"insert into crash_q (q) values (1)",
1968
"drop table crash_q $drop_attr");
1971
report("default value for column",'create_default',
1972
"create table crash_q (q integer default 10 not null)",
1973
"drop table crash_q $drop_attr");
1975
report("default value function for column",'create_default_func',
1976
"create table crash_q (q integer not null,q1 integer default (1+1))",
1977
"drop table crash_q $drop_attr");
1979
report("temporary tables",'temporary_table',
1980
"create temporary table crash_q (q integer not null)",
1981
"drop table crash_q $drop_attr");
1983
report_one("create table from select",'create_table_select',
1984
[["create table crash_q SELECT * from crash_me","yes"],
1985
["create table crash_q AS SELECT * from crash_me","with AS"]]);
1986
$dbh->do("drop table crash_q $drop_attr");
1988
report("index in create table",'index_in_create',
1989
"create table crash_q (q integer not null,index (q))",
1990
"drop table crash_q $drop_attr");
1992
# The following must be executed as we need the value of end_drop_keyword
1994
if (!(defined($limits{'create_index'}) && defined($limits{'drop_index'})))
1996
if ($res=safe_query_l('create_index',"create index crash_q on crash_me (a)"))
2000
$end_drop_keyword="";
2001
if (!safe_query_l('drop_index',"drop index crash_q"))
2003
# Can't drop the standard way; Check if mSQL
2004
if (safe_query_l('drop_index',"drop index crash_q from crash_me"))
2006
$drop_res="with 'FROM'"; # Drop is not ANSI SQL
2007
$end_drop_keyword="drop index %i from %t";
2009
# else check if Access or MySQL
2010
elsif (safe_query_l('drop_index',"drop index crash_q on crash_me"))
2012
$drop_res="with 'ON'"; # Drop is not ANSI SQL
2013
$end_drop_keyword="drop index %i on %t";
2015
# else check if MS-SQL
2016
elsif (safe_query_l('drop_index',"drop index crash_me.crash_q"))
2018
$drop_res="with 'table.index'"; # Drop is not ANSI SQL
2019
$end_drop_keyword="drop index %t.%i";
2024
# Old MySQL 3.21 supports only the create index syntax
2025
# This means that the second create doesn't give an error.
2026
$res=safe_query_l('create_index',["create index crash_q on crash_me (a)",
2027
"create index crash_q on crash_me (a)",
2028
"drop index crash_q"]);
2029
$res= $res ? 'ignored' : 'yes';
2034
$drop_res=$res='no';
2036
save_config_data('create_index',$res,"create index");
2037
save_config_data('drop_index',$drop_res,"drop index");
2039
print "create index: $limits{'create_index'}\n";
2040
print "drop index: $limits{'drop_index'}\n";
2043
# check if we can have 'NULL' as a key
2044
check_and_report("null in index","null_in_index",
2045
[create_table("crash_q",["a char(10)"],["(a)"]),
2046
"insert into crash_q values (NULL)"],
2047
"select * from crash_q",
2048
["drop table crash_q $drop_attr"],
2051
if ($limits{'unique_in_create'} eq 'yes')
2053
report("null in unique index",'null_in_unique',
2054
create_table("crash_q",["q integer"],["unique(q)"]),
2055
"insert into crash_q (q) values(NULL)",
2056
"insert into crash_q (q) values(NULL)",
2057
"drop table crash_q $drop_attr");
2058
report("null combination in unique index",'nulls_in_unique',
2059
create_table("crash_q",["q integer,q1 integer"],["unique(q,q1)"]),
2060
"insert into crash_q (q,q1) values(1,NULL)",
2061
"insert into crash_q (q,q1) values(1,NULL)",
2062
"drop table crash_q $drop_attr");
2065
if ($limits{'null_in_unique'} eq 'yes')
2067
report("null in unique index",'multi_null_in_unique',
2068
create_table("crash_q",["q integer, x integer"],["unique(q)"]),
2069
"insert into crash_q(x) values(1)",
2070
"insert into crash_q(x) values(2)",
2071
"drop table crash_q $drop_attr");
2074
if ($limits{'create_index'} ne 'no')
2076
$end_drop=$end_drop_keyword;
2077
$end_drop =~ s/%i/crash_q/;
2078
$end_drop =~ s/%t/crash_me/;
2079
report("index on column part (extension)","index_parts",,
2080
"create index crash_q on crash_me (b(5))",
2082
$end_drop=$end_drop_keyword;
2083
$end_drop =~ s/%i/crash_me/;
2084
$end_drop =~ s/%t/crash_me/;
2085
report("different namespace for index",
2087
"create index crash_me on crash_me (b)",
2091
if (!report("case independent table names","table_name_case",
2092
"create table crash_q (q integer)",
2093
"drop table CRASH_Q $drop_attr"))
2095
safe_query("drop table crash_q $drop_attr");
2098
if (!report("case independent field names","field_name_case",
2099
"create table crash_q (q integer)",
2100
"insert into crash_q(Q) values (1)",
2101
"drop table crash_q $drop_attr"))
2103
safe_query("drop table crash_q $drop_attr");
2106
if (!report("drop table if exists","drop_if_exists",
2107
"create table crash_q (q integer)",
2108
"drop table if exists crash_q $drop_attr"))
2110
safe_query("drop table crash_q $drop_attr");
2113
report("create table if not exists","create_if_not_exists",
2114
"create table crash_q (q integer)",
2115
"create table if not exists crash_q (q integer)");
2116
safe_query("drop table crash_q $drop_attr");
2119
# test of different join types
2122
assert_crash("create table crash_me2 (a integer not null,b char(10) not null,".
2124
assert_crash("insert into crash_me2 (a,b,c1) values (1,'b',1)");
2125
assert_crash("create table crash_me3 (a integer not null,b char(10) not null)");
2126
assert_crash("insert into crash_me3 (a,b) values (1,'b')");
2128
report("inner join","inner_join",
2129
"select crash_me.a from crash_me inner join crash_me2 ON ".
2130
"crash_me.a=crash_me2.a");
2131
report("left outer join","left_outer_join",
2132
"select crash_me.a from crash_me left join crash_me2 ON ".
2133
"crash_me.a=crash_me2.a");
2134
report("natural left outer join","natural_left_outer_join",
2135
"select c1 from crash_me natural left join crash_me2");
2136
report("left outer join using","left_outer_join_using",
2137
"select c1 from crash_me left join crash_me2 using (a)");
2138
report("left outer join odbc style","odbc_left_outer_join",
2139
"select crash_me.a from { oj crash_me left outer join crash_me2 ON".
2140
" crash_me.a=crash_me2.a }");
2141
report("right outer join","right_outer_join",
2142
"select crash_me.a from crash_me right join crash_me2 ON ".
2143
"crash_me.a=crash_me2.a");
2144
report("full outer join","full_outer_join",
2145
"select crash_me.a from crash_me full join crash_me2 ON "."
2146
crash_me.a=crash_me2.a");
2147
report("cross join (same as from a,b)","cross_join",
2148
"select crash_me.a from crash_me cross join crash_me3");
2149
report("natural join","natural_join",
2150
"select * from crash_me natural join crash_me3");
2151
report("union","union",
2152
"select * from crash_me union select a,b from crash_me3");
2153
report("union all","union_all",
2154
"select * from crash_me union all select a,b from crash_me3");
2155
report("intersect","intersect",
2156
"select * from crash_me intersect select * from crash_me3");
2157
report("intersect all","intersect_all",
2158
"select * from crash_me intersect all select * from crash_me3");
2159
report("except","except",
2160
"select * from crash_me except select * from crash_me3");
2161
report("except all","except_all",
2162
"select * from crash_me except all select * from crash_me3");
2163
report("except","except",
2164
"select * from crash_me except select * from crash_me3");
2165
report("except all","except_all",
2166
"select * from crash_me except all select * from crash_me3");
2167
report("minus","minus",
2168
"select * from crash_me minus select * from crash_me3"); # oracle ...
2170
report("natural join (incompatible lists)","natural_join_incompat",
2171
"select c1 from crash_me natural join crash_me2");
2172
report("union (incompatible lists)","union_incompat",
2173
"select * from crash_me union select a,b from crash_me2");
2174
report("union all (incompatible lists)","union_all_incompat",
2175
"select * from crash_me union all select a,b from crash_me2");
2176
report("intersect (incompatible lists)","intersect_incompat",
2177
"select * from crash_me intersect select * from crash_me2");
2178
report("intersect all (incompatible lists)","intersect_all_incompat",
2179
"select * from crash_me intersect all select * from crash_me2");
2180
report("except (incompatible lists)","except_incompat",
2181
"select * from crash_me except select * from crash_me2");
2182
report("except all (incompatible lists)","except_all_incompat",
2183
"select * from crash_me except all select * from crash_me2");
2184
report("except (incompatible lists)","except_incompat",
2185
"select * from crash_me except select * from crash_me2");
2186
report("except all (incompatible lists)","except_all_incompat",
2187
"select * from crash_me except all select * from crash_me2");
2188
report("minus (incompatible lists)","minus_incompat",
2189
"select * from crash_me minus select * from crash_me2"); # oracle ...
2191
assert_crash("drop table crash_me2 $drop_attr");
2192
assert_crash("drop table crash_me3 $drop_attr");
2194
# somethings to be added here ....
2195
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
2197
# >ALL | ANY | SOME - EXISTS - UNIQUE
2199
if (report("subqueries","subqueries",
2200
"select a from crash_me where crash_me.a in ".
2201
"(select max(a) from crash_me)"))
2203
$tmp=new query_repeat([],"select a from crash_me","","",
2204
" where a in (select a from crash_me",")",
2205
"",[],$max_join_tables);
2206
find_limit("recursive subqueries", "recursive_subqueries",$tmp);
2209
report("insert INTO ... SELECT ...","insert_select",
2210
"create table crash_q (a int)",
2211
"insert into crash_q (a) SELECT crash_me.a from crash_me",
2212
"drop table crash_q $drop_attr");
2214
if (!defined($limits{"transactions"}))
2217
$limit="transactions";
2218
$limit_r="rollback_metadata";
2220
foreach $type (('', 'type=bdb', 'type=innodb', 'type=gemini'))
2222
undef($limits{$limit});
2223
if (!report_trans($limit,
2224
[create_table("crash_q",["a integer not null"],[],
2226
"insert into crash_q values (1)"],
2227
"select * from crash_q",
2228
"drop table crash_q $drop_attr"
2231
report_rollback($limit_r,
2232
[create_table("crash_q",["a integer not null"],[],
2234
"insert into crash_q values (1)",
2235
"drop table crash_q $drop_attr" );
2239
print "$limits{$limit}\n";
2240
print "$limit_r: $limits{$limit_r}\n";
2243
report("atomic updates","atomic_updates",
2244
create_table("crash_q",["a integer not null"],["primary key (a)"]),
2245
"insert into crash_q values (2)",
2246
"insert into crash_q values (3)",
2247
"insert into crash_q values (1)",
2248
"update crash_q set a=a+1",
2249
"drop table crash_q $drop_attr");
2251
if ($limits{'atomic_updates'} eq 'yes')
2253
report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
2254
create_table("crash_q",["a integer not null"],
2255
["primary key (a)"]),
2256
"insert into crash_q values (2)",
2257
"insert into crash_q values (3)",
2258
"insert into crash_q values (1)",
2259
"update crash_q set a=a+1 where a < 3",
2260
"drop table crash_q $drop_attr");
2263
# To add with the views:
2264
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
2265
report("views","views",
2266
"create view crash_q as select a from crash_me",
2267
"drop view crash_q $drop_attr");
2271
my $result = 'undefined';
2273
print "foreign keys: ";
2274
save_incomplete('foreign_key','foreign keys');
2276
# 1) check if foreign keys are supported
2277
safe_query_l('foreign_key',
2278
create_table("crash_me_qf",
2279
["a integer not null"],
2280
["primary key (a)"]));
2281
$error= safe_query_l('foreign_key',
2282
create_table("crash_me_qf2",
2283
["a integer not null",
2284
"foreign key (a) references crash_me_qf (a)"],
2287
if ($error == 1) # OK -- syntax is supported
2290
# now check if foreign key really works
2291
safe_query_l('foreign_key', "insert into crash_me_qf values (1)");
2292
if (safe_query_l('foreign_key', "insert into crash_me_qf2 values (2)") eq 1)
2294
$result = 'syntax only';
2305
safe_query_l('foreign_key', "drop table crash_me_qf2 $drop_attr");
2306
safe_query_l('foreign_key', "drop table crash_me_qf $drop_attr");
2308
save_config_data('foreign_key',$result,"foreign keys");
2311
if ($limits{'foreign_key'} eq 'yes')
2313
report("allows to update of foreign key values",'foreign_update',
2314
"create table crash_me1 (a int not null primary key)",
2315
"create table crash_me2 (a int not null," .
2316
" foreign key (a) references crash_me1 (a))",
2317
"insert into crash_me1 values (1)",
2318
"insert into crash_me2 values (1)",
2319
"update crash_me1 set a = 2", ## <- must fail
2320
"drop table crash_me2 $drop_attr",
2321
"drop table crash_me1 $drop_attr"
2325
report("Create SCHEMA","create_schema",
2326
"create schema crash_schema create table crash_q (a int) ".
2327
"create table crash_q2(b int)",
2328
"drop schema crash_schema cascade");
2330
if ($limits{'foreign_key'} eq 'yes')
2332
if ($limits{'create_schema'} eq 'yes')
2334
report("Circular foreign keys","foreign_key_circular",
2335
"create schema crash_schema create table crash_q ".
2336
"(a int primary key, b int, foreign key (b) references ".
2337
"crash_q2(a)) create table crash_q2(a int, b int, ".
2338
"primary key(a), foreign key (b) references crash_q(a))",
2339
"drop schema crash_schema cascade");
2343
if ($limits{'func_sql_character_length'} eq 'yes')
2345
my $result = 'error';
2347
my $key = 'length_of_varchar_field';
2348
my $prompt='CHARACTER_LENGTH(varchar_field)';
2349
print $prompt," = ";
2350
if (!defined($limits{$key})) {
2351
save_incomplete($key,$prompt);
2353
"CREATE TABLE crash_me1 (S1 VARCHAR(100))",
2354
"INSERT INTO crash_me1 VALUES ('X')"
2356
my $recset = get_recordset($key,
2357
"SELECT CHARACTER_LENGTH(S1) FROM crash_me1");
2358
print_recordset($key,$recset);
2359
if (defined($recset)){
2360
if ( $recset->[0][0] eq 1 ) {
2361
$result = 'actual length';
2362
} elsif( $recset->[0][0] eq 100 ) {
2363
$result = 'defined length';
2366
add_log($key,$DBI::errstr);
2368
safe_query_l($key, "drop table crash_me1 $drop_attr");
2369
save_config_data($key,$result,$prompt);
2371
$result = $limits{$key};
2377
check_constraint("Column constraints","constraint_check",
2378
"create table crash_q (a int check (a>0))",
2379
"insert into crash_q values(0)",
2380
"drop table crash_q $drop_attr");
2383
check_constraint("Table constraints","constraint_check_table",
2384
"create table crash_q (a int ,b int, check (a>b))",
2385
"insert into crash_q values(0,0)",
2386
"drop table crash_q $drop_attr");
2388
check_constraint("Named constraints","constraint_check_named",
2389
"create table crash_q (a int ,b int, constraint abc check (a>b))",
2390
"insert into crash_q values(0,0)",
2391
"drop table crash_q $drop_attr");
2394
report("NULL constraint (SyBase style)","constraint_null",
2395
"create table crash_q (a int null)",
2396
"drop table crash_q $drop_attr");
2398
report("Triggers (ANSI SQL)","psm_trigger",
2399
"create table crash_q (a int ,b int)",
2400
"create trigger crash_trigger after insert on crash_q referencing ".
2401
"new table as new_a when (localtime > time '18:00:00') ".
2403
"insert into crash_q values(1,2)",
2404
"drop trigger crash_trigger",
2405
"drop table crash_q $drop_attr");
2407
report("PSM procedures (ANSI SQL)","psm_procedures",
2408
"create table crash_q (a int,b int)",
2409
"create procedure crash_proc(in a1 int, in b1 int) language ".
2410
"sql modifies sql data begin declare c1 int; set c1 = a1 + b1;".
2411
" insert into crash_q(a,b) values (a1,c1); end",
2412
"call crash_proc(1,10)",
2413
"drop procedure crash_proc",
2414
"drop table crash_q $drop_attr");
2416
report("PSM modules (ANSI SQL)","psm_modules",
2417
"create table crash_q (a int,b int)",
2418
"create module crash_m declare procedure ".
2419
"crash_proc(in a1 int, in b1 int) language sql modifies sql ".
2420
"data begin declare c1 int; set c1 = a1 + b1; ".
2421
"insert into crash_q(a,b) values (a1,c1); end; ".
2422
"declare procedure crash_proc2(INOUT a int, in b int) ".
2423
"contains sql set a = b + 10; end module",
2424
"call crash_proc(1,10)",
2425
"drop module crash_m cascade",
2426
"drop table crash_q cascade $drop_attr");
2428
report("PSM functions (ANSI SQL)","psm_functions",
2429
"create table crash_q (a int)",
2430
"create function crash_func(in a1 int, in b1 int) returns int".
2431
" language sql deterministic contains sql ".
2432
" begin return a1 * b1; end",
2433
"insert into crash_q values(crash_func(2,4))",
2434
"select a,crash_func(a,2) from crash_q",
2435
"drop function crash_func cascade",
2436
"drop table crash_q $drop_attr");
2438
report("Domains (ANSI SQL)","domains",
2439
"create domain crash_d as varchar(10) default 'Empty' ".
2440
"check (value <> 'abcd')",
2441
"create table crash_q(a crash_d, b int)",
2442
"insert into crash_q(a,b) values('xyz',10)",
2443
"insert into crash_q(b) values(10)",
2444
"drop table crash_q $drop_attr",
2445
"drop domain crash_d");
2448
if (!defined($limits{'lock_tables'}))
2450
report("lock table","lock_tables",
2451
"lock table crash_me READ",
2453
if ($limits{'lock_tables'} eq 'no')
2455
delete $limits{'lock_tables'};
2456
report("lock table","lock_tables",
2457
"lock table crash_me IN SHARE MODE");
2461
if (!report("many tables to drop table","multi_drop",
2462
"create table crash_q (a int)",
2463
"create table crash_q2 (a int)",
2464
"drop table crash_q,crash_q2 $drop_attr"))
2466
$dbh->do("drop table crash_q $drop_attr");
2467
$dbh->do("drop table crash_q2 $drop_attr");
2470
if (!report("drop table with cascade/restrict","drop_restrict",
2471
"create table crash_q (a int)",
2472
"drop table crash_q restrict"))
2474
$dbh->do("drop table crash_q $drop_attr");
2478
report("-- as comment (ANSI)","comment_--",
2479
"select * from crash_me -- Testing of comments");
2480
report("// as comment","comment_//",
2481
"select * from crash_me // Testing of comments");
2482
report("# as comment","comment_#",
2483
"select * from crash_me # Testing of comments");
2484
report("/* */ as comment","comment_/**/",
2485
"select * from crash_me /* Testing of comments */");
2488
# Check things that fails one some servers
2491
# Empress can't insert empty strings in a char() field
2492
report("insert empty string","insert_empty_string",
2493
create_table("crash_q",["a char(10) not null,b char(10)"],[]),
2494
"insert into crash_q values ('','')",
2495
"drop table crash_q $drop_attr");
2497
report("Having with alias","having_with_alias",
2498
create_table("crash_q",["a integer"],[]),
2499
"insert into crash_q values (10)",
2500
"select sum(a) as b from crash_q group by a having b > 0",
2501
"drop table crash_q $drop_attr");
2507
find_limit("table name length","max_table_name",
2508
new query_many(["create table crash_q%s (q integer)",
2509
"insert into crash_q%s values(1)"],
2510
"select * from crash_q%s",1,
2511
["drop table crash_q%s $drop_attr"],
2512
$max_name_length,7,1));
2514
find_limit("column name length","max_column_name",
2515
new query_many(["create table crash_q (q%s integer)",
2516
"insert into crash_q (q%s) values(1)"],
2517
"select q%s from crash_q",1,
2518
["drop table crash_q $drop_attr"],
2519
$max_name_length,1));
2521
if ($limits{'column_alias'} eq 'yes')
2523
find_limit("select alias name length","max_select_alias_name",
2524
new query_many(undef,
2525
"select b as %s from crash_me",undef,
2526
undef, $max_name_length));
2529
find_limit("table alias name length","max_table_alias_name",
2530
new query_many(undef,
2531
"select %s.b from crash_me %s",
2533
undef, $max_name_length));
2535
$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
2536
$end_drop=$end_drop_keyword;
2537
$end_drop =~ s/%i/crash_q%s/;
2538
$end_drop =~ s/%t/crash_me/;
2540
if ($limits{'create_index'} ne 'no')
2542
find_limit("index name length","max_index_name",
2543
new query_many(["create index crash_q%s on crash_me (a)"],
2546
$max_name_length,7));
2549
find_limit("max char() size","max_char_size",
2550
new query_many(["create table crash_q (q char(%d))",
2551
"insert into crash_q values ('%s')"],
2552
"select * from crash_q","%s",
2553
["drop table crash_q $drop_attr"],
2554
min($max_string_size,$limits{'query_size'})));
2556
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2558
find_limit("max varchar() size","max_varchar_size",
2559
new query_many(["create table crash_q (q varchar(%d))",
2560
"insert into crash_q values ('%s')"],
2561
"select * from crash_q","%s",
2562
["drop table crash_q $drop_attr"],
2563
min($max_string_size,$limits{'query_size'})));
2567
foreach $type (('mediumtext','text','text()','blob','long'))
2569
if ($limits{"type_extra_$type"} eq 'yes')
2575
if (defined($found))
2577
$found =~ s/\(\)/\(%d\)/;
2578
find_limit("max text or blob size","max_text_size",
2579
new query_many(["create table crash_q (q $found)",
2580
"insert into crash_q values ('%s')"],
2581
"select * from crash_q","%s",
2582
["drop table crash_q $drop_attr"],
2583
min($max_string_size,$limits{'query_size'}-30)));
2587
$tmp=new query_repeat([],"create table crash_q (a integer","","",
2588
",a%d integer","",")",["drop table crash_q $drop_attr"],
2591
find_limit("Columns in table","max_columns",$tmp);
2593
# Make a field definition to be used when testing keys
2595
$key_definitions="q0 integer not null";
2597
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2599
$key_definitions.=",q$i integer not null";
2600
$key_fields.=",q$i";
2602
$key_values="1," x $i;
2605
if ($limits{'unique_in_create'} eq 'yes')
2607
find_limit("unique indexes","max_unique_index",
2608
new query_table("create table crash_q (q integer",
2609
",q%d integer not null,unique (q%d)",")",
2610
["insert into crash_q (q,%f) values (1,%v)"],
2611
"select q from crash_q",1,
2612
"drop table crash_q $drop_attr",
2615
find_limit("index parts","max_index_parts",
2616
new query_table("create table crash_q ".
2617
"($key_definitions,unique (q0",
2619
["insert into crash_q ($key_fields) values ($key_values)"],
2620
"select q0 from crash_q",1,
2621
"drop table crash_q $drop_attr",
2624
find_limit("max index part length","max_index_part_length",
2625
new query_many(["create table crash_q (q char(%d) not null,".
2627
"insert into crash_q (q) values ('%s')"],
2628
"select q from crash_q","%s",
2629
["drop table crash_q $drop_attr"],
2630
$limits{'max_char_size'},0));
2632
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2634
find_limit("index varchar part length","max_index_varchar_part_length",
2635
new query_many(["create table crash_q (q varchar(%d) not null,".
2637
"insert into crash_q (q) values ('%s')"],
2638
"select q from crash_q","%s",
2639
["drop table crash_q $drop_attr"],
2640
$limits{'max_varchar_size'},0));
2645
if ($limits{'create_index'} ne 'no')
2647
if ($limits{'create_index'} eq 'ignored' ||
2648
$limits{'unique_in_create'} eq 'yes')
2649
{ # This should be true
2650
add_log('max_index',
2651
" max_unique_index=$limits{'max_unique_index'} ,".
2652
"so max_index must be same");
2653
save_config_data('max_index',$limits{'max_unique_index'},"max index");
2654
print "indexes: $limits{'max_index'}\n";
2658
if (!defined($limits{'max_index'}))
2660
safe_query_l('max_index',"create table crash_q ($key_definitions)");
2661
for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
2663
last if (!safe_query_l('max_index',
2664
"create index crash_q$i on crash_q (q$i)"));
2666
save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
2670
$end_drop=$end_drop_keyword;
2671
$end_drop =~ s/%i/crash_q$i/;
2672
$end_drop =~ s/%t/crash_q/;
2673
assert_crash($end_drop);
2675
assert_crash("drop table crash_q $drop_attr");
2677
print "indexs: $limits{'max_index'}\n";
2678
if (!defined($limits{'max_unique_index'}))
2680
safe_query_l('max_unique_index',
2681
"create table crash_q ($key_definitions)");
2682
for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2684
last if (!safe_query_l('max_unique_index',
2685
"create unique index crash_q$i on crash_q (q$i)"));
2687
save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
2688
"max unique index");
2691
$end_drop=$end_drop_keyword;
2692
$end_drop =~ s/%i/crash_q$i/;
2693
$end_drop =~ s/%t/crash_q/;
2694
assert_crash($end_drop);
2696
assert_crash("drop table crash_q $drop_attr");
2698
print "unique indexes: $limits{'max_unique_index'}\n";
2699
if (!defined($limits{'max_index_parts'}))
2701
safe_query_l('max_index_parts',
2702
"create table crash_q ($key_definitions)");
2703
$end_drop=$end_drop_keyword;
2704
$end_drop =~ s/%i/crash_q1%d/;
2705
$end_drop =~ s/%t/crash_q/;
2706
find_limit("index parts","max_index_parts",
2707
new query_table("create index crash_q1%d on crash_q (q0",
2713
assert_crash("drop table crash_q $drop_attr");
2717
print "index parts: $limits{'max_index_parts'}\n";
2719
$end_drop=$end_drop_keyword;
2720
$end_drop =~ s/%i/crash_q2%d/;
2721
$end_drop =~ s/%t/crash_me/;
2723
find_limit("index part length","max_index_part_length",
2724
new query_many(["create table crash_q (q char(%d))",
2725
"create index crash_q2%d on crash_q (q)",
2726
"insert into crash_q values('%s')"],
2727
"select q from crash_q",
2730
"drop table crash_q $drop_attr"],
2731
min($limits{'max_char_size'},"+8192")));
2735
find_limit("index length","max_index_length",
2736
new query_index_length("create table crash_q ",
2737
"drop table crash_q $drop_attr",
2740
find_limit("max table row length (without blobs)","max_row_length",
2741
new query_row_length("crash_q ",
2743
"drop table crash_q $drop_attr",
2744
min($max_row_length,
2745
$limits{'max_columns'}*
2746
min($limits{'max_char_size'},255))));
2748
find_limit("table row length with nulls (without blobs)",
2749
"max_row_length_with_null",
2750
new query_row_length("crash_q ",
2752
"drop table crash_q $drop_attr",
2753
$limits{'max_row_length'}*2));
2755
find_limit("number of columns in order by","columns_in_order_by",
2756
new query_many(["create table crash_q (%F)",
2757
"insert into crash_q values(%v)",
2758
"insert into crash_q values(%v)"],
2759
"select * from crash_q order by %f",
2761
["drop table crash_q $drop_attr"],
2764
find_limit("number of columns in group by","columns_in_group_by",
2765
new query_many(["create table crash_q (%F)",
2766
"insert into crash_q values(%v)",
2767
"insert into crash_q values(%v)"],
2768
"select %f from crash_q group by %f",
2770
["drop table crash_q $drop_attr"],
2775
# Safe arithmetic test
2777
$prompt="safe decimal arithmetic";
2778
$key="safe_decimal_arithmetic";
2779
if (!defined($limits{$key}))
2782
save_incomplete($key,$prompt);
2783
if (!safe_query_l($key,$server->create("crash_me_a",
2784
["a decimal(10,2)","b decimal(10,2)"])))
2786
print DBI->errstr();
2787
die "Can't create table 'crash_me_a' $DBI::errstr\n";
2790
if (!safe_query_l($key,
2791
["insert into crash_me_a (a,b) values (11.4,18.9)"]))
2793
die "Can't insert into table 'crash_me_a' a record: $DBI::errstr\n";
2796
$arithmetic_safe = 'no';
2797
$arithmetic_safe = 'yes'
2798
if ( (safe_query_result_l($key,
2799
'select count(*) from crash_me_a where a+b=30.3',1,0) == 0)
2800
and (safe_query_result_l($key,
2801
'select count(*) from crash_me_a where a+b-30.3 = 0',1,0) == 0)
2802
and (safe_query_result_l($key,
2803
'select count(*) from crash_me_a where a+b-30.3 < 0',0,0) == 0)
2804
and (safe_query_result_l($key,
2805
'select count(*) from crash_me_a where a+b-30.3 > 0',0,0) == 0));
2806
save_config_data($key,$arithmetic_safe,$prompt);
2807
print "$arithmetic_safe\n";
2808
assert_crash("drop table crash_me_a $drop_attr");
2812
print "$prompt=$limits{$key} (cached)\n";
2815
# Check where is null values in sorted recordset
2816
if (!safe_query($server->create("crash_me_n",["i integer","r integer"])))
2818
print DBI->errstr();
2819
die "Can't create table 'crash_me_n' $DBI::errstr\n";
2822
safe_query_l("position_of_null",["insert into crash_me_n (i) values(1)",
2823
"insert into crash_me_n values(2,2)",
2824
"insert into crash_me_n values(3,3)",
2825
"insert into crash_me_n values(4,4)",
2826
"insert into crash_me_n (i) values(5)"]);
2828
$key = "position_of_null";
2829
$prompt ="Where is null values in sorted recordset";
2830
if (!defined($limits{$key}))
2832
save_incomplete($key,$prompt);
2834
$sth=$dbh->prepare("select r from crash_me_n order by r ");
2836
add_log($key,"< select r from crash_me_n order by r ");
2837
$limit= detect_null_position($key,$sth);
2840
save_config_data($key,$limit,$prompt);
2842
print "$prompt=$limits{$key} (cache)\n";
2845
$key = "position_of_null_desc";
2846
$prompt ="Where is null values in sorted recordset (DESC)";
2847
if (!defined($limits{$key}))
2849
save_incomplete($key,$prompt);
2851
$sth=$dbh->prepare("select r from crash_me_n order by r desc");
2853
add_log($key,"< select r from crash_me_n order by r desc");
2854
$limit= detect_null_position($key,$sth);
2857
save_config_data($key,$limit,$prompt);
2859
print "$prompt=$limits{$key} (cache)\n";
2863
assert_crash("drop table crash_me_n $drop_attr");
2867
$key = 'sorted_group_by';
2868
$prompt = 'Group by always sorted';
2869
if (!defined($limits{$key}))
2871
save_incomplete($key,$prompt);
2874
"create table crash_me_t1 (a int not null, b int not null)",
2875
"insert into crash_me_t1 values (1,1)",
2876
"insert into crash_me_t1 values (1,2)",
2877
"insert into crash_me_t1 values (3,1)",
2878
"insert into crash_me_t1 values (3,2)",
2879
"insert into crash_me_t1 values (2,2)",
2880
"insert into crash_me_t1 values (2,1)",
2881
"create table crash_me_t2 (a int not null, b int not null)",
2882
"create index crash_me_t2_ind on crash_me_t2 (a)",
2883
"insert into crash_me_t2 values (1,3)",
2884
"insert into crash_me_t2 values (3,1)",
2885
"insert into crash_me_t2 values (2,2)",
2886
"insert into crash_me_t2 values (1,1)"]);
2888
my $bigqry = "select crash_me_t1.a,crash_me_t2.b from ".
2889
"crash_me_t1,crash_me_t2 where crash_me_t1.a=crash_me_t2.a ".
2890
"group by crash_me_t1.a,crash_me_t2.b";
2893
my $rs = get_recordset($key,$bigqry);
2894
print_recordset($key,$rs);
2895
if ( defined ($rs)) {
2896
if (compare_recordset($key,$rs,[[1,1],[1,3],[2,2],[3,1]]) eq 0)
2901
add_log($key,"error: ".$DBI::errstr);
2905
safe_query_l($key,["drop table crash_me_t1",
2906
"drop table crash_me_t2"]);
2907
save_config_data($key,$limit,$prompt);
2910
print "$prompt=$limits{$key} (cashed)\n";
2918
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2920
print "crash-me safe: $limits{'crash_me_safe'}\n";
2921
print "reconnected $reconnect_count times\n";
2923
$dbh->disconnect || warn $dbh->errstr;
2924
save_all_config_data();
2930
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2932
print "crash-me safe: $limits{'crash_me_safe'}\n";
2933
print "reconnected $reconnect_count times\n";
2935
$dbh->disconnect || warn $dbh->errstr;
2936
save_all_config_data();
2939
# Check where is nulls in the sorted result (for)
2940
# it expects exactly 5 rows in the result
2942
sub detect_null_position
2946
my ($z,$r1,$r2,$r3,$r4,$r5);
2947
$r1 = $sth->fetchrow_array; add_log($key,"> $r1");
2948
$r2 = $sth->fetchrow_array; add_log($key,"> $r2");
2949
$r3 = $sth->fetchrow_array; add_log($key,"> $r3");
2950
$r4 = $sth->fetchrow_array; add_log($key,"> $r4");
2951
$r5 = $sth->fetchrow_array; add_log($key,"> $r5");
2952
return "first" if ( !defined($r1) && !defined($r2) && defined($r3));
2953
return "last" if ( !defined($r5) && !defined($r4) && defined($r3));
2957
sub check_parenthesis {
2961
my $param_name=$prefix.lc($fn);
2964
save_incomplete($param_name,$fn);
2965
$r = safe_query("select $fn $end_query");
2966
add_log($param_name,$safe_query_log);
2972
$r = safe_query("select $fn() $end_query");
2973
add_log($param_name,$safe_query_log);
2976
$result="with_parenthesis";
2980
save_config_data($param_name,$result,$fn);
2983
sub check_constraint {
2989
save_incomplete($key,$prompt);
2993
$t=safe_query($create);
2994
add_log($key,$safe_query_log);
2998
$t= safe_query($check);
2999
add_log($key,$safe_query_log);
3006
add_log($key,$safe_query_log);
3008
save_config_data($key,$res,$prompt);
3016
$_ = $limits{'time_format_inresult'};
3017
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second if (/^iso$/);
3018
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second if (/^euro/);
3019
return sprintf "%02d:%02d %s",
3020
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
3022
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second if (/^HHMMSS/);
3023
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second if (/^HHHHMMSS/);
3024
return "UNKNOWN FORMAT";
3031
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second
3032
if ($limits{'time_format_ISO'} eq "yes");
3033
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second
3034
if ($limits{'time_format_EUR'} eq "yes");
3035
return sprintf "%02d:%02d %s",
3036
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
3037
if ($limits{'time_format_USA'} eq "yes");
3038
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second
3039
if ($limits{'time_format_HHMMSS'} eq "yes");
3040
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second
3041
if ($limits{'time_format_HHHHMMSS'} eq "yes");
3042
return "UNKNOWN FORMAT";
3049
$_ = $limits{'date_format_inresult'};
3050
return sprintf "%02d-%02d-%02d", ($year%100),$month,$day if (/^short iso$/);
3051
return sprintf "%04d-%02d-%02d", $year,$month,$day if (/^iso/);
3052
return sprintf "%02d.%02d.%02d", $day,$month,($year%100) if (/^short euro/);
3053
return sprintf "%02d.%02d.%04d", $day,$month,$year if (/^euro/);
3054
return sprintf "%02d/%02d/%02d", $month,$day,($year%100) if (/^short usa/);
3055
return sprintf "%02d/%02d/%04d", $month,$day,$year if (/^usa/);
3056
return sprintf "%04d%02d%02d", $year,$month,$day if (/^YYYYMMDD/);
3057
return "UNKNOWN FORMAT";
3065
return sprintf "'%04d-%02d-%02d'", $year,$month,$day
3066
if ($limits{'date_format_ISO'} eq 'yes');
3067
return sprintf "DATE '%04d-%02d-%02d'", $year,$month,$day
3068
if ($limits{'date_format_ISO_with_date'} eq 'yes');
3069
return sprintf "'%02d.%02d.%04d'", $day,$month,$year
3070
if ($limits{'date_format_EUR'} eq 'yes');
3071
return sprintf "DATE '%02d.%02d.%04d'", $day,$month,$year
3072
if ($limits{'date_format_EUR_with_date'} eq 'yes');
3073
return sprintf "'%02d/%02d/%04d'", $month,$day,$year
3074
if ($limits{'date_format_USA'} eq 'yes');
3075
return sprintf "DATE '%02d/%02d/%04d'", $month,$day,$year
3076
if ($limits{'date_format_USA_with_date'} eq 'yes');
3077
return sprintf "'%04d%02d%02d'", $year,$month,$day
3078
if ($limits{'date_format_YYYYMMDD'} eq 'yes');
3079
return sprintf "DATE '%04d%02d%02d'", $year,$month,$day
3080
if ($limits{'date_format_YYYYMMDD_with_date'} eq 'yes');
3081
return "UNKNOWN FORMAT";
3085
sub print_recordset{
3086
my ($key,$recset) = @_;
3088
foreach $rec (@$recset)
3090
add_log($key, " > ".join(',', map(repr($_), @$rec)));
3095
# read result recordset from sql server.
3096
# returns arrayref to (arrayref to) values
3097
# or undef (in case of sql errors)
3100
my ($key,$query) = @_;
3101
add_log($key, "< $query");
3102
return $dbh->selectall_arrayref($query);
3105
# function for comparing recordset (that was returned by get_recordset)
3106
# and arrayref of (arrayref of) values.
3108
# returns : zero if recordset equal that array, 1 if it doesn't equal
3111
# $key - current operation (for logging)
3112
# $recset - recordset
3113
# $mustbe - array of values that we expect
3115
# example: $a=get_recordset('some_parameter','select a,b from c');
3116
# if (compare_recordset('some_parameter',$a,[[1,1],[1,2],[1,3]]) neq 0)
3118
# print "unexpected result\n";
3121
sub compare_recordset {
3122
my ($key,$recset,$mustbe) = @_;
3123
my ($rec,$recno,$fldno,$fcount);
3124
add_log($key,"\n Check recordset:");
3126
foreach $rec (@$recset)
3128
add_log($key," " . join(',', map(repr($_),@$rec)) . " expected: " .
3129
join(',', map(repr($_), @{$mustbe->[$recno]} ) ));
3132
foreach $fldno (0 .. $fcount )
3134
if ($mustbe->[$recno][$fldno] ne $rec->[$fldno])
3136
add_log($key," Recordset doesn't correspond with template");
3142
add_log($key," Recordset corresponds with template");
3147
# converts inner perl value to printable representation
3148
# for example: undef maps to 'NULL',
3149
# string -> 'string'
3154
return "'$s'"if ($s =~ /\D/);
3155
return 'NULL'if ( not defined($s));
3162
print "$0 Ver $version\n";
3171
This program tries to find all limits and capabilities for a SQL
3172
server. As it will use the server in some 'unexpected' ways, one
3173
shouldn\'t have anything important running on it at the same time this
3174
program runs! There is a slight chance that something unexpected may
3177
As all used queries are legal according to some SQL standard. any
3178
reasonable SQL server should be able to run this test without any
3181
All questions is cached in $opt_dir/'server_name'[-suffix].cfg that
3182
future runs will use limits found in previous runs. Remove this file
3183
if you want to find the current limits for your version of the
3186
This program uses some table names while testing things. If you have any
3187
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
3188
they will be deleted by this test!
3190
$0 takes the following options:
3192
--help or --Information
3196
Don\'t ask any questions, quit on errors.
3199
Do a new connection to the server every time crash-me checks if the server
3200
is alive. This can help in cases where the server starts returning wrong
3201
data because of an earlier select.
3203
--comment='some comment'
3204
Add this comment to the crash-me limit file
3206
--config-file='filename'
3207
Read limit results from specific file
3209
--connect-options='some connect options'
3210
Add options, which uses at DBI connect.
3211
For example --connect-options=mysql_read_default_file=/etc/my.cnf.
3213
--database='database' (Default $opt_database)
3214
Create test tables in this database.
3217
Lots of printing to help debugging if something goes wrong.
3220
Save crash-me output in this directory
3223
Reformat the crash-me limit file. crash-me is not run!
3226
Start test at once, without a warning screen and without questions.
3227
This is a option for the very brave.
3228
Use this in your cron scripts to test your database every night.
3231
Prints all queries that are executed. Mostly used for debugging crash-me.
3233
--log-queries-to-file='filename'
3234
Log full queries to file.
3236
--host='hostname' (Default $opt_host)
3237
Run tests on this host.
3239
--password='password'
3240
Password for the current user.
3243
Save states during each limit tests. This will make it possible to continue
3244
by restarting with the same options if there is some bug in the DBI or
3245
DBD driver that caused $0 to die!
3247
--server='server name' (Default $opt_server)
3248
Run the test on the given server.
3249
Known servers names are: Access, Adabas, AdabasD, Empress, Oracle,
3250
Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid or Sybase.
3251
For others $0 can\'t report the server version.
3253
--suffix='suffix' (Default '')
3254
Add suffix to the output filename. For instance if you run crash-me like
3255
"crash-me --suffix="myisam",
3256
then output filename will look "mysql-myisam.cfg".
3259
User name to log into the SQL server.
3261
--db-start-cmd='command to restart server'
3262
Automaticly restarts server with this command if the database server dies.
3264
--sleep='time in seconds' (Default $opt_sleep)
3265
Wait this long before restarting server.
3269
Log into the result file queries performed for determination parameter value
3280
print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
3281
if (lc($opt_server) eq "mysql")
3285
This test should not crash MySQL if it was distributed together with the
3286
running MySQL version.
3287
If this is the case you can probably continue without having to worry about
3288
destroying something.
3291
elsif (lc($opt_server) eq "msql")
3294
This test will take down mSQL repeatedly while finding limits.
3295
To make this test easier, start mSQL in another terminal with something like:
3297
while (true); do /usr/local/mSQL/bin/msql2d ; done
3299
You should be sure that no one is doing anything important with mSQL and that
3300
you have privileges to restart it!
3301
It may take awhile to determinate the number of joinable tables, so prepare to
3305
elsif (lc($opt_server) eq "solid")
3308
This test will take down Solid server repeatedly while finding limits.
3309
You should be sure that no one is doing anything important with Solid
3310
and that you have privileges to restart it!
3312
If you are running Solid without logging and/or backup YOU WILL LOSE!
3313
Solid does not write data from the cache often enough. So if you continue
3314
you may lose tables and data that you entered hours ago!
3316
Solid will also take a lot of memory running this test. You will nead
3319
When doing the connect test Solid server or the perl api will hang when
3320
freeing connections. Kill this program and restart it to continue with the
3321
test. You don\'t have to use --restart for this case.
3325
print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
3326
print "with --restart until all tests have completed\n";
3329
elsif (lc($opt_server) eq "pg")
3332
This test will crash postgreSQL when calculating the number of joinable tables!
3333
You should be sure that no one is doing anything important with postgreSQL
3334
and that you have privileges to restart it!
3340
This test may crash $opt_server repeatedly while finding limits!
3341
You should be sure that no one is doing anything important with $opt_server
3342
and that you have privileges to restart it!
3347
Some of the tests you are about to execute may require a lot of
3348
memory. Your tests WILL adversely affect system performance. It\'s
3349
not uncommon that either this crash-me test program, or the actual
3350
database back-end, will DIE with an out-of-memory error. So might
3351
any other program on your system if it requests more memory at the
3354
Note also that while crash-me tries to find limits for the database server
3355
it will make a lot of queries that can\'t be categorized as \'normal\'. It\'s
3356
not unlikely that crash-me finds some limit bug in your server so if you
3357
run this test you have to be prepared that your server may die during it!
3359
We, the creators of this utility, are not responsible in any way if your
3360
database server unexpectedly crashes while this program tries to find the
3361
limitations of your server. By accepting the following question with \'yes\',
3362
you agree to the above!
3364
You have been warned!
3369
# No default reply here so no one can blame us for starting the test
3374
print "Start test (yes/no) ? ";
3375
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3376
last if ($tmp =~ /^yes$/i);
3377
exit 1 if ($tmp =~ /^n/i);
3384
# my @name = POSIX::uname();
3385
# my $name= $name[0] . " " . $name[2] . " " . $name[4];
3391
# Help functions that we need
3401
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3402
{ PrintError => 0, AutoCommit => 1})))
3404
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3407
print "Error: $DBI::errstr; $server->{'data_source'} ".
3408
" - '$opt_user' - '$opt_password'\n";
3409
print "I got the above error when connecting to $opt_server\n";
3410
if (defined($object) && defined($object->{'limit'}))
3412
print "This check was done with limit: $object->{'limit'}.".
3413
"\nNext check will be done with a smaller limit!\n";
3416
save_config_data('crash_me_safe','no',"crash me safe");
3417
if ($opt_db_start_cmd)
3419
print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
3420
system("$opt_db_start_cmd");
3421
print "Waiting $opt_sleep seconds so the server can initialize\n";
3426
exit(1) if ($opt_batch_mode);
3427
print "Can you check/restart it so I can continue testing?\n";
3430
print "Continue test (yes/no) ? [yes] ";
3431
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3432
$tmp = "yes" if ($tmp eq "");
3433
last if (index("yes",$tmp) >= 0);
3434
exit 1 if (index("no",$tmp) >= 0);
3442
# Test connecting a couple of times before giving an error
3443
# This is needed to get the server time to free old connections
3444
# after the connect test
3450
for ($i=0 ; $i < 10 ; $i++)
3452
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3453
{ PrintError => 0, AutoCommit => 1})))
3455
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3460
return safe_connect();
3464
# Check if the server is up and running. If not, ask the user to restart it
3471
print "Checking connection\n" if ($opt_log_all_queries);
3472
# The following line will not work properly with interbase
3473
if ($opt_check_server && defined($check_connect) && $dbh->{AutoCommit} != 0)
3477
$dbh=safe_connect($object);
3480
return if (defined($check_connect) && defined($dbh->do($check_connect)));
3481
$dbh->disconnect || warn $dbh->errstr;
3482
print "\nreconnecting\n" if ($opt_debug);
3485
$dbh=safe_connect($object);
3489
# print query if debugging
3493
if (length($query) > 130)
3495
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3503
$last_error=$DBI::errstr;
3506
if (length($query) > 130)
3508
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3510
printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
3515
# Do one or many queries. Return 1 if all was ok
3516
# Note that all rows are executed
3517
# (to ensure that we execute drop table commands)
3523
my $r = safe_query($q);
3524
add_log($key,$safe_query_log);
3531
my($query,$ok,$retry_ok,$retry,@tmp,$sth);
3534
if (ref($queries) ne "ARRAY")
3536
push(@tmp,$queries);
3539
foreach $query (@$queries)
3541
printf "query1: %-80.80s ...(%d - %d)\n",$query,
3542
length($query),$retry_limit if ($opt_log_all_queries);
3543
print LOG "$query;\n" if ($opt_log);
3544
$safe_query_log .= "< $query\n";
3545
if (length($query) > $query_size)
3548
$safe_query_log .= "Query is too long\n";
3553
for ($retry=0; $retry < $retry_limit ; $retry++)
3555
if (! ($sth=$dbh->prepare($query)))
3557
print_query($query);
3558
$safe_query_log .= "> couldn't prepare:". $dbh->errstr. "\n";
3559
$retry=100 if (!$server->abort_if_fatal_error());
3560
# Force a reconnect because of Access drop table bug!
3561
if ($retry == $retry_limit-2)
3563
print "Forcing disconnect to retry query\n" if ($opt_debug);
3564
$dbh->disconnect || warn $dbh->errstr;
3566
check_connect(); # Check that server is still up
3570
if (!$sth->execute())
3572
print_query($query);
3573
$safe_query_log .= "> execute error:". $dbh->errstr. "\n";
3574
$retry=100 if (!$server->abort_if_fatal_error());
3575
# Force a reconnect because of Access drop table bug!
3576
if ($retry == $retry_limit-2)
3578
print "Forcing disconnect to retry query\n" if ($opt_debug);
3579
$dbh->disconnect || warn $dbh->errstr;
3581
check_connect(); # Check that server is still up
3585
$retry = $retry_limit;
3587
$safe_query_log .= "> OK\n";
3592
$ok=0 if (!$retry_ok);
3593
if ($query =~ /create/i && $server->reconnect_on_errors())
3595
print "Forcing disconnect to retry query\n" if ($opt_debug);
3596
$dbh->disconnect || warn $dbh->errstr;
3597
$dbh=safe_connect();
3603
sub check_reserved_words
3607
my ($answer, $prompt, $config, $keyword_type);
3609
my @keywords_ext = ( "ansi-92/99", "ansi92", "ansi99", "extra");
3611
my %reserved_words = (
3612
'ABSOLUTE' => 0, 'ACTION' => 0, 'ADD' => 0,
3613
'AFTER' => 0, 'ALIAS' => 0, 'ALL' => 0,
3614
'ALLOCATE' => 0, 'ALTER' => 0, 'AND' => 0,
3615
'ANY' => 0, 'ARE' => 0, 'AS' => 0,
3616
'ASC' => 0, 'ASSERTION' => 0, 'AT' => 0,
3617
'AUTHORIZATION' => 0, 'BEFORE' => 0, 'BEGIN' => 0,
3618
'BIT' => 0, 'BOOLEAN' => 0, 'BOTH' => 0,
3619
'BREADTH' => 0, 'BY' => 0, 'CALL' => 0,
3620
'CASCADE' => 0, 'CASCADED' => 0, 'CASE' => 0,
3621
'CAST' => 0, 'CATALOG' => 0, 'CHAR' => 0,
3622
'CHARACTER' => 0, 'CHECK' => 0, 'CLOSE' => 0,
3623
'COLLATE' => 0, 'COLLATION' => 0, 'COLUMN' => 0,
3624
'COMMIT' => 0, 'COMPLETION' => 0, 'CONNECT' => 0,
3625
'CONNECTION' => 0, 'CONSTRAINT' => 0, 'CONSTRAINTS' => 0,
3626
'CONTINUE' => 0, 'CORRESPONDING' => 0, 'CREATE' => 0,
3627
'CROSS' => 0, 'CURRENT' => 0, 'CURRENT_DATE' => 0,
3628
'CURRENT_TIME' => 0,'CURRENT_TIMESTAMP' => 0, 'CURRENT_USER' => 0,
3629
'CURSOR' => 0, 'CYCLE' => 0, 'DATA' => 0,
3630
'DATE' => 0, 'DAY' => 0, 'DEALLOCATE' => 0,
3631
'DEC' => 0, 'DECIMAL' => 0, 'DECLARE' => 0,
3632
'DEFAULT' => 0, 'DEFERRABLE' => 0, 'DEFERRED' => 0,
3633
'DELETE' => 0, 'DEPTH' => 0, 'DESC' => 0,
3634
'DESCRIBE' => 0, 'DESCRIPTOR' => 0, 'DIAGNOSTICS' => 0,
3635
'DICTIONARY' => 0, 'DISCONNECT' => 0, 'DISTINCT' => 0,
3636
'DOMAIN' => 0, 'DOUBLE' => 0, 'DROP' => 0,
3637
'EACH' => 0, 'ELSE' => 0, 'ELSEIF' => 0,
3638
'END' => 0, 'END-EXEC' => 0, 'EQUALS' => 0,
3639
'ESCAPE' => 0, 'EXCEPT' => 0, 'EXCEPTION' => 0,
3640
'EXEC' => 0, 'EXECUTE' => 0, 'EXTERNAL' => 0,
3641
'FALSE' => 0, 'FETCH' => 0, 'FIRST' => 0,
3642
'FLOAT' => 0, 'FOR' => 0, 'FOREIGN' => 0,
3643
'FOUND' => 0, 'FROM' => 0, 'FULL' => 0,
3644
'GENERAL' => 0, 'GET' => 0, 'GLOBAL' => 0,
3645
'GO' => 0, 'GOTO' => 0, 'GRANT' => 0,
3646
'GROUP' => 0, 'HAVING' => 0, 'HOUR' => 0,
3647
'IDENTITY' => 0, 'IF' => 0, 'IGNORE' => 0,
3648
'IMMEDIATE' => 0, 'IN' => 0, 'INDICATOR' => 0,
3649
'INITIALLY' => 0, 'INNER' => 0, 'INPUT' => 0,
3650
'INSERT' => 0, 'INT' => 0, 'INTEGER' => 0,
3651
'INTERSECT' => 0, 'INTERVAL' => 0, 'INTO' => 0,
3652
'IS' => 0, 'ISOLATION' => 0, 'JOIN' => 0,
3653
'KEY' => 0, 'LANGUAGE' => 0, 'LAST' => 0,
3654
'LEADING' => 0, 'LEAVE' => 0, 'LEFT' => 0,
3655
'LESS' => 0, 'LEVEL' => 0, 'LIKE' => 0,
3656
'LIMIT' => 0, 'LOCAL' => 0, 'LOOP' => 0,
3657
'MATCH' => 0, 'MINUTE' => 0, 'MODIFY' => 0,
3658
'MODULE' => 0, 'MONTH' => 0, 'NAMES' => 0,
3659
'NATIONAL' => 0, 'NATURAL' => 0, 'NCHAR' => 0,
3660
'NEW' => 0, 'NEXT' => 0, 'NO' => 0,
3661
'NONE' => 0, 'NOT' => 0, 'NULL' => 0,
3662
'NUMERIC' => 0, 'OBJECT' => 0, 'OF' => 0,
3663
'OFF' => 0, 'OLD' => 0, 'ON' => 0,
3664
'ONLY' => 0, 'OPEN' => 0, 'OPERATION' => 0,
3665
'OPTION' => 0, 'OR' => 0, 'ORDER' => 0,
3666
'OUTER' => 0, 'OUTPUT' => 0, 'PAD' => 0,
3667
'PARAMETERS' => 0, 'PARTIAL' => 0, 'PRECISION' => 0,
3668
'PREORDER' => 0, 'PREPARE' => 0, 'PRESERVE' => 0,
3669
'PRIMARY' => 0, 'PRIOR' => 0, 'PRIVILEGES' => 0,
3670
'PROCEDURE' => 0, 'PUBLIC' => 0, 'READ' => 0,
3671
'REAL' => 0, 'RECURSIVE' => 0, 'REF' => 0,
3672
'REFERENCES' => 0, 'REFERENCING' => 0, 'RELATIVE' => 0,
3673
'RESIGNAL' => 0, 'RESTRICT' => 0, 'RETURN' => 0,
3674
'RETURNS' => 0, 'REVOKE' => 0, 'RIGHT' => 0,
3675
'ROLE' => 0, 'ROLLBACK' => 0, 'ROUTINE' => 0,
3676
'ROW' => 0, 'ROWS' => 0, 'SAVEPOINT' => 0,
3677
'SCHEMA' => 0, 'SCROLL' => 0, 'SEARCH' => 0,
3678
'SECOND' => 0, 'SECTION' => 0, 'SELECT' => 0,
3679
'SEQUENCE' => 0, 'SESSION' => 0, 'SESSION_USER' => 0,
3680
'SET' => 0, 'SIGNAL' => 0, 'SIZE' => 0,
3681
'SMALLINT' => 0, 'SOME' => 0, 'SPACE' => 0,
3682
'SQL' => 0, 'SQLEXCEPTION' => 0, 'SQLSTATE' => 0,
3683
'SQLWARNING' => 0, 'STRUCTURE' => 0, 'SYSTEM_USER' => 0,
3684
'TABLE' => 0, 'TEMPORARY' => 0, 'THEN' => 0,
3685
'TIME' => 0, 'TIMESTAMP' => 0, 'TIMEZONE_HOUR' => 0,
3686
'TIMEZONE_MINUTE' => 0, 'TO' => 0, 'TRAILING' => 0,
3687
'TRANSACTION' => 0, 'TRANSLATION' => 0, 'TRIGGER' => 0,
3688
'TRUE' => 0, 'UNDER' => 0, 'UNION' => 0,
3689
'UNIQUE' => 0, 'UNKNOWN' => 0, 'UPDATE' => 0,
3690
'USAGE' => 0, 'USER' => 0, 'USING' => 0,
3691
'VALUE' => 0, 'VALUES' => 0, 'VARCHAR' => 0,
3692
'VARIABLE' => 0, 'VARYING' => 0, 'VIEW' => 0,
3693
'WHEN' => 0, 'WHENEVER' => 0, 'WHERE' => 0,
3694
'WHILE' => 0, 'WITH' => 0, 'WITHOUT' => 0,
3695
'WORK' => 0, 'WRITE' => 0, 'YEAR' => 0,
3698
'ASYNC' => 1, 'AVG' => 1, 'BETWEEN' => 1,
3699
'BIT_LENGTH' => 1,'CHARACTER_LENGTH' => 1, 'CHAR_LENGTH' => 1,
3700
'COALESCE' => 1, 'CONVERT' => 1, 'COUNT' => 1,
3701
'EXISTS' => 1, 'EXTRACT' => 1, 'INSENSITIVE' => 1,
3702
'LOWER' => 1, 'MAX' => 1, 'MIN' => 1,
3703
'NULLIF' => 1, 'OCTET_LENGTH' => 1, 'OID' => 1,
3704
'OPERATORS' => 1, 'OTHERS' => 1, 'OVERLAPS' => 1,
3705
'PENDANT' => 1, 'POSITION' => 1, 'PRIVATE' => 1,
3706
'PROTECTED' => 1, 'REPLACE' => 1, 'SENSITIVE' => 1,
3707
'SIMILAR' => 1, 'SQLCODE' => 1, 'SQLERROR' => 1,
3708
'SUBSTRING' => 1, 'SUM' => 1, 'TEST' => 1,
3709
'THERE' => 1, 'TRANSLATE' => 1, 'TRIM' => 1,
3710
'TYPE' => 1, 'UPPER' => 1, 'VIRTUAL' => 1,
3711
'VISIBLE' => 1, 'WAIT' => 1,
3713
'ADMIN' => 2, 'AGGREGATE' => 2, 'ARRAY' => 2,
3714
'BINARY' => 2, 'BLOB' => 2, 'CLASS' => 2,
3715
'CLOB' => 2, 'CONDITION' => 2, 'CONSTRUCTOR' => 2,
3716
'CONTAINS' => 2, 'CUBE' => 2, 'CURRENT_PATH' => 2,
3717
'CURRENT_ROLE' => 2, 'DATALINK' => 2, 'DEREF' => 2,
3718
'DESTROY' => 2, 'DESTRUCTOR' => 2, 'DETERMINISTIC' => 2,
3719
'DO' => 2, 'DYNAMIC' => 2, 'EVERY' => 2,
3720
'EXIT' => 2, 'EXPAND' => 2, 'EXPANDING' => 2,
3721
'FREE' => 2, 'FUNCTION' => 2, 'GROUPING' => 2,
3722
'HANDLER' => 2, 'HAST' => 2, 'HOST' => 2,
3723
'INITIALIZE' => 2, 'INOUT' => 2, 'ITERATE' => 2,
3724
'LARGE' => 2, 'LATERAL' => 2, 'LOCALTIME' => 2,
3725
'LOCALTIMESTAMP' => 2, 'LOCATOR' => 2, 'MEETS' => 2,
3726
'MODIFIES' => 2, 'NCLOB' => 2, 'NORMALIZE' => 2,
3727
'ORDINALITY' => 2, 'OUT' => 2, 'PARAMETER' => 2,
3728
'PATH' => 2, 'PERIOD' => 2, 'POSTFIX' => 2,
3729
'PRECEDES' => 2, 'PREFIX' => 2, 'READS' => 2,
3730
'REDO' => 2, 'REPEAT' => 2, 'RESULT' => 2,
3731
'ROLLUP' => 2, 'SETS' => 2, 'SPECIFIC' => 2,
3732
'SPECIFICTYPE' => 2, 'START' => 2, 'STATE' => 2,
3733
'STATIC' => 2, 'SUCCEEDS' => 2, 'TERMINATE' => 2,
3734
'THAN' => 2, 'TREAT' => 2, 'UNDO' => 2,
3737
'ACCESS' => 3, 'ANALYZE' => 3, 'AUDIT' => 3,
3738
'AUTO_INCREMENT' => 3, 'BACKUP' => 3, 'BDB' => 3,
3739
'BERKELEYDB' => 3, 'BIGINT' => 3, 'BREAK' => 3,
3740
'BROWSE' => 3, 'BTREE' => 3, 'BULK' => 3,
3741
'CHANGE' => 3, 'CHECKPOINT' => 3, 'CLUSTER' => 3,
3742
'CLUSTERED' => 3, 'COLUMNS' => 3, 'COMMENT' => 3,
3743
'COMPRESS' => 3, 'COMPUTE' => 3, 'CONTAINSTABLE' => 3,
3744
'DATABASE' => 3, 'DATABASES' => 3, 'DAY_HOUR' => 3,
3745
'DAY_MINUTE' => 3, 'DAY_SECOND' => 3, 'DBCC' => 3,
3746
'DELAYED' => 3, 'DENY' => 3, 'DISK' => 3,
3747
'DISTINCTROW' => 3, 'DISTRIBUTED' => 3, 'DUMMY' => 3,
3748
'DUMP' => 3, 'ENCLOSED' => 3, 'ERRLVL' => 3,
3749
'ERRORS' => 3, 'ESCAPED' => 3, 'EXCLUSIVE' => 3,
3750
'EXPLAIN' => 3, 'FIELDS' => 3, 'FILE' => 3,
3751
'FILLFACTOR' => 3, 'FREETEXT' => 3, 'FREETEXTTABLE' => 3,
3752
'FULLTEXT' => 3, 'GEOMETRY' => 3, 'HASH' => 3,
3753
'HIGH_PRIORITY' => 3, 'HOLDLOCK' => 3, 'HOUR_MINUTE' => 3,
3754
'HOUR_SECOND' => 3, 'IDENTIFIED' => 3, 'IDENTITYCOL' => 3,
3755
'IDENTITY_INSERT' => 3, 'INCREMENT' => 3, 'INDEX' => 3,
3756
'INFILE' => 3, 'INITIAL' => 3, 'INNODB' => 3,
3757
'KEYS' => 3, 'KILL' => 3, 'LINENO' => 3,
3758
'LINES' => 3, 'LOAD' => 3, 'LOCK' => 3,
3759
'LONG' => 3, 'LONGBLOB' => 3, 'LONGTEXT' => 3,
3760
'LOW_PRIORITY' => 3, 'MASTER_SERVER_ID' => 3, 'MAXEXTENTS' => 3,
3761
'MEDIUMBLOB' => 3, 'MEDIUMINT' => 3, 'MEDIUMTEXT' => 3,
3762
'MIDDLEINT' => 3, 'MINUS' => 3, 'MINUTE_SECOND' => 3,
3763
'MLSLABEL' => 3, 'MODE' => 3, 'MRG_MYISAM' => 3,
3764
'NOAUDIT' => 3, 'NOCHECK' => 3, 'NOCOMPRESS' => 3,
3765
'NONCLUSTERED' => 3, 'NOWAIT' => 3, 'NUMBER' => 3,
3766
'OFFLINE' => 3, 'OFFSETS' => 3, 'ONLINE' => 3,
3767
'OPENDATASOURCE' => 3, 'OPENQUERY' => 3, 'OPENROWSET' => 3,
3768
'OPENXML' => 3, 'OPTIMIZE' => 3, 'OPTIONALLY' => 3,
3769
'OUTFILE' => 3, 'OVER' => 3, 'PCTFREE' => 3,
3770
'PERCENT' => 3, 'PLAN' => 3, 'PRINT' => 3,
3771
'PROC' => 3, 'PURGE' => 3, 'RAISERROR' => 3,
3772
'RAW' => 3, 'READTEXT' => 3, 'RECONFIGURE' => 3,
3773
'REGEXP' => 3, 'RENAME' => 3, 'REPLICATION' => 3,
3774
'REQUIRE' => 3, 'RESOURCE' => 3, 'RESTORE' => 3,
3775
'RLIKE' => 3, 'ROWCOUNT' => 3, 'ROWGUIDCOL' => 3,
3776
'ROWID' => 3, 'ROWNUM' => 3, 'RTREE' => 3,
3777
'RULE' => 3, 'SAVE' => 3, 'SETUSER' => 3,
3778
'SHARE' => 3, 'SHOW' => 3, 'SHUTDOWN' => 3,
3779
'SONAME' => 3, 'SPATIAL' => 3, 'SQL_BIG_RESULT' => 3,
3780
'SQL_CALC_FOUND_ROWS' => 3,'SQL_SMALL_RESULT' => 3, 'SSL' => 3,
3781
'STARTING' => 3, 'STATISTICS' => 3, 'STRAIGHT_JOIN' => 3,
3782
'STRIPED' => 3, 'SUCCESSFUL' => 3, 'SYNONYM' => 3,
3783
'SYSDATE' => 3, 'TABLES' => 3, 'TERMINATED' => 3,
3784
'TEXTSIZE' => 3, 'TINYBLOB' => 3, 'TINYINT' => 3,
3785
'TINYTEXT' => 3, 'TOP' => 3, 'TRAN' => 3,
3786
'TRUNCATE' => 3, 'TSEQUAL' => 3, 'TYPES' => 3,
3787
'UID' => 3, 'UNLOCK' => 3, 'UNSIGNED' => 3,
3788
'UPDATETEXT' => 3, 'USE' => 3, 'USER_RESOURCES' => 3,
3789
'VALIDATE' => 3, 'VARBINARY' => 3, 'VARCHAR2' => 3,
3790
'WAITFOR' => 3, 'WARNINGS' => 3, 'WRITETEXT' => 3,
3791
'XOR' => 3, 'YEAR_MONTH' => 3, 'ZEROFILL' => 3
3795
safe_query("drop table crash_me10 $drop_attr");
3797
foreach my $keyword (sort {$a cmp $b} keys %reserved_words)
3799
$keyword_type= $reserved_words{$keyword};
3801
$prompt= "Keyword ".$keyword;
3802
$config= "reserved_word_".$keywords_ext[$keyword_type]."_".lc($keyword);
3804
report_fail($prompt,$config,
3805
"create table crash_me10 ($keyword int not null)",
3806
"drop table crash_me10 $drop_attr"
3812
# Do a query on a query package object.
3817
my($object,$limit)=@_;
3818
my ($query,$result,$retry,$sth);
3820
$query=$object->query($limit);
3821
$result=safe_query($query);
3827
if (defined($query=$object->check_query()))
3829
for ($retry=0 ; $retry < $retry_limit ; $retry++)
3831
printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
3832
print LOG "$query;\n" if ($opt_log);
3833
if (($sth= $dbh->prepare($query)))
3837
$result= $object->check($sth);
3842
print_query($query);
3847
print_query($query);
3849
$retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
3850
if ($retry == $retry_limit-2)
3852
print "Forcing discoennect to retry query\n" if ($opt_debug);
3853
$dbh->disconnect || warn $dbh->errstr;
3855
check_connect($object); # Check that server is still up
3857
$result=0; # Query failed
3860
return $result; # Server couldn't handle the query
3866
my ($prompt,$limit,@queries)=@_;
3868
if (!defined($limits{$limit}))
3870
#print "Found limits for $prompt $limit\n";
3871
my $queries_result = safe_query(\@queries);
3872
add_log($limit, $safe_query_log);
3874
if ( $queries_result) {
3875
$report_result= "yes";
3876
add_log($limit,"As far as all queries returned OK, result is YES");
3878
$report_result= "no";
3879
add_log($limit,"As far as some queries didnt return OK, result is NO");
3881
save_config_data($limit,$report_result,$prompt);
3883
print "REPORT $prompt $limits{$limit}\n";
3884
return $limits{$limit} ne "no";
3889
my ($prompt,$limit,@queries)=@_;
3891
if (!defined($limits{$limit}))
3893
my $queries_result = safe_query(\@queries);
3894
add_log($limit, $safe_query_log);
3896
if ( $queries_result) {
3897
$report_result= "no";
3898
add_log($limit,"As far as all queries returned OK, result is NO");
3900
$report_result= "yes";
3901
add_log($limit,"As far as some queries didnt return OK, result is YES");
3903
save_config_data($limit,$report_result,$prompt);
3905
print "$limits{$limit}\n";
3906
return $limits{$limit} ne "no";
3910
# Return true if one of the queries is ok
3914
my ($prompt,$limit,$queries)=@_;
3915
my ($query,$res,$result);
3917
if (!defined($limits{$limit}))
3919
save_incomplete($limit,$prompt);
3921
foreach $query (@$queries)
3923
if (safe_query_l($limit,$query->[0]))
3925
$result= $query->[1];
3929
save_config_data($limit,$result,$prompt);
3931
print "$limits{$limit}\n";
3932
return $limits{$limit} ne "no";
3936
# Execute query and save result as limit value.
3940
my ($prompt,$limit,$query)=@_;
3943
if (!defined($limits{$limit}))
3945
save_incomplete($limit,$prompt);
3946
$error=safe_query_result($query,"1",2);
3947
add_log($limit,$safe_query_result_log);
3948
save_config_data($limit,$error ? "not supported" :$last_result,$prompt);
3950
print "$limits{$limit}\n";
3951
return $limits{$limit} ne "not supported";
3956
my ($limit,$queries,$check,$clear)=@_;
3957
if (!defined($limits{$limit}))
3959
save_incomplete($limit,$prompt);
3960
eval {undef($dbh->{AutoCommit})};
3963
if (safe_query(\@$queries))
3966
$dbh->{AutoCommit} = 1;
3967
if (safe_query_result($check,"","")) {
3968
add_log($limit,$safe_query_result_log);
3969
save_config_data($limit,"yes",$limit);
3973
add_log($limit,$safe_query_log);
3974
save_config_data($limit,"error",$limit);
3976
$dbh->{AutoCommit} = 1;
3980
add_log($limit,"Couldnt undef autocommit ?? ");
3981
save_config_data($limit,"no",$limit);
3985
return $limits{$limit} ne "yes";
3990
my ($limit,$queries,$check,$clear)=@_;
3991
if (!defined($limits{$limit}))
3993
save_incomplete($limit,$prompt);
3994
eval {undef($dbh->{AutoCommit})};
3997
if (safe_query(\@$queries))
3999
add_log($limit,$safe_query_log);
4002
$dbh->{AutoCommit} = 1;
4003
if (safe_query($check)) {
4004
add_log($limit,$safe_query_log);
4005
save_config_data($limit,"no",$limit);
4007
add_log($limit,$safe_query_log);
4008
save_config_data($limit,"yes",$limit);
4012
add_log($limit,$safe_query_log);
4013
save_config_data($limit,"error",$limit);
4018
add_log($limit,'Couldnt undef Autocommit??');
4019
save_config_data($limit,"error",$limit);
4023
$dbh->{AutoCommit} = 1;
4024
return $limits{$limit} ne "yes";
4028
sub check_and_report
4030
my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
4033
$function=0 if (!defined($function));
4035
print "$prompt: " if (!defined($skip_prompt));
4036
if (!defined($limits{$limit}))
4038
save_incomplete($limit,$prompt);
4039
$tmp=1-safe_query(\@$pre);
4040
add_log($limit,$safe_query_log);
4043
$tmp=safe_query_result($query,$answer,$string_type) ;
4044
add_log($limit,$safe_query_result_log);
4046
safe_query(\@$post);
4047
add_log($limit,$safe_query_log);
4048
delete $limits{$limit};
4049
if ($function == 3) # Report error as 'no'.
4054
if ($function == 0 ||
4055
$tmp != 0 && $function == 1 ||
4056
$tmp == 0 && $function== 2)
4058
save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
4060
print "$limits{$limit}\n";
4061
return $function == 0 ? $limits{$limit} eq "yes" : 0;
4063
return 1; # more things to check
4065
print "$limits{$limit}\n";
4066
return 0 if ($function);
4067
return $limits{$limit} eq "yes";
4073
my ($prompt,$limit,@tests)=@_;
4074
my ($tmp,$test,$type);
4078
if (!defined($limits{$limit}))
4080
save_incomplete($limit,$prompt);
4081
$type="no"; # Not supported
4082
foreach $test (@tests)
4084
my $tmp_type= shift(@$test);
4085
if (safe_query_l($limit,\@$test))
4092
save_config_data($limit, $type, $prompt);
4094
print "$limits{$limit}\n";
4095
return $limits{$limit} ne "no";
4099
# Just execute the query and check values; Returns 1 if ok
4102
sub execute_and_check
4104
my ($key,$pre,$query,$post,$answer,$string_type)=@_;
4107
$tmp=safe_query_l($key,\@$pre);
4109
$tmp=safe_query_result_l($key,$query,$answer,$string_type) == 0 if ($tmp);
4110
safe_query_l($key,\@$post);
4115
# returns 0 if ok, 1 if error, -1 if wrong answer
4116
# Sets $last_result to value of query
4117
sub safe_query_result_l{
4118
my ($key,$query,$answer,$result_type)=@_;
4119
my $r = safe_query_result($query,$answer,$result_type);
4120
add_log($key,$safe_query_result_log);
4124
sub safe_query_result
4126
# result type can be
4127
# 8 (must be empty), 2 (Any value), 0 (number)
4128
# 1 (char, endspaces can differ), 3 (exact char), 4 (NULL)
4129
# 5 (char with prefix), 6 (exact, errors are ignored)
4130
# 7 (array of numbers)
4131
my ($query,$answer,$result_type)=@_;
4132
my ($sth,$row,$result,$retry);
4133
undef($last_result);
4134
$safe_query_result_log="";
4136
printf "\nquery3: %-80.80s\n",$query if ($opt_log_all_queries);
4137
print LOG "$query;\n" if ($opt_log);
4138
$safe_query_result_log="<".$query."\n";
4140
for ($retry=0; $retry < $retry_limit ; $retry++)
4142
if (!($sth=$dbh->prepare($query)))
4144
print_query($query);
4145
$safe_query_result_log .= "> prepare failed:".$dbh->errstr."\n";
4147
if ($server->abort_if_fatal_error())
4149
check_connect(); # Check that server is still up
4152
check_connect(); # Check that server is still up
4157
print_query($query);
4158
$safe_query_result_log .= "> execute failed:".$dbh->errstr."\n";
4159
if ($server->abort_if_fatal_error())
4161
check_connect(); # Check that server is still up
4164
check_connect(); # Check that server is still up
4172
if (!($row=$sth->fetchrow_arrayref))
4174
print "\nquery: $query didn't return any result\n" if ($opt_debug);
4175
$safe_query_result_log .= "> didn't return any result:".$dbh->errstr."\n";
4177
return ($result_type == 8) ? 0 : 1;
4179
if ($result_type == 8)
4185
$last_result= $row->[0]; # Save for report_result;
4186
$safe_query_result_log .= ">".$last_result."\n";
4188
# if ($result_type == 2) We accept any return value as answer
4190
if ($result_type == 0) # Compare numbers
4192
$row->[0] =~ s/,/./; # Fix if ',' is used instead of '.'
4193
if ($row->[0] != $answer && (abs($row->[0]- $answer)/
4194
(abs($row->[0]) + abs($answer))) > 0.01)
4197
$safe_query_result_log .=
4198
"We expected '$answer' but got '$last_result' \n";
4201
elsif ($result_type == 1) # Compare where end space may differ
4203
$row->[0] =~ s/\s+$//;
4204
if ($row->[0] ne $answer)
4207
$safe_query_result_log .=
4208
"We expected '$answer' but got '$last_result' \n";
4211
elsif ($result_type == 3) # This should be a exact match
4213
if ($row->[0] ne $answer)
4216
$safe_query_result_log .=
4217
"We expected '$answer' but got '$last_result' \n";
4220
elsif ($result_type == 4) # If results should be NULL
4222
if (defined($row->[0]))
4225
$safe_query_result_log .=
4226
"We expected NULL but got '$last_result' \n";
4229
elsif ($result_type == 5) # Result should have given prefix
4231
if (length($row->[0]) < length($answer) &&
4232
substr($row->[0],1,length($answer)) ne $answer)
4235
$safe_query_result_log .=
4236
"Result must have prefix '$answer', but '$last_result' \n";
4239
elsif ($result_type == 6) # Exact match but ignore errors
4241
if ($row->[0] ne $answer)
4243
$safe_query_result_log .=
4244
"We expected '$answer' but got '$last_result' \n";
4247
elsif ($result_type == 7) # Compare against array of numbers
4249
if ($row->[0] != $answer->[0])
4251
$safe_query_result_log .= "must be '$answer->[0]' \n";
4258
while (($row=$sth->fetchrow_arrayref))
4260
$safe_query_result_log .= ">$row\n";
4262
$value=shift(@$answer);
4263
if (!defined($value))
4265
print "\nquery: $query returned to many results\n"
4267
$safe_query_result_log .= "It returned to many results \n";
4271
if ($row->[0] != $value)
4273
$safe_query_result_log .= "Must return $value here \n";
4278
if ($#$answer != -1)
4280
print "\nquery: $query returned too few results\n"
4282
$safe_query_result_log .= "It returned too few results \n";
4288
print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
4289
if ($opt_debug && $result && $result_type != 7);
4294
# Check that the query works!
4301
if (!safe_query($query))
4303
$query=join("; ",@$query) if (ref($query) eq "ARRAY");
4304
print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
4310
sub read_config_data
4312
my ($key,$limit,$prompt);
4313
if (-e $opt_config_file)
4315
open(CONFIG_FILE,"+<$opt_config_file") ||
4316
die "Can't open configure file $opt_config_file\n";
4317
print "Reading old values from cache: $opt_config_file\n";
4321
open(CONFIG_FILE,"+>>$opt_config_file") ||
4322
die "Can't create configure file $opt_config_file: $!\n";
4327
while (<CONFIG_FILE>)
4330
if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
4332
$key=$1; $limit=$2 ; $prompt=$3;
4333
if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
4335
if ($key !~ /restart/i)
4337
$limits{$key}=$limit eq "null"? undef : $limit;
4338
$prompts{$key}=length($prompt) ? substr($prompt,2) : "";
4340
delete $limits{'restart'};
4345
if ($limit > $limits{'restart'}{'tohigh'})
4347
$limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
4349
$limits{'restart'}{'tohigh'} = $limit;
4353
elsif (/\s*###(.*)$/) # log line
4355
# add log line for previously read key
4356
$log{$last_read} .= "$1\n";
4358
elsif (!/^\s*$/ && !/^\#/)
4360
die "Wrong config row: $_\n";
4366
sub save_config_data
4368
my ($key,$limit,$prompt)=@_;
4369
$prompts{$key}=$prompt;
4370
return if (defined($limits{$key}) && $limits{$key} eq $limit);
4371
if (!defined($limit) || $limit eq "")
4373
# die "Undefined limit for $key\n";
4376
print CONFIG_FILE "$key=$limit\t# $prompt\n";
4377
$limits{$key}=$limit;
4379
# now write log lines (immediatelly after limits)
4381
my $last_line_was_empty=0;
4382
foreach $line (split /\n/, $log{$key})
4384
print CONFIG_FILE " ###$line\n"
4385
unless ( ($last_line_was_empty eq 1)
4386
&& ($line =~ /^\s+$/) );
4387
$last_line_was_empty= ($line =~ /^\s+$/)?1:0;
4390
if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
4391
($limits{'operating_system'} =~ /NT/))
4393
# If perl crashes in windows, everything is lost (Wonder why? :)
4395
open(CONFIG_FILE,"+>>$opt_config_file") ||
4396
die "Can't reopen configure file $opt_config_file: $!\n";
4404
$log{$key} .= $line . "\n" if ($opt_verbose);;
4407
sub save_all_config_data
4411
return if (!$limit_changed);
4412
open(CONFIG_FILE,">$opt_config_file") ||
4413
die "Can't create configure file $opt_config_file: $!\n";
4417
delete $limits{'restart'};
4420
"#This file is automaticly generated by crash-me $version\n\n";
4421
foreach $key (sort keys %limits)
4423
$tmp="$key=$limits{$key}";
4424
print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
4425
"# $prompts{$key}\n";
4428
my $last_line_was_empty=0;
4429
foreach $line (split /\n/, $log{$key})
4431
print CONFIG_FILE " ###$line\n" unless
4432
( ($last_line_was_empty eq 1) && ($line =~ /^\s*$/));
4433
$last_line_was_empty= ($line =~ /^\s*$/)?1:0;
4440
# Save 'incomplete' in the limits file to be able to continue if
4441
# crash-me dies because of a bug in perl/DBI
4445
my ($limit,$prompt)= @_;
4446
save_config_data($limit,"incompleted",$prompt) if ($opt_restart);
4452
my ($sth,$limit)=@_;
4455
return 0 if (!($row=$sth->fetchrow_arrayref));
4456
return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
4464
for ($i=1 ; $i <= $#_; $i++)
4466
$min=$_[$i] if ($min > $_[$i]);
4474
return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
4475
return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
4476
return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
4481
# Returns a list of statements to create a table in a portable manner
4482
# but still utilizing features in the databases.
4487
my($table_name,$fields,$index,$extra) = @_;
4488
my($query,$nr,$parts,@queries,@index);
4490
$extra="" if (!defined($extra));
4492
$query="create table $table_name (";
4494
foreach $field (@$fields)
4496
$query.= $field . ',';
4498
foreach $index (@$index)
4500
$index =~ /\(([^\(]*)\)$/i;
4502
if ($index =~ /^primary key/)
4504
if ($limits{'primary_key_in_create'} eq 'yes')
4506
$query.= $index . ',';
4511
"create unique index ${table_name}_prim on $table_name ($parts)");
4514
elsif ($index =~ /^unique/)
4516
if ($limits{'unique_in_create'} eq 'yes')
4518
$query.= "unique ($parts),";
4524
"create unique index ${table_name}_$nr on $table_name ($parts)");
4530
if ($limits{'index_in_create'} eq 'yes')
4532
$query.= "index ($parts),";
4538
"create index ${table_name}_$nr on $table_name ($1)");
4543
$query.= ") $extra";
4544
unshift(@queries,$query);
4550
# This is used by some query packages to change:
4553
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
4554
# %f -> q1,q2,q3....
4555
# %F -> q1 integer,q2 integer,q3 integer....
4559
my ($query,$limit)=@_;
4562
return $query if !(defined($query));
4563
$query =~ s/%d/$limit/g;
4566
$repeat= 'a' x $limit;
4567
$query =~ s/%s/$repeat/g;
4571
$repeat= '1,' x $limit;
4573
$query =~ s/%v/$repeat/g;
4578
for ($i=1 ; $i <= $limit ; $i++)
4583
$query =~ s/%f/$repeat/g;
4588
for ($i=1 ; $i <= $limit ; $i++)
4590
$repeat.="q$i integer,";
4593
$query =~ s/%F/$repeat/g;
4600
# Different query packages
4603
package query_repeat;
4607
my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
4608
$max_limit, $check, $offset)=@_;
4610
if (defined($init) && $#$init != -1)
4612
$self->{'init'}=$init;
4614
$self->{'query'}=$query;
4615
$self->{'add1'}=$add1;
4616
$self->{'add_mid'}=$add_mid;
4617
$self->{'add'}=$add;
4618
$self->{'add_end'}=$add_end;
4619
$self->{'end_query'}=$end_query;
4620
$self->{'cleanup'}=$cleanup;
4621
$self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
4622
$self->{'check'}=$check;
4623
$self->{'offset'}=$offset;
4624
$self->{'printf'}= ($add =~ /%d/);
4630
my ($self,$limit)=@_;
4631
if (!$self->{'printf'})
4633
return $self->{'query'} . ($self->{'add'} x $limit) .
4634
($self->{'add_end'} x $limit) . $self->{'end_query'};
4636
my ($tmp,$tmp2,$tmp3,$i);
4637
$tmp=$self->{'query'};
4638
if ($self->{'add1'})
4640
for ($i=0; $i < $limit ; $i++)
4642
$tmp3 = $self->{'add1'};
4647
$tmp .= " ".$self->{'add_mid'};
4650
for ($i=0; $i < $limit ; $i++)
4652
$tmp2 = $self->{'add'};
4658
($self->{'add_end'} x $limit) . $self->{'end_query'});
4665
$tmp=int(($main::limits{"query_size"}-length($self->{'query'})
4666
-length($self->{'add_mid'})-length($self->{'end_query'}))/
4667
(length($self->{'add1'})+
4668
length($self->{'add'})+length($self->{'add_end'})));
4669
return main::min($self->{'max_limit'},$tmp);
4676
my($tmp,$statement);
4677
$tmp=$self->{'cleanup'};
4678
foreach $statement (@$tmp)
4680
main::safe_query($statement) if (defined($statement) && length($statement));
4687
my $check=$self->{'check'};
4688
return &$check($sth,$self->{'limit'}) if (defined($check));
4702
my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
4704
$self->{'query'}=$query;
4705
$self->{'end_query'}=$end_query;
4706
$self->{'cleanup'}=$cleanup;
4707
$self->{'max_limit'}=$max_limit;
4708
$self->{'check'}=$check;
4715
$self->{'limit'}=$i;
4716
return "$self->{'query'}$i$self->{'end_query'}";
4722
return $self->{'max_limit'};
4729
foreach $statement ($self->{'$cleanup'})
4731
main::safe_query($statement) if (defined($statement) && length($statement));
4739
my $check=$self->{'check'};
4740
return &$check($sth,$self->{'limit'}) if (defined($check));
4750
# This package is used when testing CREATE TABLE!
4753
package query_table;
4757
my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
4758
$cleanup, $max_limit, $offset)=@_;
4760
$self->{'query'}=$query;
4761
$self->{'add'}=$add;
4762
$self->{'end_query'}=$end_query;
4763
$self->{'extra_init'}=$extra_init;
4764
$self->{'safe_query'}=$safe_query;
4765
$self->{'check'}=$check;
4766
$self->{'cleanup'}=$cleanup;
4767
$self->{'max_limit'}=$max_limit;
4768
$self->{'offset'}=$offset;
4775
my ($self,$limit)=@_;
4776
$self->{'limit'}=$limit;
4777
$self->cleanup(); # Drop table before create
4779
my ($tmp,$tmp2,$i,$query,@res);
4780
$tmp =$self->{'query'};
4781
$tmp =~ s/%d/$limit/g;
4782
for ($i=1; $i <= $limit ; $i++)
4784
$tmp2 = $self->{'add'};
4788
push(@res,$tmp . $self->{'end_query'});
4789
$tmp=$self->{'extra_init'};
4790
foreach $query (@$tmp)
4792
push(@res,main::fix_query($query,$limit));
4801
return $self->{'max_limit'};
4808
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4814
my $check=$self->{'check'};
4815
return 0 if (!($row=$sth->fetchrow_arrayref));
4816
if (defined($check))
4818
return (defined($row->[0]) &&
4819
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4825
# Remove table before and after create table query
4830
main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
4834
# Package to do many queries with %d, and %s substitution
4841
my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
4844
$self->{'query'}=$query;
4845
$self->{'safe_query'}=$safe_query;
4846
$self->{'check'}=$check_result;
4847
$self->{'cleanup'}=$cleanup;
4848
$self->{'max_limit'}=$max_limit;
4849
$self->{'offset'}=$offset;
4850
$self->{'safe_cleanup'}=$safe_cleanup;
4857
my ($self,$limit)=@_;
4858
my ($queries,$query,@res);
4859
$self->{'limit'}=$limit;
4860
$self->cleanup() if (defined($self->{'safe_cleanup'}));
4861
$queries=$self->{'query'};
4862
foreach $query (@$queries)
4864
push(@res,main::fix_query($query,$limit));
4872
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4878
my($tmp,$statement);
4879
return if (!defined($self->{'cleanup'}));
4880
$tmp=$self->{'cleanup'};
4881
foreach $statement (@$tmp)
4883
if (defined($statement) && length($statement))
4885
main::safe_query(main::fix_query($statement,$self->{'limit'}));
4895
return 0 if (!($row=$sth->fetchrow_arrayref));
4896
$check=$self->{'check'};
4897
if (defined($check))
4899
return (defined($row->[0]) &&
4900
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4908
return $self->{'max_limit'};
4912
# Used to find max supported row length
4915
package query_row_length;
4919
my ($type,$create,$null,$drop,$max_limit)=@_;
4921
$self->{'table_name'}=$create;
4922
$self->{'null'}=$null;
4923
$self->{'cleanup'}=$drop;
4924
$self->{'max_limit'}=$max_limit;
4931
my ($self,$limit)=@_;
4932
my ($res,$values,$size,$length,$i);
4933
$self->{'limit'}=$limit;
4936
$size=main::min($main::limits{'max_char_size'},255);
4937
$size = 255 if (!$size); # Safety
4938
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4940
$res.= "q$i char($size) $self->{'null'},";
4941
$values.="'" . ('a' x $size) . "',";
4943
if ($length < $limit)
4945
$size=$limit-$length;
4946
$res.= "q$i char($size) $self->{'null'},";
4947
$values.="'" . ('a' x $size) . "',";
4951
return ["create table " . $self->{'table_name'} . " ($res)",
4952
"insert into " . $self->{'table_name'} . " values ($values)"];
4958
return $self->{'max_limit'};
4964
main::safe_query($self->{'cleanup'});
4979
# Used to find max supported index length
4982
package query_index_length;
4986
my ($type,$create,$drop,$max_limit)=@_;
4988
$self->{'create'}=$create;
4989
$self->{'cleanup'}=$drop;
4990
$self->{'max_limit'}=$max_limit;
4997
my ($self,$limit)=@_;
4998
my ($res,$size,$length,$i,$parts,$values);
4999
$self->{'limit'}=$limit;
5001
$res=$parts=$values="";
5002
$size=main::min($main::limits{'max_index_part_length'},
5003
$main::limits{'max_char_size'});
5004
$size=1 if ($size == 0); # Avoid infinite loop errors
5005
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
5007
$res.= "q$i char($size) not null,";
5009
$values.= "'" . ('a' x $size) . "',";
5011
if ($length < $limit)
5013
$size=$limit-$length;
5014
$res.= "q$i char($size) not null,";
5016
$values.= "'" . ('a' x $size) . "',";
5021
if ($main::limits{'unique_in_create'} eq 'yes')
5023
return [$self->{'create'} . "($res,unique ($parts))",
5024
"insert into crash_q values($values)"];
5026
return [$self->{'create'} . "($res)",
5027
"create index crash_q_index on crash_q ($parts)",
5028
"insert into crash_q values($values)"];
5034
return $self->{'max_limit'};
5040
main::safe_query($self->{'cleanup'});
5057
# OID test instead of / in addition to _rowid
added
removed
tests/sql-bench/crash-me
