PDA

View Full Version : PhpMyAdmin and Large Database backups



visible soul
06-12-2004, 12:13 AM
Hello again. I have successfully used PhpMyAdmin to export my smaller databases on numerous occasions. But I have a IPB forum database that I can't seem to export because of it's size. One of the tables (ibf_posts) is over 3MB and it just hangs when I try to export it.

Is there some modification that I should do to my php.ini file or some other file to enable export of larger files?

Thanks for any help you can give me.

-DKC-

FZ
06-12-2004, 09:18 AM
Hmm, the first (and so far only) thing that would seem (to me) to be the cause would be that the default maximum script execution time is exceeded. (I'm only guessing here, not an expert on this matter).

If that is the case you can try editing your php.ini and increasing the "max_execution_time" (~ line 206). Just to test whether it is the cause, you could temporarily increase it to, say, 300 or something.

Also, by "it just hangs", do you mean that the page never finishes loading, or does it appear to have finished loading but is blank? Are you exporting to the browser window or to a file?

visible soul
06-12-2004, 11:33 AM
Thanks for the reply FZ.

I increased the "max_execution_time" to 300 (default was 30) but unfortunately it didn't help.


Also, by "it just hangs", do you mean that the page never finishes loading, or does it appear to have finished loading but is blank?
I was able to backup my larger tables by breaking them down into approximately 2MB sections. If I try to export anything bigger than that IE6 gives me a new screen with the error "Server localhost # No tables found in database." (There are actually 44 tables). Firefox 0.8 stays on the same screen and the status bar says "done" after 2 or 3 seconds but no matter how long I wait no download prompt is displayed.


Are you exporting to the browser window or to a file?
I am exporting via my browser and saving the .sql.zip or .sql.gz file on my home computer



I have no problem backing up my whole VDS using the vdsbackup.cgi so I'm guessing the problem is some configuration value for php or PhpMyAdmin. I'll keep looking. :mrgreen:

-DKC-

wildjokerdesign
06-12-2004, 02:36 PM
Have you tried changeing the memory_limit = 8M to a higher limit? Not sure if that would help any or not but was on thought of mine. The other is since the under 2mb seem to work I wonder if it could be the upload_max_filesize = 2M? You might try to increase that. It really does not make since but might be worth a try. Also instead of using the save to option I have in the past been able to get it to print the sql out to the screen when it would not save it to my local machine. Then did a cut and past to my local editor. It was never on a table that big though.

I have a feeling it may be someplace else that this is controlled since I recall from awhile back another user haveing problems and trying to change the php.ini. The issue was never resolved for them or if it was they never came back to let us know.

FZ
06-12-2004, 03:21 PM
Hmm, interesting. I have a feeling the problem here has to do with the fact that a timeout is occuring somewhere: either your browser does not wait long enough for the file, or the script itself is timing out.

There are a couple of things you can do:

1. Turn on PHP error logging in your PHP.ini (if not already enabled), and set it to E_ALL or something that will enable verbose logging. Try the whole process again and then see if the logging can point to the culprit.

2. Try checking the Apache error logs for any useful info.

3. Maybe this is a known bug in PHPMyAdmin that has a way around it? Have you checked the site? Are you using the latest version of PHPMyAdmin? The versions of apps in the Site Manager tend to be quite old. The latest version is 2.5.7.

4. Is there a a way to save the file locally instead of sending it to the browser as it is created/generated? It seems that is the stage at which the problem manifests itself. Alternatively, can you not do something similar with mySQL itself (an export or dump)?

5. If it is a timeout issue, I think the only way around it would be to use output buffering... Check and see if PHPMyAdmin has that option in its config file and enable it if disabled.

6. Try what Shawn says... It may be that sending straight SQL to your browser would not cause the timeout to occur, although you'll have to download the full 3MB of data + overhead for the statements, etc.

Let us know how it goes.

FZ
06-12-2004, 03:28 PM
Further proof for using the newest version and then trying again: http://www.threadsdev.com/forum/showflat.php/Cat/0/Number/88844/Main/87206

visible soul
06-15-2004, 11:10 AM
You guys are the best. I will try your suggestions soon (I need a few 44 hour days :)) and I will come back and let you know if I get things working correctly.

Thanks.
-DKC-

visible soul
08-01-2004, 03:38 AM
:idea: :!: Success! :D (I bet you thought I wouldn't come back :oops: )

I finally had an opportunity to work on my problem.

Here's what did NOT work:

php.ini

max_execution_time = 300 ; Maximum execution time of each script, in seconds (note: default was 30)


memory_limit = 20M ; Maximum amount of memory a script may consume (note: default was 8M)


upload_max_filesize = 20M (note: default was 2M)

phpMyAdmin config.inc.php

$cfg['ExecTimeLimit'] = 0; // maximum execution time in seconds (0 for no limit) (note: 300 was default)

Here's what DID work:

I took FZ's advice and upgraded phpMyAdmin from 2.4.0 to 2.5.7-pl1 (It was much easier than I had anticipated). After the upgrade I was able to export the problem table (7MB) as an .sql file. I was still not able to export it as a zipped or gzipped file. After that milestone I successfully exported my whole forum database (31MB) with no problem. I was able zip and gzip smaller tables. I would like to know what the compression issue is but that's not a big deal to me at this point.

Just for fun :twisted: I "re-installed the old phpMyAdmin 2.4.0 and I was unable to successfully use it to export large files using the exact same settings. So it looks like the upgrade was a good move for me.
----------------------------------------------------------------

If you want to upgrade phpMyAdmin but you're the paranoid type like me here's what I did. (Correct me if I'm wrong or if I left out anything.)

Go to http://www.phpmyadmin.net/home_page/ and download the latest stable version.
Backup the config.inc.php file currently on the server.
Use the configuration values from your old config.inc.php file to correctly configure the new config.inc.php
Re-name your existing "PhpMyAdmin" directory something like "PhpMyAdmin.old"
Create a new "PhpMyAdmin" directory.
Upload the new files into the new "PhpMyAdmin" directory
Copy the .htaccess file from the old directory and use it for the new directory
Log into your new phpMyAdmin.

If you have any problems you can always revert back to the old installation.
----------------------------------------------------------------

Thanks again to everyone who helps out around here.

FZ
08-01-2004, 10:50 AM
Glad you got it to work.

owen100
10-01-2004, 05:17 PM
I had the same problem with a VB3 database but I uploaded it as a zip file to my doc root, unzipped it and ran the following SSH command to restore it

mysql -u dbusername -p databasename < /path/to/backupname.sql

Armadillo
10-03-2004, 12:47 AM
Thanks for the upgrade instructions Visible Soul.
I just upgraded mine to phpMyAdmin-2.6.0-p1.
It didnt like the reconfigured config file so I used the old one.
:D

visible soul
10-04-2004, 01:03 AM
I had the same problem with a VB3 database but I uploaded it as a zip file to my doc root, unzipped it and ran the following SSH command to restore it

mysql -u dbusername -p databasename < /path/to/backupname.sql
That's the same way I use to restore large databases. The backup with phpMyAdmin works but the restore doesn't work for me when the sql file is large.


Thanks for the upgrade instructions Visible Soul.
I just upgraded mine to phpMyAdmin-2.6.0-p1.
It didnt like the reconfigured config file so I used the old one.
:D
I think this forum is a great resource. I refer to it often. If everyone who resolved a problem posted the solution we wouldn't need no stinkin' manual. ;)

Armadillo
10-09-2004, 11:59 PM
You got that right. This place is a wealth of information.
Oh, and I figured out why it didnt like my reconfigured config file. I forgot to include some code that (I guess) the Westhosties put in there that gets the password. The new config file functions now.
:)

lbrown13
12-21-2004, 12:46 PM
You could try changing the max allowed packet to about 10 Mb,
save your php.ini, and then restart apache.


Larry

isastrillo
04-30-2011, 01:59 AM
I need help from anyone, how to upgrade phpmyadmin? Can anyone please give me the guidelines. Thanks in advance.

wildjokerdesign
04-30-2011, 06:42 AM
On a cPanel account you don't have control of upgrades it is done for you. On an older Site Manager account you do it via the Site Manager where you installed it if WestHost has supplied a new version of it. If you have manually installed it then you would follow the documentation at the phpMyAdmin site itslef. http://www.phpmyadmin.net/home_page/index.php