this may be a topic nige and i only converse on, not sure yet.

Magento. Need to set up a dev version of the site.

db is 400MB small.

got a copy off the host, but its got extended inserts.

using bigdump.php, upped the number of lines it can handle, but i get an error like this

http://www.magentocommerce.com/boards/viewthread/505110/

anyone know what that vague error message means.

Matt

First off, to make your life much easier I would consider splitting the original db up into chunks http://www.sqldumpsplitter.com/

It will make life easier because you can simply import in blocks and then deal with any problematic area when you encounter it. You could split it manually by table insert but that program is a quick and easy solution.

The likelihood on this is that its trying to insert twice somewhere or the table has already been created - maybe the schema is supposed to be updated somewhere and isn't because of an IF NOT EXISTS. You could check the table structure with DESCRIBE my_table; then you could try dropping the table and then recreating it.

good find, i'll check that app out tonight. looks like it just makes smaller files to insert though (smaller in file size, not size of insert) so might not help here.

the guy who writes bigdump will split it up for you, for 39$, i wonder if he just uses that app. i'll bet not though, i reckon he has a script that splits up inserts. which ought to be easy to replicate actually, its only parsing an admittedly huge textfile.

i read the same from that error message as you, but i think its the product id thats causing the issue. im deleting the whole db then creating an empty db with no tables in before running this restore. i have to do this as the export has no drops in.

i can't put drops in as the db is so big, actually creating the backup times the server out and kills the guys website. not ideal. he's given me access to tsohost to be able to discuss his site with them, so one plan of attack is to ask then via a support call for help, either to run the backup for me, or to ensure it doesn't run out of ram for this.

Nige, you got any magento experience then? I've shied away from it til i made this rash promise to a mate.

Magento is the only one I've not really used, I probably should have gotten into it at some point but I work with so much bespoke software its not been necessary. The last contract I did they were all about Magento in the office but I never toyed with it as I was working on a different project, I can't imagine it is tricky to pick up.

I never asked, but I take it this is 400MB uncompressed? A 400MB compressed db would be scary large. It might be worth checking if there's stats/logs tables etc that you could afford to lose too that would significantly decrease the file size. You could also either split the inserts as you mentioned or try to reform them into complete inserts (or even complete+extended) as either might prove more reliable on insertion. All of that is considerable work I think, hopefully you can avoid that.

Maybe see if you can export the database table by table instead of one massive dump (no one likes to have to strain  ) if thats possible.

you've raised a point i've been mulling over. do i really need the url logs et al. theres 5 million url log file rows of a 15 million row database.

im gonna try and drag everything but those rows down i think.

wow. actual database size is 2.5GB. downloaded sql file is 400MB.


SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;


just found an article on setting up log maintenance, since magento doesn't do it manually. so i've configured it to do some at half past.

That's shockingly big, it seems like very poor management in Magento if dbs can grow to that size.

one of the mods on the official magento forum proudly proclaims his db grew to 32GB before compression.

how in gods name can that be restorable in any amount of time?

That's sloppy. No db should ever be that big - that's down to poorly written software.

Your error is because you have two rows with the same key. That shouldn't really occur unless the schema you're importing into is different to the one you exported from or the export is corrupt.

You could either:

- Fix the export so it exactly represents what's on your source db.
- Find that key that is duplicated and remove it from the dump file.
- Edit the import schema to relax the key constraints and then remove the row later and restore key contraints.

thanks sam. heres where i got to.
SET FOREIGN_KEY_CHECKS=0;

this didn't seem to work.

so i adjusted the export, by excluding the tables i was getting issues with. this has worked, but i have to restart the db import about 7 times to get it in. its not right, by any stretch.

I got to the mac this morning, having finally got the design imported to the site and it was asking me to go through the post install config again. not sure why, but im rapidly running out of patience with this. Magento is a bit unfriendly.