diff options
Diffstat (limited to 'tests')
| -rw-r--r-- | tests/.cvsignore | 2 | ||||
| -rw-r--r-- | tests/Makefile.am | 27 | ||||
| -rw-r--r-- | tests/auto_increment.res | 114 | ||||
| -rw-r--r-- | tests/auto_increment.tst | 27 | ||||
| -rwxr-xr-x | tests/big_record.pl | 58 | ||||
| -rwxr-xr-x | tests/export.pl | 164 | ||||
| -rwxr-xr-x | tests/fork2_test.pl | 210 | ||||
| -rwxr-xr-x | tests/fork3_test.pl | 150 | ||||
| -rwxr-xr-x | tests/fork_test.pl | 247 | ||||
| -rw-r--r-- | tests/function.res | 258 | ||||
| -rw-r--r-- | tests/function.tst | 80 | ||||
| -rw-r--r-- | tests/grant.pl | 545 | ||||
| -rw-r--r-- | tests/grant.res | 436 | ||||
| -rwxr-xr-x | tests/insert_and_repair.pl | 180 | ||||
| -rwxr-xr-x | tests/lock_test.pl | 94 | ||||
| -rw-r--r-- | tests/lock_test.res | 25 | ||||
| -rwxr-xr-x | tests/mail_to_db.pl | 310 | ||||
| -rwxr-xr-x | tests/pmail.pl | 195 | ||||
| -rwxr-xr-x | tests/table_types.pl | 224 | ||||
| -rwxr-xr-x | tests/test_delayed_insert.pl | 365 | ||||
| -rw-r--r-- | tests/udf_test | 30 | ||||
| -rw-r--r-- | tests/udf_test.res | 151 |
22 files changed, 3892 insertions, 0 deletions
diff --git a/tests/.cvsignore b/tests/.cvsignore new file mode 100644 index 00000000000..282522db034 --- /dev/null +++ b/tests/.cvsignore @@ -0,0 +1,2 @@ +Makefile +Makefile.in diff --git a/tests/Makefile.am b/tests/Makefile.am new file mode 100644 index 00000000000..e0cc44b5153 --- /dev/null +++ b/tests/Makefile.am @@ -0,0 +1,27 @@ +# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB +# +# This library is free software; you can redistribute it and/or +# modify it under the terms of the GNU Library General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This library is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU Library General Public +# License along with this library; if not, write to the Free +# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, +# MA 02111-1307, USA + +## Process this file with automake to create Makefile.in + +EXTRA_DIST = auto_increment.res auto_increment.tst \ + function.res function.tst lock_test.pl lock_test.res \ + export.pl big_record.pl \ + fork_test.pl fork2_test.pl fork3_test.pl \ + insert_and_repair.pl \ + grant.pl grant.res test_delayed_insert.pl \ + pmail.pl mail_to_db.pl table_types.pl \ + udf_test udf_test.res diff --git a/tests/auto_increment.res b/tests/auto_increment.res new file mode 100644 index 00000000000..fa7b5428708 --- /dev/null +++ b/tests/auto_increment.res @@ -0,0 +1,114 @@ +-------------- +drop table if exists auto_incr_test,auto_incr_test2 +-------------- + +Query OK, 0 rows affected + +-------------- +create table auto_incr_test (id int not null auto_increment, name char(40), timestamp timestamp, primary key (id)) +-------------- + +Query OK, 0 rows affected + +-------------- +insert into auto_incr_test (name) values ("first record") +-------------- + +Query OK, 1 row affected + +-------------- +insert into auto_incr_test values (last_insert_id()+1,"second record",null) +-------------- + +Query OK, 1 row affected + +-------------- +insert into auto_incr_test (id,name) values (10,"tenth record") +-------------- + +Query OK, 1 row affected + +-------------- +insert into auto_incr_test values (0,"eleventh record",null) +-------------- + +Query OK, 1 row affected + +-------------- +insert into auto_incr_test values (last_insert_id()+1,"12","1997-01-01") +-------------- + +Query OK, 1 row affected + +-------------- +insert into auto_incr_test values (12,"this will not work",NULL) +-------------- + +ERROR 1062 at line 15: Duplicate entry '12' for key 1 +-------------- +replace into auto_incr_test values (12,"twelfth record",NULL) +-------------- + +Query OK, 2 rows affected + +-------------- +select * from auto_incr_test +-------------- + +id name timestamp +1 first record 19980817042654 +2 second record 19980817042655 +10 tenth record 19980817042655 +11 eleventh record 19980817042655 +12 twelfth record 19980817042655 +5 rows in set + +-------------- +create table auto_incr_test2 (id int not null auto_increment, name char(40), primary key (id)) +-------------- + +Query OK, 0 rows affected + +-------------- +insert into auto_incr_test2 select NULL,name from auto_incr_test +-------------- + +Query OK, 5 rows affected +Records: 5 Duplicates: 0 Warnings: 0 + +-------------- +insert into auto_incr_test2 select id,name from auto_incr_test +-------------- + +Query OK, 3 rows affected +Records: 5 Duplicates: 2 Warnings: 0 + +-------------- +replace into auto_incr_test2 select id,name from auto_incr_test +-------------- + +Query OK, 5 rows affected +Records: 5 Duplicates: 5 Warnings: 0 + +-------------- +select * from auto_incr_test2 +-------------- + +id name +1 first record +2 second record +3 tenth record +4 eleventh record +5 twelfth record +10 tenth record +11 eleventh record +12 twelfth record +8 rows in set + +-------------- +drop table auto_incr_test,auto_incr_test2 +-------------- + +Query OK, 0 rows affected + +Bye diff --git a/tests/auto_increment.tst b/tests/auto_increment.tst new file mode 100644 index 00000000000..a11a05c8eff --- /dev/null +++ b/tests/auto_increment.tst @@ -0,0 +1,27 @@ +# +# Test of auto_increment +# +# run this program with mysql -vvf test < this file + +drop table if exists auto_incr_test,auto_incr_test2 ; + +create table auto_incr_test (id int not null auto_increment, name char(40), timestamp timestamp, primary key (id)) ; + +insert into auto_incr_test (name) values ("first record"); +insert into auto_incr_test values (last_insert_id()+1,"second record",null); +insert into auto_incr_test (id,name) values (10,"tenth record"); +insert into auto_incr_test values (0,"eleventh record",null); +insert into auto_incr_test values (last_insert_id()+1,"12","1997-01-01"); +insert into auto_incr_test values (12,"this will not work",NULL); +replace into auto_incr_test values (12,"twelfth record",NULL); + +select * from auto_incr_test ; + +create table auto_incr_test2 (id int not null auto_increment, name char(40), primary key (id)) ; +insert into auto_incr_test2 select NULL,name from auto_incr_test; +insert into auto_incr_test2 select id,name from auto_incr_test; +replace into auto_incr_test2 select id,name from auto_incr_test; + +select * from auto_incr_test2 ; + +drop table auto_incr_test,auto_incr_test2; diff --git a/tests/big_record.pl b/tests/big_record.pl new file mode 100755 index 00000000000..52d3dca5f7b --- /dev/null +++ b/tests/big_record.pl @@ -0,0 +1,58 @@ +#!/usr/bin/perl + +# This is a test with stores big records in a blob +# Note that for the default test the mysql server should have been +# started with at least 'mysqld -O max_allowed_packet=200k' + +$host= shift || ""; +$test_db="test"; +$opt_user=$opt_password=""; + +use DBI; +$|= 1; # Autoflush + +$table="test_big_record"; +$rows=20; # Test of blobs up to ($rows-1)*10000+1 bytes + +print "Connection to database $test_db\n"; + +$dbh = DBI->connect("DBI:mysql:$test_db:$host",$opt_user,$opt_password) || die "Can't connect: $DBI::errstr\n"; + +$dbh->do("drop table if exists $table"); + +print "Creating table $table\n"; + +($dbh->do("\ +CREATE TABLE $table ( + auto int(5) unsigned NOT NULL DEFAULT '0' auto_increment, + test mediumblob, + PRIMARY KEY (auto))")) or die $DBI::errstr; + +print "Inserting $rows records\n"; + +for ($i=0 ; $i < $rows ; $i++) +{ + $tmp= chr(65+$i) x ($i*10000+1); + $tmp= $dbh->quote($tmp); + $dbh->do("insert into $table (test) values ($tmp)") or die $DBI::errstr; +} + +print "Testing records\n"; + +$sth=$dbh->prepare("select * from $table") or die $dbh->errstr; +$sth->execute() or die $sth->errstr; + +$i=0; +while (($row = $sth->fetchrow_arrayref)) +{ + print $row->[0]," ",length($row->[1]),"\n"; + die "Record $i had wrong data in blob" if ($row->[1] ne (chr(65+$i)) x ($i*10000+1)); + $i++; +} + +die "Didn't get all rows from server" if ($i != $rows); + +$dbh->do("drop table $table") or die $DBI::errstr; + +print "Test ok\n"; +exit 0; diff --git a/tests/export.pl b/tests/export.pl new file mode 100755 index 00000000000..47a13bab8e6 --- /dev/null +++ b/tests/export.pl @@ -0,0 +1,164 @@ +#!/usr/bin/perl + +# This is a test with uses two processes to a database. +# The other inserts records in two tables, the other does a lot of joins +# on these. +# +# Warning, the output from this test will differ in 'found' from time to time, +# but there should never be any errors +# + +$host= shift || ""; +$test_db="test"; + +use Mysql; +$|= 1; # Autoflush + +$org_file="/tmp/export-org.$$"; +$tmp_file="/tmp/export-old.$$"; +$tmp_file2="/tmp/export-new.$$"; + +print "Connection to database $test_db\n"; + +$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; +$dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; + +$dbh->Query("drop table export"); # Ignore this error + +print "Creating table\n"; + +($dbh->Query("\ +CREATE TABLE export ( + auto int(5) unsigned NOT NULL DEFAULT '0' auto_increment, + string char(11) NOT NULL, + tiny tinyint(4) NOT NULL DEFAULT '0', + short smallint(6) NOT NULL DEFAULT '0', + medium mediumint(8) NOT NULL DEFAULT '0', + longint int(11) NOT NULL DEFAULT '0', + longlong bigint(20) NOT NULL DEFAULT '0', + real_float float(13,1) NOT NULL DEFAULT '0.0', + real_double double(13,1) NOT NULL, + utiny tinyint(3) unsigned NOT NULL DEFAULT '0', + ushort smallint(5) unsigned zerofill NOT NULL DEFAULT '00000', + umedium mediumint(8) unsigned NOT NULL DEFAULT '0', + ulong int(11) unsigned NOT NULL DEFAULT '0', + ulonglong bigint(20) unsigned NOT NULL DEFAULT '0', + time_stamp timestamp, + blob_col blob, + tinyblob_col tinyblob, + mediumblob_col tinyblob not null, + longblob_col longblob not null, + PRIMARY KEY (auto), + KEY (string(5)), + KEY unsigned_tinykey (utiny), + KEY (tiny), + KEY (short), + FOREIGN KEY (medium) references export, + KEY (longlong), + KEY (real_float), + KEY (real_double), + KEY (ushort), + KEY (umedium), + KEY (ulong), + KEY (ulonglong), + KEY (ulonglong,ulong))")) or die $Mysql::db_errstr; + +print "Inserting data\n"; + +@A=("insert into export values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1)", + "insert into export values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,2,2)", + "insert into export values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,3,'','','','3')", + "insert into export values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,'-1')", + "insert into export values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,'-4294967295')", + "insert into export values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,'4294967295')", + "insert into export (string,tinyblob_col) values ('special','''\\0\\t\t\n''')", + "insert into export (string) values (',,!!\\\\##')", + "insert into export (tinyblob_col) values (',,!!!\\\\\\##')" + ); + +foreach $A (@A) +{ + $dbh->Query($A) or die "query: $A returned: " . $Mysql::db_errstr; +} + + +print "Doing dump, load, check on different formats\n"; + +@A=(# Ordinary format + "", + # Field terminated by something + "fields optionally enclosed by '+' escaped by '' terminated by ',,,' lines terminated by ',,,,'", + "fields enclosed by '' terminated by ',' lines terminated by ''", + "fields enclosed by '' terminated by ',' lines terminated by '!!'", + #Fields enclosed by + #"fields enclosed by '+' terminated by ''", + #"fields enclosed by '+' terminated by '' lines terminated by ''", + "fields enclosed by '+' terminated by ',,' lines terminated by '!!!'", + "fields enclosed by '+' terminated by ',,' lines terminated by '##'", + "fields enclosed by '+' escaped by '' terminated by ',,' lines terminated by '###'", + "fields enclosed by '+' escaped by '' terminated by '!' lines terminated by ''", + "fields enclosed by '+' terminated by ',' lines terminated by ''", + #Fields optionally enclosed by + "fields optionally enclosed by '+' terminated by ','", + "fields optionally enclosed by '+' terminated by ',' lines terminated by ''", + "fields optionally enclosed by '''' terminated by ',' lines starting by 'INSERT INTO a VALUES(' terminated by ');\n'", + ); + +$dbh->Query("select * into outfile '$org_file' from export") or die $Mysql::db_errstr; + + +foreach $A (@A) +{ + unlink($tmp_file); + unlink($tmp_file2); + $dbh->Query("select * into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; + $dbh->Query("delete from export") or die $Mysql::db_errstr; + $dbh->Query("load data infile '$tmp_file' into table export $A") or die $Mysql::db_errstr . " with format: $A\n"; + $dbh->Query("select * into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; + if (`cmp $tmp_file2 $org_file`) + { + print "Using format $A\n"; + print "$tmp_file2 and $org_file differ. Plese check files\n"; + exit 1; + } +} + + +@A=(#Fixed size fields + "fields enclosed by '' escaped by '' terminated by ''", + "fields enclosed by '' escaped by '' terminated by '' lines terminated by '\\r\\n'", + "fields enclosed by '' terminated by '' lines terminated by ''" + ); + +unlink($org_file); + +$field_list="auto,ifnull(string,''),tiny,short,medium,longint,longlong,real_float,ifnull(real_double,''),utiny,ushort,umedium,ulong,ulonglong,time_stamp"; + +$dbh->Query("select $field_list into outfile '$org_file' from export") or die $Mysql::db_errstr; + +$field_list="auto,string,tiny,short,medium,longint,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,time_stamp"; + +foreach $A (@A) +{ + unlink($tmp_file); + unlink($tmp_file2); + $dbh->Query("select $field_list into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; + $dbh->Query("delete from export") or die $Mysql::db_errstr; + $dbh->Query("load data infile '$tmp_file' into table export $A ($field_list)") or die $Mysql::db_errstr; + $dbh->Query("select $field_list into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; + if (`cmp $tmp_file2 $org_file`) + { + print "Using format $A\n"; + print "$tmp_file2 and $org_file differ. Plese check files\n"; + exit 1; + } +} + +unlink($tmp_file); +unlink($tmp_file2); +unlink($org_file); + +$dbh->Query("drop table export") or die $Mysql::db_errstr; + +print "Test ok\n"; +exit 0; diff --git a/tests/fork2_test.pl b/tests/fork2_test.pl new file mode 100755 index 00000000000..e8a579d9d81 --- /dev/null +++ b/tests/fork2_test.pl @@ -0,0 +1,210 @@ +#!/usr/bin/perl -w + +# This is a test with uses 5 processes to insert, update and select from +# two tables. +# One inserts records in the tables, one updates some record in it and +# the last 3 does different selects on the tables. +# Er, hmmm..., something like that :^) +# Modified to do crazy-join, à la Nasdaq. + +$opt_loop_count=10000; # Change this to make test harder/easier + +##################### Standard benchmark inits ############################## + +use Mysql; +use Getopt::Long; +use Benchmark; + +package main; + +$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= + $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; +$opt_host=""; $opt_db="test"; + +GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in", + "skip-delete", "verbose","fast-insert","lock-tables","debug","fast", + "force") || die "Aborted"; +$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$Mysql::db_errstr=$opt_force=undef; # Ignore warnings from these + +print "Testing 9 multiple connections to a server with 1 insert/update\n"; +print "and 8 select connections.\n"; + + +@testtables = qw(bench_f21 bench_f22 bench_f23 bench_f24 bench_f25); +$numtables = $#testtables; # make emacs happier +$dtable = "directory"; +#### +#### Start timeing and start test +#### + +$start_time=new Benchmark; +if (!$opt_skip_create) +{ + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $Mysql::QUIET = 1; + foreach $table (@testtables) { + $dbh->Query("drop table $table"); + } + $dbh->Query("drop table $dtable"); + $Mysql::QUIET = 0; + + foreach $table (@testtables) { + print "Creating table $table in database $opt_db\n"; + $dbh->Query("create table $table". + " (id int(6) not null,". + " info varchar(32),". + " marker timestamp,". + " primary key(id))") + or die $Mysql::db_errstr; + } + print "Creating directory table $dtable in $opt_db\n"; + $dbh->Query("create table $dtable (id int(6), last int(6))") + or die $Mysql::db_errstr; + # Populate directory table + for $i ( 0 .. $numtables ) { + $dbh->Query("insert into $dtable values($i, 0)"); + } + $dbh=0; # Close handler +} +$|= 1; # Autoflush + +#### +#### Start the tests +#### + +#$test_index = 0; + +test_1() if (($pid=fork()) == 0); $work{$pid}="insert"; +test_2() if (($pid=fork()) == 0); $work{$pid}="simple1"; +test_3() if (($pid=fork()) == 0); $work{$pid}="funny1"; +test_2() if (($pid=fork()) == 0); $work{$pid}="simple2"; +test_3() if (($pid=fork()) == 0); $work{$pid}="funny2"; +test_2() if (($pid=fork()) == 0); $work{$pid}="simple3"; +test_3() if (($pid=fork()) == 0); $work{$pid}="funny3"; +test_2() if (($pid=fork()) == 0); $work{$pid}="simple4"; +test_3() if (($pid=fork()) == 0); $work{$pid}="funny4"; + +$errors=0; +while (($pid=wait()) != -1) +{ + $ret=$?/256; + print "thread '" . $work{$pid} . "' finnished with exit code $ret\n"; + $errors++ if ($ret != 0); +} + +if (!$opt_skip_delete && !$errors) +{ + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + foreach $table (@testtables) { + $dbh->Query("drop table $table"); + } +} +print ($errors ? "Test failed\n" :"Test ok\n"); + +$end_time=new Benchmark; +print "Total time: " . + timestr(timediff($end_time, $start_time),"noc") . "\n"; + +exit(0); + +# +# Insert records in the ?? tables the Nasdaq way +# + +sub test_1 +{ + my ($dbh,$table,$tmpvar,$rows,$found,$i); + + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=1; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); + # Nasdaq step 1: + $sth=$dbh->Query("select id,last from $dtable where id='$tmpvar'") + or die "Select directory row: $Mysql::db_errstr\n"; + # Nasdaq step 2: + my ($did,$dlast) = $sth->FetchRow + or die "Fetch directory row: $Mysql::db_errstr\n"; + $dlast++; + $sth=$dbh->Query("INSERT into $testtables[$did]". + " VALUES($dlast,'This is entry $dlast',NULL)") + || die "Got error on insert table $testtable[$did]:". + " $Mysql::db_errstr\n"; + # Nasdaq step 3 - where my application hangs + $sth=$dbh->Query("update $dtable set last='$dlast' where id='$tmpvar'") + or die "Updating directory for table $testtable[$did]:". + " Mysql::db_errstr\n"; + $rows++; + } + $dbh=0; + print "Test_1: Inserted $rows rows\n"; + exit(0); +} + +# +# Nasdaq simple select +# + +sub test_2 +{ + my ($dbh,$id,$tmpvar,$rows,$found,$i); + + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $rows=$found=0; + $tmpvar=1; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); + $sth=$dbh->Query("select a.id,a.info from $testtables[$tmpvar] as a,". + "$dtable as d". + " where a.id=d.last and $i >= 0") + || die "Got error select max: $Mysql::db_errstr\n"; + if ((@row = $sth->FetchRow()) && defined($row[0])) + { + $found++; + } + } + $dbh=0; + print "Test_2: Found $found rows\n"; + exit(0); +} + + +# +# Nasdaq not-so-simple select +# + +sub test_3 +{ + my ($dbh,$id,$tmpvar,$rows,$i); + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $rows=0; + $tmpvar ||= $numtables; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); + $id1 = ($tmpvar+1) % $numtables; + $id2 = ($id1+1) % $numtables; + $id3 = ($id2+1) % $numtables; + $sth = $dbh->Query("SELECT greatest(a.id, b.id, c.id), a.info". + " FROM $testtables[$id1] as a,". + " $testtables[$id2] as b,". + " $testtables[$id3] as c,". + " $dtable as d1, $dtable as d2, $dtable as d3". + " WHERE ". + " d1.last=a.id AND d2.last=b.id AND d3.last=c.id". + " AND d1.id='$id1' AND d2.id='$id2'". + " AND d3.id='$id3'") + or die "Funny select: $Mysql::db_errstr\n"; + $rows+=$sth->numrows; + } + $dbh=0; + print "Test_3: Found $rows rows\n"; + exit(0); +} + + + + diff --git a/tests/fork3_test.pl b/tests/fork3_test.pl new file mode 100755 index 00000000000..0ede221a7f4 --- /dev/null +++ b/tests/fork3_test.pl @@ -0,0 +1,150 @@ +#!/usr/bin/perl -w +# +# This is a test with uses 3 processes to insert, delete and select +# + +$opt_loop_count=100000; # Change this to make test harder/easier + +##################### Standard benchmark inits ############################## + +use DBI; +use Getopt::Long; +use Benchmark; + +package main; + +$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= + $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; +$opt_host=""; $opt_db="test"; + +GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in","skip-delete", +"verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted"; +$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these + +print "Testing 3 multiple connections to a server with 1 insert, 1 delete\n"; +print "and 1 select connections.\n"; + +$firsttable = "bench_f1"; + +#### +#### Start timeing and start test +#### + +$start_time=new Benchmark; +if (!$opt_skip_create) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $dbh->do("drop table if exists $firsttable"); + + print "Creating table $firsttable in database $opt_db\n"; + $dbh->do("create table $firsttable (id int(6) not null, info varchar(32), marker char(1), primary key(id))") || die $DBI::errstr; + $dbh->disconnect; $dbh=0; # Close handler +} +$|= 1; # Autoflush + +#### +#### Start the tests +#### + +test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; +test_delete() if (($pid=fork()) == 0); $work{$pid}="delete"; +test_select() if (($pid=fork()) == 0); $work{$pid}="select1"; + +$errors=0; +while (($pid=wait()) != -1) +{ + $ret=$?/256; + print "thread '" . $work{$pid} . "' finnished with exit code $ret\n"; + $errors++ if ($ret != 0); +} + +if (!$opt_skip_delete && !$errors) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $dbh->do("drop table $firsttable"); + $dbh->disconnect; $dbh=0; # Close handler +} +print ($errors ? "Test failed\n" :"Test ok\n"); + +$end_time=new Benchmark; +print "Total time: " . + timestr(timediff($end_time, $start_time),"noc") . "\n"; + +exit(0); + +# +# Insert records in the table +# + +sub test_insert +{ + my ($dbh,$i,$sth); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $sth=$dbh->do("insert into $firsttable values ($i,'This is entry $i','')") || die "Got error on insert: $Mysql::db_errstr\n"; + $sth=0; + } + $dbh->disconnect; $dbh=0; + print "Test_insert: Inserted $i rows\n"; + exit(0); +} + +sub test_delete +{ + my ($dbh,$i,$sth,@row); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + sleep(5); + if ($opt_lock_tables) + { + $sth=$dbh->do("lock tables $firsttable WRITE") || die "Got error on lock tables $firsttable: $Mysql::db_errstr\n"; + } + $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on select from $firsttable: $dbh->errstr\n"; + $sth->execute || die $dbh->errstr; + if ((@row = $sth->fetchrow_array())) + { + last if (!$row[0]); # Insert thread is probably ready + } + $sth=$dbh->do("delete from $firsttable") || die "Got error on delete from $firsttable: $dbh->errstr;\n"; + } + $sth=0; + $dbh->disconnect; $dbh=0; + print "Test_delete: Deleted all rows $i times\n"; + exit(0); +} + + +# +# select records +# + +sub test_select +{ + my ($dbh,$i,$sth,@row); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on select from $firsttable: $dbh->errstr;\n"; + $sth->execute || die $dbh->errstr; + @row = $sth->fetchrow_array(); + $sth=0; + } + $dbh->disconnect; $dbh=0; + print "Test_select: ok\n"; + exit(0); +} diff --git a/tests/fork_test.pl b/tests/fork_test.pl new file mode 100755 index 00000000000..cd166b84555 --- /dev/null +++ b/tests/fork_test.pl @@ -0,0 +1,247 @@ +#!/usr/bin/perl -w + +# This is a test with uses 5 processes to insert, update and select from +# two tables. +# One inserts records in the tables, one updates some record in it and +# the last 3 does different selects on the tables. +# + +$opt_loop_count=10000; # Change this to make test harder/easier + +##################### Standard benchmark inits ############################## + +use Mysql; +use Getopt::Long; +use Benchmark; + +package main; + +$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= + $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; +$opt_host=""; $opt_db="test"; + +GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in", + "skip-delete","verbose","fast-insert","lock-tables","debug","fast", + "force") || die "Aborted"; +$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$Mysql::db_errstr=$opt_force=undef; # Ignore warnings from these + +print "Testing 5 multiple connections to a server with 1 insert, 1 update\n"; +print "and 3 select connections.\n"; + + +$firsttable = "bench_f1"; +$secondtable = "bench_f2"; + +#### +#### Start timeing and start test +#### + +$start_time=new Benchmark; +if (!$opt_skip_create) +{ + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $Mysql::QUIET = 1; + $dbh->Query("drop table $firsttable"); + $dbh->Query("drop table $secondtable"); + $Mysql::QUIET = 0; + + print "Creating tables $firsttable and $secondtable in database $opt_db\n"; + $dbh->Query("create table $firsttable (id int(6) not null, info varchar(32), marker char(1), primary key(id))") or die $Mysql::db_errstr; + $dbh->Query("create table $secondtable (id int(6) not null, row int(3) not null,value double, primary key(id,row))") or die $Mysql::db_errstr; + + $dbh=0; # Close handler +} +$|= 1; # Autoflush + +#### +#### Start the tests +#### + +test_1() if (($pid=fork()) == 0); $work{$pid}="insert"; +test_2() if (($pid=fork()) == 0); $work{$pid}="update"; +test_3() if (($pid=fork()) == 0); $work{$pid}="select1"; +test_4() if (($pid=fork()) == 0); $work{$pid}="select2"; +test_5() if (($pid=fork()) == 0); $work{$pid}="select3"; + +$errors=0; +while (($pid=wait()) != -1) +{ + $ret=$?/256; + print "thread '" . $work{$pid} . "' finnished with exit code $ret\n"; + $errors++ if ($ret != 0); +} + +if (!$opt_skip_delete && !$errors) +{ + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $dbh->Query("drop table $firsttable"); + $dbh->Query("drop table $secondtable"); +} +print ($errors ? "Test failed\n" :"Test ok\n"); + +$end_time=new Benchmark; +print "Total time: " . + timestr(timediff($end_time, $start_time),"noc") . "\n"; + +exit(0); + +# +# Insert records in the two tables +# + +sub test_1 +{ + my ($dbh,$tmpvar,$rows,$found,$i); + + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=1; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $sth=$dbh->Query("insert into $firsttable values ($i,'This is entry $i','')") || die "Got error on insert: $Mysql::db_errstr\n"; + $row_count=($i % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $sth=$dbh->Query("insert into $secondtable values ($i,$j,0)") || die "Got error on insert: $Mysql::db_errstr\n"; + } + if (($tmpvar % 10) == 0) + { + $sth=$dbh->Query("select max(info) from $firsttable") || die "Got error on select max(info): $Mysql::db_errstr\n"; + $sth=$dbh->Query("select max(value) from $secondtable") || die "Got error on select max(info): $Mysql::db_errstr\n"; + $found+=2; + } + } + $dbh=0; + print "Test_1: Inserted $rows rows, found $found rows\n"; + exit(0); +} + +# +# Update records in both tables +# + +sub test_2 +{ + my ($dbh,$id,$tmpvar,$rows,$found,$i,$max_id,$tmp); + + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=111111; + $rows=$found=$max_id=$id=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmp=(($tmpvar + 63) + $i)*3; + $tmp=$tmp-int($tmp/100000)*100000; + $tmpvar^= $tmp; + $tmp=$tmpvar - int($tmpvar/10)*10; + if ($max_id < 2 || $tmp == 0) + { + $max_id=0; + $sth=$dbh->Query("select max(id) from $firsttable where marker=''") || die "Got error select max: $Mysql::db_errstr\n"; + if ((@row = $sth->FetchRow()) && defined($row[0])) + { + $found++; + $max_id=$id=$row[0]; + } + } + else + { + $id= $tmpvar % ($max_id-1)+1; + } + if ($id) + { + $sth=$dbh->Query("update $firsttable set marker='x' where id=$id") || die "Got error update $firsttable: $Mysql::db_errstr\n"; + $rows+=$sth->affected_rows; + if ($sth->affected_rows) + { + $sth=$dbh->Query("update $secondtable set value=$i where id=$id") || die "Got error update $firsttable: $Mysql::db_errstr\n"; + $rows+=$sth->affected_rows; + } + } + } + $dbh=0; + print "Test_2: Found $found rows, Updated $rows rows\n"; + exit(0); +} + + +# +# select records +# + +sub test_3 +{ + my ($dbh,$id,$tmpvar,$rows,$i); + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=222222; + $rows=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$tmpvar % $opt_loop_count; + $sth=$dbh->Query("select id from $firsttable where id=$id") || die "Got error on select from $firsttable: $Mysql::db_errstr\n"; + $rows+=$sth->numrows; + } + $dbh=0; + print "Test_3: Found $rows rows\n"; + exit(0); +} + + +# +# Note that this uses row=1 and in some cases won't find any matching +# records +# + +sub test_4 +{ + my ($dbh,$id,$tmpvar,$rows,$i); + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=333333; + $rows=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$tmpvar % $opt_loop_count; + $sth=$dbh->Query("select id from $secondtable where id=$id") || die "Got error on select form $secondtable: $Mysql::db_errstr\n"; + $rows+=$sth->numrows; + } + $dbh=0; + print "Test_4: Found $rows rows\n"; + exit(0); +} + + +sub test_5 +{ + my ($dbh,$id,$tmpvar,$rows,$i,$max_id); + $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; + $tmpvar=444444; + $rows=$max_id=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + if ($max_id == 0 || ($tmpvar % 10 == 0)) + { + $sth=$dbh->Query("select max(id) from $firsttable") || die "Got error select max: $Mysql::db_errstr\n"; + if ((@row = $sth->FetchRow()) && defined($row[0])) + { + $max_id=$id=$row[0]; + } + else + { + $id=0; + } + } + else + { + $id= $tmpvar % $max_id; + } + $sth=$dbh->Query("select value from $firsttable,$secondtable where $firsttable.id=$id and $secondtable.id=$firsttable.id") || die "Got error on select form $secondtable: $Mysql::db_errstr\n"; + $rows+=$sth->numrows; + } + $dbh=0; + print "Test_5: Found $rows rows\n"; + exit(0); +} diff --git a/tests/function.res b/tests/function.res new file mode 100644 index 00000000000..a2322020d74 --- /dev/null +++ b/tests/function.res @@ -0,0 +1,258 @@ +-------------- +select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2,sign(-5) +-------------- + +1+1 1-1 1+1*2 8/5 8%5 mod(8,5) mod(8,5)|0 -(1+1)*-2 sign(-5) +2 0 3 1.60 3 3 3 4 -1 +-------------- +select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5) +-------------- + +floor(5.5) floor(-5.5) ceiling(5.5) ceiling(-5.5) round(5.5) round(-5.5) +5 -6 6 -5 6 -6 +-------------- +select abs(-10),log(exp(10)),exp(log(sqrt(10))*2),pow(10,log10(10)),rand(999999),rand() +-------------- + +abs(-10) log(exp(10)) exp(log(sqrt(10))*2) pow(10,log10(10)) rand(999999) rand() +10 10.000000 10.000000 10.000000 0.1844 0.7637 +-------------- +select least(6,1.0,2.0),greatest(3,4,5,0) +-------------- + +least(6,1.0,2.0) greatest(3,4,5,0) +1.0 5 +-------------- +select 1 | (1+1),5 & 3,bit_count(7) +-------------- + +1 | (1+1) 5 & 3 bit_count(7) +3 1 3 +-------------- +select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") +-------------- + +0=0 1>0 1>=1 1<0 1<=0 strcmp("abc","abcd") strcmp("b","a") strcmp("a","a") +1 1 1 0 0 -1 1 0 +-------------- +select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b" +-------------- + +"a"<"b" "a"<="b" "b">="a" "b">"a" "a"="A" "a"<>"b" +1 1 1 1 1 1 +-------------- +select "abc" like "a%", "abc" not like "%d%", "ab" like "a\%", "a%" like "a\%","abcd" like "a%b_%d" +-------------- + +"abc" like "a%" "abc" not like "%d%" "ab" like "a\%" "a%" like "a\%" "abcd" like "a%b_%d" +1 1 0 1 1 +-------------- +select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$" +-------------- + +"Det här är svenska" regexp "h[[:alpha:]]+r" "aba" regexp "^(a|b)*$" +1 1 +-------------- +select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0 +-------------- + +!0 NOT 0=1 !(0=0) 1 AND 1 1 && 0 0 OR 1 1 || NULL 1=1 or 1=1 and 1=0 +1 1 0 1 0 1 1 1 +-------------- +select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 +-------------- + +IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0 +this is a 2 2.0 +-------------- +select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3 +-------------- + +2 between 1 and 3 "monty" between "max" and "my" 2=2 and "monty" between "max" and "my" and 3=3 +1 1 1 +-------------- +select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0) +-------------- + +2 in (3,2,5,9,5,1) "monty" in ("david","monty","allan") 1.2 in (1.4,1.2,1.0) +1 1 1 +-------------- +select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo' +-------------- + +hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo +hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo +-------------- +select concat("monty"," was here ","again"),length("hello"),ascii("hello") +-------------- + +concat("monty"," was here ","again") length("hello") ascii("hello") +monty was here again 5 104 +-------------- +select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2) +-------------- + +locate("he","hello") locate("he","hello",2) locate("lo","hello",2) +1 0 4 +-------------- +select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5) +-------------- + +left("hello",2) right("hello",2) substring("hello",2,2) mid("hello",1,5) +he lo el hello +-------------- +select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) +-------------- + +concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) +happy +-------------- +select concat("!",ltrim(" left "),"!",rtrim(" right "),"!") +-------------- + +concat("!",ltrim(" left "),"!",rtrim(" right "),"!") +!left ! right! +-------------- +select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es") +-------------- + +insert("txs",2,1,"hi") insert("is ",4,0,"a") insert("txxxxt",2,4,"es") +this is a test +-------------- +select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c") +-------------- + +replace("aaaa","a","b") replace("aaaa","aa","b") replace("aaaa","a","bb") replace("aaaa","","b") replace("bbbb","a","c") +bbbb bb bbbbbbbb aaaa bbbb +-------------- +select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") +-------------- + +replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") +this is a REAL test +-------------- +select soundex(""),soundex("he"),soundex("hello all folks") +-------------- + +soundex("") soundex("he") soundex("hello all folks") + H000 H4142 +-------------- +select password("test") +-------------- + +password("test") +378b243e220ca493 +-------------- +select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 +-------------- + +0x41 0x41+0 0x41 | 0x7fffffffffffffff | 0 0xffffffffffffffff | 0 +A 65 9223372036854775807 -1 +-------------- +select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0 +-------------- + +interval(55,10,20,30,40,50,60,70,80,90,100) interval(3,1,1+1,1+1+1+1) field("IBM","NCA","ICL","SUN","IBM","DIGITAL") field("A","B","C") elt(2,"ONE","TWO","THREE") interval(0,1,2,3,4) elt(1,1,2,3)|0 elt(1,1.1,1.2,1.3)+0 +5 2 4 0 TWO 0 1 1.1 +-------------- +select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555,3),format(123456.5555,4),format(1234567.5555,5),format("12345.2399",2) +-------------- + +format(1.5555,0) format(123.5555,1) format(1234.5555,2) format(12345.5555,3) format(123456.5555,4) format(1234567.5555,5) format("12345.2399",2) +2 123.6 1,234.56 12,345.556 123,456.5555 1,234,567.55550 12,345.24 +-------------- +select database(),user() +-------------- + +database() user() + monty +-------------- +select null,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null +-------------- + +NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null +NULL 1 1 1 1 TRUE TRUE 1 1 +-------------- +select 1 | NULL,1 & NULL,1+NULL,1-NULL +-------------- + +1 | NULL 1 & NULL 1+NULL 1-NULL +NULL NULL NULL NULL +-------------- +select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0 +-------------- + +NULL=NULL NULL<>NULL NULL IS NULL NULL IS NOT NULL IFNULL(NULL,1.1)+0 IFNULL(NULL,1) | 0 +NULL NULL 1 0 1.1 1 +-------------- +select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null +-------------- + +strcmp("a",NULL) (1<NULL)+0.0 NULL regexp "a" null like "a%" "a%" like null +NULL NULL NULL NULL NULL +-------------- +select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1) +-------------- + +concat("a",NULL) replace(NULL,"a","b") replace("string","i",NULL) replace("string",NULL,"i") insert("abc",1,1,NULL) left(NULL,1) +NULL NULL NULL NULL NULL NULL +-------------- +select field(NULL,"a","b","c") +-------------- + +field(NULL,"a","b","c") +0 +-------------- +select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null,2 between null and 1,2 between 3 AND NULL +-------------- + +2 between null and 1 2 between 3 AND NULL NULL between 1 and 2 2 between NULL and 3 2 between 1 AND null 2 between null and 1 2 between 3 AND NULL +0 0 NULL NULL NULL 0 0 +-------------- +select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2) +-------------- + +insert("aa",100,1,"b") insert("aa",1,3,"b") left("aa",-1) substring("a",1,2) +aa b a +-------------- +select elt(2,1),field(NULL,"a","b","c") +-------------- + +elt(2,1) field(NULL,"a","b","c") +NULL 0 +-------------- +select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a") +-------------- + +locate("a","b",2) locate("","a",1) ltrim("a") rtrim("a") +0 1 a a +-------------- +select concat("1","2")|0,concat("1",".5")+0.0 +-------------- + +concat("1","2")|0 concat("1",".5")+0.0 +12 1.5 +-------------- +select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01") +-------------- + +from_days(to_days("960101")) to_days(960201)-to_days("19960101") to_days(curdate()+1)-to_days(curdate()) weekday("1997-01-01") +1996-01-01 31 1 2 +-------------- +select period_add("9602",-12),period_diff(199505,"9404") +-------------- + +period_add("9602",-12) period_diff(199505,"9404") +199502 13 +-------------- +select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()) +-------------- + +now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) +0 0 0 +-------------- +select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now()) +-------------- + +now() now()+0 curdate() weekday(curdate()) weekday(now()) unix_timestamp() unix_timestamp(now()) +1998-08-17 04:24:33 19980817042433 1998-08-17 0 0 903317073 903317073 diff --git a/tests/function.tst b/tests/function.tst new file mode 100644 index 00000000000..5b8746e8d3c --- /dev/null +++ b/tests/function.tst @@ -0,0 +1,80 @@ +# Test of functions +# +# mysql -v < this_file + +# +# numerical functions +# +select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2,sign(-5) ; +select floor(5.5),floor(-5.5),ceiling(5.5),ceiling(-5.5),round(5.5),round(-5.5); +select abs(-10),log(exp(10)),exp(log(sqrt(10))*2),pow(10,log10(10)),rand(999999),rand(); +select least(6,1.0,2.0),greatest(3,4,5,0) ; +select 1 | (1+1),5 & 3,bit_count(7) ; +# +# test functions +# +select 0=0,1>0,1>=1,1<0,1<=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") ; +select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b"; +select "abc" like "a%", "abc" not like "%d%", "ab" like "a\%", "a%" like "a\%","abcd" like "a%b_%d"; +select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$"; +select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0; +select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 ; +select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3; +select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); + +# +# string functions +# +select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; +select concat("monty"," was here ","again"),length("hello"),ascii("hello"); +select locate("he","hello"),locate("he","hello",2),locate("lo","hello",2) ; +select left("hello",2),right("hello",2),substring("hello",2,2),mid("hello",1,5) ; +select concat("",left(right(concat("what ",concat("is ","happening")),9),4),"",substring("monty",5,1)) ; +select concat("!",ltrim(" left "),"!",rtrim(" right "),"!"); +select insert("txs",2,1,"hi"),insert("is ",4,0,"a"),insert("txxxxt",2,4,"es"); +select replace("aaaa","a","b"),replace("aaaa","aa","b"),replace("aaaa","a","bb"),replace("aaaa","","b"),replace("bbbb","a","c"); +select replace(concat(lcase(concat("THIS"," ","IS"," ","A"," ")),ucase("false")," ","test"),"FALSE","REAL") ; +select soundex(""),soundex("he"),soundex("hello all folks"); +select password("test"); +# +# varbinary as string and number +# +select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ; + +# +# misc functions +# +select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0; +select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555,3),format(123456.5555,4),format(1234567.5555,5),format("12345.2399",2); + +# +# system functions +# +select database(),user(); + +# +# Null tests +# +select null,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; +select 1 | NULL,1 & NULL,1+NULL,1-NULL; +select NULL=NULL,NULL<>NULL,NULL IS NULL, NULL IS NOT NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0; +select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null; +select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1); +select field(NULL,"a","b","c"); +select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null,2 between null and 1,2 between 3 AND NULL; +# +# Wrong or 'funny' use of functions. +# +select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2); +select elt(2,1),field(NULL,"a","b","c"); +select locate("a","b",2),locate("","a",1),ltrim("a"),rtrim("a"); +select concat("1","2")|0,concat("1",".5")+0.0; + +# +# time functions +# The last line should return new values for each test run +# +select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(curdate()+1)-to_days(curdate()),weekday("1997-01-01") ; +select period_add("9602",-12),period_diff(199505,"9404") ; +select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); +select now(),now()+0,curdate(),weekday(curdate()),weekday(now()),unix_timestamp(),unix_timestamp(now()); diff --git a/tests/grant.pl b/tests/grant.pl new file mode 100644 index 00000000000..a34a2909897 --- /dev/null +++ b/tests/grant.pl @@ -0,0 +1,545 @@ +#!/usr/bin/perl +# +# Testing of grants. +# Note that this will delete all table and column grants ! +# + +use DBI; +use Getopt::Long; +use strict; + +use vars qw($dbh $user_dbh $opt_help $opt_Information $opt_force $opt_debug + $opt_verbose $opt_server $opt_root_user $opt_password $opt_user + $opt_database $opt_host $version $user $tables_cols $columns_cols); + +$version="1.0"; +$opt_help=$opt_Information=$opt_force=$opt_debug=$opt_verbose=0; +$opt_host="localhost", +$opt_server="mysql"; +$opt_root_user="root"; +$opt_password=""; +$opt_user="grant_user"; +$opt_database="grant_test"; + +GetOptions("Information","help","server=s","root-user=s","password=s","user","database=s","force","host=s","debug","verbose") || usage(); +usage() if ($opt_help || $opt_Information); + +$user="$opt_user\@$opt_host"; + +if (!$opt_force) +{ + print_info() +} + +$|=1; + +$tables_cols="Host, Db, User, Table_name, Grantor, Table_priv, Column_priv"; +$columns_cols="Host, Db, User, Table_name, Column_name, Column_priv"; + +# +# clear grant tables +# + +$dbh = DBI->connect("DBI:mysql:mysql:$opt_host;mysql_read_default_group=perl", + $opt_root_user,$opt_password, + { PrintError => 0}) || die "Can't connect to mysql server: $DBI::errstr\n"; + +safe_query("delete from user where user='$opt_user' or user='${opt_user}2'"); +safe_query("delete from db where user='$opt_user'"); +safe_query("delete from tables_priv"); +safe_query("delete from columns_priv"); +safe_query("lock tables mysql.user write"); # Test lock tables +safe_query("flush privileges"); +safe_query("unlock tables"); # should already be unlocked +safe_query("drop database $opt_database",2); +safe_query("create database $opt_database"); + +# check that the user can't login yet + +user_connect(1); +#goto test; + +# +# Test grants on user level +# + +safe_query("grant select on *.* to $user"); +safe_query("set password FOR ${opt_user}2\@$opt_host = password('test')",1); +safe_query("set password FOR $opt_user=password('test')"); +user_connect(1); +safe_query("set password FOR $opt_user=''"); +user_connect(0); +user_query("select * from mysql.user where user = '$opt_user'"); +user_query("select * from mysql.db where user = '$opt_user'"); +safe_query("grant select on *.* to $user,$user"); + +# The following should fail +user_query("insert into mysql.user (host,user) values ('error','$opt_user')",1); +user_query("update mysql.user set host='error' WHERE user='$opt_user'",1); +user_query("create table $opt_database.test (a int,b int)",1); +user_query("grant select on *.* to ${opt_user}2\@$opt_host",1); +safe_query("revoke select on $opt_database.test from $opt_user\@opt_host",1); +safe_query("revoke select on $opt_database.* from $opt_user\@opt_host",1); +safe_query("revoke select on *.* from $opt_user",1); +safe_query("grant select on $opt_database.not_exists to $opt_user",1); +safe_query("grant FILE on $opt_database.test to $opt_user",1); +safe_query("grant select on *.* to wrong___________user_name",1); +safe_query("grant select on $opt_database.* to wrong___________user_name",1); +user_query("grant select on $opt_database.test to $opt_user with grant option",1); +safe_query("set password FOR ''\@''=''",1); +user_query("set password FOR root\@$opt_host = password('test')",1); + +# Change privileges for user +safe_query("revoke select on *.* from $user"); +safe_query("grant create on *.* to $user"); +user_connect(0); +user_query("create table $opt_database.test (a int,b int)"); + +safe_query("grant select(c) on $opt_database.test to $user",1); +safe_query("revoke select(c) on $opt_database.test from $user",1); +safe_query("grant select on $opt_database.test to wrong___________user_name",1); +user_query("INSERT INTO $opt_database.test values (2,0)",1); + +safe_query("grant ALL PRIVILEGES on *.* to $user"); +safe_query("REVOKE INSERT on *.* from $user"); +user_connect(0); +user_query("INSERT INTO $opt_database.test values (1,0)",1); +safe_query("grant INSERT on *.* to $user"); +user_connect(0); +user_query("INSERT INTO $opt_database.test values (2,0)"); +user_query("select count(*) from $opt_database.test"); +safe_query("revoke SELECT on *.* from $user"); +user_connect(0); +user_query("select count(*) from $opt_database.test",1); +user_query("INSERT INTO $opt_database.test values (3,0)"); +safe_query("grant SELECT on *.* to $user"); +user_connect(0); +user_query("select count(*) from $opt_database.test"); +safe_query("revoke ALL PRIVILEGES on *.* from $user"); +user_connect(1); +safe_query("delete from user where user='$opt_user'"); +safe_query("flush privileges"); +if (0) # Only if no anonymous user on localhost. +{ + safe_query("grant select on *.* to $opt_user"); + user_connect(0); + safe_query("revoke select on *.* from $opt_user"); + user_connect(1); +} +safe_query("delete from user where user='$opt_user'"); +safe_query("flush privileges"); + +# +# Test grants on database level +# +safe_query("grant select on $opt_database.* to $user"); +safe_query("select * from mysql.user where user = '$opt_user'"); +safe_query("select * from mysql.db where user = '$opt_user'"); +user_connect(0); +user_query("select count(*) from $opt_database.test"); +# The following should fail +user_query("select * from mysql.user where user = '$opt_user'",1); +user_query("insert into $opt_database.test values (4,0)",1); +user_query("update $opt_database.test set a=1",1); +user_query("delete from $opt_database.test",1); +user_query("create table $opt_database.test2 (a int)",1); +user_query("ALTER TABLE $opt_database.test add c int",1); +user_query("CREATE INDEX dummy ON $opt_database.test (a)",1); +user_query("drop table $opt_database.test",1); +user_query("grant ALL PRIVILEGES on $opt_database.* to ${opt_user}2\@$opt_host",1); + +# Change privileges for user +safe_query("grant ALL PRIVILEGES on $opt_database.* to $user WITH GRANT OPTION"); +user_connect(0); +user_query("insert into $opt_database.test values (5,0)"); +safe_query("REVOKE ALL PRIVILEGES on * from $user",1); +safe_query("REVOKE ALL PRIVILEGES on *.* from $user"); +safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user"); +safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user"); +user_connect(0); +user_query("insert into $opt_database.test values (6,0)",1); +safe_query("REVOKE GRANT OPTION on $opt_database.* from $user"); +user_connect(1); +safe_query("grant ALL PRIVILEGES on $opt_database.* to $user"); + +user_connect(0); +user_query("select * from mysql.user where user = '$opt_user'",1); +user_query("insert into $opt_database.test values (7,0)"); +user_query("update $opt_database.test set a=3 where a=2"); +user_query("delete from $opt_database.test where a=3"); +user_query("create table $opt_database.test2 (a int not null)"); +user_query("alter table $opt_database.test2 add b int"); +user_query("create index dummy on $opt_database.test2 (a)"); +user_query("drop table $opt_database.test2"); +user_query("show tables"); + +# These should fail +user_query("insert into mysql.user (host,user) values ('error','$opt_user',0)",1); + +# Revoke database privileges +safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user"); +safe_query("select * from mysql.user where user = '$opt_user'"); +safe_query("select * from mysql.db where user = '$opt_user'"); +user_connect(1); + +# +# Test of grants on table level +# + +safe_query("grant create on $opt_database.test2 to $user"); +user_connect(0); +user_query("create table $opt_database.test2 (a int not null)"); +user_query("show tables"); # Should only show test, not test2 +user_query("show columns from test",1); +user_query("show keys from test",1); +user_query("show columns from test2"); +user_query("show keys from test2"); +user_query("select * from test",1); +safe_query("grant insert on $opt_database.test to $user"); +user_query("show tables"); +user_query("insert into $opt_database.test values (8,0)"); +user_query("update $opt_database.test set b=1",1); +safe_query("grant update on $opt_database.test to $user"); +user_query("update $opt_database.test set b=2"); +user_query("delete from $opt_database.test",1); +safe_query("grant delete on $opt_database.test to $user"); +user_query("delete from $opt_database.test where a=1",1); +user_query("update $opt_database.test set b=3 where b=1",1); +user_query("update $opt_database.test set b=b+1",1); + +# Add one privilege at a time until the user has all privileges +user_query("select * from test",1); +safe_query("grant select on $opt_database.test to $user"); +user_query("delete from $opt_database.test where a=1"); +user_query("update $opt_database.test set b=2 where b=1"); +user_query("update $opt_database.test set b=b+1"); +user_query("select count(*) from test"); + +user_query("create table $opt_database.test3 (a int)",1); +user_query("alter table $opt_database.test2 add c int",1); +safe_query("grant alter on $opt_database.test2 to $user"); +user_query("alter table $opt_database.test2 add c int"); +user_query("create index dummy ON $opt_database.test (a)",1); +safe_query("grant index on $opt_database.test2 to $user"); +user_query("create index dummy ON $opt_database.test2 (a)"); +user_query("insert into test2 SELECT a,a from test",1); +safe_query("grant insert on test2 to $user",1); # No table: mysql.test2 +safe_query("grant insert(a) on $opt_database.test2 to $user"); +user_query("insert into test2 SELECT a,a from test",1); +safe_query("grant insert(c) on $opt_database.test2 to $user"); +user_query("insert into test2 SELECT a,a from test"); +user_query("select count(*) from test2,test",1); +user_query("select count(*) from test,test2",1); +user_query("replace into test2 SELECT a from test",1); +safe_query("grant update on $opt_database.test2 to $user"); +user_query("replace into test2 SELECT a,a from test",1); +safe_query("grant DELETE on $opt_database.test2 to $user"); +user_query("replace into test2 SELECT a,a from test"); +user_query("insert into test (a) SELECT a from test2",1); + +user_query("drop table $opt_database.test2",1); +user_query("grant select on $opt_database.test2 to $user with grant option",1); +safe_query("grant drop on $opt_database.test2 to $user with grant option"); +user_query("grant drop on $opt_database.test2 to $user with grant option"); +user_query("grant select on $opt_database.test2 to $user with grant option",1); +user_query("drop table $opt_database.test2"); + +# Check that the user doesn't have some user privileges +user_query("create database $opt_database",1); +user_query("drop database $opt_database",1); +user_query("flush tables",1); +safe_query("flush privileges"); + +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); +safe_query("revoke ALL PRIVILEGES on $opt_database.test2 from $user"); +safe_query("revoke GRANT OPTION on $opt_database.test2 from $user"); +safe_query("select $tables_cols from mysql.tables_priv"); + +user_query("select count(a) from test",1); + +# +# Test some grants on column level +# + +user_query("delete from $opt_database.test where a=2",1); +user_query("delete from $opt_database.test where A=2",1); +user_query("update test set b=5 where b>0",1); +safe_query("grant update(b),delete on $opt_database.test to $user"); +safe_query("revoke update(a) on $opt_database.test from $user",1); +user_query("delete from $opt_database.test where a=2",1); +user_query("update test set b=5 where b>0",1); +safe_query("grant select(a),select(b) on $opt_database.test to $user"); +user_query("delete from $opt_database.test where a=2"); +user_query("delete from $opt_database.test where A=2"); +user_query("update test set b=5 where b>0"); +user_query("update test set a=11 where b>5",1); +user_query("select a,A from test"); + +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("revoke GRANT OPTION on $opt_database.test from $user",1); +# +# Test grants on database level +# + +safe_query("grant select(a) on $opt_database.test to $user"); +user_query("show columns from test"); +safe_query("grant insert (b), update (b) on $opt_database.test to $user"); + +user_query("select count(a) from test"); +user_query("select count(skr.a) from test as skr"); +user_query("select count(a) from test where a > 5"); +user_query("insert into test (b) values (5)"); +user_query("insert into test (b) values (a)"); +user_query("update test set b=3 where a > 0"); + +user_query("select * from test",1); +user_query("select b from test",1); +user_query("select a from test where b > 0",1); +user_query("insert into test (a) values (10)",1); +user_query("insert into test (b) values (b)",1); +user_query("insert into test (a,b) values (1,5)",1); +user_query("insert into test (b) values (1),(b)",1); +user_query("update test set b=3 where b > 0",1); + +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("select $columns_cols from mysql.columns_priv"); +safe_query("revoke select(a), update (b) on $opt_database.test from $user"); +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("select $columns_cols from mysql.columns_priv"); + +user_query("select count(a) from test",1); +user_query("update test set b=4",1); + +safe_query("grant select(a,b), update (a,b) on $opt_database.test to $user"); +user_query("select count(a),count(b) from test where a+b > 0"); +user_query("insert into test (b) values (9)"); +user_query("update test set b=6 where b > 0"); + +safe_query("flush privileges"); # Test restoring privileges from disk +safe_query("select $tables_cols from mysql.tables_priv"); +safe_query("select $columns_cols from mysql.columns_priv"); + +# Try mixing of table and database privileges + +user_query("insert into test (a,b) values (12,12)",1); +safe_query("grant insert on $opt_database.* to $user"); +user_connect(0); +user_query("insert into test (a,b) values (13,13)"); + +# This grants and revokes SELECT on different levels. +safe_query("revoke select(b) on $opt_database.test from $user"); +user_query("select count(a) from test where a+b > 0",1); +user_query("update test set b=5 where a=2"); +safe_query("grant select on $opt_database.test to $user"); +user_connect(0); +user_query("select count(a) from test where a+b > 0"); +safe_query("revoke select(b) on $opt_database.test from $user"); +user_query("select count(a) from test where a+b > 0"); +safe_query("revoke select on $opt_database.test from $user"); +user_connect(0); +user_query("select count(a) from test where a+b > 0",1); +safe_query("grant select(a) on $opt_database.test to $user"); +user_query("select count(a) from test where a+b > 0",1); +safe_query("grant select on *.* to $user"); +user_connect(0); +user_query("select count(a) from test where a+b > 0"); +safe_query("revoke select on *.* from $user"); +safe_query("grant select(b) on $opt_database.test to $user"); +user_connect(0); +user_query("select count(a) from test where a+b > 0"); + + +safe_query("select * from mysql.db where user = '$opt_user'"); +safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'"); +safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'"); + +safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user"); +user_query("select count(a) from test",1); +user_query("select * from mysql.user",1); +safe_query("select * from mysql.db where user = '$opt_user'"); +safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'"); +safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'"); + +# +# Test IDENTIFIED BY +# + +safe_query("delete from user where user='$opt_user'"); +safe_query("flush privileges"); +safe_query("grant ALL PRIVILEGES on $opt_database.test to $user identified by 'dummy', ${opt_user}\@127.0.0.1 identified by 'dummy2'"); +user_connect(0,"dummy"); +safe_query("grant SELECT on $opt_database.* to $user identified by ''"); +user_connect(0); + +# +# Clean up things +# + +safe_query("drop database $opt_database"); +safe_query("delete from user where user='$opt_user'"); +safe_query("delete from db where user='$opt_user'"); +safe_query("delete from tables_priv"); +safe_query("delete from columns_priv"); +safe_query("flush privileges"); + +print "end of test\n"; +exit 0; + +sub usage +{ + print <<EOF; +$0 Ver $version + +This program tests that the GRANT commands works by creating a temporary +database ($opt_database) and user ($opt_user). + +Options: + +--database (Default $opt_database) + In which database the test tables are created. + +--force + Don''t ask any question before starting this test. + +--host='host name' (Default $opt_host) + Host name where the database server is located. + +--Information +--help + Print this help + +--password + Password for root-user. + +--server='server name' (Default $opt_server) + Run the test on the given SQL server. + +--user (Default $opt_user) + A non-existing user on which we will test the GRANT commands. + +--verbose + Write all queries when we are execute them. + +--root-user='user name' (Default $opt_root_user) + User with privileges to modify the 'mysql' database. +EOF + exit(0); +} + + +sub print_info +{ + my $tmp; + print <<EOF; +This test will clear your table and column grant table and recreate the +$opt_database database ! All privileges for $user will be destroyed ! + +Don\'t run this test if you have done any GRANT commands that you want to keep! +EOF + for (;;) + { + print "Start test (yes/no) ? "; + $tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp); + last if ($tmp =~ /^yes$/i); + exit 1 if ($tmp =~ /^n/i); + print "\n"; + } +} + + +sub user_connect +{ + my ($ignore_error,$password)=@_; + $password="" if (!defined($password)); + + print "Connecting $opt_user\n" if ($opt_verbose); + $user_dbh->disconnect if (defined($user_dbh)); + + $user_dbh=DBI->connect("DBI:mysql:$opt_database:$opt_host",$opt_user, + $password, { PrintError => 0}); + if (!$user_dbh) + { + print "$DBI::errstr\n"; + if (!$ignore_error) + { + die "The above should not have failed!"; + } + } + elsif ($ignore_error) + { + die "Connect succeeded when it shouldn't have !\n"; + } +} + +sub safe_query +{ + my ($query,$ignore_error)=@_; + if (do_query($dbh,$query)) + { + if (!defined($ignore_error)) + { + die "The above should not have failed!"; + } + } + elsif (defined($ignore_error) && $ignore_error == 1) + { + die "Query '$query' succeeded when it shouldn't have !\n"; + } +} + + +sub user_query +{ + my ($query,$ignore_error)=@_; + if (do_query($user_dbh,$query)) + { + if (!defined($ignore_error)) + { + die "The above should not have failed!"; + } + } + elsif (defined($ignore_error) && $ignore_error == 1) + { + die "Query '$query' succeeded when it shouldn't have !\n"; + } +} + + +sub do_query +{ + my ($my_dbh, $query)=@_; + my ($sth,$row,$tab,$col,$found); + + print "$query\n" if ($opt_debug || $opt_verbose); + if (!($sth= $my_dbh->prepare($query))) + { + print "Error in prepare: $DBI::errstr\n"; + return 1; + } + if (!$sth->execute) + { + print "Error in execute: $DBI::errstr\n"; + die if ($DBI::errstr =~ /parse error/); + $sth->finish; + return 1; + } + $found=0; + while (($row=$sth->fetchrow_arrayref)) + { + $found=1; + $tab=""; + foreach $col (@$row) + { + print $tab; + print defined($col) ? $col : "NULL"; + $tab="\t"; + } + print "\n"; + } + print "\n" if ($found); + $sth->finish; + return 0; +} diff --git a/tests/grant.res b/tests/grant.res new file mode 100644 index 00000000000..7cc08807fd1 --- /dev/null +++ b/tests/grant.res @@ -0,0 +1,436 @@ +delete from user where user='grant_user' or user='grant_user2' +delete from db where user='grant_user' +delete from tables_priv +delete from columns_priv +lock tables mysql.user write +flush privileges +unlock tables +drop database grant_test +Error in execute: Can't drop database 'grant_test'. Database doesn't exist +create database grant_test +Connecting grant_user +Access denied for user: '@localhost' to database 'grant_test' +grant select on *.* to grant_user@localhost +set password FOR grant_user2@localhost = password('test') +Error in execute: Can't find any matching row in the user table +set password FOR grant_user=password('test') +Connecting grant_user +Access denied for user: 'grant_user@localhost' (Using password: NO) +set password FOR grant_user='' +Connecting grant_user +select * from mysql.user where user = 'grant_user' +localhost grant_user Y N N N N N N N N N N N N N + +select * from mysql.db where user = 'grant_user' +grant select on *.* to grant_user@localhost,grant_user@localhost +insert into mysql.user (host,user) values ('error','grant_user') +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +update mysql.user set host='error' WHERE user='grant_user' +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +create table grant_test.test (a int,b int) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +grant select on *.* to grant_user2@localhost +Error in execute: Access denied for user: 'grant_user@localhost' (Using password: NO) +revoke select on grant_test.test from grant_user@opt_host +Error in execute: There is no such grant defined for user 'grant_user' on host 'opt_host' +revoke select on grant_test.* from grant_user@opt_host +Error in execute: There is no such grant defined for user 'grant_user' on host 'opt_host' +revoke select on *.* from grant_user +Error in execute: There is no such grant defined for user 'grant_user' on host '%' +grant select on grant_test.not_exists to grant_user +Error in execute: Table 'grant_test.not_exists' doesn't exist +grant FILE on grant_test.test to grant_user +Error in execute: Illegal GRANT/REVOKE command. Please consult the manual which privileges can be used. +grant select on *.* to wrong___________user_name +Error in execute: The host or user argument to GRANT is too long +grant select on grant_test.* to wrong___________user_name +Error in execute: The host or user argument to GRANT is too long +grant select on grant_test.test to grant_user with grant option +Error in execute: grant command denied to user: 'grant_user@localhost' for table 'test' +set password FOR ''@''='' +Error in execute: You are using MySQL as an anonymous users and anonymous users are not allowed to change passwords +set password FOR root@localhost = password('test') +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +revoke select on *.* from grant_user@localhost +grant create on *.* to grant_user@localhost +Connecting grant_user +create table grant_test.test (a int,b int) +grant select(c) on grant_test.test to grant_user@localhost +Error in execute: Unknown column 'c' in 'test' +revoke select(c) on grant_test.test from grant_user@localhost +Error in execute: There is no such grant defined for user 'grant_user' on host 'localhost' on table 'test' +grant select on grant_test.test to wrong___________user_name +Error in execute: The host or user argument to GRANT is too long +INSERT INTO grant_test.test values (2,0) +Error in execute: insert command denied to user: 'grant_user@localhost' for table 'test' +grant ALL PRIVILEGES on *.* to grant_user@localhost +REVOKE INSERT on *.* from grant_user@localhost +Connecting grant_user +INSERT INTO grant_test.test values (1,0) +Error in execute: insert command denied to user: 'grant_user@localhost' for table 'test' +grant INSERT on *.* to grant_user@localhost +Connecting grant_user +INSERT INTO grant_test.test values (2,0) +select count(*) from grant_test.test +1 + +revoke SELECT on *.* from grant_user@localhost +Connecting grant_user +select count(*) from grant_test.test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +INSERT INTO grant_test.test values (3,0) +grant SELECT on *.* to grant_user@localhost +Connecting grant_user +select count(*) from grant_test.test +2 + +revoke ALL PRIVILEGES on *.* from grant_user@localhost +Connecting grant_user +Access denied for user: 'grant_user@localhost' to database 'grant_test' +delete from user where user='grant_user' +flush privileges +delete from user where user='grant_user' +flush privileges +grant select on grant_test.* to grant_user@localhost +select * from mysql.user where user = 'grant_user' +localhost grant_user N N N N N N N N N N N N N N + +select * from mysql.db where user = 'grant_user' +localhost grant_test grant_user Y N N N N N N N N N + +Connecting grant_user +select count(*) from grant_test.test +2 + +select * from mysql.user where user = 'grant_user' +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +insert into grant_test.test values (4,0) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +update grant_test.test set a=1 +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +delete from grant_test.test +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +create table grant_test.test2 (a int) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +ALTER TABLE grant_test.test add c int +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +CREATE INDEX dummy ON grant_test.test (a) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +drop table grant_test.test +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +grant ALL PRIVILEGES on grant_test.* to grant_user2@localhost +Error in execute: Access denied for user: 'grant_user@localhost' (Using password: NO) +grant ALL PRIVILEGES on grant_test.* to grant_user@localhost WITH GRANT OPTION +Connecting grant_user +insert into grant_test.test values (5,0) +REVOKE ALL PRIVILEGES on * from grant_user@localhost +Error in execute: There is no such grant defined for user 'grant_user' on host 'localhost' +REVOKE ALL PRIVILEGES on *.* from grant_user@localhost +REVOKE ALL PRIVILEGES on grant_test.* from grant_user@localhost +REVOKE ALL PRIVILEGES on grant_test.* from grant_user@localhost +Connecting grant_user +insert into grant_test.test values (6,0) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +REVOKE GRANT OPTION on grant_test.* from grant_user@localhost +Connecting grant_user +Access denied for user: 'grant_user@localhost' to database 'grant_test' +grant ALL PRIVILEGES on grant_test.* to grant_user@localhost +Connecting grant_user +select * from mysql.user where user = 'grant_user' +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +insert into grant_test.test values (7,0) +update grant_test.test set a=3 where a=2 +delete from grant_test.test where a=3 +create table grant_test.test2 (a int not null) +alter table grant_test.test2 add b int +create index dummy on grant_test.test2 (a) +drop table grant_test.test2 +show tables +insert into mysql.user (host,user) values ('error','grant_user',0) +Error in execute: Access denied for user: 'grant_user@localhost' to database 'mysql' +revoke ALL PRIVILEGES on grant_test.* from grant_user@localhost +select * from mysql.user where user = 'grant_user' +localhost grant_user N N N N N N N N N N N N N N + +select * from mysql.db where user = 'grant_user' +Connecting grant_user +Access denied for user: 'grant_user@localhost' to database 'grant_test' +grant create on grant_test.test2 to grant_user@localhost +Connecting grant_user +create table grant_test.test2 (a int not null) +show tables +test2 + +show columns from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +show keys from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +show columns from test2 +a int(11) 0 + +show keys from test2 +select * from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +grant insert on grant_test.test to grant_user@localhost +show tables +test +test2 + +insert into grant_test.test values (8,0) +update grant_test.test set b=1 +Error in execute: update command denied to user: 'grant_user@localhost' for table 'test' +grant update on grant_test.test to grant_user@localhost +update grant_test.test set b=2 +delete from grant_test.test +Error in execute: delete command denied to user: 'grant_user@localhost' for table 'test' +grant delete on grant_test.test to grant_user@localhost +delete from grant_test.test where a=1 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +update grant_test.test set b=3 where b=1 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +update grant_test.test set b=b+1 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +select * from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +grant select on grant_test.test to grant_user@localhost +delete from grant_test.test where a=1 +update grant_test.test set b=2 where b=1 +update grant_test.test set b=b+1 +select count(*) from test +3 + +create table grant_test.test3 (a int) +Error in execute: create command denied to user: 'grant_user@localhost' for table 'test3' +alter table grant_test.test2 add c int +Error in execute: alter command denied to user: 'grant_user@localhost' for table 'test2' +grant alter on grant_test.test2 to grant_user@localhost +alter table grant_test.test2 add c int +create index dummy ON grant_test.test (a) +Error in execute: index command denied to user: 'grant_user@localhost' for table 'test' +grant index on grant_test.test2 to grant_user@localhost +create index dummy ON grant_test.test2 (a) +insert into test2 SELECT a,a from test +Error in execute: insert command denied to user: 'grant_user@localhost' for table 'test2' +grant insert on test2 to grant_user@localhost +Error in execute: Table 'mysql.test2' doesn't exist +grant insert(a) on grant_test.test2 to grant_user@localhost +insert into test2 SELECT a,a from test +Error in execute: insert command denied to user: 'grant_user@localhost' for column 'c' in table 'test2' +grant insert(c) on grant_test.test2 to grant_user@localhost +insert into test2 SELECT a,a from test +select count(*) from test2,test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test2' +select count(*) from test,test2 +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test2' +replace into test2 SELECT a from test +Error in execute: update command denied to user: 'grant_user@localhost' for table 'test2' +grant update on grant_test.test2 to grant_user@localhost +replace into test2 SELECT a,a from test +Error in execute: delete command denied to user: 'grant_user@localhost' for table 'test2' +grant DELETE on grant_test.test2 to grant_user@localhost +replace into test2 SELECT a,a from test +insert into test (a) SELECT a from test2 +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test2' +drop table grant_test.test2 +Error in execute: drop command denied to user: 'grant_user@localhost' for table 'test2' +grant select on grant_test.test2 to grant_user@localhost with grant option +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test2' +grant drop on grant_test.test2 to grant_user@localhost with grant option +grant drop on grant_test.test2 to grant_user@localhost with grant option +grant select on grant_test.test2 to grant_user@localhost with grant option +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test2' +drop table grant_test.test2 +create database grant_test +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +drop database grant_test +Error in execute: Access denied for user: 'grant_user@localhost' to database 'grant_test' +flush tables +Error in execute: Access denied for user: 'grant_user@localhost' (Using password: NO) +flush privileges +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 grant_user@localhost Update,Delete,Create,Drop,Grant,Index,Alter Insert +localhost grant_test grant_user test root@localhost Select,Insert,Update,Delete + +revoke ALL PRIVILEGES on grant_test.test from grant_user@localhost +revoke ALL PRIVILEGES on grant_test.test2 from grant_user@localhost +revoke GRANT OPTION on grant_test.test2 from grant_user@localhost +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter + +select count(a) from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +delete from grant_test.test where a=2 +Error in execute: delete command denied to user: 'grant_user@localhost' for table 'test' +delete from grant_test.test where A=2 +Error in execute: delete command denied to user: 'grant_user@localhost' for table 'test' +update test set b=5 where b>0 +Error in execute: update command denied to user: 'grant_user@localhost' for table 'test' +grant update(b),delete on grant_test.test to grant_user@localhost +revoke update(a) on grant_test.test from grant_user@localhost +Error in execute: There is no such grant defined for user 'grant_user' on host 'localhost' on table 'test' +delete from grant_test.test where a=2 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +update test set b=5 where b>0 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +grant select(a),select(b) on grant_test.test to grant_user@localhost +delete from grant_test.test where a=2 +delete from grant_test.test where A=2 +update test set b=5 where b>0 +update test set a=11 where b>5 +Error in execute: update command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +select a,A from test +8 8 +5 5 +7 7 + +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter +localhost grant_test grant_user test root@localhost Delete Select,Update + +revoke ALL PRIVILEGES on grant_test.test from grant_user@localhost +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter + +revoke GRANT OPTION on grant_test.test from grant_user@localhost +Error in execute: There is no such grant defined for user 'grant_user' on host 'localhost' on table 'test' +grant select(a) on grant_test.test to grant_user@localhost +show columns from test +a int(11) YES NULL select +b int(11) YES NULL + +grant insert (b), update (b) on grant_test.test to grant_user@localhost +select count(a) from test +3 + +select count(skr.a) from test as skr +3 + +select count(a) from test where a > 5 +2 + +insert into test (b) values (5) +insert into test (b) values (a) +update test set b=3 where a > 0 +select * from test +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +select b from test +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +select a from test where b > 0 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +insert into test (a) values (10) +Error in execute: insert command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +insert into test (b) values (b) +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +insert into test (a,b) values (1,5) +Error in execute: insert command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +insert into test (b) values (1),(b) +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +update test set b=3 where b > 0 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter +localhost grant_test grant_user test root@localhost Select,Insert,Update + +select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv +localhost grant_test grant_user test b Insert,Update +localhost grant_test grant_user test a Select + +revoke select(a), update (b) on grant_test.test from grant_user@localhost +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter +localhost grant_test grant_user test root@localhost Insert + +select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv +localhost grant_test grant_user test b Insert + +select count(a) from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +update test set b=4 +Error in execute: update command denied to user: 'grant_user@localhost' for table 'test' +grant select(a,b), update (a,b) on grant_test.test to grant_user@localhost +select count(a),count(b) from test where a+b > 0 +3 3 + +insert into test (b) values (9) +update test set b=6 where b > 0 +flush privileges +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter +localhost grant_test grant_user test root@localhost Select,Insert,Update + +select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv +localhost grant_test grant_user test b Select,Insert,Update +localhost grant_test grant_user test a Select,Update + +insert into test (a,b) values (12,12) +Error in execute: insert command denied to user: 'grant_user@localhost' for column 'a' in table 'test' +grant insert on grant_test.* to grant_user@localhost +Connecting grant_user +insert into test (a,b) values (13,13) +revoke select(b) on grant_test.test from grant_user@localhost +select count(a) from test where a+b > 0 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +update test set b=5 where a=2 +grant select on grant_test.test to grant_user@localhost +Connecting grant_user +select count(a) from test where a+b > 0 +4 + +revoke select(b) on grant_test.test from grant_user@localhost +select count(a) from test where a+b > 0 +4 + +revoke select on grant_test.test from grant_user@localhost +Connecting grant_user +select count(a) from test where a+b > 0 +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +grant select(a) on grant_test.test to grant_user@localhost +select count(a) from test where a+b > 0 +Error in execute: select command denied to user: 'grant_user@localhost' for column 'b' in table 'test' +grant select on *.* to grant_user@localhost +Connecting grant_user +select count(a) from test where a+b > 0 +4 + +revoke select on *.* from grant_user@localhost +grant select(b) on grant_test.test to grant_user@localhost +Connecting grant_user +select count(a) from test where a+b > 0 +4 + +select * from mysql.db where user = 'grant_user' +localhost grant_test grant_user N Y N N N N N N N N + +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv where user = 'grant_user' +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter +localhost grant_test grant_user test root@localhost Select,Insert,Update + +select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv where user = 'grant_user' +localhost grant_test grant_user test b Select,Insert,Update +localhost grant_test grant_user test a Select,Update + +revoke ALL PRIVILEGES on grant_test.test from grant_user@localhost +select count(a) from test +Error in execute: select command denied to user: 'grant_user@localhost' for table 'test' +select * from mysql.user +Error in execute: select command denied to user: 'grant_user@localhost' for table 'user' +select * from mysql.db where user = 'grant_user' +localhost grant_test grant_user N Y N N N N N N N N + +select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv where user = 'grant_user' +localhost grant_test grant_user test2 root@localhost Grant,Index,Alter + +select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv where user = 'grant_user' +delete from user where user='grant_user' +flush privileges +grant ALL PRIVILEGES on grant_test.test to grant_user@localhost identified by 'dummy', grant_user@127.0.0.1 identified by 'dummy2' +Connecting grant_user +grant SELECT on grant_test.* to grant_user@localhost identified by '' +Connecting grant_user +drop database grant_test +delete from user where user='grant_user' +delete from db where user='grant_user' +delete from tables_priv +delete from columns_priv +flush privileges +end of test diff --git a/tests/insert_and_repair.pl b/tests/insert_and_repair.pl new file mode 100755 index 00000000000..4d68c2ab9a0 --- /dev/null +++ b/tests/insert_and_repair.pl @@ -0,0 +1,180 @@ +#!/usr/bin/perl -w +# +# This is a test of insert and repair/check. +# + +$opt_loop_count=100000; # Change this to make test harder/easier + +##################### Standard benchmark inits ############################## + +use DBI; +use Getopt::Long; +use Benchmark; + +package main; + +$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= + $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; +$opt_host=$opt_user=$opt_password=""; $opt_db="test"; + +GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in", + "skip-delete","verbose","fast-insert","lock-tables","debug","fast", + "force","user=s","password=s") || die "Aborted"; +$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these + +$firsttable = "bench_f1"; +$secondtable = "bench_f2"; + +#### +#### Start timeing and start test +#### + +$start_time=new Benchmark; +if (!$opt_skip_create) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $dbh->do("drop table if exists $firsttable, $secondtable"); + + print "Creating tables $firsttable and $secondtable in database $opt_db\n"; + $dbh->do("create table $firsttable (id int(7) not null, thread tinyint not null, info varchar(32), marker char(1), primary key(id,thread))") or die $DBI::errstr; + $dbh->do("create table $secondtable (id int(7) not null, thread tinyint not null, row int(3) not null,value double, primary key(id,thread,row)) delay_key_write=1") or die $DBI::errstr; + $dbh->disconnect; $dbh=0; # Close handler +} +$|= 1; # Autoflush + +#### +#### Start the tests +#### + +insert_in_bench1() if (($pid=fork()) == 0); $work{$pid}="insert in bench1"; +insert_in_bench2() if (($pid=fork()) == 0); $work{$pid}="insert in bench2"; +repair_and_check() if (($pid=fork()) == 0); $work{$pid}="repair/check"; + +$errors=0; +while (($pid=wait()) != -1) +{ + $ret=$?/256; + print "thread '" . $work{$pid} . "' finnished with exit code $ret\n"; + $errors++ if ($ret != 0); +} + +if (!$opt_skip_delete && !$errors) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $dbh->do("drop table $firsttable,$secondtable"); +} +print ($errors ? "Test failed\n" :"Test ok\n"); + +$end_time=new Benchmark; +print "Total time: " . + timestr(timediff($end_time, $start_time),"noc") . "\n"; + +exit(0); + +# +# Insert records in the two tables +# + +sub insert_in_bench1 +{ + my ($dbh,$rows,$found,$i); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $sth=$dbh->do("insert into $firsttable values ($i,0,'This is entry $i','')") || die "Got error on insert: $DBI::errstr\n"; + $row_count=($i % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $sth=$dbh->do("insert into $secondtable values ($i,0,$j,0)") || die "Got error on insert: $DBI::errstr\n"; + } + } + $dbh->disconnect; $dbh=0; + print "insert_in_bench1: Inserted $rows rows\n"; + exit(0); +} + +sub insert_in_bench2 +{ + my ($dbh,$rows,$found,$i); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $sth=$dbh->do("insert into $firsttable values ($i,1,'This is entry $i','')") || die "Got error on insert: $DBI::errstr\n"; + $row_count=((7-$i) % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $sth=$dbh->do("insert into $secondtable values ($i,1,$j,0)") || die "Got error on insert: $DBI::errstr\n"; + } + } + $dbh->disconnect; $dbh=0; + print "insert_in_bench2: Inserted $rows rows\n"; + exit(0); +} + + +sub repair_and_check +{ + my ($dbh,$row,@row,$found1,$found2,$last_found1,$last_found2,$i,$type, + $table); + $found1=$found2=0; $last_found1=$last_found2= -1; + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + + for ($i=0; $found1 != $last_found1 && $found2 != $last_found1 ; $i++) + { + $type=($i & 2) ? "repair" : "check"; + if ($i & 1) + { + $table=$firsttable; + $last_found1=$found1; + } + else + { + $table=$secondtable; + $last_found2=$found2; + } + $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $dbh->errstr\n"; + $sth->execute || die $dbh->errstr; + + while (($row=$sth->fetchrow_arrayref)) + { + if ($row->[3] ne "OK") + { + print "Got error " . $row->[3] . " when doing $type on $table\n"; + exit(1); + } + } + $sth=$dbh->prepare("select count(*) from $table") || die "Got error on prepare: $dbh->errstr\n"; + $sth->execute || die $dbh->errstr; + @row = $sth->fetchrow_array(); + if ($i & 1) + { + $found1= $row[0]; + } + else + { + $found2= $row[0]; + } + $sth->finish; + sleep(2); + } + $dbh->disconnect; $dbh=0; + print "check/repair: Did $i repair/checks\n"; + exit(0); +} diff --git a/tests/lock_test.pl b/tests/lock_test.pl new file mode 100755 index 00000000000..5daeeddad8e --- /dev/null +++ b/tests/lock_test.pl @@ -0,0 +1,94 @@ +#!/usr/bin/perl + +# This is a test with uses two processes to a database. +# The other inserts records in two tables, the other does a lot of joins +# on these. +# Every time the read thread outputs info, it does a ALTER TABLE command +# which should stop the insert thread until the ALTER TABLE command is ready. +# +# Warning, the output from this test will differ in 'found' from time to time, +# but there should never be any errors +# + +$host = shift || ""; +$test_db="test"; +$test_count=10000; +srand 0; # Repeatable test + +use Mysql; +$|= 1; # Autoflush + +$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; +$dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; + +$firsttable = "test_lock_1"; +$secondtable = "test_lock_2"; +$dbh->Query("drop table $firsttable"); +$dbh->Query("drop table $secondtable"); + +print "Creating tables $firsttable and $secondtable in database $test_db\n"; +$dbh->Query("create table $firsttable (id int(6) not null, info char(32), auto int(11) not null auto_increment, primary key(id),key(auto))") or die $Mysql::db_errstr; + +$dbh->Query("create table $secondtable (id int(6) not null, info varchar(32), key(id))") or die $Mysql::db_errstr; + +$dbh=0; # Close handler + +if (fork() == 0) +{ # Insert process + $dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; + $dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; + $first_id=1; $second_id=1; + $first_count=$second_count=0; + print "Writing started\n"; + for ($i=1 ; $i <= $test_count ; $i++) + { + if (rand(3) <= 1) + { + $sth=$dbh->Query("insert into $firsttable values ($first_id,'This is entry $i',NULL)") || die "Got error on insert: $Mysql::db_errstr\n"; + die "Row not inserted, aborting\n" if ($sth->affected_rows != 1); + $first_id++; + $first_count++; + } + else + { + $sth=$dbh->Query("insert into $secondtable values ($second_id,'This is entry $i')") || die "Got error on insert: $Mysql::db_errstr\n"; + die "Row not inserted, aborting\n" if ($sth->affected_rows != 1); + $second_id++ if (rand(10) <= 1); # Don't always count it up + $second_count++; + } + print "Write: $i\n" if ($i % 1000 == 0); + } + print "Writing done ($first_count $second_count)\n"; +} +else +{ + $dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; + $dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; + $locked=$found=0; + print "Reading started\n"; + for ($i=1 ; $i <= $test_count ; $i++) + { + $id=int(rand($test_count)/3)+1; + $sth=$dbh->Query("select count(*) from $firsttable,$secondtable where $firsttable.id = $secondtable.id and $firsttable.id=$id") || die "Got error on select: $Mysql::db_errstr\n"; + $found++ if ($sth->numrows); + if ($i % 1000 == 0) + { + print "Read: $i Found: $found\n"; + if ($found) + { + $locked=1-$locked; + if ($locked) + { + $sth=$dbh->Query("lock tables $firsttable write,$secondtable write"); + } + $sth=$dbh->Query("alter table $firsttable CHANGE id id int(6) not null") || die "Got error on ALTER TABLE: $Mysql::db_errstr\n"; + $sth=$dbh->Query("alter table $secondtable CHANGE info info char(32) not null") || die "Got error on ALTER TABLE: $Mysql::db_errstr\n"; + if ($locked) + { + $sth=$dbh->Query("unlock tables"); + } + } + } + } + print "Reading done Found: $found\n"; +} diff --git a/tests/lock_test.res b/tests/lock_test.res new file mode 100644 index 00000000000..5a9464d59ec --- /dev/null +++ b/tests/lock_test.res @@ -0,0 +1,25 @@ +Creating tables test_lock_1 and test_lock_2 in database test +Reading started +Writing started +Write: 1000 +Read: 1000 Found: 28 +Write: 2000 +Write: 3000 +Read: 2000 Found: 79 +Write: 4000 +Write: 5000 +Read: 3000 Found: 165 +Write: 6000 +Write: 7000 +Read: 4000 Found: 291 +Write: 8000 +Write: 9000 +Write: 10000 +Writing done +Read: 5000 Found: 482 +Read: 6000 Found: 680 +Read: 7000 Found: 862 +Read: 8000 Found: 1076 +Read: 9000 Found: 1275 +Read: 10000 Found: 1507 +Reading done Found: 1507 diff --git a/tests/mail_to_db.pl b/tests/mail_to_db.pl new file mode 100755 index 00000000000..7886ffb7e0f --- /dev/null +++ b/tests/mail_to_db.pl @@ -0,0 +1,310 @@ +#!/usr/bin/perl +# Copyright Abandoned 1998 TCX DataKonsult AB & Monty Program KB & Detron HB +# This file is public domain and comes with NO WARRANTY of any kind +# +# This program is brought to you by Janne-Petteri Koilo with the +# administration of Michael Widenius. + +# This program takes your mails and puts them into your database. It ignores +# messages with the same from, date and message text. +# You can use mail-files that are compressed or gzipped and ends with +# -.gz or -.Z. + +use DBI; +use Getopt::Long; + +$VER = "1.6"; + +$opt_db = "mail"; +$opt_table = "mails"; +$opt_max_mail_size = 65536; +$opt_db_engine = "mysql"; +$opt_host = "localhost"; +$opt_user = $opt_password = ""; +$opt_help = $opt_version = $opt_test=0; + +GetOptions("help","version","user=s","password=s", + "db_engine=s","db=s","host=s","max_mail_size=s","test") || usage(); + +usage($VER) if ($opt_help || $opt_version || !$ARGV[0]); + +%months= ('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5, + 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, + 'Nov' => 11, 'Des' => 12); + +$count_no_from = $count_no_txt = $count_too_big = 0; +$count_forwarded_msgs = $count_duplicates = $no_subject = 0; +$inserted_mails = 0; +$dbh=0; + +$dbh = DBI->connect("DBI:$opt_db_engine:$opt_db:$opt_host",$opt_user, + $opt_password,{ PrintError => 0}) || die $DBI::errstr; +if (!$opt_test) +{ + create_table_if_needed($dbh); +} + +foreach (@ARGV) +{ + if (/^(.*)\.(gz|Z)$/) #checks if the file is compressed or gzipped + { + open(FILE, "zcat $_ |"); + process_mail_file($dbh,$1); + } + else + { + open(FILE,$_); + process_mail_file($dbh,$_); + } +} +$dbh->disconnect if (!$opt_test); + +$ignored = $count_no_from + $count_no_txt + $count_too_big + $count_duplicates + $no_subject; +print "Mails inserted:\t\t\t$inserted_mails\n"; +print "Mails ignored:\t\t\t$ignored\n"; +print "Mails without \"From:\" -field:\t$count_no_from\n"; +print "Mails without message:\t\t$count_no_txt\n"; +print "Too big mails (> $opt_max_mail_size):\t$count_too_big\n"; +print "Duplicate mails:\t\t$count_duplicates\n"; +print "Forwarded mails:\t\t$count_forwarded_msgs\n"; +print "No subject:\t\t\t$no_subject\n"; +print "Mails altogether:\t\t"; +print $inserted_mails+$ignored; +print "\n"; +exit(0); + +sub usage +{ + my($VER)=@_; + + $0 =~ s/.\/(.+)/$1/; + if ($opt_version) + { + print "$0 version $VER\n"; + } + else + { + print <<EOF; +$0 version $VER + +Usage: $0 [options] file1 [file2 file3 ...] + +Description: Inserts mails from file(s) into a database + +Options: +--help show this help and exit +--version shows the version of the program +--db_engine=... database server (default: $opt_db_engine) +--db=... database to be used (default: $opt_db) +--host=... hostname to be used (default: $opt_host) +--password=... user password for the db server +--user=... username for the db server +--max_mail_size=# max size of a mail to be inserted into the db. + mail will be ignored if it exceeds this size + (default $opt_max_mail_size) +--test Don\'t connect to the database, just write the + queries to stdout +EOF + } + exit(0); +} + +sub create_table_if_needed +{ + my ($dbh)=@_; + my ($sth,$create); + + $sth = $dbh->prepare("select count(*) from $opt_table") or die $dbh->errstr; + if (!$sth->execute) + { + $create = "CREATE TABLE $opt_table (msg_nro mediumint unsigned not null "; + $create .= "auto_increment, date DATETIME NOT NULL, time_zone CHAR(6) "; + $create .= "NOT NULL, mail_from char(120) not null, reply char(120), "; + $create .= "mail_to TEXT, cc TEXT, sbj char(200), txt MEDIUMTEXT NOT "; + $create .= "NULL, file char(32) noT NULL, hash INT NOT NULL, key "; + $create .= "(msg_nro), primary key (mail_from, date, time_zone, hash))"; + $sth = $dbh->prepare($create) or die $dbh->errstr; + $sth->execute() or die $dbh->errstr; + } +} + +sub process_mail_file +{ + my ($dbh,$file_name)= @_; + my (%values,$type,$check); + + %values=(); $type=""; + $check=0; + + while (<FILE>) + { + chop; + if ($type ne "message") + { + if (/^Reply-To: (.*)/i) # finding different fields from file + { + $type="reply"; + $values{$type}= $1; + } + elsif (/^From: (.*)/i) + { + $type="from"; + $values{$type}= $1; + } + elsif (/^To: (.*)/i) + { + $type="to"; + $values{$type}= $1; + } + elsif (/^Cc: (.*)/i) + { + $type="cc"; + $values{$type}= $1; + } + elsif (/^Subject: (.*)/i) + { + $type="subject"; + $values{$type}= $1; + } + elsif (/^Date: (.*)/i) + { + date_parser($1,\%values); + $type="rubbish"; + } + elsif (/^[\w\W-]+:\s/) + { + $type="rubbish"; + } + elsif ($_ eq "") + { + $type="message"; + $values{$type}=""; + } + else + { + s/^\s*/ /; + $values{$type}.= $_; + } + } + elsif ($check!=0 && $_ ne "") # in case of forwarded messages + { + $values{$type}.= "\n" . $_; + $check--; + } + elsif (/^From .* \d\d:\d\d:\d\d\s\d\d\d\d$/) + { + $values{'hash'}= checksum("$values{'message'}"); + update_table($dbh,$file_name,\%values); + %values=(); $type=""; + $check=0; + } + elsif (/-* forwarded message .*-*/i) # in case of forwarded messages + { + $values{$type}.= "\n" . $_; + $check++; + $count_forwarded_msgs++; + } + else + { + $values{$type}.= "\n" . $_; + } + } + $values{'hash'}= checksum("$values{'message'}"); + update_table($dbh,$file_name,\%values); +} + +######## + +# converts date to the right form + +sub date_parser +{ + my ($date_raw,$values)=@_; + + $date_raw =~ /\s*(\d{1,2}) (\w+) (\d{2,4}) (\d+:\d+:\d+)\s*([\w-+]{3-5})?/; + + $values->{'date'}=$3 . "-" . $months{$2} . "-" . "$1 $4"; + $values->{'time_zone'}=$5; +} + +######### + +# this is runned when the whole mail is gathered. +# this actually puts the mail to the database. + +sub update_table +{ + my($dbh,$file_name,$values)=@_; + my($query); + + if (! defined($values->{'subject'}) || !defined($values->{'to'})) + { + $no_subject++; + return; # Ignore these + } + $values->{'message'} =~ s/^\s*//; #removes whitespaces from the beginning + $values->{'message'} =~ s/\s*$//; #removes whitespaces from the end + $query = "insert into $opt_table values (NULL,'" . $values->{'date'}; + $query .= "','" . $values->{'time_zone'} . "',"; + $query .= (defined($values->{'from'}) ? $dbh->quote($values->{'from'}) : "NULL") . ","; + $query .= (defined($values->{'reply'}) ? $dbh->quote($values->{'reply'}) : "NULL") . ","; + + $query .= (defined($values->{'to'}) ? $dbh->quote($values->{'to'}) : "NULL") . ","; + $query .= (defined($values->{'cc'}) ? $dbh->quote($values->{'cc'}) : "NULL") . ","; + $query .= $dbh->quote($values->{'subject'}) . ","; + $query .= $dbh->quote($values->{'message'}) . "," . $dbh->quote($file_name); + $query .= ",'" . $values->{'hash'} . "')"; + + if (length($values->{'message'}) > $opt_max_mail_size) #disables big message + { + $count_too_big++; + } + elsif ($values->{'from'} eq "") #disables mails with no from field + { + $count_no_from++; + } + elsif ($opt_test) + { + print "$query\n"; + $inserted_mails++; + } + elsif ($values->{'message'} eq "") #disables mails with no message text + { + $count_no_msg_text++; + } + elsif ($dbh->do($query)) + { + $inserted_mails++; + } + elsif (!($dbh->errstr =~ /Duplicate entry /)) #disables duplicates + { + die "Aborting: Got error '" . $dbh->errstr ."' for query: '$query'\n"; + } + else + { + $count_duplicates++; + } + $query=""; +} + + +########## + +# In case you have two identical messages we wanted to identify them +# and remove additionals; We do this by calculating a hash number of the +# message and ignoring messages with the same from, date and hash. +# This function calculates a simple 32 bit hash value for the message. + +sub checksum +{ + my ($txt)= @_; + my ($crc,$i,$count); + $count = length($txt); + for ($crc = $i = 0; $i < $count ; $i++) + { + $crc = (($crc << 1) + (ord (substr ($txt, $i, 1)))) + + (($crc & (1 << 30)) ? 1 : 0); + $crc &= ((1 << 31) -1); + } + return $crc; +} diff --git a/tests/pmail.pl b/tests/pmail.pl new file mode 100755 index 00000000000..4ca2033b4d1 --- /dev/null +++ b/tests/pmail.pl @@ -0,0 +1,195 @@ +#!/usr/bin/perl +# +# Prints mails to standard output +# +#### +#### Standard inits and get options +#### + +use DBI; +use Getopt::Long; + +$VER="1.4a"; + +@fldnms= ("mail_from","mail_to","cc","date","time_zone","file","sbj","txt"); +$fields=8; +@mail= (@from,@to,@cc,@date,@time_zone,@file,@sbj,@txt); + +$opt_user= $opt_password= ""; +$opt_socket= "/tmp/mysql.sock"; +$opt_port= 3306; +$opt_db="test"; +$opt_table="mails"; +$opt_help=$opt_count=0; + +GetOptions("help","count","port=i","db=s","table=s","host=s","password=s", + "user=s","socket=s") || usage(); + +if ($opt_host eq '') +{ + $opt_host = "localhost"; +} + +if ($opt_help || !$ARGV[0]) +{ + usage(); +} + +#### +#### Connect and parsing the query to MySQL +#### + +$dbh= DBI->connect("DBI:mysql:$opt_db:$opt_host:port=$opt_port:mysql_socket=$opt_mysql_socket", $opt_user,$opt_password, { PrintError => 0}) +|| die $DBI::errstr; + +if ($opt_count) +{ + count_mails(); +} + +$fields=0; +$query = "select "; +foreach $val (@fldnms) +{ + if (!$fields) + { + $query.= "$val"; + } + else + { + $query.= ",$val"; + } + $fields++; +} +$query.= " from $opt_table where $ARGV[0]"; + +#### +#### Send query and save result +#### + +$sth= $dbh->prepare($query); +if (!$sth->execute) +{ + print "$DBI::errstr\n"; + $sth->finish; + die; +} +for ($i=0; ($row= $sth->fetchrow_arrayref); $i++) +{ + for ($j=0; $j < $fields; $j++) + { + $mail[$j][$i]= $row->[$j]; + } +} + +#### +#### Print to stderr +#### + +for ($i=0; $mail[0][$i]; $i++) +{ + print "#" x 33; + print " " . ($i+1) . ". Mail "; + print "#" x 33; + print "\nFrom: $mail[0][$i]\n"; + print "To: $mail[1][$i]\n"; + print "Cc: $mail[2][$i]\n"; + print "Date: $mail[3][$i]\n"; + print "Timezone: $mail[4][$i]\n"; + print "File: $mail[5][$i]\n"; + print "Subject: $mail[6][$i]\n"; + print "Message:\n$mail[7][$i]\n"; +} +print "#" x 20; +print " Summary: "; +if ($i == 1) +{ + print "$i Mail "; + print "matches the query "; +} +else +{ + print "$i Mails "; + print "match the query "; +} +print "#" x 20; +print "\n"; + +#### +#### Count mails that matches the query, but don't show them +#### + +sub count_mails +{ + $sth= $dbh->prepare("select count(*) from $opt_table where $ARGV[0]"); + if (!$sth->execute) + { + print "$DBI::errstr\n"; + $sth->finish; + die; + } + while (($row= $sth->fetchrow_arrayref)) + { + $mail_count= $row->[0]; + } + if ($mail_count == 1) + { + print "$mail_count Mail matches the query.\n"; + } + else + { + print "$mail_count Mails match the query.\n"; + } + exit; +} + +#### +#### Usage +#### + +sub usage +{ + print <<EOF; + pmail version $VER by Jani Tolonen + + Usage: pmail [options] "SQL where clause" + Options: + --help show this help + --count Shows how many mails matches the query, but not the mails. + --db= database to use (Default: $opt_db) + --table= table to use (Default: $opt_table) + --host= Hostname which to connect (Default: $opt_host) + --socket= Unix socket to be used for connection (Default: $opt_socket) + --password= Password to use for mysql + --user= User to be used for mysql connection, if not current user + --port= mysql port to be used (Default: $opt_port) + "SQL where clause" is the end of the select clause, + where the condition is expressed. The result will + be the mail(s) that matches the condition and + will be displayed with the fields: + - From + - To + - Cc + - Date + - Timezone + - File (Where from the current mail was loaded into the database) + - Subject + - Message text + The field names that can be used in the where clause are: + Field Type + - mail_from varchar(120) + - date datetime + - sbj varchar(200) + - txt mediumtext + - cc text + - mail_to text + - time_zone varchar(6) + - reply varchar(120) + - file varchar(32) + - hash int(11) + An example of the pmail: + pmail "txt like '%libmysql.dll%' and sbj like '%delphi%'" + NOTE: the txt field is NOT case sensitive! +EOF + exit(0); +} diff --git a/tests/table_types.pl b/tests/table_types.pl new file mode 100755 index 00000000000..8198cd9ba86 --- /dev/null +++ b/tests/table_types.pl @@ -0,0 +1,224 @@ +#!/usr/bin/perl +# + +use DBI; +use Benchmark; + +$opt_loop_count=100000; # number of rows/3 +$small_loop_count=10; # Loop for full table retrieval +$range_loop_count=$small_loop_count*50; +$many_keys_loop_count=$opt_loop_count; + +chomp($pwd = `pwd`); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +if ($opt_loop_count < 256) +{ + $opt_loop_count=256; # Some tests must have some data to work! +} + +if ($opt_small_test) +{ + $opt_loop_count/=100; + $range_loop_count/=10; + $many_keys_loop_count=$opt_loop_count/10; +} +elsif ($opt_small_tables) +{ + $opt_loop_count=10000; # number of rows/3 + $many_keys_loop_count=$opt_loop_count; +} +elsif ($opt_small_key_tables) +{ + $many_keys_loop_count/=10; +} + +print "Testing the speed difference between some table types\n"; + +#### +#### Generating random keys +#### + +print "Generating random keys\n"; +$random[$opt_loop_count]=0; +for ($i=0 ; $i < $opt_loop_count ; $i++) +{ + $random[$i]=$i+$opt_loop_count; +} + +my $tmpvar=1; +for ($i=0 ; $i < $opt_loop_count ; $i++) +{ + $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count); + $swap=$tmpvar % $opt_loop_count; + $tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp; +} + +$total_rows=$opt_loop_count*3; + +#### +#### Connect and start timeing +#### +$start_time=new Benchmark; +$dbh = $server->connect(); +#### +#### Create needed tables +#### + +$table_name="bench1"; +<<<<<<< table_types.pl +||||||| 1.2 +test("n","type=isam","char"); test("m","type=myisam pack_keys=1","char"); exit(1); + +======= + +>>>>>>> /tmp/T4a17019 +test($table_name,"type=isam","char"); +test($table_name,"type=myisam pack_keys=0","char"); +test($table_name,"type=myisam pack_keys=0","char"); +test($table_name,"type=myisam pack_keys=0 checksum=1","char"); +test($table_name,"type=myisam pack_keys=1","char"); + +test($table_name,"type=isam","varchar"); +test($table_name,"type=myisam pack_keys=1","varchar"); +test($table_name,"type=myisam pack_keys=0","varchar"); +test($table_name,"type=myisam pack_keys=0 checksum=1","varchar"); + +#test("type=heap","char"); # The default table sizes is a bit big for this one + +$dbh->disconnect; +exit (0); + +sub test { + my ($name,$options,$chartype)=@_; + + print "\nTesting with options: '$options'\n"; + $dbh->do("drop table $name"); + do_many($dbh,$server->create("$name", + ["id int NOT NULL", + "id2 int NOT NULL", + "id3 int NOT NULL", + "dummy1 $chartype(30)"], + ["primary key (id,id2)", + "index index_id3 (id3)"], + $options)); + + if ($opt_lock_tables) + { + $sth = $dbh->do("LOCK TABLES $name WRITE") || die $DBI::errstr; + } + + if ($opt_fast && defined($server->{vacuum})) + { + $server->vacuum(\$dbh,1); + } + + #### + #### Insert $total_rows records in order, in reverse order and random. + #### + + $loop_time=new Benchmark; + + if ($opt_fast_insert) + { + $query="insert into $name values "; + } + else + { + $query="insert into $name (id,id2,id3,dummy1) values "; + } + + if (($opt_fast || $opt_fast_insert) && $limits->{'multi_value_insert'}) + { + $query_size=$server->{'limits'}->{'query_size'}; + + print "Inserting $opt_loop_count multiple-value rows in order\n"; + $res=$query; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmp= "($i,$i,$i,'ABCDEFGHIJ'),"; + if (length($tmp)+length($res) < $query_size) + { + $res.= $tmp; + } + else + { + $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; + $res=$query . $tmp; + } + } + print "Inserting $opt_loop_count multiple-value rows in reverse order\n"; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) . + "," .($total_rows-1-$i) . ",'BCDEFGHIJK'),"; + if (length($tmp)+length($res) < $query_size) + { + $res.= $tmp; + } + else + { + $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; + $res=$query . $tmp; + } + } + print "Inserting $opt_loop_count multiple-value rows in random order\n"; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] . + ",'CDEFGHIJKL')," or die $DBI::errstr; + if (length($tmp)+length($res) < $query_size) + { + $res.= $tmp; + } + else + { + $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; + $res=$query . $tmp; + } + } + $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; + } + else + { + print "Inserting $opt_loop_count rows in order\n"; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr; + } + + print "Inserting $opt_loop_count rows in reverse order\n"; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," . + ($total_rows-1-$i) . "," . + ($total_rows-1-$i) . ",'BCDEFGHIJK')") + or die $DBI::errstr; + } + + print "Inserting $opt_loop_count rows in random order\n"; + + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] . + "," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr; + } + } + + $end_time=new Benchmark; + print "Time for insert (" . ($total_rows) . "): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + + if ($opt_fast && defined($server->{vacuum})) + { + $server->vacuum(\$dbh,1); + } + + $sth=$dbh->prepare("show table status like '$name'"); + $sth->execute || die "Show table status returned error: $DBI::errstr\n"; + while (@row = $sth->fetchrow_array) + { + print join("| ",@row) . " |\n"; + } + $dbh->do("drop table $name") if (!$opt_skip_delete); +} diff --git a/tests/test_delayed_insert.pl b/tests/test_delayed_insert.pl new file mode 100755 index 00000000000..e49d73a19bd --- /dev/null +++ b/tests/test_delayed_insert.pl @@ -0,0 +1,365 @@ +#!/usr/bin/perl -w + +# This is a test for INSERT DELAYED +# + +$opt_loop_count=10000; # Change this to make test harder/easier + +##################### Standard benchmark inits ############################## + +use DBI; +use Getopt::Long; +use Benchmark; + +package main; + +$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= + $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; +$opt_host=$opt_user=$opt_password=""; $opt_db="test"; + +GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in","skip-delete", +"verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted"; +$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these + +print "Testing 8 multiple connections to a server with 1 insert, 2 delayed\n"; +print "insert, 1 update, 1 delete, 1 flush tables and 3 select connections.\n"; + +$firsttable = "bench_f1"; +$secondtable = "bench_f2"; + +#### +#### Start timeing and start test +#### + +$start_time=new Benchmark; +if (!$opt_skip_create) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $Mysql::QUIET = 1; + $dbh->do("drop table if exists $firsttable,$secondtable"); + $Mysql::QUIET = 0; + + print "Creating tables $firsttable and $secondtable in database $opt_db\n"; + $dbh->do("create table $firsttable (id int(6) not null, info varchar(32), marker char(1), primary key(id))") or die $DBI::errstr; + $dbh->do("create table $secondtable (id int(6) not null, row int(3) not null,value double, primary key(id,row))") or die $DBI::errstr; + + $dbh->disconnect; +} +$|= 1; # Autoflush + +#### +#### Start the tests +#### + +test_1() if (($pid=fork()) == 0); $work{$pid}="insert"; +test_delayed_1() if (($pid=fork()) == 0); $work{$pid}="delayed_insert1"; +test_delayed_2() if (($pid=fork()) == 0); $work{$pid}="delayed_insert2"; +test_2() if (($pid=fork()) == 0); $work{$pid}="update"; +test_3() if (($pid=fork()) == 0); $work{$pid}="select1"; +test_4() if (($pid=fork()) == 0); $work{$pid}="select2"; +test_5() if (($pid=fork()) == 0); $work{$pid}="select3"; +test_del() if (($pid=fork()) == 0); $work{$pid}="delete"; +test_flush() if (($pid=fork()) == 0); $work{$pid}="flush"; + +$errors=0; +while (($pid=wait()) != -1) +{ + $ret=$?/256; + print "thread '" . $work{$pid} . "' finnished with exit code $ret\n"; + $errors++ if ($ret != 0); +} + +if (!$opt_skip_delete && !$errors) +{ + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $dbh->do("drop table $firsttable"); + $dbh->do("drop table $secondtable"); +} +print ($errors ? "Test failed\n" :"Test ok\n"); + +$end_time=new Benchmark; +print "Total time: " . + timestr(timediff($end_time, $start_time),"noc") . "\n"; + +exit(0); + +# +# Insert records in the two tables +# + +sub test_1 +{ + my ($dbh,$tmpvar,$rows,$found,$i); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=1; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $dbh->do("insert into $firsttable values ($i,'This is entry $i','')") || die "Got error on insert: $DBI::errstr\n"; + $row_count=($i % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $dbh->do("insert into $secondtable values ($i,$j,0)") || die "Got error on insert: $DBI::errstr\n"; + } + } + $dbh->disconnect; + print "Test_1: Inserted $rows rows\n"; + exit(0); +} + + +sub test_delayed_1 +{ + my ($dbh,$tmpvar,$rows,$found,$i,$id); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=1; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$i+$opt_loop_count; + $dbh->do("insert delayed into $firsttable values ($id,'This is entry $id','')") || die "Got error on insert: $DBI::errstr\n"; + $row_count=($i % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $dbh->do("insert into $secondtable values ($id,$j,0)") || die "Got error on insert: $DBI::errstr\n"; + } + if (($tmpvar % 100) == 0) + { + $dbh->do("select max(info) from $firsttable") || die "Got error on select max(info): $DBI::errstr\n"; + $dbh->do("select max(value) from $secondtable") || die "Got error on select max(info): $DBI::errstr\n"; + $found+=2; + } + } + $dbh->disconnect; + print "Test_1: Inserted delayed $rows rows, found $found rows\n"; + exit(0); +} + + +sub test_delayed_2 +{ + my ($dbh,$tmpvar,$rows,$found,$i,$id); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=1; + $rows=$found=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$i+$opt_loop_count*2; + $dbh->do("insert delayed into $firsttable values ($id,'This is entry $id','')") || die "Got error on insert: $DBI::errstr\n"; + $row_count=($i % 7)+1; + $rows+=1+$row_count; + for ($j=0 ; $j < $row_count; $j++) + { + $dbh->do("insert delayed into $secondtable values ($id,$j,0)") || die "Got error on insert: $DBI::errstr\n"; + } + if (($tmpvar % 100) == 0) + { + $dbh->do("select max(info) from $firsttable") || die "Got error on select max(info): $DBI::errstr\n"; + $dbh->do("select max(value) from $secondtable") || die "Got error on select max(info): $DBI::errstr\n"; + $found+=2; + } + } + $dbh->disconnect; + print "Test_1: Inserted delayed $rows rows, found $found rows\n"; + exit(0); +} + +# +# Update records in both tables +# + +sub test_2 +{ + my ($dbh,$id,$tmpvar,$rows,$found,$i,$max_id,$tmp,$sth,$count); + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=111111; + $rows=$found=$max_id=$id=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmp=(($tmpvar + 63) + $i)*3; + $tmp=$tmp-int($tmp/100000)*100000; + $tmpvar^= $tmp; + $tmp=$tmpvar - int($tmpvar/10)*10; + if ($max_id*$tmp == 0) + { + $max_id=0; + $sth=$dbh->prepare("select max(id) from $firsttable where marker=''"); + $sth->execute() || die "Got error select max: $DBI::errstr\n"; + if ((@row = $sth->fetchrow_array()) && defined($row[0])) + { + $found++; + $max_id=$id=$row[0]; + } + $sth->finish; + } + else + { + $id= $tmpvar % ($max_id-1)+1; + } + if ($id) + { + ($count=$dbh->do("update $firsttable set marker='x' where id=$id")) || die "Got error update $firsttable: $DBI::errstr\n"; + $rows+=$count; + if ($count > 0) + { + $count=$dbh->do("update $secondtable set value=$i where id=$id") || die "Got error update $firsttable: $DBI::errstr\n"; + $rows+=$count; + } + } + } + $dbh->disconnect; + print "Test_2: Found $found rows, Updated $rows rows\n"; + exit(0); +} + + +# +# select records +# + +sub test_3 +{ + my ($dbh,$id,$tmpvar,$rows,$i,$count); + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=222222; + $rows=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$tmpvar % $opt_loop_count; + $count=$dbh->do("select id from $firsttable where id=$id") || die "Got error on select from $firsttable: $DBI::errstr\n"; + $rows+=$count; + } + $dbh->disconnect; + print "Test_3: Found $rows rows\n"; + exit(0); +} + + +# +# Note that this uses row=1 and in some cases won't find any matching +# records +# + +sub test_4 +{ + my ($dbh,$id,$tmpvar,$rows,$i,$count); + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=333333; + $rows=0; + for ($i=0 ; $i < $opt_loop_count; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + $id=$tmpvar % $opt_loop_count; + $count=$dbh->do("select id from $secondtable where id=$id") || die "Got error on select from $secondtable: $DBI::errstr\n"; + $rows+=$count; + } + $dbh->disconnect; + print "Test_4: Found $rows rows\n"; + exit(0); +} + + +sub test_5 +{ + my ($dbh,$id,$tmpvar,$rows,$i,$max_id,$count,$sth); + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $tmpvar=444444; + $rows=$max_id=0; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000); + if ($max_id == 0 || ($tmpvar % 10 == 0)) + { + $sth=$dbh->prepare("select max(id) from $firsttable"); + $sth->execute() || die "Got error select max: $DBI::errstr\n"; + if ((@row = $sth->fetchrow_array()) && defined($row[0])) + { + $max_id=$id=$row[0]; + } + else + { + $id=0; + } + $sth->finish; + } + else + { + $id= $tmpvar % $max_id; + } + $count=$dbh->do("select value from $firsttable,$secondtable where $firsttable.id=$id and $secondtable.id=$firsttable.id") || die "Got error on select from $secondtable: $DBI::errstr\n"; + $rows+=$count; + } + $dbh->disconnect; + print "Test_5: Found $rows rows\n"; + exit(0); +} + + +# +# Delete the smallest row +# + +sub test_del +{ + my ($dbh,$min_id,$i,$sth,$rows); + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr; + $rows=0; + for ($i=0 ; $i < $opt_loop_count/3; $i++) + { + $sth=$dbh->prepare("select min(id) from $firsttable"); + $sth->execute() || die "Got error on select from $firsttable: $DBI::errstr\n"; + if ((@row = $sth->fetchrow_array()) && defined($row[0])) + { + $min_id=$row[0]; + } + $sth->finish; + $dbh->do("delete from $firsttable where id = $min_id") || die "Got error on DELETE from $firsttable: $DBI::errstr\n"; + $rows++; + } + $dbh->disconnect; + print "Test_del: Deleted $rows rows\n"; + exit(0); +} + + +# +# Do a flush tables once in a while +# + +sub test_flush +{ + my ($dbh,$sth,$found1,$last_found1,$i,@row); + $found1=0; $last_found1=-1; + + $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", + $opt_user, $opt_password, + { PrintError => 0}) || die $DBI::errstr; + + for ($i=0; $found1 != $last_found1 ; $i++) + { + $sth=$dbh->prepare("flush tables") || die "Got error on prepare: $dbh->errstr\n"; + $sth->execute || die $dbh->errstr; + $sth->finish; + + $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on prepare: $dbh->errstr\n"; + $sth->execute || die $dbh->errstr; + @row = $sth->fetchrow_array(); + $last_found1=$found1; + $found1= $row[0]; + $sth->finish; + sleep(5); + } + $dbh->disconnect; $dbh=0; + print "flush: Did $i repair/checks\n"; + exit(0); +} diff --git a/tests/udf_test b/tests/udf_test new file mode 100644 index 00000000000..4621a7b34a5 --- /dev/null +++ b/tests/udf_test @@ -0,0 +1,30 @@ +# +# For this script to work, you need to compile and install the +# udf_example script ! +# + +CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; +CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so"; +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so"; +CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; +CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so"; +CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so"; + +select metaphon("hello"); +select myfunc_double("hello","world"); +select myfunc_int(1,2,3),myfunc_int("1","11","111"); +select lookup("localhost"); +select reverse_lookup("127.0.0.1"); + +create temporary table t1 (a int,b double); +insert into t1 values (1,5),(1,4),(2,8),(3,9),(4,11); +select avgcost(a,b) from t1; +select avgcost(a,b) from t1 group by a; +drop table t1; + +DROP FUNCTION metaphon; +DROP FUNCTION myfunc_double; +DROP FUNCTION myfunc_int; +DROP FUNCTION lookup; +DROP FUNCTION reverse_lookup; +DROP FUNCTION avgcost; diff --git a/tests/udf_test.res b/tests/udf_test.res new file mode 100644 index 00000000000..66634e13616 --- /dev/null +++ b/tests/udf_test.res @@ -0,0 +1,151 @@ +-------------- +CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so" +-------------- + +Query OK, 0 rows affected + +-------------- +select metaphon("hello") +-------------- + +metaphon("hello") +HL +1 row in set + +-------------- +select myfunc_double("hello","world") +-------------- + +myfunc_double("hello","world") +108.40 +1 row in set + +-------------- +select myfunc_int(1,2,3),myfunc_int("1","11","111") +-------------- + +myfunc_int(1,2,3) myfunc_int("1","11","111") +6 6 +1 row in set + +-------------- +select lookup("localhost") +-------------- + +lookup("localhost") +127.0.0.1 +1 row in set + +-------------- +select reverse_lookup("127.0.0.1") +-------------- + +reverse_lookup("127.0.0.1") +localhost +1 row in set + +-------------- +create temporary table t1 (a int,b double) +-------------- + +Query OK, 0 rows affected + +-------------- +insert into t1 values (1,5),(1,4),(2,8),(3,9),(4,11) +-------------- + +Query OK, 5 rows affected +Records: 0 Duplicates: 5 Warnings: 0 + +-------------- +select avgcost(a,b) from t1 +-------------- + +avgcost(a,b) +8.7273 +1 row in set + +-------------- +select avgcost(a,b) from t1 group by a +-------------- + +avgcost(a,b) +4.5000 +8.0000 +9.0000 +11.0000 +4 rows in set + +-------------- +drop table t1 +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION metaphon +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION myfunc_double +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION myfunc_int +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION lookup +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION reverse_lookup +-------------- + +Query OK, 0 rows affected + +-------------- +DROP FUNCTION avgcost +-------------- + +Query OK, 0 rows affected + +Bye |