Re: DBD::mysql mysql_server_prepare=1 anyone?
I have compiled a fresh copy of perl 5.8.8 (which defaults to no threads) and I still get the same Bus error. So maybe this is not related to threads. So, if this is not related to threads, recompiling mysql with -with- thread-safe-client will probably not help. My next experiment will be using mysql 5 instead of 4.1 Regards, Gustavo Delfino P.D. These are the details of my new perl: These are the details of my new perl: $ /usr/local/bin/perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=darwin, osvers=8.8.0, archname=darwin-2level uname='darwin gd-powerbook-g4.local 8.8.0 darwin kernel version 8.8.0: fri sep 8 17:18:57 pdt 2006; root:xnu-792.12.6.obj~1release_ppc power macintosh powerpc ' config_args='-de' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-fno-common -DPERL_DARWIN -no-cpp-precomp - fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/ include -I/opt/local/include', optimize='-O3', cppflags='-no-cpp-precomp -fno-common -DPERL_DARWIN -no-cpp- precomp -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/ usr/local/include -I/opt/local/include' ccversion='', gccversion='4.0.0 20041026 (Apple Computer, Inc. build 4061)', gccosandvers='darwin8' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 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 -L/opt/local/lib' libpth=/usr/local/lib /opt/local/lib /usr/lib libs=-ldbm -ldl -lm -lc perllibs=-ldl -lm -lc libc=/usr/lib/libc.dylib, so=dylib, useshrplib=false, libperl=libperl.a gnulibc_version='' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=bundle, d_dlsymun=undef, ccdlflags=' ' cccdlflags=' ', lddlflags=' -bundle -undefined dynamic_lookup -L/ usr/local/lib -L/opt/local/lib' Characteristics of this binary (from libperl): Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO Built under darwin Compiled at Oct 8 2006 06:27:17 @INC: /usr/local/lib/perl5/5.8.8/darwin-2level /usr/local/lib/perl5/5.8.8 /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level /usr/local/lib/perl5/site_perl/5.8.8 /usr/local/lib/perl5/site_perl On Oct 8, 2006, at 1:07 AM, Sherm Pendley wrote: On Oct 7, 2006, at 2:26 PM, Gustavo Delfino wrote: I now want to try with the -with-thread-safe-client option, but I don't know how to add it. It's not a Makefile.PL option. It's a configure option when you're building MySQL itself. sherm--
Re: DBD::mysql mysql_server_prepare=1 anyone?
On 2006–10–08, at 13:41, Gustavo Delfino wrote: I have compiled a fresh copy of perl 5.8.8 (which defaults to no threads) and I still get the same Bus error. So maybe this is not related to threads. Thanks for putting in the effort to try. Too bad it had no effect. You should add a note about this finding to the bug report you submitted earlier. So, if this is not related to threads, recompiling mysql with -with- thread-safe-client will probably not help. My next experiment will be using mysql 5 instead of 4.1 Good luck. -- Dominic Dunlop
Re: DBD::mysql mysql_server_prepare=1 anyone?
On 2006–10–06, at 21:58, Gustavo Delfino wrote: Any advice on how to solve this? Well, I can't help you, but I know someone who can. Or who should be able to, anyway. Go to http://search.cpan.org/~capttofu/DBD- mysql-3.0007/ and repost your question through the View/Report bugs link after first checking that your bug is not a duplicate. (At a quick glance, it does not seem to be to me, but please check carefully.) The report you posted to the list is a good one, but lacks two things which will help in the search for a solution: The crash log. You should be able to find this in ~/Library/Logs/ CrashReporter/perl.crash.log. (If there are lots of log entries, include only one in your report.) The output of perl -V for the perl you're using. (I'm guessing this is the 5.8.6 delivered as standard with Mac OS X.) One more thing: the perl shipped with Mac OS X supports threading, and the DBI documentation still says Using DBI with perl threads is not yet recommended for production environments. although I can't see that this is an issue with your code. However, the DBD::MySQL man page says The obvious question is: Are the C libraries thread safe? In the case of MySQL the answer is mostly and, in theory, you should be able to get a yes, if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe- client to configure. See the section on How to make a threadsafe client in the manual. so you might try a couple of things. First, rebuild DBD::MySQL with that option; second (and rather more tedious), download the perl source, build a non-threaded perl (the default) and try that. -- Dominic Dunlop
Re: DBD::mysql mysql_server_prepare=1 anyone?
However, the DBD::MySQL man page says The obvious question is: Are the C libraries thread safe? In the case of MySQL the answer is mostly and, in theory, you should be able to get a yes, if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe- client to configure. See the section on How to make a threadsafe client in the manual. so you might try a couple of things. First, rebuild DBD::MySQL with that option; second (and rather more tedious), download the perl source, build a non-threaded perl (the default) and try that. Thank you Dominic. I have submitted a bug report to cpan.org with the additional information you suggested: http://rt.cpan.org/Public/Bug/Display.html?id=21946 I now want to try with the -with-thread-safe-client option, but I don't know how to add it. I tried with: perl Makefile.PL -with-thread-safe-client result: Unknown option: with-thread-safe-client perl Makefile.PL --with-thread-safe-client result: Unknown option: with-thread-safe-client perl Makefile.PL --cflags=-with-thread-safe-client result: OK, but make fails: powerpc-apple-darwin8-gcc-4.0.0: unrecognized option `-with-thread- safe-client' perl Makefile.PL --cflags=--with-thread-safe-client result: OK, but make fails: unrecognized command line option -fwith-thread-safe-client Do you know how to add it? Regards, Gustavo Delfino
Re: DBD::mysql mysql_server_prepare=1 anyone?
On Oct 7, 2006, at 2:26 PM, Gustavo Delfino wrote: I now want to try with the -with-thread-safe-client option, but I don't know how to add it. It's not a Makefile.PL option. It's a configure option when you're building MySQL itself. sherm-- Web Hosting by West Virginians, for West Virginians: http://wv-www.net Cocoa programming in Perl: http://camelbones.sourceforge.net
DBD::mysql mysql_server_prepare=1 anyone?
Hello, I am trying to make my DBI application faster. According to
DBD::mysql documentation I should be able to prepare a query that I'm
going to use in a loop so that when I execute it inside the loop, it
goes faster. In order to maximize this performance improvement, you
need to add mysql_server_prepare=1 to the connection string.
The problem is that when I do this I get a Bus Error. Is it just me
or prepared statements are broken under Mac OS X?
Now the details:
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/35limitok
t/35prepare..ok
t/40bindparamok
t/40bindparam2...ok
t/40blobsok
t/40listfields...ok
t/40nullsok
t/40numrows..ok
t/41bindparamok
t/41blobs_prepareok
t/42bindparamok
t/50chopblanks...ok
t/50commit...ok
t/60leaksok
t/dbdadmin...ok
t/insertid...ok
t/param_values...ok
t/prepare_noerrorskipped
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/35limitok
t/35prepare..ok
t/40bindparamok
t/40bindparam2...ok
t/40blobsok
t/40listfields...ok
t/40nullsok
t/40numrows..ok
t/41bindparamok
t/41blobs_prepareok
t/42bindparamok
t/50chopblanks...ok
t/50commit...ok
t/60leaksok
t/dbdadmin...ok
t/insertid...ok
t/param_values...ok
t/prepare_noerrorskipped
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 on my Power Mac G5 (Dual 2.5 GHz).
My DBD::mysql version is:
$ perldoc -m DBD::mysql | grep '$VERSION ='
$VERSION = '3.0007';
My perl version is:
$ perl -v
This is perl, v5.8.6 built for darwin-thread-multi-2level
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
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 (this was on my PowerBook G4).
Any advice on how to solve this?
Regards,
Gustavo Delfino
Caracas, Venezuela
