1#!/usr/bin/perl 2 3# Copyright (C) 2000, 2001 MySQL AB 4# Use is subject to license terms 5# 6# This program is free software; you can redistribute it and/or modify 7# it under the terms of the GNU General Public License, version 2.0, 8# as published by the Free Software Foundation. 9# 10# This program is also distributed with certain software (including 11# but not limited to OpenSSL) that is licensed under separate terms, 12# as designated in a particular file or component or in included license 13# documentation. The authors of MySQL hereby grant you an additional 14# permission to link the program and your derivative works with the 15# separately licensed software that they have included with MySQL. 16# 17# This program is distributed in the hope that it will be useful, 18# but WITHOUT ANY WARRANTY; without even the implied warranty of 19# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 20# GNU General Public License, version 2.0, for more details. 21# 22# You should have received a copy of the GNU General Public License 23# along with this program; if not, write to the Free Software 24# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA 25 26# This is a test with uses two processes to a database. 27# The other inserts records in two tables, the other does a lot of joins 28# on these. 29# 30# Warning, the output from this test will differ in 'found' from time to time, 31# but there should never be any errors 32# 33 34$host= shift || ""; 35$test_db="test"; 36 37use Mysql; 38$|= 1; # Autoflush 39 40$org_file="/tmp/export-org.$$"; 41$tmp_file="/tmp/export-old.$$"; 42$tmp_file2="/tmp/export-new.$$"; 43 44print "Connection to database $test_db\n"; 45 46$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; 47$dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; 48 49$dbh->Query("drop table if exists export"); # Ignore this error 50 51print "Creating table\n"; 52 53($dbh->Query("\ 54CREATE TABLE export ( 55 auto int(5) unsigned NOT NULL DEFAULT '0' auto_increment, 56 string char(11) NOT NULL, 57 tiny tinyint(4) NOT NULL DEFAULT '0', 58 short smallint(6) NOT NULL DEFAULT '0', 59 medium mediumint(8) NOT NULL DEFAULT '0', 60 longint int(11) NOT NULL DEFAULT '0', 61 longlong bigint(20) NOT NULL DEFAULT '0', 62 real_float float(13,1) NOT NULL DEFAULT '0.0', 63 real_double double(13,1) NOT NULL, 64 utiny tinyint(3) unsigned NOT NULL DEFAULT '0', 65 ushort smallint(5) unsigned zerofill NOT NULL DEFAULT '00000', 66 umedium mediumint(8) unsigned NOT NULL DEFAULT '0', 67 ulong int(11) unsigned NOT NULL DEFAULT '0', 68 ulonglong bigint(20) unsigned NOT NULL DEFAULT '0', 69 time_stamp timestamp, 70 blob_col blob, 71 tinyblob_col tinyblob, 72 mediumblob_col tinyblob not null, 73 longblob_col longblob not null, 74 PRIMARY KEY (auto), 75 KEY (string(5)), 76 KEY unsigned_tinykey (utiny), 77 KEY (tiny), 78 KEY (short), 79 FOREIGN KEY (medium) references export, 80 KEY (longlong), 81 KEY (real_float), 82 KEY (real_double), 83 KEY (ushort), 84 KEY (umedium), 85 KEY (ulong), 86 KEY (ulonglong), 87 KEY (ulonglong,ulong))")) or die $Mysql::db_errstr; 88 89print "Inserting data\n"; 90 91@A=("insert into export values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1)", 92 "insert into export values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,2,2)", 93 "insert into export values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,3,'','','','3')", 94 "insert into export values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,'-1')", 95 "insert into export values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,'-4294967295')", 96 "insert into export values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,'4294967295')", 97 "insert into export (string,tinyblob_col) values ('special','''\\0\\t\t\n''')", 98 "insert into export (string) values (',,!!\\\\##')", 99 "insert into export (tinyblob_col) values (',,!!!\\\\\\##')" 100 ); 101 102foreach $A (@A) 103{ 104 $dbh->Query($A) or die "query: $A returned: " . $Mysql::db_errstr; 105} 106 107 108print "Doing dump, load, check on different formats\n"; 109 110@A=(# Ordinary format 111 "", 112 # Field terminated by something 113 "fields optionally enclosed by '+' escaped by '' terminated by ',,,' lines terminated by ',,,,'", 114 "fields enclosed by '' terminated by ',' lines terminated by ''", 115 "fields enclosed by '' terminated by ',' lines terminated by '!!'", 116 #Fields enclosed by 117 #"fields enclosed by '+' terminated by ''", 118 #"fields enclosed by '+' terminated by '' lines terminated by ''", 119 "fields enclosed by '+' terminated by ',,' lines terminated by '!!!'", 120 "fields enclosed by '+' terminated by ',,' lines terminated by '##'", 121 "fields enclosed by '+' escaped by '' terminated by ',,' lines terminated by '###'", 122 "fields enclosed by '+' escaped by '' terminated by '!' lines terminated by ''", 123 "fields enclosed by '+' terminated by ',' lines terminated by ''", 124 #Fields optionally enclosed by 125 "fields optionally enclosed by '+' terminated by ','", 126 "fields optionally enclosed by '+' terminated by ',' lines terminated by ''", 127 "fields optionally enclosed by '''' terminated by ',' lines starting by 'INSERT INTO a VALUES(' terminated by ');\n'", 128 ); 129 130$dbh->Query("select * into outfile '$org_file' from export") or die $Mysql::db_errstr; 131 132 133foreach $A (@A) 134{ 135 unlink($tmp_file); 136 unlink($tmp_file2); 137 $dbh->Query("select * into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; 138 $dbh->Query("delete from export") or die $Mysql::db_errstr; 139 $dbh->Query("load data infile '$tmp_file' into table export $A") or die $Mysql::db_errstr . " with format: $A\n"; 140 $dbh->Query("select * into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; 141 if (`cmp $tmp_file2 $org_file`) 142 { 143 print "Using format $A\n"; 144 print "$tmp_file2 and $org_file differ. Plese check files\n"; 145 exit 1; 146 } 147} 148 149 150@A=(#Fixed size fields 151 "fields enclosed by '' escaped by '' terminated by ''", 152 "fields enclosed by '' escaped by '' terminated by '' lines terminated by '\\r\\n'", 153 "fields enclosed by '' terminated by '' lines terminated by ''" 154 ); 155 156unlink($org_file); 157 158$field_list="auto,ifnull(string,''),tiny,short,medium,longint,longlong,real_float,ifnull(real_double,''),utiny,ushort,umedium,ulong,ulonglong,time_stamp"; 159 160$dbh->Query("select $field_list into outfile '$org_file' from export") or die $Mysql::db_errstr; 161 162$field_list="auto,string,tiny,short,medium,longint,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,time_stamp"; 163 164foreach $A (@A) 165{ 166 unlink($tmp_file); 167 unlink($tmp_file2); 168 $dbh->Query("select $field_list into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; 169 $dbh->Query("delete from export") or die $Mysql::db_errstr; 170 $dbh->Query("load data infile '$tmp_file' into table export $A ($field_list)") or die $Mysql::db_errstr; 171 $dbh->Query("select $field_list into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; 172 if (`cmp $tmp_file2 $org_file`) 173 { 174 print "Using format $A\n"; 175 print "$tmp_file2 and $org_file differ. Plese check files\n"; 176 exit 1; 177 } 178} 179 180unlink($tmp_file); 181unlink($tmp_file2); 182unlink($org_file); 183 184$dbh->Query("drop table export") or die $Mysql::db_errstr; 185 186print "Test ok\n"; 187exit 0; 188