[CDBI] Lost connection to MySQL server during query

Carlos Vicente cvicente at network-services.uoregon.edu
Fri Jan 20 18:06:00 GMT 2006


Hi all,

I've been having this kind of error recently and can't seem to find a
solution:

error:   	DBD::mysql::st execute failed: Lost connection to MySQL server
during query [for Statement "SELECT address, prefix, status, used_by
FROM Ipblock
WHERE id=?
"] at /usr/lib/perl5/site_perl/5.8.0/DBIx/ContextualFetch.pm line 51.
context:  	
...  	
47:  	sub _untaint_execute {
48:  	my $sth = shift;
49:  	my $old_value = $sth->{Taint};
50:  	$sth->{Taint} = 0;
51:  	my $ret = $sth->SUPER::execute(@_);
52:  	$sth->{Taint} = $old_value;
53:  	return $ret;
54:  	}
55:  	
...  	
code stack:  	/usr/lib/perl5/site_perl/5.8.0/DBIx/ContextualFetch.pm:51
/usr/lib/perl5/site_perl/5.8.0/DBIx/ContextualFetch.pm:31
/usr/lib/perl5/site_perl/5.8.0/DBIx/ContextualFetch.pm:86
/usr/lib/perl5/site_perl/5.8.0/Class/DBI.pm:849
/usr/lib/perl5/site_perl/5.8.0/Class/DBI.pm:836
/usr/lib/perl5/site_perl/5.8.0/Class/Accessor.pm:385
/usr/local/netdot/lib/Netdot/IPManager.pm:416
/usr/local/netdot/lib/Netdot/IPManager.pm:748
/usr/local/netdot/lib/Netdot/DeviceManager.pm:828
/usr/local/netdot/htdocs/updatedevice.html:164
/usr/local/netdot/htdocs/section_management.html:174
/usr/local/netdot/htdocs/autohandler:20
raw error

As you can probably guess, this is a web app using Class::DBI, HTML::Mason and mod_perl.

The problem happens at random and it's difficult to reproduce.  It happens at differnt parts of the code.
In some cases, to simplify things, I bypass Class::DBI and execute direct SQL queries using the db handle returned by db_Main.
I don't know whether this has anything to do with the issue.
I've followed the suggestions in the wiki about the interaction between Apache::DBI and Ima::DBI and added an overridden 
db_Main in my Class::DBI subclass.  But that didn't seem get rid of the error.  :-(

The versions of packages I'm running are as follows:

Class::DBI v3.0.14
Ima::DBI 0.34
DBI 1.50
DBD::mysql 3.0002
Apache 2.0.53
Apache::DBI 0.9901 
Ima::DBI 0.34
HTML::Mason 1.32
mod_perl-2.0.1
MySQL 4.1.14-standard


Any help would be very appreciated.

cv


On Fri, 2006-01-20 at 04:00 -0800,
classdbi-request at svr02.digitalcraftsmen.net wrote:
> Send ClassDBI mailing list submissions to
> 	classdbi at lists.digitalcraftsmen.net
> 
> To subscribe or unsubscribe via the World Wide Web, visit
> 	http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
> or, via email, send a message with subject or body 'help' to
> 	classdbi-request at lists.digitalcraftsmen.net
> 
> You can reach the person managing the list at
> 	classdbi-owner at lists.digitalcraftsmen.net
> 
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of ClassDBI digest..."
> 
> 
> Today's Topics:
> 
>    1. Re: Prepared statement handle error under mod_perl
>       (Perrin Harkins)
>    2. Re: Prepared statement handle error under mod_perl (Ryan Tate)
>    3. Can CDBI do this? (Ben Lavender)
>    4. Re: Can CDBI do this? (Bill Moseley)
>    5. Re: Can CDBI do this? (Will Hawes)
>    6. Re: Can CDBI do this? (Edward J. Sabol)
>    7. Re: Re: Can CDBI do this? (Matt S Trout)
>    8. Re: Re: Can CDBI do this? (Perrin Harkins)
>    9. Patch: POD typos in CDBI::Sweet (Frank Wiegand)
>   10. Re: Patch: POD typos in CDBI::Sweet (Matt S Trout)
> 
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Thu, 19 Jan 2006 09:54:08 -0500
> From: Perrin Harkins <perrin at elem.com>
> Subject: Re: [CDBI] Prepared statement handle error under mod_perl
> To: Ryan Tate <lists at ryantate.com>
> Cc: classdbi at lists.digitalcraftsmen.net
> Message-ID: <1137682448.31894.6.camel at localhost.localdomain>
> Content-Type: text/plain
> 
> On Wed, 2006-01-18 at 22:44 -0800, Ryan Tate wrote:
> > Thanks for this, it sounds promising. I have it disabled but of course
> > I need some hours before I know if it worked ;->
> 
> You can probably just kill the connection from the server side to get
> the same effect.
> 
> - Perrin
> 
> 
> 
> 
> ------------------------------
> 
> Message: 2
> Date: Thu, 19 Jan 2006 09:57:36 -0800
> From: Ryan Tate <lists at ryantate.com>
> Subject: Re: [CDBI] Prepared statement handle error under mod_perl
> To: Perrin Harkins <perrin at elem.com>,
> 	classdbi at lists.digitalcraftsmen.net
> Message-ID:
> 	<9e3ac3270601190957i11eda6ebwf8d838301648fd85 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> On 1/19/06, Perrin Harkins <perrin at elem.com> wrote:
> > You can probably just kill the connection from the server side to get
> > the same effect.
> 
> That would be far too intelligent ... but I tried this technique at
> your insistence.
> 
> Doing so, I was able to confirm that this was an issue with
> mysql_auto_reconnect. Setting it to 0 in my connection attributes
> allowed my Web app to survive a stop and start of mysqld just fine.
> Leaving the DBD::mysql default in place (and thus leaving
> mysql_auto_reconnect at 1, since I'm running under mod_perl), the Web
> app produced the aforementioned error after stopping and then starting
> mysqld.
> 
> So thanks for solving this problem!
> 
> RT
> 
> 
> 
> ------------------------------
> 
> Message: 3
> Date: Thu, 19 Jan 2006 22:11:10 +0100
> From: Ben Lavender <blavender at gmail.com>
> Subject: [CDBI] Can CDBI do this?
> To: classdbi at lists.digitalcraftsmen.net
> Message-ID:
> 	<ee8ed2da0601191311p19a3b03age3e13e158322522f at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> Hello everyone,
> 
> I've been using cdbi for a while, but have to jump on to someone
> else's work for something.  Long story short, I've got to do some
> multi-table searches of a sort I haven't done before.
> 
> For purposes of illustration, I have 2 tables, events and
> certifications.  An event row has a field, cert, which is a primary
> key id of a row on the certs table.  In CDBI speak, a CDBI::Event
> has_a CDBI::Cert.
> 
> Long story short, I need to run a query that looks like this:
> 
> SELECT events.(essential) FROM   events, certs WHERE  ( events.duedate
> < "2999-01-01" AND certs.id = events.cert AND certs.type = "Some
> String I Input") ORDER BY dueDate LIMIT 10;
> 
> Can CDBI or one of it's nifty associated classes do this without
> resorting to messey, maintenance-error-prone set_sql and the like?
> The CDBI:AbstractSearch docs would seem to point to 'no'.  I'll manage
> if not, but I don't want to start doing something I'm going to kick
> myself for later.
> 
> Thanks,
> Ben
> 
> 
> Note:  Initially sent to list before joining and bounced.  Apologies
> should a duplicate appear.
> 
> 
> 
> ------------------------------
> 
> Message: 4
> Date: Thu, 19 Jan 2006 13:20:32 -0800
> From: Bill Moseley <moseley at hank.org>
> Subject: Re: [CDBI] Can CDBI do this?
> To: classdbi at lists.digitalcraftsmen.net
> Message-ID: <20060119212032.GA20474 at hank.org>
> Content-Type: text/plain; charset=us-ascii
> 
> On Thu, Jan 19, 2006 at 10:11:10PM +0100, Ben Lavender wrote:
> > SELECT events.(essential) FROM   events, certs WHERE  ( events.duedate
> > < "2999-01-01" AND certs.id = events.cert AND certs.type = "Some
> > String I Input") ORDER BY dueDate LIMIT 10;
> 
> Look at CDBI::Sweet;
> 
> -- 
> Bill Moseley
> moseley at hank.org
> 
> 
> 
> 
> ------------------------------
> 
> Message: 5
> Date: Thu, 19 Jan 2006 21:32:23 +0000
> From: Will Hawes <info at whawes.co.uk>
> Subject: Re: [CDBI] Can CDBI do this?
> Cc: classdbi at lists.digitalcraftsmen.net
> Message-ID: <43D00567.8070806 at whawes.co.uk>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> 
> Bill Moseley wrote:
> > On Thu, Jan 19, 2006 at 10:11:10PM +0100, Ben Lavender wrote:
> >> SELECT events.(essential) FROM   events, certs WHERE  ( events.duedate
> >> < "2999-01-01" AND certs.id = events.cert AND certs.type = "Some
> >> String I Input") ORDER BY dueDate LIMIT 10;
> > 
> > Look at CDBI::Sweet;
> > 
> 
> Or better still, switch to DBIx::Class.
> 
> 
> 
> 
> ------------------------------
> 
> Message: 6
> Date: Thu, 19 Jan 2006 16:45:54 -0500
> From: "Edward J. Sabol" <sabol at alderaan.gsfc.nasa.gov>
> Subject: [CDBI] Re: Can CDBI do this?
> To: classdbi at svr02.digitalcraftsmen.net
> Message-ID: <200601192145.k0JLjs99019937 at alderaan.gsfc.nasa.gov>
> 
> Ben Lavender asked about the following:
> >>> SELECT events.(essential) FROM events, certs WHERE ( events.duedate <
> >>> "2999-01-01" AND certs.id = events.cert AND certs.type = "Some String I
> >>> Input") ORDER BY dueDate LIMIT 10;
> 
> Bill Moseley replied:
> >> Look at CDBI::Sweet;
> 
> Will Hawes replied:
> > Or better still, switch to DBIx::Class.
> 
> Frankly, you don't need to do either one. Yes, CDBI can do this. Read the
> CDBI docs on the set_sql method:
> 
> http://search.cpan.org/~tmtm/Class-DBI/lib/Class/DBI.pm#Ima%3A%3ADBI_queries
> 
> and read the Wiki node on using joins:
> 
> http://wiki.class-dbi.com/wiki/Using_joins
> 
> 
> 
> ------------------------------
> 
> Message: 7
> Date: Thu, 19 Jan 2006 22:14:15 +0000
> From: Matt S Trout <dbix-class at trout.me.uk>
> Subject: Re: [CDBI] Re: Can CDBI do this?
> To: "Edward J. Sabol" <sabol at alderaan.gsfc.nasa.gov>
> Cc: classdbi at svr02.digitalcraftsmen.net
> Message-ID: <20060119221415.GC29384 at central.redice.net>
> Content-Type: text/plain; charset=us-ascii
> 
> On Thu, Jan 19, 2006 at 04:45:54PM -0500, Edward J. Sabol wrote:
> > Ben Lavender asked about the following:
> > >>> SELECT events.(essential) FROM events, certs WHERE ( events.duedate <
> > >>> "2999-01-01" AND certs.id = events.cert AND certs.type = "Some String I
> > >>> Input") ORDER BY dueDate LIMIT 10;
> > 
> > Bill Moseley replied:
> > >> Look at CDBI::Sweet;
> > 
> > Will Hawes replied:
> > > Or better still, switch to DBIx::Class.
> > 
> > Frankly, you don't need to do either one. Yes, CDBI can do this. Read the
> > CDBI docs on the set_sql method:
> 
> This is, admittedly, true. You don't *need* to do either one. But your life
> will be far easier if you do, unless you like writing an extra SQL query
> every single time you need to change the arguments. And embedding SQL queries
> willy-nilly in your object class, so you've now got two languages to worry
> about instead of one.
> 
> I spent a long time doing set_sqls. Eventually I gave up and wrote the Sweet
> join support instead, and my maintainability shot up. YMMV, but that's my
> experience - and the reason why when I hit the ceiling of what CDBI could
> cope with via subclassing, I started DBIx::Class to replace it (for values
> of "replace" meaning "in my code" - I think it's a massive improvement on
> CDBI, but I have no issue with people disagreeing with me on that :)
> 
> -- 
>      Matt S Trout       Offering custom development, consultancy and support
>   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
> Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information
> 
>  + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +
> 
> 
> 
> ------------------------------
> 
> Message: 8
> Date: Thu, 19 Jan 2006 17:08:50 -0500
> From: Perrin Harkins <perrin at elem.com>
> Subject: Re: [CDBI] Re: Can CDBI do this?
> To: "Edward J. Sabol" <sabol at alderaan.gsfc.nasa.gov>
> Cc: classdbi at svr02.digitalcraftsmen.net
> Message-ID: <1137708530.5270.40.camel at localhost.localdomain>
> Content-Type: text/plain
> 
> On Thu, 2006-01-19 at 16:45 -0500, Edward J. Sabol wrote:
> > Frankly, you don't need to do either one. Yes, CDBI can do this. Read the
> > CDBI docs on the set_sql method
> 
> The original poster specifically asked for a solution that avoids
> set_sql, but I agree, it's much simpler than any of the alternatives if
> you already have a working Class::DBI setup.
> 
> - Perrin
> 
> 
> 
> 
> ------------------------------
> 
> Message: 9
> Date: Fri, 20 Jan 2006 00:02:07 +0100
> From: Frank Wiegand <frank at planet-interview.de>
> Subject: [CDBI] Patch: POD typos in CDBI::Sweet
> To: classdbi at svr02.digitalcraftsmen.net
> Message-ID: <43D01A6F.6020005 at planet-interview.de>
> Content-Type: text/plain; charset="iso-8859-15"
> 
> Here's a patch that fixes some POD typos in Class/DBI/Sweet.pm
> -------------- next part --------------
> Index: lib/Class/DBI/Sweet.pm
> ===================================================================
> --- lib/Class/DBI/Sweet.pm	(Revision 3108)
> +++ lib/Class/DBI/Sweet.pm	(Arbeitskopie)
> @@ -958,7 +958,7 @@
>  
>  =item case, cmp, convert, and logic
>  
> -These attributes are passed to L<SQL::Abstact>'s constuctor and alter the
> +These attributes are passed to L<SQL::Abstact>'s constructor and alter the
>  behavior of the criteria.
>  
>      { cmp => 'like' }
> @@ -1060,7 +1060,7 @@
>  The %s strings are replaced by sql parts as described in L<Ima::DBI>.  See
>  "statement_order" for the sql part that replaces each instance of %s.
>  
> -In addition, the associated statment for COUNT(*) statement has "_Count"
> +In addition, the associated statement for a COUNT(*) statement has "_Count"
>  appended to the sql_method name.  Only "from" and "where" are passed to the sprintf
>  function.
>  
> @@ -1149,7 +1149,7 @@
>  
>      where order_by limit
>  
> -Prefecch_cols are the columns selected when a prefetch is speccified -- use in the SELECT.
> +Prefetch_cols are the columns selected when a prefetch is specified -- use in the SELECT.
>  Prefetch_names are just the column names for use in GROUP BY.
>  
>  This is useful when statement order needs to be changed, such as when using a
> @@ -1179,11 +1179,11 @@
>  
>  =head2 page
>  
> -Retuns a page object and an iterator. The page object is an instance of
> +Returns a page object and an iterator. The page object is an instance of
>  L<Data::Page>.
>  
>      ( $page, $iterator )
> -        = MyApp::Article->page( $criteria, { rows => 10, page => 2 );
> +        = MyApp::Article->page( $criteria, { rows => 10, page => 2 } );
>  
>      printf( "Results %d - %d of %d Found\n",
>          $page->first, $page->last, $page->total_entries );
> 
> ------------------------------
> 
> Message: 10
> Date: Fri, 20 Jan 2006 00:00:55 +0000
> From: Matt S Trout <dbix-class at trout.me.uk>
> Subject: Re: [CDBI] Patch: POD typos in CDBI::Sweet
> To: Frank Wiegand <frank at planet-interview.de>
> Cc: classdbi at svr02.digitalcraftsmen.net
> Message-ID: <20060120000055.GF29384 at central.redice.net>
> Content-Type: text/plain; charset=us-ascii
> 
> On Fri, Jan 20, 2006 at 12:02:07AM +0100, Frank Wiegand wrote:
> > Here's a patch that fixes some POD typos in Class/DBI/Sweet.pm
> 
> Contact Marcus Ramberg, who's maintaing Sweet these days.
> 
> Also -
> 
> -These attributes are passed to L<SQL::Abstact>'s constuctor and alter the
> +These attributes are passed to L<SQL::Abstact>'s constructor and alter the
> 
> Maybe you could fix Abstact -> Abstract while you're there :)
> 
> -- 
>      Matt S Trout       Offering custom development, consultancy and support
>   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
> Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information
> 
>  + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +
> 
> 
> 
> ------------------------------
> 
> _______________________________________________
> ClassDBI mailing list
> ClassDBI at lists.digitalcraftsmen.net
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
> 
> 
> End of ClassDBI Digest, Vol 7, Issue 22
> ***************************************




More information about the ClassDBI mailing list