Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0

    Import MySQL dump


    I am having some problems importing a mySQL dump and would appreciate someone pointing out what i am missing. I have a working mySQL /php /apache install and want to import that dump into an empty database. That should be simple, right?

    Code:
    mysql> grant all on newdatabase.* to root@localhost;
    mysql> create database newdatabase; 
    mysql> use newdatabase; 
    mysql> show tables;      
           Empty set (0.00 sec) 
    mysql> quit; 
    
    $ mysql -u root -p newdatabase < c:\path\dump.sql 
    $      Enter password: ***********************
    And that apparently works.. I can see the database files being created in the data directory. However, it is painfully slow and importing a 3 GB dump appears to only be ~ 30% done after 36 hours. I can't run the entire import as a single session and couldn't find how to pause and resume the import (anyone?).

    Until I find a way to pause /resume an import, I tried splitting the dump into ten smaller files as an alternative, taking care to make sure that the split only happens after a statement is fully complete. However, the split files don't seem to import. MySQL accepts the commands and sits blinking at the command prompt. But of course it doesn't show any kind of progress indicator.. and I don't see any database files being created in the data directory. Nor any errors in the mySQL.err log.

    I tried restarting mySQL, adding headers to each file split and some other variations, but it does not appear that split files will import.

    So, any ideas on..

    - what I might be doing wrong?
    - how to pause and resume a long SQL import session?
    - why split files won't import?
    - how to troubleshoot the failed import in the absence of error messages?

    Any insight or suggestions greatly appreciated.



    -
    mySQL 5.5 /php 5.3.5 /apache 2.2




    -
  2. #2
  3. Old Fart
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Chicago
    Posts
    106
    Rep Power
    4
    Did you create dump.sql with the mysqldump command?
    If so did you use the --opt option on your mysqldump command?
    How many indexes do you have on your tables?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Code:
    mysqldump --opt --user=root --password newdatabase > newdatabase.sql
    Thank you for the reply.

    Yes, the dump was made with mysqldump (v. 10.13). And the database is heavily indexed. About 25 indexes on 300 tables. Three of the tables are > 500 MB each. The dump was made from an ubuntu install and I am restoring it to a local windows development PC.

    I had a closer look at the restore process and my assumption that the ~ 30% import progress in 36 hours was linear was not correct. The apparent ~ 30% progress in creating the database .frm /.MYD /.MYI files is achieved in the first few minutes. After that, mySQL continues to use significant processor and is doing a lot of file reads and (temp) file writes. But I don't see any actual dbase files continue to be written to the mySQL data directory.

    Which makes it unclear to me if the restore is hanging or in a loop? Or if I simply need to be patient while the tables and indexes are processed. There is no help in the error logs, no explicit errors at all. The apparent stall occurs while importing an 800 MB table.

    So I suppose back to Google to try and discern how I can tell if this is hung or simply slow.


    -
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Code:
    mysql> show full processlist;
    
     ID User  Host            db           Command Time State Info
     3  root  localhost:2320  newdatabase  Query     0  NULL  show full processlist
     4  root  localhost:3031  newdatabase  Sleep  6489        NULL
    <sigh>

    Apparently hung then. And no error messages to be found anywhere. I'll try removing that table and see if the remainder of the dump restores properly.

    Any suggestions as to common causes of mySQL import hangs?


    -
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    This is frustrating because there aren't any bread crumbs to follow.

    Removing the 800 MB table gives better results with perhaps half of the database being imported. Where it appears that the process hangs on yet another big table. This is hardly a good solution though.. remove all of the large tables and settle for half of the database being imported?

    Again no errors reported on the command line and nothing in the mySQL log that is generated in the mySQL data directory. Also nothing in the system log or event viewer. There is an "ib_logfile0", but it is hex encoded and as far as I can discern a scratchpad or temp file that the innodb engine uses when building tables - so not (easily) human readable and unlikely to further narrow down the problem.

    There must be some general procedure or method to identify if a dump import is successful /complete? Hopefully something that would reference errors or otherwise give hints as to what the problem might be if the import fails. I am not finding much on Google though. And the 800 MB table looks fine on visual inspection. So it's not obvious what obscure bit in 800 MB of data is causing the import to hang.

    In the alternative, what common problems tend to trip people up? Lacking a better alternative, I guess I can pursue common stumbling blocks one by one. As I am not finding much on best practices for mySQL.. maybe there are some third party syntax checkers or debug tools? Not loving mySQL very much at the moment, but open to ideas on how to best proceed.


    mutters while feeding Google more & more creative keywords..



    -
  10. #6
  11. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    You should not need to grant rights to your MySQL root user. I'm curious if you have a reason for doing that.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Originally Posted by Nilpo
    You should not need to grant rights to your MySQL root user. I'm curious if you have a reason for doing that.
    Warning: mysql_connect(): Access denied for user: 'root@localhost' (Using password: YES) in
    /path/to/newdatabase.sql ...
    Cannnot connect to DB server

    To avoid access errors and concentrate on the primary concern i.e. how to identify and resolve (presumed) import parsing issues.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    The import appears to "succeed" if the three largest tables are removed from the dump. Which is small consolation as they comprise two thirds of the database or about ~ 2 GB of data.

    The only clue I have is a CLI message
    ERROR 2006 (HY000) at line 6361; MySQL server has gone away

    The line number isn't consistent if the import is rerun. But apparently MySQL is failing at that approximate location, give or take a few lines each time. This error is seen only after the first two large tables were removed from the dump. Of course there are no apparent syntax errors to be found at that table location. And no other error messages whatever. There is a debug function that could be specified for mysqldump. But I don't see anything similar for a mysql import.

    Any mySQL gurus that care to throw a bone this way as to how to troubleshoot import failures?




    -
  16. #9
  17. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    It sounds like root login is disabled (as it should be in a production server.)

    Why are you connecting to MySQL in PHP using your root user anyway? That's a big no-no.

    If root login is not disabled, it means that the provided password is not authenticating.
    Last edited by Nilpo; December 26th, 2013 at 11:37 AM.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    The default configuration would be for a production machine, so the root login probably was initially disabled. As noted earlier in the thread though this is not a production server, I am restoring to a development machine. So it's entirely appropriate to grant all here and i don't see an authentication issue afterward.

    As important as security is in production environments it is not the question at hand. I am seeking to resolve the import issues and for the purposes of this thread do not care about security unless it is a contributing cause of the failing import.
  20. #11
  21. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    As important as security is in production environments it is not the question at hand. I am seeking to resolve the import issues and for the purposes of this thread do not care about security unless it is a contributing cause of the failing import.
    It's an authentication issue, security IS the problem.

    Aside from that, it's ok to use root from the command line on a dev server. But you should never, ever, ever use it from PHP on ANY server. If you accidentally leave that in your code and someone finds a vulnerability, you're toast.

    Create a proper user and work from there.

    I still think it's a bad password.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    Code:
    mysql> create user test@localhost
    identified by password *80E462C37378CAD12055BB0488827D2BA2A9B689;
    mysql> grant all on newdatabase.* to test@localhost;
    
    $ mysql -u test -p -e "flush privileges";
    $      Enter password: ********************
    mysql> create database newdatabase;
    mysql> use newdatabase;
    mysql> show tables;
           Empty set (0.00 sec)
    mysql> quit;
    
    $ mysql -u test -p newdatabase < c:\working\dump.sql
    You say that it is an authentication problem. I don't see evidence of that.. would you kindly elaborate for me? As we delineated earlier, the root account was originally disabled. It was subsequently granted access and appears to authenticate properly. At your suggestion, I created a new user and noted that the import fails in the same manner for the new user as well.



    -
  24. #13
  25. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    This means its an authentication error.

    Warning: mysql_connect(): Access denied for user: 'root@localhost' (Using password: YES)

    If you're sure the password is correct, are you accessing from an allowed IP? If this is via SSH, you need to make sure that you have allowed access from your IP address. Otherwise, MySQL will simply disconnect.

    Sorry, it's hard to tell. You're talking about problems on the CLI but you're providing error messages from PHP so it's hard to follow along.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    18
    Rep Power
    0
    The original configuration was for a production machine and the authentication message predates enabling the root account. All of the accounts currently authenticate properly.


    -
  28. #15
  29. Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2004
    Location
    New Springfield, OH
    Posts
    1,214
    Rep Power
    1469
    Originally Posted by argot
    The original configuration was for a production machine and the authentication message predates enabling the root account. All of the accounts authenticate properly as expected.


    -
    Ok, on to the next one then.
    Code:
    $ mysql -u test -p newdatabase < c:\working\dump.sql
    
    ERROR 2006 (HY000) at line 6361; MySQL server has gone away
    This means that the mysql server is timing out or closing the connection.

    If it's timing out, check my.cnf and set wait_timeout to a higher value (in seconds). Make sure to restart mysql after changing this. This needs to be long enough to allow your script to run.

    If that's not it, then the server is likely dropping packets that are too large. Try increase the allowed size by editing my.cnf and setting max_allowed_packet to a higher value. Again, restart mysql afterward.

    If it runs awhile and then stops, it's probably the first one. If it stops almost immediately after starting, it's more likely the second one.
    Last edited by Nilpo; December 26th, 2013 at 04:11 PM.
    Don't like me? Click it.

    Scripting problems? Windows questions? Ask the Windows Guru!

    Stay up to date with all of my latest content. Follow me on Twitter!

    Help us help you! Post your exact error message with these easy tips!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo