Issue while taking DB Backups

[msksharma]

Hi,

We have a OTRS testing environment server which has old data.

For some testing, they wanted a DB refresh.

OTRS version is 6.0.26

Today i ran the command ./backup.pl -d /mnt/synology/OTRS6/ -c gzip -t dbonly to take a backup of only the DB.

However after some time, i am getting this error

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `article_data_mime_attachment` at row: 21926

failed
Please help me out.

Regards,
Krishna M S

[skullz]

Increase or set max_allowed_packet under [mysqldump] section in your mysql/mariadb config file..

/etc/my.cnf (if using centos)

[msksharma]

Hi,

My current setting is 500M. What should be the recommended size?.

Regards,
Krishna M S

[msksharma]

Hi,

These are my settings for mysqldump

[mysqldump]
quick
quote-names
#max_allowed_packet = 16M
max_allowed_packet = 100M

Do i increase this also to 500M?

Regards,
Krishna M S

[skullz]

It under [mysqldump]..no sure..you can start at 500 though..just try to gradually increase it..make sure after changes, db have to be restarted..

I guess it coming from article attachment..perhaps move over your article from db to filesystem instead..

[msksharma]

Hi,
I changed it to 1GB and restarted the system. However i still get the error

root@otrs:/opt/otrs/scripts# ./backup.pl -d /mnt/synology/OTRS6/ -c gzip -t dbonly
Backup of filesystem data disabled by parameter dbonly ...
Dump MySQL data to /mnt/synology/OTRS6//2020-07-30_05-13/DatabaseBackup.sql.gz ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `article_data_mime_attachment` at row: 21926
failed

I do not know how much i need to specify. Please suggest and help me out.

Regards,
Krishna M S

[msksharma]

Hi,

Even changing to 5G did not work.

[mysqldump]
quick
quote-names
#max_allowed_packet = 16M
max_allowed_packet = 5G

Regards,
Krishna M S

[wurzel]

Hi,

this should work. For my opinion this is more a database question. you might get more help in mysql forum?

Flo

[skullz]

Hi,

Even changing to 5G did not work.

[mysqldump]
quick
quote-names
#max_allowed_packet = 16M
max_allowed_packet = 5G

Regards,
Krishna M S

After each changes, make sure restart the database..not otrs..

The others way I can see is by using file storage based article.

[msksharma]

Hi,

Yes, i was making sure i used to run

service mysql restart

Do u have some guide on how to move attachments to FS? Is this the same as articles? Because i see that my /opt/otrs/var/article size is 51GB.

Regards,
Krishna M S

[skullz]

Hi,

Yes, i was making sure i used to run

service mysql restart

Do u have some guide on how to move attachments to FS? Is this the same as articles? Because i see that my /opt/otrs/var/article size is 51GB.

Regards,
Krishna M S

Yes..that supposed to be the correct one..apparently, you are already on file system based..this is weird..should be easy enough to backup/dump the db..

However, I suggest check first the size of this article table..

[msksharma]

Hi,

Could u please let me know how do i find it out? I am not too familiar with MySQL?

Regards,
Krishna M S