[Dbix-class] lost connection during query
J.G.Konrad
konradjg at gmail.com
Mon Nov 12 17:13:55 GMT 2007
for starters i was failing to set mysql_auto_reconnect=3D0. this value
defaults to 1 when using mod_perl. By setting this to zero it has
solved the issue of the failing statement not being retried using
Apache, mod_perl, Catalyst and DBix.
So now the failing statement is retried, successfully, but the next
statement fails. I understand that DBIx does not take care of this (
it will only reconnect+retry once per statement/txn ), but I'm curious
to know why after the failed statement is retried, the next statement
fails as well? After the second failure, there are more statements
run successfully. For what I described here I have set the
wait_timeout=3D10.
Does anybody else have wait_timeout issues? Our application is
running 6 lamp machines ( apache, mod_perl, catalyst, dbix ) with 22 (
and growing ) cat & dbix vhosted sites on each machine.
On Nov 9, 2007 3:19 PM, J. G. Konrad <konradjg at gmail.com> wrote:
> Using DBIx::Class 0.08007 and a simple script that will connect to a
> database, wait for greater than the timeout and execute a statement
> on a expired connection, everything works as described below. A new
> connection is made and the failing statement is retried. Setting
> DBIC_TRACE=3D1 shows the SQL twice even though only one ->single() is
> executed.
>
> When using a Apache, mod_perl, Catalyst , DBIx setup, a new connection
> is created but the failing statement is not retried. It doesn't
> sound like this is the expected behavior. Am I missing something for
> this type of setup?
>
>
> On Oct 30, 2007 2:46 PM, Brandon Black <blblack at gmail.com> wrote:
> >
> > On 10/30/07, J. G. Konrad <konradjg at gmail.com> wrote:
> > > I was wondering if anyone had experience with this type of error, "DBI
> > > Exception: DBD::mysql::st execute failed: Lost connection to MySQL se=
rver
> > > during query" while using DBIx::Class ?
> > >
> > > our MySQL server was getting swamped with too many connections so I c=
hanged
> > > the wait_timeout to 30 seconds rather then the default of 28800 secon=
ds. In
> > > previous post that I read there is reconnecting behavior and this sho=
uld
> > > work. That is, if the connection is gone ( timed out ) , DBIx::Clas=
s will
> > > reconnect. Am I hitting some kind of race condition with the reconnec=
tion
> > > behavior?
> > >
> > > I am using version 0.08003 DBIx::Class.
> > >
> > >
> > > any thoughts / comments are appreciated.
> > >
> >
> > The reconnect behavior of DBIx::Class was intended for when you get
> > disconnected in-between statements. I'm not familiar with this issue
> > of MySQL disconnecting a query that's in-progress. I would hope that
> > means the query has no side-effects (in the case of a txn, or even an
> > isolated update/insert/delete via AutoCommit).
> >
> > That being said, the way the reconnect code currently operates is that
> > after a DBI exception it checks to see whether the connection is still
> > alive. If it isn't, it assumes that's the cause of the exception and
> > then reconnects and retries the statement or transaction. However, it
> > will only do this exactly once per statement/txn attempt on your part.
> > If it's happening twice in a row (quite possible in the scenario
> > you're describing), it won't save you on the second failure in a row
> > of the same statement/txn.
> >
> > The logic behind the "only reconnect+retry once per statement/txn"
> > idea is that we want to protect from things like disconnects due to
> > idle sessions or rebooted servers, but we don't want to retry
> > indefinitely if we keep getting disconnected (because that probably
> > means we're triggering a bug and killing the connect (or server) with
> > our statement, or something even worse, that needs to be addressed
> > manually).
> >
> > You definitely should upgrade DBIx::Class though, there were related
> > bugfixes (in general - perhaps not specific enough to your case) since
> > 0.08003 IIRC.
> >
> > -- Brandon
> >
> > _______________________________________________
> > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> > Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.rawm=
ode.org
> >
>
-------------- next part --------------
snip
....
....
[ ** NOTE ** 1ST FAILING QUERY ]
SELECT me.id, me.user_id, me.parent_user_id, me.locale, me.invite_code, me.=
create_date, me.create_datetime, me.register_date, me.time_zone, me.pm_id, =
me.current_step, me.country_code, me.recognition_cookie, me.validated, me.p=
artner_id, me.password, me.remote_ip, me.email, me.email_optin_notification=
, me.email_optin_reminder, me.status, me.click_id, me.motivator_id, me.acti=
on_requirement_id, me.page_flow_id, me.user_type, me.tracking_cookie, me.ca=
n_redeem, me.flow_id, me.tainted, me.pcp_version FROM users me WHERE ( emai=
l =3D ? ): 'konradjg at gmail.com'
-> prepare_cached for DBD::mysql::db (DBI::db=3DHASH(0xb773104)~0xa4975=
48 'SELECT me.id, me.user_id, me.parent_user_id, me.locale, me.invite_code,=
me.create_date, me.create_datetime, me.register_date, me.time_zone, me.pm_=
id, me.current_step, me.country_code, me.recognition_cookie, me.validated, =
me.partner_id, me.password, me.remote_ip, me.email, me.email_optin_notifica=
tion, me.email_optin_reminder, me.status, me.click_id, me.motivator_id, me.=
action_requirement_id, m...' HASH(0xbc469f8) 3) thr#98c2518
-> FETCH for DBD::mysql::st (DBI::st=3DHASH(0xbbb4444)~0xb97c650 'Activ=
e') thr#98c2518
-> dbd_st_FETCH_attrib for 0bbba9e8, key Active
<- FETCH=3D '' at DBI.pm line 1648
<- prepare_cached=3D DBI::st=3DHASH(0xbbb4444) at DBI.pm line 1168
-> bind_param for DBD::mysql::st (DBI::st=3DHASH(0xbbb4444)~0xb97c650 1=
'konradjg at gmail.com' HASH(0xbc47634)) thr#98c2518
<- bind_param=3D 1 at DBI.pm line 984
-> execute for DBD::mysql::st (DBI::st=3DHASH(0xbbb4444)~0xb97c650) thr=
#98c2518
-> dbd_st_execute for 0bbba9e8
>- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 40120
Binding parameters: SELECT me.id, me.user_id, me.parent_user_id, me.locale,=
me.invite_code, me.create_date, me.create_datetime, me.register_date, me.t=
ime_zone, me.pm_id, me.current_step, me.country_code, me.recognition_cookie=
, me.validated, me.partner_id, me.password, me.remote_ip, me.email, me.emai=
l_optin_notification, me.email_optin_reminder, me.status, me.click_id, me.m=
otivator_id, me.action_requirement_id, me.page_flow_id, me.user_type, me.tr=
acking_cookie, me.can_redeem, me.flow_id, me.tainted, me.pcp_version FROM u=
sers me WHERE ( email =3D 'konradjg at gmail.com' )
--> do_error
Lost connection to MySQL server during query error 2013 recorded: Lost conn=
ection to MySQL server during query
<-- do_error
IGNORING ERROR errno 0
<- dbd_st_execute returning imp_sth->row_num 18446744073709551614
!! ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- execute=3D undef at DBI.pm line 990
1 -> FETCH for DBD::mysql::st (DBI::st=3DHASH(0xb97c650)~INNER 'ParamValu=
es') thr#98c2518
-> dbd_st_FETCH_attrib for 0bbba9e8, key ParamValues
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
1 <- FETCH=3D HASH(0xbc476d0)1keys at DBI.pm line 990
-> HandleError on DBI::st=3DHASH(0xb97c650) via CODE(0xb950c08) (undef)
DBIx::Storage::DBI->connected()...
-> FETCH for DBD::mysql::db (DBI::db=3DHASH(0xb773104)~0xa497548 'Activ=
e') thr#98c2518
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- FETCH=3D 1 at DBI.pm line 698
-> ping for DBD::mysql::db (DBI::db=3DHASH(0xb773104)~0xa497548) thr#98=
c2518
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- ping=3D '' at DBI.pm line 698
DBI::db=3DHASH(0xbc2d96c) trace level set to 0x0/2 (DBI @ 0x0/0) in DBI=
1.601-ithread (pid 7479)
<> FETCH=3D 2 ('TraceLevel' from cache) at DBI.pm line 694
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'Username'=
'kRad') thr#98c2518
<- STORE=3D 1 at DBI.pm line 694
<> FETCH=3D 'kRad' ('Username' from cache) at DBI.pm line 694
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'mysql_aut=
o_reconnect' '0') thr#98c2518
<- STORE=3D 1 at DBI.pm line 694
-> FETCH for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'mysql_aut=
o_reconnect') thr#98c2518
<- FETCH=3D 0 at DBI.pm line 694
-> connected in DBD::_::db for DBD::mysql::db (DBI::db=3DHASH(0xbc2d99c=
)~0xbc2d96c 'dbi:mysql(TraceLevel=3D2):yungberg_foobar_com:soma.foobar.com'=
'kRad' 'x8029A' HASH(0xa484c34)) thr#98c2518
<- connected=3D undef at DBI.pm line 700
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'dbi_conne=
ct_closure' CODE(0xbc47784)) thr#98c2518
<- STORE=3D 1 at DBI.pm line 709
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'HandleErr=
or' CODE(0xbc2fbe4)) thr#98c2518
<- STORE=3D 1 at DBI.pm line 834
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'ShowError=
Statement' 1) thr#98c2518
<- STORE=3D 1 at DBI.pm line 835
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'RaiseErro=
r' 1) thr#98c2518
<- STORE=3D 1 at DBI.pm line 836
-> STORE for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'PrintErro=
r' 0) thr#98c2518
<- STORE=3D 1 at DBI.pm line 837
-> FETCH for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'AutoCommi=
t') thr#98c2518
<- FETCH=3D 1 at Grouped.pm line 270
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbcb4cd0)~INNER) thr#98c=
2518
Freeing 1 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc7340c)~INNER) thr#98c=
2518
Freeing 1 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc54c1c)~INNER) thr#98c=
2518
Freeing 1 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc54cc4)~INNER) thr#98c=
2518
Freeing 2 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc555a0)~INNER) thr#98c=
2518
Freeing 2 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc0af70)~INNER) thr#98c=
2518
Freeing 2 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xb97c650)~INNER) thr#98c=
2518
Freeing 1 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc3c7b8)~INNER) thr#98c=
2518
Freeing 1 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbcba698)~INNER) thr#98c=
2518
Freeing 4 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc79178)~INNER) thr#98c=
2518
Freeing 3 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::st (DBI::st=3DHASH(0xbc54ae4)~INNER) thr#98c=
2518
Freeing 2 parameters, bind 0 fbind 0
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
-> DESTROY for DBD::mysql::db (DBI::db=3DHASH(0xa497548)~INNER) thr#98c=
2518
&imp_dbh->mysql: b7324c4
ERROR: 2013 'Lost connection to MySQL server during query' (err#0)
<- DESTROY=3D undef at DBI.pm line 760
SELECT me.id, me.user_id, me.parent_user_id, me.locale, me.invite_code, me.=
create_date, me.create_datetime, me.register_date, me.time_zone, me.pm_id, =
me.current_step, me.country_code, me.recognition_cookie, me.validated, me.p=
artner_id, me.password, me.remote_ip, me.email, me.email_optin_notification=
, me.email_optin_reminder, me.status, me.click_id, me.motivator_id, me.acti=
on_requirement_id, me.page_flow_id, me.user_type, me.tracking_cookie, me.ca=
n_redeem, me.flow_id, me.tainted, me.pcp_version FROM users me WHERE ( emai=
l =3D ? ): 'konradjg at gmail.com'
-> prepare_cached for DBD::mysql::db (DBI::db=3DHASH(0xbc2d99c)~0xbc2d9=
6c 'SELECT me.id, me.user_id, me.parent_user_id, me.locale, me.invite_code,=
me.create_date, me.create_datetime, me.register_date, me.time_zone, me.pm_=
id, me.current_step, me.country_code, me.recognition_cookie, me.validated, =
me.partner_id, me.password, me.remote_ip, me.email, me.email_optin_notifica=
tion, me.email_optin_reminder, me.status, me.click_id, me.motivator_id, me.=
action_requirement_id, m...' HASH(0xb7311f4) 3) thr#98c2518
1 -> prepare for DBD::mysql::db (DBI::db=3DHASH(0xbc2d96c)~INNER 'SELECT =
me.id, me.user_id, me.parent_user_id, me.locale, me.invite_code, me.create_=
date, me.create_datetime, me.register_date, me.time_zone, me.pm_id, me.curr=
ent_step, me.country_code, me.recognition_cookie, me.validated, me.partner_=
id, me.password, me.remote_ip, me.email, me.email_optin_notification, me.em=
ail_optin_reminder, me.status, me.click_id, me.motivator_id, me.action_requ=
irement_id, m...' HASH(0xb7311f4)) thr#98c2518
-> dbd_st_prepare MYSQL_VERSION_ID 40120
>- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
<- dbd_st_prepare
1 <- prepare=3D DBI::st=3DHASH(0xbc55180) at DBI.pm line 1654
<- prepare_cached=3D DBI::st=3DHASH(0xbc55180) at DBI.pm line 1168
-> bind_param for DBD::mysql::st (DBI::st=3DHASH(0xbc55180)~0x96016c8 1=
'konradjg at gmail.com' HASH(0xb7311dc)) thr#98c2518
<- bind_param=3D 1 at DBI.pm line 984
-> execute for DBD::mysql::st (DBI::st=3DHASH(0xbc55180)~0x96016c8) thr=
#98c2518
-> dbd_st_execute for 0bc79cc4
>- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 40120
Binding parameters: SELECT me.id, me.user_id, me.parent_user_id, me.locale,=
me.invite_code, me.create_date, me.create_datetime, me.register_date, me.t=
ime_zone, me.pm_id, me.current_step, me.country_code, me.recognition_cookie=
, me.validated, me.partner_id, me.password, me.remote_ip, me.email, me.emai=
l_optin_notification, me.email_optin_reminder, me.status, me.click_id, me.m=
otivator_id, me.action_requirement_id, me.page_flow_id, me.user_type, me.tr=
acking_cookie, me.can_redeem, me.flow_id, me.tainted, me.pcp_version FROM u=
sers me WHERE ( email =3D 'konradjg at gmail.com' )
<- dbd_st_execute returning imp_sth->row_num 1
<- execute=3D 1 at DBI.pm line 990
-> fetchrow_array for DBD::mysql::st (DBI::st=3DHASH(0xbc55180)~0x96016=
c8) thr#98c2518
-> dbd_st_fetch
dbd_st_fetch for 0bc79cc4, chopblanks 0
dbd_st_fetch result set details
imp_sth->result=3D0bc52448
mysql_num_fields=3D31
mysql_num_rows=3D1
mysql_affected_rows=3D1
dbd_st_fetch for 0bc79cc4, currow=3D 1
<- dbd_st_fetch, 31 cols
<- fetchrow_array=3D ( '17' '244' undef 'en' undef '2007-11-12' '2007-1=
1-12 07:31:10' undef undef '8b7cb96a7c2e43419987' '10' 'US' '8b7cb96a-7c2e-=
4341-9987-34ea08d773c4' '0' '0' 'shebarim-hpmuhmot' '10.101.12.146' 'konrad=
jg at gmail.com' '1' '1' '1' undef '20' '20' '31' '#non-viral' 'af4d80a04d2d48=
539e67' undef '884' '0' '2.20.4.3312' ) [31 items] row1 at Cursor.pm line 85
-> FETCH for DBD::mysql::st (DBI::st=3DHASH(0x96016c8)~INNER 'Active') =
thr#98c2518
-> dbd_st_FETCH_attrib for 0bc79cc4, key Active
<- FETCH=3D 1 at Cursor.pm line 171
-> finish for DBD::mysql::st (DBI::st=3DHASH(0xbc55180)~0x96016c8) thr#=
98c2518
--> dbd_st_finish
>- dbd_st_free_result_sets
<- dbd_st_free_result_sets RC -1
<- dbd_st_free_result_sets
<-- dbd_st_finish
<- finish=3D 1 at Cursor.pm line 171
[ ** NOTE ** 2ND FAILING QUERY ]
SELECT me.id, me.code, me.current_page_type_id, me.next_page_type_id FROM =
page_types me: =
DBIx::Storage::DBI->connected()...
SELECT me.id, me.definition FROM flows me WHERE ( program_id =3D ? ): '10'
SELECT me.id, me.status, me.pausable_id, me.pausable_type, me.created_at FR=
OM status me WHERE ( pausable_id =3D ? AND pausable_type =3D ? ) ORDER BY c=
reated_at desc: '471', 'Flow'
paused
more queries
....
....
....
Catalyst catches exception: DBI Exception: DBD::mysql::st execute failed: L=
ost connection to MySQL server during query [for Statement "SELECT me.id, m=
e.code, me.current_page_type_id,
More information about the DBIx-Class
mailing list