Mac Forum / Programming / Perl / October 2006

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/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 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