need Help - Mysqldump issue

Hello,
Starting today, my daily database backup script does not work :-( with
mysqldump typically Out of memory error.
So, I try to change max_allowed_packet option value, but I don't succeed
(mysql run on ubuntu 10.04 OS with 5.1.41-3ubuntu12.7 0 mysql version.)

On the last successfull backup, database size was : 2,59 Go

Here is some of my test :

(with --max_allowed_packet option = 512M )
/usr/bin/mysqldump -A --max_allowed_packet=512M
--default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
Enter password:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes
when dumping table `jahia_sl2_version_content` at row: 0

(with --max_allowed_packet = 1024M or 2048 option : )
/usr/bin/mysqldump -A --max_allowed_packet=1024M
--default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
Enter password:
mysqldump: Out of memory (Needed 1405796107 bytes)
mysqldump: Couldn't allocate memory

(with --max_allowed_packet = 4096M option : )
/usr/bin/mysqldump -A --skip-quick --max_allowed_packet=4096M
--default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
Warning: option 'max_allowed_packet': unsigned value 4294967296 adjusted
to 2147483648
Enter password:
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when
retrieving data from server

Adding --skip-opt --quick option does not solve the issue

Adding --skip-quick, the error is :
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when
retrieving data from server

Here is my config : /etc/mysql/my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 4096M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M

log_error = /var/log/mysql/error.log

expire_logs_days = 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M

includedir /etc/mysql/conf.d/

Any help would be appreciate !!
Bests
--

----------------------------------------------------
Christophe Dumonet
Centre de Ressources Informatiques
Institut Francais de Mecanique Avancee (IFMA)
Campus des Cezeaux
BP 265
63175 AUBIERE Cedex
Tel : +33 - 4.73.28.80.64
Fax : +33 - 4.73.28.81.00
Mail : Christophe.Dumonet@ifma.fr
----------------------------------------------------



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: need Help - Mysqldump issue

Hello,
Thank for your help
..... I just try mysqldump with --quick or --opt option ... to avoid
"out of memory" problem but

--> dump fails with --max_allowed_packet=2048M and --quick :
root@pcjahia01:/# /usr/bin/mysqldump -A --max_allowed_packet=2048M
--quick --default-character-set=UTF8 -u root -p > /var/tmp/testbackup01.sql
Enter password:
mysqldump: Error 5: Out of memory (Needed 702898072 bytes) when dumping
table `jahia_sl2_version_content` at row: 0


--> dump succeed with --max_allowed_packet=4096M and --quick ( but the
computer freezes a lot ........)

I thought using --quick option avoid mysqldump "out of memory"
problem...but I still need --max_allowed_packet=4096M ...

Computer total memory is 8Gb, I run a J2EE application which own 4Gb,
additionally a 3Gb mysql database, altought I use mysqldump with --quick

Do you think I need more physical memory ?

Bests,
Christophe.

----------------------------------------------------
Christophe Dumonet
Centre de Ressources Informatiques
Institut Francais de Mecanique Avancee (IFMA)
Campus des Cezeaux
BP 265
63175 AUBIERE Cedex
Tel : +33 - 4.73.28.80.64
Fax : +33 - 4.73.28.81.00
Mail : Christophe.Dumonet@ifma.fr
----------------------------------------------------


Le 19/11/2010 10:46, Christophe DUMONET a écrit :
> Hello,
> Starting today, my daily database backup script does not work :-(
> with mysqldump typically Out of memory error.
> So, I try to change max_allowed_packet option value, but I don't succeed
> (mysql run on ubuntu 10.04 OS with 5.1.41-3ubuntu12.7 0 mysql version.)
>
> On the last successfull backup, database size was : 2,59 Go
>
> Here is some of my test :
>
> (with --max_allowed_packet option = 512M )
> /usr/bin/mysqldump -A --max_allowed_packet=512M
> --default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
> Enter password:
> mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet'
> bytes when dumping table `jahia_sl2_version_content` at row: 0
>
> (with --max_allowed_packet = 1024M or 2048 option : )
> /usr/bin/mysqldump -A --max_allowed_packet=1024M
> --default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
> Enter password:
> mysqldump: Out of memory (Needed 1405796107 bytes)
> mysqldump: Couldn't allocate memory
>
> (with --max_allowed_packet = 4096M option : )
> /usr/bin/mysqldump -A --skip-quick --max_allowed_packet=4096M
> --default-character-set=UTF8 -u root -p > /tmp/testbackup01.sql
> Warning: option 'max_allowed_packet': unsigned value 4294967296
> adjusted to 2147483648
> Enter password:
> mysqldump: Out of memory (Needed 702898104 bytes)
> mysqldump: Got error: 2008: MySQL client ran out of memory when
> retrieving data from server
>
> Adding --skip-opt --quick option does not solve the issue
>
> Adding --skip-quick, the error is :
> mysqldump: Out of memory (Needed 702898104 bytes)
> mysqldump: Got error: 2008: MySQL client ran out of memory when
> retrieving data from server
>
> Here is my config : /etc/mysql/my.cnf
>
> [client]
> port = 3306
> socket = /var/run/mysqld/mysqld.sock
>
> [mysqld_safe]
> socket = /var/run/mysqld/mysqld.sock
> nice = 0
>
> [mysqld]
>
> user = mysql
> socket = /var/run/mysqld/mysqld.sock
> port = 3306
> basedir = /usr
> datadir = /var/lib/mysql
> tmpdir = /tmp
> skip-external-locking
> bind-address = 127.0.0.1
> key_buffer = 16M
> max_allowed_packet = 4096M
> thread_stack = 192K
> thread_cache_size = 8
> myisam-recover = BACKUP
> query_cache_limit = 1M
> query_cache_size = 16M
>
> log_error = /var/log/mysql/error.log
>
> expire_logs_days = 10
> max_binlog_size = 100M
>
> [mysqldump]
> quick
> quote-names
> max_allowed_packet = 16M
>
> [mysql]
>
> [isamchk]
> key_buffer = 16M
>
> includedir /etc/mysql/conf.d/
>
> Any help would be appreciate !!
> Bests


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org