3
# Copyright (c) 2000-2006 MySQL AB, 2009 Sun Microsystems, Inc.
4
# Use is subject to license terms.
6
# This library is free software; you can redistribute it and/or
7
# modify it under the terms of the GNU Library General Public
8
# License as published by the Free Software Foundation; version 2
11
# This library is distributed in the hope that it will be useful,
12
# but WITHOUT ANY WARRANTY; without even the implied warranty of
13
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14
# Library General Public License for more details.
16
# You should have received a copy of the GNU Library General Public
17
# License along with this library; if not, write to the Free
18
# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
21
# Written by Monty for the TCX/Monty Program/Detron benchmark suite.
22
# Empress and PostgreSQL patches by Luuk de Boer
23
# Extensions for ANSI SQL and Mimer by Bengt Gunne
24
# Some additions and corrections by Matthias Urlich
26
# This programs tries to find all limits for a sql server
27
# It gets the name from what it does to most servers :)
29
# Be sure to use --help before running this!
31
# If you want to add support for another server, add a new package for the
32
# server in server-cfg. You only have to support the 'new' and 'version'
33
# functions. new doesn't need to have any limits if one doesn't want to
38
# CMT includes types and functions which are synonyms for other types
39
# and functions, including those in SQL9x. It should label those synonyms
40
# as such, and clarify ones such as "mediumint" with comments such as
41
# "3-byte int" or "same as xxx".
49
$pwd = cwd(); $pwd = "." if ($pwd eq '');
50
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
52
$opt_server="mysql"; $opt_host="localhost"; $opt_database="test";
54
$opt_user=$opt_password="";$opt_verbose=1;
55
$opt_debug=$opt_help=$opt_Information=$opt_restart=$opt_force=$opt_quick=0;
56
$opt_log_all_queries=$opt_fix_limit_file=$opt_batch_mode=$opt_version=0;
57
$opt_db_start_cmd=""; # the db server start command
58
$opt_check_server=0; # Check if server is alive before each query
59
$opt_sleep=10; # time to sleep while starting the db server
60
$limit_changed=0; # For configure file
63
$opt_comment=$opt_config_file=$opt_log_queries_to_file="";
64
$limits{'crash_me_safe'}='yes';
65
$prompts{'crash_me_safe'}='crash me safe';
66
$limits{'operating_system'}= machine();
67
$prompts{'operating_system'}='crash-me tested on';
70
GetOptions("Information","help","server=s","debug","user=s","password=s",
71
"database=s","restart","force","quick","log-all-queries","comment=s",
72
"host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","suffix=s",
73
"batch-mode","config-file=s","log-queries-to-file=s","check-server",
75
"verbose!" => \$opt_verbose) || usage();
76
usage() if ($opt_help || $opt_Information);
77
version() && exit(0) if ($opt_version);
79
$opt_suffix = '-'.$opt_suffix if (length($opt_suffix) != 0);
80
$opt_config_file = "$pwd/$opt_dir/$opt_server$opt_suffix.cfg"
81
if (length($opt_config_file) == 0);
82
$log_prefix=' ###'; # prefix for log lines in result file
84
$safe_query_result_log='';
89
if ($opt_fix_limit_file)
91
print "Fixing limit file for $opt_server\n";
94
save_all_config_data();
98
$server=get_server($opt_server,$opt_host,$opt_database);
99
$opt_server=$server->{'cmp_name'};
101
$|=1; # For debugging
103
print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
104
print "I hope you didn't have anything important running on this server....\n";
106
if ($limit_changed) # Must have been restarted
108
save_config_data('crash_me_safe','no',"crash me safe");
111
if (!$opt_force && !$opt_batch_mode)
117
print "Using --force. I assume you know what you are doing...\n";
121
save_config_data('crash_me_version',$version,"crash me version");
124
save_config_data('server_version',$server_version,"server version");
126
if (length($opt_comment))
128
save_config_data('user_comment',$opt_comment,"comment");
132
if (length($opt_log_queries_to_file))
134
open(LOG,">$opt_log_queries_to_file") ||
135
die "Can't open file $opt_log_queries_to_file\n";
140
# Set up some limits that's regared as unlimited
141
# We don't want to take up all resources from the server...
144
$max_connections="+1000"; # Number of simultaneous connections
145
$max_buffer_size="+16000000"; # size of communication buffer.
146
$max_string_size="+8000000"; # Enough for this test
147
$max_name_length="+512"; # Actually 256, but ...
148
$max_keys="+64"; # Probably too big.
149
$max_join_tables="+64"; # Probably too big.
150
$max_columns="+8192"; # Probably too big.
151
$max_row_length=$max_string_size;
152
$max_key_length="+8192"; # Big enough
153
$max_order_by="+64"; # Big enough
154
$max_expressions="+10000";
155
$max_big_expressions="+100";
156
$max_stacked_expressions="+2000";
157
$query_size=$max_buffer_size;
158
$longreadlen=16000000; # For retrieval buffer
162
# First do some checks that needed for the rest of the benchmark
164
use sigtrap; # Must be removed with perl5.005_2 on Win98
165
$SIG{PIPE} = 'IGNORE';
168
$problem_counter +=1;
169
if ($problem_counter >= 100) {
170
die("Too many problems, try to restart");
178
# Test if the database require RESTRICT/CASCADE after DROP TABLE
181
# Really remove the crash_me table
182
$prompt="drop table require cascade/restrict";
184
$dbh->do("drop table crash_me");
185
$dbh->do("drop table crash_me cascade");
186
if (!safe_query_l('drop_requires_cascade',
187
["create table crash_me (a integer not null)",
188
"drop table crash_me"]))
190
$dbh->do("drop table crash_me cascade");
191
if (safe_query_l('drop_requires_cascade',
192
["create table crash_me (a integer not null)",
193
"drop table crash_me cascade"]))
195
save_config_data('drop_requires_cascade',"yes","$prompt");
196
$drop_attr="cascade";
200
die "Can't create and drop table 'crash_me'\n";
205
save_config_data('drop_requires_cascade',"no","$prompt");
209
# Remove tables from old runs
210
$dbh->do("drop table crash_me $drop_attr");
211
$dbh->do("drop table crash_me2 $drop_attr");
212
$dbh->do("drop table crash_me3 $drop_attr");
213
$dbh->do("drop table crash_q $drop_attr");
214
$dbh->do("drop table crash_q1 $drop_attr");
216
$prompt="Tables without primary key";
217
if (!safe_query_l('no_primary_key',
218
["create table crash_me (a integer not null,b char(10) not null)",
219
"insert into crash_me (a,b) values (1,'a')"]))
221
if (!safe_query_l('no_primary_key',
222
["create table crash_me (a integer not null,b char(10) not null".
223
", primary key (a))",
224
"insert into crash_me (a,b) values (1,'a')"]))
226
die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
228
save_config_data('no_primary_key',"no",$prompt);
232
save_config_data('no_primary_key',"yes",$prompt);
236
# Define strings for character NULL and numeric NULL used in expressions
238
$char_null=$server->{'char_null'};
239
$numeric_null=$server->{'numeric_null'};
240
if ($char_null eq '')
244
if ($numeric_null eq '')
246
$numeric_null="NULL";
249
print "$prompt: $limits{'no_primary_key'}\n";
251
report("SELECT without FROM",'select_without_from',"select 1");
252
if ($limits{'select_without_from'} ne "yes")
254
$end_query=" from crash_me";
255
$check_connect="select a from crash_me";
260
$check_connect="select 1";
263
assert($check_connect);
264
assert("select a from crash_me where b<'b'");
266
report("Select constants",'select_constants',"select 1 $end_query");
267
report("Select table_name.*",'table_wildcard',
268
"select crash_me.* from crash_me");
269
report("Allows \' and \" as string markers",'quote_with_"',
270
'select a from crash_me where b<"c"');
271
check_and_report("Double '' as ' in strings",'double_quotes',[],
272
"select 'Walker''s' $end_query",[],"Walker's",1);
273
check_and_report("Multiple line strings","multi_strings",[],
274
"select a from crash_me where b < 'a'\n'b'",[],"1",0);
275
check_and_report("\" as identifier quote (ANSI SQL)",'quote_ident_with_"',[],
276
'select "A" from crash_me',[],"1",0);
277
check_and_report("\` as identifier quote",'quote_ident_with_`',[],
278
'select `A` from crash_me',[],"1",0);
279
check_and_report("[] as identifier quote",'quote_ident_with_[',[],
280
'select [A] from crash_me',[],"1",0);
281
report('Double "" in identifiers as "','quote_ident_with_dbl_"',
282
'create table crash_me1 ("abc""d" integer)',
283
'drop table crash_me1');
285
report("Column alias","column_alias","select a as ab from crash_me");
286
report("Table alias","table_alias","select b.a from crash_me as b");
287
report("Functions",'functions',"select 1+1 $end_query");
288
report("Group functions",'group_functions',"select count(*) from crash_me");
289
report("Group functions with distinct",'group_distinct_functions',
290
"select count(distinct a) from crash_me");
291
report("Group functions with several distinct",'group_many_distinct_functions',
292
"select count(distinct a), count(distinct b) from crash_me");
293
report("Group by",'group_by',"select a from crash_me group by a");
294
report("Group by position",'group_by_position',
295
"select a from crash_me group by 1");
296
report("Group by alias",'group_by_alias',
297
"select a as ab from crash_me group by ab");
298
report("Group on unused column",'group_on_unused',
299
"select count(*) from crash_me group by a");
301
report("Order by",'order_by',"select a from crash_me order by a");
302
report("Order by position",'order_by_position',
303
"select a from crash_me order by 1");
304
report("Order by function","order_by_function",
305
"select a from crash_me order by a+1");
306
report("Order by on unused column",'order_on_unused',
307
"select b from crash_me order by a");
308
# little bit deprecated
309
#check_and_report("Order by DESC is remembered",'order_by_remember_desc',
310
# ["create table crash_q (s int,s1 int)",
311
# "insert into crash_q values(1,1)",
312
# "insert into crash_q values(3,1)",
313
# "insert into crash_q values(2,1)"],
314
# "select s,s1 from crash_q order by s1 DESC,s",
315
# ["drop table crash_q $drop_attr"],[3,2,1],7,undef(),3);
316
report("Compute",'compute',
317
"select a from crash_me order by a compute sum(a) by a");
318
report("INSERT with Value lists",'insert_multi_value',
319
"create table crash_q (s char(10))",
320
"insert into crash_q values ('a'),('b')",
321
"drop table crash_q $drop_attr");
322
report("INSERT with set syntax",'insert_with_set',
323
"create table crash_q (a integer)",
324
"insert into crash_q SET a=1",
325
"drop table crash_q $drop_attr");
326
report("INSERT with DEFAULT","insert_with_default",
327
"create table crash_me_q (a int)",
328
"insert into crash_me_q (a) values (DEFAULT)",
329
"drop table crash_me_q $drop_attr");
331
report("INSERT with empty value list","insert_with_empty_value_list",
332
"create table crash_me_q (a int)",
333
"insert into crash_me_q (a) values ()",
334
"drop table crash_me_q $drop_attr");
336
report("INSERT DEFAULT VALUES","insert_default_values",
337
"create table crash_me_q (a int)",
338
"insert into crash_me_q DEFAULT VALUES",
339
"drop table crash_me_q $drop_attr");
341
report("allows end ';'","end_colon", "select * from crash_me;");
342
try_and_report("LIMIT number of rows","select_limit",
344
"select * from crash_me limit 1"],
346
"select TOP 1 * from crash_me"]);
347
report("SELECT with LIMIT #,#","select_limit2",
348
"select * from crash_me limit 1,1");
349
report("SELECT with LIMIT # OFFSET #",
350
"select_limit3", "select * from crash_me limit 1 offset 1");
352
# The following alter table commands MUST be kept together!
353
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))"))
355
report("Alter table add column",'alter_add_col',
356
"alter table crash_q add d integer");
357
report_one("Alter table add many columns",'alter_add_multi_col',
358
[["alter table crash_q add (f integer,g integer)","yes"],
359
["alter table crash_q add f integer, add g integer","with add"],
360
["alter table crash_q add f integer,g integer","without add"]] );
361
report("Alter table change column",'alter_change_col',
362
"alter table crash_q change a e char(50)");
364
# informix can only change data type with modify
365
report_one("Alter table modify column",'alter_modify_col',
366
[["alter table crash_q modify c1 CHAR(20)","yes"],
367
["alter table crash_q alter c1 CHAR(20)","with alter"]]);
368
report("Alter table alter column default",'alter_alter_col',
369
"alter table crash_q alter b set default 10");
370
report_one("Alter table drop column",'alter_drop_col',
371
[["alter table crash_q drop column b","yes"],
372
["alter table crash_q drop column b restrict",
373
"with restrict/cascade"]]);
374
report("Alter table rename table",'alter_rename_table',
375
"alter table crash_q rename to crash_q1");
377
# Make sure both tables will be dropped, even if rename fails.
378
$dbh->do("drop table crash_q1 $drop_attr");
379
$dbh->do("drop table crash_q $drop_attr");
381
report("rename table","rename_table",
382
"create table crash_q (a integer, b integer,c1 CHAR(10))",
383
"rename table crash_q to crash_q1",
384
"drop table crash_q1 $drop_attr");
385
# Make sure both tables will be dropped, even if rename fails.
386
$dbh->do("drop table crash_q1 $drop_attr");
387
$dbh->do("drop table crash_q $drop_attr");
389
report("truncate","truncate_table",
390
"create table crash_q (a integer, b integer,c1 CHAR(10))",
391
"truncate table crash_q",
392
"drop table crash_q $drop_attr");
394
if ($dbh->do("create table crash_q (a integer, b integer,c1 CHAR(10))") &&
395
$dbh->do("create table crash_q1 (a integer, b integer,c1 CHAR(10) not null)"))
397
report("Alter table add constraint",'alter_add_constraint',
398
"alter table crash_q add constraint c2 check(a > b)");
399
report_one("Alter table drop constraint",'alter_drop_constraint',
400
[["alter table crash_q drop constraint c2","yes"],
401
["alter table crash_q drop constraint c2 restrict",
402
"with restrict/cascade"]]);
403
report("Alter table add unique",'alter_add_unique',
404
"alter table crash_q add constraint u1 unique(c1)");
405
try_and_report("Alter table drop unique",'alter_drop_unique',
407
"alter table crash_q drop constraint u1"],
408
["with constraint and restrict/cascade",
409
"alter table crash_q drop constraint u1 restrict"],
411
"alter table crash_q drop key u1"]);
412
try_and_report("Alter table add primary key",'alter_add_primary_key',
414
"alter table crash_q1 add constraint p1 primary key(c1)"],
415
["with add primary key",
416
"alter table crash_q1 add primary key(c1)"]);
417
report("Alter table add foreign key",'alter_add_foreign_key',
418
"alter table crash_q add constraint f1 foreign key(c1) references crash_q1(c1)");
419
try_and_report("Alter table drop foreign key",'alter_drop_foreign_key',
420
["with drop constraint",
421
"alter table crash_q drop constraint f1"],
422
["with drop constraint and restrict/cascade",
423
"alter table crash_q drop constraint f1 restrict"],
424
["with drop foreign key",
425
"alter table crash_q drop foreign key f1"]);
426
try_and_report("Alter table drop primary key",'alter_drop_primary_key',
428
"alter table crash_q1 drop constraint p1 restrict"],
430
"alter table crash_q1 drop primary key"]);
432
$dbh->do("drop table crash_q $drop_attr");
433
$dbh->do("drop table crash_q1 $drop_attr");
435
check_and_report("Case insensitive compare","case_insensitive_strings",
436
[],"select b from crash_me where b = 'A'",[],'a',1);
437
check_and_report("Ignore end space in compare","ignore_end_space",
438
[],"select b from crash_me where b = 'a '",[],'a',1);
439
check_and_report("Group on column with null values",'group_by_null',
440
["create table crash_q (s char(10))",
441
"insert into crash_q values(null)",
442
"insert into crash_q values(null)"],
443
"select count(*),s from crash_q group by s",
444
["drop table crash_q $drop_attr"],2,0);
447
if (!defined($limits{'having'}))
448
{ # Complicated because of postgreSQL
449
if (!safe_query_result_l("having",
450
"select a from crash_me group by a having a > 0",1,0))
452
if (!safe_query_result_l("having",
453
"select a from crash_me group by a having a < 0",
455
{ save_config_data("having","error",$prompt); }
457
{ save_config_data("having","yes",$prompt); }
460
{ save_config_data("having","no",$prompt); }
462
print "$prompt: $limits{'having'}\n";
464
if ($limits{'having'} eq 'yes')
466
report("Having with group function","having_with_group",
467
"select a from crash_me group by a having count(*) = 1");
470
if ($limits{'column_alias'} eq 'yes')
472
report("Order by alias",'order_by_alias',
473
"select a as ab from crash_me order by ab");
474
if ($limits{'having'} eq 'yes')
476
report("Having on alias","having_with_alias",
477
"select a as ab from crash_me group by a having ab > 0");
480
report("binary numbers (0b1001)","binary_numbers","select 0b1001 $end_query");
481
report("hex numbers (0x41)","hex_numbers","select 0x41 $end_query");
482
report("binary strings (b'0110')","binary_strings","select b'0110' $end_query");
483
report("hex strings (x'1ace')","hex_strings","select x'1ace' $end_query");
485
report_result("Value of logical operation (1=1)","logical_value",
486
"select (1=1) $end_query");
488
report_result("Value of TRUE","value_of_true","select TRUE $end_query");
489
report_result("Value of FALSE","value_of_false","select FALSE $end_query");
491
$logical_value= $limits{'logical_value'};
495
if ($res=safe_query_l('has_true_false',"select (1=1)=true $end_query")) {
499
save_config_data('has_true_false',$result,"TRUE and FALSE");
502
# Check how many connections the server can handle:
503
# We can't test unlimited connections, because this may take down the
507
$prompt="Simultaneous connections (installation default)";
509
if (defined($limits{'connections'}))
511
print "$limits{'connections'}\n";
517
for ($i=1; $i < $max_connections ; $i++)
519
if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
520
{ PrintError => 0})))
522
print "Last connect error: $DBI::errstr\n" if ($opt_debug);
525
$dbh->{LongReadLen}= $longreadlen; # Set retrieval buffer
526
print "." if ($opt_debug);
530
save_config_data('connections',$i,$prompt);
531
foreach $dbh (@connect)
533
print "#" if ($opt_debug);
534
$dbh->disconnect || warn $dbh->errstr; # close connection
537
$#connect=-1; # Free connections
541
print "Can't connect to server: $DBI::errstr.".
542
" Please start it and try again\n";
545
$dbh=retry_connect();
550
# Check size of communication buffer, strings...
553
$prompt="query size";
555
if (!defined($limits{'query_size'}))
559
$end=$max_buffer_size;
560
$select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';
562
assert($query . "$select$end_query");
564
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
566
if ($limits{'restart'}{'tohigh'})
568
$end = $limits{'restart'}{'tohigh'} - 1;
569
print "\nRestarting this with low limit: $first and high limit: $end\n";
570
delete $limits{'restart'};
571
$first=$first+int(($end-$first+4)/5); # Prefere lower on errors
573
for ($i=$first ; $i < $end ; $i*=2)
575
last if (!safe_query($query .
576
(" " x ($i - length($query)-length($end_query) -1))
577
. "$select$end_query"));
579
save_config_data("restart",$i,"") if ($opt_restart);
583
if ($i < $max_buffer_size)
585
while ($first != $end)
587
$i=int(($first+$end+1)/2);
588
if (safe_query($query .
589
(" " x ($i - length($query)-length($end_query) -1)) .
590
"$select$end_query"))
600
save_config_data('query_size',$end,$prompt);
602
$query_size=$limits{'query_size'};
604
print "$limits{'query_size'}\n";
607
# Check for reserved words
610
check_reserved_words($dbh);
613
# Test database types
616
@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
619
"integer","int","smallint",
620
"numeric(9,2)","decimal(6,2)","dec(6,2)",
621
"bit", "bit(2)","bit varying(2)","float","float(8)","real",
622
"double precision", "date","time","timestamp",
623
"time(6)", "timestamp(6)",
624
"datetime", "datetime(6)",
625
"interval year", "interval year to month",
627
"interval day", "interval day to hour", "interval day to minute",
628
"interval day to second",
629
"interval hour", "interval hour to minute",
630
"interval hour to second",
631
"interval minute", "interval minute to second",
633
"national character varying(20)",
634
"national character(20)","nchar(1)",
635
"national char varying(20)","nchar varying(20)",
636
"national character varying(20)",
637
"timestamp with time zone");
638
@odbc_types=("binary(1)","varbinary(1)","tinyint","bigint");
639
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
641
"long varchar(1)", "varchar2(257)",
642
"mediumint","middleint","int unsigned",
643
"int1","int2","int3","int4","int8","uint",
644
"money","smallmoney","float4","float8","smallfloat",
645
"float(6,2)","double",
646
"enum('red')","set('red')", "int(5) zerofill", "serial",
647
"char(10) binary","int not null auto_increment,unique(q)",
648
"abstime","year","datetime","smalldatetime","timespan","reltime",
650
"int not null identity,unique(q)",
652
"box","bool","circle","polygon","point","line","lseg","path",
653
"interval", "inet", "cidr", "macaddr",
656
"varchar2(16)","nvarchar2(16)","number(9,2)","number(9)",
657
"number", "long","raw(16)","long raw","rowid","mlslabel","clob",
661
@types=(["sql",\@sql_types],
662
["odbc",\@odbc_types],
663
["extra",\@extra_types]);
665
foreach $types (@types)
667
print "\nSupported $types->[0] types\n";
669
foreach $use_type (@$tmp)
672
$type =~ s/\(.*\)/(1 arg)/;
673
if (index($use_type,",")>= 0)
675
$type =~ s/\(1 arg\)/(2 arg)/;
677
if (($tmp2=index($type,",unique")) >= 0)
679
$type=substr($type,0,$tmp2);
683
$tmp2 =~ s/_not_null//g;
684
report("Type $type","type_$types->[0]_$tmp2",
685
"create table crash_q (q $use_type)",
686
"drop table crash_q $drop_attr");
691
# Test some type limits
695
check_and_report("Remembers end space in char()","remember_end_space",
696
["create table crash_q (a char(10))",
697
"insert into crash_q values('hello ')"],
698
"select a from crash_q where a = 'hello '",
699
["drop table crash_q $drop_attr"],
702
check_and_report("Remembers end space in varchar()",
703
"remember_end_space_varchar",
704
["create table crash_q (a varchar(10))",
705
"insert into crash_q values('hello ')"],
706
"select a from crash_q where a = 'hello '",
707
["drop table crash_q $drop_attr"],
710
if (($limits{'type_extra_float(2_arg)'} eq "yes" ||
711
$limits{'type_sql_decimal(2_arg)'} eq "yes") &&
712
(!defined($limits{'storage_of_float'})))
714
my $type=$limits{'type_extra_float(2_arg)'} eq "yes" ? "float(4,1)" :
716
my $result="undefined";
717
if (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
718
"insert into crash_q values(1.14)"],
719
"select q1 from crash_q",
720
["drop table crash_q $drop_attr"],1.1,0) &&
721
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
722
"insert into crash_q values(1.16)"],
723
"select q1 from crash_q",
724
["drop table crash_q $drop_attr"],1.1,0))
728
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
729
"insert into crash_q values(1.14)"],
730
"select q1 from crash_q",
731
["drop table crash_q $drop_attr"],1.1,0) &&
732
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
733
"insert into crash_q values(1.16)"],
734
"select q1 from crash_q",
735
["drop table crash_q $drop_attr"],1.2,0))
739
elsif (execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
740
"insert into crash_q values(1.14)"],
741
"select q1 from crash_q",
742
["drop table crash_q $drop_attr"],1.14,0) &&
743
execute_and_check("storage_of_float",["create table crash_q (q1 $type)",
744
"insert into crash_q values(1.16)"],
745
"select q1 from crash_q",
746
["drop table crash_q $drop_attr"],1.16,0))
750
$prompt="Storage of float values";
751
save_config_data("storage_of_float", $result, $prompt);
754
if (defined($limits{'storage_of_float'}))
756
print "Storage of float values: $limits{'storage_of_float'}\n";
759
try_and_report("Type for row id", "rowid",
761
"create table crash_q (a rowid)",
762
"drop table crash_q $drop_attr"],
764
"create table crash_q (a int not null auto_increment".
765
", primary key(a))","drop table crash_q $drop_attr"],
767
"create table crash_q (a oid, primary key(a))",
768
"drop table crash_q $drop_attr"],
770
"create table crash_q (a serial, primary key(a))",
771
"drop table crash_q $drop_attr"]);
773
try_and_report("Automatic row id", "automatic_rowid",
775
"create table crash_q (a int not null, primary key(a))",
776
"insert into crash_q values (1)",
777
"select _rowid from crash_q",
778
"drop table crash_q $drop_attr"]);
785
(["+, -, * and /","+","5*3-4/2+1",14,0],
786
["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
787
["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
788
["searched CASE","searched_case",
789
"case when 1 > 2 then 'false' when 2 > 1 then 'true' end", "true",1],
790
["simple CASE","simple_case",
791
"case 2 when 1 then 'false' when 2 then 'true' end", "true",1],
792
["CAST","cast","CAST(1 as CHAR)","1",1],
793
["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
794
["CHAR_LENGTH","char_length","char_length(b)","10",0],
795
["CHAR_LENGTH(constant)","char_length(constant)",
796
"char_length('abcd')","4",0],
797
["COALESCE","coalesce","coalesce($char_null,'bcd','qwe')","bcd",1],
798
["CURRENT_DATE","current_date","current_date",0,2],
799
["CURRENT_TIME","current_time","current_time",0,2],
800
["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
801
["EXTRACT","extract_sql",
802
"extract(minute from timestamp '2000-02-23 18:43:12.987')",43,0],
803
["LOCALTIME","localtime","localtime",0,2],
804
["LOCALTIMESTAMP","localtimestamp","localtimestamp",0,2],
805
["LOWER","lower","LOWER('ABC')","abc",1],
806
["NULLIF with strings","nullif_string",
807
"NULLIF(NULLIF('first','second'),'first')",undef(),4],
808
["NULLIF with numbers","nullif_num","NULLIF(NULLIF(1,2),1)",undef(),4],
809
["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
810
["POSITION","position","position('ll' in 'hello')",3,0],
811
["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",3],
812
["UPPER","upper","UPPER('abc')","ABC",1],
813
["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
817
(["ASCII", "ascii", "ASCII('A')","65",0],
818
["CHAR", "char", "CHAR(65)" ,"A",1],
819
["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
820
["DIFFERENCE()","difference","difference('abc','abe')",3,0],
821
["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
822
["LEFT","left","left('abcd',2)","ab",1],
823
["LTRIM","ltrim","ltrim(' abcd')","abcd",1],
824
["REAL LENGTH","length","length('abcd ')","5",0],
825
["ODBC LENGTH","length_without_space","length('abcd ')","4",0],
826
["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
827
["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
828
["LCASE","lcase","lcase('ABC')","abc",1],
829
["REPEAT","repeat","repeat('ab',3)","ababab",1],
830
["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
831
["RIGHT","right","right('abcd',2)","cd",1],
832
["RTRIM","rtrim","rtrim(' abcd ')"," abcd",1],
833
["SPACE","space","space(5)"," ",3],
834
["SOUNDEX","soundex","soundex('hello')",0,2],
835
["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
836
["UCASE","ucase","ucase('abc')","ABC",1],
838
["ABS","abs","abs(-5)",5,0],
839
["ACOS","acos","acos(0)","1.570796",0],
840
["ASIN","asin","asin(1)","1.570796",0],
841
["ATAN","atan","atan(1)","0.785398",0],
842
["ATAN2","atan2","atan2(1,0)","1.570796",0],
843
["CEILING","ceiling","ceiling(-4.5)",-4,0],
844
["COS","cos","cos(0)","1.00000",0],
845
["COT","cot","cot(1)","0.64209262",0],
846
["DEGREES","degrees","degrees(6.283185)","360",0],
847
["EXP","exp","exp(1.0)","2.718282",0],
848
["FLOOR","floor","floor(2.5)","2",0],
849
["LOG","log","log(2)","0.693147",0],
850
["LOG10","log10","log10(10)","1",0],
851
["MOD","mod","mod(11,7)","4",0],
852
["PI","pi","pi()","3.141593",0],
853
["POWER","power","power(2,4)","16",0],
854
["RAND","rand","rand(1)",0,2], # Any value is acceptable
855
["RADIANS","radians","radians(360)","6.283185",0],
856
["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
857
["SIGN","sign","sign(-5)",-1,0],
858
["SIN","sin","sin(1)","0.841471",0],
859
["SQRT","sqrt","sqrt(4)",2,0],
860
["TAN","tan","tan(1)","1.557408",0],
861
["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
862
["NOW","now","now()",0,2], # Any value is acceptable
863
["CURDATE","curdate","curdate()",0,2],
864
["CURTIME","curtime","curtime()",0,2],
865
["TIMESTAMPADD","timestampadd",
866
"timestampadd(SQL_TSI_SECOND,1,'1997-01-01 00:00:00')",
867
"1997-01-01 00:00:01",1],
868
["TIMESTAMPDIFF","timestampdiff",
869
"timestampdiff(SQL_TSI_SECOND,'1997-01-01 00:00:02',".
870
" '1997-01-01 00:00:01')","1",0],
871
["USER()","user()","user()",0,2],
872
["DATABASE","database","database()",0,2],
873
["IFNULL","ifnull","ifnull(2,3)",2,0],
874
["ODBC syntax LEFT & RIGHT", "fn_left",
875
"{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
880
["& (bitwise and)",'&',"5 & 3",1,0],
881
["| (bitwise or)",'|',"1 | 2",3,0],
882
["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
883
["<> in SELECT","<>","1<>1","0",0],
884
["=","=","(1=1)",1,$logical_value],
885
["~* (case insensitive compare)","~*","'hi' ~* 'HI'",1,$logical_value],
886
["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
887
["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
888
["ASCII_CHAR", "ascii_char", "ASCII_CHAR(65)","A",1],
889
["ASCII_CODE", "ascii_code", "ASCII_CODE('A')","65",0],
890
["ATN2","atn2","atn2(1,0)","1.570796",0],
891
["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
892
["BIT_COUNT","bit_count","bit_count(5)",2,0],
893
["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
894
["CHARINDEX","charindex","charindex('a','crash')",3,0],
895
["CHR", "chr", "CHR(65)" ,"A",1], # oracle
896
["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
897
["CONVERT","convert","convert(CHAR,5)","5",1],
898
["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
899
["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
900
["ENCRYPT","encrypt","encrypt('hello')",0,2],
901
["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
902
["FORMAT","format","format(1234.5555,2)","1,234.56",1],
903
["GETDATE","getdate","getdate()",0,2],
904
["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1],
905
["IF","if", "if(5,6,7)",6,0],
906
["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
907
["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
908
["INITCAP","initcap","initcap('the soap')","The Soap",1],
909
# oracle Returns char, with the first letter of each word in uppercase
910
["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)" ,"14",0], # oracle instring
911
["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)" ,"27",0],
912
# oracle instring in bytes
913
["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
914
["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
915
["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1],
917
["LENGTHB","lengthb","lengthb('CANDIDE')","14",0],
918
# oracle length in bytes
919
["LIKE ESCAPE in SELECT","like_escape",
920
"'%' like 'a%' escape 'a'",$logical_value,0],
921
["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
922
["LN","ln","ln(95)","4.55387689",0],
923
# oracle natural logarithm of n
924
["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
925
["LOG(m,n)","log(m_n)","log(10,100)","2",0],
926
# oracle logarithm, base m, of n
927
["LOGN","logn","logn(2)","0.693147",0],
929
["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
930
["MOD as %","%","10%7","3",0],
931
["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
932
["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
933
["NOT as '!' in SELECT","!","! 1",0,0],
934
["NOT in SELECT","not","not $false",$logical_value,0],
935
["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
936
["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
937
["PASSWORD","password","password('hello')",0,2],
938
["PASTE", "paste", "paste('ABCDEFG',3,2,'1234')","AB1234EFG",1],
939
["PATINDEX","patindex","patindex('%a%','crash')",3,0],
940
["POW","pow","pow(3,2)",9,0],
941
["RANGE","range","range(a)","0.0",0],
942
# informix range(a) = max(a) - min(a)
943
["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
944
["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
945
["REVERSE","reverse","reverse('abcd')","dcba",1],
946
["ROOT","root","root(4)",2,0], # informix
947
["ROUND(1 arg)","round1","round(5.63)","6",0],
948
["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
949
["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
950
["STR","str","str(123.45,5,1)",123.5,3],
951
["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
952
["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
953
["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)" ,"CD",1],
954
# oracle substring with bytes
955
["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
956
["SUBSTRING_INDEX","substring_index",
957
"substring_index('www.tcx.se','.',-2)", "tcx.se",1],
958
["SYSDATE","sysdate","sysdate()",0,2],
959
["TAIL","tail","tail('ABCDEFG',3)","EFG",0],
960
["TANH","tanh","tanh(1)","0.462117157",0],
961
# oracle hyperbolic tangent of n
962
["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
963
["TRIM; Many char extension",
964
"trim_many_char","trim(':!' FROM ':abc!')","abc",3],
965
["TRIM; Substring extension",
966
"trim_substring","trim('cb' FROM 'abccb')","abc",3],
967
["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
968
["UID","uid","uid",0,2], # oracle uid from user
969
["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
970
["USERENV","userenv","userenv",0,2], # oracle user enviroment
971
["VERSION","version","version()",0,2],
972
["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
973
["automatic string->num convert","auto_string2num","'1'+2",3,0],
974
["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
975
["SUBSTR (2 arg)",'substr2arg',"substr('abcd',2)",'bcd',1], #sapdb func
976
["SUBSTR (3 arg)",'substr3arg',"substr('abcd',2,2)",'bc',1],
977
["LFILL (3 arg)",'lfill3arg',"lfill('abcd','.',6)",'..abcd',1],
978
["RFILL (3 arg)",'rfill3arg',"rfill('abcd','.',6)",'abcd..',1],
979
["RPAD (4 arg)",'rpad4arg',"rpad('abcd',2,'+-',8)",'abcd+-+-',1],
980
["LPAD (4 arg)",'rpad4arg',"lpad('abcd',2,'+-',8)",'+-+-abcd',1],
981
["TRIM (1 arg)",'trim1arg',"trim(' abcd ')",'abcd',1],
982
["TRIM (2 arg)",'trim2arg',"trim('..abcd..','.')",'abcd',1],
983
["LTRIM (2 arg)",'ltrim2arg',"ltrim('..abcd..','.')",'abcd..',1],
984
["RTRIM (2 arg)",'rtrim2arg',"rtrim('..abcd..','.')",'..abcd',1],
985
["EXPAND",'expand2arg',"expand('abcd',6)",'abcd ',0],
986
["REPLACE (2 arg) ",'replace2arg',"replace('AbCd','bC')",'Ad',1],
987
["MAPCHAR",'mapchar',"mapchar('A�')",'Aa',1],
988
["ALPHA",'alpha',"alpha('A�',2)",'AA',1],
989
["ASCII in string cast",'ascii_string',"ascii('a')",'a',1],
990
["EBCDIC in string cast",'ebcdic_string',"ebcdic('a')",'a',1],
991
["TRUNC (1 arg)",'trunc1arg',"trunc(222.6)",222,0],
992
["FIXED",'fixed',"fixed(222.6666,10,2)",'222.67',0],
993
["FLOAT",'float',"float(6666.66,4)",6667,0],
994
["LENGTH",'length',"length(1)",2,0],
995
["INDEX",'index',"index('abcdefg','cd',1,1)",3,0],
996
["MICROSECOND",'microsecond',
997
"MICROSECOND('19630816200212111111')",'111111',0],
998
["TIMESTAMP",'timestamp',
999
"timestamp('19630816','00200212')",'19630816200212000000',0],
1000
["VALUE",'value',"value(NULL,'WALRUS')",'WALRUS',0],
1001
["DECODE",'decode',"DECODE('S-103','T72',1,'S-103',2,'Leopard',3)",2,0],
1002
["NUM",'num',"NUM('2123')",2123,0],
1003
["CHR (any type to string)",'chr_str',"CHR(67)",'67',0],
1004
["HEX",'hex',"HEX('A')",41,0],
1008
@sql_group_functions=
1010
["AVG","avg","avg(a)",1,0],
1011
["COUNT (*)","count_*","count(*)",1,0],
1012
["COUNT column name","count_column","count(a)",1,0],
1013
["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
1014
["MAX on numbers","max","max(a)",1,0],
1015
["MAX on strings","max_str","max(b)","a",1],
1016
["MIN on numbers","min","min(a)",1,0],
1017
["MIN on strings","min_str","min(b)","a",1],
1018
["SUM","sum","sum(a)",1,0],
1019
["ANY","any","any(a)",$logical_value,0],
1020
["EVERY","every","every(a)",$logical_value,0],
1021
["SOME","some","some(a)",$logical_value,0],
1024
@extra_group_functions=
1026
["BIT_AND",'bit_and',"bit_and(a)",1,0],
1027
["BIT_OR", 'bit_or', "bit_or(a)",1,0],
1028
["COUNT(DISTINCT expr,expr,...)",
1029
"count_distinct_list","count(distinct a,b)",1,0],
1030
["STD","std","std(a)",0,0],
1031
["STDDEV","stddev","stddev(a)",0,0],
1032
["VARIANCE","variance","variance(a)",0,0],
1037
["= ALL","eq_all","b =all (select b from crash_me)",1,0],
1038
["= ANY","eq_any","b =any (select b from crash_me)",1,0],
1039
["= SOME","eq_some","b =some (select b from crash_me)",1,0],
1040
["BETWEEN","between","5 between 4 and 6",1,0],
1041
["EXISTS","exists","exists (select * from crash_me)",1,0],
1042
["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
1043
["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
1044
["LIKE","like","b like 'a%'",1,0],
1045
["MATCH UNIQUE","match_unique",
1046
"1 match unique (select a from crash_me)",1,0],
1047
["MATCH","match","1 match (select a from crash_me)",1,0],
1048
["MATCHES","matches","b matches 'a*'",1,0],
1049
["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
1050
["NOT EXISTS","not_exists",
1051
"not exists (select * from crash_me where a = 2)",1,0],
1052
["NOT LIKE","not_like","b not like 'b%'",1,0],
1053
["NOT UNIQUE","not_unique",
1054
"not unique (select * from crash_me where a = 2)",1,0],
1055
["UNIQUE","unique","unique (select * from crash_me)",1,0],
1058
@types=(["sql",\@sql_functions,0],
1059
["odbc",\@odbc_functions,0],
1060
["extra",\@extra_functions,0],
1061
["where",\@where_functions,0]);
1063
@group_types=(["sql",\@sql_group_functions,0],
1064
["extra",\@extra_group_functions,0]);
1067
foreach $types (@types)
1069
print "\nSupported $types->[0] functions\n";
1071
foreach $type (@$tmp)
1073
if (defined($limits{"func_$types->[0]_$type->[1]"}))
1077
if ($types->[0] eq "where")
1079
check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
1080
[],"select a from crash_me where $type->[2]",[],
1081
$type->[3],$type->[4]);
1083
elsif ($limits{'functions'} eq 'yes')
1085
if (($type->[2] =~ /char_length\(b\)/) && (!$end_query))
1087
my $tmp= $type->[2];
1088
$tmp .= " from crash_me ";
1089
undef($limits{"func_$types->[0]_$type->[1]"});
1090
check_and_report("Function $type->[0]",
1091
"func_$types->[0]_$type->[1]",
1092
[],"select $tmp ",[],
1093
$type->[3],$type->[4]);
1097
undef($limits{"func_$types->[0]_$type->[1]"});
1098
$result = check_and_report("Function $type->[0]",
1099
"func_$types->[0]_$type->[1]",
1100
[],"select $type->[2] $end_query",[],
1101
$type->[3],$type->[4]);
1104
# check without type specifyer
1105
if ($type->[2] =~ /DATE /)
1107
my $tmp= $type->[2];
1109
undef($limits{"func_$types->[0]_$type->[1]"});
1110
$result = check_and_report("Function $type->[0]",
1111
"func_$types->[0]_$type->[1]",
1112
[],"select $tmp $end_query",[],
1113
$type->[3],$type->[4]);
1117
if ($types->[0] eq "odbc" && ! ($type->[2] =~ /\{fn/))
1119
my $tmp= $type->[2];
1120
# Check by converting to ODBC format
1121
undef($limits{"func_$types->[0]_$type->[1]"});
1123
$tmp =~ s/('1997-\d\d-\d\d \d\d:\d\d:\d\d')/{ts $1}/g;
1124
$tmp =~ s/(DATE '1997-\d\d-\d\d')/{d $1}/g;
1125
$tmp =~ s/(TIME '12:13:14')/{t $1}/g;
1128
check_and_report("Function $type->[0]",
1129
"func_$types->[0]_$type->[1]",
1130
[],"select $tmp $end_query",[],
1131
$type->[3],$type->[4]);
1140
if ($limits{'functions'} eq 'yes')
1142
foreach $types (@group_types)
1144
print "\nSupported $types->[0] group functions\n";
1146
foreach $type (@$tmp)
1148
check_and_report("Group function $type->[0]",
1149
"group_func_$types->[0]_$type->[1]",
1150
[],"select $type->[2],a from crash_me group by a",[],
1151
$type->[3],$type->[4]);
1155
report("mixing of integer and float in expression","float_int_expr",
1156
"select 1+1.0 $end_query");
1157
if ($limits{'func_odbc_exp'} eq 'yes')
1159
report("No need to cast from integer to float",
1160
"dont_require_cast_to_float", "select exp(1) $end_query");
1162
check_and_report("Is 1+NULL = NULL","null_num_expr",
1163
[],"select 1+$numeric_null $end_query",[],undef(),4);
1164
$tmp=sql_concat("'a'",$char_null);
1167
check_and_report("Is $tmp = NULL", "null_concat_expr", [],
1168
"select $tmp $end_query",[], undef(),4);
1170
$prompt="Need to cast NULL for arithmetic";
1171
add_log("Need_cast_for_null",
1172
" Check if numeric_null ($numeric_null) is 'NULL'");
1173
save_config_data("Need_cast_for_null",
1174
($numeric_null eq "NULL") ? "no" : "yes",
1185
my $result = 'undefined';
1188
save_incomplete('func_extra_noround','Function NOROUND');
1190
# 1) check if noround() function is supported
1191
$error = safe_query_l('func_extra_noround',"select noround(22.6) $end_query");
1192
if ($error ne 1) # syntax error -- noround is not supported
1196
else # Ok, now check if it really works
1198
$error=safe_query_l('func_extra_noround',
1199
["create table crash_me_nr (a int)",
1200
"insert into crash_me_nr values(noround(10.2))",
1201
"drop table crash_me_nr $drop_attr"]);
1204
$result= "syntax only";
1212
save_config_data('func_extra_noround',$result,"Function NOROUND");
1215
check_parenthesis("func_sql_","CURRENT_USER");
1216
check_parenthesis("func_sql_","SESSION_USER");
1217
check_parenthesis("func_sql_","SYSTEM_USER");
1218
check_parenthesis("func_sql_","USER");
1221
if ($limits{'type_sql_date'} eq 'yes')
1223
# Checking the format of date in result.
1225
safe_query("drop table crash_me_d $drop_attr");
1226
assert("create table crash_me_d (a date)");
1227
# find the example of date
1229
if ($limits{'func_extra_sysdate'} eq 'yes') {
1230
$dateexample=' sysdate() ';
1232
elsif ($limits{'func_sql_current_date'} eq 'yes') {
1233
$dateexample='CURRENT_DATE';
1235
elsif ($limits{'func_odbc_curdate'} eq 'yes') {
1236
$dateexample='curdate()';
1238
elsif ($limits{'func_extra_getdate'} eq 'yes') {
1239
$dateexample='getdate()';
1241
elsif ($limits{'func_odbc_now'} eq 'yes') {
1242
$dateexample='now()';
1245
$dateexample="DATE '1963-08-16'";
1248
my $key = 'date_format_inresult';
1249
my $prompt = "Date format in result";
1250
if (! safe_query_l('date_format_inresult',
1251
"insert into crash_me_d values($dateexample) "))
1253
die "Cannot insert date ($dateexample):".$last_error;
1255
my $sth= $dbh->prepare("select a from crash_me_d");
1256
add_log('date_format_inresult',"< select a from crash_me_d");
1258
$_= $sth->fetchrow_array;
1259
add_log('date_format_inresult',"> $_");
1260
safe_query_l($key,"delete from crash_me_d");
1261
if (/\d{4}-\d{2}-\d{2}/){ save_config_data($key,"iso",$prompt);}
1262
elsif (/\d{2}-\d{2}-\d{2}/){ save_config_data($key,"short iso",$prompt);}
1263
elsif (/\d{2}\.\d{2}\.\d{4}/){ save_config_data($key,"euro",$prompt);}
1264
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"short euro",$prompt);}
1265
elsif (/\d{2}\/\d{2}\/\d{4}/){ save_config_data($key,"usa",$prompt);}
1266
elsif (/\d{2}\/\d{2}\/\d{2}/){ save_config_data($key,"short usa",$prompt);}
1267
elsif (/\d*/){ save_config_data($key,"YYYYMMDD",$prompt);}
1268
else { save_config_data($key,"unknown",$prompt);};
1271
check_and_report("Supports YYYY-MM-DD (ISO) format","date_format_ISO",
1272
[ "insert into crash_me_d(a) values ('1963-08-16')"],
1273
"select a from crash_me_d",
1274
["delete from crash_me_d"],
1275
make_date_r(1963,8,16),1);
1277
check_and_report("Supports DATE 'YYYY-MM-DD' (ISO) format",
1278
"date_format_ISO_with_date",
1279
[ "insert into crash_me_d(a) values (DATE '1963-08-16')"],
1280
"select a from crash_me_d",
1281
["delete from crash_me_d"],
1282
make_date_r(1963,8,16),1);
1284
check_and_report("Supports DD.MM.YYYY (EUR) format","date_format_EUR",
1285
[ "insert into crash_me_d(a) values ('16.08.1963')"],
1286
"select a from crash_me_d",
1287
["delete from crash_me_d"],
1288
make_date_r(1963,8,16),1);
1289
check_and_report("Supports DATE 'DD.MM.YYYY' (EUR) format",
1290
"date_format_EUR_with_date",
1291
[ "insert into crash_me_d(a) values (DATE '16.08.1963')"],
1292
"select a from crash_me_d",
1293
["delete from crash_me_d"],
1294
make_date_r(1963,8,16),1);
1296
check_and_report("Supports YYYYMMDD format",
1297
"date_format_YYYYMMDD",
1298
[ "insert into crash_me_d(a) values ('19630816')"],
1299
"select a from crash_me_d",
1300
["delete from crash_me_d"],
1301
make_date_r(1963,8,16),1);
1302
check_and_report("Supports DATE 'YYYYMMDD' format",
1303
"date_format_YYYYMMDD_with_date",
1304
[ "insert into crash_me_d(a) values (DATE '19630816')"],
1305
"select a from crash_me_d",
1306
["delete from crash_me_d"],
1307
make_date_r(1963,8,16),1);
1309
check_and_report("Supports MM/DD/YYYY format",
1311
[ "insert into crash_me_d(a) values ('08/16/1963')"],
1312
"select a from crash_me_d",
1313
["delete from crash_me_d"],
1314
make_date_r(1963,8,16),1);
1315
check_and_report("Supports DATE 'MM/DD/YYYY' format",
1316
"date_format_USA_with_date",
1317
[ "insert into crash_me_d(a) values (DATE '08/16/1963')"],
1318
"select a from crash_me_d",
1319
["delete from crash_me_d"],
1320
make_date_r(1963,8,16),1);
1325
check_and_report("Supports 0000-00-00 dates","date_zero",
1326
["create table crash_me2 (a date not null)",
1327
"insert into crash_me2 values (".make_date(0,0,0).")"],
1328
"select a from crash_me2",
1329
["drop table crash_me2 $drop_attr"],
1330
make_date_r(0,0,0),1);
1332
check_and_report("Supports 0001-01-01 dates","date_one",
1333
["create table crash_me2 (a date not null)",
1334
"insert into crash_me2 values (".make_date(1,1,1).")"],
1335
"select a from crash_me2",
1336
["drop table crash_me2 $drop_attr"],
1337
make_date_r(1,1,1),1);
1339
check_and_report("Supports 9999-12-31 dates","date_last",
1340
["create table crash_me2 (a date not null)",
1341
"insert into crash_me2 values (".make_date(9999,12,31).")"],
1342
"select a from crash_me2",
1343
["drop table crash_me2 $drop_attr"],
1344
make_date_r(9999,12,31),1);
1346
check_and_report("Supports 'infinity dates","date_infinity",
1347
["create table crash_me2 (a date not null)",
1348
"insert into crash_me2 values ('infinity')"],
1349
"select a from crash_me2",
1350
["drop table crash_me2 $drop_attr"],
1353
if (!defined($limits{'date_with_YY'}))
1355
check_and_report("Supports YY-MM-DD dates","date_with_YY",
1356
["create table crash_me2 (a date not null)",
1357
"insert into crash_me2 values ('98-03-03')"],
1358
"select a from crash_me2",
1359
["drop table crash_me2 $drop_attr"],
1360
make_date_r(1998,3,3),5);
1361
if ($limits{'date_with_YY'} eq "yes")
1363
undef($limits{'date_with_YY'});
1364
check_and_report("Supports YY-MM-DD 2000 compilant dates",
1366
["create table crash_me2 (a date not null)",
1367
"insert into crash_me2 values ('10-03-03')"],
1368
"select a from crash_me2",
1369
["drop table crash_me2 $drop_attr"],
1370
make_date_r(2010,3,3),5);
1379
save_incomplete('func_odbc_week','WEEK');
1380
$error = safe_query_result_l('func_odbc_week',
1381
"select week(".make_date(1997,2,1).") $end_query",5,0);
1382
# actually this query must return 4 or 5 in the $last_result,
1383
# $error can be 1 (not supported at all) , -1 ( probably USA weeks)
1384
# and 0 - EURO weeks
1386
if ($last_result == 4) {
1390
add_log('func_odbc_week',
1391
" must return 4 or 5, but $last_result");
1393
} elsif ($error == 0) {
1397
save_config_data('func_odbc_week',$result,"WEEK");
1400
my $insert_query ='insert into crash_me_d values('.
1401
make_date(1997,2,1).')';
1402
safe_query($insert_query);
1405
["DAYNAME","dayname","dayname(a)","",2],
1406
["MONTH","month","month(a)","",2],
1407
["MONTHNAME","monthname","monthname(a)","",2],
1408
["DAYOFMONTH","dayofmonth","dayofmonth(a)",1,0],
1409
["DAYOFWEEK","dayofweek","dayofweek(a)",7,0],
1410
["DAYOFYEAR","dayofyear","dayofyear(a)",32,0],
1411
["QUARTER","quarter","quarter(a)",1,0],
1412
["YEAR","year","year(a)",1997,0]))
1414
$prompt='Function '.$fn->[0];
1415
$key='func_odbc_'.$fn->[1];
1416
add_log($key,"< ".$insert_query);
1417
check_and_report($prompt,$key,
1418
[],"select ".$fn->[2]." from crash_me_d",[],
1423
safe_query(['delete from crash_me_d',
1424
'insert into crash_me_d values('.make_date(1963,8,16).')']);
1426
["DATEADD","dateadd","dateadd(day,3,make_date(1997,11,30))",0,2],
1427
["MDY","mdy","mdy(7,1,1998)","make_date_r(1998,07,01)",0], # informix
1428
["DATEDIFF","datediff",
1429
"datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
1430
["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
1431
["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
1432
["DATE_FORMAT","date_format",
1433
"date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
1434
["FROM_DAYS","from_days",
1435
"from_days(729024)","make_date_r(1996,1,1)",1],
1436
["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
1437
["MONTHS_BETWEEN","months_between",
1438
"months_between(make_date(1997,2,2),make_date(1997,1,1))",
1439
"1.03225806",0], # oracle number of months between 2 dates
1440
["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
1441
["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
1442
["WEEKDAY","weekday","weekday(make_date(1997,11,29))",5,0],
1443
["ADDDATE",'adddate',
1444
"ADDDATE(make_date(2002,12,01),3)",'make_date_r(2002,12,04)',0],
1445
["SUBDATE",'subdate',
1446
"SUBDATE(make_date(2002,12,04),3)",'make_date_r(2002,12,01)',0],
1447
["DATEDIFF (2 arg)",'datediff2arg',
1448
"DATEDIFF(make_date(2002,12,04),make_date(2002,12,01))",'3',0],
1449
["WEEKOFYEAR",'weekofyear',
1450
"WEEKOFYEAR(make_date(1963,08,16))",'33',0],
1451
# table crash_me_d must contain record with 1963-08-16 (for CHAR)
1452
["CHAR (conversation date)",'char_date',
1453
"CHAR(a,EUR)",'16.08.1963',0],
1454
["MAKEDATE",'makedate',"MAKEDATE(1963,228)"
1455
,'make_date_r(1963,08,16)',0],
1456
["TO_DAYS","to_days",
1457
"to_days(make_date(1996,01,01))",729024,0],
1458
["ADD_MONTHS","add_months",
1459
"add_months(make_date(1997,01,01),1)","make_date_r(1997,02,01)",0],
1460
# oracle the date plus n months
1461
["LAST_DAY","last_day",
1462
"last_day(make_date(1997,04,01))","make_date_r(1997,04,30)",0],
1463
# oracle last day of month of date
1464
["DATE",'date',"date(make_date(1963,8,16))",
1465
'make_date_r(1963,8,16)',0],
1466
["DAY",'day',"DAY(make_date(2002,12,01))",1,0]))
1468
$prompt='Function '.$fn->[0];
1469
$key='func_extra_'.$fn->[1];
1470
my $qry="select ".$fn->[2]." from crash_me_d";
1471
while( $qry =~ /^(.*)make_date\((\d+),(\d+),(\d+)\)(.*)$/)
1473
my $dt= &make_date($2,$3,$4);
1476
my $result=$fn->[3];
1477
while( $result =~ /^(.*)make_date_r\((\d+),(\d+),(\d+)\)(.*)$/)
1479
my $dt= &make_date_r($2,$3,$4);
1482
check_and_report($prompt,$key,
1489
safe_query("drop table crash_me_d $drop_attr");
1493
if ($limits{'type_sql_time'} eq 'yes')
1495
# Checking the format of date in result.
1497
safe_query("drop table crash_me_t $drop_attr");
1498
assert("create table crash_me_t (a time)");
1499
# find the example of time
1501
if ($limits{'func_sql_current_time'} eq 'yes') {
1502
$timeexample='CURRENT_TIME';
1504
elsif ($limits{'func_odbc_curtime'} eq 'yes') {
1505
$timeexample='curtime()';
1507
elsif ($limits{'func_sql_localtime'} eq 'yes') {
1508
$timeexample='localtime';
1510
elsif ($limits{'func_odbc_now'} eq 'yes') {
1511
$timeexample='now()';
1514
$timeexample="'02:55:12'";
1517
my $key = 'time_format_inresult';
1518
my $prompt = "Time format in result";
1519
if (! safe_query_l('time_format_inresult',
1520
"insert into crash_me_t values($timeexample) "))
1522
die "Cannot insert time ($timeexample):".$last_error;
1524
my $sth= $dbh->prepare("select a from crash_me_t");
1525
add_log('time_format_inresult',"< select a from crash_me_t");
1527
$_= $sth->fetchrow_array;
1528
add_log('time_format_inresult',"> $_");
1529
safe_query_l($key,"delete from crash_me_t");
1530
if (/\d{2}:\d{2}:\d{2}/){ save_config_data($key,"iso",$prompt);}
1531
elsif (/\d{2}\.\d{2}\.\d{2}/){ save_config_data($key,"euro",$prompt);}
1532
elsif (/\d{2}:\d{2}\s+(AM|PM)/i){ save_config_data($key,"usa",$prompt);}
1533
elsif (/\d{8}$/){ save_config_data($key,"HHHHMMSS",$prompt);}
1534
elsif (/\d{4}$/){ save_config_data($key,"HHMMSS",$prompt);}
1535
else { save_config_data($key,"unknown",$prompt);};
1538
check_and_report("Supports HH:MM:SS (ISO) time format","time_format_ISO",
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 HH.MM.SS (EUR) time format","time_format_EUR",
1545
[ "insert into crash_me_t(a) values ('20.08.16')"],
1546
"select a from crash_me_t",
1547
["delete from crash_me_t"],
1548
make_time_r(20,8,16),1);
1550
check_and_report("Supports HHHHmmSS time format",
1551
"time_format_HHHHMMSS",
1552
[ "insert into crash_me_t(a) values ('00200816')"],
1553
"select a from crash_me_t",
1554
["delete from crash_me_t"],
1555
make_time_r(20,8,16),1);
1557
check_and_report("Supports HHmmSS time format",
1558
"time_format_HHHHMMSS",
1559
[ "insert into crash_me_t(a) values ('200816')"],
1560
"select a from crash_me_t",
1561
["delete from crash_me_t"],
1562
make_time_r(20,8,16),1);
1564
check_and_report("Supports HH:MM:SS (AM|PM) time format",
1566
[ "insert into crash_me_t(a) values ('08:08:16 PM')"],
1567
"select a from crash_me_t",
1568
["delete from crash_me_t"],
1569
make_time_r(20,8,16),1);
1571
my $insert_query ='insert into crash_me_t values('.
1572
make_time(20,8,16).')';
1573
safe_query($insert_query);
1576
["HOUR","hour","hour('".make_time(12,13,14)."')",12,0],
1577
["ANSI HOUR","hour_time","hour(TIME '".make_time(12,13,14)."')",12,0],
1578
["MINUTE","minute","minute('".make_time(12,13,14)."')",13,0],
1579
["SECOND","second","second('".make_time(12,13,14)."')",14,0]
1583
$prompt='Function '.$fn->[0];
1584
$key='func_odbc_'.$fn->[1];
1585
add_log($key,"< ".$insert_query);
1586
check_and_report($prompt,$key,
1587
[],"select ".$fn->[2]." $end_query",[],
1592
# safe_query(['delete from crash_me_t',
1593
# 'insert into crash_me_t values('.make_time(20,8,16).')']);
1595
["TIME_TO_SEC","time_to_sec","time_to_sec('".
1596
make_time(1,23,21)."')","5001",0],
1597
["SEC_TO_TIME","sec_to_time","sec_to_time(5001)",
1598
make_time_r(01,23,21),1],
1599
["ADDTIME",'addtime',"ADDTIME('".make_time(20,2,12).
1600
"','".make_time(0,0,3)."')",make_time_r(20,2,15),0],
1601
["SUBTIME",'subtime',"SUBTIME('".make_time(20,2,15)
1602
."','".make_time(0,0,3)."')",make_time_r(20,2,12),0],
1603
["TIMEDIFF",'timediff',"TIMEDIFF('".make_time(20,2,15)."','".
1604
make_time(20,2,12)."')",make_time_r(0,0,3),0],
1605
["MAKETIME",'maketime',"MAKETIME(20,02,12)",make_time_r(20,2,12),0],
1606
["TIME",'time',"time('".make_time(20,2,12)."')",make_time_r(20,2,12),0]
1609
$prompt='Function '.$fn->[0];
1610
$key='func_extra_'.$fn->[1];
1611
my $qry="select ".$fn->[2]." $end_query";
1612
my $result=$fn->[3];
1613
check_and_report($prompt,$key,
1620
safe_query("drop table crash_me_t $drop_attr");
1625
# NOT id BETWEEN a and b
1626
if ($limits{'func_where_not_between'} eq 'yes')
1628
my $result = 'error';
1630
my $key='not_id_between';
1631
my $prompt='NOT ID BETWEEN interprets as ID NOT BETWEEN';
1633
save_incomplete($key,$prompt);
1634
safe_query_l($key,["create table crash_me_b (i int)",
1635
"insert into crash_me_b values(2)",
1636
"insert into crash_me_b values(5)"]);
1637
$err =safe_query_result_l($key,
1638
"select i from crash_me_b where not i between 1 and 3",
1641
if (not defined($last_result)) {
1648
safe_query_l($key,["drop table crash_me_b"]);
1649
save_config_data($key,$result,$prompt);
1656
report("LIKE on numbers","like_with_number",
1657
"create table crash_q (a int,b int)",
1658
"insert into crash_q values(10,10)",
1659
"select * from crash_q where a like '10'",
1660
"drop table crash_q $drop_attr");
1662
report("column LIKE column","like_with_column",
1663
"create table crash_q (a char(10),b char(10))",
1664
"insert into crash_q values('abc','abc')",
1665
"select * from crash_q where a like b",
1666
"drop table crash_q $drop_attr");
1668
report("update of column= -column","NEG",
1669
"create table crash_q (a integer)",
1670
"insert into crash_q values(10)",
1671
"update crash_q set a=-a",
1672
"drop table crash_q $drop_attr");
1674
if ($limits{'func_odbc_left'} eq 'yes' ||
1675
$limits{'func_odbc_substring'} eq 'yes')
1677
my $type= ($limits{'func_odbc_left'} eq 'yes' ?
1678
"left(a,4)" : "substring(a for 4)");
1680
check_and_report("String functions on date columns","date_as_string",
1681
["create table crash_me2 (a date not null)",
1682
"insert into crash_me2 values ('1998-03-03')"],
1683
"select $type from crash_me2",
1684
["drop table crash_me2 $drop_attr"],
1689
$tmp=sql_concat("b","b");
1692
check_and_report("char are space filled","char_is_space_filled",
1693
[],"select $tmp from crash_me where b = 'a '",[],
1697
if (!defined($limits{'multi_table_update'}))
1699
if (check_and_report("Update with many tables","multi_table_update",
1700
["create table crash_q (a integer,b char(10))",
1701
"insert into crash_q values(1,'c')",
1702
"update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
1703
"select b from crash_q",
1704
["drop table crash_q $drop_attr"],
1707
check_and_report("Update with many tables","multi_table_update",
1708
["create table crash_q (a integer,b char(10))",
1709
"insert into crash_q values(1,'c')",
1710
"update crash_q,crash_me set crash_q.b=crash_me.b ".
1711
"where crash_q.a=crash_me.a"],
1712
"select b from crash_q",
1713
["drop table crash_q $drop_attr"],
1719
report("DELETE FROM table1,table2...","multi_table_delete",
1720
"create table crash_q (a integer,b char(10))",
1721
"insert into crash_q values(1,'c')",
1722
"delete crash_q.* from crash_q,crash_me where crash_q.a=crash_me.a",
1723
"drop table crash_q $drop_attr");
1725
check_and_report("Update with sub select","select_table_update",
1726
["create table crash_q (a integer,b char(10))",
1727
"insert into crash_q values(1,'c')",
1728
"update crash_q set b= ".
1729
"(select b from crash_me where crash_q.a = crash_me.a)"],
1730
"select b from crash_q",
1731
["drop table crash_q $drop_attr"],
1734
check_and_report("Calculate 1--1","minus_neg",[],
1735
"select a--1 from crash_me",[],0,2);
1737
report("ANSI SQL simple joins","simple_joins",
1738
"select crash_me.a from crash_me, crash_me t0");
1741
# Check max string size, and expression limits
1744
foreach $type (('mediumtext','text','text()','blob','long'))
1746
if ($limits{"type_extra_$type"} eq 'yes')
1752
if (defined($found))
1754
$found =~ s/\(\)/\(%d\)/;
1755
find_limit("max text or blob size","max_text_size",
1756
new query_many(["create table crash_q (q $found)",
1757
"insert into crash_q values ('%s')"],
1758
"select * from crash_q","%s",
1759
["drop table crash_q $drop_attr"],
1760
min($max_string_size,$limits{'query_size'}-30)));
1764
# It doesn't make lots of sense to check for string lengths much bigger than
1765
# what can be stored...
1767
find_limit(($prompt="constant string size in where"),"where_string_size",
1768
new query_repeat([],"select a from crash_me where b >='",
1770
if ($limits{'where_string_size'} == 10)
1772
save_config_data('where_string_size','nonstandard',$prompt);
1775
if ($limits{'select_constants'} eq 'yes')
1777
find_limit("constant string size in SELECT","select_string_size",
1778
new query_repeat([],"select '","","","a","","'$end_query"));
1781
goto no_functions if ($limits{'functions'} ne "yes");
1783
if ($limits{'func_odbc_repeat'} eq 'yes')
1785
find_limit("return string size from function","repeat_string_size",
1787
"select repeat('a',%d) $end_query","%s",
1789
$max_string_size,0));
1792
$tmp=find_limit("simple expressions","max_expressions",
1793
new query_repeat([],"select 1","","","+1","",$end_query,
1794
undef(),$max_expressions));
1798
$tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
1799
find_limit("big expressions", "max_big_expressions",
1800
new query_repeat([],"select 0","","","+$tmp","",$end_query,
1801
undef(),$max_big_expressions));
1804
find_limit("stacked expressions", "max_stack_expression",
1805
new query_repeat([],"select 1","","","+(1",")",$end_query,
1806
undef(),$max_stacked_expressions));
1810
if (!defined($limits{'max_conditions'}))
1812
find_limit("OR and AND in WHERE","max_conditions",
1813
new query_repeat([],
1814
"select a from crash_me where a=1 and b='a'","",
1815
"", " or a=%d and b='%d'","","","",
1816
[],($query_size-42)/29,undef,2));
1817
$limits{'max_conditions'}*=2;
1819
# The 42 is the length of the constant part.
1820
# The 29 is the length of the variable part, plus two seven-digit numbers.
1822
find_limit("tables in join", "join_tables",
1823
new query_repeat([],
1824
"select crash_me.a",",t%d.a","from crash_me",
1825
",crash_me t%d","","",[],$max_join_tables,undef,
1828
# Different CREATE TABLE options
1830
report("primary key in create table",'primary_key_in_create',
1831
"create table crash_q (q integer not null,primary key (q))",
1832
"drop table crash_q $drop_attr");
1834
report("unique in create table",'unique_in_create',
1835
"create table crash_q (q integer not null,unique (q))",
1836
"drop table crash_q $drop_attr");
1838
if ($limits{'unique_in_create'} eq 'yes')
1840
report("unique null in create",'unique_null_in_create',
1841
"create table crash_q (q integer,unique (q))",
1842
"insert into crash_q (q) values (NULL)",
1843
"insert into crash_q (q) values (NULL)",
1844
"insert into crash_q (q) values (1)",
1845
"drop table crash_q $drop_attr");
1848
report("default value for column",'create_default',
1849
"create table crash_q (q integer default 10 not null)",
1850
"drop table crash_q $drop_attr");
1852
report("default value function for column",'create_default_func',
1853
"create table crash_q (q integer not null,q1 integer default (1+1))",
1854
"drop table crash_q $drop_attr");
1856
report("temporary tables",'temporary_table',
1857
"create temporary table crash_q (q integer not null)",
1858
"drop table crash_q $drop_attr");
1860
report_one("create table from select",'create_table_select',
1861
[["create table crash_q SELECT * from crash_me","yes"],
1862
["create table crash_q AS SELECT * from crash_me","with AS"]]);
1863
$dbh->do("drop table crash_q $drop_attr");
1865
report("index in create table",'index_in_create',
1866
"create table crash_q (q integer not null,index (q))",
1867
"drop table crash_q $drop_attr");
1869
# The following must be executed as we need the value of end_drop_keyword
1871
if (!(defined($limits{'create_index'}) && defined($limits{'drop_index'})))
1873
if ($res=safe_query_l('create_index',"create index crash_q on crash_me (a)"))
1877
$end_drop_keyword="";
1878
if (!safe_query_l('drop_index',"drop index crash_q"))
1880
# Can't drop the standard way; Check if mSQL
1881
if (safe_query_l('drop_index',"drop index crash_q from crash_me"))
1883
$drop_res="with 'FROM'"; # Drop is not ANSI SQL
1884
$end_drop_keyword="drop index %i from %t";
1886
# else check if Access or MySQL
1887
elsif (safe_query_l('drop_index',"drop index crash_q on crash_me"))
1889
$drop_res="with 'ON'"; # Drop is not ANSI SQL
1890
$end_drop_keyword="drop index %i on %t";
1892
# else check if MS-SQL
1893
elsif (safe_query_l('drop_index',"drop index crash_me.crash_q"))
1895
$drop_res="with 'table.index'"; # Drop is not ANSI SQL
1896
$end_drop_keyword="drop index %t.%i";
1901
# Old MySQL 3.21 supports only the create index syntax
1902
# This means that the second create doesn't give an error.
1903
$res=safe_query_l('create_index',["create index crash_q on crash_me (a)",
1904
"create index crash_q on crash_me (a)",
1905
"drop index crash_q"]);
1906
$res= $res ? 'ignored' : 'yes';
1911
$drop_res=$res='no';
1913
save_config_data('create_index',$res,"create index");
1914
save_config_data('drop_index',$drop_res,"drop index");
1916
print "create index: $limits{'create_index'}\n";
1917
print "drop index: $limits{'drop_index'}\n";
1920
# check if we can have 'NULL' as a key
1921
check_and_report("null in index","null_in_index",
1922
[create_table("crash_q",["a char(10)"],["(a)"]),
1923
"insert into crash_q values (NULL)"],
1924
"select * from crash_q",
1925
["drop table crash_q $drop_attr"],
1928
if ($limits{'unique_in_create'} eq 'yes')
1930
report("null in unique index",'null_in_unique',
1931
create_table("crash_q",["q integer"],["unique(q)"]),
1932
"insert into crash_q (q) values(NULL)",
1933
"insert into crash_q (q) values(NULL)",
1934
"drop table crash_q $drop_attr");
1935
report("null combination in unique index",'nulls_in_unique',
1936
create_table("crash_q",["q integer,q1 integer"],["unique(q,q1)"]),
1937
"insert into crash_q (q,q1) values(1,NULL)",
1938
"insert into crash_q (q,q1) values(1,NULL)",
1939
"drop table crash_q $drop_attr");
1942
if ($limits{'null_in_unique'} eq 'yes')
1944
report("null in unique index",'multi_null_in_unique',
1945
create_table("crash_q",["q integer, x integer"],["unique(q)"]),
1946
"insert into crash_q(x) values(1)",
1947
"insert into crash_q(x) values(2)",
1948
"drop table crash_q $drop_attr");
1951
if ($limits{'create_index'} ne 'no')
1953
$end_drop=$end_drop_keyword;
1954
$end_drop =~ s/%i/crash_q/;
1955
$end_drop =~ s/%t/crash_me/;
1956
report("index on column part (extension)","index_parts",,
1957
"create index crash_q on crash_me (b(5))",
1959
$end_drop=$end_drop_keyword;
1960
$end_drop =~ s/%i/crash_me/;
1961
$end_drop =~ s/%t/crash_me/;
1962
report("different namespace for index",
1964
"create index crash_me on crash_me (b)",
1968
if (!report("case independent table names","table_name_case",
1969
"create table crash_q (q integer)",
1970
"drop table CRASH_Q $drop_attr"))
1972
safe_query("drop table crash_q $drop_attr");
1975
if (!report("case independent field names","field_name_case",
1976
"create table crash_q (q integer)",
1977
"insert into crash_q(Q) values (1)",
1978
"drop table crash_q $drop_attr"))
1980
safe_query("drop table crash_q $drop_attr");
1983
if (!report("drop table if exists","drop_if_exists",
1984
"create table crash_q (q integer)",
1985
"drop table if exists crash_q $drop_attr"))
1987
safe_query("drop table crash_q $drop_attr");
1990
report("create table if not exists","create_if_not_exists",
1991
"create table crash_q (q integer)",
1992
"create table if not exists crash_q (q integer)");
1993
safe_query("drop table crash_q $drop_attr");
1996
# test of different join types
1999
assert("create table crash_me2 (a integer not null,b char(10) not null,".
2001
assert("insert into crash_me2 (a,b,c1) values (1,'b',1)");
2002
assert("create table crash_me3 (a integer not null,b char(10) not null)");
2003
assert("insert into crash_me3 (a,b) values (1,'b')");
2005
report("inner join","inner_join",
2006
"select crash_me.a from crash_me inner join crash_me2 ON ".
2007
"crash_me.a=crash_me2.a");
2008
report("left outer join","left_outer_join",
2009
"select crash_me.a from crash_me left join crash_me2 ON ".
2010
"crash_me.a=crash_me2.a");
2011
report("natural left outer join","natural_left_outer_join",
2012
"select c1 from crash_me natural left join crash_me2");
2013
report("left outer join using","left_outer_join_using",
2014
"select c1 from crash_me left join crash_me2 using (a)");
2015
report("left outer join odbc style","odbc_left_outer_join",
2016
"select crash_me.a from { oj crash_me left outer join crash_me2 ON".
2017
" crash_me.a=crash_me2.a }");
2018
report("right outer join","right_outer_join",
2019
"select crash_me.a from crash_me right join crash_me2 ON ".
2020
"crash_me.a=crash_me2.a");
2021
report("full outer join","full_outer_join",
2022
"select crash_me.a from crash_me full join crash_me2 ON "."
2023
crash_me.a=crash_me2.a");
2024
report("cross join (same as from a,b)","cross_join",
2025
"select crash_me.a from crash_me cross join crash_me3");
2026
report("natural join","natural_join",
2027
"select * from crash_me natural join crash_me3");
2028
report("union","union",
2029
"select * from crash_me union select a,b from crash_me3");
2030
report("union all","union_all",
2031
"select * from crash_me union all select a,b from crash_me3");
2032
report("intersect","intersect",
2033
"select * from crash_me intersect select * from crash_me3");
2034
report("intersect all","intersect_all",
2035
"select * from crash_me intersect all select * from crash_me3");
2036
report("except","except",
2037
"select * from crash_me except select * from crash_me3");
2038
report("except all","except_all",
2039
"select * from crash_me except all select * from crash_me3");
2040
report("except","except",
2041
"select * from crash_me except select * from crash_me3");
2042
report("except all","except_all",
2043
"select * from crash_me except all select * from crash_me3");
2044
report("minus","minus",
2045
"select * from crash_me minus select * from crash_me3"); # oracle ...
2047
report("natural join (incompatible lists)","natural_join_incompat",
2048
"select c1 from crash_me natural join crash_me2");
2049
report("union (incompatible lists)","union_incompat",
2050
"select * from crash_me union select a,b from crash_me2");
2051
report("union all (incompatible lists)","union_all_incompat",
2052
"select * from crash_me union all select a,b from crash_me2");
2053
report("intersect (incompatible lists)","intersect_incompat",
2054
"select * from crash_me intersect select * from crash_me2");
2055
report("intersect all (incompatible lists)","intersect_all_incompat",
2056
"select * from crash_me intersect all select * from crash_me2");
2057
report("except (incompatible lists)","except_incompat",
2058
"select * from crash_me except select * from crash_me2");
2059
report("except all (incompatible lists)","except_all_incompat",
2060
"select * from crash_me except all select * from crash_me2");
2061
report("except (incompatible lists)","except_incompat",
2062
"select * from crash_me except select * from crash_me2");
2063
report("except all (incompatible lists)","except_all_incompat",
2064
"select * from crash_me except all select * from crash_me2");
2065
report("minus (incompatible lists)","minus_incompat",
2066
"select * from crash_me minus select * from crash_me2"); # oracle ...
2068
assert("drop table crash_me2 $drop_attr");
2069
assert("drop table crash_me3 $drop_attr");
2071
# somethings to be added here ....
2072
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
2074
# >ALL | ANY | SOME - EXISTS - UNIQUE
2076
if (report("subqueries","subqueries",
2077
"select a from crash_me where crash_me.a in ".
2078
"(select max(a) from crash_me)"))
2080
$tmp=new query_repeat([],"select a from crash_me","","",
2081
" where a in (select a from crash_me",")",
2082
"",[],$max_join_tables);
2083
find_limit("recursive subqueries", "recursive_subqueries",$tmp);
2086
report("insert INTO ... SELECT ...","insert_select",
2087
"create table crash_q (a int)",
2088
"insert into crash_q (a) SELECT crash_me.a from crash_me",
2089
"drop table crash_q $drop_attr");
2091
if (!defined($limits{"transactions"}))
2094
$limit="transactions";
2095
$limit_r="rollback_metadata";
2097
foreach $type (('', 'type=bdb', 'type=innodb', 'type=gemini'))
2099
undef($limits{$limit});
2100
if (!report_trans($limit,
2101
[create_table("crash_q",["a integer not null"],[],
2103
"insert into crash_q values (1)"],
2104
"select * from crash_q",
2105
"drop table crash_q $drop_attr"
2108
report_rollback($limit_r,
2109
[create_table("crash_q",["a integer not null"],[],
2111
"insert into crash_q values (1)",
2112
"drop table crash_q $drop_attr" );
2116
print "$limits{$limit}\n";
2117
print "$limit_r: $limits{$limit_r}\n";
2120
report("atomic updates","atomic_updates",
2121
create_table("crash_q",["a integer not null"],["primary key (a)"]),
2122
"insert into crash_q values (2)",
2123
"insert into crash_q values (3)",
2124
"insert into crash_q values (1)",
2125
"update crash_q set a=a+1",
2126
"drop table crash_q $drop_attr");
2128
if ($limits{'atomic_updates'} eq 'yes')
2130
report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
2131
create_table("crash_q",["a integer not null"],
2132
["primary key (a)"]),
2133
"insert into crash_q values (2)",
2134
"insert into crash_q values (3)",
2135
"insert into crash_q values (1)",
2136
"update crash_q set a=a+1 where a < 3",
2137
"drop table crash_q $drop_attr");
2140
# To add with the views:
2141
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
2142
report("views","views",
2143
"create view crash_q as select a from crash_me",
2144
"drop view crash_q $drop_attr");
2148
my $result = 'undefined';
2150
print "foreign keys: ";
2151
save_incomplete('foreign_key','foreign keys');
2153
# 1) check if foreign keys are supported
2154
safe_query_l('foreign_key',
2155
create_table("crash_me_qf",
2156
["a integer not null"],
2157
["primary key (a)"]));
2158
$error= safe_query_l('foreign_key',
2159
create_table("crash_me_qf2",
2160
["a integer not null",
2161
"foreign key (a) references crash_me_qf (a)"],
2164
if ($error == 1) # OK -- syntax is supported
2167
# now check if foreign key really works
2168
safe_query_l('foreign_key', "insert into crash_me_qf values (1)");
2169
if (safe_query_l('foreign_key', "insert into crash_me_qf2 values (2)") eq 1)
2171
$result = 'syntax only';
2182
safe_query_l('foreign_key', "drop table crash_me_qf2 $drop_attr");
2183
safe_query_l('foreign_key', "drop table crash_me_qf $drop_attr");
2185
save_config_data('foreign_key',$result,"foreign keys");
2188
if ($limits{'foreign_key'} eq 'yes')
2190
report("allows to update of foreign key values",'foreign_update',
2191
"create table crash_me1 (a int not null primary key)",
2192
"create table crash_me2 (a int not null," .
2193
" foreign key (a) references crash_me1 (a))",
2194
"insert into crash_me1 values (1)",
2195
"insert into crash_me2 values (1)",
2196
"update crash_me1 set a = 2", ## <- must fail
2197
"drop table crash_me2 $drop_attr",
2198
"drop table crash_me1 $drop_attr"
2202
report("Create SCHEMA","create_schema",
2203
"create schema crash_schema create table crash_q (a int) ".
2204
"create table crash_q2(b int)",
2205
"drop schema crash_schema cascade");
2207
if ($limits{'foreign_key'} eq 'yes')
2209
if ($limits{'create_schema'} eq 'yes')
2211
report("Circular foreign keys","foreign_key_circular",
2212
"create schema crash_schema create table crash_q ".
2213
"(a int primary key, b int, foreign key (b) references ".
2214
"crash_q2(a)) create table crash_q2(a int, b int, ".
2215
"primary key(a), foreign key (b) references crash_q(a))",
2216
"drop schema crash_schema cascade");
2220
if ($limits{'func_sql_character_length'} eq 'yes')
2222
my $result = 'error';
2224
my $key = 'length_of_varchar_field';
2225
my $prompt='CHARACTER_LENGTH(varchar_field)';
2226
print $prompt," = ";
2227
if (!defined($limits{$key})) {
2228
save_incomplete($key,$prompt);
2230
"CREATE TABLE crash_me1 (S1 VARCHAR(100))",
2231
"INSERT INTO crash_me1 VALUES ('X')"
2233
my $recset = get_recordset($key,
2234
"SELECT CHARACTER_LENGTH(S1) FROM crash_me1");
2235
print_recordset($key,$recset);
2236
if (defined($recset)){
2237
if ( $recset->[0][0] eq 1 ) {
2238
$result = 'actual length';
2239
} elsif( $recset->[0][0] eq 100 ) {
2240
$result = 'defined length';
2243
add_log($key,$DBI::errstr);
2245
safe_query_l($key, "drop table crash_me1 $drop_attr");
2246
save_config_data($key,$result,$prompt);
2248
$result = $limits{$key};
2254
check_constraint("Column constraints","constraint_check",
2255
"create table crash_q (a int check (a>0))",
2256
"insert into crash_q values(0)",
2257
"drop table crash_q $drop_attr");
2260
check_constraint("Table constraints","constraint_check_table",
2261
"create table crash_q (a int ,b int, check (a>b))",
2262
"insert into crash_q values(0,0)",
2263
"drop table crash_q $drop_attr");
2265
check_constraint("Named constraints","constraint_check_named",
2266
"create table crash_q (a int ,b int, constraint abc check (a>b))",
2267
"insert into crash_q values(0,0)",
2268
"drop table crash_q $drop_attr");
2271
report("NULL constraint (SyBase style)","constraint_null",
2272
"create table crash_q (a int null)",
2273
"drop table crash_q $drop_attr");
2275
report("Triggers (ANSI SQL)","psm_trigger",
2276
"create table crash_q (a int ,b int)",
2277
"create trigger crash_trigger after insert on crash_q referencing ".
2278
"new table as new_a when (localtime > time '18:00:00') ".
2280
"insert into crash_q values(1,2)",
2281
"drop trigger crash_trigger",
2282
"drop table crash_q $drop_attr");
2284
report("PSM procedures (ANSI SQL)","psm_procedures",
2285
"create table crash_q (a int,b int)",
2286
"create procedure crash_proc(in a1 int, in b1 int) language ".
2287
"sql modifies sql data begin declare c1 int; set c1 = a1 + b1;".
2288
" insert into crash_q(a,b) values (a1,c1); end",
2289
"call crash_proc(1,10)",
2290
"drop procedure crash_proc",
2291
"drop table crash_q $drop_attr");
2293
report("PSM modules (ANSI SQL)","psm_modules",
2294
"create table crash_q (a int,b int)",
2295
"create module crash_m declare procedure ".
2296
"crash_proc(in a1 int, in b1 int) language sql modifies sql ".
2297
"data begin declare c1 int; set c1 = a1 + b1; ".
2298
"insert into crash_q(a,b) values (a1,c1); end; ".
2299
"declare procedure crash_proc2(INOUT a int, in b int) ".
2300
"contains sql set a = b + 10; end module",
2301
"call crash_proc(1,10)",
2302
"drop module crash_m cascade",
2303
"drop table crash_q cascade $drop_attr");
2305
report("PSM functions (ANSI SQL)","psm_functions",
2306
"create table crash_q (a int)",
2307
"create function crash_func(in a1 int, in b1 int) returns int".
2308
" language sql deterministic contains sql ".
2309
" begin return a1 * b1; end",
2310
"insert into crash_q values(crash_func(2,4))",
2311
"select a,crash_func(a,2) from crash_q",
2312
"drop function crash_func cascade",
2313
"drop table crash_q $drop_attr");
2315
report("Domains (ANSI SQL)","domains",
2316
"create domain crash_d as varchar(10) default 'Empty' ".
2317
"check (value <> 'abcd')",
2318
"create table crash_q(a crash_d, b int)",
2319
"insert into crash_q(a,b) values('xyz',10)",
2320
"insert into crash_q(b) values(10)",
2321
"drop table crash_q $drop_attr",
2322
"drop domain crash_d");
2325
if (!defined($limits{'lock_tables'}))
2327
report("lock table","lock_tables",
2328
"lock table crash_me READ",
2330
if ($limits{'lock_tables'} eq 'no')
2332
delete $limits{'lock_tables'};
2333
report("lock table","lock_tables",
2334
"lock table crash_me IN SHARE MODE");
2338
if (!report("many tables to drop table","multi_drop",
2339
"create table crash_q (a int)",
2340
"create table crash_q2 (a int)",
2341
"drop table crash_q,crash_q2 $drop_attr"))
2343
$dbh->do("drop table crash_q $drop_attr");
2344
$dbh->do("drop table crash_q2 $drop_attr");
2347
if (!report("drop table with cascade/restrict","drop_restrict",
2348
"create table crash_q (a int)",
2349
"drop table crash_q restrict"))
2351
$dbh->do("drop table crash_q $drop_attr");
2355
report("-- as comment (ANSI)","comment_--",
2356
"select * from crash_me -- Testing of comments");
2357
report("// as comment","comment_//",
2358
"select * from crash_me // Testing of comments");
2359
report("# as comment","comment_#",
2360
"select * from crash_me # Testing of comments");
2361
report("/* */ as comment","comment_/**/",
2362
"select * from crash_me /* Testing of comments */");
2365
# Check things that fails one some servers
2368
# Empress can't insert empty strings in a char() field
2369
report("insert empty string","insert_empty_string",
2370
create_table("crash_q",["a char(10) not null,b char(10)"],[]),
2371
"insert into crash_q values ('','')",
2372
"drop table crash_q $drop_attr");
2374
report("Having with alias","having_with_alias",
2375
create_table("crash_q",["a integer"],[]),
2376
"insert into crash_q values (10)",
2377
"select sum(a) as b from crash_q group by a having b > 0",
2378
"drop table crash_q $drop_attr");
2384
find_limit("table name length","max_table_name",
2385
new query_many(["create table crash_q%s (q integer)",
2386
"insert into crash_q%s values(1)"],
2387
"select * from crash_q%s",1,
2388
["drop table crash_q%s $drop_attr"],
2389
$max_name_length,7,1));
2391
find_limit("column name length","max_column_name",
2392
new query_many(["create table crash_q (q%s integer)",
2393
"insert into crash_q (q%s) values(1)"],
2394
"select q%s from crash_q",1,
2395
["drop table crash_q $drop_attr"],
2396
$max_name_length,1));
2398
if ($limits{'column_alias'} eq 'yes')
2400
find_limit("select alias name length","max_select_alias_name",
2401
new query_many(undef,
2402
"select b as %s from crash_me",undef,
2403
undef, $max_name_length));
2406
find_limit("table alias name length","max_table_alias_name",
2407
new query_many(undef,
2408
"select %s.b from crash_me %s",
2410
undef, $max_name_length));
2412
$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
2413
$end_drop=$end_drop_keyword;
2414
$end_drop =~ s/%i/crash_q%s/;
2415
$end_drop =~ s/%t/crash_me/;
2417
if ($limits{'create_index'} ne 'no')
2419
find_limit("index name length","max_index_name",
2420
new query_many(["create index crash_q%s on crash_me (a)"],
2423
$max_name_length,7));
2426
find_limit("max char() size","max_char_size",
2427
new query_many(["create table crash_q (q char(%d))",
2428
"insert into crash_q values ('%s')"],
2429
"select * from crash_q","%s",
2430
["drop table crash_q $drop_attr"],
2431
min($max_string_size,$limits{'query_size'})));
2433
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2435
find_limit("max varchar() size","max_varchar_size",
2436
new query_many(["create table crash_q (q varchar(%d))",
2437
"insert into crash_q values ('%s')"],
2438
"select * from crash_q","%s",
2439
["drop table crash_q $drop_attr"],
2440
min($max_string_size,$limits{'query_size'})));
2444
foreach $type (('mediumtext','text','text()','blob','long'))
2446
if ($limits{"type_extra_$type"} eq 'yes')
2452
if (defined($found))
2454
$found =~ s/\(\)/\(%d\)/;
2455
find_limit("max text or blob size","max_text_size",
2456
new query_many(["create table crash_q (q $found)",
2457
"insert into crash_q values ('%s')"],
2458
"select * from crash_q","%s",
2459
["drop table crash_q $drop_attr"],
2460
min($max_string_size,$limits{'query_size'}-30)));
2464
$tmp=new query_repeat([],"create table crash_q (a integer","","",
2465
",a%d integer","",")",["drop table crash_q $drop_attr"],
2468
find_limit("Columns in table","max_columns",$tmp);
2470
# Make a field definition to be used when testing keys
2472
$key_definitions="q0 integer not null";
2474
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2476
$key_definitions.=",q$i integer not null";
2477
$key_fields.=",q$i";
2479
$key_values="1," x $i;
2482
if ($limits{'unique_in_create'} eq 'yes')
2484
find_limit("unique indexes","max_unique_index",
2485
new query_table("create table crash_q (q integer",
2486
",q%d integer not null,unique (q%d)",")",
2487
["insert into crash_q (q,%f) values (1,%v)"],
2488
"select q from crash_q",1,
2489
"drop table crash_q $drop_attr",
2492
find_limit("index parts","max_index_parts",
2493
new query_table("create table crash_q ".
2494
"($key_definitions,unique (q0",
2496
["insert into crash_q ($key_fields) values ($key_values)"],
2497
"select q0 from crash_q",1,
2498
"drop table crash_q $drop_attr",
2501
find_limit("max index part length","max_index_part_length",
2502
new query_many(["create table crash_q (q char(%d) not null,".
2504
"insert into crash_q (q) values ('%s')"],
2505
"select q from crash_q","%s",
2506
["drop table crash_q $drop_attr"],
2507
$limits{'max_char_size'},0));
2509
if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
2511
find_limit("index varchar part length","max_index_varchar_part_length",
2512
new query_many(["create table crash_q (q varchar(%d) not null,".
2514
"insert into crash_q (q) values ('%s')"],
2515
"select q from crash_q","%s",
2516
["drop table crash_q $drop_attr"],
2517
$limits{'max_varchar_size'},0));
2522
if ($limits{'create_index'} ne 'no')
2524
if ($limits{'create_index'} eq 'ignored' ||
2525
$limits{'unique_in_create'} eq 'yes')
2526
{ # This should be true
2527
add_log('max_index',
2528
" max_unique_index=$limits{'max_unique_index'} ,".
2529
"so max_index must be same");
2530
save_config_data('max_index',$limits{'max_unique_index'},"max index");
2531
print "indexes: $limits{'max_index'}\n";
2535
if (!defined($limits{'max_index'}))
2537
safe_query_l('max_index',"create table crash_q ($key_definitions)");
2538
for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
2540
last if (!safe_query_l('max_index',
2541
"create index crash_q$i on crash_q (q$i)"));
2543
save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
2547
$end_drop=$end_drop_keyword;
2548
$end_drop =~ s/%i/crash_q$i/;
2549
$end_drop =~ s/%t/crash_q/;
2552
assert("drop table crash_q $drop_attr");
2554
print "indexs: $limits{'max_index'}\n";
2555
if (!defined($limits{'max_unique_index'}))
2557
safe_query_l('max_unique_index',
2558
"create table crash_q ($key_definitions)");
2559
for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
2561
last if (!safe_query_l('max_unique_index',
2562
"create unique index crash_q$i on crash_q (q$i)"));
2564
save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
2565
"max unique index");
2568
$end_drop=$end_drop_keyword;
2569
$end_drop =~ s/%i/crash_q$i/;
2570
$end_drop =~ s/%t/crash_q/;
2573
assert("drop table crash_q $drop_attr");
2575
print "unique indexes: $limits{'max_unique_index'}\n";
2576
if (!defined($limits{'max_index_parts'}))
2578
safe_query_l('max_index_parts',
2579
"create table crash_q ($key_definitions)");
2580
$end_drop=$end_drop_keyword;
2581
$end_drop =~ s/%i/crash_q1%d/;
2582
$end_drop =~ s/%t/crash_q/;
2583
find_limit("index parts","max_index_parts",
2584
new query_table("create index crash_q1%d on crash_q (q0",
2590
assert("drop table crash_q $drop_attr");
2594
print "index parts: $limits{'max_index_parts'}\n";
2596
$end_drop=$end_drop_keyword;
2597
$end_drop =~ s/%i/crash_q2%d/;
2598
$end_drop =~ s/%t/crash_me/;
2600
find_limit("index part length","max_index_part_length",
2601
new query_many(["create table crash_q (q char(%d))",
2602
"create index crash_q2%d on crash_q (q)",
2603
"insert into crash_q values('%s')"],
2604
"select q from crash_q",
2607
"drop table crash_q $drop_attr"],
2608
min($limits{'max_char_size'},"+8192")));
2612
find_limit("index length","max_index_length",
2613
new query_index_length("create table crash_q ",
2614
"drop table crash_q $drop_attr",
2617
find_limit("max table row length (without blobs)","max_row_length",
2618
new query_row_length("crash_q ",
2620
"drop table crash_q $drop_attr",
2621
min($max_row_length,
2622
$limits{'max_columns'}*
2623
min($limits{'max_char_size'},255))));
2625
find_limit("table row length with nulls (without blobs)",
2626
"max_row_length_with_null",
2627
new query_row_length("crash_q ",
2629
"drop table crash_q $drop_attr",
2630
$limits{'max_row_length'}*2));
2632
find_limit("number of columns in order by","columns_in_order_by",
2633
new query_many(["create table crash_q (%F)",
2634
"insert into crash_q values(%v)",
2635
"insert into crash_q values(%v)"],
2636
"select * from crash_q order by %f",
2638
["drop table crash_q $drop_attr"],
2641
find_limit("number of columns in group by","columns_in_group_by",
2642
new query_many(["create table crash_q (%F)",
2643
"insert into crash_q values(%v)",
2644
"insert into crash_q values(%v)"],
2645
"select %f from crash_q group by %f",
2647
["drop table crash_q $drop_attr"],
2652
# Safe arithmetic test
2654
$prompt="safe decimal arithmetic";
2655
$key="safe_decimal_arithmetic";
2656
if (!defined($limits{$key}))
2659
save_incomplete($key,$prompt);
2660
if (!safe_query_l($key,$server->create("crash_me_a",
2661
["a decimal(10,2)","b decimal(10,2)"])))
2663
print DBI->errstr();
2664
die "Can't create table 'crash_me_a' $DBI::errstr\n";
2667
if (!safe_query_l($key,
2668
["insert into crash_me_a (a,b) values (11.4,18.9)"]))
2670
die "Can't insert into table 'crash_me_a' a record: $DBI::errstr\n";
2673
$arithmetic_safe = 'no';
2674
$arithmetic_safe = 'yes'
2675
if ( (safe_query_result_l($key,
2676
'select count(*) from crash_me_a where a+b=30.3',1,0) == 0)
2677
and (safe_query_result_l($key,
2678
'select count(*) from crash_me_a where a+b-30.3 = 0',1,0) == 0)
2679
and (safe_query_result_l($key,
2680
'select count(*) from crash_me_a where a+b-30.3 < 0',0,0) == 0)
2681
and (safe_query_result_l($key,
2682
'select count(*) from crash_me_a where a+b-30.3 > 0',0,0) == 0));
2683
save_config_data($key,$arithmetic_safe,$prompt);
2684
print "$arithmetic_safe\n";
2685
assert("drop table crash_me_a $drop_attr");
2689
print "$prompt=$limits{$key} (cached)\n";
2692
# Check where is null values in sorted recordset
2693
if (!safe_query($server->create("crash_me_n",["i integer","r integer"])))
2695
print DBI->errstr();
2696
die "Can't create table 'crash_me_n' $DBI::errstr\n";
2699
safe_query_l("position_of_null",["insert into crash_me_n (i) values(1)",
2700
"insert into crash_me_n values(2,2)",
2701
"insert into crash_me_n values(3,3)",
2702
"insert into crash_me_n values(4,4)",
2703
"insert into crash_me_n (i) values(5)"]);
2705
$key = "position_of_null";
2706
$prompt ="Where is null values in sorted recordset";
2707
if (!defined($limits{$key}))
2709
save_incomplete($key,$prompt);
2711
$sth=$dbh->prepare("select r from crash_me_n order by r ");
2713
add_log($key,"< select r from crash_me_n order by r ");
2714
$limit= detect_null_position($key,$sth);
2717
save_config_data($key,$limit,$prompt);
2719
print "$prompt=$limits{$key} (cache)\n";
2722
$key = "position_of_null_desc";
2723
$prompt ="Where is null values in sorted recordset (DESC)";
2724
if (!defined($limits{$key}))
2726
save_incomplete($key,$prompt);
2728
$sth=$dbh->prepare("select r from crash_me_n order by r desc");
2730
add_log($key,"< select r from crash_me_n order by r desc");
2731
$limit= detect_null_position($key,$sth);
2734
save_config_data($key,$limit,$prompt);
2736
print "$prompt=$limits{$key} (cache)\n";
2740
assert("drop table crash_me_n $drop_attr");
2744
$key = 'sorted_group_by';
2745
$prompt = 'Group by always sorted';
2746
if (!defined($limits{$key}))
2748
save_incomplete($key,$prompt);
2751
"create table crash_me_t1 (a int not null, b int not null)",
2752
"insert into crash_me_t1 values (1,1)",
2753
"insert into crash_me_t1 values (1,2)",
2754
"insert into crash_me_t1 values (3,1)",
2755
"insert into crash_me_t1 values (3,2)",
2756
"insert into crash_me_t1 values (2,2)",
2757
"insert into crash_me_t1 values (2,1)",
2758
"create table crash_me_t2 (a int not null, b int not null)",
2759
"create index crash_me_t2_ind on crash_me_t2 (a)",
2760
"insert into crash_me_t2 values (1,3)",
2761
"insert into crash_me_t2 values (3,1)",
2762
"insert into crash_me_t2 values (2,2)",
2763
"insert into crash_me_t2 values (1,1)"]);
2765
my $bigqry = "select crash_me_t1.a,crash_me_t2.b from ".
2766
"crash_me_t1,crash_me_t2 where crash_me_t1.a=crash_me_t2.a ".
2767
"group by crash_me_t1.a,crash_me_t2.b";
2770
my $rs = get_recordset($key,$bigqry);
2771
print_recordset($key,$rs);
2772
if ( defined ($rs)) {
2773
if (compare_recordset($key,$rs,[[1,1],[1,3],[2,2],[3,1]]) eq 0)
2778
add_log($key,"error: ".$DBI::errstr);
2782
safe_query_l($key,["drop table crash_me_t1",
2783
"drop table crash_me_t2"]);
2784
save_config_data($key,$limit,$prompt);
2787
print "$prompt=$limits{$key} (cashed)\n";
2795
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2797
print "crash-me safe: $limits{'crash_me_safe'}\n";
2798
print "reconnected $reconnect_count times\n";
2800
$dbh->disconnect || warn $dbh->errstr;
2801
save_all_config_data();
2807
$dbh->do("drop table crash_me $drop_attr"); # Remove temporary table
2809
print "crash-me safe: $limits{'crash_me_safe'}\n";
2810
print "reconnected $reconnect_count times\n";
2812
$dbh->disconnect || warn $dbh->errstr;
2813
save_all_config_data();
2816
# Check where is nulls in the sorted result (for)
2817
# it expects exactly 5 rows in the result
2819
sub detect_null_position
2823
my ($z,$r1,$r2,$r3,$r4,$r5);
2824
$r1 = $sth->fetchrow_array; add_log($key,"> $r1");
2825
$r2 = $sth->fetchrow_array; add_log($key,"> $r2");
2826
$r3 = $sth->fetchrow_array; add_log($key,"> $r3");
2827
$r4 = $sth->fetchrow_array; add_log($key,"> $r4");
2828
$r5 = $sth->fetchrow_array; add_log($key,"> $r5");
2829
return "first" if ( !defined($r1) && !defined($r2) && defined($r3));
2830
return "last" if ( !defined($r5) && !defined($r4) && defined($r3));
2834
sub check_parenthesis {
2838
my $param_name=$prefix.lc($fn);
2841
save_incomplete($param_name,$fn);
2842
$r = safe_query("select $fn $end_query");
2843
add_log($param_name,$safe_query_log);
2849
$r = safe_query("select $fn() $end_query");
2850
add_log($param_name,$safe_query_log);
2853
$result="with_parenthesis";
2857
save_config_data($param_name,$result,$fn);
2860
sub check_constraint {
2866
save_incomplete($key,$prompt);
2870
$t=safe_query($create);
2871
add_log($key,$safe_query_log);
2875
$t= safe_query($check);
2876
add_log($key,$safe_query_log);
2883
add_log($key,$safe_query_log);
2885
save_config_data($key,$res,$prompt);
2893
$_ = $limits{'time_format_inresult'};
2894
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second if (/^iso$/);
2895
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second if (/^euro/);
2896
return sprintf "%02d:%02d %s",
2897
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
2899
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second if (/^HHMMSS/);
2900
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second if (/^HHHHMMSS/);
2901
return "UNKNOWN FORMAT";
2908
return sprintf "%02d:%02d:%02d", ($hour%24),$minute,$second
2909
if ($limits{'time_format_ISO'} eq "yes");
2910
return sprintf "%02d.%02d.%02d", ($hour%24),$minute,$second
2911
if ($limits{'time_format_EUR'} eq "yes");
2912
return sprintf "%02d:%02d %s",
2913
($hour >= 13? ($hour-12) : $hour),$minute,($hour >=13 ? 'PM':'AM')
2914
if ($limits{'time_format_USA'} eq "yes");
2915
return sprintf "%02d%02d%02d", ($hour%24),$minute,$second
2916
if ($limits{'time_format_HHMMSS'} eq "yes");
2917
return sprintf "%04d%02d%02d", ($hour%24),$minute,$second
2918
if ($limits{'time_format_HHHHMMSS'} eq "yes");
2919
return "UNKNOWN FORMAT";
2926
$_ = $limits{'date_format_inresult'};
2927
return sprintf "%02d-%02d-%02d", ($year%100),$month,$day if (/^short iso$/);
2928
return sprintf "%04d-%02d-%02d", $year,$month,$day if (/^iso/);
2929
return sprintf "%02d.%02d.%02d", $day,$month,($year%100) if (/^short euro/);
2930
return sprintf "%02d.%02d.%04d", $day,$month,$year if (/^euro/);
2931
return sprintf "%02d/%02d/%02d", $month,$day,($year%100) if (/^short usa/);
2932
return sprintf "%02d/%02d/%04d", $month,$day,$year if (/^usa/);
2933
return sprintf "%04d%02d%02d", $year,$month,$day if (/^YYYYMMDD/);
2934
return "UNKNOWN FORMAT";
2942
return sprintf "'%04d-%02d-%02d'", $year,$month,$day
2943
if ($limits{'date_format_ISO'} eq yes);
2944
return sprintf "DATE '%04d-%02d-%02d'", $year,$month,$day
2945
if ($limits{'date_format_ISO_with_date'} eq yes);
2946
return sprintf "'%02d.%02d.%04d'", $day,$month,$year
2947
if ($limits{'date_format_EUR'} eq 'yes');
2948
return sprintf "DATE '%02d.%02d.%04d'", $day,$month,$year
2949
if ($limits{'date_format_EUR_with_date'} eq 'yes');
2950
return sprintf "'%02d/%02d/%04d'", $month,$day,$year
2951
if ($limits{'date_format_USA'} eq 'yes');
2952
return sprintf "DATE '%02d/%02d/%04d'", $month,$day,$year
2953
if ($limits{'date_format_USA_with_date'} eq 'yes');
2954
return sprintf "'%04d%02d%02d'", $year,$month,$day
2955
if ($limits{'date_format_YYYYMMDD'} eq 'yes');
2956
return sprintf "DATE '%04d%02d%02d'", $year,$month,$day
2957
if ($limits{'date_format_YYYYMMDD_with_date'} eq 'yes');
2958
return "UNKNOWN FORMAT";
2962
sub print_recordset{
2963
my ($key,$recset) = @_;
2965
foreach $rec (@$recset)
2967
add_log($key, " > ".join(',', map(repr($_), @$rec)));
2972
# read result recordset from sql server.
2973
# returns arrayref to (arrayref to) values
2974
# or undef (in case of sql errors)
2977
my ($key,$query) = @_;
2978
add_log($key, "< $query");
2979
return $dbh->selectall_arrayref($query);
2982
# function for comparing recordset (that was returned by get_recordset)
2983
# and arrayref of (arrayref of) values.
2985
# returns : zero if recordset equal that array, 1 if it doesn't equal
2988
# $key - current operation (for logging)
2989
# $recset - recordset
2990
# $mustbe - array of values that we expect
2992
# example: $a=get_recordset('some_parameter','select a,b from c');
2993
# if (compare_recordset('some_parameter',$a,[[1,1],[1,2],[1,3]]) neq 0)
2995
# print "unexpected result\n";
2998
sub compare_recordset {
2999
my ($key,$recset,$mustbe) = @_;
3000
my $rec,$recno,$fld,$fldno,$fcount;
3001
add_log($key,"\n Check recordset:");
3003
foreach $rec (@$recset)
3005
add_log($key," " . join(',', map(repr($_),@$rec)) . " expected: " .
3006
join(',', map(repr($_), @{$mustbe->[$recno]} ) ));
3009
foreach $fldno (0 .. $fcount )
3011
if ($mustbe->[$recno][$fldno] ne $rec->[$fldno])
3013
add_log($key," Recordset doesn't correspond with template");
3019
add_log($key," Recordset corresponds with template");
3024
# converts inner perl value to printable representation
3025
# for example: undef maps to 'NULL',
3026
# string -> 'string'
3031
return "'$s'"if ($s =~ /\D/);
3032
return 'NULL'if ( not defined($s));
3039
print "$0 Ver $version\n";
3048
This program tries to find all limits and capabilities for a SQL
3049
server. As it will use the server in some 'unexpected' ways, one
3050
shouldn\'t have anything important running on it at the same time this
3051
program runs! There is a slight chance that something unexpected may
3054
As all used queries are legal according to some SQL standard. any
3055
reasonable SQL server should be able to run this test without any
3058
All questions is cached in $opt_dir/'server_name'[-suffix].cfg that
3059
future runs will use limits found in previous runs. Remove this file
3060
if you want to find the current limits for your version of the
3063
This program uses some table names while testing things. If you have any
3064
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
3065
they will be deleted by this test!
3067
$0 takes the following options:
3069
--help or --Information
3073
Don\'t ask any questions, quit on errors.
3075
--config-file='filename'
3076
Read limit results from specific file
3078
--comment='some comment'
3079
Add this comment to the crash-me limit file
3082
Do a new connection to the server every time crash-me checks if the server
3083
is alive. This can help in cases where the server starts returning wrong
3084
data because of an earlier select.
3086
--database='database' (Default $opt_database)
3087
Create test tables in this database.
3090
Save crash-me output in this directory
3093
Lots of printing to help debugging if something goes wrong.
3096
Reformat the crash-me limit file. crash-me is not run!
3099
Start test at once, without a warning screen and without questions.
3100
This is a option for the very brave.
3101
Use this in your cron scripts to test your database every night.
3104
Prints all queries that are executed. Mostly used for debugging crash-me.
3106
--log-queries-to-file='filename'
3107
Log full queries to file.
3109
--host='hostname' (Default $opt_host)
3110
Run tests on this host.
3112
--password='password'
3113
Password for the current user.
3116
Save states during each limit tests. This will make it possible to continue
3117
by restarting with the same options if there is some bug in the DBI or
3118
DBD driver that caused $0 to die!
3120
--server='server name' (Default $opt_server)
3121
Run the test on the given server.
3122
Known servers names are: Access, Adabas, AdabasD, Empress, Oracle,
3123
Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid or Sybase.
3124
For others $0 can\'t report the server version.
3126
--suffix='suffix' (Default '')
3127
Add suffix to the output filename. For instance if you run crash-me like
3128
"crash-me --suffix="myisam",
3129
then output filename will look "mysql-myisam.cfg".
3132
User name to log into the SQL server.
3134
--db-start-cmd='command to restart server'
3135
Automaticly restarts server with this command if the database server dies.
3137
--sleep='time in seconds' (Default $opt_sleep)
3138
Wait this long before restarting server.
3141
Log into the result file queries performed for determination parameter value
3142
This causes rows starting with ' ###' to be logged into the .cnf file
3145
Don't log '###' quries to the .cnf file.
3156
print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
3157
if (lc($opt_server) eq "mysql")
3161
This test should not crash MySQL if it was distributed together with the
3162
running MySQL version.
3163
If this is the case you can probably continue without having to worry about
3164
destroying something.
3167
elsif (lc($opt_server) eq "msql")
3170
This test will take down mSQL repeatedly while finding limits.
3171
To make this test easier, start mSQL in another terminal with something like:
3173
while (true); do /usr/local/mSQL/bin/msql2d ; done
3175
You should be sure that no one is doing anything important with mSQL and that
3176
you have privileges to restart it!
3177
It may take awhile to determinate the number of joinable tables, so prepare to
3181
elsif (lc($opt_server) eq "solid")
3184
This test will take down Solid server repeatedly while finding limits.
3185
You should be sure that no one is doing anything important with Solid
3186
and that you have privileges to restart it!
3188
If you are running Solid without logging and/or backup YOU WILL LOSE!
3189
Solid does not write data from the cache often enough. So if you continue
3190
you may lose tables and data that you entered hours ago!
3192
Solid will also take a lot of memory running this test. You will nead
3195
When doing the connect test Solid server or the perl api will hang when
3196
freeing connections. Kill this program and restart it to continue with the
3197
test. You don\'t have to use --restart for this case.
3201
print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
3202
print "with --restart until all tests have completed\n";
3205
elsif (lc($opt_server) eq "pg")
3208
This test will crash postgreSQL when calculating the number of joinable tables!
3209
You should be sure that no one is doing anything important with postgreSQL
3210
and that you have privileges to restart it!
3216
This test may crash $opt_server repeatedly while finding limits!
3217
You should be sure that no one is doing anything important with $opt_server
3218
and that you have privileges to restart it!
3223
Some of the tests you are about to execute may require a lot of
3224
memory. Your tests WILL adversely affect system performance. It\'s
3225
not uncommon that either this crash-me test program, or the actual
3226
database back-end, will DIE with an out-of-memory error. So might
3227
any other program on your system if it requests more memory at the
3230
Note also that while crash-me tries to find limits for the database server
3231
it will make a lot of queries that can\'t be categorized as \'normal\'. It\'s
3232
not unlikely that crash-me finds some limit bug in your server so if you
3233
run this test you have to be prepared that your server may die during it!
3235
We, the creators of this utility, are not responsible in any way if your
3236
database server unexpectedly crashes while this program tries to find the
3237
limitations of your server. By accepting the following question with \'yes\',
3238
you agree to the above!
3240
You have been warned!
3245
# No default reply here so no one can blame us for starting the test
3250
print "Start test (yes/no) ? ";
3251
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3252
last if ($tmp =~ /^yes$/i);
3253
exit 1 if ($tmp =~ /^n/i);
3260
my @name = POSIX::uname();
3261
my $name= $name[0] . " " . $name[2] . " " . $name[4];
3267
# Help functions that we need
3277
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3278
{ PrintError => 0, AutoCommit => 1})))
3280
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3283
print "Error: $DBI::errstr; $server->{'data_source'} ".
3284
" - '$opt_user' - '$opt_password'\n";
3285
print "I got the above error when connecting to $opt_server\n";
3286
if (defined($object) && defined($object->{'limit'}))
3288
print "This check was done with limit: $object->{'limit'}.".
3289
"\nNext check will be done with a smaller limit!\n";
3292
save_config_data('crash_me_safe','no',"crash me safe");
3293
if ($opt_db_start_cmd)
3295
print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
3296
system("$opt_db_start_cmd");
3297
print "Waiting $opt_sleep seconds so the server can initialize\n";
3302
exit(1) if ($opt_batch_mode);
3303
print "Can you check/restart it so I can continue testing?\n";
3306
print "Continue test (yes/no) ? [yes] ";
3307
$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
3308
$tmp = "yes" if ($tmp eq "");
3309
last if (index("yes",$tmp) >= 0);
3310
exit 1 if (index("no",$tmp) >= 0);
3318
# Test connecting a couple of times before giving an error
3319
# This is needed to get the server time to free old connections
3320
# after the connect test
3326
for ($i=0 ; $i < 10 ; $i++)
3328
if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
3329
{ PrintError => 0, AutoCommit => 1})))
3331
$dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
3336
return safe_connect();
3340
# Check if the server is up and running. If not, ask the user to restart it
3347
print "Checking connection\n" if ($opt_log_all_queries);
3348
# The following line will not work properly with interbase
3349
if ($opt_check_server && defined($check_connect) && $dbh->{AutoCommit} != 0)
3353
$dbh=safe_connect($object);
3356
return if (defined($check_connect) && defined($dbh->do($check_connect)));
3357
$dbh->disconnect || warn $dbh->errstr;
3358
print "\nreconnecting\n" if ($opt_debug);
3361
$dbh=safe_connect($object);
3365
# print query if debugging
3369
if (length($query) > 130)
3371
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3379
$last_error=$DBI::errstr;
3382
if (length($query) > 130)
3384
$query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
3386
printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
3391
# Do one or many queries. Return 1 if all was ok
3392
# Note that all rows are executed
3393
# (to ensure that we execute drop table commands)
3399
my $r = safe_query($q);
3400
add_log($key,$safe_query_log);
3407
my($query,$ok,$retry_ok,$retry,@tmp,$sth);
3410
if (ref($queries) ne "ARRAY")
3412
push(@tmp,$queries);
3415
foreach $query (@$queries)
3417
printf "query1: %-80.80s ...(%d - %d)\n",$query,
3418
length($query),$retry_limit if ($opt_log_all_queries);
3419
print LOG "$query;\n" if ($opt_log);
3420
$safe_query_log .= "< $query\n";
3421
if (length($query) > $query_size)
3424
$safe_query_log .= "Query is too long\n";
3429
for ($retry=0; $retry < $retry_limit ; $retry++)
3431
if (! ($sth=$dbh->prepare($query)))
3433
print_query($query);
3434
$safe_query_log .= "> couldn't prepare:". $dbh->errstr. "\n";
3435
$retry=100 if (!$server->abort_if_fatal_error());
3436
# Force a reconnect because of Access drop table bug!
3437
if ($retry == $retry_limit-2)
3439
print "Forcing disconnect to retry query\n" if ($opt_debug);
3440
$dbh->disconnect || warn $dbh->errstr;
3442
check_connect(); # Check that server is still up
3446
if (!$sth->execute())
3448
print_query($query);
3449
$safe_query_log .= "> execute error:". $dbh->errstr. "\n";
3450
$retry=100 if (!$server->abort_if_fatal_error());
3451
# Force a reconnect because of Access drop table bug!
3452
if ($retry == $retry_limit-2)
3454
print "Forcing disconnect to retry query\n" if ($opt_debug);
3455
$dbh->disconnect || warn $dbh->errstr;
3457
check_connect(); # Check that server is still up
3461
$retry = $retry_limit;
3463
$safe_query_log .= "> OK\n";
3468
$ok=0 if (!$retry_ok);
3469
if ($query =~ /create/i && $server->reconnect_on_errors())
3471
print "Forcing disconnect to retry query\n" if ($opt_debug);
3472
$dbh->disconnect || warn $dbh->errstr;
3473
$dbh=safe_connect();
3479
sub check_reserved_words
3483
my $answer, $prompt, $config, $keyword_type;
3485
my @keywords_ext = ( "ansi-92/99", "ansi92", "ansi99", "extra");
3487
my %reserved_words = (
3488
'ABSOLUTE' => 0, 'ACTION' => 0, 'ADD' => 0,
3489
'AFTER' => 0, 'ALIAS' => 0, 'ALL' => 0,
3490
'ALLOCATE' => 0, 'ALTER' => 0, 'AND' => 0,
3491
'ANY' => 0, 'ARE' => 0, 'AS' => 0,
3492
'ASC' => 0, 'ASSERTION' => 0, 'AT' => 0,
3493
'AUTHORIZATION' => 0, 'BEFORE' => 0, 'BEGIN' => 0,
3494
'BIT' => 0, 'BOOLEAN' => 0, 'BOTH' => 0,
3495
'BREADTH' => 0, 'BY' => 0, 'CALL' => 0,
3496
'CASCADE' => 0, 'CASCADED' => 0, 'CASE' => 0,
3497
'CAST' => 0, 'CATALOG' => 0, 'CHAR' => 0,
3498
'CHARACTER' => 0, 'CHECK' => 0, 'CLOSE' => 0,
3499
'COLLATE' => 0, 'COLLATION' => 0, 'COLUMN' => 0,
3500
'COMMIT' => 0, 'COMPLETION' => 0, 'CONNECT' => 0,
3501
'CONNECTION' => 0, 'CONSTRAINT' => 0, 'CONSTRAINTS' => 0,
3502
'CONTINUE' => 0, 'CORRESPONDING' => 0, 'CREATE' => 0,
3503
'CROSS' => 0, 'CURRENT' => 0, 'CURRENT_DATE' => 0,
3504
'CURRENT_TIME' => 0,'CURRENT_TIMESTAMP' => 0, 'CURRENT_USER' => 0,
3505
'CURSOR' => 0, 'CYCLE' => 0, 'DATA' => 0,
3506
'DATE' => 0, 'DAY' => 0, 'DEALLOCATE' => 0,
3507
'DEC' => 0, 'DECIMAL' => 0, 'DECLARE' => 0,
3508
'DEFAULT' => 0, 'DEFERRABLE' => 0, 'DEFERRED' => 0,
3509
'DELETE' => 0, 'DEPTH' => 0, 'DESC' => 0,
3510
'DESCRIBE' => 0, 'DESCRIPTOR' => 0, 'DIAGNOSTICS' => 0,
3511
'DICTIONARY' => 0, 'DISCONNECT' => 0, 'DISTINCT' => 0,
3512
'DOMAIN' => 0, 'DOUBLE' => 0, 'DROP' => 0,
3513
'EACH' => 0, 'ELSE' => 0, 'ELSEIF' => 0,
3514
'END' => 0, 'END-EXEC' => 0, 'EQUALS' => 0,
3515
'ESCAPE' => 0, 'EXCEPT' => 0, 'EXCEPTION' => 0,
3516
'EXEC' => 0, 'EXECUTE' => 0, 'EXTERNAL' => 0,
3517
'FALSE' => 0, 'FETCH' => 0, 'FIRST' => 0,
3518
'FLOAT' => 0, 'FOR' => 0, 'FOREIGN' => 0,
3519
'FOUND' => 0, 'FROM' => 0, 'FULL' => 0,
3520
'GENERAL' => 0, 'GET' => 0, 'GLOBAL' => 0,
3521
'GO' => 0, 'GOTO' => 0, 'GRANT' => 0,
3522
'GROUP' => 0, 'HAVING' => 0, 'HOUR' => 0,
3523
'IDENTITY' => 0, 'IF' => 0, 'IGNORE' => 0,
3524
'IMMEDIATE' => 0, 'IN' => 0, 'INDICATOR' => 0,
3525
'INITIALLY' => 0, 'INNER' => 0, 'INPUT' => 0,
3526
'INSERT' => 0, 'INT' => 0, 'INTEGER' => 0,
3527
'INTERSECT' => 0, 'INTERVAL' => 0, 'INTO' => 0,
3528
'IS' => 0, 'ISOLATION' => 0, 'JOIN' => 0,
3529
'KEY' => 0, 'LANGUAGE' => 0, 'LAST' => 0,
3530
'LEADING' => 0, 'LEAVE' => 0, 'LEFT' => 0,
3531
'LESS' => 0, 'LEVEL' => 0, 'LIKE' => 0,
3532
'LIMIT' => 0, 'LOCAL' => 0, 'LOOP' => 0,
3533
'MATCH' => 0, 'MINUTE' => 0, 'MODIFY' => 0,
3534
'MODULE' => 0, 'MONTH' => 0, 'NAMES' => 0,
3535
'NATIONAL' => 0, 'NATURAL' => 0, 'NCHAR' => 0,
3536
'NEW' => 0, 'NEXT' => 0, 'NO' => 0,
3537
'NONE' => 0, 'NOT' => 0, 'NULL' => 0,
3538
'NUMERIC' => 0, 'OBJECT' => 0, 'OF' => 0,
3539
'OFF' => 0, 'OLD' => 0, 'ON' => 0,
3540
'ONLY' => 0, 'OPEN' => 0, 'OPERATION' => 0,
3541
'OPTION' => 0, 'OR' => 0, 'ORDER' => 0,
3542
'OUTER' => 0, 'OUTPUT' => 0, 'PAD' => 0,
3543
'PARAMETERS' => 0, 'PARTIAL' => 0, 'PRECISION' => 0,
3544
'PREORDER' => 0, 'PREPARE' => 0, 'PRESERVE' => 0,
3545
'PRIMARY' => 0, 'PRIOR' => 0, 'PRIVILEGES' => 0,
3546
'PROCEDURE' => 0, 'PUBLIC' => 0, 'READ' => 0,
3547
'REAL' => 0, 'RECURSIVE' => 0, 'REF' => 0,
3548
'REFERENCES' => 0, 'REFERENCING' => 0, 'RELATIVE' => 0,
3549
'RESIGNAL' => 0, 'RESTRICT' => 0, 'RETURN' => 0,
3550
'RETURNS' => 0, 'REVOKE' => 0, 'RIGHT' => 0,
3551
'ROLE' => 0, 'ROLLBACK' => 0, 'ROUTINE' => 0,
3552
'ROW' => 0, 'ROWS' => 0, 'SAVEPOINT' => 0,
3553
'SCHEMA' => 0, 'SCROLL' => 0, 'SEARCH' => 0,
3554
'SECOND' => 0, 'SECTION' => 0, 'SELECT' => 0,
3555
'SEQUENCE' => 0, 'SESSION' => 0, 'SESSION_USER' => 0,
3556
'SET' => 0, 'SIGNAL' => 0, 'SIZE' => 0,
3557
'SMALLINT' => 0, 'SOME' => 0, 'SPACE' => 0,
3558
'SQL' => 0, 'SQLEXCEPTION' => 0, 'SQLSTATE' => 0,
3559
'SQLWARNING' => 0, 'STRUCTURE' => 0, 'SYSTEM_USER' => 0,
3560
'TABLE' => 0, 'TEMPORARY' => 0, 'THEN' => 0,
3561
'TIME' => 0, 'TIMESTAMP' => 0, 'TIMEZONE_HOUR' => 0,
3562
'TIMEZONE_MINUTE' => 0, 'TO' => 0, 'TRAILING' => 0,
3563
'TRANSACTION' => 0, 'TRANSLATION' => 0, 'TRIGGER' => 0,
3564
'TRUE' => 0, 'UNDER' => 0, 'UNION' => 0,
3565
'UNIQUE' => 0, 'UNKNOWN' => 0, 'UPDATE' => 0,
3566
'USAGE' => 0, 'USER' => 0, 'USING' => 0,
3567
'VALUE' => 0, 'VALUES' => 0, 'VARCHAR' => 0,
3568
'VARIABLE' => 0, 'VARYING' => 0, 'VIEW' => 0,
3569
'WHEN' => 0, 'WHENEVER' => 0, 'WHERE' => 0,
3570
'WHILE' => 0, 'WITH' => 0, 'WITHOUT' => 0,
3571
'WORK' => 0, 'WRITE' => 0, 'YEAR' => 0,
3574
'ASYNC' => 1, 'AVG' => 1, 'BETWEEN' => 1,
3575
'BIT_LENGTH' => 1,'CHARACTER_LENGTH' => 1, 'CHAR_LENGTH' => 1,
3576
'COALESCE' => 1, 'CONVERT' => 1, 'COUNT' => 1,
3577
'EXISTS' => 1, 'EXTRACT' => 1, 'INSENSITIVE' => 1,
3578
'LOWER' => 1, 'MAX' => 1, 'MIN' => 1,
3579
'NULLIF' => 1, 'OCTET_LENGTH' => 1, 'OID' => 1,
3580
'OPERATORS' => 1, 'OTHERS' => 1, 'OVERLAPS' => 1,
3581
'PENDANT' => 1, 'POSITION' => 1, 'PRIVATE' => 1,
3582
'PROTECTED' => 1, 'REPLACE' => 1, 'SENSITIVE' => 1,
3583
'SIMILAR' => 1, 'SQLCODE' => 1, 'SQLERROR' => 1,
3584
'SUBSTRING' => 1, 'SUM' => 1, 'TEST' => 1,
3585
'THERE' => 1, 'TRANSLATE' => 1, 'TRIM' => 1,
3586
'TYPE' => 1, 'UPPER' => 1, 'VIRTUAL' => 1,
3587
'VISIBLE' => 1, 'WAIT' => 1,
3589
'ADMIN' => 2, 'AGGREGATE' => 2, 'ARRAY' => 2,
3590
'BINARY' => 2, 'BLOB' => 2, 'CLASS' => 2,
3591
'CLOB' => 2, 'CONDITION' => 2, 'CONSTRUCTOR' => 2,
3592
'CONTAINS' => 2, 'CUBE' => 2, 'CURRENT_PATH' => 2,
3593
'CURRENT_ROLE' => 2, 'DATALINK' => 2, 'DEREF' => 2,
3594
'DESTROY' => 2, 'DESTRUCTOR' => 2, 'DETERMINISTIC' => 2,
3595
'DO' => 2, 'DYNAMIC' => 2, 'EVERY' => 2,
3596
'EXIT' => 2, 'EXPAND' => 2, 'EXPANDING' => 2,
3597
'FREE' => 2, 'FUNCTION' => 2, 'GROUPING' => 2,
3598
'HANDLER' => 2, 'HAST' => 2, 'HOST' => 2,
3599
'INITIALIZE' => 2, 'INOUT' => 2, 'ITERATE' => 2,
3600
'LARGE' => 2, 'LATERAL' => 2, 'LOCALTIME' => 2,
3601
'LOCALTIMESTAMP' => 2, 'LOCATOR' => 2, 'MEETS' => 2,
3602
'MODIFIES' => 2, 'NCLOB' => 2, 'NORMALIZE' => 2,
3603
'ORDINALITY' => 2, 'OUT' => 2, 'PARAMETER' => 2,
3604
'PATH' => 2, 'PERIOD' => 2, 'POSTFIX' => 2,
3605
'PRECEDES' => 2, 'PREFIX' => 2, 'READS' => 2,
3606
'REDO' => 2, 'REPEAT' => 2, 'RESULT' => 2,
3607
'ROLLUP' => 2, 'SETS' => 2, 'SPECIFIC' => 2,
3608
'SPECIFICTYPE' => 2, 'START' => 2, 'STATE' => 2,
3609
'STATIC' => 2, 'SUCCEEDS' => 2, 'TERMINATE' => 2,
3610
'THAN' => 2, 'TREAT' => 2, 'UNDO' => 2,
3613
'ACCESS' => 3, 'ANALYZE' => 3, 'AUDIT' => 3,
3614
'AUTO_INCREMENT' => 3, 'BACKUP' => 3, 'BDB' => 3,
3615
'BERKELEYDB' => 3, 'BIGINT' => 3, 'BREAK' => 3,
3616
'BROWSE' => 3, 'BTREE' => 3, 'BULK' => 3,
3617
'CHANGE' => 3, 'CHECKPOINT' => 3, 'CLUSTER' => 3,
3618
'CLUSTERED' => 3, 'COLUMNS' => 3, 'COMMENT' => 3,
3619
'COMPRESS' => 3, 'COMPUTE' => 3, 'CONTAINSTABLE' => 3,
3620
'DATABASE' => 3, 'DATABASES' => 3, 'DAY_HOUR' => 3,
3621
'DAY_MINUTE' => 3, 'DAY_SECOND' => 3, 'DBCC' => 3,
3622
'DELAYED' => 3, 'DENY' => 3, 'DISK' => 3,
3623
'DISTINCTROW' => 3, 'DISTRIBUTED' => 3, 'DUMMY' => 3,
3624
'DUMP' => 3, 'ENCLOSED' => 3, 'ERRLVL' => 3,
3625
'ERRORS' => 3, 'ESCAPED' => 3, 'EXCLUSIVE' => 3,
3626
'EXPLAIN' => 3, 'FIELDS' => 3, 'FILE' => 3,
3627
'FILLFACTOR' => 3, 'FREETEXT' => 3, 'FREETEXTTABLE' => 3,
3628
'FULLTEXT' => 3, 'GEOMETRY' => 3, 'HASH' => 3,
3629
'HIGH_PRIORITY' => 3, 'HOLDLOCK' => 3, 'HOUR_MINUTE' => 3,
3630
'HOUR_SECOND' => 3, 'IDENTIFIED' => 3, 'IDENTITYCOL' => 3,
3631
'IDENTITY_INSERT' => 3, 'INCREMENT' => 3, 'INDEX' => 3,
3632
'INFILE' => 3, 'INITIAL' => 3, 'INNODB' => 3,
3633
'KEYS' => 3, 'KILL' => 3, 'LINENO' => 3,
3634
'LINES' => 3, 'LOAD' => 3, 'LOCK' => 3,
3635
'LONG' => 3, 'LONGBLOB' => 3, 'LONGTEXT' => 3,
3636
'LOW_PRIORITY' => 3, 'MASTER_SERVER_ID' => 3, 'MAXEXTENTS' => 3,
3637
'MEDIUMBLOB' => 3, 'MEDIUMINT' => 3, 'MEDIUMTEXT' => 3,
3638
'MIDDLEINT' => 3, 'MINUS' => 3, 'MINUTE_SECOND' => 3,
3639
'MLSLABEL' => 3, 'MODE' => 3, 'MRG_MYISAM' => 3,
3640
'NOAUDIT' => 3, 'NOCHECK' => 3, 'NOCOMPRESS' => 3,
3641
'NONCLUSTERED' => 3, 'NOWAIT' => 3, 'NUMBER' => 3,
3642
'OFFLINE' => 3, 'OFFSETS' => 3, 'ONLINE' => 3,
3643
'OPENDATASOURCE' => 3, 'OPENQUERY' => 3, 'OPENROWSET' => 3,
3644
'OPENXML' => 3, 'OPTIMIZE' => 3, 'OPTIONALLY' => 3,
3645
'OUTFILE' => 3, 'OVER' => 3, 'PCTFREE' => 3,
3646
'PERCENT' => 3, 'PLAN' => 3, 'PRINT' => 3,
3647
'PROC' => 3, 'PURGE' => 3, 'RAISERROR' => 3,
3648
'RAW' => 3, 'READTEXT' => 3, 'RECONFIGURE' => 3,
3649
'REGEXP' => 3, 'RENAME' => 3, 'REPLICATION' => 3,
3650
'REQUIRE' => 3, 'RESOURCE' => 3, 'RESTORE' => 3,
3651
'RLIKE' => 3, 'ROWCOUNT' => 3, 'ROWGUIDCOL' => 3,
3652
'ROWID' => 3, 'ROWNUM' => 3, 'RTREE' => 3,
3653
'RULE' => 3, 'SAVE' => 3, 'SETUSER' => 3,
3654
'SHARE' => 3, 'SHOW' => 3, 'SHUTDOWN' => 3,
3655
'SONAME' => 3, 'SPATIAL' => 3, 'SQL_BIG_RESULT' => 3,
3656
'SQL_CALC_FOUND_ROWS' => 3,'SQL_SMALL_RESULT' => 3, 'SSL' => 3,
3657
'STARTING' => 3, 'STATISTICS' => 3, 'STRAIGHT_JOIN' => 3,
3658
'STRIPED' => 3, 'SUCCESSFUL' => 3, 'SYNONYM' => 3,
3659
'SYSDATE' => 3, 'TABLES' => 3, 'TERMINATED' => 3,
3660
'TEXTSIZE' => 3, 'TINYBLOB' => 3, 'TINYINT' => 3,
3661
'TINYTEXT' => 3, 'TOP' => 3, 'TRAN' => 3,
3662
'TRUNCATE' => 3, 'TSEQUAL' => 3, 'TYPES' => 3,
3663
'UID' => 3, 'UNLOCK' => 3, 'UNSIGNED' => 3,
3664
'UPDATETEXT' => 3, 'USE' => 3, 'USER_RESOURCES' => 3,
3665
'VALIDATE' => 3, 'VARBINARY' => 3, 'VARCHAR2' => 3,
3666
'WAITFOR' => 3, 'WARNINGS' => 3, 'WRITETEXT' => 3,
3667
'XOR' => 3, 'YEAR_MONTH' => 3, 'ZEROFILL' => 3
3671
safe_query("drop table crash_me10 $drop_attr");
3673
foreach my $keyword (sort {$a cmp $b} keys %reserved_words)
3675
$keyword_type= $reserved_words{$keyword};
3677
$prompt= "Keyword ".$keyword;
3678
$config= "reserved_word_".$keywords_ext[$keyword_type]."_".lc($keyword);
3680
report_fail($prompt,$config,
3681
"create table crash_me10 ($keyword int not null)",
3682
"drop table crash_me10 $drop_attr"
3688
# Do a query on a query package object.
3693
my($object,$limit)=@_;
3694
my ($query,$result,$retry,$sth);
3696
$query=$object->query($limit);
3697
$result=safe_query($query);
3703
if (defined($query=$object->check_query()))
3705
for ($retry=0 ; $retry < $retry_limit ; $retry++)
3707
printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
3708
print LOG "$query;\n" if ($opt_log);
3709
if (($sth= $dbh->prepare($query)))
3713
$result= $object->check($sth);
3718
print_query($query);
3723
print_query($query);
3725
$retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
3726
if ($retry == $retry_limit-2)
3728
print "Forcing discoennect to retry query\n" if ($opt_debug);
3729
$dbh->disconnect || warn $dbh->errstr;
3731
check_connect($object); # Check that server is still up
3733
$result=0; # Query failed
3736
return $result; # Server couldn't handle the query
3742
my ($prompt,$limit,@queries)=@_;
3744
if (!defined($limits{$limit}))
3746
my $queries_result = safe_query(\@queries);
3747
add_log($limit, $safe_query_log);
3749
if ( $queries_result) {
3750
$report_result= "yes";
3751
add_log($limit,"As far as all queries returned OK, result is YES");
3753
$report_result= "no";
3754
add_log($limit,"As far as some queries didnt return OK, result is NO");
3756
save_config_data($limit,$report_result,$prompt);
3758
print "$limits{$limit}\n";
3759
return $limits{$limit} ne "no";
3764
my ($prompt,$limit,@queries)=@_;
3766
if (!defined($limits{$limit}))
3768
my $queries_result = safe_query(\@queries);
3769
add_log($limit, $safe_query_log);
3771
if ( $queries_result) {
3772
$report_result= "no";
3773
add_log($limit,"As far as all queries returned OK, result is NO");
3775
$report_result= "yes";
3776
add_log($limit,"As far as some queries didnt return OK, result is YES");
3778
save_config_data($limit,$report_result,$prompt);
3780
print "$limits{$limit}\n";
3781
return $limits{$limit} ne "no";
3785
# Return true if one of the queries is ok
3789
my ($prompt,$limit,$queries)=@_;
3790
my ($query,$res,$result);
3792
if (!defined($limits{$limit}))
3794
save_incomplete($limit,$prompt);
3796
foreach $query (@$queries)
3798
if (safe_query_l($limit,$query->[0]))
3800
$result= $query->[1];
3804
save_config_data($limit,$result,$prompt);
3806
print "$limits{$limit}\n";
3807
return $limits{$limit} ne "no";
3811
# Execute query and save result as limit value.
3815
my ($prompt,$limit,$query)=@_;
3818
if (!defined($limits{$limit}))
3820
save_incomplete($limit,$prompt);
3821
$error=safe_query_result($query,"1",2);
3822
add_log($limit,$safe_query_result_log);
3823
save_config_data($limit,$error ? "not supported" :$last_result,$prompt);
3825
print "$limits{$limit}\n";
3826
return $limits{$limit} ne "not supported";
3831
my ($limit,$queries,$check,$clear)=@_;
3832
if (!defined($limits{$limit}))
3834
save_incomplete($limit,$prompt);
3835
eval {undef($dbh->{AutoCommit})};
3838
if (safe_query(\@$queries))
3841
$dbh->{AutoCommit} = 1;
3842
if (safe_query_result($check,"","")) {
3843
add_log($limit,$safe_query_result_log);
3844
save_config_data($limit,"yes",$limit);
3848
add_log($limit,$safe_query_log);
3849
save_config_data($limit,"error",$limit);
3851
$dbh->{AutoCommit} = 1;
3855
add_log($limit,"Couldnt undef autocommit ?? ");
3856
save_config_data($limit,"no",$limit);
3860
return $limits{$limit} ne "yes";
3865
my ($limit,$queries,$check,$clear)=@_;
3866
if (!defined($limits{$limit}))
3868
save_incomplete($limit,$prompt);
3869
eval {undef($dbh->{AutoCommit})};
3872
if (safe_query(\@$queries))
3874
add_log($limit,$safe_query_log);
3877
$dbh->{AutoCommit} = 1;
3878
if (safe_query($check)) {
3879
add_log($limit,$safe_query_log);
3880
save_config_data($limit,"no",$limit);
3882
add_log($limit,$safe_query_log);
3883
save_config_data($limit,"yes",$limit);
3887
add_log($limit,$safe_query_log);
3888
save_config_data($limit,"error",$limit);
3893
add_log($limit,'Couldnt undef Autocommit??');
3894
save_config_data($limit,"error",$limit);
3898
$dbh->{AutoCommit} = 1;
3899
return $limits{$limit} ne "yes";
3903
sub check_and_report
3905
my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
3908
$function=0 if (!defined($function));
3910
print "$prompt: " if (!defined($skip_prompt));
3911
if (!defined($limits{$limit}))
3913
save_incomplete($limit,$prompt);
3914
$tmp=1-safe_query(\@$pre);
3915
add_log($limit,$safe_query_log);
3918
$tmp=safe_query_result($query,$answer,$string_type) ;
3919
add_log($limit,$safe_query_result_log);
3921
safe_query(\@$post);
3922
add_log($limit,$safe_query_log);
3923
delete $limits{$limit};
3924
if ($function == 3) # Report error as 'no'.
3929
if ($function == 0 ||
3930
$tmp != 0 && $function == 1 ||
3931
$tmp == 0 && $function== 2)
3933
save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
3935
print "$limits{$limit}\n";
3936
return $function == 0 ? $limits{$limit} eq "yes" : 0;
3938
return 1; # more things to check
3940
print "$limits{$limit}\n";
3941
return 0 if ($function);
3942
return $limits{$limit} eq "yes";
3948
my ($prompt,$limit,@tests)=@_;
3949
my ($tmp,$test,$type);
3953
if (!defined($limits{$limit}))
3955
save_incomplete($limit,$prompt);
3956
$type="no"; # Not supported
3957
foreach $test (@tests)
3959
my $tmp_type= shift(@$test);
3960
if (safe_query_l($limit,\@$test))
3967
save_config_data($limit, $type, $prompt);
3969
print "$limits{$limit}\n";
3970
return $limits{$limit} ne "no";
3974
# Just execute the query and check values; Returns 1 if ok
3977
sub execute_and_check
3979
my ($key,$pre,$query,$post,$answer,$string_type)=@_;
3982
$tmp=safe_query_l($key,\@$pre);
3984
$tmp=safe_query_result_l($key,$query,$answer,$string_type) == 0 if ($tmp);
3985
safe_query_l($key,\@$post);
3990
# returns 0 if ok, 1 if error, -1 if wrong answer
3991
# Sets $last_result to value of query
3992
sub safe_query_result_l{
3993
my ($key,$query,$answer,$result_type)=@_;
3994
my $r = safe_query_result($query,$answer,$result_type);
3995
add_log($key,$safe_query_result_log);
3999
sub safe_query_result
4001
# result type can be
4002
# 8 (must be empty), 2 (Any value), 0 (number)
4003
# 1 (char, endspaces can differ), 3 (exact char), 4 (NULL)
4004
# 5 (char with prefix), 6 (exact, errors are ignored)
4005
# 7 (array of numbers)
4006
my ($query,$answer,$result_type)=@_;
4007
my ($sth,$row,$result,$retry);
4008
undef($last_result);
4009
$safe_query_result_log="";
4011
printf "\nquery3: %-80.80s\n",$query if ($opt_log_all_queries);
4012
print LOG "$query;\n" if ($opt_log);
4013
$safe_query_result_log="<".$query."\n";
4015
for ($retry=0; $retry < $retry_limit ; $retry++)
4017
if (!($sth=$dbh->prepare($query)))
4019
print_query($query);
4020
$safe_query_result_log .= "> prepare failed:".$dbh->errstr."\n";
4022
if ($server->abort_if_fatal_error())
4024
check_connect(); # Check that server is still up
4027
check_connect(); # Check that server is still up
4032
print_query($query);
4033
$safe_query_result_log .= "> execute failed:".$dbh->errstr."\n";
4034
if ($server->abort_if_fatal_error())
4036
check_connect(); # Check that server is still up
4039
check_connect(); # Check that server is still up
4047
if (!($row=$sth->fetchrow_arrayref))
4049
print "\nquery: $query didn't return any result\n" if ($opt_debug);
4050
$safe_query_result_log .= "> didn't return any result:".$dbh->errstr."\n";
4052
return ($result_type == 8) ? 0 : 1;
4054
if ($result_type == 8)
4060
$last_result= $row->[0]; # Save for report_result;
4061
$safe_query_result_log .= ">".$last_result."\n";
4063
# if ($result_type == 2) We accept any return value as answer
4065
if ($result_type == 0) # Compare numbers
4067
$row->[0] =~ s/,/./; # Fix if ',' is used instead of '.'
4068
if ($row->[0] != $answer && (abs($row->[0]- $answer)/
4069
(abs($row->[0]) + abs($answer))) > 0.01)
4072
$safe_query_result_log .=
4073
"We expected '$answer' but got '$last_result' \n";
4076
elsif ($result_type == 1) # Compare where end space may differ
4078
$row->[0] =~ s/\s+$//;
4079
if ($row->[0] ne $answer)
4082
$safe_query_result_log .=
4083
"We expected '$answer' but got '$last_result' \n";
4086
elsif ($result_type == 3) # This should be a exact match
4088
if ($row->[0] ne $answer)
4091
$safe_query_result_log .=
4092
"We expected '$answer' but got '$last_result' \n";
4095
elsif ($result_type == 4) # If results should be NULL
4097
if (defined($row->[0]))
4100
$safe_query_result_log .=
4101
"We expected NULL but got '$last_result' \n";
4104
elsif ($result_type == 5) # Result should have given prefix
4106
if (length($row->[0]) < length($answer) &&
4107
substr($row->[0],1,length($answer)) ne $answer)
4110
$safe_query_result_log .=
4111
"Result must have prefix '$answer', but '$last_result' \n";
4114
elsif ($result_type == 6) # Exact match but ignore errors
4116
if ($row->[0] ne $answer)
4118
$safe_query_result_log .=
4119
"We expected '$answer' but got '$last_result' \n";
4122
elsif ($result_type == 7) # Compare against array of numbers
4124
if ($row->[0] != $answer->[0])
4126
$safe_query_result_log .= "must be '$answer->[0]' \n";
4133
while (($row=$sth->fetchrow_arrayref))
4135
$safe_query_result_log .= ">$row\n";
4137
$value=shift(@$answer);
4138
if (!defined($value))
4140
print "\nquery: $query returned to many results\n"
4142
$safe_query_result_log .= "It returned to many results \n";
4146
if ($row->[0] != $value)
4148
$safe_query_result_log .= "Must return $value here \n";
4153
if ($#$answer != -1)
4155
print "\nquery: $query returned too few results\n"
4157
$safe_query_result_log .= "It returned too few results \n";
4163
print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
4164
if ($opt_debug && $result && $result_type != 7);
4169
# Find limit using binary search. This is a weighed binary search that
4170
# will prefere lower limits to get the server to crash as
4171
# few times as possible
4176
my ($prompt,$limit,$query)=@_;
4177
my ($first,$end,$i,$tmp,@tmp_array, $queries);
4179
if (defined($end=$limits{$limit}))
4181
print "$end (cache)\n";
4184
save_incomplete($limit,$prompt);
4185
add_log($limit,"We are trying (example with N=5):");
4186
$queries = $query->query(5);
4187
if (ref($queries) ne "ARRAY")
4189
push(@tmp_array,$queries);
4190
$queries= \@tmp_array;
4192
foreach $tmp (@$queries)
4193
{ add_log($limit,repr_query($tmp)); }
4195
if (defined($queries = $query->check_query()))
4197
if (ref($queries) ne "ARRAY")
4200
push(@tmp_array,$queries);
4201
$queries= \@tmp_array;
4203
foreach $tmp (@$queries)
4204
{ add_log($limit,repr_query($tmp)); }
4206
if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
4208
if (!safe_query_l($limit,$query->{'init'}))
4215
if (!limit_query($query,1)) # This must work
4217
print "\nMaybe fatal error: Can't check '$prompt' for limit=1\n".
4218
"error: $last_error\n";
4223
$first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});
4225
if (defined($end=$limits{'restart'}{'tohigh'}))
4228
print "\nRestarting this with low limit: $first and high limit: $end\n";
4229
delete $limits{'restart'};
4230
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
4234
$end= $query->max_limit();
4235
$i=int(($end+$first)/2);
4238
unless(limit_query($query,0+$end)) {
4239
while ($first < $end)
4241
print "." if ($opt_debug);
4242
save_config_data("restart",$i,"") if ($opt_restart);
4243
if (limit_query($query,$i))
4246
$log_str .= " $i:OK";
4247
$i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
4252
$log_str .= " $i:FAIL";
4253
$i=$first+int(($end-$first+4)/5); # Prefere lower on errors
4257
$end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
4258
if ($end >= $query->{'max_limit'} &&
4259
substr($query->{'max_limit'},0,1) eq '+')
4261
$end= $query->{'max_limit'};
4264
add_log($limit,$log_str);
4265
save_config_data($limit,$end,$prompt);
4266
delete $limits{'restart'};
4271
# Check that the query works!
4278
if (!safe_query($query))
4280
$query=join("; ",@$query) if (ref($query) eq "ARRAY");
4281
print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
4287
sub read_config_data
4289
my ($key,$limit,$prompt);
4290
if (-e $opt_config_file)
4292
open(CONFIG_FILE,"+<$opt_config_file") ||
4293
die "Can't open configure file $opt_config_file\n";
4294
print "Reading old values from cache: $opt_config_file\n";
4298
open(CONFIG_FILE,"+>>$opt_config_file") ||
4299
die "Can't create configure file $opt_config_file: $!\n";
4304
while (<CONFIG_FILE>)
4307
if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
4309
$key=$1; $limit=$2 ; $prompt=$3;
4310
if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
4312
if ($key !~ /restart/i)
4314
$limits{$key}=$limit eq "null"? undef : $limit;
4315
$prompts{$key}=length($prompt) ? substr($prompt,2) : "";
4317
delete $limits{'restart'};
4322
if ($limit > $limits{'restart'}{'tohigh'})
4324
$limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
4326
$limits{'restart'}{'tohigh'} = $limit;
4330
elsif (/\s*###(.*)$/) # log line
4332
# add log line for previously read key
4333
$log{$last_read} .= "$1\n";
4335
elsif (!/^\s*$/ && !/^\#/)
4337
die "Wrong config row: $_\n";
4343
sub save_config_data
4345
my ($key,$limit,$prompt)=@_;
4346
$prompts{$key}=$prompt;
4347
return if (defined($limits{$key}) && $limits{$key} eq $limit);
4348
if (!defined($limit) || $limit eq "")
4350
# die "Undefined limit for $key\n";
4353
print CONFIG_FILE "$key=$limit\t# $prompt\n";
4354
$limits{$key}=$limit;
4356
# now write log lines (immediatelly after limits)
4358
my $last_line_was_empty=0;
4359
foreach $line (split /\n/, $log{$key})
4361
print CONFIG_FILE "$log_prefix$line\n"
4362
unless ( ($last_line_was_empty eq 1)
4363
&& ($line =~ /^\s+$/) );
4364
$last_line_was_empty= ($line =~ /^\s+$/)?1:0;
4367
if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
4368
($limits{'operating_system'} =~ /NT/))
4370
# If perl crashes in windows, everything is lost (Wonder why? :)
4372
open(CONFIG_FILE,"+>>$opt_config_file") ||
4373
die "Can't reopen configure file $opt_config_file: $!\n";
4381
$log{$key} .= $line . "\n" if ($opt_verbose);
4384
sub save_all_config_data
4388
return if (!$limit_changed);
4389
open(CONFIG_FILE,">$opt_config_file") ||
4390
die "Can't create configure file $opt_config_file: $!\n";
4394
delete $limits{'restart'};
4397
"#This file is automaticly generated by crash-me $version\n\n";
4398
foreach $key (sort keys %limits)
4400
$tmp="$key=$limits{$key}";
4401
print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
4402
"# $prompts{$key}\n";
4406
my $last_line_was_empty=0;
4407
foreach $line (split /\n/, $log{$key})
4409
print CONFIG_FILE "$log_prefix$line\n" unless
4410
( ($last_line_was_empty eq 1) && ($line =~ /^\s*$/));
4411
$last_line_was_empty= ($line =~ /^\s*$/)?1:0;
4419
# Save 'incomplete' in the limits file to be able to continue if
4420
# crash-me dies because of a bug in perl/DBI
4424
my ($limit,$prompt)= @_;
4425
save_config_data($limit,"incompleted",$prompt) if ($opt_restart);
4431
my ($sth,$limit)=@_;
4434
return 0 if (!($row=$sth->fetchrow_arrayref));
4435
return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
4443
for ($i=1 ; $i <= $#_; $i++)
4445
$min=$_[$i] if ($min > $_[$i]);
4453
return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
4454
return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
4455
return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
4460
# Returns a list of statements to create a table in a portable manner
4461
# but still utilizing features in the databases.
4466
my($table_name,$fields,$index,$extra) = @_;
4467
my($query,$nr,$parts,@queries,@index);
4469
$extra="" if (!defined($extra));
4471
$query="create table $table_name (";
4473
foreach $field (@$fields)
4475
$query.= $field . ',';
4477
foreach $index (@$index)
4479
$index =~ /\(([^\(]*)\)$/i;
4481
if ($index =~ /^primary key/)
4483
if ($limits{'primary_key_in_create'} eq 'yes')
4485
$query.= $index . ',';
4490
"create unique index ${table_name}_prim on $table_name ($parts)");
4493
elsif ($index =~ /^unique/)
4495
if ($limits{'unique_in_create'} eq 'yes')
4497
$query.= "unique ($parts),";
4503
"create unique index ${table_name}_$nr on $table_name ($parts)");
4509
if ($limits{'index_in_create'} eq 'yes')
4511
$query.= "index ($parts),";
4517
"create index ${table_name}_$nr on $table_name ($1)");
4522
$query.= ") $extra";
4523
unshift(@queries,$query);
4529
# This is used by some query packages to change:
4532
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
4533
# %f -> q1,q2,q3....
4534
# %F -> q1 integer,q2 integer,q3 integer....
4538
my ($query,$limit)=@_;
4541
return $query if !(defined($query));
4542
$query =~ s/%d/$limit/g;
4545
$repeat= 'a' x $limit;
4546
$query =~ s/%s/$repeat/g;
4550
$repeat= '1,' x $limit;
4552
$query =~ s/%v/$repeat/g;
4557
for ($i=1 ; $i <= $limit ; $i++)
4562
$query =~ s/%f/$repeat/g;
4567
for ($i=1 ; $i <= $limit ; $i++)
4569
$repeat.="q$i integer,";
4572
$query =~ s/%F/$repeat/g;
4579
# Different query packages
4582
package query_repeat;
4586
my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
4587
$max_limit, $check, $offset)=@_;
4589
if (defined($init) && $#$init != -1)
4591
$self->{'init'}=$init;
4593
$self->{'query'}=$query;
4594
$self->{'add1'}=$add1;
4595
$self->{'add_mid'}=$add_mid;
4596
$self->{'add'}=$add;
4597
$self->{'add_end'}=$add_end;
4598
$self->{'end_query'}=$end_query;
4599
$self->{'cleanup'}=$cleanup;
4600
$self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
4601
$self->{'check'}=$check;
4602
$self->{'offset'}=$offset;
4603
$self->{'printf'}= ($add =~ /%d/);
4609
my ($self,$limit)=@_;
4610
if (!$self->{'printf'})
4612
return $self->{'query'} . ($self->{'add'} x $limit) .
4613
($self->{'add_end'} x $limit) . $self->{'end_query'};
4615
my ($tmp,$tmp2,$tmp3,$i);
4616
$tmp=$self->{'query'};
4617
if ($self->{'add1'})
4619
for ($i=0; $i < $limit ; $i++)
4621
$tmp3 = $self->{'add1'};
4626
$tmp .= " ".$self->{'add_mid'};
4629
for ($i=0; $i < $limit ; $i++)
4631
$tmp2 = $self->{'add'};
4637
($self->{'add_end'} x $limit) . $self->{'end_query'});
4644
$tmp=int(($main::limits{"query_size"}-length($self->{'query'})
4645
-length($self->{'add_mid'})-length($self->{'end_query'}))/
4646
(length($self->{'add1'})+
4647
length($self->{'add'})+length($self->{'add_end'})));
4648
return main::min($self->{'max_limit'},$tmp);
4655
my($tmp,$statement);
4656
$tmp=$self->{'cleanup'};
4657
foreach $statement (@$tmp)
4659
main::safe_query($statement) if (defined($statement) && length($statement));
4666
my $check=$self->{'check'};
4667
return &$check($sth,$self->{'limit'}) if (defined($check));
4681
my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
4683
$self->{'query'}=$query;
4684
$self->{'end_query'}=$end_query;
4685
$self->{'cleanup'}=$cleanup;
4686
$self->{'max_limit'}=$max_limit;
4687
$self->{'check'}=$check;
4694
$self->{'limit'}=$i;
4695
return "$self->{'query'}$i$self->{'end_query'}";
4701
return $self->{'max_limit'};
4708
foreach $statement ($self->{'$cleanup'})
4710
main::safe_query($statement) if (defined($statement) && length($statement));
4718
my $check=$self->{'check'};
4719
return &$check($sth,$self->{'limit'}) if (defined($check));
4729
# This package is used when testing CREATE TABLE!
4732
package query_table;
4736
my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
4737
$cleanup, $max_limit, $offset)=@_;
4739
$self->{'query'}=$query;
4740
$self->{'add'}=$add;
4741
$self->{'end_query'}=$end_query;
4742
$self->{'extra_init'}=$extra_init;
4743
$self->{'safe_query'}=$safe_query;
4744
$self->{'check'}=$check;
4745
$self->{'cleanup'}=$cleanup;
4746
$self->{'max_limit'}=$max_limit;
4747
$self->{'offset'}=$offset;
4754
my ($self,$limit)=@_;
4755
$self->{'limit'}=$limit;
4756
$self->cleanup(); # Drop table before create
4758
my ($tmp,$tmp2,$i,$query,@res);
4759
$tmp =$self->{'query'};
4760
$tmp =~ s/%d/$limit/g;
4761
for ($i=1; $i <= $limit ; $i++)
4763
$tmp2 = $self->{'add'};
4767
push(@res,$tmp . $self->{'end_query'});
4768
$tmp=$self->{'extra_init'};
4769
foreach $query (@$tmp)
4771
push(@res,main::fix_query($query,$limit));
4780
return $self->{'max_limit'};
4787
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4793
my $check=$self->{'check'};
4794
return 0 if (!($row=$sth->fetchrow_arrayref));
4795
if (defined($check))
4797
return (defined($row->[0]) &&
4798
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4804
# Remove table before and after create table query
4809
main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
4813
# Package to do many queries with %d, and %s substitution
4820
my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
4823
$self->{'query'}=$query;
4824
$self->{'safe_query'}=$safe_query;
4825
$self->{'check'}=$check_result;
4826
$self->{'cleanup'}=$cleanup;
4827
$self->{'max_limit'}=$max_limit;
4828
$self->{'offset'}=$offset;
4829
$self->{'safe_cleanup'}=$safe_cleanup;
4836
my ($self,$limit)=@_;
4837
my ($queries,$query,@res);
4838
$self->{'limit'}=$limit;
4839
$self->cleanup() if (defined($self->{'safe_cleanup'}));
4840
$queries=$self->{'query'};
4841
foreach $query (@$queries)
4843
push(@res,main::fix_query($query,$limit));
4851
return main::fix_query($self->{'safe_query'},$self->{'limit'});
4857
my($tmp,$statement);
4858
return if (!defined($self->{'cleanup'}));
4859
$tmp=$self->{'cleanup'};
4860
foreach $statement (@$tmp)
4862
if (defined($statement) && length($statement))
4864
main::safe_query(main::fix_query($statement,$self->{'limit'}));
4874
return 0 if (!($row=$sth->fetchrow_arrayref));
4875
$check=$self->{'check'};
4876
if (defined($check))
4878
return (defined($row->[0]) &&
4879
$row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
4887
return $self->{'max_limit'};
4891
# Used to find max supported row length
4894
package query_row_length;
4898
my ($type,$create,$null,$drop,$max_limit)=@_;
4900
$self->{'table_name'}=$create;
4901
$self->{'null'}=$null;
4902
$self->{'cleanup'}=$drop;
4903
$self->{'max_limit'}=$max_limit;
4910
my ($self,$limit)=@_;
4911
my ($res,$values,$size,$length,$i);
4912
$self->{'limit'}=$limit;
4915
$size=main::min($main::limits{'max_char_size'},255);
4916
$size = 255 if (!$size); # Safety
4917
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4919
$res.= "q$i char($size) $self->{'null'},";
4920
$values.="'" . ('a' x $size) . "',";
4922
if ($length < $limit)
4924
$size=$limit-$length;
4925
$res.= "q$i char($size) $self->{'null'},";
4926
$values.="'" . ('a' x $size) . "',";
4930
return ["create table " . $self->{'table_name'} . " ($res)",
4931
"insert into " . $self->{'table_name'} . " values ($values)"];
4937
return $self->{'max_limit'};
4943
main::safe_query($self->{'cleanup'});
4958
# Used to find max supported index length
4961
package query_index_length;
4965
my ($type,$create,$drop,$max_limit)=@_;
4967
$self->{'create'}=$create;
4968
$self->{'cleanup'}=$drop;
4969
$self->{'max_limit'}=$max_limit;
4976
my ($self,$limit)=@_;
4977
my ($res,$size,$length,$i,$parts,$values);
4978
$self->{'limit'}=$limit;
4980
$res=$parts=$values="";
4981
$size=main::min($main::limits{'max_index_part_length'},
4982
$main::limits{'max_char_size'});
4983
$size=1 if ($size == 0); # Avoid infinite loop errors
4984
for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
4986
$res.= "q$i char($size) not null,";
4988
$values.= "'" . ('a' x $size) . "',";
4990
if ($length < $limit)
4992
$size=$limit-$length;
4993
$res.= "q$i char($size) not null,";
4995
$values.= "'" . ('a' x $size) . "',";
5000
if ($main::limits{'unique_in_create'} eq 'yes')
5002
return [$self->{'create'} . "($res,unique ($parts))",
5003
"insert into crash_q values($values)"];
5005
return [$self->{'create'} . "($res)",
5006
"create index crash_q_index on crash_q ($parts)",
5007
"insert into crash_q values($values)"];
5013
return $self->{'max_limit'};
5019
main::safe_query($self->{'cleanup'});
5036
# OID test instead of / in addition to _rowid
added
removed
sql-bench/crash-me.sh
