| Subject: | Server side prepared statements don't work under Mac OS X (Bus error) |
Hello all, DBD::mysql prepared statements are not working under Mac OS X 10.4.8. Trying to
use mysql_server_prepare=1 results in a bus error. I have reproduced this on a PowerBook
G4 and on a dual processor PowerMac G5. Oddly, all the tests are passing OK.
All test are passing with mysql_server_prepare=1:
$ cd ~/src/DBD-mysql-3.0007
$ export MYSQL_SERVER_PREPARE=1
$ export SLOW_TESTS=1
$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/
lib', 'blib/arch')" t/*.t
t/00base.............ok
t/10dsnlist..........ok
t/20createdrop.......ok
t/30insertfetch......ok
t/35limit............ok
t/35prepare..........ok
t/40bindparam........ok
t/40bindparam2.......ok
t/40blobs............ok
t/40listfields.......ok
t/40nulls............ok
t/40numrows..........ok
t/41bindparam........ok
t/41blobs_prepare....ok
t/42bindparam........ok
t/50chopblanks.......ok
t/50commit...........ok
t/60leaks............ok
t/dbdadmin...........ok
t/insertid...........ok
t/param_values.......ok
t/prepare_noerror....skipped
all skipped: test - will only run with MySQL 5.1 and above.
t/texecute...........ok
All tests successful, 1 test skipped.
Files=23, Tests=443, 58 wallclock secs (14.84 cusr + 9.45 csys = 24.29 CPU)
All test are also passing without mysql_server_prepare=1:
$ unset MYSQL_SERVER_PREPARE
$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/
lib', 'blib/arch')" t/*.t
t/00base.............ok
t/10dsnlist..........ok
t/20createdrop.......ok
t/30insertfetch......ok
t/35limit............ok
t/35prepare..........ok
t/40bindparam........ok
t/40bindparam2.......ok
t/40blobs............ok
t/40listfields.......ok
t/40nulls............ok
t/40numrows..........ok
t/41bindparam........ok
t/41blobs_prepare....ok
t/42bindparam........ok
t/50chopblanks.......ok
t/50commit...........ok
t/60leaks............ok
t/dbdadmin...........ok
t/insertid...........ok
t/param_values.......ok
t/prepare_noerror....skipped
all skipped: test - will only run with MySQL 5.1 and above.
t/texecute...........ok
All tests successful, 1 test skipped.
Files=23, Tests=449, 46 wallclock secs (12.13 cusr + 7.90 csys = 20.03 CPU)
Now the details of my program. It is a small script with a single prepare outside a loop:
#!/usr/bin/perl
use GD;
use DBI; # Load Database Interface Module
use Data::Dumper;
# Connect to database
my $dbh = DBI->connect
('DBI:mysql:database=database_name;host=localhost:mysql_server_prepare=1',
'myUser', 'myPassword', {RaiseError => 1})
or die "$0: Can not connect to database: " . $DBI::errstr;
# create a new image
$im = new GD::Image(6490,4000);
# allocate color black
$black = $im->colorAllocate(0,0,0);
# The maximum id value in table is 25958999
my $sth = $dbh->prepare("SELECT id FROM myTable WHERE id=?");
my $id = 1;
foreach $x (0..6489) {
print "x=$x/6490 id=$id\n";
foreach $y (0..3999){
$sth->execute($id);
if ($sth->fetchrow_array) {
$im->setPixel($x,$y,$black);
}
$id = $id + 1;
}
open(OUT,">/Users/gdelfino/id.png") or die "can not write output file";
binmode OUT;
print OUT $im->png;
close(OUT);
# I wish there were a "tail -f" equivalent for images
}
When I run it I get the following error:
$ perl -w id_photo.pl
x=0/6490 id=1
Bus error
If I replace mysql_server_prepare=1 with mysql_server_prepare=0, the program runs just fine
.
My DBD::mysql version is:
$ perldoc -m DBD::mysql | grep '$VERSION ='
$VERSION = '3.0007';
My DBI version is:
$ perldoc -m DBI | grep '^$DBI::VERSION'
$DBI::VERSION = "1.52";
The version of mysql that I am using is:
$ mysql --version
mysql Ver 14.7 Distrib 4.1.21, for apple-darwin8.6.0 (powerpc) using readline 4.3
My perl version is:
$ perl -V
Summary of my perl5 (revision 5 version 8 subversion 6) configuration:
Platform:
osname=darwin, osvers=8.0, archname=darwin-thread-multi-2level
uname='darwin b28.apple.com 8.0 darwin kernel version 7.5.0: thu mar 3 18:48:46 pst
2005; root:xnuxnu-517.99.13.obj~1release_ppc power macintosh powerpc '
config_args='-ds -e -Dprefix=/usr -Dccflags=-g -pipe -Dldflags=-Dman3ext=3pm -
Duseithreads -Duseshrplib'
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc', ccflags ='-g -pipe -fno-common -DPERL_DARWIN -no-cpp-precomp -fno-strict-
aliasing -I/usr/local/include',
optimize='-Os',
cppflags='-no-cpp-precomp -g -pipe -fno-common -DPERL_DARWIN -no-cpp-precomp
-fno-strict-aliasing -I/usr/local/include'
ccversion='', gccversion='3.3 20030304 (Apple Computer, Inc. build 1809)',
gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='env MACOSX_DEPLOYMENT_TARGET=10.3 cc', ldflags ='-L/usr/local/lib'
libpth=/usr/local/lib /usr/lib
libs=-ldbm -ldl -lm -lc
perllibs=-ldl -lm -lc
libc=/usr/lib/libc.dylib, so=dylib, useshrplib=true, libperl=libperl.dylib
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=bundle, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' ', lddlflags='-bundle -undefined dynamic_lookup -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES
PERL_IMPLICIT_CONTEXT
Locally applied patches:
23953 - fix for File::Path::rmtree CAN-2004-0452 security issue
33990 - fix for setuid perl security issues
Built under darwin
Compiled at Mar 20 2005 16:34:19
%ENV:
PERL5LIB="/sw/lib/perl5:/sw/lib/perl5/darwin"
@INC:
/sw/lib/perl5
/sw/lib/perl5/darwin
/System/Library/Perl/5.8.6/darwin-thread-multi-2level
/System/Library/Perl/5.8.6
/Library/Perl/5.8.6/darwin-thread-multi-2level
/Library/Perl/5.8.6
/Library/Perl
/Network/Library/Perl/5.8.6/darwin-thread-multi-2level
/Network/Library/Perl/5.8.6
/Network/Library/Perl
/System/Library/Perl/Extras/5.8.6/darwin-thread-multi-2level
/System/Library/Perl/Extras/5.8.6
/Library/Perl/5.8.1
With DBD::mysql 3.0003 or 3.0004 (I don't remember) I was able to run this program with
mysql_server_prepare=1 and I was amazed with the improvement in performance.
Any advice on how to solve this?
Regards,
Gustavo Delfino
Caracas, Venezuela