[Catalyst] MySQL server has gone away

[Peter Edwards]

I've seen this problem under Red Hat Enterprise Linux 4 and Mysql 3.23 and
suspect it was down to incorrect configuration of full/half duplex on the
Network Interface Card against the router, possibly combined with a Linux
Ethernet driver bug. Some useful pointers here
http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

We put in code to wrap the DBI calls, detect the error and re-run the
transactions in that case. You can detect it with $sth->execute() fails and
$sth->errstr() =~ m/gone away/i

For lookups we use tie() through a locking wrapper to DB_File, generating
the DBM databases from master data in the database.

Regards, Peter
Dragonstaff Limited  http://www.dragonstaff.com 

>     Oleg Pronin wrote:
>     > One can say that it is a very rare situation. I can tell you that in
>     > production environment (about 1.000.000 hits per day) it happens
>     every
>     > day several times. Some users just see an error, some lost their
>     data.
>     > This is just not always visible to you.
>     The OP may find a requirement to incorporate a transaction model
>     (explicit start txn/commit/rollback) to solve this problem in MySQL;
>     which is what they'd have to do for Oracle or Postgres or Rdb. I'd
>     hope
>     that MySQL reports a transaction failure when the database connection
>     evaporates. This would allow a retry on the failed transaction.
>     However,
>     it may be that the Catalyst Controller / View design/implementation is
>     only what would ordinarily be flat file accesses that use SQL
>     instead of
>     keyed-access flat files. If that's the case, wrapping a transaction
>     around each of these SQL prepare/execute pairs will be a substantial
>     performance hit.