702 MySQL Error: MySQL client ran out of memory

9 posts by 3 authors in: Forums > CMS Builder
Last Post: March 18, 2013   (RSS)

By nmsinc - February 28, 2013

Backing up our database from CMS today I received a 702 MySQL Error: MySQL client ran out of memory. Any help would be appreciated!

Thanks - nmsinc

nmsinc

By Dave - February 28, 2013

Hi nmsinc, 

Do you know how many records are in your database or how large it is?  And I assume you're using the backup feature under Admin > General?

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - February 28, 2013

Hi Dave,

8,365 records

nmsinc

By Dave - March 5, 2013

Hi nmsinc,

Sorry for the delay in responding, we just moved our office and have had some downtime getting things all setup again.

Re: 8,365 records, that doesn't sounds like much at all.  Do you think the memory limits for MySQL might be set especially low?  And do you know how big the backup files were before?

I can debug it if you like, just send CMS/FTP login credentials to dave@interactivetools.com (Note: Don't post login credentials to the forum).

Hope that helps! Cheers.

Dave Edis - Senior Developer
interactivetools.com

By Dave - March 12, 2013

Hi nmsinc, 

Thanks for sending in the support ticket, I had a look at your installation and the issue is definitely related to the 250+ meg database.

Your database is larger than can be supported by CMSB's built in backup/restore system. The next step would be to talk to your host or server admin about alternative backup/restore options for MySQL. We have some 1gig+ mysql databases ourselves and we need to do the same thing.

Sorry I can't suggest much else.  Let me know if you have any questions or want any more technical details about it.

Dave Edis - Senior Developer
interactivetools.com

By Codee - March 12, 2013

So, what are the limits for backup&restore?  I've always been under the impression a client can have CMSB running and managing hundreds of thousands of records with no discernible loss in speed.  I figured the auto backup plugin would be able to accomodate any size DB that CMSB handles...but apparently not? Please enlighten me so that I can possibly work with a couple of particular clients that don't have particularly large numbers of records in their database but each record has lots of large-filed photographs included.

By Dave - March 12, 2013

Hi equinox,

There's no set limits, but we have the combined restrictions of MySQL, PHP, and the webserver (typically Apache).  We're pretty good at working around those, but at certain points it makes more sense to fallback on established best-practices for doing large backups or other tasks rather than re-inventing the wheel.  

If you've ever copied a 1gig file from one location to another on your desktop you'll know that even that can take a couple minutes.  With database backups it can take a lot longer, so when they get large you need to look at taking special steps to back them up.  Even disk space and bandwidth becomes an issue.  Take AutoBackup for example, it can email a copy of the backup.. but obviously at 1gig that doesn't make a lot of sense.

And with most web servers, there's often a limit on how many seconds we can run for before the server just kills the process in the middle of whatever it's doing.  So we can't perform long-running and have to take extra steps to do that (either breaking them up into chunks or attempting to run from the command-line, cron, etc).  

Now, you mentioned you have lots of photographs.  Those are actually stored as files if your uploads folder, we only store the filepath so your database is still probably quite small.  Just make sure you're backing up both your MySQL with autobackup or manually, and -also- downloading your /uploads/ folders.

Hope that helps, let me know any questions.  Thanks! :)

Dave Edis - Senior Developer
interactivetools.com

By Codee - March 18, 2013

Okay, panic-breathing has stopped. Thanks, Dave.