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
$opt_server="mysql"; $opt_host="localhost"; $opt_database="test";
53
$opt_user=$opt_password="";$opt_verbose=1;
54
$opt_debug=$opt_help=$opt_Information=$opt_restart=$opt_force=$opt_quick=0;
55
$opt_log_all_queries=$opt_fix_limit_file=$opt_batch_mode=$opt_version=0;
56
$opt_db_start_cmd=""; # the db server start command
57
$opt_check_server=0; # Check if server is alive before each query
58
$opt_sleep=10; # time to sleep while starting the db server
59
$limit_changed=0; # For configure file
62
$opt_comment=$opt_config_file=$opt_log_queries_to_file="";
63
$limits{'crash_me_safe'}='yes';
64
$prompts{'crash_me_safe'}='crash me safe';
65
$limits{'operating_system'}= machine();
66
$prompts{'operating_system'}='crash-me tested on';
69
GetOptions("Information","help","server=s","debug","user=s","password=s",
70
"database=s","restart","force","quick","log-all-queries","comment=s",
71
"host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","suffix=s",
72
"batch-mode","config-file=s","log-queries-to-file=s","check-server",
74
"verbose!" => \$opt_verbose) || usage();
75
usage() if ($opt_help || $opt_Information);
76
version() && exit(0) if ($opt_version);
78
$opt_suffix = '-'.$opt_suffix if (length($opt_suffix) != 0);
79
$opt_config_file = "$pwd/$opt_dir/$opt_server$opt_suffix.cfg"
80
if (length($opt_config_file) == 0);
81
$log_prefix=' ###'; # prefix for log lines in result file
83
$safe_query_result_log='';
88
if ($opt_fix_limit_file)
90
print "Fixing limit file for $opt_server\n";
93
save_all_config_data();
97
$server=get_server($opt_server,$opt_host,$opt_database);
98
$opt_server=$server->{'cmp_name'};
100
$|=1; # For debugging
102
print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
103
print "I hope you didn't have anything important running on this server....\n";
105
if ($limit_changed) # Must have been restarted
107
save_config_data('crash_me_safe','no',"crash me safe");
110
if (!$opt_force && !$opt_batch_mode)
116
print "Using --force. I assume you know what you are doing...\n";
120
save_config_data('crash_me_version',$version,"crash me version");
123
save_config_data('server_version',$server_version,"server version");
125
if (length($opt_comment))
127
save_config_data('user_comment',$opt_comment,"comment");
131
if (length($opt_log_queries_to_file))
133
open(LOG,">$opt_log_queries_to_file") ||
134
die "Can't open file $opt_log_queries_to_file\n";
139
# Set up some limits that's regared as unlimited
140
# We don't want to take up all resources from the server...
143
$max_connections="+1000"; # Number of simultaneous connections
144
$max_buffer_size="+16000000"; # size of communication buffer.
145
$max_string_size="+8000000"; # Enough for this test
146
$max_name_length="+512"; # Actually 256, but ...
147
$max_keys="+64"; # Probably too big.
148
$max_join_tables="+64"; # Probably too big.
149
$max_columns="+8192"; # Probably too big.
150
$max_row_length=$max_string_size;
151
$max_key_length="+8192"; # Big enough
152
$max_order_by="+64"; # Big enough
153
$max_expressions="+10000";
154
$max_big_expressions="+100";
155
$max_stacked_expressions="+2000";
156
$query_size=$max_buffer_size;
157
$longreadlen=16000000; # For retrieval buffer
161
# First do some checks that needed for the rest of the benchmark
163
use sigtrap; # Must be removed with perl5.005_2 on Win98
164
$SIG{PIPE} = 'IGNORE';
167
$problem_counter +=1;
168
if ($problem_counter >= 100) {
169
die("Too many problems, try to restart");
177
# Test if the database require RESTRICT/CASCADE after DROP TABLE
180
# Really remove the crash_me table
181
$prompt="drop table require cascade/restrict";
183
$dbh->do("drop table crash_me");
184
$dbh->do("drop table crash_me cascade");
185
if (!safe_query_l('drop_requires_cascade',
186
["create table crash_me (a integer not null)",
187
"drop table crash_me"]))
189
$dbh->do("drop table crash_me cascade");
190
if (safe_query_l('drop_requires_cascade',
191
["create table crash_me (a integer not null)",
192
"drop table crash_me cascade"]))
194
save_config_data('drop_requires_cascade',"yes","$prompt");
195
$drop_attr="cascade";
199
die "Can't create and drop table 'crash_me'\n";
204
save_config_data('drop_requires_cascade',"no","$prompt");
208
# Remove tables from old runs
209
$dbh->do("drop table crash_me $drop_attr");
210
$dbh->do("drop table crash_me2 $drop_attr");
211
$dbh->do("drop table crash_me3 $drop_attr");
212
$dbh->do("drop table crash_q $drop_attr");
213
$dbh->do("drop table crash_q1 $drop_attr");
215
$prompt="Tables without primary key";
216
if (!safe_query_l('no_primary_key',
217
["create table crash_me (a integer not null,b char(10) not null)",
218
"insert into crash_me (a,b) values (1,'a')"]))
220
if (!safe_query_l('no_primary_key',
221
["create table crash_me (a integer not null,b char(10) not null".
222
", primary key (a))",
223
"insert into crash_me (a,b) values (1,'a')"]))
225
die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
227
save_config_data('no_primary_key',"no",$prompt);
231
save_config_data('no_primary_key',"yes",$prompt);
235
# Define strings for character NULL and numeric NULL used in expressions
237
$char_null=$server->{'char_null'};
238
$numeric_null=$server->{'numeric_null'};
239
if ($char_null eq '')
243
if ($numeric_null eq '')
245
$numeric_null="NULL";
248
print "$prompt: $limits{'no_primary_key'}\n";
250
report("SELECT without FROM",'select_without_from',"select 1");
251
if ($limits{'select_without_from'} ne "yes")
253
$end_query=" from crash_me";
254
$check_connect="select a from crash_me";
259
$check_connect="select 1";
262
assert($check_connect);
263
assert("select a from crash_me where b<'b'");
265
report("Select constants",'select_constants',"select 1 $end_query");
266
report("Select table_name.*",'table_wildcard',
267
"select crash_me.* from crash_me");
268
report("Allows \' and \" as string markers",'quote_with_"',
269
'select a from crash_me where b<"c"');
270
check_and_report("Double '' as ' in strings",'double_quotes',[],
271
"select 'Walker''s' $end_query",[],"Walker's",1);
272
check_and_report("Multiple line strings","multi_strings",[],
273
"select a from crash_me where b < 'a'\n'b'",[],"1",0);
274
check_and_report("\" as identifier quote (ANSI SQL)",'quote_ident_with_"',[],
275
'select "A" from crash_me',[],"1",0);
276
check_and_report("\` as identifier quote",'quote_ident_with_`',[],
277
'select `A` from crash_me',[],"1",0);
278
check_and_report("[] as identifier quote",'quote_ident_with_[',[],
279
'select [A] from crash_me',[],"1",0);
280
report('Double "" in identifiers as "','quote_ident_with_dbl_"',
281
'create table crash_me1 ("abc""d" integer)',
282
'drop table crash_me1');
284
report("Column alias","column_alias","select a as ab from crash_me");
285
report("Table alias","table_alias","select b.a from crash_me as b");
286
report("Functions",'functions',"select 1+1 $end_query");
287
report("Group functions",'group_functions',"select count(*) from crash_me");
288
report("Group functions with distinct",'group_distinct_functions',
289
"select count(distinct a) from crash_me");
290
report("Group functions with several distinct",'group_many_distinct_functions',
291
"select count(distinct a), count(distinct b) from crash_me");
292
report("Group by",'group_by',"select a from crash_me group by a");
293
report("Group by position",'group_by_position',
294
"select a from crash_me group by 1");
295
report("Group by alias",'group_by_alias',
296
"select a as ab from crash_me group by ab");
297
report("Group on unused column",'group_on_unused',
298
"select count(*) from crash_me group by a");
300
report("Order by",'order_by',"select a from crash_me order by a");
301
report("Order by position",'order_by_position',
302
"select a from crash_me order by 1");
303
report("Order by function","order_by_function",
304
"select a from crash_me order by a+1");
305
report("Order by on unused column",'order_on_unused',
306
"select b from crash_me order by a");
307
# little bit deprecated
308
#check_and_report("Order by DESC is remembered",'order_by_remember_desc',
309
# ["create table crash_q (s int,s1 int)",
310
# "insert into crash_q values(1,1)",
311
# "insert into crash_q values(3,1)",
312
# "insert into crash_q values(2,1)"],
313
# "select s,s1 from crash_q order by s1 DESC,s",
314
# ["drop table crash_q $drop_attr"],[3,2,1],7,undef(),3);
315
report("Compute",'compute',
316
"select a from crash_me order by a compute sum(a) by a");
317
report("INSERT with Value lists",'insert_multi_value',
318
"create table crash_q (s char(10))",
319
"insert into crash_q values ('a'),('b')",
320
"drop table crash_q $drop_attr");
321
report("INSERT with set syntax",'insert_with_set',
322
"create table crash_q (a integer)",
323
"insert into crash_q SET a=1",
324
"drop table crash_q $drop_attr");
325
report("INSERT with DEFAULT","insert_with_default",
326
"create table crash_me_q (a int)",
327
"insert into crash_me_q (a) values (DEFAULT)",
328
"drop table crash_me_q $drop_attr");
330
report("INSERT with empty value list","insert_with_empty_value_list",
331
"create table crash_me_q (a int)",
332
"insert into crash_me_q (a) values ()",
333
"drop table crash_me_q $drop_attr");
335
report("INSERT DEFAULT VALUES","insert_default_values",
336
"create table crash_me_q (a int)",
337
"insert into crash_me_q DEFAULT VALUES",
338
"drop table crash_me_q $drop_attr");
340
report("allows end ';'","end_colon", "select * from crash_me;");
341
try_and_report("LIMIT number of rows","select_limit",
343
"select * from crash_me limit 1"],
345
"select TOP 1 * from crash_me"]);
346
report("SELECT with LIMIT #,#","select_limit2",
347
"select * from crash_me limit 1,1");
348
report("SELECT with LIMIT # OFFSET #",
349
"select_limit3", "select * from crash_me limit 1 offset 1");
351
# The following alter table commands MUST be kept together!
352
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))"))
354
report("Alter table add column",'alter_add_col',
355
"alter table crash_q add d integer");
356
report_one("Alter table add many columns",'alter_add_multi_col',
357
[["alter table crash_q add (f integer,g integer)","yes"],
358
["alter table crash_q add f integer, add g integer","with add"],
359
["alter table crash_q add f integer,g integer","without add"]] );
360
report("Alter table change column",'alter_change_col',
361
"alter table crash_q change a e char(50)");
363
# informix can only change data type with modify
364
report_one("Alter table modify column",'alter_modify_col',
365
[["alter table crash_q modify c1 CHAR(20)","yes"],
366
["alter table crash_q alter c1 CHAR(20)","with alter"]]);
367
report("Alter table alter column default",'alter_alter_col',
368
"alter table crash_q alter b set default 10");
369
report_one("Alter table drop column",'alter_drop_col',
370
[["alter table crash_q drop column b","yes"],
371
["alter table crash_q drop column b restrict",
372
"with restrict/cascade"]]);
373
report("Alter table rename table",'alter_rename_table',
374
"alter table crash_q rename to crash_q1");
376
# Make sure both tables will be dropped, even if rename fails.
377
$dbh->do("drop table crash_q1 $drop_attr");
378
$dbh->do("drop table crash_q $drop_attr");
380
report("rename table","rename_table",
381
"create table crash_q (a integer, b integer,c1 CHAR(10))",
382
"rename table crash_q to crash_q1",
383
"drop table crash_q1 $drop_attr");
384
# Make sure both tables will be dropped, even if rename fails.
385
$dbh->do("drop table crash_q1 $drop_attr");
386
$dbh->do("drop table crash_q $drop_attr");
388
report("truncate","truncate_table",
389
"create table crash_q (a integer, b integer,c1 CHAR(10))",
390
"truncate table crash_q",
391
"drop table crash_q $drop_attr");
393
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))") &&
394
$dbh->do("create table crash_q1 (a integer, b integer,c1 CHAR(10) not null)"))
396
report("Alter table add constraint",'alter_add_constraint',
397
"alter table crash_q add constraint c2 check(a > b)");
398
report_one("Alter table drop constraint",'alter_drop_constraint',
399
[["alter table crash_q drop constraint c2","yes"],
400
["alter table crash_q drop constraint c2 restrict",
401
"with restrict/cascade"]]);
402
report("Alter table add unique",'alter_add_unique',
403
"alter table crash_q add constraint u1 unique(c1)");
404
try_and_report("Alter table drop unique",'alter_drop_unique',
406
"alter table crash_q drop constraint u1"],
407
["with constraint and restrict/cascade",
408
"alter table crash_q drop constraint u1 restrict"],
410
"alter table crash_q drop key u1"]);
411
try_and_report("Alter table add primary key",'alter_add_primary_key',
413
"alter table crash_q1 add constraint p1 primary key(c1)"],
414
["with add primary key",
415
"alter table crash_q1 add primary key(c1)"]);
416
report("Alter table add foreign key",'alter_add_foreign_key',
417
"alter table crash_q add constraint f1 foreign key(c1) references crash_q1(c1)");
418
try_and_report("Alter table drop foreign key",'alter_drop_foreign_key',
419
["with drop constraint",
420
"alter table crash_q drop constraint f1"],
421
["with drop constraint and restrict/cascade",
422
"alter table crash_q drop constraint f1 restrict"],
423
["with drop foreign key",
424
"alter table crash_q drop foreign key f1"]);
425
try_and_report("Alter table drop primary key",'alter_drop_primary_key',
427
"alter table crash_q1 drop constraint p1 restrict"],
429
"alter table crash_q1 drop primary key"]);
431
$dbh->do("drop table crash_q $drop_attr");
432
$dbh->do("drop table crash_q1 $drop_attr");
434
check_and_report("Case insensitive compare","case_insensitive_strings",
435
[],"select b from crash_me where b = 'A'",[],'a',1);
436
check_and_report("Ignore end space in compare","ignore_end_space",
437
[],"select b from crash_me where b = 'a '",[],'a',1);
438
check_and_report("Group on column with null values",'group_by_null',
439
["create table crash_q (s char(10))",
440
"insert into crash_q values(null)",
441
"insert into crash_q values(null)"],
442
"select count(*),s from crash_q group by s",
443
["drop table crash_q $drop_attr"],2,0);
446
if (!defined($limits{'having'}))
447
{ # Complicated because of postgreSQL
448
if (!safe_query_result_l("having",
449
"select a from crash_me group by a having a > 0",1,0))
451
if (!safe_query_result_l("having",
452
"select a from crash_me group by a having a < 0",
454
{ save_config_data("having","error",$prompt); }
456
{ save_config_data("having","yes",$prompt); }
459
{ save_config_data("having","no",$prompt); }
461
print "$prompt: $limits{'having'}\n";
463
if ($limits{'having'} eq 'yes')
465
report("Having with group function","having_with_group",
466
"select a from crash_me group by a having count(*) = 1");
469
if ($limits{'column_alias'} eq 'yes')
471
report("Order by alias",'order_by_alias',
472
"select a as ab from crash_me order by ab");
473
if ($limits{'having'} eq 'yes')
475
report("Having on alias","having_with_alias",
476
"select a as ab from crash_me group by a having ab > 0");
479
report("binary numbers (0b1001)","binary_numbers","select 0b1001 $end_query");
480
report("hex numbers (0x41)","hex_numbers","select 0x41 $end_query");
481
report("binary strings (b'0110')","binary_strings","select b'0110' $end_query");
482
report("hex strings (x'1ace')","hex_strings","select x'1ace' $end_query");
484
report_result("Value of logical operation (1=1)","logical_value",
485
"select (1=1) $end_query");
487
report_result("Value of TRUE","value_of_true","select TRUE $end_query");
488
report_result("Value of FALSE","value_of_false","select FALSE $end_query");
490
$logical_value= $limits{'logical_value'};
494
if ($res=safe_query_l('has_true_false',"select (1=1)=true $end_query")) {
498
save_config_data('has_true_false',$result,"TRUE and FALSE");
501
# Check how many connections the server can handle:
502
# We can't test unlimited connections, because this may take down the
506
$prompt="Simultaneous connections (installation default)";
508
if (defined($limits{'connections'}))
510
print "$limits{'connections'}\n";
516
for ($i=1; $i < $max_connections ; $i++)
518
if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
519
{ PrintError => 0})))
521
print "Last connect error: $DBI::errstr\n" if ($opt_debug);
524
$dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
525
print "." if ($opt_debug);
529
save_config_data('connections',$i,$prompt);
530
foreach $dbh (@connect)
532
print "#" if ($opt_debug);
533
$dbh->disconnect || warn $dbh->errstr; # close connection
536
$#connect=-1; # Free connections
540
print "Can't connect to server: $DBI::errstr.".
541
" Please start it and try again\n";
544
$dbh=retry_connect();
549
# Check size of communication buffer, strings...
552
$prompt="query size";
554
if (!defined($limits{'query_size'}))
558
$end=$max_buffer_size;
559
$select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';
561
assert($query . "$select$end_query");
563
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
565
if ($limits{'restart'}{'tohigh'})
567
$end = $limits{'restart'}{'tohigh'} - 1;
568
print "\nRestarting this with low limit: $first and high limit: $end\n";
569
delete $limits{'restart'};
570
$first=$first+int(($end-$first+4)/5); # Prefere lower on errors
572
for ($i=$first ; $i < $end ; $i*=2)
574
last if (!safe_query($query .
575
(" " x ($i - length($query)-length($end_query) -1))
576
. "$select$end_query"));
578
save_config_data("restart",$i,"") if ($opt_restart);
582
if ($i < $max_buffer_size)
584
while ($first != $end)
586
$i=int(($first+$end+1)/2);
587
if (safe_query($query .
588
(" " x ($i - length($query)-length($end_query) -1)) .
589
"$select$end_query"))
599
save_config_data('query_size',$end,$prompt);
601
$query_size=$limits{'query_size'};
603
print "$limits{'query_size'}\n";
606
# Check for reserved words
609
check_reserved_words($dbh);
612
# Test database types
615
@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
618
"integer","int","smallint",
619
"numeric(9,2)","decimal(6,2)","dec(6,2)",
620
"bit", "bit(2)","bit varying(2)","float","float(8)","real",
621
"double precision", "date","time","timestamp",
622
"interval year", "interval year to month",
624
"interval day", "interval day to hour", "interval day to minute",
625
"interval day to second",
626
"interval hour", "interval hour to minute",
627
"interval hour to second",
628
"interval minute", "interval minute to second",
630
"national character varying(20)",
631
"national character(20)","nchar(1)",
632
"national char varying(20)","nchar varying(20)",
633
"national character varying(20)",
634
"timestamp with time zone");
635
@odbc_types=("binary(1)","varbinary(1)","tinyint","bigint",
637
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
639
"long varchar(1)", "varchar2(257)",
640
"mediumint","middleint","int unsigned",
641
"int1","int2","int3","int4","int8","uint",
642
"money","smallmoney","float4","float8","smallfloat",
643
"float(6,2)","double",
644
"enum('red')","set('red')", "int(5) zerofill", "serial",
645
"char(10) binary","int not null auto_increment,unique(q)",
646
"abstime","year","datetime","smalldatetime","timespan","reltime",
648
"int not null identity,unique(q)",
650
"box","bool","circle","polygon","point","line","lseg","path",
651
"interval", "inet", "cidr", "macaddr",
654
"varchar2(16)","nvarchar2(16)","number(9,2)","number(9)",
655
"number", "long","raw(16)","long raw","rowid","mlslabel","clob",
659
@types=(["sql",\@sql_types],
660
["odbc",\@odbc_types],
661
["extra",\@extra_types]);
663
foreach $types (@types)
665
print "\nSupported $types->[0] types\n";
667
foreach $use_type (@$tmp)
670
$type =~ s/\(.*\)/(1 arg)/;
671
if (index($use_type,",")>= 0)
673
$type =~ s/\(1 arg\)/(2 arg)/;
675
if (($tmp2=index($type,",unique")) >= 0)
677
$type=substr($type,0,$tmp2);
681
$tmp2 =~ s/_not_null//g;
682
report("Type $type","type_$types->[0]_$tmp2",
683
"create table crash_q (q $use_type)",
684
"drop table crash_q $drop_attr");
689
# Test some type limits
693
check_and_report("Remembers end space in char()","remember_end_space",
694
["create table crash_q (a char(10))",
695
"insert into crash_q values('hello ')"],
696
"select a from crash_q where a = 'hello '",
697
["drop table crash_q $drop_attr"],
700
check_and_report("Remembers end space in varchar()",
701
"remember_end_space_varchar",
702
["create table crash_q (a varchar(10))",
703
"insert into crash_q values('hello ')"],
704
"select a from crash_q where a = 'hello '",
705
["drop table crash_q $drop_attr"],
708
if (($limits{'type_extra_float(2_arg)'} eq "yes" ||
709
$limits{'type_sql_decimal(2_arg)'} eq "yes") &&
710
(!defined($limits{'storage_of_float'})))
712
my $type=$limits{'type_extra_float(2_arg)'} eq "yes" ? "float(4,1)" :
714
my $result="undefined";
715
if (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
716
"insert into crash_q values(1.14)"],
717
"select q1 from crash_q",
718
["drop table crash_q $drop_attr"],1.1,0) &&
719
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
720
"insert into crash_q values(1.16)"],
721
"select q1 from crash_q",
722
["drop table crash_q $drop_attr"],1.1,0))
726
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
727
"insert into crash_q values(1.14)"],
728
"select q1 from crash_q",
729
["drop table crash_q $drop_attr"],1.1,0) &&
730
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
731
"insert into crash_q values(1.16)"],
732
"select q1 from crash_q",
733
["drop table crash_q $drop_attr"],1.2,0))
737
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
738
"insert into crash_q values(1.14)"],
739
"select q1 from crash_q",
740
["drop table crash_q $drop_attr"],1.14,0) &&
741
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
742
"insert into crash_q values(1.16)"],
743
"select q1 from crash_q",
744
["drop table crash_q $drop_attr"],1.16,0))
748
$prompt="Storage of float values";
749
print "$prompt: $result\n";
750
save_config_data("storage_of_float", $result, $prompt);
753
try_and_report("Type for row id", "rowid",
755
"create table crash_q (a rowid)",
756
"drop table crash_q $drop_attr"],
758
"create table crash_q (a int not null auto_increment".
759
", primary key(a))","drop table crash_q $drop_attr"],
761
"create table crash_q (a oid, primary key(a))",
762
"drop table crash_q $drop_attr"],
764
"create table crash_q (a serial, primary key(a))",
765
"drop table crash_q $drop_attr"]);
767
try_and_report("Automatic row id", "automatic_rowid",
769
"create table crash_q (a int not null, primary key(a))",
770
"insert into crash_q values (1)",
771
"select _rowid from crash_q",
772
"drop table crash_q $drop_attr"]);
779
(["+, -, * and /","+","5*3-4/2+1",14,0],
780
["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
781
["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
782
["searched CASE","searched_case",
783
"case when 1 > 2 then 'false' when 2 > 1 then 'true' end", "true",1],
784
["simple CASE","simple_case",
785
"case 2 when 1 then 'false' when 2 then 'true' end", "true",1],
786
["CAST","cast","CAST(1 as CHAR)","1",1],
787
["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
788
["CHAR_LENGTH","char_length","char_length(b)","10",0],
789
["CHAR_LENGTH(constant)","char_length(constant)",
790
"char_length('abcd')","4",0],
791
["COALESCE","coalesce","coalesce($char_null,'bcd','qwe')","bcd",1],
792
["CURRENT_DATE","current_date","current_date",0,2],
793
["CURRENT_TIME","current_time","current_time",0,2],
794
["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
795
["EXTRACT","extract_sql",
796
"extract(minute from timestamp '2000-02-23 18:43:12.987')",43,0],
797
["LOCALTIME","localtime","localtime",0,2],
798
["LOCALTIMESTAMP","localtimestamp","localtimestamp",0,2],
799
["LOWER","lower","LOWER('ABC')","abc",1],
800
["NULLIF with strings","nullif_string",
801
"NULLIF(NULLIF('first','second'),'first')",undef(),4],
802
["NULLIF with numbers","nullif_num","NULLIF(NULLIF(1,2),1)",undef(),4],
803
["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
804
["POSITION","position","position('ll' in 'hello')",3,0],
805
["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",3],
806
["UPPER","upper","UPPER('abc')","ABC",1],
807
["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
811
(["ASCII", "ascii", "ASCII('A')","65",0],
812
["CHAR", "char", "CHAR(65)" ,"A",1],
813
["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
814
["DIFFERENCE()","difference","difference('abc','abe')",3,0],
815
["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
816
["LEFT","left","left('abcd',2)","ab",1],
817
["LTRIM","ltrim","ltrim(' abcd')","abcd",1],
818
["REAL LENGTH","length","length('abcd ')","5",0],
819
["ODBC LENGTH","length_without_space","length('abcd ')","4",0],
820
["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
821
["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
822
["LCASE","lcase","lcase('ABC')","abc",1],
823
["REPEAT","repeat","repeat('ab',3)","ababab",1],
824
["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
825
["RIGHT","right","right('abcd',2)","cd",1],
826
["RTRIM","rtrim","rtrim(' abcd ')"," abcd",1],
827
["SPACE","space","space(5)"," ",3],
828
["SOUNDEX","soundex","soundex('hello')",0,2],
829
["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
830
["UCASE","ucase","ucase('abc')","ABC",1],
832
["ABS","abs","abs(-5)",5,0],
833
["ACOS","acos","acos(0)","1.570796",0],
834
["ASIN","asin","asin(1)","1.570796",0],
835
["ATAN","atan","atan(1)","0.785398",0],
836
["ATAN2","atan2","atan2(1,0)","1.570796",0],
837
["CEILING","ceiling","ceiling(-4.5)",-4,0],
838
["COS","cos","cos(0)","1.00000",0],
839
["COT","cot","cot(1)","0.64209262",0],
840
["DEGREES","degrees","degrees(6.283185)","360",0],
841
["EXP","exp","exp(1.0)","2.718282",0],
842
["FLOOR","floor","floor(2.5)","2",0],
843
["LOG","log","log(2)","0.693147",0],
844
["LOG10","log10","log10(10)","1",0],
845
["MOD","mod","mod(11,7)","4",0],
846
["PI","pi","pi()","3.141593",0],
847
["POWER","power","power(2,4)","16",0],
848
["RAND","rand","rand(1)",0,2], # Any value is acceptable
849
["RADIANS","radians","radians(360)","6.283185",0],
850
["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
851
["SIGN","sign","sign(-5)",-1,0],
852
["SIN","sin","sin(1)","0.841471",0],
853
["SQRT","sqrt","sqrt(4)",2,0],
854
["TAN","tan","tan(1)","1.557408",0],
855
["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
856
["NOW","now","now()",0,2], # Any value is acceptable
857
["CURDATE","curdate","curdate()",0,2],
858
["CURTIME","curtime","curtime()",0,2],
859
["TIMESTAMPADD","timestampadd",
860
"timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
861
"1997-01-01 00:00:01",1],
862
["TIMESTAMPDIFF","timestampdiff",
863
"timestampdiff(SQL_TSI_SECOND,'1997-01-01 00:00:02',".
864
" '1997-01-01 00:00:01')","1",0],
865
["USER()","user()","user()",0,2],
866
["DATABASE","database","database()",0,2],
867
["IFNULL","ifnull","ifnull(2,3)",2,0],
868
["ODBC syntax LEFT & RIGHT", "fn_left",
869
"{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
874
["& (bitwise and)",'&',"5 & 3",1,0],
875
["| (bitwise or)",'|',"1 | 2",3,0],
876
["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
877
["<> in SELECT","<>","1<>1","0",0],
878
["=","=","(1=1)",1,$logical_value],
879
["~* (case insensitive compare)","~*","'hi' ~* 'HI'",1,$logical_value],
880
["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
881
["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
882
["ASCII_CHAR", "ascii_char", "ASCII_CHAR(65)","A",1],
883
["ASCII_CODE", "ascii_code", "ASCII_CODE('A')","65",0],
884
["ATN2","atn2","atn2(1,0)","1.570796",0],
885
["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
886
["BIT_COUNT","bit_count","bit_count(5)",2,0],
887
["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
888
["CHARINDEX","charindex","charindex('a','crash')",3,0],
889
["CHR", "chr", "CHR(65)" ,"A",1], # oracle
890
["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
891
["CONVERT","convert","convert(CHAR,5)","5",1],
892
["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
893
["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
894
["ENCRYPT","encrypt","encrypt('hello')",0,2],
895
["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
896
["FORMAT","format","format(1234.5555,2)","1,234.56",1],
897
["GETDATE","getdate","getdate()",0,2],
898
["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1],
899
["IF","if", "if(5,6,7)",6,0],
900
["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
901
["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
902
["INITCAP","initcap","initcap('the soap')","The Soap",1],
903
# oracle Returns char, with the first letter of each word in uppercase
904
["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)" ,"14",0], # oracle instring
905
["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)" ,"27",0],
906
# oracle instring in bytes
907
["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
908
["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
909
["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1],
911
["LENGTHB","lengthb","lengthb('CANDIDE')","14",0],
912
# oracle length in bytes
913
["LIKE ESCAPE in SELECT","like_escape",
914
"'%' like 'a%' escape 'a'",$logical_value,0],
915
["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
916
["LN","ln","ln(95)","4.55387689",0],
917
# oracle natural logarithm of n
918
["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
919
["LOG(m,n)","log(m_n)","log(10,100)","2",0],
920
# oracle logarithm, base m, of n
921
["LOGN","logn","logn(2)","0.693147",0],
923
["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
924
["MOD as %","%","10%7","3",0],
925
["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
926
["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
927
["NOT as '!' in SELECT","!","! 1",0,0],
928
["NOT in SELECT","not","not $false",$logical_value,0],
929
["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
930
["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
931
["PASSWORD","password","password('hello')",0,2],
932
["PASTE", "paste", "paste('ABCDEFG',3,2,'1234')","AB1234EFG",1],
933
["PATINDEX","patindex","patindex('%a%','crash')",3,0],
934
["POW","pow","pow(3,2)",9,0],
935
["RANGE","range","range(a)","0.0",0],
936
# informix range(a) = max(a) - min(a)
937
["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
938
["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
939
["REVERSE","reverse","reverse('abcd')","dcba",1],
940
["ROOT","root","root(4)",2,0], # informix
941
["ROUND(1 arg)","round1","round(5.63)","6",0],
942
["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
943
["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
944
["STR","str","str(123.45,5,1)",123.5,3],
945
["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
946
["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
947
["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)" ,"CD",1],
948
# oracle substring with bytes
949
["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
950
["SUBSTRING_INDEX","substring_index",
951
"substring_index('www.tcx.se','.',-2)", "tcx.se",1],
952
["SYSDATE","sysdate","sysdate()",0,2],
953
["TAIL","tail","tail('ABCDEFG',3)","EFG",0],
954
["TANH","tanh","tanh(1)","0.462117157",0],
955
# oracle hyperbolic tangent of n
956
["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
957
["TRIM; Many char extension",
958
"trim_many_char","trim(':!' FROM ':abc!')","abc",3],
959
["TRIM; Substring extension",
960
"trim_substring","trim('cb' FROM 'abccb')","abc",3],
961
["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
962
["UID","uid","uid",0,2], # oracle uid from user
963
["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
964
["USERENV","userenv","userenv",0,2], # oracle user enviroment
965
["VERSION","version","version()",0,2],
966
["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
967
["automatic string->num convert","auto_string2num","'1'+2",3,0],
968
["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
969
["SUBSTR (2 arg)",'substr2arg',"substr('abcd',2)",'bcd',1], #sapdb func
970
["SUBSTR (3 arg)",'substr3arg',"substr('abcd',2,2)",'bc',1],
971
["LFILL (3 arg)",'lfill3arg',"lfill('abcd','.',6)",'..abcd',1],
972
["RFILL (3 arg)",'rfill3arg',"rfill('abcd','.',6)",'abcd..',1],
973
["RPAD (4 arg)",'rpad4arg',"rpad('abcd',2,'+-',8)",'abcd+-+-',1],
974
["LPAD (4 arg)",'rpad4arg',"lpad('abcd',2,'+-',8)",'+-+-abcd',1],
975
["TRIM (1 arg)",'trim1arg',"trim(' abcd ')",'abcd',1],
976
["TRIM (2 arg)",'trim2arg',"trim('..abcd..','.')",'abcd',1],
977
["LTRIM (2 arg)",'ltrim2arg',"ltrim('..abcd..','.')",'abcd..',1],
978
["RTRIM (2 arg)",'rtrim2arg',"rtrim('..abcd..','.')",'..abcd',1],
979
["EXPAND",'expand2arg',"expand('abcd',6)",'abcd ',0],
980
["REPLACE (2 arg) ",'replace2arg',"replace('AbCd','bC')",'Ad',1],
981
["MAPCHAR",'mapchar',"mapchar('A�')",'Aa',1],
982
["ALPHA",'alpha',"alpha('A�',2)",'AA',1],
983
["ASCII in string cast",'ascii_string',"ascii('a')",'a',1],
984
["EBCDIC in string cast",'ebcdic_string',"ebcdic('a')",'a',1],
985
["TRUNC (1 arg)",'trunc1arg',"trunc(222.6)",222,0],
986
["FIXED",'fixed',"fixed(222.6666,10,2)",'222.67',0],
987
["FLOAT",'float',"float(6666.66,4)",6667,0],
988
["LENGTH",'length',"length(1)",2,0],
989
["INDEX",'index',"index('abcdefg','cd',1,1)",3,0],
990
["MICROSECOND",'microsecond',
991
"MICROSECOND('19630816200212111111')",'111111',0],
992
["TIMESTAMP",'timestamp',
993
"timestamp('19630816','00200212')",'19630816200212000000',0],
994
["VALUE",'value',"value(NULL,'WALRUS')",'WALRUS',0],
995
["DECODE",'decode',"DECODE('S-103','T72',1,'S-103',2,'Leopard',3)",2,0],
996
["NUM",'num',"NUM('2123')",2123,0],
997
["CHR (any type to string)",'chr_str',"CHR(67)",'67',0],
998
["HEX",'hex',"HEX('A')",41,0],
1002
@sql_group_functions=
1004
["AVG","avg","avg(a)",1,0],
1005
["COUNT (*)","count_*","count(*)",1,0],
1006
["COUNT column name","count_column","count(a)",1,0],
1007
["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
1008
["MAX on numbers","max","max(a)",1,0],
1009
["MAX on strings","max_str","max(b)","a",1],
1010
["MIN on numbers","min","min(a)",1,0],
1011
["MIN on strings","min_str","min(b)","a",1],
1012
["SUM","sum","sum(a)",1,0],
1013
["ANY","any","any(a)",$logical_value,0],
1014
["EVERY","every","every(a)",$logical_value,0],
1015
["SOME","some","some(a)",$logical_value,0],
1018
@extra_group_functions=
1020
["BIT_AND",'bit_and',"bit_and(a)",1,0],
1021
["BIT_OR", 'bit_or', "bit_or(a)",1,0],
1022
["COUNT(DISTINCT expr,expr,...)",
1023
"count_distinct_list","count(distinct a,b)",1,0],
1024
["STD","std","std(a)",0,0],
1025
["STDDEV","stddev","stddev(a)",0,0],
1026
["VARIANCE","variance","variance(a)",0,0],
1031
["= ALL","eq_all","b =all (select b from crash_me)",1,0],
1032
["= ANY","eq_any","b =any (select b from crash_me)",1,0],
1033
["= SOME","eq_some","b =some (select b from crash_me)",1,0],
1034
["BETWEEN","between","5 between 4 and 6",1,0],
1035
["EXISTS","exists","exists (select * from crash_me)",1,0],
1036
["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
1037
["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
1038
["LIKE","like","b like 'a%'",1,0],
1039
["MATCH UNIQUE","match_unique",
1040
"1 match unique (select a from crash_me)",1,0],
1041
["MATCH","match","1 match (select a from crash_me)",1,0],
1042
["MATCHES","matches","b matches 'a*'",1,0],
1043
["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
1044
["NOT EXISTS","not_exists",
1045
"not exists (select * from crash_me where a = 2)",1,0],
1046
["NOT LIKE","not_like","b not like 'b%'",1,0],
1047
["NOT UNIQUE","not_unique",
1048
"not unique (select * from crash_me where a = 2)",1,0],
1049
["UNIQUE","unique","unique (select * from crash_me)",1,0],
1052
@types=(["sql",\@sql_functions,0],
1053
["odbc",\@odbc_functions,0],
1054
["extra",\@extra_functions,0],
1055
["where",\@where_functions,0]);
1057
@group_types=(["sql",\@sql_group_functions,0],
1058
["extra",\@extra_group_functions,0]);
1061
foreach $types (@types)
1063
print "\nSupported $types->[0] functions\n";
1065
foreach $type (@$tmp)
1067
if (defined($limits{"func_$types->[0]_$type->[1]"}))
1071
if ($types->[0] eq "where")
1073
check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
1074
[],"select a from crash_me where $type->[2]",[],
1075
$type->[3],$type->[4]);
1077
elsif ($limits{'functions'} eq 'yes')
1079
if (($type->[2] =~ /char_length\(b\)/) && (!$end_query))
1081
my $tmp= $type->[2];
1082
$tmp .= " from crash_me ";
1083
undef($limits{"func_$types->[0]_$type->[1]"});
1084
check_and_report("Function $type->[0]",
1085
"func_$types->[0]_$type->[1]",
1086
[],"select $tmp ",[],
1087
$type->[3],$type->[4]);
1091
undef($limits{"func_$types->[0]_$type->[1]"});
1092
$result = check_and_report("Function $type->[0]",
1093
"func_$types->[0]_$type->[1]",
1094
[],"select $type->[2] $end_query",[],
1095
$type->[3],$type->[4]);
1098
# check without type specifyer
1099
if ($type->[2] =~ /DATE /)
1101
my $tmp= $type->[2];
1103
undef($limits{"func_$types->[0]_$type->[1]"});
1104
$result = check_and_report("Function $type->[0]",
1105
"func_$types->[0]_$type->[1]",
1106
[],"select $tmp $end_query",[],
1107
$type->[3],$type->[4]);
1111
if ($types->[0] eq "odbc" && ! ($type->[2] =~ /\{fn/))
1113
my $tmp= $type->[2];
1114
# Check by converting to ODBC format
1115
undef($limits{"func_$types->[0]_$type->[1]"});
1117
$tmp =~ s/('1997-\d\d-\d\d \d\d:\d\d:\d\d')/{ts $1}/g;
1118
$tmp =~ s/(DATE '1997-\d\d-\d\d')/{d $1}/g;
1119
$tmp =~ s/(TIME '12:13:14')/{t $1}/g;
1122
check_and_report("Function $type->[0]",
1123
"func_$types->[0]_$type->[1]",
1124
[],"select $tmp $end_query",[],
1125
$type->[3],$type->[4]);
1134
if ($limits{'functions'} eq 'yes')
1136
foreach $types (@group_types)
1138
print "\nSupported $types->[0] group functions\n";
1140
foreach $type (@$tmp)
1142
check_and_report("Group function $type->[0]",
1143
"group_func_$types->[0]_$type->[1]",
1144
[],"select $type->[2],a from crash_me group by a",[],
1145
$type->[3],$type->[4]);
1149
report("mixing of integer and float in expression","float_int_expr",
1150
"select 1+1.0 $end_query");
1151
if ($limits{'func_odbc_exp'} eq 'yes')
1153
report("No need to cast from integer to float",
1154
"dont_require_cast_to_float", "select exp(1) $end_query");
1156
check_and_report("Is 1+NULL = NULL","null_num_expr",
1157
[],"select 1+$numeric_null $end_query",[],undef(),4);
1158
$tmp=sql_concat("'a'",$char_null);
1161
check_and_report("Is $tmp = NULL", "null_concat_expr", [],
1162
"select $tmp $end_query",[], undef(),4);
1164
$prompt="Need to cast NULL for arithmetic";
1165
add_log("Need_cast_for_null",
1166
" Check if numeric_null ($numeric_null) is 'NULL'");
1167
save_config_data("Need_cast_for_null",
1168
($numeric_null eq "NULL") ? "no" : "yes",
1179
my $result = 'undefined';
1182
save_incomplete('func_extra_noround','Function NOROUND');
1184
# 1) check if noround() function is supported
1185
$error = safe_query_l('func_extra_noround',"select noround(22.6) $end_query");
1186
if ($error ne 1) # syntax error -- noround is not supported
1190
else # Ok, now check if it really works
1192
$error=safe_query_l('func_extra_noround',
1193
["create table crash_me_nr (a int)",
1194
"insert into crash_me_nr values(noround(10.2))",
1195
"drop table crash_me_nr $drop_attr"]);
1198
$result= "syntax only";
1206
save_config_data('func_extra_noround',$result,"Function NOROUND");
1209
check_parenthesis("func_sql_","CURRENT_USER");
1210
check_parenthesis("func_sql_","SESSION_USER");
1211
check_parenthesis("func_sql_","SYSTEM_USER");
1212
check_parenthesis("func_sql_","USER");
1215
if ($limits{'type_sql_date'} eq 'yes')
1217
# Checking the format of date in result.
1219
safe_query("drop table crash_me_d $drop_attr");
1220
assert("create table crash_me_d (a date)");
1221
# find the example of date
1223
if ($limits{'func_extra_sysdate'} eq 'yes') {
1224
$dateexample=' sysdate() ';
1226
elsif ($limits{'func_sql_current_date'} eq 'yes') {
1227
$dateexample='CURRENT_DATE';
1229
elsif ($limits{'func_odbc_curdate'} eq 'yes') {
1230
$dateexample='curdate()';
1232
elsif ($limits{'func_extra_getdate'} eq 'yes') {
1233
$dateexample='getdate()';
1235
elsif ($limits{'func_odbc_now'} eq 'yes') {
1236
$dateexample='now()';
1239
$dateexample="DATE '1963-08-16'";
1242
my $key = 'date_format_inresult';
1243
my $prompt = "Date format in result";
1244
if (! safe_query_l('date_format_inresult',
1245
"insert into crash_me_d values($dateexample) "))
1247
die "Cannot insert date ($dateexample):".$last_error;
1249
my $sth= $dbh->prepare("select a from crash_me_d");
1250
add_log('date_format_inresult',"< select a from crash_me_d");
1252
$_= $sth->fetchrow_array;
1253
add_log('date_format_inresult',"> $_");
1254
safe_query_l($key,"delete from crash_me_d");
1255
if (/\d{4}-\d{2}-\d{2}/){ save_config_data($key,"iso",$prompt);}
1256
elsif (/\d{2}-\d{2}-\d{2}/){ save_config_data($key,"short iso",$prompt);}
1257
elsif (/\d{2}\.\d{2}\.\d{4}/){ save_config_data($key,"euro",$prompt);}
1258
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"short euro",$prompt);}
1259
elsif (/\d{2}\/\d{2}\/\d{4}/){ save_config_data($key,"usa",$prompt);}
1260
elsif (/\d{2}\/\d{2}\/\d{2}/){ save_config_data($key,"short usa",$prompt);}
1261
elsif (/\d*/){ save_config_data($key,"YYYYMMDD",$prompt);}
1262
else { save_config_data($key,"unknown",$prompt);};
1265
check_and_report("Supports YYYY-MM-DD (ISO) format","date_format_ISO",
1266
[ "insert into crash_me_d(a) values ('1963-08-16')"],
1267
"select a from crash_me_d",
1268
["delete from crash_me_d"],
1269
make_date_r(1963,8,16),1);
1271
check_and_report("Supports DATE 'YYYY-MM-DD' (ISO) format",
1272
"date_format_ISO_with_date",
1273
[ "insert into crash_me_d(a) values (DATE '1963-08-16')"],
1274
"select a from crash_me_d",
1275
["delete from crash_me_d"],
1276
make_date_r(1963,8,16),1);
1278
check_and_report("Supports DD.MM.YYYY (EUR) format","date_format_EUR",
1279
[ "insert into crash_me_d(a) values ('16.08.1963')"],
1280
"select a from crash_me_d",
1281
["delete from crash_me_d"],
1282
make_date_r(1963,8,16),1);
1283
check_and_report("Supports DATE 'DD.MM.YYYY' (EUR) format",
1284
"date_format_EUR_with_date",
1285
[ "insert into crash_me_d(a) values (DATE '16.08.1963')"],
1286
"select a from crash_me_d",
1287
["delete from crash_me_d"],
1288
make_date_r(1963,8,16),1);
1290
check_and_report("Supports YYYYMMDD format",
1291
"date_format_YYYYMMDD",
1292
[ "insert into crash_me_d(a) values ('19630816')"],
1293
"select a from crash_me_d",
1294
["delete from crash_me_d"],
1295
make_date_r(1963,8,16),1);
1296
check_and_report("Supports DATE 'YYYYMMDD' format",
1297
"date_format_YYYYMMDD_with_date",
1298
[ "insert into crash_me_d(a) values (DATE '19630816')"],
1299
"select a from crash_me_d",
1300
["delete from crash_me_d"],
1301
make_date_r(1963,8,16),1);
1303
check_and_report("Supports MM/DD/YYYY format",
1305
[ "insert into crash_me_d(a) values ('08/16/1963')"],
1306
"select a from crash_me_d",
1307
["delete from crash_me_d"],
1308
make_date_r(1963,8,16),1);
1309
check_and_report("Supports DATE 'MM/DD/YYYY' format",
1310
"date_format_USA_with_date",
1311
[ "insert into crash_me_d(a) values (DATE '08/16/1963')"],
1312
"select a from crash_me_d",
1313
["delete from crash_me_d"],
1314
make_date_r(1963,8,16),1);
1319
check_and_report("Supports 0000-00-00 dates","date_zero",
1320
["create table crash_me2 (a date not null)",
1321
"insert into crash_me2 values (".make_date(0,0,0).")"],
1322
"select a from crash_me2",
1323
["drop table crash_me2 $drop_attr"],
1324
make_date_r(0,0,0),1);
1326
check_and_report("Supports 0001-01-01 dates","date_one",
1327
["create table crash_me2 (a date not null)",
1328
"insert into crash_me2 values (".make_date(1,1,1).")"],
1329
"select a from crash_me2",
1330
["drop table crash_me2 $drop_attr"],
1331
make_date_r(1,1,1),1);
1333
check_and_report("Supports 9999-12-31 dates","date_last",
1334
["create table crash_me2 (a date not null)",
1335
"insert into crash_me2 values (".make_date(9999,12,31).")"],
1336
"select a from crash_me2",
1337
["drop table crash_me2 $drop_attr"],
1338
make_date_r(9999,12,31),1);
1340
check_and_report("Supports 'infinity dates","date_infinity",
1341
["create table crash_me2 (a date not null)",
1342
"insert into crash_me2 values ('infinity')"],
1343
"select a from crash_me2",
1344
["drop table crash_me2 $drop_attr"],
1347
if (!defined($limits{'date_with_YY'}))
1349
check_and_report("Supports YY-MM-DD dates","date_with_YY",
1350
["create table crash_me2 (a date not null)",
1351
"insert into crash_me2 values ('98-03-03')"],
1352
"select a from crash_me2",
1353
["drop table crash_me2 $drop_attr"],
1354
make_date_r(1998,3,3),5);
1355
if ($limits{'date_with_YY'} eq "yes")
1357
undef($limits{'date_with_YY'});
1358
check_and_report("Supports YY-MM-DD 2000 compilant dates",
1360
["create table crash_me2 (a date not null)",
1361
"insert into crash_me2 values ('10-03-03')"],
1362
"select a from crash_me2",
1363
["drop table crash_me2 $drop_attr"],
1364
make_date_r(2010,3,3),5);
1373
save_incomplete('func_odbc_week','WEEK');
1374
$error = safe_query_result_l('func_odbc_week',
1375
"select week(".make_date(1997,2,1).") $end_query",5,0);
1376
# actually this query must return 4 or 5 in the $last_result,
1377
# $error can be 1 (not supported at all) , -1 ( probably USA weeks)
1378
# and 0 - EURO weeks
1380
if ($last_result == 4) {
1384
add_log('func_odbc_week',
1385
" must return 4 or 5, but $last_result");
1387
} elsif ($error == 0) {
1391
save_config_data('func_odbc_week',$result,"WEEK");
1394
my $insert_query ='insert into crash_me_d values('.
1395
make_date(1997,2,1).')';
1396
safe_query($insert_query);
1399
["DAYNAME","dayname","dayname(a)","",2],
1400
["MONTH","month","month(a)","",2],
1401
["MONTHNAME","monthname","monthname(a)","",2],
1402
["DAYOFMONTH","dayofmonth","dayofmonth(a)",1,0],
1403
["DAYOFWEEK","dayofweek","dayofweek(a)",7,0],
1404
["DAYOFYEAR","dayofyear","dayofyear(a)",32,0],
1405
["QUARTER","quarter","quarter(a)",1,0],
1406
["YEAR","year","year(a)",1997,0]))
1408
$prompt='Function '.$fn->[0];
1409
$key='func_odbc_'.$fn->[1];
1410
add_log($key,"< ".$insert_query);
1411
check_and_report($prompt,$key,
1412
[],"select ".$fn->[2]." from crash_me_d",[],
1417
safe_query(['delete from crash_me_d',
1418
'insert into crash_me_d values('.make_date(1963,8,16).')']);
1420
["DATEADD","dateadd","dateadd(day,3,make_date(1997,11,30))",0,2],
1421
["MDY","mdy","mdy(7,1,1998)","make_date_r(1998,07,01)",0], # informix
1422
["DATEDIFF","datediff",
1423
"datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
1424
["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
1425
["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
1426
["DATE_FORMAT","date_format",
1427
"date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
1428
["FROM_DAYS","from_days",
1429
"from_days(729024)","make_date_r(1996,1,1)",1],
1430
["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
1431
["MONTHS_BETWEEN","months_between",
1432
"months_between(make_date(1997,2,2),make_date(1997,1,1))",
1433
"1.03225806",0], # oracle number of months between 2 dates
1434
["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
1435
["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
1436
["WEEKDAY","weekday","weekday(make_date(1997,11,29))",5,0],
1437
["ADDDATE",'adddate',
1438
"ADDDATE(make_date(2002,12,01),3)",'make_date_r(2002,12,04)',0],
1439
["SUBDATE",'subdate',
1440
"SUBDATE(make_date(2002,12,04),3)",'make_date_r(2002,12,01)',0],
1441
["DATEDIFF (2 arg)",'datediff2arg',
1442
"DATEDIFF(make_date(2002,12,04),make_date(2002,12,01))",'3',0],
1443
["WEEKOFYEAR",'weekofyear',
1444
"WEEKOFYEAR(make_date(1963,08,16))",'33',0],
1445
# table crash_me_d must contain record with 1963-08-16 (for CHAR)
1446
["CHAR (conversation date)",'char_date',
1447
"CHAR(a,EUR)",'16.08.1963',0],
1448
["MAKEDATE",'makedate',"MAKEDATE(1963,228)"
1449
,'make_date_r(1963,08,16)',0],
1450
["TO_DAYS","to_days",
1451
"to_days(make_date(1996,01,01))",729024,0],
1452
["ADD_MONTHS","add_months",
1453
"add_months(make_date(1997,01,01),1)","make_date_r(1997,02,01)",0],
1454
# oracle the date plus n months
1455
["LAST_DAY","last_day",
1456
"last_day(make_date(1997,04,01))","make_date_r(1997,04,30)",0],
1457
# oracle last day of month of date
1458
["DATE",'date',"date(make_date(1963,8,16))",
1459
'make_date_r(1963,8,16)',0],
1460
["DAY",'day',"DAY(make_date(2002,12,01))",1,0]))
1462
$prompt='Function '.$fn->[0];
1463
$key='func_extra_'.$fn->[1];
1464
my $qry="select ".$fn->[2]." from crash_me_d";
1465
while( $qry =~ /^(.*)make_date\((\d+),(\d+),(\d+)\)(.*)$/)
1467
my $dt= &make_date($2,$3,$4);
1470
my $result=$fn->[3];
1471
while( $result =~ /^(.*)make_date_r\((\d+),(\d+),(\d+)\)(.*)$/)
1473
my $dt= &make_date_r($2,$3,$4);
1476
check_and_report($prompt,$key,
1483
safe_query("drop table crash_me_d $drop_attr");
1487
if ($limits{'type_sql_time'} eq 'yes')
1489
# Checking the format of date in result.
1491
safe_query("drop table crash_me_t $drop_attr");
1492
assert("create table crash_me_t (a time)");
1493
# find the example of time
1495
if ($limits{'func_sql_current_time'} eq 'yes') {
1496
$timeexample='CURRENT_TIME';
1498
elsif ($limits{'func_odbc_curtime'} eq 'yes') {
1499
$timeexample='curtime()';
1501
elsif ($limits{'func_sql_localtime'} eq 'yes') {
1502
$timeexample='localtime';
1504
elsif ($limits{'func_odbc_now'} eq 'yes') {
1505
$timeexample='now()';
1508
$timeexample="'02:55:12'";
1511
my $key = 'time_format_inresult';
1512
my $prompt = "Time format in result";
1513
if (! safe_query_l('time_format_inresult',
1514
"insert into crash_me_t values($timeexample) "))
1516
die "Cannot insert time ($timeexample):".$last_error;
1518
my $sth= $dbh->prepare("select a from crash_me_t");
1519
add_log('time_format_inresult',"< select a from crash_me_t");
1521
$_= $sth->fetchrow_array;
1522
add_log('time_format_inresult',"> $_");
1523
safe_query_l($key,"delete from crash_me_t");
1524
if (/\d{2}:\d{2}:\d{2}/){ save_config_data($key,"iso",$prompt);}
1525
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"euro",$prompt);}
1526
elsif (/\d{2}:\d{2}\s+(AM|PM)/i){ save_config_data($key,"usa",$prompt);}
1527
elsif (/\d{8}$/){ save_config_data($key,"HHHHMMSS",$prompt);}
1528
elsif (/\d{4}$/){ save_config_data($key,"HHMMSS",$prompt);}
1529
else { save_config_data($key,"unknown",$prompt);};
1532
check_and_report("Supports HH:MM:SS (ISO) time format","time_format_ISO",
1533
[ "insert into crash_me_t(a) values ('20:08:16')"],
1534
"select a from crash_me_t",
1535
["delete from crash_me_t"],
1536
make_time_r(20,8,16),1);
1538
check_and_report("Supports HH.MM.SS (EUR) time format","time_format_EUR",
1539
[ "insert into crash_me_t(a) values ('20.08.16')"],
1540
"select a from crash_me_t",
1541
["delete from crash_me_t"],
1542
make_time_r(20,8,16),1);
1544
check_and_report("Supports HHHHmmSS time format",
1545
"time_format_HHHHMMSS",
1546
[ "insert into crash_me_t(a) values ('00200816')"],
1547
"select a from crash_me_t",
1548
["delete from crash_me_t"],
1549
make_time_r(20,8,16),1);
1551
check_and_report("Supports HHmmSS time format",
1552
"time_format_HHHHMMSS",
1553
[ "insert into crash_me_t(a) values ('200816')"],
1554
"select a from crash_me_t",
1555
["delete from crash_me_t"],
1556
make_time_r(20,8,16),1);
1558
check_and_report("Supports HH:MM:SS (AM|PM) time format",
1560
[ "insert into crash_me_t(a) values ('08:08:16 PM')"],
1561
"select a from crash_me_t",
1562
["delete from crash_me_t"],
1563
make_time_r(20,8,16),1);
1565
my $insert_query ='insert into crash_me_t values('.
1566
make_time(20,8,16).')';
1567
safe_query($insert_query);
1570
["HOUR","hour","hour('".make_time(12,13,14)."')",12,0],
1571
["ANSI HOUR","hour_time","hour(TIME '".make_time(12,13,14)."')",12,0],
1572
["MINUTE","minute","minute('".make_time(12,13,14)."')",13,0],
1573
["SECOND","second","second('".make_time(12,13,14)."')",14,0]
1577
$prompt='Function '.$fn->[0];
1578
$key='func_odbc_'.$fn->[1];
1579
add_log($key,"< ".$insert_query);
1580
check_and_report($prompt,$key,
1581
[],"select ".$fn->[2]." $end_query",[],
1586
# safe_query(['delete from crash_me_t',
1587
# 'insert into crash_me_t values('.make_time(20,8,16).')']);
1589
["TIME_TO_SEC","time_to_sec","time_to_sec('".
1590
make_time(1,23,21)."')","5001",0],
1591
["SEC_TO_TIME","sec_to_time","sec_to_time(5001)",
1592
make_time_r(01,23,21),1],
1593
["ADDTIME",'addtime',"ADDTIME('".make_time(20,2,12).
1594
"','".make_time(0,0,3)."')",make_time_r(20,2,15),0],
1595
["SUBTIME",'subtime',"SUBTIME('".make_time(20,2,15)
1596
."','".make_time(0,0,3)."')",make_time_r(20,2,12),0],
1597
["TIMEDIFF",'timediff',"TIMEDIFF('".make_time(20,2,15)."','".
1598
make_time(20,2,12)."')",make_time_r(0,0,3),0],
1599
["MAKETIME",'maketime',"MAKETIME(20,02,12)",make_time_r(20,2,12),0],
1600
["TIME",'time',"time('".make_time(20,2,12)."')",make_time_r(20,2,12),0]
1603
$prompt='Function '.$fn->[0];
1604
$key='func_extra_'.$fn->[1];
1605
my $qry="select ".$fn->[2]." $end_query";
1606
my $result=$fn->[3];
1607
check_and_report($prompt,$key,
1614
safe_query("drop table crash_me_t $drop_attr");
1619
# NOT id BETWEEN a and b
1620
if ($limits{'func_where_not_between'} eq 'yes')
1622
my $result = 'error';
1624
my $key='not_id_between';
1625
my $prompt='NOT ID BETWEEN interprets as ID NOT BETWEEN';
1627
save_incomplete($key,$prompt);
1628
safe_query_l($key,["create table crash_me_b (i int)",
1629
"insert into crash_me_b values(2)",
1630
"insert into crash_me_b values(5)"]);
1631
$err =safe_query_result_l($key,
1632
"select i from crash_me_b where not i between 1 and 3",
1635
if (not defined($last_result)) {
1642
safe_query_l($key,["drop table crash_me_b"]);
1643
save_config_data($key,$result,$prompt);
1650
report("LIKE on numbers","like_with_number",
1651
"create table crash_q (a int,b int)",
1652
"insert into crash_q values(10,10)",
1653
"select * from crash_q where a like '10'",
1654
"drop table crash_q $drop_attr");
1656
report("column LIKE column","like_with_column",
1657
"create table crash_q (a char(10),b char(10))",
1658
"insert into crash_q values('abc','abc')",
1659
"select * from crash_q where a like b",
1660
"drop table crash_q $drop_attr");
1662
report("update of column= -column","NEG",
1663
"create table crash_q (a integer)",
1664
"insert into crash_q values(10)",
1665
"update crash_q set a=-a",
1666
"drop table crash_q $drop_attr");
1668
if ($limits{'func_odbc_left'} eq 'yes' ||
1669
$limits{'func_odbc_substring'} eq 'yes')
1671
my $type= ($limits{'func_odbc_left'} eq 'yes' ?
1672
"left(a,4)" : "substring(a for 4)");
1674
check_and_report("String functions on date columns","date_as_string",
1675
["create table crash_me2 (a date not null)",
1676
"insert into crash_me2 values ('1998-03-03')"],
1677
"select $type from crash_me2",
1678
["drop table crash_me2 $drop_attr"],
1683
$tmp=sql_concat("b","b");
1686
check_and_report("char are space filled","char_is_space_filled",
1687
[],"select $tmp from crash_me where b = 'a '",[],
1691
if (!defined($limits{'multi_table_update'}))
1693
if (check_and_report("Update with many tables","multi_table_update",
1694
["create table crash_q (a integer,b char(10))",
1695
"insert into crash_q values(1,'c')",
1696
"update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
1697
"select b from crash_q",
1698
["drop table crash_q $drop_attr"],
1701
check_and_report("Update with many tables","multi_table_update",
1702
["create table crash_q (a integer,b char(10))",
1703
"insert into crash_q values(1,'c')",
1704
"update crash_q,crash_me set crash_q.b=crash_me.b ".
1705
"where crash_q.a=crash_me.a"],
1706
"select b from crash_q",
1707
["drop table crash_q $drop_attr"],
1713
report("DELETE FROM table1,table2...","multi_table_delete",
1714
"create table crash_q (a integer,b char(10))",
1715
"insert into crash_q values(1,'c')",
1716
"delete crash_q.* from crash_q,crash_me where crash_q.a=crash_me.a",
1717
"drop table crash_q $drop_attr");
1719
check_and_report("Update with sub select","select_table_update",
1720
["create table crash_q (a integer,b char(10))",
1721
"insert into crash_q values(1,'c')",
1722
"update crash_q set b= ".
1723
"(select b from crash_me where crash_q.a = crash_me.a)"],
1724
"select b from crash_q",
1725
["drop table crash_q $drop_attr"],
1728
check_and_report("Calculate 1--1","minus_neg",[],
1729
"select a--1 from crash_me",[],0,2);
1731
report("ANSI SQL simple joins","simple_joins",
1732
"select crash_me.a from crash_me, crash_me t0");
1735
# Check max string size, and expression limits
1738
foreach $type (('mediumtext','text','text()','blob','long'))
1740
if ($limits{"type_extra_$type"} eq 'yes')
1746
if (defined($found))
1748
$found =~ s/\(\)/\(%d\)/;
1749
find_limit("max text or blob size","max_text_size",
1750
new query_many(["create table crash_q (q $found)",
1751
"insert into crash_q values ('%s')"],
1752
"select * from crash_q","%s",
1753
["drop table crash_q $drop_attr"],
1754
min($max_string_size,$limits{'query_size'}-30)));
1758
# It doesn't make lots of sense to check for string lengths much bigger than
1759
# what can be stored...
1761
find_limit(($prompt="constant string size in where"),"where_string_size",
1762
new query_repeat([],"select a from crash_me where b >='",
1764
if ($limits{'where_string_size'} == 10)
1766
save_config_data('where_string_size','nonstandard',$prompt);
1769
if ($limits{'select_constants'} eq 'yes')
1771
find_limit("constant string size in SELECT","select_string_size",
1772
new query_repeat([],"select '","","","a","","'$end_query"));
1775
goto no_functions if ($limits{'functions'} ne "yes");
1777
if ($limits{'func_odbc_repeat'} eq 'yes')
1779
find_limit("return string size from function","repeat_string_size",
1781
"select repeat('a',%d) $end_query","%s",
1783
$max_string_size,0));
1786
$tmp=find_limit("simple expressions","max_expressions",
1787
new query_repeat([],"select 1","","","+1","",$end_query,
1788
undef(),$max_expressions));
1792
$tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
1793
find_limit("big expressions", "max_big_expressions",
1794
new query_repeat([],"select 0","","","+$tmp","",$end_query,
1795
undef(),$max_big_expressions));
1798
find_limit("stacked expressions", "max_stack_expression",
1799
new query_repeat([],"select 1","","","+(1",")",$end_query,
1800
undef(),$max_stacked_expressions));
1804
if (!defined($limits{'max_conditions'}))
1806
find_limit("OR and AND in WHERE","max_conditions",
1807
new query_repeat([],
1808
"select a from crash_me where a=1 and b='a'","",
1809
"", " or a=%d and b='%d'","","","",
1810
[],($query_size-42)/29,undef,2));
1811
$limits{'max_conditions'}*=2;
1813
# The 42 is the length of the constant part.
1814
# The 29 is the length of the variable part, plus two seven-digit numbers.
1816
find_limit("tables in join", "join_tables",
1817
new query_repeat([],
1818
"select crash_me.a",",t%d.a","from crash_me",
1819
",crash_me t%d","","",[],$max_join_tables,undef,
1822
# Different CREATE TABLE options
1824
report("primary key in create table",'primary_key_in_create',
1825
"create table crash_q (q integer not null,primary key (q))",
1826
"drop table crash_q $drop_attr");
1828
report("unique in create table",'unique_in_create',
1829
"create table crash_q (q integer not null,unique (q))",
1830
"drop table crash_q $drop_attr");
1832
if ($limits{'unique_in_create'} eq 'yes')
1834
report("unique null in create",'unique_null_in_create',
1835
"create table crash_q (q integer,unique (q))",
1836
"insert into crash_q (q) values (NULL)",
1837
"insert into crash_q (q) values (NULL)",
1838
"insert into crash_q (q) values (1)",
1839
"drop table crash_q $drop_attr");
1842
report("default value for column",'create_default',
1843
"create table crash_q (q integer default 10 not null)",
1844
"drop table crash_q $drop_attr");
1846
report("default value function for column",'create_default_func',
1847
"create table crash_q (q integer not null,q1 integer default (1+1))",
1848
"drop table crash_q $drop_attr");
1850
report("temporary tables",'temporary_table',
1851
"create temporary table crash_q (q integer not null)",
1852
"drop table crash_q $drop_attr");
1854
report_one("create table from select",'create_table_select',
1855
[["create table crash_q SELECT * from crash_me","yes"],
1856
["create table crash_q AS SELECT * from crash_me","with AS"]]);
1857
$dbh->do("drop table crash_q $drop_attr");
1859
report("index in create table",'index_in_create',
1860
"create table crash_q (q integer not null,index (q))",
1861
"drop table crash_q $drop_attr");
1863
# The following must be executed as we need the value of end_drop_keyword
1865
if (!(defined($limits{'create_index'}) && defined($limits{'drop_index'})))
1867
if ($res=safe_query_l('create_index',"create index crash_q on crash_me (a)"))
1871
$end_drop_keyword="";
1872
if (!safe_query_l('drop_index',"drop index crash_q"))
1874
# Can't drop the standard way; Check if mSQL
1875
if (safe_query_l('drop_index',"drop index crash_q from crash_me"))
1877
$drop_res="with 'FROM'"; # Drop is not ANSI SQL
1878
$end_drop_keyword="drop index %i from %t";
1880
# else check if Access or MySQL
1881
elsif (safe_query_l('drop_index',"drop index crash_q on crash_me"))
1883
$drop_res="with 'ON'"; # Drop is not ANSI SQL
1884
$end_drop_keyword="drop index %i on %t";
1886
# else check if MS-SQL
1887
elsif (safe_query_l('drop_index',"drop index crash_me.crash_q"))
1889
$drop_res="with 'table.index'"; # Drop is not ANSI SQL
1890
$end_drop_keyword="drop index %t.%i";
1895
# Old MySQL 3.21 supports only the create index syntax
1896
# This means that the second create doesn't give an error.
1897
$res=safe_query_l('create_index',["create index crash_q on crash_me (a)",
1898
"create index crash_q on crash_me (a)",
1899
"drop index crash_q"]);
1900
$res= $res ? 'ignored' : 'yes';
1905
$drop_res=$res='no';
1907
save_config_data('create_index',$res,"create index");
1908
save_config_data('drop_index',$drop_res,"drop index");
1910
print "create index: $limits{'create_index'}\n";
1911
print "drop index: $limits{'drop_index'}\n";
1914
# check if we can have 'NULL' as a key
1915
check_and_report("null in index","null_in_index",
1916
[create_table("crash_q",["a char(10)"],["(a)"]),
1917
"insert into crash_q values (NULL)"],
1918
"select * from crash_q",
1919
["drop table crash_q $drop_attr"],
1922
if ($limits{'unique_in_create'} eq 'yes')
1924
report("null in unique index",'null_in_unique',
1925
create_table("crash_q",["q integer"],["unique(q)"]),
1926
"insert into crash_q (q) values(NULL)",
1927
"insert into crash_q (q) values(NULL)",
1928
"drop table crash_q $drop_attr");
1929
report("null combination in unique index",'nulls_in_unique',
1930
create_table("crash_q",["q integer,q1 integer"],["unique(q,q1)"]),
1931
"insert into crash_q (q,q1) values(1,NULL)",
1932
"insert into crash_q (q,q1) values(1,NULL)",
1933
"drop table crash_q $drop_attr");
1936
if ($limits{'null_in_unique'} eq 'yes')
1938
report("null in unique index",'multi_null_in_unique',
1939
create_table("crash_q",["q integer, x integer"],["unique(q)"]),
1940
"insert into crash_q(x) values(1)",
1941
"insert into crash_q(x) values(2)",
1942
"drop table crash_q $drop_attr");
1945
if ($limits{'create_index'} ne 'no')
1947
$end_drop=$end_drop_keyword;
1948
$end_drop =~ s/%i/crash_q/;
1949
$end_drop =~ s/%t/crash_me/;
1950
report("index on column part (extension)","index_parts",,
1951
"create index crash_q on crash_me (b(5))",
1953
$end_drop=$end_drop_keyword;
1954
$end_drop =~ s/%i/crash_me/;
1955
$end_drop =~ s/%t/crash_me/;
1956
report("different namespace for index",
1958
"create index crash_me on crash_me (b)",
1962
if (!report("case independent table names","table_name_case",
1963
"create table crash_q (q integer)",
1964
"drop table CRASH_Q $drop_attr"))
1966
safe_query("drop table crash_q $drop_attr");
1969
if (!report("case independent field names","field_name_case",
1970
"create table crash_q (q integer)",
1971
"insert into crash_q(Q) values (1)",
1972
"drop table crash_q $drop_attr"))
1974
safe_query("drop table crash_q $drop_attr");
1977
if (!report("drop table if exists","drop_if_exists",
1978
"create table crash_q (q integer)",
1979
"drop table if exists crash_q $drop_attr"))
1981
safe_query("drop table crash_q $drop_attr");
1984
report("create table if not exists","create_if_not_exists",
1985
"create table crash_q (q integer)",
1986
"create table if not exists crash_q (q integer)");
1987
safe_query("drop table crash_q $drop_attr");
1990
# test of different join types
1993
assert("create table crash_me2 (a integer not null,b char(10) not null,".
1995
assert("insert into crash_me2 (a,b,c1) values (1,'b',1)");
1996
assert("create table crash_me3 (a integer not null,b char(10) not null)");
1997
assert("insert into crash_me3 (a,b) values (1,'b')");
1999
report("inner join","inner_join",
2000
"select crash_me.a from crash_me inner join crash_me2 ON ".
2001
"crash_me.a=crash_me2.a");
2002
report("left outer join","left_outer_join",
2003
"select crash_me.a from crash_me left join crash_me2 ON ".
2004
"crash_me.a=crash_me2.a");
2005
report("natural left outer join","natural_left_outer_join",
2006
"select c1 from crash_me natural left join crash_me2");
2007
report("left outer join using","left_outer_join_using",
2008
"select c1 from crash_me left join crash_me2 using (a)");
2009
report("left outer join odbc style","odbc_left_outer_join",
2010
"select crash_me.a from { oj crash_me left outer join crash_me2 ON".
2011
" crash_me.a=crash_me2.a }");
2012
report("right outer join","right_outer_join",
2013
"select crash_me.a from crash_me right join crash_me2 ON ".
2014
"crash_me.a=crash_me2.a");
2015
report("full outer join","full_outer_join",
2016
"select crash_me.a from crash_me full join crash_me2 ON "."
2017
crash_me.a=crash_me2.a");
2018
report("cross join (same as from a,b)","cross_join",
2019
"select crash_me.a from crash_me cross join crash_me3");
2020
report("natural join","natural_join",
2021
"select * from crash_me natural join crash_me3");
2022
report("union","union",
2023
"select * from crash_me union select a,b from crash_me3");
2024
report("union all","union_all",
2025
"select * from crash_me union all select a,b from crash_me3");
2026
report("intersect","intersect",
2027
"select * from crash_me intersect select * from crash_me3");
2028
report("intersect all","intersect_all",
2029
"select * from crash_me intersect all select * from crash_me3");
2030
report("except","except",
2031
"select * from crash_me except select * from crash_me3");
2032
report("except all","except_all",
2033
"select * from crash_me except all select * from crash_me3");
2034
report("except","except",
2035
"select * from crash_me except select * from crash_me3");
2036
report("except all","except_all",
2037
"select * from crash_me except all select * from crash_me3");
2038
report("minus","minus",
2039
"select * from crash_me minus select * from crash_me3"); # oracle ...
2041
report("natural join (incompatible lists)","natural_join_incompat",
2042
"select c1 from crash_me natural join crash_me2");
2043
report("union (incompatible lists)","union_incompat",
2044
"select * from crash_me union select a,b from crash_me2");
2045
report("union all (incompatible lists)","union_all_incompat",
2046
"select * from crash_me union all select a,b from crash_me2");
2047
report("intersect (incompatible lists)","intersect_incompat",
2048
"select * from crash_me intersect select * from crash_me2");
2049
report("intersect all (incompatible lists)","intersect_all_incompat",
2050
"select * from crash_me intersect all select * from crash_me2");
2051
report("except (incompatible lists)","except_incompat",
2052
"select * from crash_me except select * from crash_me2");
2053
report("except all (incompatible lists)","except_all_incompat",
2054
"select * from crash_me except all select * from crash_me2");
2055
report("except (incompatible lists)","except_incompat",
2056
"select * from crash_me except select * from crash_me2");
2057
report("except all (incompatible lists)","except_all_incompat",
2058
"select * from crash_me except all select * from crash_me2");
2059
report("minus (incompatible lists)","minus_incompat",
2060
"select * from crash_me minus select * from crash_me2"); # oracle ...
2062
assert("drop table crash_me2 $drop_attr");
2063
assert("drop table crash_me3 $drop_attr");
2065
# somethings to be added here ....
2066
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
2068
# >ALL | ANY | SOME - EXISTS - UNIQUE
2070
if (report("subqueries","subqueries",
2071
"select a from crash_me where crash_me.a in ".
2072
"(select max(a) from crash_me)"))
2074
$tmp=new query_repeat([],"select a from crash_me","","",
2075
" where a in (select a from crash_me",")",
2076
"",[],$max_join_tables);
2077
find_limit("recursive subqueries", "recursive_subqueries",$tmp);
2080
report("insert INTO ... SELECT ...","insert_select",
2081
"create table crash_q (a int)",
2082
"insert into crash_q (a) SELECT crash_me.a from crash_me",
2083
"drop table crash_q $drop_attr");
2085
if (!defined($limits{"transactions"}))
2088
$limit="transactions";
2089
$limit_r="rollback_metadata";
2091
foreach $type (('', 'type=bdb', 'type=innodb', 'type=gemini'))
2093
undef($limits{$limit});
2094
if (!report_trans($limit,
2095
[create_table("crash_q",["a integer not null"],[],
2097
"insert into crash_q values (1)"],
2098
"select * from crash_q",
2099
"drop table crash_q $drop_attr"
2102
report_rollback($limit_r,
2103
[create_table("crash_q",["a integer not null"],[],
2105
"insert into crash_q values (1)",
2106
"drop table crash_q $drop_attr" );
2110
print "$limits{$limit}\n";
2111
print "$limit_r: $limits{$limit_r}\n";
2114
report("atomic updates","atomic_updates",
2115
create_table("crash_q",["a integer not null"],["primary key (a)"]),
2116
"insert into crash_q values (2)",
2117
"insert into crash_q values (3)",
2118
"insert into crash_q values (1)",
2119
"update crash_q set a=a+1",
2120
"drop table crash_q $drop_attr");
2122
if ($limits{'atomic_updates'} eq 'yes')
2124
report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
2125
create_table("crash_q",["a integer not null"],
2126
["primary key (a)"]),
2127
"insert into crash_q values (2)",
2128
"insert into crash_q values (3)",
2129
"insert into crash_q values (1)",
2130
"update crash_q set a=a+1 where a < 3",
2131
"drop table crash_q $drop_attr");
2134
# To add with the views:
2135
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
2136
report("views","views",
2137
"create view crash_q as select a from crash_me",
2138
"drop view crash_q $drop_attr");
2142
my $result = 'undefined';
2144
print "foreign keys: ";
2145
save_incomplete('foreign_key','foreign keys');
2147
# 1) check if foreign keys are supported
2148
safe_query_l('foreign_key',
2149
create_table("crash_me_qf",
2150
["a integer not null"],
2151
["primary key (a)"]));
2152
$error= safe_query_l('foreign_key',
2153
create_table("crash_me_qf2",
2154
["a integer not null",
2155
"foreign key (a) references crash_me_qf (a)"],
2158
if ($error == 1) # OK -- syntax is supported
2161
# now check if foreign key really works
2162
safe_query_l('foreign_key', "insert into crash_me_qf values (1)");
2163
if (safe_query_l('foreign_key', "insert into crash_me_qf2 values (2)") eq 1)
2165
$result = 'syntax only';
2176
safe_query_l('foreign_key', "drop table crash_me_qf2 $drop_attr");
2177
safe_query_l('foreign_key', "drop table crash_me_qf $drop_attr");
2179
save_config_data('foreign_key',$result,"foreign keys");
2182
if ($limits{'foreign_key'} eq 'yes')
2184
report("allows to update of foreign key values",'foreign_update',
2185
"create table crash_me1 (a int not null primary key)",
2186
"create table crash_me2 (a int not null," .
2187
" foreign key (a) references crash_me1 (a))",
2188
"insert into crash_me1 values (1)",
2189
"insert into crash_me2 values (1)",
2190
"update crash_me1 set a = 2", ## <- must fail
2191
"drop table crash_me2 $drop_attr",
2192
"drop table crash_me1 $drop_attr"
2196
report("Create SCHEMA","create_schema",
2197
"create schema crash_schema create table crash_q (a int) ".
2198
"create table crash_q2(b int)",
2199
"drop schema crash_schema cascade");
2201
if ($limits{'foreign_key'} eq 'yes')
2203
if ($limits{'create_schema'} eq 'yes')
2205
report("Circular foreign keys","foreign_key_circular",
2206
"create schema crash_schema create table crash_q ".
2207
"(a int primary key, b int, foreign key (b) references ".
2208
"crash_q2(a)) create table crash_q2(a int, b int, ".
2209
"primary key(a), foreign key (b) references crash_q(a))",
2210
"drop schema crash_schema cascade");
2214
if ($limits{'func_sql_character_length'} eq 'yes')
2216
my $result = 'error';
2218
my $key = 'length_of_varchar_field';
2219
my $prompt='CHARACTER_LENGTH(varchar_field)';
2220
print $prompt," = ";
2221
if (!defined($limits{$key})) {
2222
save_incomplete($key,$prompt);
2224
"CREATE TABLE crash_me1 (S1 VARCHAR(100))",
2225
"INSERT INTO crash_me1 VALUES ('X')"
2227
my $recset = get_recordset($key,
2228
"SELECT CHARACTER_LENGTH(S1) FROM crash_me1");
2229
print_recordset($key,$recset);
2230
if (defined($recset)){
2231
if ( $recset->[0][0] eq 1 ) {
2232
$result = 'actual length';
2233
} elsif( $recset->[0][0] eq 100 ) {
2234
$result = 'defined length';
2237
add_log($key,$DBI::errstr);
2239
safe_query_l($key, "drop table crash_me1 $drop_attr");
2240
save_config_data($key,$result,$prompt);
2242
$result = $limits{$key};
2248
check_constraint("Column constraints","constraint_check",
2249
"create table crash_q (a int check (a>0))",
2250
"insert into crash_q values(0)",
2251
"drop table crash_q $drop_attr");
2254
check_constraint("Table constraints","constraint_check_table",
2255
"create table crash_q (a int ,b int, check (a>b))",
2256
"insert into crash_q values(0,0)",
2257
"drop table crash_q $drop_attr");
2259
check_constraint("Named constraints","constraint_check_named",
2260
"create table crash_q (a int ,b int, constraint abc check (a>b))",
2261
"insert into crash_q values(0,0)",
2262
"drop table crash_q $drop_attr");
2265
report("NULL constraint (SyBase style)","constraint_null",
2266
"create table crash_q (a int null)",
2267
"drop table crash_q $drop_attr");
2269
report("Triggers (ANSI SQL)","psm_trigger",
2270
"create table crash_q (a int ,b int)",
2271
"create trigger crash_trigger after insert on crash_q referencing ".
2272
"new table as new_a when (localtime > time '18:00:00') ".
2274
"insert into crash_q values(1,2)",
2275
"drop trigger crash_trigger",
2276
"drop table crash_q $drop_attr");
2278
report("PSM procedures (ANSI SQL)","psm_procedures",
2279
"create table crash_q (a int,b int)",
2280
"create procedure crash_proc(in a1 int, in b1 int) language ".
2281
"sql modifies sql data begin declare c1 int; set c1 = a1 + b1;".
2282
" insert into crash_q(a,b) values (a1,c1); end",
2283
"call crash_proc(1,10)",
2284
"drop procedure crash_proc",
2285
"drop table crash_q $drop_attr");
2287
report("PSM modules (ANSI SQL)","psm_modules",
2288
"create table crash_q (a int,b int)",
2289
"create module crash_m declare procedure ".
2290
"crash_proc(in a1 int, in b1 int) language sql modifies sql ".
2291
"data begin declare c1 int; set c1 = a1 + b1; ".
2292
"insert into crash_q(a,b) values (a1,c1); end; ".
2293
"declare procedure crash_proc2(INOUT a int, in b int) ".
2294
"contains sql set a = b + 10; end module",
2295
"call crash_proc(1,10)",
2296
"drop module crash_m cascade",
2297
"drop table crash_q cascade $drop_attr");
2299
report("PSM functions (ANSI SQL)","psm_functions",
2300
"create table crash_q (a int)",
2301
"create function crash_func(in a1 int, in b1 int) returns int".
2302
" language sql deterministic contains sql ".
2303
" begin return a1 * b1; end",
2304
"insert into crash_q values(crash_func(2,4))",
2305
"select a,crash_func(a,2) from crash_q",
2306
"drop function crash_func cascade",
2307
"drop table crash_q $drop_attr");
2309
report("Domains (ANSI SQL)","domains",
2310
"create domain crash_d as varchar(10) default 'Empty' ".
2311
"check (value <> 'abcd')",
2312
"create table crash_q(a crash_d, b int)",
2313
"insert into crash_q(a,b) values('xyz',10)",
2314
"insert into crash_q(b) values(10)",
2315
"drop table crash_q $drop_attr",
2316
"drop domain crash_d");
2319
if (!defined($limits{'lock_tables'}))
2321
report("lock table","lock_tables",
2322
"lock table crash_me READ",
2324
if ($limits{'lock_tables'} eq 'no')
2326
delete $limits{'lock_tables'};
2327
report("lock table","lock_tables",
2328
"lock table crash_me IN SHARE MODE");
2332
if (!report("many tables to drop table","multi_drop",
2333
"create table crash_q (a int)",
2334
"create table crash_q2 (a int)",
2335
"drop table crash_q,crash_q2 $drop_attr"))
2337
$dbh->do("drop table crash_q $drop_attr");
2338
$dbh->do("drop table crash_q2 $drop_attr");
2341
if (!report("drop table with cascade/restrict","drop_restrict",
2342
"create table crash_q (a int)",
2343
"drop table crash_q restrict"))
2345
$dbh->do("drop table crash_q $drop_attr");
2349
report("-- as comment (ANSI)","comment_--",
2350
"select * from crash_me -- Testing of comments");
2351
report("// as comment","comment_//",
2352
"select * from crash_me // Testing of comments");
2353
report("# as comment","comment_#",
2354
"select * from crash_me # Testing of comments");
2355
report("/* */ as comment","comment_/**/",
2356
"select * from crash_me /* Testing of comments */");
2359
# Check things that fails one some servers
2362
# Empress can't insert empty strings in a char() field
2363
report("insert empty string","insert_empty_string",
2364
create_table("crash_q",["a char(10) not null,b char(10)"],[]),
2365
"insert into crash_q values ('','')",
2366
"drop table crash_q $drop_attr");
2368
report("Having with alias","having_with_alias",
2369
create_table("crash_q",["a integer"],[]),
2370
"insert into crash_q values (10)",
2371
"select sum(a) as b from crash_q group by a having b > 0",
2372
"drop table crash_q $drop_attr");
2378
find_limit("table name length","max_table_name",
2379
new query_many(["create table crash_q%s (q integer)",
2380
"insert into crash_q%s values(1)"],
2381
"select * from crash_q%s",1,
2382
["drop table crash_q%s $drop_attr"],
2383
$max_name_length,7,1));
2385
find_limit("column name length","max_column_name",
2386
new query_many(["create table crash_q (q%s integer)",
2387
"insert into crash_q (q%s) values(1)"],
2388
"select q%s from crash_q",1,
2389
["drop table crash_q $drop_attr"],
2390
$max_name_length,1));
2392
if ($limits{'column_alias'} eq 'yes')
2394
find_limit("select alias name length","max_select_alias_name",
2395
new query_many(undef,
2396
"select b as %s from crash_me",undef,
2397
undef, $max_name_length));
2400
find_limit("table alias name length","max_table_alias_name",
2401
new query_many(undef,
2402
"select %s.b from crash_me %s",
2404
undef, $max_name_length));
2406
$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
2407
$end_drop=$end_drop_keyword;
2408
$end_drop =~ s/%i/crash_q%s/;
2409
$end_drop =~ s/%t/crash_me/;
2411
if ($limits{'create_index'} ne 'no')
2413
find_limit("index name length","max_index_name",
2414
new query_many(["create index crash_q%s on crash_me (a)"],
2417
$max_name_length,7));
2420
find_limit("max char() size","max_char_size",
2421
new query_many(["create table crash_q (q char(%d))",
2422
"insert into crash_q values ('%s')"],
2423
"select * from crash_q","%s",
2424
["drop table crash_q $drop_attr"],
2425
min($max_string_size,$limits{'query_size'})));
2427
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2429
find_limit("max varchar() size","max_varchar_size",
2430
new query_many(["create table crash_q (q varchar(%d))",
2431
"insert into crash_q values ('%s')"],
2432
"select * from crash_q","%s",
2433
["drop table crash_q $drop_attr"],
2434
min($max_string_size,$limits{'query_size'})));
2438
foreach $type (('mediumtext','text','text()','blob','long'))
2440
if ($limits{"type_extra_$type"} eq 'yes')
2446
if (defined($found))
2448
$found =~ s/\(\)/\(%d\)/;
2449
find_limit("max text or blob size","max_text_size",
2450
new query_many(["create table crash_q (q $found)",
2451
"insert into crash_q values ('%s')"],
2452
"select * from crash_q","%s",
2453
["drop table crash_q $drop_attr"],
2454
min($max_string_size,$limits{'query_size'}-30)));
2458
$tmp=new query_repeat([],"create table crash_q (a integer","","",
2459
",a%d integer","",")",["drop table crash_q $drop_attr"],
2462
find_limit("Columns in table","max_columns",$tmp);
2464
# Make a field definition to be used when testing keys
2466
$key_definitions="q0 integer not null";
2468
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2470
$key_definitions.=",q$i integer not null";
2471
$key_fields.=",q$i";
2473
$key_values="1," x $i;
2476
if ($limits{'unique_in_create'} eq 'yes')
2478
find_limit("unique indexes","max_unique_index",
2479
new query_table("create table crash_q (q integer",
2480
",q%d integer not null,unique (q%d)",")",
2481
["insert into crash_q (q,%f) values (1,%v)"],
2482
"select q from crash_q",1,
2483
"drop table crash_q $drop_attr",
2486
find_limit("index parts","max_index_parts",
2487
new query_table("create table crash_q ".
2488
"($key_definitions,unique (q0",
2490
["insert into crash_q ($key_fields) values ($key_values)"],
2491
"select q0 from crash_q",1,
2492
"drop table crash_q $drop_attr",
2495
find_limit("max index part length","max_index_part_length",
2496
new query_many(["create table crash_q (q char(%d) not null,".
2498
"insert into crash_q (q) values ('%s')"],
2499
"select q from crash_q","%s",
2500
["drop table crash_q $drop_attr"],
2501
$limits{'max_char_size'},0));
2503
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2505
find_limit("index varchar part length","max_index_varchar_part_length",
2506
new query_many(["create table crash_q (q varchar(%d) not null,".
2508
"insert into crash_q (q) values ('%s')"],
2509
"select q from crash_q","%s",
2510
["drop table crash_q $drop_attr"],
2511
$limits{'max_varchar_size'},0));
2516
if ($limits{'create_index'} ne 'no')
2518
if ($limits{'create_index'} eq 'ignored' ||
2519
$limits{'unique_in_create'} eq 'yes')
2520
{ # This should be true
2521
add_log('max_index',
2522
" max_unique_index=$limits{'max_unique_index'} ,".
2523
"so max_index must be same");
2524
save_config_data('max_index',$limits{'max_unique_index'},"max index");
2525
print "indexes: $limits{'max_index'}\n";
2529
if (!defined($limits{'max_index'}))
2531
safe_query_l('max_index',"create table crash_q ($key_definitions)");
2532
for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
2534
last if (!safe_query_l('max_index',
2535
"create index crash_q$i on crash_q (q$i)"));
2537
save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
2541
$end_drop=$end_drop_keyword;
2542
$end_drop =~ s/%i/crash_q$i/;
2543
$end_drop =~ s/%t/crash_q/;
2546
assert("drop table crash_q $drop_attr");
2548
print "indexs: $limits{'max_index'}\n";
2549
if (!defined($limits{'max_unique_index'}))
2551
safe_query_l('max_unique_index',
2552
"create table crash_q ($key_definitions)");
2553
for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2555
last if (!safe_query_l('max_unique_index',
2556
"create unique index crash_q$i on crash_q (q$i)"));
2558
save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
2559
"max unique index");
2562
$end_drop=$end_drop_keyword;
2563
$end_drop =~ s/%i/crash_q$i/;
2564
$end_drop =~ s/%t/crash_q/;
2567
assert("drop table crash_q $drop_attr");
2569
print "unique indexes: $limits{'max_unique_index'}\n";
2570
if (!defined($limits{'max_index_parts'}))
2572
safe_query_l('max_index_parts',
2573
"create table crash_q ($key_definitions)");
2574
$end_drop=$end_drop_keyword;
2575
$end_drop =~ s/%i/crash_q1%d/;
2576
$end_drop =~ s/%t/crash_q/;
2577
find_limit("index parts","max_index_parts",
2578
new query_table("create index crash_q1%d on crash_q (q0",
2584
assert("drop table crash_q $drop_attr");
2588
print "index parts: $limits{'max_index_parts'}\n";
2590
$end_drop=$end_drop_keyword;
2591
$end_drop =~ s/%i/crash_q2%d/;
2592
$end_drop =~ s/%t/crash_me/;
2594
find_limit("index part length","max_index_part_length",
2595
new query_many(["create table crash_q (q char(%d))",
2596
"create index crash_q2%d on crash_q (q)",
2597
"insert into crash_q values('%s')"],
2598
"select q from crash_q",
2601
"drop table crash_q $drop_attr"],
2602
min($limits{'max_char_size'},"+8192")));
2606
find_limit("index length","max_index_length",
2607
new query_index_length("create table crash_q ",
2608
"drop table crash_q $drop_attr",
2611
find_limit("max table row length (without blobs)","max_row_length",
2612
new query_row_length("crash_q ",
2614
"drop table crash_q $drop_attr",
2615
min($max_row_length,
2616
$limits{'max_columns'}*
2617
min($limits{'max_char_size'},255))));
2619
find_limit("table row length with nulls (without blobs)",
2620
"max_row_length_with_null",
2621
new query_row_length("crash_q ",
2623
"drop table crash_q $drop_attr",
2624
$limits{'max_row_length'}*2));
2626
find_limit("number of columns in order by","columns_in_order_by",
2627
new query_many(["create table crash_q (%F)",
2628
"insert into crash_q values(%v)",
2629
"insert into crash_q values(%v)"],
2630
"select * from crash_q order by %f",
2632
["drop table crash_q $drop_attr"],
2635
find_limit("number of columns in group by","columns_in_group_by",
2636
new query_many(["create table crash_q (%F)",
2637
"insert into crash_q values(%v)",
2638
"insert into crash_q values(%v)"],
2639
"select %f from crash_q group by %f",
2641
["drop table crash_q $drop_attr"],
2646
# Safe arithmetic test
2648
$prompt="safe decimal arithmetic";
2649
$key="safe_decimal_arithmetic";
2650
if (!defined($limits{$key}))
2653
save_incomplete($key,$prompt);
2654
if (!safe_query_l($key,$server->create("crash_me_a",
2655
["a decimal(10,2)","b decimal(10,2)"])))
2657
print DBI->errstr();
2658
die "Can't create table 'crash_me_a' $DBI::errstr\n";
2661
if (!safe_query_l($key,
2662
["insert into crash_me_a (a,b) values (11.4,18.9)"]))
2664
die "Can't insert into table 'crash_me_a' a record: $DBI::errstr\n";
2667
$arithmetic_safe = 'no';
2668
$arithmetic_safe = 'yes'
2669
if ( (safe_query_result_l($key,
2670
'select count(*) from crash_me_a where a+b=30.3',1,0) == 0)
2671
and (safe_query_result_l($key,
2672
'select count(*) from crash_me_a where a+b-30.3 = 0',1,0) == 0)
2673
and (safe_query_result_l($key,
2674
'select count(*) from crash_me_a where a+b-30.3 < 0',0,0) == 0)
2675
and (safe_query_result_l($key,
2676
'select count(*) from crash_me_a where a+b-30.3 > 0',0,0) == 0));
2677
save_config_data($key,$arithmetic_safe,$prompt);
2678
print "$arithmetic_safe\n";
2679
assert("drop table crash_me_a $drop_attr");
2683
print "$prompt=$limits{$key} (cached)\n";
2686
# Check where is null values in sorted recordset
2687
if (!safe_query($server->create("crash_me_n",["i integer","r integer"])))
2689
print DBI->errstr();
2690
die "Can't create table 'crash_me_n' $DBI::errstr\n";
2693
safe_query_l("position_of_null",["insert into crash_me_n (i) values(1)",
2694
"insert into crash_me_n values(2,2)",
2695
"insert into crash_me_n values(3,3)",
2696
"insert into crash_me_n values(4,4)",
2697
"insert into crash_me_n (i) values(5)"]);
2699
$key = "position_of_null";
2700
$prompt ="Where is null values in sorted recordset";
2701
if (!defined($limits{$key}))
2703
save_incomplete($key,$prompt);
2705
$sth=$dbh->prepare("select r from crash_me_n order by r ");
2707
add_log($key,"< select r from crash_me_n order by r ");
2708
$limit= detect_null_position($key,$sth);
2711
save_config_data($key,$limit,$prompt);
2713
print "$prompt=$limits{$key} (cache)\n";
2716
$key = "position_of_null_desc";
2717
$prompt ="Where is null values in sorted recordset (DESC)";
2718
if (!defined($limits{$key}))
2720
save_incomplete($key,$prompt);
2722
$sth=$dbh->prepare("select r from crash_me_n order by r desc");
2724
add_log($key,"< select r from crash_me_n order by r desc");
2725
$limit= detect_null_position($key,$sth);
2728
save_config_data($key,$limit,$prompt);
2730
print "$prompt=$limits{$key} (cache)\n";
2734
assert("drop table crash_me_n $drop_attr");
2738
$key = 'sorted_group_by';
2739
$prompt = 'Group by always sorted';
2740
if (!defined($limits{$key}))
2742
save_incomplete($key,$prompt);
2745
"create table crash_me_t1 (a int not null, b int not null)",
2746
"insert into crash_me_t1 values (1,1)",
2747
"insert into crash_me_t1 values (1,2)",
2748
"insert into crash_me_t1 values (3,1)",
2749
"insert into crash_me_t1 values (3,2)",
2750
"insert into crash_me_t1 values (2,2)",
2751
"insert into crash_me_t1 values (2,1)",
2752
"create table crash_me_t2 (a int not null, b int not null)",
2753
"create index crash_me_t2_ind on crash_me_t2 (a)",
2754
"insert into crash_me_t2 values (1,3)",
2755
"insert into crash_me_t2 values (3,1)",
2756
"insert into crash_me_t2 values (2,2)",
2757
"insert into crash_me_t2 values (1,1)"]);
2759
my $bigqry = "select crash_me_t1.a,crash_me_t2.b from ".
2760
"crash_me_t1,crash_me_t2 where crash_me_t1.a=crash_me_t2.a ".
2761
"group by crash_me_t1.a,crash_me_t2.b";
2764
my $rs = get_recordset($key,$bigqry);
2765
print_recordset($key,$rs);
2766
if ( defined ($rs)) {
2767
if (compare_recordset($key,$rs,[[1,1],[1,3],[2,2],[3,1]]) eq 0)
2772
add_log($key,"error: ".$DBI::errstr);
2776
safe_query_l($key,["drop table crash_me_t1",
2777
"drop table crash_me_t2"]);
2778
save_config_data($key,$limit,$prompt);
2781
print "$prompt=$limits{$key} (cashed)\n";
2789
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2791
print "crash-me safe: $limits{'crash_me_safe'}\n";
2792
print "reconnected $reconnect_count times\n";
2794
$dbh->disconnect || warn $dbh->errstr;
2795
save_all_config_data();
2801
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2803
print "crash-me safe: $limits{'crash_me_safe'}\n";
2804
print "reconnected $reconnect_count times\n";
2806
$dbh->disconnect || warn $dbh->errstr;
2807
save_all_config_data();
2810
# Check where is nulls in the sorted result (for)
2811
# it expects exactly 5 rows in the result
2813
sub detect_null_position
2817
my ($z,$r1,$r2,$r3,$r4,$r5);
2818
$r1 = $sth->fetchrow_array; add_log($key,"> $r1");
2819
$r2 = $sth->fetchrow_array; add_log($key,"> $r2");
2820
$r3 = $sth->fetchrow_array; add_log($key,"> $r3");
2821
$r4 = $sth->fetchrow_array; add_log($key,"> $r4");
2822
$r5 = $sth->fetchrow_array; add_log($key,"> $r5");
2823
return "first" if ( !defined($r1) && !defined($r2) && defined($r3));
2824
return "last" if ( !defined($r5) && !defined($r4) && defined($r3));
2828
sub check_parenthesis {
2832
my $param_name=$prefix.lc($fn);
2835
save_incomplete($param_name,$fn);
2836
$r = safe_query("select $fn $end_query");
2837
add_log($param_name,$safe_query_log);
2843
$r = safe_query("select $fn() $end_query");
2844
add_log($param_name,$safe_query_log);
2847
$result="with_parenthesis";
2851
save_config_data($param_name,$result,$fn);
2854
sub check_constraint {
2860
save_incomplete($key,$prompt);
2864
$t=safe_query($create);
2865
add_log($key,$safe_query_log);
2869
$t= safe_query($check);
2870
add_log($key,$safe_query_log);
2877
add_log($key,$safe_query_log);
2879
save_config_data($key,$res,$prompt);
2887
$_ = $limits{'time_format_inresult'};
2888
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second if (/^iso$/);
2889
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second if (/^euro/);
2890
return sprintf "%02d:%02d %s",
2891
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
2893
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second if (/^HHMMSS/);
2894
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second if (/^HHHHMMSS/);
2895
return "UNKNOWN FORMAT";
2902
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second
2903
if ($limits{'time_format_ISO'} eq "yes");
2904
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second
2905
if ($limits{'time_format_EUR'} eq "yes");
2906
return sprintf "%02d:%02d %s",
2907
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
2908
if ($limits{'time_format_USA'} eq "yes");
2909
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second
2910
if ($limits{'time_format_HHMMSS'} eq "yes");
2911
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second
2912
if ($limits{'time_format_HHHHMMSS'} eq "yes");
2913
return "UNKNOWN FORMAT";
2920
$_ = $limits{'date_format_inresult'};
2921
return sprintf "%02d-%02d-%02d", ($year%100),$month,$day if (/^short iso$/);
2922
return sprintf "%04d-%02d-%02d", $year,$month,$day if (/^iso/);
2923
return sprintf "%02d.%02d.%02d", $day,$month,($year%100) if (/^short euro/);
2924
return sprintf "%02d.%02d.%04d", $day,$month,$year if (/^euro/);
2925
return sprintf "%02d/%02d/%02d", $month,$day,($year%100) if (/^short usa/);
2926
return sprintf "%02d/%02d/%04d", $month,$day,$year if (/^usa/);
2927
return sprintf "%04d%02d%02d", $year,$month,$day if (/^YYYYMMDD/);
2928
return "UNKNOWN FORMAT";
2936
return sprintf "'%04d-%02d-%02d'", $year,$month,$day
2937
if ($limits{'date_format_ISO'} eq yes);
2938
return sprintf "DATE '%04d-%02d-%02d'", $year,$month,$day
2939
if ($limits{'date_format_ISO_with_date'} eq yes);
2940
return sprintf "'%02d.%02d.%04d'", $day,$month,$year
2941
if ($limits{'date_format_EUR'} eq 'yes');
2942
return sprintf "DATE '%02d.%02d.%04d'", $day,$month,$year
2943
if ($limits{'date_format_EUR_with_date'} eq 'yes');
2944
return sprintf "'%02d/%02d/%04d'", $month,$day,$year
2945
if ($limits{'date_format_USA'} eq 'yes');
2946
return sprintf "DATE '%02d/%02d/%04d'", $month,$day,$year
2947
if ($limits{'date_format_USA_with_date'} eq 'yes');
2948
return sprintf "'%04d%02d%02d'", $year,$month,$day
2949
if ($limits{'date_format_YYYYMMDD'} eq 'yes');
2950
return sprintf "DATE '%04d%02d%02d'", $year,$month,$day
2951
if ($limits{'date_format_YYYYMMDD_with_date'} eq 'yes');
2952
return "UNKNOWN FORMAT";
2956
sub print_recordset{
2957
my ($key,$recset) = @_;
2959
foreach $rec (@$recset)
2961
add_log($key, " > ".join(',', map(repr($_), @$rec)));
2966
# read result recordset from sql server.
2967
# returns arrayref to (arrayref to) values
2968
# or undef (in case of sql errors)
2971
my ($key,$query) = @_;
2972
add_log($key, "< $query");
2973
return $dbh->selectall_arrayref($query);
2976
# function for comparing recordset (that was returned by get_recordset)
2977
# and arrayref of (arrayref of) values.
2979
# returns : zero if recordset equal that array, 1 if it doesn't equal
2982
# $key - current operation (for logging)
2983
# $recset - recordset
2984
# $mustbe - array of values that we expect
2986
# example: $a=get_recordset('some_parameter','select a,b from c');
2987
# if (compare_recordset('some_parameter',$a,[[1,1],[1,2],[1,3]]) neq 0)
2989
# print "unexpected result\n";
2992
sub compare_recordset {
2993
my ($key,$recset,$mustbe) = @_;
2994
my $rec,$recno,$fld,$fldno,$fcount;
2995
add_log($key,"\n Check recordset:");
2997
foreach $rec (@$recset)
2999
add_log($key," " . join(',', map(repr($_),@$rec)) . " expected: " .
3000
join(',', map(repr($_), @{$mustbe->[$recno]} ) ));
3003
foreach $fldno (0 .. $fcount )
3005
if ($mustbe->[$recno][$fldno] ne $rec->[$fldno])
3007
add_log($key," Recordset doesn't correspond with template");
3013
add_log($key," Recordset corresponds with template");
3018
# converts inner perl value to printable representation
3019
# for example: undef maps to 'NULL',
3020
# string -> 'string'
3025
return "'$s'"if ($s =~ /\D/);
3026
return 'NULL'if ( not defined($s));
3033
print "$0 Ver $version\n";
3042
This program tries to find all limits and capabilities for a SQL
3043
server. As it will use the server in some 'unexpected' ways, one
3044
shouldn\'t have anything important running on it at the same time this
3045
program runs! There is a slight chance that something unexpected may
3048
As all used queries are legal according to some SQL standard. any
3049
reasonable SQL server should be able to run this test without any
3052
All questions is cached in $opt_dir/'server_name'[-suffix].cfg that
3053
future runs will use limits found in previous runs. Remove this file
3054
if you want to find the current limits for your version of the
3057
This program uses some table names while testing things. If you have any
3058
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
3059
they will be deleted by this test!
3061
$0 takes the following options:
3063
--help or --Information
3067
Don\'t ask any questions, quit on errors.
3069
--config-file='filename'
3070
Read limit results from specific file
3072
--comment='some comment'
3073
Add this comment to the crash-me limit file
3076
Do a new connection to the server every time crash-me checks if the server
3077
is alive. This can help in cases where the server starts returning wrong
3078
data because of an earlier select.
3080
--database='database' (Default $opt_database)
3081
Create test tables in this database.
3084
Save crash-me output in this directory
3087
Lots of printing to help debugging if something goes wrong.
3090
Reformat the crash-me limit file. crash-me is not run!
3093
Start test at once, without a warning screen and without questions.
3094
This is a option for the very brave.
3095
Use this in your cron scripts to test your database every night.
3098
Prints all queries that are executed. Mostly used for debugging crash-me.
3100
--log-queries-to-file='filename'
3101
Log full queries to file.
3103
--host='hostname' (Default $opt_host)
3104
Run tests on this host.
3106
--password='password'
3107
Password for the current user.
3110
Save states during each limit tests. This will make it possible to continue
3111
by restarting with the same options if there is some bug in the DBI or
3112
DBD driver that caused $0 to die!
3114
--server='server name' (Default $opt_server)
3115
Run the test on the given server.
3116
Known servers names are: Access, Adabas, AdabasD, Empress, Oracle,
3117
Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid or Sybase.
3118
For others $0 can\'t report the server version.
3120
--suffix='suffix' (Default '')
3121
Add suffix to the output filename. For instance if you run crash-me like
3122
"crash-me --suffix="myisam",
3123
then output filename will look "mysql-myisam.cfg".
3126
User name to log into the SQL server.
3128
--db-start-cmd='command to restart server'
3129
Automaticly restarts server with this command if the database server dies.
3131
--sleep='time in seconds' (Default $opt_sleep)
3132
Wait this long before restarting server.
3136
Log into the result file queries performed for determination parameter value
3147
print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
3148
if (lc($opt_server) eq "mysql")
3152
This test should not crash MySQL if it was distributed together with the
3153
running MySQL version.
3154
If this is the case you can probably continue without having to worry about
3155
destroying something.
3158
elsif (lc($opt_server) eq "msql")
3161
This test will take down mSQL repeatedly while finding limits.
3162
To make this test easier, start mSQL in another terminal with something like:
3164
while (true); do /usr/local/mSQL/bin/msql2d ; done
3166
You should be sure that no one is doing anything important with mSQL and that
3167
you have privileges to restart it!
3168
It may take awhile to determinate the number of joinable tables, so prepare to
3172
elsif (lc($opt_server) eq "solid")
3175
This test will take down Solid server repeatedly while finding limits.
3176
You should be sure that no one is doing anything important with Solid
3177
and that you have privileges to restart it!
3179
If you are running Solid without logging and/or backup YOU WILL LOSE!
3180
Solid does not write data from the cache often enough. So if you continue
3181
you may lose tables and data that you entered hours ago!
3183
Solid will also take a lot of memory running this test. You will nead
3186
When doing the connect test Solid server or the perl api will hang when
3187
freeing connections. Kill this program and restart it to continue with the
3188
test. You don\'t have to use --restart for this case.
3192
print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
3193
print "with --restart until all tests have completed\n";
3196
elsif (lc($opt_server) eq "pg")
3199
This test will crash postgreSQL when calculating the number of joinable tables!
3200
You should be sure that no one is doing anything important with postgreSQL
3201
and that you have privileges to restart it!
3207
This test may crash $opt_server repeatedly while finding limits!
3208
You should be sure that no one is doing anything important with $opt_server
3209
and that you have privileges to restart it!
3214
Some of the tests you are about to execute may require a lot of
3215
memory. Your tests WILL adversely affect system performance. It\'s
3216
not uncommon that either this crash-me test program, or the actual
3217
database back-end, will DIE with an out-of-memory error. So might
3218
any other program on your system if it requests more memory at the
3221
Note also that while crash-me tries to find limits for the database server
3222
it will make a lot of queries that can\'t be categorized as \'normal\'. It\'s
3223
not unlikely that crash-me finds some limit bug in your server so if you
3224
run this test you have to be prepared that your server may die during it!
3226
We, the creators of this utility, are not responsible in any way if your
3227
database server unexpectedly crashes while this program tries to find the
3228
limitations of your server. By accepting the following question with \'yes\',
3229
you agree to the above!
3231
You have been warned!
3236
# No default reply here so no one can blame us for starting the test
3241
print "Start test (yes/no) ? ";
3242
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3243
last if ($tmp =~ /^yes$/i);
3244
exit 1 if ($tmp =~ /^n/i);
3251
my @name = POSIX::uname();
3252
my $name= $name[0] . " " . $name[2] . " " . $name[4];
3258
# Help functions that we need
3268
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3269
{ PrintError => 0, AutoCommit => 1})))
3271
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3274
print "Error: $DBI::errstr; $server->{'data_source'} ".
3275
" - '$opt_user' - '$opt_password'\n";
3276
print "I got the above error when connecting to $opt_server\n";
3277
if (defined($object) && defined($object->{'limit'}))
3279
print "This check was done with limit: $object->{'limit'}.".
3280
"\nNext check will be done with a smaller limit!\n";
3283
save_config_data('crash_me_safe','no',"crash me safe");
3284
if ($opt_db_start_cmd)
3286
print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
3287
system("$opt_db_start_cmd");
3288
print "Waiting $opt_sleep seconds so the server can initialize\n";
3293
exit(1) if ($opt_batch_mode);
3294
print "Can you check/restart it so I can continue testing?\n";
3297
print "Continue test (yes/no) ? [yes] ";
3298
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3299
$tmp = "yes" if ($tmp eq "");
3300
last if (index("yes",$tmp) >= 0);
3301
exit 1 if (index("no",$tmp) >= 0);
3309
# Test connecting a couple of times before giving an error
3310
# This is needed to get the server time to free old connections
3311
# after the connect test
3317
for ($i=0 ; $i < 10 ; $i++)
3319
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3320
{ PrintError => 0, AutoCommit => 1})))
3322
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3327
return safe_connect();
3331
# Check if the server is up and running. If not, ask the user to restart it
3338
print "Checking connection\n" if ($opt_log_all_queries);
3339
# The following line will not work properly with interbase
3340
if ($opt_check_server && defined($check_connect) && $dbh->{AutoCommit} != 0)
3344
$dbh=safe_connect($object);
3347
return if (defined($check_connect) && defined($dbh->do($check_connect)));
3348
$dbh->disconnect || warn $dbh->errstr;
3349
print "\nreconnecting\n" if ($opt_debug);
3352
$dbh=safe_connect($object);
3356
# print query if debugging
3360
if (length($query) > 130)
3362
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3370
$last_error=$DBI::errstr;
3373
if (length($query) > 130)
3375
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3377
printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
3382
# Do one or many queries. Return 1 if all was ok
3383
# Note that all rows are executed
3384
# (to ensure that we execute drop table commands)
3390
my $r = safe_query($q);
3391
add_log($key,$safe_query_log);
3398
my($query,$ok,$retry_ok,$retry,@tmp,$sth);
3401
if (ref($queries) ne "ARRAY")
3403
push(@tmp,$queries);
3406
foreach $query (@$queries)
3408
printf "query1: %-80.80s ...(%d - %d)\n",$query,
3409
length($query),$retry_limit if ($opt_log_all_queries);
3410
print LOG "$query;\n" if ($opt_log);
3411
$safe_query_log .= "< $query\n";
3412
if (length($query) > $query_size)
3415
$safe_query_log .= "Query is too long\n";
3420
for ($retry=0; $retry < $retry_limit ; $retry++)
3422
if (! ($sth=$dbh->prepare($query)))
3424
print_query($query);
3425
$safe_query_log .= "> couldn't prepare:". $dbh->errstr. "\n";
3426
$retry=100 if (!$server->abort_if_fatal_error());
3427
# Force a reconnect because of Access drop table bug!
3428
if ($retry == $retry_limit-2)
3430
print "Forcing disconnect to retry query\n" if ($opt_debug);
3431
$dbh->disconnect || warn $dbh->errstr;
3433
check_connect(); # Check that server is still up
3437
if (!$sth->execute())
3439
print_query($query);
3440
$safe_query_log .= "> execute error:". $dbh->errstr. "\n";
3441
$retry=100 if (!$server->abort_if_fatal_error());
3442
# Force a reconnect because of Access drop table bug!
3443
if ($retry == $retry_limit-2)
3445
print "Forcing disconnect to retry query\n" if ($opt_debug);
3446
$dbh->disconnect || warn $dbh->errstr;
3448
check_connect(); # Check that server is still up
3452
$retry = $retry_limit;
3454
$safe_query_log .= "> OK\n";
3459
$ok=0 if (!$retry_ok);
3460
if ($query =~ /create/i && $server->reconnect_on_errors())
3462
print "Forcing disconnect to retry query\n" if ($opt_debug);
3463
$dbh->disconnect || warn $dbh->errstr;
3464
$dbh=safe_connect();
3470
sub check_reserved_words
3474
my $answer, $prompt, $config, $keyword_type;
3476
my @keywords_ext = ( "ansi-92/99", "ansi92", "ansi99", "extra");
3478
my %reserved_words = (
3479
'ABSOLUTE' => 0, 'ACTION' => 0, 'ADD' => 0,
3480
'AFTER' => 0, 'ALIAS' => 0, 'ALL' => 0,
3481
'ALLOCATE' => 0, 'ALTER' => 0, 'AND' => 0,
3482
'ANY' => 0, 'ARE' => 0, 'AS' => 0,
3483
'ASC' => 0, 'ASSERTION' => 0, 'AT' => 0,
3484
'AUTHORIZATION' => 0, 'BEFORE' => 0, 'BEGIN' => 0,
3485
'BIT' => 0, 'BOOLEAN' => 0, 'BOTH' => 0,
3486
'BREADTH' => 0, 'BY' => 0, 'CALL' => 0,
3487
'CASCADE' => 0, 'CASCADED' => 0, 'CASE' => 0,
3488
'CAST' => 0, 'CATALOG' => 0, 'CHAR' => 0,
3489
'CHARACTER' => 0, 'CHECK' => 0, 'CLOSE' => 0,
3490
'COLLATE' => 0, 'COLLATION' => 0, 'COLUMN' => 0,
3491
'COMMIT' => 0, 'COMPLETION' => 0, 'CONNECT' => 0,
3492
'CONNECTION' => 0, 'CONSTRAINT' => 0, 'CONSTRAINTS' => 0,
3493
'CONTINUE' => 0, 'CORRESPONDING' => 0, 'CREATE' => 0,
3494
'CROSS' => 0, 'CURRENT' => 0, 'CURRENT_DATE' => 0,
3495
'CURRENT_TIME' => 0,'CURRENT_TIMESTAMP' => 0, 'CURRENT_USER' => 0,
3496
'CURSOR' => 0, 'CYCLE' => 0, 'DATA' => 0,
3497
'DATE' => 0, 'DAY' => 0, 'DEALLOCATE' => 0,
3498
'DEC' => 0, 'DECIMAL' => 0, 'DECLARE' => 0,
3499
'DEFAULT' => 0, 'DEFERRABLE' => 0, 'DEFERRED' => 0,
3500
'DELETE' => 0, 'DEPTH' => 0, 'DESC' => 0,
3501
'DESCRIBE' => 0, 'DESCRIPTOR' => 0, 'DIAGNOSTICS' => 0,
3502
'DICTIONARY' => 0, 'DISCONNECT' => 0, 'DISTINCT' => 0,
3503
'DOMAIN' => 0, 'DOUBLE' => 0, 'DROP' => 0,
3504
'EACH' => 0, 'ELSE' => 0, 'ELSEIF' => 0,
3505
'END' => 0, 'END-EXEC' => 0, 'EQUALS' => 0,
3506
'ESCAPE' => 0, 'EXCEPT' => 0, 'EXCEPTION' => 0,
3507
'EXEC' => 0, 'EXECUTE' => 0, 'EXTERNAL' => 0,
3508
'FALSE' => 0, 'FETCH' => 0, 'FIRST' => 0,
3509
'FLOAT' => 0, 'FOR' => 0, 'FOREIGN' => 0,
3510
'FOUND' => 0, 'FROM' => 0, 'FULL' => 0,
3511
'GENERAL' => 0, 'GET' => 0, 'GLOBAL' => 0,
3512
'GO' => 0, 'GOTO' => 0, 'GRANT' => 0,
3513
'GROUP' => 0, 'HAVING' => 0, 'HOUR' => 0,
3514
'IDENTITY' => 0, 'IF' => 0, 'IGNORE' => 0,
3515
'IMMEDIATE' => 0, 'IN' => 0, 'INDICATOR' => 0,
3516
'INITIALLY' => 0, 'INNER' => 0, 'INPUT' => 0,
3517
'INSERT' => 0, 'INT' => 0, 'INTEGER' => 0,
3518
'INTERSECT' => 0, 'INTERVAL' => 0, 'INTO' => 0,
3519
'IS' => 0, 'ISOLATION' => 0, 'JOIN' => 0,
3520
'KEY' => 0, 'LANGUAGE' => 0, 'LAST' => 0,
3521
'LEADING' => 0, 'LEAVE' => 0, 'LEFT' => 0,
3522
'LESS' => 0, 'LEVEL' => 0, 'LIKE' => 0,
3523
'LIMIT' => 0, 'LOCAL' => 0, 'LOOP' => 0,
3524
'MATCH' => 0, 'MINUTE' => 0, 'MODIFY' => 0,
3525
'MODULE' => 0, 'MONTH' => 0, 'NAMES' => 0,
3526
'NATIONAL' => 0, 'NATURAL' => 0, 'NCHAR' => 0,
3527
'NEW' => 0, 'NEXT' => 0, 'NO' => 0,
3528
'NONE' => 0, 'NOT' => 0, 'NULL' => 0,
3529
'NUMERIC' => 0, 'OBJECT' => 0, 'OF' => 0,
3530
'OFF' => 0, 'OLD' => 0, 'ON' => 0,
3531
'ONLY' => 0, 'OPEN' => 0, 'OPERATION' => 0,
3532
'OPTION' => 0, 'OR' => 0, 'ORDER' => 0,
3533
'OUTER' => 0, 'OUTPUT' => 0, 'PAD' => 0,
3534
'PARAMETERS' => 0, 'PARTIAL' => 0, 'PRECISION' => 0,
3535
'PREORDER' => 0, 'PREPARE' => 0, 'PRESERVE' => 0,
3536
'PRIMARY' => 0, 'PRIOR' => 0, 'PRIVILEGES' => 0,
3537
'PROCEDURE' => 0, 'PUBLIC' => 0, 'READ' => 0,
3538
'REAL' => 0, 'RECURSIVE' => 0, 'REF' => 0,
3539
'REFERENCES' => 0, 'REFERENCING' => 0, 'RELATIVE' => 0,
3540
'RESIGNAL' => 0, 'RESTRICT' => 0, 'RETURN' => 0,
3541
'RETURNS' => 0, 'REVOKE' => 0, 'RIGHT' => 0,
3542
'ROLE' => 0, 'ROLLBACK' => 0, 'ROUTINE' => 0,
3543
'ROW' => 0, 'ROWS' => 0, 'SAVEPOINT' => 0,
3544
'SCHEMA' => 0, 'SCROLL' => 0, 'SEARCH' => 0,
3545
'SECOND' => 0, 'SECTION' => 0, 'SELECT' => 0,
3546
'SEQUENCE' => 0, 'SESSION' => 0, 'SESSION_USER' => 0,
3547
'SET' => 0, 'SIGNAL' => 0, 'SIZE' => 0,
3548
'SMALLINT' => 0, 'SOME' => 0, 'SPACE' => 0,
3549
'SQL' => 0, 'SQLEXCEPTION' => 0, 'SQLSTATE' => 0,
3550
'SQLWARNING' => 0, 'STRUCTURE' => 0, 'SYSTEM_USER' => 0,
3551
'TABLE' => 0, 'TEMPORARY' => 0, 'THEN' => 0,
3552
'TIME' => 0, 'TIMESTAMP' => 0, 'TIMEZONE_HOUR' => 0,
3553
'TIMEZONE_MINUTE' => 0, 'TO' => 0, 'TRAILING' => 0,
3554
'TRANSACTION' => 0, 'TRANSLATION' => 0, 'TRIGGER' => 0,
3555
'TRUE' => 0, 'UNDER' => 0, 'UNION' => 0,
3556
'UNIQUE' => 0, 'UNKNOWN' => 0, 'UPDATE' => 0,
3557
'USAGE' => 0, 'USER' => 0, 'USING' => 0,
3558
'VALUE' => 0, 'VALUES' => 0, 'VARCHAR' => 0,
3559
'VARIABLE' => 0, 'VARYING' => 0, 'VIEW' => 0,
3560
'WHEN' => 0, 'WHENEVER' => 0, 'WHERE' => 0,
3561
'WHILE' => 0, 'WITH' => 0, 'WITHOUT' => 0,
3562
'WORK' => 0, 'WRITE' => 0, 'YEAR' => 0,
3565
'ASYNC' => 1, 'AVG' => 1, 'BETWEEN' => 1,
3566
'BIT_LENGTH' => 1,'CHARACTER_LENGTH' => 1, 'CHAR_LENGTH' => 1,
3567
'COALESCE' => 1, 'CONVERT' => 1, 'COUNT' => 1,
3568
'EXISTS' => 1, 'EXTRACT' => 1, 'INSENSITIVE' => 1,
3569
'LOWER' => 1, 'MAX' => 1, 'MIN' => 1,
3570
'NULLIF' => 1, 'OCTET_LENGTH' => 1, 'OID' => 1,
3571
'OPERATORS' => 1, 'OTHERS' => 1, 'OVERLAPS' => 1,
3572
'PENDANT' => 1, 'POSITION' => 1, 'PRIVATE' => 1,
3573
'PROTECTED' => 1, 'REPLACE' => 1, 'SENSITIVE' => 1,
3574
'SIMILAR' => 1, 'SQLCODE' => 1, 'SQLERROR' => 1,
3575
'SUBSTRING' => 1, 'SUM' => 1, 'TEST' => 1,
3576
'THERE' => 1, 'TRANSLATE' => 1, 'TRIM' => 1,
3577
'TYPE' => 1, 'UPPER' => 1, 'VIRTUAL' => 1,
3578
'VISIBLE' => 1, 'WAIT' => 1,
3580
'ADMIN' => 2, 'AGGREGATE' => 2, 'ARRAY' => 2,
3581
'BINARY' => 2, 'BLOB' => 2, 'CLASS' => 2,
3582
'CLOB' => 2, 'CONDITION' => 2, 'CONSTRUCTOR' => 2,
3583
'CONTAINS' => 2, 'CUBE' => 2, 'CURRENT_PATH' => 2,
3584
'CURRENT_ROLE' => 2, 'DATALINK' => 2, 'DEREF' => 2,
3585
'DESTROY' => 2, 'DESTRUCTOR' => 2, 'DETERMINISTIC' => 2,
3586
'DO' => 2, 'DYNAMIC' => 2, 'EVERY' => 2,
3587
'EXIT' => 2, 'EXPAND' => 2, 'EXPANDING' => 2,
3588
'FREE' => 2, 'FUNCTION' => 2, 'GROUPING' => 2,
3589
'HANDLER' => 2, 'HAST' => 2, 'HOST' => 2,
3590
'INITIALIZE' => 2, 'INOUT' => 2, 'ITERATE' => 2,
3591
'LARGE' => 2, 'LATERAL' => 2, 'LOCALTIME' => 2,
3592
'LOCALTIMESTAMP' => 2, 'LOCATOR' => 2, 'MEETS' => 2,
3593
'MODIFIES' => 2, 'NCLOB' => 2, 'NORMALIZE' => 2,
3594
'ORDINALITY' => 2, 'OUT' => 2, 'PARAMETER' => 2,
3595
'PATH' => 2, 'PERIOD' => 2, 'POSTFIX' => 2,
3596
'PRECEDES' => 2, 'PREFIX' => 2, 'READS' => 2,
3597
'REDO' => 2, 'REPEAT' => 2, 'RESULT' => 2,
3598
'ROLLUP' => 2, 'SETS' => 2, 'SPECIFIC' => 2,
3599
'SPECIFICTYPE' => 2, 'START' => 2, 'STATE' => 2,
3600
'STATIC' => 2, 'SUCCEEDS' => 2, 'TERMINATE' => 2,
3601
'THAN' => 2, 'TREAT' => 2, 'UNDO' => 2,
3604
'ACCESS' => 3, 'ANALYZE' => 3, 'AUDIT' => 3,
3605
'AUTO_INCREMENT' => 3, 'BACKUP' => 3, 'BDB' => 3,
3606
'BERKELEYDB' => 3, 'BIGINT' => 3, 'BREAK' => 3,
3607
'BROWSE' => 3, 'BTREE' => 3, 'BULK' => 3,
3608
'CHANGE' => 3, 'CHECKPOINT' => 3, 'CLUSTER' => 3,
3609
'CLUSTERED' => 3, 'COLUMNS' => 3, 'COMMENT' => 3,
3610
'COMPRESS' => 3, 'COMPUTE' => 3, 'CONTAINSTABLE' => 3,
3611
'DATABASE' => 3, 'DATABASES' => 3, 'DAY_HOUR' => 3,
3612
'DAY_MINUTE' => 3, 'DAY_SECOND' => 3, 'DBCC' => 3,
3613
'DELAYED' => 3, 'DENY' => 3, 'DISK' => 3,
3614
'DISTINCTROW' => 3, 'DISTRIBUTED' => 3, 'DUMMY' => 3,
3615
'DUMP' => 3, 'ENCLOSED' => 3, 'ERRLVL' => 3,
3616
'ERRORS' => 3, 'ESCAPED' => 3, 'EXCLUSIVE' => 3,
3617
'EXPLAIN' => 3, 'FIELDS' => 3, 'FILE' => 3,
3618
'FILLFACTOR' => 3, 'FREETEXT' => 3, 'FREETEXTTABLE' => 3,
3619
'FULLTEXT' => 3, 'GEOMETRY' => 3, 'HASH' => 3,
3620
'HIGH_PRIORITY' => 3, 'HOLDLOCK' => 3, 'HOUR_MINUTE' => 3,
3621
'HOUR_SECOND' => 3, 'IDENTIFIED' => 3, 'IDENTITYCOL' => 3,
3622
'IDENTITY_INSERT' => 3, 'INCREMENT' => 3, 'INDEX' => 3,
3623
'INFILE' => 3, 'INITIAL' => 3, 'INNODB' => 3,
3624
'KEYS' => 3, 'KILL' => 3, 'LINENO' => 3,
3625
'LINES' => 3, 'LOAD' => 3, 'LOCK' => 3,
3626
'LONG' => 3, 'LONGBLOB' => 3, 'LONGTEXT' => 3,
3627
'LOW_PRIORITY' => 3, 'MASTER_SERVER_ID' => 3, 'MAXEXTENTS' => 3,
3628
'MEDIUMBLOB' => 3, 'MEDIUMINT' => 3, 'MEDIUMTEXT' => 3,
3629
'MIDDLEINT' => 3, 'MINUS' => 3, 'MINUTE_SECOND' => 3,
3630
'MLSLABEL' => 3, 'MODE' => 3, 'MRG_MYISAM' => 3,
3631
'NOAUDIT' => 3, 'NOCHECK' => 3, 'NOCOMPRESS' => 3,
3632
'NONCLUSTERED' => 3, 'NOWAIT' => 3, 'NUMBER' => 3,
3633
'OFFLINE' => 3, 'OFFSETS' => 3, 'ONLINE' => 3,
3634
'OPENDATASOURCE' => 3, 'OPENQUERY' => 3, 'OPENROWSET' => 3,
3635
'OPENXML' => 3, 'OPTIMIZE' => 3, 'OPTIONALLY' => 3,
3636
'OUTFILE' => 3, 'OVER' => 3, 'PCTFREE' => 3,
3637
'PERCENT' => 3, 'PLAN' => 3, 'PRINT' => 3,
3638
'PROC' => 3, 'PURGE' => 3, 'RAISERROR' => 3,
3639
'RAW' => 3, 'READTEXT' => 3, 'RECONFIGURE' => 3,
3640
'REGEXP' => 3, 'RENAME' => 3, 'REPLICATION' => 3,
3641
'REQUIRE' => 3, 'RESOURCE' => 3, 'RESTORE' => 3,
3642
'RLIKE' => 3, 'ROWCOUNT' => 3, 'ROWGUIDCOL' => 3,
3643
'ROWID' => 3, 'ROWNUM' => 3, 'RTREE' => 3,
3644
'RULE' => 3, 'SAVE' => 3, 'SETUSER' => 3,
3645
'SHARE' => 3, 'SHOW' => 3, 'SHUTDOWN' => 3,
3646
'SONAME' => 3, 'SPATIAL' => 3, 'SQL_BIG_RESULT' => 3,
3647
'SQL_CALC_FOUND_ROWS' => 3,'SQL_SMALL_RESULT' => 3, 'SSL' => 3,
3648
'STARTING' => 3, 'STATISTICS' => 3, 'STRAIGHT_JOIN' => 3,
3649
'STRIPED' => 3, 'SUCCESSFUL' => 3, 'SYNONYM' => 3,
3650
'SYSDATE' => 3, 'TABLES' => 3, 'TERMINATED' => 3,
3651
'TEXTSIZE' => 3, 'TINYBLOB' => 3, 'TINYINT' => 3,
3652
'TINYTEXT' => 3, 'TOP' => 3, 'TRAN' => 3,
3653
'TRUNCATE' => 3, 'TSEQUAL' => 3, 'TYPES' => 3,
3654
'UID' => 3, 'UNLOCK' => 3, 'UNSIGNED' => 3,
3655
'UPDATETEXT' => 3, 'USE' => 3, 'USER_RESOURCES' => 3,
3656
'VALIDATE' => 3, 'VARBINARY' => 3, 'VARCHAR2' => 3,
3657
'WAITFOR' => 3, 'WARNINGS' => 3, 'WRITETEXT' => 3,
3658
'XOR' => 3, 'YEAR_MONTH' => 3, 'ZEROFILL' => 3
3662
safe_query("drop table crash_me10 $drop_attr");
3664
foreach my $keyword (sort {$a cmp $b} keys %reserved_words)
3666
$keyword_type= $reserved_words{$keyword};
3668
$prompt= "Keyword ".$keyword;
3669
$config= "reserved_word_".$keywords_ext[$keyword_type]."_".lc($keyword);
3671
report_fail($prompt,$config,
3672
"create table crash_me10 ($keyword int not null)",
3673
"drop table crash_me10 $drop_attr"
3679
# Do a query on a query package object.
3684
my($object,$limit)=@_;
3685
my ($query,$result,$retry,$sth);
3687
$query=$object->query($limit);
3688
$result=safe_query($query);
3694
if (defined($query=$object->check_query()))
3696
for ($retry=0 ; $retry < $retry_limit ; $retry++)
3698
printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
3699
print LOG "$query;\n" if ($opt_log);
3700
if (($sth= $dbh->prepare($query)))
3704
$result= $object->check($sth);
3709
print_query($query);
3714
print_query($query);
3716
$retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
3717
if ($retry == $retry_limit-2)
3719
print "Forcing discoennect to retry query\n" if ($opt_debug);
3720
$dbh->disconnect || warn $dbh->errstr;
3722
check_connect($object); # Check that server is still up
3724
$result=0; # Query failed
3727
return $result; # Server couldn't handle the query
3733
my ($prompt,$limit,@queries)=@_;
3735
if (!defined($limits{$limit}))
3737
my $queries_result = safe_query(\@queries);
3738
add_log($limit, $safe_query_log);
3740
if ( $queries_result) {
3741
$report_result= "yes";
3742
add_log($limit,"As far as all queries returned OK, result is YES");
3744
$report_result= "no";
3745
add_log($limit,"As far as some queries didnt return OK, result is NO");
3747
save_config_data($limit,$report_result,$prompt);
3749
print "$limits{$limit}\n";
3750
return $limits{$limit} ne "no";
3755
my ($prompt,$limit,@queries)=@_;
3757
if (!defined($limits{$limit}))
3759
my $queries_result = safe_query(\@queries);
3760
add_log($limit, $safe_query_log);
3762
if ( $queries_result) {
3763
$report_result= "no";
3764
add_log($limit,"As far as all queries returned OK, result is NO");
3766
$report_result= "yes";
3767
add_log($limit,"As far as some queries didnt return OK, result is YES");
3769
save_config_data($limit,$report_result,$prompt);
3771
print "$limits{$limit}\n";
3772
return $limits{$limit} ne "no";
3776
# Return true if one of the queries is ok
3780
my ($prompt,$limit,$queries)=@_;
3781
my ($query,$res,$result);
3783
if (!defined($limits{$limit}))
3785
save_incomplete($limit,$prompt);
3787
foreach $query (@$queries)
3789
if (safe_query_l($limit,$query->[0]))
3791
$result= $query->[1];
3795
save_config_data($limit,$result,$prompt);
3797
print "$limits{$limit}\n";
3798
return $limits{$limit} ne "no";
3802
# Execute query and save result as limit value.
3806
my ($prompt,$limit,$query)=@_;
3809
if (!defined($limits{$limit}))
3811
save_incomplete($limit,$prompt);
3812
$error=safe_query_result($query,"1",2);
3813
add_log($limit,$safe_query_result_log);
3814
save_config_data($limit,$error ? "not supported" :$last_result,$prompt);
3816
print "$limits{$limit}\n";
3817
return $limits{$limit} ne "not supported";
3822
my ($limit,$queries,$check,$clear)=@_;
3823
if (!defined($limits{$limit}))
3825
save_incomplete($limit,$prompt);
3826
eval {undef($dbh->{AutoCommit})};
3829
if (safe_query(\@$queries))
3832
$dbh->{AutoCommit} = 1;
3833
if (safe_query_result($check,"","")) {
3834
add_log($limit,$safe_query_result_log);
3835
save_config_data($limit,"yes",$limit);
3839
add_log($limit,$safe_query_log);
3840
save_config_data($limit,"error",$limit);
3842
$dbh->{AutoCommit} = 1;
3846
add_log($limit,"Couldnt undef autocommit ?? ");
3847
save_config_data($limit,"no",$limit);
3851
return $limits{$limit} ne "yes";
3856
my ($limit,$queries,$check,$clear)=@_;
3857
if (!defined($limits{$limit}))
3859
save_incomplete($limit,$prompt);
3860
eval {undef($dbh->{AutoCommit})};
3863
if (safe_query(\@$queries))
3865
add_log($limit,$safe_query_log);
3868
$dbh->{AutoCommit} = 1;
3869
if (safe_query($check)) {
3870
add_log($limit,$safe_query_log);
3871
save_config_data($limit,"no",$limit);
3873
add_log($limit,$safe_query_log);
3874
save_config_data($limit,"yes",$limit);
3878
add_log($limit,$safe_query_log);
3879
save_config_data($limit,"error",$limit);
3884
add_log($limit,'Couldnt undef Autocommit??');
3885
save_config_data($limit,"error",$limit);
3889
$dbh->{AutoCommit} = 1;
3890
return $limits{$limit} ne "yes";
3894
sub check_and_report
3896
my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
3899
$function=0 if (!defined($function));
3901
print "$prompt: " if (!defined($skip_prompt));
3902
if (!defined($limits{$limit}))
3904
save_incomplete($limit,$prompt);
3905
$tmp=1-safe_query(\@$pre);
3906
add_log($limit,$safe_query_log);
3909
$tmp=safe_query_result($query,$answer,$string_type) ;
3910
add_log($limit,$safe_query_result_log);
3912
safe_query(\@$post);
3913
add_log($limit,$safe_query_log);
3914
delete $limits{$limit};
3915
if ($function == 3) # Report error as 'no'.
3920
if ($function == 0 ||
3921
$tmp != 0 && $function == 1 ||
3922
$tmp == 0 && $function== 2)
3924
save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
3926
print "$limits{$limit}\n";
3927
return $function == 0 ? $limits{$limit} eq "yes" : 0;
3929
return 1; # more things to check
3931
print "$limits{$limit}\n";
3932
return 0 if ($function);
3933
return $limits{$limit} eq "yes";
3939
my ($prompt,$limit,@tests)=@_;
3940
my ($tmp,$test,$type);
3944
if (!defined($limits{$limit}))
3946
save_incomplete($limit,$prompt);
3947
$type="no"; # Not supported
3948
foreach $test (@tests)
3950
my $tmp_type= shift(@$test);
3951
if (safe_query_l($limit,\@$test))
3958
save_config_data($limit, $type, $prompt);
3960
print "$limits{$limit}\n";
3961
return $limits{$limit} ne "no";
3965
# Just execute the query and check values; Returns 1 if ok
3968
sub execute_and_check
3970
my ($key,$pre,$query,$post,$answer,$string_type)=@_;
3973
$tmp=safe_query_l($key,\@$pre);
3975
$tmp=safe_query_result_l($key,$query,$answer,$string_type) == 0 if ($tmp);
3976
safe_query_l($key,\@$post);
3981
# returns 0 if ok, 1 if error, -1 if wrong answer
3982
# Sets $last_result to value of query
3983
sub safe_query_result_l{
3984
my ($key,$query,$answer,$result_type)=@_;
3985
my $r = safe_query_result($query,$answer,$result_type);
3986
add_log($key,$safe_query_result_log);
3990
sub safe_query_result
3992
# result type can be
3993
# 8 (must be empty), 2 (Any value), 0 (number)
3994
# 1 (char, endspaces can differ), 3 (exact char), 4 (NULL)
3995
# 5 (char with prefix), 6 (exact, errors are ignored)
3996
# 7 (array of numbers)
3997
my ($query,$answer,$result_type)=@_;
3998
my ($sth,$row,$result,$retry);
3999
undef($last_result);
4000
$safe_query_result_log="";
4002
printf "\nquery3: %-80.80s\n",$query if ($opt_log_all_queries);
4003
print LOG "$query;\n" if ($opt_log);
4004
$safe_query_result_log="<".$query."\n";
4006
for ($retry=0; $retry < $retry_limit ; $retry++)
4008
if (!($sth=$dbh->prepare($query)))
4010
print_query($query);
4011
$safe_query_result_log .= "> prepare failed:".$dbh->errstr."\n";
4013
if ($server->abort_if_fatal_error())
4015
check_connect(); # Check that server is still up
4018
check_connect(); # Check that server is still up
4023
print_query($query);
4024
$safe_query_result_log .= "> execute failed:".$dbh->errstr."\n";
4025
if ($server->abort_if_fatal_error())
4027
check_connect(); # Check that server is still up
4030
check_connect(); # Check that server is still up
4038
if (!($row=$sth->fetchrow_arrayref))
4040
print "\nquery: $query didn't return any result\n" if ($opt_debug);
4041
$safe_query_result_log .= "> didn't return any result:".$dbh->errstr."\n";
4043
return ($result_type == 8) ? 0 : 1;
4045
if ($result_type == 8)
4051
$last_result= $row->[0]; # Save for report_result;
4052
$safe_query_result_log .= ">".$last_result."\n";
4054
# if ($result_type == 2) We accept any return value as answer
4056
if ($result_type == 0) # Compare numbers
4058
$row->[0] =~ s/,/./; # Fix if ',' is used instead of '.'
4059
if ($row->[0] != $answer && (abs($row->[0]- $answer)/
4060
(abs($row->[0]) + abs($answer))) > 0.01)
4063
$safe_query_result_log .=
4064
"We expected '$answer' but got '$last_result' \n";
4067
elsif ($result_type == 1) # Compare where end space may differ
4069
$row->[0] =~ s/\s+$//;
4070
if ($row->[0] ne $answer)
4073
$safe_query_result_log .=
4074
"We expected '$answer' but got '$last_result' \n";
4077
elsif ($result_type == 3) # This should be a exact match
4079
if ($row->[0] ne $answer)
4082
$safe_query_result_log .=
4083
"We expected '$answer' but got '$last_result' \n";
4086
elsif ($result_type == 4) # If results should be NULL
4088
if (defined($row->[0]))
4091
$safe_query_result_log .=
4092
"We expected NULL but got '$last_result' \n";
4095
elsif ($result_type == 5) # Result should have given prefix
4097
if (length($row->[0]) < length($answer) &&
4098
substr($row->[0],1,length($answer)) ne $answer)
4101
$safe_query_result_log .=
4102
"Result must have prefix '$answer', but '$last_result' \n";
4105
elsif ($result_type == 6) # Exact match but ignore errors
4107
if ($row->[0] ne $answer)
4109
$safe_query_result_log .=
4110
"We expected '$answer' but got '$last_result' \n";
4113
elsif ($result_type == 7) # Compare against array of numbers
4115
if ($row->[0] != $answer->[0])
4117
$safe_query_result_log .= "must be '$answer->[0]' \n";
4124
while (($row=$sth->fetchrow_arrayref))
4126
$safe_query_result_log .= ">$row\n";
4128
$value=shift(@$answer);
4129
if (!defined($value))
4131
print "\nquery: $query returned to many results\n"
4133
$safe_query_result_log .= "It returned to many results \n";
4137
if ($row->[0] != $value)
4139
$safe_query_result_log .= "Must return $value here \n";
4144
if ($#$answer != -1)
4146
print "\nquery: $query returned too few results\n"
4148
$safe_query_result_log .= "It returned too few results \n";
4154
print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
4155
if ($opt_debug && $result && $result_type != 7);
4160
# Find limit using binary search. This is a weighed binary search that
4161
# will prefere lower limits to get the server to crash as
4162
# few times as possible
4167
my ($prompt,$limit,$query)=@_;
4168
my ($first,$end,$i,$tmp,@tmp_array, $queries);
4170
if (defined($end=$limits{$limit}))
4172
print "$end (cache)\n";
4175
save_incomplete($limit,$prompt);
4176
add_log($limit,"We are trying (example with N=5):");
4177
$queries = $query->query(5);
4178
if (ref($queries) ne "ARRAY")
4180
push(@tmp_array,$queries);
4181
$queries= \@tmp_array;
4183
foreach $tmp (@$queries)
4184
{ add_log($limit,repr_query($tmp)); }
4186
if (defined($queries = $query->check_query()))
4188
if (ref($queries) ne "ARRAY")
4191
push(@tmp_array,$queries);
4192
$queries= \@tmp_array;
4194
foreach $tmp (@$queries)
4195
{ add_log($limit,repr_query($tmp)); }
4197
if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
4199
if (!safe_query_l($limit,$query->{'init'}))
4206
if (!limit_query($query,1)) # This must work
4208
print "\nMaybe fatal error: Can't check '$prompt' for limit=1\n".
4209
"error: $last_error\n";
4214
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
4216
if (defined($end=$limits{'restart'}{'tohigh'}))
4219
print "\nRestarting this with low limit: $first and high limit: $end\n";
4220
delete $limits{'restart'};
4221
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
4225
$end= $query->max_limit();
4226
$i=int(($end+$first)/2);
4229
unless(limit_query($query,0+$end)) {
4230
while ($first < $end)
4232
print "." if ($opt_debug);
4233
save_config_data("restart",$i,"") if ($opt_restart);
4234
if (limit_query($query,$i))
4237
$log_str .= " $i:OK";
4238
$i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
4243
$log_str .= " $i:FAIL";
4244
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
4248
$end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
4249
if ($end >= $query->{'max_limit'} &&
4250
substr($query->{'max_limit'},0,1) eq '+')
4252
$end= $query->{'max_limit'};
4255
add_log($limit,$log_str);
4256
save_config_data($limit,$end,$prompt);
4257
delete $limits{'restart'};
4262
# Check that the query works!
4269
if (!safe_query($query))
4271
$query=join("; ",@$query) if (ref($query) eq "ARRAY");
4272
print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
4278
sub read_config_data
4280
my ($key,$limit,$prompt);
4281
if (-e $opt_config_file)
4283
open(CONFIG_FILE,"+<$opt_config_file") ||
4284
die "Can't open configure file $opt_config_file\n";
4285
print "Reading old values from cache: $opt_config_file\n";
4289
open(CONFIG_FILE,"+>>$opt_config_file") ||
4290
die "Can't create configure file $opt_config_file: $!\n";
4295
while (<CONFIG_FILE>)
4298
if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
4300
$key=$1; $limit=$2 ; $prompt=$3;
4301
if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
4303
if ($key !~ /restart/i)
4305
$limits{$key}=$limit eq "null"? undef : $limit;
4306
$prompts{$key}=length($prompt) ? substr($prompt,2) : "";
4308
delete $limits{'restart'};
4313
if ($limit > $limits{'restart'}{'tohigh'})
4315
$limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
4317
$limits{'restart'}{'tohigh'} = $limit;
4321
elsif (/\s*###(.*)$/) # log line
4323
# add log line for previously read key
4324
$log{$last_read} .= "$1\n";
4326
elsif (!/^\s*$/ && !/^\#/)
4328
die "Wrong config row: $_\n";
4334
sub save_config_data
4336
my ($key,$limit,$prompt)=@_;
4337
$prompts{$key}=$prompt;
4338
return if (defined($limits{$key}) && $limits{$key} eq $limit);
4339
if (!defined($limit) || $limit eq "")
4341
# die "Undefined limit for $key\n";
4344
print CONFIG_FILE "$key=$limit\t# $prompt\n";
4345
$limits{$key}=$limit;
4347
# now write log lines (immediatelly after limits)
4349
my $last_line_was_empty=0;
4350
foreach $line (split /\n/, $log{$key})
4352
print CONFIG_FILE " ###$line\n"
4353
unless ( ($last_line_was_empty eq 1)
4354
&& ($line =~ /^\s+$/) );
4355
$last_line_was_empty= ($line =~ /^\s+$/)?1:0;
4358
if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
4359
($limits{'operating_system'} =~ /NT/))
4361
# If perl crashes in windows, everything is lost (Wonder why? :)
4363
open(CONFIG_FILE,"+>>$opt_config_file") ||
4364
die "Can't reopen configure file $opt_config_file: $!\n";
4372
$log{$key} .= $line . "\n" if ($opt_verbose);;
4375
sub save_all_config_data
4379
return if (!$limit_changed);
4380
open(CONFIG_FILE,">$opt_config_file") ||
4381
die "Can't create configure file $opt_config_file: $!\n";
4385
delete $limits{'restart'};
4388
"#This file is automaticly generated by crash-me $version\n\n";
4389
foreach $key (sort keys %limits)
4391
$tmp="$key=$limits{$key}";
4392
print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
4393
"# $prompts{$key}\n";
4395
my $last_line_was_empty=0;
4396
foreach $line (split /\n/, $log{$key})
4398
print CONFIG_FILE " ###$line\n" unless
4399
( ($last_line_was_empty eq 1) && ($line =~ /^\s*$/));
4400
$last_line_was_empty= ($line =~ /^\s*$/)?1:0;
4407
# Save 'incomplete' in the limits file to be able to continue if
4408
# crash-me dies because of a bug in perl/DBI
4412
my ($limit,$prompt)= @_;
4413
save_config_data($limit,"incompleted",$prompt) if ($opt_restart);
4419
my ($sth,$limit)=@_;
4422
return 0 if (!($row=$sth->fetchrow_arrayref));
4423
return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
4431
for ($i=1 ; $i <= $#_; $i++)
4433
$min=$_[$i] if ($min > $_[$i]);
4441
return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
4442
return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
4443
return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
4448
# Returns a list of statements to create a table in a portable manner
4449
# but still utilizing features in the databases.
4454
my($table_name,$fields,$index,$extra) = @_;
4455
my($query,$nr,$parts,@queries,@index);
4457
$extra="" if (!defined($extra));
4459
$query="create table $table_name (";
4461
foreach $field (@$fields)
4463
$query.= $field . ',';
4465
foreach $index (@$index)
4467
$index =~ /\(([^\(]*)\)$/i;
4469
if ($index =~ /^primary key/)
4471
if ($limits{'primary_key_in_create'} eq 'yes')
4473
$query.= $index . ',';
4478
"create unique index ${table_name}_prim on $table_name ($parts)");
4481
elsif ($index =~ /^unique/)
4483
if ($limits{'unique_in_create'} eq 'yes')
4485
$query.= "unique ($parts),";
4491
"create unique index ${table_name}_$nr on $table_name ($parts)");
4497
if ($limits{'index_in_create'} eq 'yes')
4499
$query.= "index ($parts),";
4505
"create index ${table_name}_$nr on $table_name ($1)");
4510
$query.= ") $extra";
4511
unshift(@queries,$query);
4517
# This is used by some query packages to change:
4520
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
4521
# %f -> q1,q2,q3....
4522
# %F -> q1 integer,q2 integer,q3 integer....
4526
my ($query,$limit)=@_;
4529
return $query if !(defined($query));
4530
$query =~ s/%d/$limit/g;
4533
$repeat= 'a' x $limit;
4534
$query =~ s/%s/$repeat/g;
4538
$repeat= '1,' x $limit;
4540
$query =~ s/%v/$repeat/g;
4545
for ($i=1 ; $i <= $limit ; $i++)
4550
$query =~ s/%f/$repeat/g;
4555
for ($i=1 ; $i <= $limit ; $i++)
4557
$repeat.="q$i integer,";
4560
$query =~ s/%F/$repeat/g;
4567
# Different query packages
4570
package query_repeat;
4574
my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
4575
$max_limit, $check, $offset)=@_;
4577
if (defined($init) && $#$init != -1)
4579
$self->{'init'}=$init;
4581
$self->{'query'}=$query;
4582
$self->{'add1'}=$add1;
4583
$self->{'add_mid'}=$add_mid;
4584
$self->{'add'}=$add;
4585
$self->{'add_end'}=$add_end;
4586
$self->{'end_query'}=$end_query;
4587
$self->{'cleanup'}=$cleanup;
4588
$self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
4589
$self->{'check'}=$check;
4590
$self->{'offset'}=$offset;
4591
$self->{'printf'}= ($add =~ /%d/);
4597
my ($self,$limit)=@_;
4598
if (!$self->{'printf'})
4600
return $self->{'query'} . ($self->{'add'} x $limit) .
4601
($self->{'add_end'} x $limit) . $self->{'end_query'};
4603
my ($tmp,$tmp2,$tmp3,$i);
4604
$tmp=$self->{'query'};
4605
if ($self->{'add1'})
4607
for ($i=0; $i < $limit ; $i++)
4609
$tmp3 = $self->{'add1'};
4614
$tmp .= " ".$self->{'add_mid'};
4617
for ($i=0; $i < $limit ; $i++)
4619
$tmp2 = $self->{'add'};
4625
($self->{'add_end'} x $limit) . $self->{'end_query'});
4632
$tmp=int(($main::limits{"query_size"}-length($self->{'query'})
4633
-length($self->{'add_mid'})-length($self->{'end_query'}))/
4634
(length($self->{'add1'})+
4635
length($self->{'add'})+length($self->{'add_end'})));
4636
return main::min($self->{'max_limit'},$tmp);
4643
my($tmp,$statement);
4644
$tmp=$self->{'cleanup'};
4645
foreach $statement (@$tmp)
4647
main::safe_query($statement) if (defined($statement) && length($statement));
4654
my $check=$self->{'check'};
4655
return &$check($sth,$self->{'limit'}) if (defined($check));
4669
my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
4671
$self->{'query'}=$query;
4672
$self->{'end_query'}=$end_query;
4673
$self->{'cleanup'}=$cleanup;
4674
$self->{'max_limit'}=$max_limit;
4675
$self->{'check'}=$check;
4682
$self->{'limit'}=$i;
4683
return "$self->{'query'}$i$self->{'end_query'}";
4689
return $self->{'max_limit'};
4696
foreach $statement ($self->{'$cleanup'})
4698
main::safe_query($statement) if (defined($statement) && length($statement));
4706
my $check=$self->{'check'};
4707
return &$check($sth,$self->{'limit'}) if (defined($check));
4717
# This package is used when testing CREATE TABLE!
4720
package query_table;
4724
my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
4725
$cleanup, $max_limit, $offset)=@_;
4727
$self->{'query'}=$query;
4728
$self->{'add'}=$add;
4729
$self->{'end_query'}=$end_query;
4730
$self->{'extra_init'}=$extra_init;
4731
$self->{'safe_query'}=$safe_query;
4732
$self->{'check'}=$check;
4733
$self->{'cleanup'}=$cleanup;
4734
$self->{'max_limit'}=$max_limit;
4735
$self->{'offset'}=$offset;
4742
my ($self,$limit)=@_;
4743
$self->{'limit'}=$limit;
4744
$self->cleanup(); # Drop table before create
4746
my ($tmp,$tmp2,$i,$query,@res);
4747
$tmp =$self->{'query'};
4748
$tmp =~ s/%d/$limit/g;
4749
for ($i=1; $i <= $limit ; $i++)
4751
$tmp2 = $self->{'add'};
4755
push(@res,$tmp . $self->{'end_query'});
4756
$tmp=$self->{'extra_init'};
4757
foreach $query (@$tmp)
4759
push(@res,main::fix_query($query,$limit));
4768
return $self->{'max_limit'};
4775
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4781
my $check=$self->{'check'};
4782
return 0 if (!($row=$sth->fetchrow_arrayref));
4783
if (defined($check))
4785
return (defined($row->[0]) &&
4786
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4792
# Remove table before and after create table query
4797
main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
4801
# Package to do many queries with %d, and %s substitution
4808
my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
4811
$self->{'query'}=$query;
4812
$self->{'safe_query'}=$safe_query;
4813
$self->{'check'}=$check_result;
4814
$self->{'cleanup'}=$cleanup;
4815
$self->{'max_limit'}=$max_limit;
4816
$self->{'offset'}=$offset;
4817
$self->{'safe_cleanup'}=$safe_cleanup;
4824
my ($self,$limit)=@_;
4825
my ($queries,$query,@res);
4826
$self->{'limit'}=$limit;
4827
$self->cleanup() if (defined($self->{'safe_cleanup'}));
4828
$queries=$self->{'query'};
4829
foreach $query (@$queries)
4831
push(@res,main::fix_query($query,$limit));
4839
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4845
my($tmp,$statement);
4846
return if (!defined($self->{'cleanup'}));
4847
$tmp=$self->{'cleanup'};
4848
foreach $statement (@$tmp)
4850
if (defined($statement) && length($statement))
4852
main::safe_query(main::fix_query($statement,$self->{'limit'}));
4862
return 0 if (!($row=$sth->fetchrow_arrayref));
4863
$check=$self->{'check'};
4864
if (defined($check))
4866
return (defined($row->[0]) &&
4867
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4875
return $self->{'max_limit'};
4879
# Used to find max supported row length
4882
package query_row_length;
4886
my ($type,$create,$null,$drop,$max_limit)=@_;
4888
$self->{'table_name'}=$create;
4889
$self->{'null'}=$null;
4890
$self->{'cleanup'}=$drop;
4891
$self->{'max_limit'}=$max_limit;
4898
my ($self,$limit)=@_;
4899
my ($res,$values,$size,$length,$i);
4900
$self->{'limit'}=$limit;
4903
$size=main::min($main::limits{'max_char_size'},255);
4904
$size = 255 if (!$size); # Safety
4905
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4907
$res.= "q$i char($size) $self->{'null'},";
4908
$values.="'" . ('a' x $size) . "',";
4910
if ($length < $limit)
4912
$size=$limit-$length;
4913
$res.= "q$i char($size) $self->{'null'},";
4914
$values.="'" . ('a' x $size) . "',";
4918
return ["create table " . $self->{'table_name'} . " ($res)",
4919
"insert into " . $self->{'table_name'} . " values ($values)"];
4925
return $self->{'max_limit'};
4931
main::safe_query($self->{'cleanup'});
4946
# Used to find max supported index length
4949
package query_index_length;
4953
my ($type,$create,$drop,$max_limit)=@_;
4955
$self->{'create'}=$create;
4956
$self->{'cleanup'}=$drop;
4957
$self->{'max_limit'}=$max_limit;
4964
my ($self,$limit)=@_;
4965
my ($res,$size,$length,$i,$parts,$values);
4966
$self->{'limit'}=$limit;
4968
$res=$parts=$values="";
4969
$size=main::min($main::limits{'max_index_part_length'},
4970
$main::limits{'max_char_size'});
4971
$size=1 if ($size == 0); # Avoid infinite loop errors
4972
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4974
$res.= "q$i char($size) not null,";
4976
$values.= "'" . ('a' x $size) . "',";
4978
if ($length < $limit)
4980
$size=$limit-$length;
4981
$res.= "q$i char($size) not null,";
4983
$values.= "'" . ('a' x $size) . "',";
4988
if ($main::limits{'unique_in_create'} eq 'yes')
4990
return [$self->{'create'} . "($res,unique ($parts))",
4991
"insert into crash_q values($values)"];
4993
return [$self->{'create'} . "($res)",
4994
"create index crash_q_index on crash_q ($parts)",
4995
"insert into crash_q values($values)"];
5001
return $self->{'max_limit'};
5007
main::safe_query($self->{'cleanup'});
5024
# OID test instead of / in addition to _rowid
added
removed
sql-bench/crash-me.sh
