Page 1 of 2 12 LastLast
Results 1 to 10 of 16
  1. #1
    Senior Member visible soul's Avatar
    Join Date
    Sep 2003
    Location
    Corpus Christi
    Posts
    111

    Default PhpMyAdmin and Large Database backups

    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-
    "Beware of all enterprises that require new clothes." -Henry David Thoreau

  2. #2
    Senior Member FZ's Avatar
    Join Date
    May 2003
    Location
    Johannesburg, South Africa
    Posts
    1,024

    Default

    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?
    Fayez Zaheer

  3. #3
    Senior Member visible soul's Avatar
    Join Date
    Sep 2003
    Location
    Corpus Christi
    Posts
    111

    Default

    Thanks for the reply FZ.

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

    Quote Originally Posted by FZ
    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.

    Quote Originally Posted by FZ
    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-
    "Beware of all enterprises that require new clothes." -Henry David Thoreau

  4. #4
    Moderator wildjokerdesign's Avatar
    Join Date
    Jun 2003
    Location
    Kansas City Mo
    Posts
    5,721

    Default

    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.
    Shawn
    Please remember your charity of choice: http://www.redcross.org

    Handy Links: wildjokerdesign.net | Plain Text Editors: EditPlus | Crimson

  5. #5
    Senior Member FZ's Avatar
    Join Date
    May 2003
    Location
    Johannesburg, South Africa
    Posts
    1,024

    Default

    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.
    Fayez Zaheer

  6. #6
    Senior Member FZ's Avatar
    Join Date
    May 2003
    Location
    Johannesburg, South Africa
    Posts
    1,024

    Default

    Further proof for using the newest version and then trying again: http://www.threadsdev.com/forum/show...844/Main/87206
    Fayez Zaheer

  7. #7
    Senior Member visible soul's Avatar
    Join Date
    Sep 2003
    Location
    Corpus Christi
    Posts
    111

    Default

    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-
    "Beware of all enterprises that require new clothes." -Henry David Thoreau

  8. #8
    Senior Member visible soul's Avatar
    Join Date
    Sep 2003
    Location
    Corpus Christi
    Posts
    111

    Default Success!

    :idea: :!: Success! (I bet you thought I wouldn't come back ops: )

    I finally had an opportunity to work on my problem.

    Here's what did NOT work:

    php.ini
    Code:
    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
    Code:
    $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.)

    1. Go to http://www.phpmyadmin.net/home_page/ and download the latest stable version.
    2. Backup the config.inc.php file currently on the server.
    3. Use the configuration values from your old config.inc.php file to correctly configure the new config.inc.php
    4. Re-name your existing "PhpMyAdmin" directory something like "PhpMyAdmin.old"
    5. Create a new "PhpMyAdmin" directory.
    6. Upload the new files into the new "PhpMyAdmin" directory
    7. Copy the .htaccess file from the old directory and use it for the new directory
    8. 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.
    "Beware of all enterprises that require new clothes." -Henry David Thoreau

  9. #9
    Senior Member FZ's Avatar
    Join Date
    May 2003
    Location
    Johannesburg, South Africa
    Posts
    1,024

    Default

    Glad you got it to work.
    Fayez Zaheer

  10. #10
    Junior Member
    Join Date
    Sep 2004
    Posts
    19

    Default

    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

Similar Threads

  1. Moving a Large Database
    By dvduval in forum PHP / MySQL
    Replies: 0
    Last Post: 06-27-2005, 09:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •