Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Thomas Bätzler]

Hi Stephen,

> It's fixed!!! Recap: large RT database (host Ubuntu 14.04LTS, database
server
> MySQL 5.7.x on Windows Server 2012). Backup .sql files became truncated at
> ~18GB when the database is easily 20GB.

Good to hear. Does your my.cnf file not have a "[mysqldump]" section? That's
where mysqldump looks for its settings.
If you run "mysqldump --help", it'll tell you where it looks for that ini
file, and further down it'll tell you what it's actually using
settings-wise. 
Oh, and make sure that you only have one of those ini files, so that one
doesn't overwrite the settings in the other ;-)


MfG,
Thomas Bätzler
-- 
BRINGE Informationstechnik GmbH
Zur Seeplatte 12
D-76228 Karlsruhe
Germany

Fon: +49 721 94246-0
Fon: +49 171 5438457
Fax: +49 721 94246-66
Web: http://www.bringe.de/

Geschäftsführer: Dipl.-Ing. (FH) Martin Bringe
Ust.Id: DE812936645, HRB 108943 Mannheim


smime.p7s
Description: S/MIME cryptographic signature

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

It's fixed!!! Recap: large RT database (host Ubuntu 14.04LTS, database server 
MySQL 5.7.x on Windows Server 2012). Backup .sql files became truncated at 
~18GB when the database is easily 20GB.

It looks like (for whatever reason) the max_allowed_packet option is not 
carrying over to the backup scripts. I re-wrote my backup options as:

-u USER -h HOST --max_allowed_packet=512M --single_transaction > output.sql

That solved it.

And I get a full backup of the file. Probably my own fault, but wanted to share 
the information. I'm now getting 19.5GB+ backups. Thanks to everyone!

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

The problem has become more interested. Switching from --results-file to > 
exposed the max packet problem but now, I can't seem to get around it. I 
currently have my max_allowed_packet set to 1024M and it's still dying. I have 
the approximate row & attachment ID where it's failing and if I'm reading it 
right it's a 110K PDF file. I tried upping it to 2048M but apparently that 
isn't allowed as the value in MySQL doesn't go any higher.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com
Message: 11
Date: Thu, 26 Jan 2017 14:05:27 +
From: "Cena, Stephen (ext. 300)" 

Re: [rt-users] MySQL backups of RT 4.4.1 truncated (Cena, Stephen (ext. 300))

[Cena, Stephen (ext. 300)]

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

Re: [rt-users] MySQL backups of RT 4.4.1 truncated (Cena, Stephen (ext. 300))

[Cena, Stephen (ext. 300)]

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: 2
Date: Wed, 25 Jan 2017 12:28:10 +
From: "Cena, Stephen (ext. 300)" <s...@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:
<87f81e27495dc8489147e34a4152e268a4966...@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-gene...@qvii.com
To report email issues: postmas...@qvii.com

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

Excellent point. The MySQL TEMP folder was moved, but not the operating system 
ones. I'll see if I can move the OS temp locations to the larger drive today & 
see what that gets me. I've just gotten the last 30 lines of the "attachments" 
table .sql file so I'm checking that to see if maybe there's anything in the 
text that might cause a stop. Still looking into this one guys. Thank you for 
all who have answered so far! Will update as soon as I know more.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

-Original Message-
From: Jeffrey Pilant [mailto:jeffrey.pilant@bayer.com] 
Sent: Monday, January 23, 2017 5:59 PM
To: Cena, Stephen (ext. 300) 
Subject: RE: MySQL backups of RT 4.4.1 truncated

There are temp files and temp files.  The files you moved may not be the ones 
you need to move.  You might try observing the free space as the backup 
proceeds and see what it is filling with.  That might give you a clue of which 
set of temp files need moved.  I.e. rt vs. web vs. MySQL vs. System vs. 
whatever.  Each may define temp files in a different way.  Config file vs. 
registry variable vs. environment variable vs. different environment variable 
vs. command line parameter vs. whatever.  We programmers can get creative.

As to the question you asked about indexing, I don't know.  That should be 
asked to the list at large.

/jeff

Freundliche Grüße / Best regards,

Jeffrey Pilant

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

Update: I tried increasing the database permissions on the account I'm using to 
run the backup but that didn't change anything. I don't think it's a file size 
limitation as before they stopped working, my backups were reaching 19GB. When 
I look at the actual database file on the server for the attachments table its 
19.7GB on disk. I'm going to see if there is a way to turn on any additional 
logging during operation/backup but this really has me scratching my head. Any 
additional help would be appreciated & I will update the group as soon as I 
know something new.

Message: 1
Date: Fri, 20 Jan 2017 14:04:04 +
From: "Cena, Stephen (ext. 300)" <s...@qvii.com>
To: 'Jeffrey Pilant' <jeffrey.pilant@bayer.com>,
"'rt-users@lists.bestpractical.com'"
<rt-users@lists.bestpractical.com>
Subject: Re: [rt-users] MySQL backups of RT 4.4.1 truncated
Message-ID:
<87f81e27495dc8489147e34a4152e268a4962...@mailstore2010.ogp.qvii.com>
Content-Type: text/plain; charset="us-ascii"

All the backups I'm doing are local to keep performance high. I sadly lost the 
"discussion" to keep the MySQL server on a Linux machine. What we're going to 
try is the following:
We're snapshotting/backing up the system at regular intervals so I will always 
have a backup of the .sql files. I'm going to move the MySQL TMP folder to the 
larger drive to see if that solves the issue. I think you might be on to 
something with the drive issue as it looks to be the only consistent factor. 
I'll update as I know more.

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-gene...@qvii.com 
To report email issues: postmas...@qvii.com

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

All the backups I'm doing are local to keep performance high. I sadly lost the 
"discussion" to keep the MySQL server on a Linux machine. What we're going to 
try is the following:
We're snapshotting/backing up the system at regular intervals so I will always 
have a backup of the .sql files. I'm going to move the MySQL TMP folder to the 
larger drive to see if that solves the issue. I think you might be on to 
something with the drive issue as it looks to be the only consistent factor. 
I'll update as I know more.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

-Original Message-
From: Jeffrey Pilant [mailto:jeffrey.pilant@bayer.com] 
Sent: Friday, January 20, 2017 8:54 AM
To: Cena, Stephen (ext. 300) 
Subject: RE: MySQL backups of RT 4.4.1 truncated

Just FYI:
When windows copies files between drives, I believe it copies them to a temp 
area first before copying the temp area to the destination.
It probably does this to avoid writing a partial file when running out of 
space.  Because it causes a doubling of data transfer, it is stupid, but then, 
it is Microsoft Windows.

If your backup process includes copying the result across drives, this could be 
the source of the problem.

/jeff

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

I just looked into this now. Got a little swamped yesterday. It looks like the 
system is using the OS drive which barely has 19GB free so it's possible that's 
what's tripping it up. I checked the error log & the only errors I'm seeing are 
dropped packets from my RT systems around the time of the backup (which would 
make sense if they aren't stopped). I have a 20GB partition that I can use to 
move the temp tiles to. I'm also going to talk to our VM admin about getting a 
"scratch drive" added. I'm running another PowerShell "grep" on my attachments 
backup file as the sizes from the 18th and the 19th are identical. With the 
volume we do, I don't see how that's possible. The "everything else" .sql file 
shows expected growth.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

-Original Message-
From: Jeffrey Pilant [mailto:jeffrey.pilant@bayer.com] 
Sent: Thursday, January 19, 2017 2:16 PM
To: Cena, Stephen (ext. 300) 
Subject: RE: MySQL backups of RT 4.4.1 truncated

Folder compression can slow things down, but should not cause an issue.

What about temp/scratch space?  If you are making temp files on a different 
disk that is low in space, failure there could cause the problem.  Check the 
free space of all accessible drives, and check where temp files go.

/jeff

-Original Message-
Jeff - No limit that I'm aware of. If I look at a VM backup in December, the 
SQL files actually hit 19GB+ versus the "18 and change". The only other 
possibility that it COULD be is I've had to turn on Windows folder compression 
because the backups are getting so large their filling the drive. I could 
temporarily disable it to see if that is factoring into it, but I've bene using 
it all along without issue. The real kicker is I didn't notice the issue soon 
enough  so I'm having difficulty identifying what specifically went wrong.

Stephen Cena


The information contained in this e-mail is for the exclusive use of the 
intended recipient(s) and may be confidential, proprietary, and/or legally 
privileged.  Inadvertent disclosure of this message does not constitute a 
waiver of any privilege.  If you receive this message in error, please do not 
directly or indirectly use, print, copy, forward, or disclose any part of this 
message.  Please also delete this e-mail and all copies and notify the sender.  
Thank you. 

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

Jeff - No limit that I'm aware of. If I look at a VM backup in December, the 
SQL files actually hit 19GB+ versus the "18 and change". The only other 
possibility that it COULD be is I've had to turn on Windows folder compression 
because the backups are getting so large their filling the drive. I could 
temporarily disable it to see if that is factoring into it, but I've bene using 
it all along without issue. The real kicker is I didn't notice the issue soon 
enough  so I'm having difficulty identifying what specifically went wrong.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

-Original Message-
From: Jeffrey Pilant [mailto:jeffrey.pilant@bayer.com] 
Sent: Thursday, January 19, 2017 1:27 PM
To: Cena, Stephen (ext. 300) 
Subject: RE: MySQL backups of RT 4.4.1 truncated

Another stab in the dark.  Is the destination file of the backup hitting a size 
boundary?  What size is it?  Anything close to a power of 2 would be 
suspicious.  Also, free space on the destination and on the temp/scratch areas 
need to be large enough.

/jeff

The information contained in this e-mail is for the exclusive use of the 
intended recipient(s) and may be confidential, proprietary, and/or legally 
privileged.  Inadvertent disclosure of this message does not constitute a 
waiver of any privilege.  If you receive this message in error, please do not 
directly or indirectly use, print, copy, forward, or disclose any part of this 
message.  Please also delete this e-mail and all copies and notify the sender.  
Thank you. 

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

I've done as you requested. It looks like I'm hovering around 31MB if I'm 
reading it properly: 30828544 . Should I increase the packet size anyways? If I 
split my backups into two pieces they appear to run properly. Both resulting 
.sql files dhow the "dump complete" text at the end.

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-gene...@qvii.com
To report email issues: postmas...@qvii.com

-Original Message-
From: Thomas Bätzler [mailto:t.baetz...@bringe.com] 
Sent: Thursday, January 19, 2017 8:36 AM
To: Cena, Stephen (ext. 300) <s...@qvii.com>; 
'rt-users@lists.bestpractical.com' <rt-users@lists.bestpractical.com>
Subject: Re: [rt-users] MySQL backups of RT 4.4.1 truncated

Hi Stephen,

Am 19.01.2017 um 13:33 schrieb Cena, Stephen (ext. 300):
> I had that issue initially, and changed the value to 64MB. It was 
> originally 4MB, then I increased it to 16MB, it's been at 64MB for 
> some time.

You can check your RT database to see whether 64MB is enough by running

select max(OCTET_LENGTH(Content)) from Attachments;

in the context for your RT4 database. If that query comes up with anything 
above 48MB, I'd raise that variable to a suitably higher value.


MfG,
Thomas Bätzler
--
BRINGE Informationstechnik GmbH
Zur Seeplatte 12
D-76228 Karlsruhe
Germany

Fon: +49 721 94246-0
Fon: +49 171 5438457
Fax: +49 721 94246-66
Web: http://www.bringe.de/

Geschäftsführer: Dipl.-Ing. (FH) Martin Bringe
Ust.Id: DE812936645, HRB 108943 Mannheim

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Thomas Bätzler]

Hi Stephen,

Am 19.01.2017 um 13:33 schrieb Cena, Stephen (ext. 300):
> I had that issue initially, and changed the value to 64MB. It was
> originally 4MB, then I increased it to 16MB, it’s been at 64MB for some
> time.

You can check your RT database to see whether 64MB is enough by running

select max(OCTET_LENGTH(Content)) from Attachments;

in the context for your RT4 database. If that query comes up with
anything above 48MB, I'd raise that variable to a suitably higher value.


MfG,
Thomas Bätzler
-- 
BRINGE Informationstechnik GmbH
Zur Seeplatte 12
D-76228 Karlsruhe
Germany

Fon: +49 721 94246-0
Fon: +49 171 5438457
Fax: +49 721 94246-66
Web: http://www.bringe.de/

Geschäftsführer: Dipl.-Ing. (FH) Martin Bringe
Ust.Id: DE812936645, HRB 108943 Mannheim

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

I had that issue initially, and changed the value to 64MB. It was originally 
4MB, then I increased it to 16MB, it's been at 64MB for some time.

# The maximum size of one packet or any generated or intermediate string, or 
any parameter sent by the
# mysql_stmt_send_long_data() C API function.
#max_allowed_packet=4M
#max_allowed_packet=16M
max_allowed_packet=64M

It's the oddest thing. The backups were working perfectly through most of 
December. Sometime between the week of Christmas and now it seems to have 
"blown up". As a work around right now, I've split the backup in two pieces: 
just attachments, and everything else. That appears to be working but I can't 
seem to explain why this has happened.

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-gene...@qvii.com<mailto:hd-gene...@qvii.com>
To report email issues: postmas...@qvii.com<mailto:postmas...@qvii.com>

From: Thomas Bätzler [mailto:t.baetz...@bringe.com]
Sent: Thursday, January 19, 2017 3:04 AM
To: 'rt-users@lists.bestpractical.com' <rt-users@lists.bestpractical.com>
Cc: Cena, Stephen (ext. 300) <s...@qvii.com>
Subject: AW: [rt-users] MySQL backups of RT 4.4.1 truncated

Hello Stephen,

just a stab in the dark, but could you please check the value set for 
"max_allowed_packet" in the mysqldump section of /etc/mysql/my.cnf (or whatever 
your my.cnf file is named)? If you try to dump mysql objects that are larger 
than this size, mysqldump will stop; probably with a message like "mysql server 
has gone away". You can raise that value for mysqldump just by editing that 
file; no server restart is required.

HTH,
Thomas Bätzler
--
BRINGE Informationstechnik GmbH
Zur Seeplatte 12
D-76228 Karlsruhe
Germany

Fon: +49 721 94246-0
Fon: +49 171 5438457
Fax: +49 721 94246-66
Web: http://www.bringe.de/

Geschäftsführer: Dipl.-Ing. (FH) Martin Bringe
Ust.Id: DE812936645, HRB 108943 Mannheim

Von: rt-users [mailto:rt-users-boun...@lists.bestpractical.com] Im Auftrag von 
Cena, Stephen (ext. 300)
Gesendet: Mittwoch, 18. Januar 2017 18:23
An: 'rt-users@lists.bestpractical.com' 
<rt-users@lists.bestpractical.com<mailto:rt-users@lists.bestpractical.com>>
Betreff: Re: [rt-users] MySQL backups of RT 4.4.1 truncated

I just ran a test with the attachments table "ignored", and the backup worked 
perfectly. As a "sanity check", I also ran mysqlcheck on the schema & no errors 
were found. Should I split my backup job into two pieces, or is there a better 
way to get a backup all in one shot?


--



Message: 4

Date: Wed, 18 Jan 2017 13:54:57 +

From: "Cena, Stephen (ext. 300)" <s...@qvii.com<mailto:s...@qvii.com>>

To: "'rt-users@lists.bestpractical.com'"


<rt-users@lists.bestpractical.com<mailto:rt-users@lists.bestpractical.com>>

Subject: [rt-users] MySQL backups of RT 4.4.1 truncated

Message-ID:


<87f81e27495dc8489147e34a4152e268a4950...@mailstore2010.ogp.qvii.com<mailto:87f81e27495dc8489147e34a4152e268a4950...@mailstore2010.ogp.qvii.com>>

Content-Type: text/plain; charset="us-ascii"



I'm currently running RT 4.4.1 on Ubuntu 14.04 LTS with MySQL 5.6 on Windows 
Server 2012. Recently, we enabled full-text indexing in native MySQL & 
everything is great. After doing some work on the server over the weekend, I 
discovered that the .sql files being generated for the backup are being 
"truncated" after the attachments table (~18-19GB).  After reviewing your 
documentation (https://docs.bestpractical.com/rt/4.4.1/backups.html) I realized 
my mysqldump command was incorrect. I'm working in a lab environment now to see 
how I can optimize/change my script to more closely match yours. The only 
thought I have right now is to exclude the data from the AttachmentsIndex as 
that would get rebuilt on restoration anyways. Is there any other reason that 
the backup would just "stop" after the attachment table? I apologize if this is 
beyond the scope of the forums thread.



Thank you in advance!



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-gene...@qvii.com<mailto:hd-gene...@qvii.com<mailto:hd-gene...@qvii.com%3cmailto:hd-gene...@qvii.com>>

To report email issues: 
postmas...@qvii.com<mailto:postmas...@qvii.com<mailto:postmas...@qvii.com%3cmailto:postmas...@qvii.com>>

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-gene...@qvii.com<mailto:hd-gene...@qvii.com>
To report email issues: postmas...@qvii.com<mailto:postmas...@qvii.com>

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Thomas Bätzler]

Hello Stephen,

 

just a stab in the dark, but could you please check the value set for
“max_allowed_packet” in the mysqldump section of /etc/mysql/my.cnf (or
whatever your my.cnf file is named)? If you try to dump mysql objects that
are larger than this size, mysqldump will stop; probably with a message like
“mysql server has gone away”. You can raise that value for mysqldump just by
editing that file; no server restart is required. 

 

HTH,

Thomas Bätzler

-- 

BRINGE Informationstechnik GmbH

Zur Seeplatte 12

D-76228 Karlsruhe

Germany

 

Fon: +49 721 94246-0

Fon: +49 171 5438457

Fax: +49 721 94246-66

Web: http://www.bringe.de/

 

Geschäftsführer: Dipl.-Ing. (FH) Martin Bringe

Ust.Id: DE812936645, HRB 108943 Mannheim

 

Von: rt-users [mailto:rt-users-boun...@lists.bestpractical.com] Im Auftrag
von Cena, Stephen (ext. 300)
Gesendet: Mittwoch, 18. Januar 2017 18:23
An: 'rt-users@lists.bestpractical.com' <rt-users@lists.bestpractical.com>
Betreff: Re: [rt-users] MySQL backups of RT 4.4.1 truncated

 

I just ran a test with the attachments table “ignored”, and the backup
worked perfectly. As a “sanity check”, I also ran mysqlcheck on the schema &
no errors were found. Should I split my backup job into two pieces, or is
there a better way to get a backup all in one shot?

 

--

 

Message: 4

Date: Wed, 18 Jan 2017 13:54:57 +

From: "Cena, Stephen (ext. 300)" <s...@qvii.com <mailto:s...@qvii.com> >

To: "'rt-users@lists.bestpractical.com'"

<rt-users@lists.bestpractical.com
<mailto:rt-users@lists.bestpractical.com> >

Subject: [rt-users] MySQL backups of RT 4.4.1 truncated

Message-ID:

 
<87f81e27495dc8489147e34a4152e268a4950...@mailstore2010.ogp.qvii.com
<mailto:87f81e27495dc8489147e34a4152e268a4950...@mailstore2010.ogp.qvii.com>
>

Content-Type: text/plain; charset="us-ascii"

 

I'm currently running RT 4.4.1 on Ubuntu 14.04 LTS with MySQL 5.6 on Windows
Server 2012. Recently, we enabled full-text indexing in native MySQL &
everything is great. After doing some work on the server over the weekend, I
discovered that the .sql files being generated for the backup are being
"truncated" after the attachments table (~18-19GB).  After reviewing your
documentation (https://docs.bestpractical.com/rt/4.4.1/backups.html) I
realized my mysqldump command was incorrect. I'm working in a lab
environment now to see how I can optimize/change my script to more closely
match yours. The only thought I have right now is to exclude the data from
the AttachmentsIndex as that would get rebuilt on restoration anyways. Is
there any other reason that the backup would just "stop" after the
attachment table? I apologize if this is beyond the scope of the forums
thread.

 

Thank you in advance!

 

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-gene...@qvii.com
<mailto:hd-gene...@qvii.com%3cmailto:hd-gene...@qvii.com>
<mailto:hd-gene...@qvii.com>

To report email issues: postmas...@qvii.com
<mailto:postmas...@qvii.com%3cmailto:postmas...@qvii.com>
<mailto:postmas...@qvii.com>

 

Stephen Cena

Senior Systems Administrator 

Quality Vision International, Inc.

Phone: (585) 544-0450 x300

To notify helpdesk:  <http://helpdesk.ogp.qvii.com>
http://helpdesk.ogp.qvii.com or email:  <mailto:hd-gene...@qvii.com>
hd-gene...@qvii.com

To report email issues:  <mailto:postmas...@qvii.com> postmas...@qvii.com

 



smime.p7s
Description: S/MIME cryptographic signature

Re: [rt-users] MySQL backups of RT 4.4.1 truncated

[Cena, Stephen (ext. 300)]

I just ran a test with the attachments table "ignored", and the backup worked 
perfectly. As a "sanity check", I also ran mysqlcheck on the schema & no errors 
were found. Should I split my backup job into two pieces, or is there a better 
way to get a backup all in one shot?


--



Message: 4

Date: Wed, 18 Jan 2017 13:54:57 +

From: "Cena, Stephen (ext. 300)" >

To: "'rt-users@lists.bestpractical.com'"


>

Subject: [rt-users] MySQL backups of RT 4.4.1 truncated

Message-ID:


<87f81e27495dc8489147e34a4152e268a4950...@mailstore2010.ogp.qvii.com>

Content-Type: text/plain; charset="us-ascii"



I'm currently running RT 4.4.1 on Ubuntu 14.04 LTS with MySQL 5.6 on Windows 
Server 2012. Recently, we enabled full-text indexing in native MySQL & 
everything is great. After doing some work on the server over the weekend, I 
discovered that the .sql files being generated for the backup are being 
"truncated" after the attachments table (~18-19GB).  After reviewing your 
documentation (https://docs.bestpractical.com/rt/4.4.1/backups.html) I realized 
my mysqldump command was incorrect. I'm working in a lab environment now to see 
how I can optimize/change my script to more closely match yours. The only 
thought I have right now is to exclude the data from the AttachmentsIndex as 
that would get rebuilt on restoration anyways. Is there any other reason that 
the backup would just "stop" after the attachment table? I apologize if this is 
beyond the scope of the forums thread.



Thank you in advance!



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-gene...@qvii.com>

To report email issues: 
postmas...@qvii.com>

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-gene...@qvii.com
To report email issues: postmas...@qvii.com