Backup problem - max_allowed_packet

[andy17d]

Hello.

For some time during the backup OTRS database get the message:

Backup /backup/2016-10-14_02-00/Config.tar.gz ... done
Backup /backup/2016-10-14_02-00/Application.tar.gz ... done
Dump MySQL rdbms ... mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `article_attachment` at row: 356140
failed

So far, I solved this problem by increasing the value of the parameter max_allowed_packet.
But now, despite entering higher value, this parameter will not increase more than 1073741824 namely 1GB.

I read that 1GB is the upper limit for this parameter.
How, then, I can perform backup the entire database?
Help, please

[jojo]

max_allowed package is not the size of the dump. Please consult the mysql manual or your Databaseadministrator

[andy17d]

max_allowed package is not the size of the dump. Please consult the mysql manual or your Databaseadministrator

But what should I consult? I am the administrator of the MySQL server.

[jojo]

http://dev.mysql.com/doc/

[andy17d]

http://dev.mysql.com/doc/

Why did you give me a link to the manual for mysql?

[jojo]

as max_allowed package is a setting of the mysql database

[andy17d]

In the my.cnf file:

Code: Select all

max_allowed_packet = 2G

but "SHOW VARIABLES" query say that:

Code: Select all

max_allowed_packet = 1073741824

There are 372516 rows in the article_attachment table.

The problems begin with the rows no 356140

Dump MySQL rdbms ... mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `article_attachment` at row: 356140 failed

[jojo]

max allowed package defines how large 1 insert/select in/from table can be. So it has no dependency on the nummer of rows. Please read and understand the mysql manuals or get an experienced databse admin as this are fundamental basics of operating a mysql database.

Also not related to OTRS.

[root]

The OTRS backup script uses the mysqldump command. This command uses a own setting for max_allowed_packet. Your can configure this by adding/modifying the $HOME/.my.cnf of the user who performs the dump (or /etc/my.cnf globally). Just add this:

Code: Select all

[mysqldump]
max_allowed_packet = 2G

Checkout https://dev.mysql.com/doc/refman/5.5/en ... on-summary and https://dev.mysql.com/doc/refman/5.5/en ... files.html

[andy17d]

The problem generally applies to backup performed automatically by the cron.

To date, the following tests done (as root):
- Dump the database with the command mysqldump - is working properly
- Backup of the database using a script backup.pl manually activated - also works properly.

But when I run the script backup.pl as a user otrs, then crash.

In the file /etc/my.cnf I had already set:

Code: Select all

[Mysqld]
max_allowed_packet = 2G

[Mysqldump]
max_allowed_packet = 2G

[andy17d]

I even modified the script backup.pl
from:

Code: Select all

"-f $ $ DBDump Directory / DatabaseBackup.sql -h $ DatabaseHost U $ DatabaseUser $ Database"

for:

Code: Select all

"-f $ $ DBDump Directory / DatabaseBackup.sql -h $ DatabaseHost U $ DatabaseUser $ Database --max_allowed_packet=2G"

But it does not work.

[root]

Is there another .my.cnf in the home dir of the otrs user?

[andy17d]

There is only one file my.cnf in the system - /etc/my.cnf

[root]

You said you tried it manually. Also with the otrs user?

[andy17d]

I did attempt follows:
I logged into the server as root.

First test:
/backup.pl -d /backup - ok
Second test:
sudo -u otrs backup.pl -d /backup - crashes after copying about 7 GB

[root]

What are the permissions of /etc/my.cnf?

[andy17d]

Indeed, the problem was with the rights to /etc/my.cnf file and directory / etc / mysql.
Otrs user didn't have right to read them.
I gave it to him right now and everything works. I just wonder how it could previously operate properly for 3 years.
Thank you very much for your help.