Re: DBD::mysql::st execute failed: Lost connection to MySQL server during query

[Philip Johnson]

It took a while, but I optimized all the tables. Because I could not afford for the tables to lock for minutes+ during the optimize, what I did was copy the data out of each table into a temporary holder table, remove the copied rows, optimize the now tiny table, then insert all the rows back in. A pretty inefficient replication of how table optimization itself usually works, but this way the table was locked for at most a few seconds at a time rather then (especially in the case of Inbound, 13.6 GB table) locking for somewhere between minutes and hours.

There hasn't really been any churn on this table, pretty much all the work (both in terms of reads and writes) all occurs at the very newest few pages of the table, and all the rest is kept simply for historical reasons and isn't even queried by any automated processes. Only unprocessed rows (status = 'n', not 'S') and inserting of rows are normal functions.

I did notice during the some other optimizes something a little odd. It looked like every time Router had to wait for a lock (for example, optimizing a smaller table which router tried to perform an insert on), instead of waiting for the lock router instead died with the "Lost connection" error. Looking into that a little further, it makes me wonder if perhaps innodb thought that it had entered a deadlock state, and terminated the transaction with the router script. I say this because the termination was immediate, and innodb does have realtime deadlock detection. I don't see how there could have truly been a deadlock (optimizing a table only locks that table, i would assume), but maybe innodb is thinking this particular script is deadlocking whenever it has to wait for a lock?
I should note that other scripts waiting for a lock on the same table were patiently waiting for the lock to be availible, only router was getting instantly killed.