Issue while taking DB Backups

Moderator: crythias

Post Reply
msksharma
OTRS newbie
Posts: 50
Joined: 24 Mar 2014, 13:58
OTRS Version?: 6.0.26
Real Name: Krishna M S
Company: Toradex AG

Issue while taking DB Backups

Post by 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
OTRS wizard
Posts: 301
Joined: 24 Feb 2012, 03:58
OTRS Version?: OTRS 6
Real Name: Skullz
Contact:

Re: Issue while taking DB Backups

Post by skullz »

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

/etc/my.cnf (if using centos)

msksharma
OTRS newbie
Posts: 50
Joined: 24 Mar 2014, 13:58
OTRS Version?: 6.0.26
Real Name: Krishna M S
Company: Toradex AG

Re: Issue while taking DB Backups

Post by msksharma »

Hi,

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

Regards,
Krishna M S

msksharma
OTRS newbie
Posts: 50
Joined: 24 Mar 2014, 13:58
OTRS Version?: 6.0.26
Real Name: Krishna M S
Company: Toradex AG

Re: Issue while taking DB Backups

Post by 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
OTRS wizard
Posts: 301
Joined: 24 Feb 2012, 03:58
OTRS Version?: OTRS 6
Real Name: Skullz
Contact:

Re: Issue while taking DB Backups

Post by 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
OTRS newbie
Posts: 50
Joined: 24 Mar 2014, 13:58
OTRS Version?: 6.0.26
Real Name: Krishna M S
Company: Toradex AG

Re: Issue while taking DB Backups

Post by 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
OTRS newbie
Posts: 50
Joined: 24 Mar 2014, 13:58
OTRS Version?: 6.0.26
Real Name: Krishna M S
Company: Toradex AG

Re: Issue while taking DB Backups

Post by 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
OTRS guru
Posts: 2973
Joined: 08 Jul 2010, 22:25
OTRS Version?: 6.0.x

Re: Issue while taking DB Backups

Post by wurzel »

Hi,

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

Flo
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 10 (Test)
    ((OTRS)) Community Edition 6.0.x, LAMP LIVE auf Debian 10

    -- Ich beantworte keine Forums-Fragen PN - No PN please

    I won't answer to unfriendly users any more. A greeting and regards are just polite.

    skullz
    OTRS wizard
    Posts: 301
    Joined: 24 Feb 2012, 03:58
    OTRS Version?: OTRS 6
    Real Name: Skullz
    Contact:

    Re: Issue while taking DB Backups

    Post by skullz »

    msksharma wrote:
    30 Jul 2020, 05:23
    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
    OTRS newbie
    Posts: 50
    Joined: 24 Mar 2014, 13:58
    OTRS Version?: 6.0.26
    Real Name: Krishna M S
    Company: Toradex AG

    Re: Issue while taking DB Backups

    Post by 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
    OTRS wizard
    Posts: 301
    Joined: 24 Feb 2012, 03:58
    OTRS Version?: OTRS 6
    Real Name: Skullz
    Contact:

    Re: Issue while taking DB Backups

    Post by skullz »

    msksharma wrote:
    30 Jul 2020, 10:53
    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
    OTRS newbie
    Posts: 50
    Joined: 24 Mar 2014, 13:58
    OTRS Version?: 6.0.26
    Real Name: Krishna M S
    Company: Toradex AG

    Re: Issue while taking DB Backups

    Post by 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

    Post Reply