Re: < ? Solved ? > Re: mysqldump: Error 2013: Lost connection to MySQL server
On Tue, Jan 13, 2009 at 6:06 PM, Dan wrote: > On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner > wrote: > >> This sounds like you need to raise max_allowed_packet for mysqldump >> (and possibly mysqld) - these are separate settings for both the >> client and the server. You can do this via the my.cnf (or ~/.my.cnf) >> or specify it as an option on the command line "mysqldump --opt ... >> --max_allowed_packet=1G dbname > backup-file". > > This is certainly the most common advice for this error, yes. I increased > the max_allowed_packet size from 1M to 128M when the problem initially > occured. This didn't fix anything. My apologies. I hadn't read up-thread where this was discussed, and given that, max_allowed_packet is almost certainly not the problem. Sorry for the noise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: < ? Solved ? > Re: mysqldump: Error 2013: Lost connection to MySQL server
On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner wrote: > This sounds like you need to raise max_allowed_packet for mysqldump > (and possibly mysqld) - these are separate settings for both the > client and the server. You can do this via the my.cnf (or ~/.my.cnf) > or specify it as an option on the command line "mysqldump --opt ... > --max_allowed_packet=1G dbname > backup-file". This is certainly the most common advice for this error, yes. I increased the max_allowed_packet size from 1M to 128M when the problem initially occured. This didn't fix anything. Since dbmail splits up all email body / attachments into small chunks and inserts these chunks in separate records, I really don't see how a max_allowed_packet size of 128M would fail ... especially since the data got in there with a max_allowed_packet size of 1M to begin with. The biggest email in the database is 50M. So even if dbmail *hadn't* split the email into separate records, a max_allowed_packet size of 128M should be *easily* big enough, shouldn't it? As for a max_allowed_packet size of 1G, that just sounds dangerous. The server has 900MB or so of chip RAM and 512MB of swap. It's also running a LOT of other services. I don't want something stupid happening like Linux's out-of-memory-killer coming along and killing MySQL, causing database corruption. Can someone please comment on this? If it's not dangerous, I will try it. As noted in a prior post, I 'successfully' completed a backup last night, and I'm testing it now, but it took 10 hours to complete, and was still running when people came in this morning, which is obviously not desirable, so if I can somehow still use the --opt option of mysqldump by making max_allowed_packet to some absolutely astronomical level without endangering things, maybe that's the way to go. Maybe ... Anyway, thanks for the comments Andrew. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: < ? Solved ? > Re: mysqldump: Error 2013: Lost connection to MySQL server
This sounds like you need to raise max_allowed_packet for mysqldump (and possibly mysqld) - these are separate settings for both the client and the server. You can do this via the my.cnf (or ~/.my.cnf) or specify it as an option on the command line "mysqldump --opt ... --max_allowed_packet=1G dbname > backup-file". On Tue, Jan 13, 2009 at 2:58 PM, Dan wrote: > On Tue, 2009-01-13 at 12:19 +0530, Chandru wrote: > >> Hi, >> >> Did u try using this command >> >> >> mysqldump --opt db_name > db_name.sql -p 2>>bkp.err > > Not quite. Firstly, I had to alter the normal backup cron job, and that > doesn't happen until late at night. > > Secondly, yes I added the redirection to capture errors. There were none > ( empty file this time ). > > Thirdly, I didn't use '--opt'. I had no other suggestions yesterday > ( before I went to bed anyway - there's 1 in my inbox this morning ), so > I did some experimenting of my own and changed the dump command to: > > mysqldump --skip-opt --add-drop-table --add-locks --create-options > --quick --lock-tables --set-charset --disable-keys dbmail > dbmail.sql > -pSOME_PASSWORD 2>>bkp.err > > This made mysql do 1 insert per record. > > The backup *appears* to have completed successfully. At least the end of > the dump file looks valid. It ends dumping the last table, then a view, > then I get: > > -- Dump completed on 2009-01-13 17:23:13 > > Previously it just finished part-way through dumping a blob. > > I have yet to do extensive testing on it. I suppose I should try > importing the dump file into another server and see if I get the correct > number of rows in each table ... > > The only issue now is that the dump file is much smaller than I would > have expected. When using --opt, I was getting 30GB dump files. I would > have expected the current format ( 1 insert statement per record ) to be > much bigger, but it's 23GB. Now having said that, I did email the > current DB administrator and ask him to get people to archive all emails > with huge attachments somewhere on a network share ( people have some > pretty big attachments ). Also I asked him to get people to clean out > their Trash ( which happens only when we tell them to ). So I suppose > it's not completely infeasible that this alone is responsible for the > difference. > > Anyway, it's been a very disconcerting experience. It goes without > saying that people would expect that anything that gets into a MySQL > database should be able to be backed up by mysqldump. And it's worrying > that the default --opt can't do that. When I get some time I'll enter a > bug ... > > Thanks for you help Chandru. > > Dan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=andrew.b.gar...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org