How do I import my Magento database? (memory errors)

December 22, 2012 1:16 AM

I am trying to import a Magento database in phpMyAdmin but keep getting an exhausted memory error.

I am in MySQL hell.

I had a Magento CE 1.5 installation, and I am now trying to use my webhost's automated system to install Magento CE 1.7, and then replace the newly created database's contents with the SQL backup file made of the 1.5 database.

I tried to just import the old sql base files into the new database, but apparently phpMyAdmin can't just "replace" existing data with new data, so I wiped all the tables and am trying to use Import to bring in the gzipped backup.

However, I keep getting this error:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 214237 bytes) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/import/csv.php on line 356

I don't have access to the file the error message quotes.

This is from my attempt at importing in CSV, the same happens if I try the SQL format version.

The database unzipped is 300 MB, and its gzipped version is 40 MB.

The tools at my disposal are FTP and phpMyAdmin. I don't know if have SSH access, and I'd be all thumbs with it if I did because I have never used it. Is this an out of memory type situation due to such a large database? What can I do?

posted by Unhyper to Computers & Internet (2 answers total)

Best answer: Yes, this is phpmyadmin running out of memory trying to parse your input file.

Working with Magento without SSH access is pretty much going to be a nightmare longterm, so definitely worth setting if you have access - cpanel often has ssh details in it. If you can get ssh access, then importing a sql file into a databse can be as simple as

mysql my_db_name < my_sql_file.sql

One option might be splitting up the SQL backup file - this can be pretty ugly too depending on what editors you have available. Another option might be installing mysql locally, importing it into there, and then doing a new dump without a bunch of the log tables - on our Magento installs, 90% of your average dump is the contents of log_url, log_url_info, log_visitor_info, log_visitor, report_event, report_viewed_product_export and the like, which is generally not that interesting or useful data in the age of google analytics.
posted by xiw at 1:57 AM on December 22, 2012

Response by poster: I took your advice and looked into it, downloaded Putty and managed to log in via SSH. I'll try the command you supplied and see where this adventure takes me.
posted by Unhyper at 3:09 AM on December 22, 2012