[rt-users] Problems with RT3 losing MySQL connection

[Derek Suzuki]

 I am running RT 3.0.3 under FastCGI with Apache 1.3.27 and MySQL 4.0.13 on
Red Hat 7.3.  I basically have everything working okay, but if I leave the
system alone overnight and try to access it again in the morning, I get the
following error:

RT Couldn't write to session directory '/usr/local/rt/var/session_data':
DBD::mysql::st execute failed: MySQL server has gone away at
/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm line 54.

Stack:
  [/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm:54]
  [/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm:60]
  [/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:569]
  [/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:497]
  [/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:462]
  [/usr/local/rt/share/html/Elements/SetupSessionCookie:45]
  [/usr/local/rt/share/html/autohandler:51]
. Check that this dir ectory's permissions are correct. at
/usr/local/rt/share/html/Elements/SetupSessionCookie line 60.


Trace begun at /usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Exceptions.pm line
128
HTML::Mason::Exceptions::rethrow_exception('RT Couldn\'t write to session
directory \'/usr/local/rt/var/session_data\': DBD::mysql::st execute failed:
MySQL server has gone away at
/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm line
54.^J^JStack:^J
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm:54]^J
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Lock/MySQL.pm:60]^J
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:569]^J
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:497]^J
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:462]^J
[/usr/local/rt/share/html/Elements/SetupSessionCookie:45]^J
[/usr/local/rt/share/html/autohandler:51]^J. Check that this dir ectory\'s
permissions are correct. at
/usr/local/rt/share/html/Elements/SetupSessionCookie line 60.^J') called at
/usr/local/rt/share/html/Elements/SetupSessionCookie line 60
HTML::Mason::Commands::__ANON__ at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Component.pm line 134
HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x900848
0)') called at /usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line
1056
eval {...}('HTML::Mason::Component::FileBased=HASH(0x9008480)') called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line 1050
HTML::Mason::Request::comp(undef, undef) called at
/usr/local/rt/share/html/autohandler line 51
HTML::Mason::Commands::__ANON__ at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Component.pm line 134
HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x8fe125
c)') called at /usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line
1056
eval {...}('HTML::Mason::Component::FileBased=HASH(0x8fe125c)') called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line 1050
HTML::Mason::Request::comp(undef, undef, undef) called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line 333
eval {...}(undef, undef, undef) called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line 333
eval {...}(undef, undef, undef) called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Request.pm line 290
HTML::Mason::Request::exec('HTML::Mason::Request::CGI=HASH(0xa403b00)')
called at /usr/lib/perl5/site_perl/5.6.1/HTML/Mason/Interp.pm line 225
HTML::Mason::Interp::exec(undef, undef) called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/CGIHandler.pm line 87
HTML::Mason::CGIHandler::_handler('HTML::Mason::CGIHandler=HASH(0x8e9e494)',
'HASH(0xa3df214)') called at
/usr/lib/perl5/site_perl/5.6.1/HTML/Mason/CGIHandler.pm line 70
HTML::Mason::CGIHandler::handle_cgi_object('HTML::Mason::CGIHandler=HASH(0x8
e9e494)', 'CGI::Fast=HASH(0xa18b0a4)') called at
/usr/local/rt/bin/mason_handler.fcgi line 50


 Now, my understanding is that the "MySQL server has gone away" error is
caused by the database connection timing out after eight hours of
inactivity.  It looks like SetupSessionCookie has no understanding of this
particular error condition, and is misreporting the result as a failure to
write the unused session directory.  I can cause this error to occur
immediately if I restart MySQL without restarting Apache.
 As a workaround, I tried Set($WebSessionClass , 'Apache::Session::File') in
order to keep session data in the filesystem instead of the database.  This
got the sessions working again, but if the database connection was lost I
ended up with most of the content missing from the RT web display.
 Is there a robust workaround for this problem?  Either a keepalive, or
(preferably) a reliable way to re-establish a dropped connection on the fly?

Thanks,
Derek