Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 43822
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: CAPTTOFU [...] cpan.org
Requestors: gary [...] pento.net
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 4.010
Fixed in: (no value)



Subject: Quotes around large numbers cause performance problems in MySQL
Download (untitled) / with headers
text/plain 266b
Quotes around large INTs can cause performance problems in MySQL. See: http://bugs.mysql.com/34384 http://bugs.mysql.com/43319 This isn't scheduled to be fixed in MySQL until 6.0, an easy fix in DBD-mysql is to change quote() and placeholders to not quote numbers.
Download (untitled) / with headers
text/plain 411b
On Tue Mar 03 19:00:20 2009, pento wrote: Show quoted text
> Quotes around large INTs can cause performance problems in MySQL. See: > > http://bugs.mysql.com/34384 > http://bugs.mysql.com/43319 > > This isn't scheduled to be fixed in MySQL until 6.0, an easy fix in > DBD-mysql is to change quote() and placeholders to not quote numbers.
I'll be done with my Book this week, so I'll be fixing this very soon -- next week-ish.
From: gary [...] pento.net
Download (untitled) / with headers
text/plain 543b
On Wed Mar 04 15:30:31 2009, CAPTTOFU wrote: Show quoted text
> On Tue Mar 03 19:00:20 2009, pento wrote:
> > Quotes around large INTs can cause performance problems in MySQL. See: > > > > http://bugs.mysql.com/34384 > > http://bugs.mysql.com/43319 > > > > This isn't scheduled to be fixed in MySQL until 6.0, an easy fix in > > DBD-mysql is to change quote() and placeholders to not quote numbers.
> > I'll be done with my Book this week, so I'll be fixing this very soon -- > next week-ish.
Just following up on this, have you had a chance to look at it?
From: Jeremy [...] Zawodny.com
Download (untitled) / with headers
text/plain 806b
On Thu Mar 19 18:52:00 2009, pento wrote: Show quoted text
> On Wed Mar 04 15:30:31 2009, CAPTTOFU wrote:
> > On Tue Mar 03 19:00:20 2009, pento wrote:
> > > Quotes around large INTs can cause performance problems in MySQL. See: > > > > > > http://bugs.mysql.com/34384 > > > http://bugs.mysql.com/43319 > > > > > > This isn't scheduled to be fixed in MySQL until 6.0, an easy fix in > > > DBD-mysql is to change quote() and placeholders to not quote numbers.
> > > > I'll be done with my Book this week, so I'll be fixing this very soon -- > > next week-ish.
> > Just following up on this, have you had a chance to look at it?
Anything I can do to help speed this along? This is affecting us semi-regularly at craigslist. Feel free to ping me at jzawodn@craigslist.org to discuss more if you'd like. Thanks! Jeremy
Testing this now. There seems to already be code to do this if you're not using server-side prepared statement. I will have a reply soon. On Thu Apr 09 18:17:46 2009, JZAWODNY wrote: Show quoted text
> On Thu Mar 19 18:52:00 2009, pento wrote:
> > On Wed Mar 04 15:30:31 2009, CAPTTOFU wrote:
> > > On Tue Mar 03 19:00:20 2009, pento wrote:
> > > > Quotes around large INTs can cause performance problems in
MySQL. See: Show quoted text
> > > > > > > > http://bugs.mysql.com/34384 > > > > http://bugs.mysql.com/43319 > > > > > > > > This isn't scheduled to be fixed in MySQL until 6.0, an easy fix in > > > > DBD-mysql is to change quote() and placeholders to not quote
numbers. Show quoted text
> > > > > > I'll be done with my Book this week, so I'll be fixing this very
soon -- Show quoted text
> > > next week-ish.
> > > > Just following up on this, have you had a chance to look at it?
> > Anything I can do to help speed this along? This is affecting us > semi-regularly at craigslist. Feel free to ping me at > jzawodn@craigslist.org to discuss more if you'd like. > > Thanks! > > Jeremy
Download (untitled) / with headers
text/plain 406b
There's an attribute "mysql_unsafe_bind_type_guessing" that was there but didn't work as advertised that I've fixed. I want to rename it too. It doesn't exactly make you want to use it by the name of it. It has issues, but with documentation can be made useful. With that said, I can't get this issue with quotes to be a bug in the first place - it works fine either way. I'm running mysql 5.1.26 on OS X.
Please see http://svn.perl.org/modules/DBD-mysql/trunk I will be releasing this week 4.011 Try this out (mysql_bind_type_guessing - makes it so no quotes will be used if number - works with floats too;): use strict; use warnings; use DBI; use Data::Dumper; my $create= <<'EOTABLE'; create table bigt1 ( id bigint unsigned not null default 0 ) EOTABLE #my $dbh= DBI->connect('DBI:mysql:test', 'user', 'pass', { mysql_bind_type_guessing => 2}) # or die "unable to connect $DBI::errstr"; my $dbh= DBI->connect('DBI:mysql:test', 'user', 'pass') or die "unable to connect $DBI::errstr"; $dbh->{mysql_bind_type_guessing}= 1; $dbh->do('drop table if exists bigt1'); $dbh->do($create); my $statement= 'insert into bigt1 (id) values (?)'; my $sth= $dbh->prepare($statement); my $rows= $sth->execute('9999999999999999'); print "rows $rows\n"; $statement= 'update bigt1 set id = ?'; $sth= $dbh->prepare($statement); $rows= $sth->execute('9999999999999998'); print "rows $rows\n"; my $retref= $dbh->selectall_arrayref('select * from bigt1'); print Dumper $retref;
Download (untitled) / with headers
text/plain 1.3k
On Sat Apr 11 12:51:21 2009, CAPTTOFU wrote: Show quoted text
> Please see http://svn.perl.org/modules/DBD-mysql/trunk > > I will be releasing this week 4.011 > > Try this out (mysql_bind_type_guessing - makes it so no quotes will be > used if number - works with floats too;): > > use strict; > use warnings; > > use DBI; > use Data::Dumper; > > my $create= <<'EOTABLE'; > create table bigt1 ( > id bigint unsigned not null default 0 > ) > EOTABLE > > #my $dbh= DBI->connect('DBI:mysql:test', 'user', 'pass', { > mysql_bind_type_guessing => 2}) > # or die "unable to connect $DBI::errstr"; > my $dbh= DBI->connect('DBI:mysql:test', 'user', 'pass') > or die "unable to connect $DBI::errstr"; > > $dbh->{mysql_bind_type_guessing}= 1; > > $dbh->do('drop table if exists bigt1'); > $dbh->do($create); > > my $statement= 'insert into bigt1 (id) values (?)'; > > my $sth= $dbh->prepare($statement); > > my $rows= $sth->execute('9999999999999999'); > print "rows $rows\n"; > > $statement= 'update bigt1 set id = ?'; > $sth= $dbh->prepare($statement); > $rows= $sth->execute('9999999999999998'); > print "rows $rows\n"; > > my $retref= $dbh->selectall_arrayref('select * from bigt1'); > print Dumper $retref;
I meant to add: 439 Query insert into bigt1 (id) values (9999999999999999) 439 Query update bigt1 set id = 9999999999999998 439 Query select * from bigt1 From log
Fixed in 4.012


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.