Restoring a DB - need help!

[minuteman1970]

I am running Xampp locally, with the idea of doing most of my OSc configuration offline and later uploading the DB to my hosted store. After adding 2600 products to my empty local shop, I ran a DB backup getting a 4 meg SQL file. I am having difficulty in uploading it to my hosted shop, and usually run into timeouts due to the file size.

I used winzip to compress the SQL file down to about 300k, but when I import the zipped DB using phpmyadmin I only end up with empty categories. In some cases, the import fails altogether. I have also tried using the 'restore' feature within the OSc admin panel, but haven't had any luck.
I am looking for some guidance as to the best way to handle this situation.
Thanks in advance.

[michael_s]

There are tthree options that will work.

1. If you have SSH access, run the sql file on the server directly from the mysql command line. This will avoid all timouts and be very fast, since everything is done server side. To accomplish this, you FTP your sql file to your account root. Then SSH to your account root and login to mysql. From the mysql command line, run the sql batch file on your db. Fast and furious, your db will be imported in less than 30 seconds

2. Open the sql file locally in a text editor and cut parts of it from the file. Then paste the cut piece into phpmyadmin sql section. Submit. Make sure to use pieces that are not too big so as not to time out. Repeat until the original sql file you opened in a text editor is empty.

3. Get mysqlfront or sqlyog or some other mysql front end and configure it to connect to your db remotely. Execute the sql in one of these programs remotely. It will not timeout, since it directly connects to mysql. To do this you must be able to allow remote access to your db.

[MindTwist]

I would first check the upload_max_size on your target host PHP config.

You can do so by creating a file phpinfo.php with the following content and uploading it to your site. Then navigate to yoursite.com/phpinfo.php and look for the string upload_max_filesize

<?php
phpinfo();
?>

If that is the problem, you might try to get the upload_max_size to 8M.

If you try to import via phpmyadmin with the DB on a ZIP compressed, I would also try to compress it with an older version of Winzip (or even PKZIP on a DOS window), since with a new version you might be using a newer compression algorithm that your host can not uncompress ok.

With OSCMAX you can also FTP upload it to /admin/backups uncompressed and import it from the admin panel.

Spliting the SQL file into pieces should also do the trick.

[minuteman1970]

Guys,
Thanks for the tips. I actually put a call into my hosting company; it turns out that they limit DB sizes to 2 megs which I am able to restore myself. I submitted a request for a restore, but am still waiting.

I tried a restore of the uncompressed file via the OScMax admin panel, but it failed (likely due to the size issue).

When I compressed the file, I had used the Windows XP internal ZIP feature to create the file, as opposed to the actual winzip program. The result was a (sometimes) successful import, but I was only left with empty categories and no products.

[MindTwist]

Just found out you have the phpinfo() info handy on OSCMAX on the admin panel, under tools/server info.

If they have a 2M upload max on PHP, I can understand that. If they have a 2M max on SQL databases, I think that is way too small, I would complain and ask for that limit to be removed.

My old host had 8M upload_max_size which was great for running squirrelmail and sending emails with big attachments, on the new one I have for my store I just noticed I am limited to 2M.

At least my DB is only about 900k uncompressed at the moment, so no big deal for restoring via phpmyadmin