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 : r...@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.dumo...@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=arch...@jab.org
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.dumo...@ifma.fr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump issue
Ginger Cheng <[EMAIL PROTECTED]> wrote: > If a table has a column defined as 'float not null' and the corresponding > txt file used to load it have sth like 'nan' for the column, although > giving warnings, the record will be loaded and the column looks like 'nan' > by using mysqlimport. But if you do mysqldump, it puts a 'null' in the > column for the insert statement, which fails the later mysql < mysqldump > (cuz the table definition does not allow it). Can you prepare a testcase for that? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump issue
Ginger Cheng wrote: Hello, MySQL gurus, Since I have been moving data around using mysqldump recently, I have found sth annoying: If a table has a column defined as 'float not null' and the corresponding txt file used to load it have sth like 'nan' for the column, although giving warnings, the record will be loaded and the column looks like 'nan' by using mysqlimport. But if you do mysqldump, it puts a 'null' in the column for the insert statement, which fails the later mysql < mysqldump (cuz the table definition does not allow it). But mysqldump option to produce .sql and .txt file does not allow write to mounted file server or remote machine. mysqldump thedatabase > afile/anywhere will work as long as the user has write permissions. It does not matter whether it is a mounted fileserver or not. SO the mysql machine has to have enough space to do it locally. It can be tough for a huge database. Have anyone had this problem before? THank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump issue
Hello, MySQL gurus, Since I have been moving data around using mysqldump recently, I have found sth annoying: If a table has a column defined as 'float not null' and the corresponding txt file used to load it have sth like 'nan' for the column, although giving warnings, the record will be loaded and the column looks like 'nan' by using mysqlimport. But if you do mysqldump, it puts a 'null' in the column for the insert statement, which fails the later mysql < mysqldump (cuz the table definition does not allow it). But mysqldump option to produce .sql and .txt file does not allow write to mounted file server or remote machine. SO the mysql machine has to have enough space to do it locally. It can be tough for a huge database. Have anyone had this problem before? THank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump issue (table named group)
Hello. On Fri 2003-01-31 at 13:45:26 -0600, [EMAIL PROTECTED] wrote: > >Description: > One of our customers had a table named group, admitedly a bad design > but still not something that should cause mysqldump to fail hard! Please be more specific. Did mysqldump error out or the re-import of the data? > >How-To-Repeat: > Name a table group and run mysqldump, even with --opt Have you tried --quote-names ? > >Fix: > Quick workaround is to rename the table, but actually, mysqldump should > surround columns and tables with `` marks when creating sql code See above, --quote-names is supposed to do that. If it doesn't for you, please provide a repeatable example. Bye, Benjamin. > Release: mysql-3.23.54 (Source distribution) > System: Linux x.com 2.4.18-19.7.x #1 Thu Dec 12 09:00:42 EST 2002 >i686 unknown [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump issue (table named group)
>Description: One of our customers had a table named group, admitedly a bad design but still not something that should cause mysqldump to fail hard! >How-To-Repeat: Name a table group and run mysqldump, even with --opt >Fix: Quick workaround is to rename the table, but actually, mysqldump should surround columns and tables with `` marks when creating sql code >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.54 (Source distribution) >Environment: System: Linux x.com 2.4.18-19.7.x #1 Thu Dec 12 09:00:42 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='gcc' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 18 21:03 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Oct 10 10:16 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312442 Oct 10 09:51 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 10 09:46 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Jan 6 20:45 /usr/lib/libc-client.a -> c-client.a Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--datadir=/usr/share' '--libdir=/usr/lib' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-debug' '--without-readline' '--enable-shared' '--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb' '--with-berkeley-db' '--enable-large-files=yes' '--enable-largefile=yes' '--with-thread-safe-client' '--enable-assembler' 'CFLAGS=-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' 'CXXFLAGS=-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
