MySQL Dump Error

xlegends

Member
Hi guys,

So I have automatic hourly backups via mysqldump.

Since switching over to XF2 from vb4, I notice at the moment of mysql dump cron, the forum freezes for about 15-20 seconds.


After a month of this today it finally began to fail all together producing errors

Code:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `
It looks like its exhausting memory when I have plenty of disk and ram available.


I did a complete system reboot and it resolved the dump error.

Now it successfully dumps again but I would like to fix the freeze during dump issue.




I have a plain default my.cnf config file

centos7/php72/mysqld57

/etc/my.cnf

Code:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


I added to it

Code:
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

It didnt help. Although thankfully the dump errors havent returned. The freezes during dumps still exists.

Any suggestions?
 

xlegends

Member
urrrgh now im getting this running Rebuild User Caches

Code:
An exception occurred: [XF\Db\Exception] MySQL statement prepare error [2006]: MySQL server has gone away in src/XF/Db/AbstractStatement.php on line 228

XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 39
XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 54
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1261
XF\Mvc\Entity\Finder->fetchOne() in src/XF/Repository/UpgradeCheck.php at line 39
XF\Repository\UpgradeCheck->getLatestUpgradeCheck() in src/XF/Admin/App.php at line 238
XF\Admin\App->renderPageHtml() in src/XF/App.php at line 1995
XF\App->renderPage() in src/XF/Admin/App.php at line 122
XF\Admin\App->renderPage() in src/XF/Mvc/Dispatcher.php at line 402
XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2184
XF\App->run() in src/XF.php at line 391
XF::runApp() in admin.php at line 13

and my dump errors are back

Code:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `xf_post` at row: 1153253

a system reboot corrected it once again for now.
 
Last edited:

xlegends

Member
I finally caught what its doing at the time it freezes and error produces

free -g

available memory: 0

This server has 16gb, I guess I can upgrade memory but any other point other than dumps only, theres always 9-10 gb available and a full swap.
 
Top