Post by Dunald » Thu Apr 18, 2013 10:56 pm

Hi all!
I have a store using latest opencart. I have about 10000 products in my store and today the backupfile became to large after I added 400 more products to my store. I get this error:
Fatal error: Allowed memory size of 83886080 bytes exhausted (tried to allocate 4718564 bytes) in /customers/a/2/b/kontorstema.se/httpd.www/admin/model/tool/backup.php on line 71
My host http://www.one.com says it is because the backup file is larger then the limit of 80MB. And they can not help me with this issue... However if I make a backup using phpmyadmin the file is only 6MB!?


I tried to make a backup WITHOUT the products = works fine
I tried to make a backup with ONLY the products = error

So I can´t make a backup in the admin....
Is there a way to fix this? I mean can I backup 50% of my products at a time or something like that? Probably not but I have to ask. Any ideas how to solve this? ???
Last edited by Dunald on Wed Sep 04, 2013 3:48 pm, edited 2 times in total.

Active Member

Posts

Joined
Tue Mar 15, 2011 9:05 pm

Post by butte » Fri Apr 19, 2013 3:24 am

[Edit, per next two posts: As I misread yours, USE phpMyAdmin not OC, in the following sentence.] As an interim solution you can use admin panel's Settings / Backup to pull down a text .sql file that will probably be smaller than the limit. The backups from OC and from phpMyAdmin differ (not in your data but in various mysql-related items and in file sizes), and the one via OC will be the smaller of the two. Just remember that you can use OC or phpMyAdmin for backup or restore, but between the two methods you do not want to swap the two methods' two backup different files.

If (taken alone but together) only 10,000 products work but only 10,400 products swamp the 80 meg limit, then on average each product bears more than coarsely 7.7 kb and maybe your descriptions should be shortened.

A simpler solution in the long run would probably be to change hosts sooner than later.
Last edited by butte on Fri Apr 19, 2013 5:29 am, edited 1 time in total.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Fri Apr 19, 2013 3:37 am

You might have missed it but the OpenCart backup is the one Dunald's having issues with based on the error (/admin/model/tool/backup.php). OpenCart also generates SQL backups that are much, much larger than phpMyAdmin and much less useful.

I always recommend going with phpMyAdmin. It's a superior solution to OpenCart's built-in method as it handles memory better, gives full restore data, generates smaller SQL files, and allows for on-the-fly compressing.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by butte » Fri Apr 19, 2013 5:24 am

Touche. Thanks, rph, dinna "might" but did miss it on the fly. (So as to preclude of offset confusion my prior post bears editing to note do the reverse, to use phpMyAdmin.)

Dunald, what rph and I are both saying is that you have two ways to back up the database, and from where you started (that I misread and he caught) with OC, the other one left is phpMyAdmin.

I'll add to what he said, since the phpMyAdmin backup can also be readily used usefully in Oracle's Workshop and similar. My only disagreement is that when drawn down as a concurrent backup pair, OC backups (unzipped .sql) are not always bigger than phpMyAdmin backups (unzipped .sql or zipped .zip) are. It's worth making both backups if possible.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Fri Apr 19, 2013 8:55 am

I can't imagine an instance where OpenCart would be smaller. OpenCart dumps the data with all the columns in each insert like:

Code: Select all

INSERT INTO `product_filter` (`product_id`, `filter_id`) VALUES ('936', '4');
INSERT INTO `product_filter` (`product_id`, `filter_id`) VALUES ('937', '2');
INSERT INTO `product_filter` (`product_id`, `filter_id`) VALUES ('938', '4');
While phpMyAdmin defaults to:

Code: Select all

INSERT INTO `product_filter` (`product_id`, `filter_id`) VALUES
('936', '4'),
('937', '2'),
('938', '4'),
That means OpenCart is going to have tons of redundant data, especially in large tables like `product` where each insert includes:

Code: Select all

INSERT INTO `product` (`product_id`, `model`, `sku`, `upc`, `ean`, `jan`, `isbn`, `mpn`, `location`, `quantity`, `stock_status_id`, `image`, `manufacturer_id`, `shipping`, `price`, `points`, `tax_class_id`, `date_available`, `weight`, `weight_class_id`, `length`, `width`, `height`, `length_class_id`, `subtract`, `minimum`, `sort_order`, `status`, `date_added`, `date_modified`, `viewed`, `cost`) VALUES ( ...
I just tested on one large store DB and the raw SQL from OpenCart was twice as large as from phpMyAdmin (64 MB vs. 32 MB). I also had to increase the php.ini memory just to run the OpenCart backup as the same memory exhausted issue occurs.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by butte » Fri Apr 19, 2013 10:19 am

The important part at the moment, of course, is that Dunald probably does have the better means at his disposal via his hosting control panel (or via Oracle's Workbench or similar), and has been quiet for several hours.

I suppose the difference whichever way it falls must spring from elections available and taken in phpMyAdmin or in OC (before any zipping/not choice), and is affected by host nuances which do vary such as php and phpMyAdmin settings (including features displayed) along with which control panel is in effect. It might also spring from differences in backup overheads (various stuffing that isn't itself the usual OC information) relative to quite small through very large store sizes. When both are available I obtain both just for whatever slim margin of safety might be gained by the dual backups. Overall that's interesting enough that I'll tinker with it a bit.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Fri Apr 19, 2013 11:41 am

Default phpMyAdmin setting is Complete + Extended (i.e. Both) which is the second example I posted. A user could manually change it to Complete like OpenCart uses, but why?

Either way OpenCart backups are not useful as they are extremely large and don't keep table info. If your database suffered a catastrophic failure you'd never be able to properly restore off it.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by butte » Fri Apr 19, 2013 12:33 pm

Okay, then what is the justification for having the Backup / Restore functions inside OC in the first place?

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by grgr » Fri Apr 19, 2013 2:50 pm

Very useful if you need to just replace the contents of a single table. If you are working on a area you can keep a backup and if you mess up it is a simple task to restore the data. Not quite so useful as a full backup but quick and easy for data in single tables.

For a full backup of the database I dump the contents of mySQL on a daily basis via a cronjob.

Image Image Image Image Image Image Image Image


User avatar
Active Member

Posts

Joined
Mon Mar 28, 2011 4:08 pm
Location - UK

Post by butte » Sat Apr 20, 2013 1:00 pm

That was part of my point, so I'm not alone . . .

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Sat Apr 20, 2013 1:12 pm

phpMyAdmin can handle individual tables or groups of tables too (though I'd probably do a full backup just because of the interconnectedness of the data). The only major advantage I can think of for the built-in OpenCart method is that it will always be available to a dev with permissions.

Any system I'm admining on I'll use phpMyAdmin though. And the odd occasion it comes up when I'm troubleshooting an issue for someone I use a custom script I FTP up rather than deal with OC backup/restore.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by butte » Sun Apr 21, 2013 12:31 pm

All three of us are on the same proverbial page, each has its uses according to needs. In addition to phpMyAdmin, unless it runs on the local machine, Oracle's Workbench or similar will often be comparably useful.

So, what have we done with Dunald?

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by Dunald » Tue Apr 23, 2013 7:57 pm

butte wrote:All three of us are on the same proverbial page, each has its uses according to needs. In addition to phpMyAdmin, unless it runs on the local machine, Oracle's Workbench or similar will often be comparably useful.

So, what have we done with Dunald?
Hi all!
Thanks for the answers. I understand that the phpmyadmin is a "better" backup. However as I am importing many products to my store it sometimes goes wrong and it was easy just to restore from my latest backup in the OC.
I do take backup files in the phpmyadmin but only for safety reasons as if my entire site should fail.
It is also much more easy to make a backup in OC, I dont have to go to another site (phpmyadmin) and nothing can go wrong when I use OC but everything can go wrong if I do something really wrong in phpmyadmin.

But do I understand the answers correct?
Is there no solution to my problem with the backup in OC? And no I dont´t think my descriptions are to big and yes I need all my products in my store. :-\

Active Member

Posts

Joined
Tue Mar 15, 2011 9:05 pm

Post by butte » Wed Apr 24, 2013 11:37 pm

Welcome back, Dunald, that's a relief. Yes, using phpMyAdmin can cause headaches, and yes, for routine non-catastrophic measures the OC backup and restore work well.

Your present problem may be resolved by specifically unselecting enough of the OC options to obtain what you actually want and need at the moment. There's a scrollable checklist, several items pertain to products, and unclicking first the non-product ones, and if [edit; and as] need be then unclicking also some of the product ones, should give you a .sql smaller than the complete one that won't transfer.
Last edited by butte on Thu Apr 25, 2013 10:26 am, edited 1 time in total.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Thu Apr 25, 2013 9:11 am

Dunald wrote:Is there no solution to my problem with the backup in OC?
Afraid not.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by rph » Thu Apr 25, 2013 9:15 am

butte wrote:Okay, then what is the justification for having the Backup / Restore functions inside OC in the first place?
It's not a bad feature, just an incomplete one. It needs improvement, not scuttling.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by butte » Thu Apr 25, 2013 10:37 am

Dunald, for your predicament I would still try either trimming from "just" the product entries (which together still were too large) or building from one by one the product entries (each should be small enough) in the scrollable checklist for the OC Backup.

Of course, you would also need to bear in mind the same 80 meg limitation in trying to use the mating OC Restore function.

However, for the long haul (beyond your 10,400 products), rph has basically told you that between your server's 80 meg limit and your OC Backup's limitations, you're already on thin ice that's still thinning. You may want to look for an account upgrade or a new host before the problem gets completely out of hand. You would also want to pluck a myPhpAdmin backup for insurance against any catastrophic failure (whether via server or via booboo).

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by Dunald » Fri Apr 26, 2013 3:21 pm

OK, thanks for your advice.
Well I can make a backup without the products, the problem is that when I sometimes import products I put products in the wrong category and the backup file helped me with this. However now I understand that I can use my import tool and rewrite the import again and solve this problem.
The problem with my backup file may not be that big, as I can use myPhpAdmin for this, may only take a minute longer.
Well I also have some other problems with my host http://www.one.com as my site doesn´t perform well/fast with this host. So perhaps I need to change to another host. - Thanks again for your support :yahoo:

Active Member

Posts

Joined
Tue Mar 15, 2011 9:05 pm

Post by butte » Sat Apr 27, 2013 12:10 am

It occurred to me overnight that you can trim some of the database size, if you haven't already done this, by both uninstalling all of the default categories and products, and deleting all of the default product pictures from image/ and if any still lurk in it also from database.

Given that "one" I would switch. See PM as to hosts.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by rph » Sun Apr 28, 2013 1:06 am

I've attached a vqmod script for improved backup files. Table structure/creation still needs to be properly dealt with but it will dramatically reduce the size of backup files.

Edit: I've now added a database structure backup so if the backup table doesn't exist it will be created. This should protect against catastrophic DB failures while not effecting the normal truncate function.

On the default store data, backup file size went from 684 KB to 311 KB (55% smaller).
improved_backup.png

improved_backup.png (19.68 KiB) Viewed 5015 times


-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska
Who is online

Users browsing this forum: No registered users and 50 guests