XF 1.4 Recommanded value for sql "wait_timeout" & "max_allowed_packet" ?

[cclaerhout]

After having some sql lost connection in some parts of XenForo, I've wasted my day to ask my host to check the values of "wait_timeout" & "max_allowed_packet". I've been told the problems came from by db, so I check table by table and the issue comes from the table "xf_style_property_definition" which can't be read or dumped. It triggers instead this error:

#2013 - Lost connection to MySQL server during query
(...)
<!-- PMA-SQL-ERROR 
(...)
#2006 - MySQL server has gone away

A fast Internet search gives some websites [ref] which recommend to increase the value of the "wait_timeout"

Can I have a reference of this value to provide my host and close this ticket?

Thanks

 

[Mike]

That would likely be max_allowed_packet. Depending on how you're dumping, you can set it to make sure that no single query goes longer than that (assuming there isn't binary data stored in a row that's longer than that).

Regardless, setting it to something like 16M is probably sufficient.

 

[cclaerhout]

Thanks a lot, but I was wrong:

"The table you're having issues dumping is not due to any issue with MySQL settings or the server. Instead, it's simply that the table is sufficiently corrupted such that its data is not accessible to MySQL. You can further confirm this as mysqldump's initial error is that it can't acquire a lock. When you set it to skip attempting a lock, it then fails to complete its attempted query."

"InnoDB is an excellent storage engine and its high performance and row-level locking can actually aid in preventing tables from corrupting. However, it has limited fallback for when something does go wrong. It can be a single poorly-formatted query or less and a table can corrupt. InnoDB fights against this by maintaining transactional logs which it can use to "rollback" changes that caused issues on the next MySQL restart. We keep the largest size of these logs that MySQL 5.5 allows us to, giving our server the best chance possible to contain an afflicted transaction when MySQL restarts to correct corruption. However, some issues cannot be rolled back, and at other times the point of rollback may simply roll off the transaction log before it can be addressed."

Then I have to recreate this table dumping XenForo default table then reinstalling addons