MySQL backups of RT 4.4.1 truncated (Cena, Stephen (ext. 300))

RT Users
1

Small update: I was able to fix the system errors for logging. While the err.log doesn’t contain much, there were two telling entries:

2017-01-26T01:12:29.975366Z 1622 [Note] Aborted connection 1622 to db: ‘rt_service’ user: ‘backupdb’ host: ‘localhost’ (Got an error writing communication packets)
2017-01-26T01:24:16.284038Z 1628 [Note] Aborted connection 1628 to db: ‘rt_service’ user: ‘backupdb’ host: ‘localhost’ (Got an error writing communication packets)

That’s my MySQL backup account which only is used to dump the files and that’s my affected database.

I’ve got an active MySQL ‘support request’ out there: https://forums.mysql.com/read.php?28,654543 , I’ll be adding this shortly.

Something is definitely disrupting the connection.

Message: 2Date: Wed, 25 Jan 2017 12:28:10 +0000
From: “Cena, Stephen (ext. 300)” SJC@qvii.com
To: “rt-users@lists.bestpractical.com”
rt-users@lists.bestpractical.com
Subject: Re: [rt-users] MySQL backups of RT 4.4.1 truncated
Message-ID:
87F81E27495DC8489147E34A4152E268A49666CA@MailStore2010.ogp.qvii.com
Content-Type: text/plain; charset=“us-ascii”

Update on my MySQL backup issue: no change. I’ve now moved the Windows TEMP folders to the larger drive to see if the issue was running out of temp space and it has not helped. I will keep the group updated to any developments, but will refrain if its not anything noteworthy. I’m going to try contacting the MySQL forums as this looks to be more suited to them. I’m also going to see if I can spin up a Linux MySQL DB server and “move” the tables into it via SFTP/copy, then attempt a backup there. I’m good with MySQL but it’s not my bread & butter. Any additional help from the group will be welcomed.

To date, I’ve tried:
-Splitting the attachments table into its own backup job: failed.
-Moved MySQL temp folder/location to larger drive: failed.
-Moved Windows 2012 system TEMP folders to larger drive: failed.
-Ran MySQL diagnostics on the RT database affected: all tests pass

Stephen Cena
Senior Systems Administrator
Quality Vision International, Inc.
Phone: (585) 544-0450 x300
To notify helpdesk: http://helpdesk.ogp.qvii.com or email: hd-general@qvii.com
To report email issues: postmaster@qvii.com

2

It looks like I’ve found the problems. To recap: I’ve got MySQL Server 5.7 on a Windows Server 2012 host running an RT database of ~20GB. My mysqldump backups started becoming truncated for some reason.

I’m not sure how much of this is relevant to Windows, but it turns out this is what was happening:
Using the --results-file= instead of > was masking a “max_allowed_packet” problem. Once I switched my backup scripts to use the redirect it appeared. The backup is still crashing, but the size is larger now. I’m tweaking my max_allowed_packet size to get it stable.

Also, I saw a note that if you’re dumping data to the local host that using “shared memory” is faster than a TCP connection to local host. I’m tempted to try it but if anyone has any experience there I’d appreciate it.

Thank you to everyone who helped debug this with me. I hope this helps someone in the future!

Stephen Cena
Senior Systems Administrator
Quality Vision International, Inc.
Phone: (585) 544-0450 x300
To notify helpdesk: http://helpdesk.ogp.qvii.com or email: hd-general@qvii.com
To report email issues: postmaster@qvii.com