Problem with restoring database backup

[Irin]

Hi,

 

I got a problem with restoring my database backup in oscommerce/admin/backup. I started to restore backup and I got the following message:

 

CGI Timeout

The specified CGI application exceeded the allowed time for processing. The server has deleted the process.

 

And it damages my database. I don't get most of the tables in my database. What can be wrong?

 

Thanks a lot,

Irina.

[Jack_mcs]

The most likely reason is that the database is too large to be uploaded that way. Each server has a time that it will allow you to do such things and once the time is reached, it cuts you off. You should be able to contact your host and ask them to load the database for you. If they won't do that, then ou would need to use an external program to load it. That will not be an easy solution, unless you are familiar with setting up such programs, so getting your host to do it is the best solution.

 

Jack

[Irin]

The most likely reason is that the database is too large to be uploaded that way. Each server has a time that it will allow you to do such things and once the time is reached, it cuts you off. You should be able to contact your host and ask them to load the database for you. If they won't do that, then ou would need to use an external program to load it. That will not be an easy solution, unless you are familiar with setting up such programs, so getting your host to do it is the best solution.

 

Jack

Thanks for your reply, Jack. I already contacted my provider and they restored my database. But that's not the perfect solution if I need to restore my database again. Is there any way I could set the allowed time for this operation to higher?

 

Thanks.

[AlanR]

Thanks for your reply, Jack. I already contacted my provider and they restored my database. But that's not the perfect solution if I need to restore my database again. Is there any way I could set the allowed time for this operation to higher?

 

Thanks.

Are you using the compressed option for the database backup?

 

It saves a lot of time on uploads if you're uploading from your local PC. Another workaround is using phpMyAdmin to restore the backup. It has an option which you can set to use a server directory as the upload (import) source.

[Irin]

Are you using the compressed option for the database backup?

 

It saves a lot of time on uploads if you're uploading from your local PC. Another workaround is using phpMyAdmin to restore the backup. It has an option which you can set to use a server directory as the upload (import) source.

I don't use compression option for the database backup. I have "No Compression (Pure SQL)" selected and no option to cancel this selection. Of course I can use phpMyAdmin to restore the backup but it sets max allowed file size to 2Mb. Mine is always bigger and this option doesn't work for me either. Is there any config file in oscommerce where I can set the allowed time for restore backup operation?

 

Thanks.

[kwalker]

If you have ssh or ftp access to your database, you can easily download the complete database without any timeouts, work on it and re-upload it, again without any timeouts.

 

 

Kevin

[Jack_mcs]

Thanks for your reply, Jack. I already contacted my provider and they restored my database. But that's not the perfect solution if I need to restore my database again. Is there any way I could set the allowed time for this operation to higher?

 

Thanks.

There are a number of solutions - here are a few. Using the mysql shell will do it but it can be cryptic if you are not used to it. There is a program named bigdump that does the same thing for you and is easier to use. And there is a program named SQLyog that has a nice interface. They are freely available as downloads from the web.

 

Jack

[Guest]

Another way to do it via phpMyAdmin, which takes some time, but you can definitely do it yourself, is to upload it a few tables at a time. You can open up your backup .sql file in any text editor. Search for "drop table", copy a few tables at a time, and paste them into the SQL window in phpMyAdmin.

 

Definitely not the best solution, but often faster than calling your web hosting company to get them to do it.

 

-jared

[AlanR]

Of course I can use phpMyAdmin to restore the backup but it sets max allowed file size to 2Mb. Mine is always bigger and this option doesn't work for me either. Is there any config file in oscommerce where I can set the allowed time for restore backup operation?

phpMyAdmin 2.7.0 has an 8Mb limit. Plus you can always use the import from server directory option.

[AlanR]

I don't use compression option for the database backup. I have "No Compression (Pure SQL)" selected and no option to cancel this selection.

Here's the way to make the compression option available in the osC backup utility.

 

(I'm assuming a *nix server.)

 

Find in: admin/includes/application_top.php

 

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/usr/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/usr/bin/gunzip');

define('LOCAL_EXE_ZIP', '/usr/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/usr/local/bin/unzip');

 

Change to (remove /usr from the first two lines):

 

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/bin/gunzip');

define('LOCAL_EXE_ZIP', '/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/local/bin/unzip');

 

The gzip option in the backup manager should then appear.

[Irin]

Thanks for all your replies, guys. Sorry for not mentioning my server, it's Windows. My phpMyAdmin version is 2.6.0-pl3 and my provider doesn't plan to update it, because he says that newest versions are full of bugs. Don't know. I tried to upload my database by few tables at a time by using Microsoft Notepad. Just copy and paste them into the SQL window in phpMyAdmin. But, I would say, that's awful. It takes a long time to copy and paste 2Mb file. That's definitely not a perfect solution. I can download my database .sql file with ftp but I don't know how can I restore it by ftp.

How can I enable gzip option in the backup manager on a Windows server? May I use the same code as for *nix?

Find in: admin/includes/application_top.php

 

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/usr/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/usr/bin/gunzip');

define('LOCAL_EXE_ZIP', '/usr/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/usr/local/bin/unzip');

 

Change to (remove /usr from the first two lines):

 

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/bin/gunzip');

define('LOCAL_EXE_ZIP', '/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/local/bin/unzip');

Thanks a lot.

[Jack_mcs]

Editing the file like that is probably not going to do it for you since the majority of the size is probably in one or two tables. But here are a few things to try. FIrst, go into phpmyadmin, select all of the tables and choose optimize. The tables are usually already optimized but it won't hurt to do this. Then select the sessions tables and empty it (don't drop it). Do the same to any other table that the contents don't matter. If the size of the database is still too large, you can export parts of it at a time. Go to export and select the first 20 or so tables and export using the full (structure and data) method (usually the default). If the size of the saved file is under 1 MB (just a guess - may be more or less), then you should be able to upload that using phpmyadmin. Then do another export starting with the next set of tables. The majority of the size will be in the orders and products tables so you may have to do those individually. Using this procedure, you can do the whole database is 10 minutes or so, once you get used to the procedure and providing the sections are still not too large.

 

Jack

[AlanR]

Thanks for all your replies, guys. Sorry for not mentioning my server, it's Windows. My phpMyAdmin version is 2.6.0-pl3 and my provider doesn't plan to update it, because he says that newest versions are full of bugs.

 

He's making excuses. 2.6.0 is way out of date. You can download and install 2.7.0 all by yourself.

 

How can I enable gzip option in the backup manager on a Windows server? May I use the same code as for *nix?

 

Thanks a lot.

Try setting

 

define('LOCAL_EXE_ZIP', '/bin/zip');

define('LOCAL_EXE_UNZIP', '/bin/unzip');

 

in your application_top.php. That may make zip compression available.

 

I have no experience with Windows servers.

[Irin]

He's making excuses. 2.6.0 is way out of date. You can download and install 2.7.0 all by yourself.

Try setting

 

define('LOCAL_EXE_ZIP', '/bin/zip');

define('LOCAL_EXE_UNZIP', '/bin/unzip');

 

in your application_top.php. That may make zip compression available.

 

I have no experience with Windows servers.

Where can I download 2.7.0? Could you please provide me with a reference. How can I install it? I'm not familiar with updating phpMyAdmin, have never done it before.

 

Thanks.

[AlanR]

Where can I download 2.7.0? Could you please provide me with a reference. How can I install it? I'm not familiar with updating phpMyAdmin, have never done it before.

 

Thanks.

http://phpmyadmin.net

 

Their instructions are a little cryptic but you'll figure it out, everyone does. It has an installer script now but if you have trouble come back here. I can post a sample config.inc.php that will get you going.

[Irin]

http://phpmyadmin.net

 

Their instructions are a little cryptic but you'll figure it out, everyone does. It has an installer script now but if you have trouble come back here. I can post a sample config.inc.php that will get you going.

Unfortunately, I cannot change the PHPMyAdmin because that is installed by plesk and plesk uses that version. The only I can do is to install phpmyadmin in another directory in my website. Then I would need to redirect all the references to my database to a different folder. I'll may be try to enable gzip option in the backup manager if it'll work on my server.

 

Thanks.

[Irin]

Unfortunately, I cannot change the PHPMyAdmin because that is installed by plesk and plesk uses that version. The only I can do is to install phpmyadmin in another directory in my website. Then I would need to redirect all the references to my database to a different folder. I'll may be try to enable gzip option in the backup manager if it'll work on my server.

 

Thanks.

In my admin/includes/application_top.php I have define('LOCAL_EXE_ZIP', '/bin/zip'); and define('LOCAL_EXE_UNZIP', '/bin/unzip'); already set up. The code is:

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/usr/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/usr/bin/gunzip');

define('LOCAL_EXE_ZIP', '/usr/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/usr/local/bin/unzip');

What should I change to enable gzip option?

 

Thanks.

[AlanR]

Unfortunately, I cannot change the PHPMyAdmin because that is installed by plesk and plesk uses that version.

Not so... phpMyAdmin is simply an application installed like osC. When you use it from your control panel you're simpy using a shared version, available to all users on the server.

 

The only I can do is to install phpmyadmin in another directory in my website. Then I would need to redirect all the references to my database to a different folder. I'll may be try to enable gzip option in the backup manager if it'll work on my server.

Of course, you can install it in any directory you wish. Just make sure access to it is protected like your osC admin section. It doesn't care which folder it's in, all it needs are the database details just like osC.

 

You can have it use your osC backups folder as an export and an import folder if you wish.

 

like so...

 

$cfg['UploadDir'] = '/sumotherdir/somedir/htdocs/pma/import/';

$cfg['SaveDir'] = '/sumotherdir/somedir/htdocs/pma/export/';

 

I don't know how to get the compressed options working on your server if the suggestions I've given you already don't work. Maybe someone else has some ideas about Windows servers.

[Irin]

Not so... phpMyAdmin is simply an application installed like osC. When you use it from your control panel you're simpy using a shared version, available to all users on the server.

This is what my provider says. May be he's just making excuses for not willing to update it.

 

Of course, you can install it in any directory you wish. Just make sure access to it is protected like your osC admin section. It doesn't care which folder it's in, all it needs are the database details just like osC.

 

You can have it use your osC backups folder as an export and an import folder if you wish.

 

like so...

 

$cfg['UploadDir'] = '/sumotherdir/somedir/htdocs/pma/import/';

$cfg['SaveDir'] = '/sumotherdir/somedir/htdocs/pma/export/';

 

I don't know how to get the compressed options working on your server if the suggestions I've given you already don't work. Maybe someone else has some ideas about Windows servers.

I tried to make changes in admin/includes/application_top.php just as you suggested:

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/usr/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/usr/bin/gunzip');

define('LOCAL_EXE_ZIP', '/usr/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/usr/local/bin/unzip');

 

Change to (remove /usr from the first two lines):

 

// Used in the "Backup Manager" to compress backups

define('LOCAL_EXE_GZIP', '/bin/gzip');

define('LOCAL_EXE_GUNZIP', '/bin/gunzip');

define('LOCAL_EXE_ZIP', '/local/bin/zip');

define('LOCAL_EXE_UNZIP', '/local/bin/unzip');

but it didn't enable gzip option in my backup manager. I still have No Compression (Pure SQL) selected.

 

Thanks.

[MaBiSmAd]

This is what my provider says. May be he's just making excuses for not willing to update it.

I tried to make changes in admin/includes/application_top.php just as you suggested:

 

but it didn't enable gzip option in my backup manager. I still have No Compression (Pure SQL) selected.

 

Thanks.

 

The mysql administrator program might be what you want -(http://dev.mysql.com/downloads/administrator/index.html) This is a Windows application from the makers of mysql. It will run on your local Windows computer, connects to the mysql server (you will need the IP address of the server and your database's user name and password.) I don't know if it has a file size limit, but I use it to backup/restore and move databases around. As it is not browser or PHP based, it should not experience the timeout problem.

[Irin]

The mysql administrator program might be what you want -(http://dev.mysql.com/downloads/administrator/index.html) This is a Windows application from the makers of mysql. It will run on your local Windows computer, connects to the mysql server (you will need the IP address of the server and your database's user name and password.) I don't know if it has a file size limit, but I use it to backup/restore and move databases around. As it is not browser or PHP based, it should not experience the timeout problem.

I've installed that mysql administrator program from dev.mysql.com, but somehow it can't establish connection with my server. I'm getting MYSQL Administrator Error:

Could not connect to the specified instance.

MYSQL Error Number 2003

Can't connect to MYSQL server on 'localhost' (10061)

If you want to check the network connection, please click the Ping button.

 

OK Ping

I don't know what's wrong with either program or my database. What is this MYSQL Error 2003 means? How can I correct this problem?

 

Thanks.