SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: Import Failing

  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Import Failing

    Got a new MacBook Pro last March 2013, and I am *still* trying to get it set up and working?! (Oh the agony of migrations!!!!!)

    On my old MacBook, every week I use phpMyAdmin to the "export" tab to backup my database to a backup folder.

    This morning I took the hard-drive out of my old MacBook, plugged it into an external HDD enclosure, plugged it into my new MBP, and chose to import the latest backup called "doubledee_backup_2013-12-08_LastBeforeMigration.sql"

    Unfortunately, phpMyAdmin keeps giving me this error shortly after the import begins...

    Code:
    Error
    
    SQL query:
    
    DELIMITER;
    
    --
    -- Dumping data for table `article_placement`
    --
    INSERT INTO `article_placement`
    VALUES ( 1, 'finance', 'featured-finance', 'economy', 'which-states-support-main-street', 0, '2013-05-11 18:33:34', NULL ) ;
    
    MySQL said: Documentation
    #1146 - Table 'doubledee.section_dimension' doesn't exist

    I don't understand why it would complain that the "section_dimension" table doesn't exist, when the whole point is that I am trying to import all of my old database structure into a new, *bare* database on my new MBP?!

    My old MacBook has an older version of MAMP, and thus phpMyAdmin, MySQL, PHP, etc., but you wouldn't think that would make that big of a deal when it comes to exporting/importing a basic database?!

    Any ideas what is going on??

    Sincerely,


    Debbie

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    1/ does the table exist?
    2/ have you run the create table sql if there is one?
    3/ did the export command export the data or the data + table structure?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Hi Debbie,

    Looks like you decided to take the easy "Quick - display only the minimal options" when exporting your database.

    Try exporting again select "Custom - display all possible options".

    Object creation options

    Add statements:
    Add DROP TABLE statement
    Add CREATE PROCEDURE / FUNCTION / EVENT statement
    CREATE TABLE options:
    IF NOT EXISTS
    AUTO_INCREMENT
    Enclose table and column names with backquotes (Protects column and table names formed with special characters or keywords)
    It should save you the time in re-creating all the tables and possibly the fields all over again.
    Last edited by John_Betong; Dec 9, 2013 at 03:29. Reason: spellng: not my fortey
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    1/ does the table exist?
    It exists in my original database.


    Quote Originally Posted by spikeZ View Post
    2/ have you run the create table sql if there is one?
    No, because my script is supposed to build everything inside the database container...


    Quote Originally Posted by spikeZ View Post
    3/ did the export command export the data or the data + table structure?
    Data + Structure


    Debbie

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I've got a REALLY horrible feeling about all of this...


    Quote Originally Posted by John_Betong View Post
    Looks like you decided to take the easy "Quick - display only the minimal options" when exporting your database.

    Try exporting again select "Custom - display all possible options".

    It should save you the time in re-creating all the tables and possibly the fields all over again.
    Hate to break it to you, but that stuff didn't help...


    Here are screenshots of what I am doing in MAMP's phpMyAdmin...

    Create Export DBSchema
    Attachment 63527


    Preparing to Import DB Schema
    Attachment 63528


    ** Importing **
    Attachment 63529


    Crash & Burn...
    Attachment 63530

    Sincerely,


    Debbie

  6. #6
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I've got a REALLY horrible feeling about all of this...

    ...
    ...
    ...
    Crash & Burn...
    Attachment 63530
    The Crash & Burn error message states that the operation timed out. I have overcome this problem by Zipping the export.

    I also think that the database names must be identical.
    Last edited by John_Betong; Dec 9, 2013 at 11:04. Reason: added database name requirements
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    The Crash & Burn error message states that the operation timed out. I have overcome this problem by Zipping the export.
    I tried that and it makes no difference...

    Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!

    Off Topic:

    What freaks me out the most about all of this, is that I *thought* I knew how to back-up my database, and apparently all of these backups are now useless... If size was an issue, then what in the hell will happen when I do this same thing on my Production Database which has 10,000 Members in it and is several hundred MB's in size, if not into the GB's?!



    Quote Originally Posted by John_Betong View Post
    I also think that the database names must be identical.
    On my new MBP I had the same database name, and it still isn't working...


    Debbie

  8. #8
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    >>> I tried that and it makes no difference...
    What was the new error messages?

    >>> Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!
    Is that the size of the zipped file?

    I frequently download a Zip file about 1.5 meg and the raw SQL file is about three times bigger.

    As far as the download file size limitations, PhpMyAdmin has a default which can easily be enlarged.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    >>> I tried that and it makes no difference...
    What was the new error messages?
    Same as above.


    Quote Originally Posted by John_Betong View Post
    >>> Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!
    Is that the size of the zipped file?
    No, the raw .sql file.


    Quote Originally Posted by John_Betong View Post
    I frequently download a Zip file about 1.5 meg and the raw SQL file is about three times bigger.

    As far as the download file size limitations, PhpMyAdmin has a default which can easily be enlarged.
    Last night when I was trying things, the ZIP file was 123 KB and the .sql file was 1.4 MB (Both are extremely TINY!!)


    One things I have noticed is that after things crash/time-out, 30 out of 31 objects are created.

    The problem now seems to be with my "z_result_log" which is where I keep all of my Errors/Outcomes.

    That table is *only* 6,377 records and has a whopping 9 columns?!

    It seems like when the Import is kicked off, things hang up after maybe 1,500 records get INSERTed.

    After things crash, the final "z_result_log_VIEW" View is then not being created.

    When I created a separate Export of the "z_result_log" table, then it gets successfully Imported in under 3 seconds?!

    There are NO Foreign Keys on this table.

    And I currently only have one Trigger, and it does not touch the "z_result_log_ table.

    So I'm not sure what is going on here...


    Debbie

  10. #10
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    The moral of the story is: don't use phpmyadmin to backup and restore db. I've been there and after finding my dumps corrupted I said never again! Phpmyadmin is simply not the right tool for the job, php is not the best language for this because due to timeout limits the export/import need to be split into multiple jobs and this creates complexity and results in bugs. For exporting a simple and small table from time to time it's okay but never for transferring large amounts of data or for stuff that is important in any way!

    I don't know of any good mysql software for the Mac, Windows users can use the free edition of SQLyog for easy backup/restore. But there's always the good old mysqldump invoked from the command line and it's pretty easy to use and most importantly it's can be relied upon. Mysqldump has many options but the basic usage is quite simple: How to load database from a dump file. But even the somewhat clumsy MySQL Workbench will do the job fine as it acts as a GUI for mysqldump and it's multi-platform.

  11. #11
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ...
    ...
    ...
    >>> Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!
    ...
    ...
    ...
    It appears as though you have a problem with at least one table.

    I prefer to have a working export/import sample.

    I would try and find the good tables by export/import a limited amount of tables (start with 16).

    If the export is OK then increase the tables and if not, reduce the number of tables.

    Repeat until the problematic table is found

    Select only the problematic table and export/import to find the problem.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    It appears as though you have a problem with at least one table.
    Right, as I said in Post #9...

    Quote Originally Posted by doubledee
    The problem now seems to be with my "z_result_log" which is where I keep all of my Errors/Outcomes.

    That table is *only* 6,377 records and has a whopping 9 columns?!
    Is this table "broken"??

    I don't think so.

    And that seems like a small number of records...

    So what gives?!


    Quote Originally Posted by John_Betong View Post
    I prefer to have a working export/import sample.
    Not sure what you are asking?

    Did you want to see the .sql for when I export that one table?


    Quote Originally Posted by John_Betong View Post
    I would try and find the good tables by export/import a limited amount of tables (start with 16).

    If the export is OK then increase the tables and if not, reduce the number of tables.

    Repeat until the problematic table is found

    Select only the problematic table and export/import to find the problem.
    As I said above...

    It seems like if I leave out the "z_results_log" table from the export file, then all 30 of the 31 Tables/Views get created okay. (Although I'm not sure how you *verify* that is the case??)

    And if I create a 2nd export for the "z_results_log" table, then by itself, it runs in a few seconds.

    But that doesn't explain why things are hanging up?!

    If export #1 with 30 Tables/Views runs in 1 second, and export #2 with the "z_results_log" table only runs in maybe 3 seconds, then why doesn't my original export with all 31 Tables/Views not run in under 5 seconds instead of crashing after 5 minutes?!

    Sincerely,


    Debbie

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    But even the somewhat clumsy MySQL Workbench will do the job fine as it acts as a GUI for mysqldump and it's multi-platform.
    But as far as I know, phpMyAdmin is also just a GUI for running mysqldump...


    And how much more reliable is MySQL WorkBench not only for Backup & Restores, but basics database maintenance as well??

    (I've heard people rip on phpMyAdmin in the past, but up until now, I have found it to be a real Godsend for someone who doesn't have the time or competency to do everything via command-line...)

    Sincerely,


    Debbie

  14. #14
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Right, as I said in Post #9...
    Is this table "broken"??

    I don't think so.

    And that seems like a small number of records...

    So what gives?!
    OK, if you can export/import the database with 30 tables then that is "working export/import" sample.

    Try first to see if the exported log table can be added to your "working export/import" and see if there are any errors.

    Secondly export/import the single log table in your database and see if there are any errors.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    OK, if you can export/import the database with 30 tables then that is "working export/import" sample.

    Try first to see if the exported log table can be added to your "working export/import" and see if there are any errors.
    How would I do that?


    Quote Originally Posted by John_Betong View Post
    Secondly export/import the single log table in your database and see if there are any errors.
    John, you're not reading very carefully...

    As I have said a couple of times...

    1.) When I export *all* 31 Tables/Views into a single .sql file, and then try and Restore things, I get an error.


    2.) When I export all Tables/Views except for my "z_results_log" table, and then try and Restore things, I get a successful restore in a few seconds.


    3.) When I export just the "z_results_log" table by itself, and then try and Restore things, I get a successful restore in a few seconds.


    So for some reason, phpMyAdmin won't play nice when I export *everything* and then try and restore *everything* from one .sql file?!

    Sincerely,


    Debbie

  16. #16
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by DoubleDee View Post

    OK, if you can export/import the database with 30 tables then that is "working export/import" sample.
    Try first to see if the exported log table can be added to your "working export/import" and see if there are any errors.
    How would I do that?
    With the PhpMyAdmin:
    Selecting a database to export
    Select "Custom - display all possible options"
    Select the 30 good tables to be exported
    Select the create table IF NOT EXISTS and AUTO_INCREMENT
    Select the Zip option
    Select Go and save the Zip file
    Try importing the Zip file into an empty database.

    ==========================//============================
    John, you're not reading very carefully...

    As I have said a couple of times...
    1.) When I export *all* 31 Tables/Views into a single .sql file, and then try and Restore things, I get an error.
    2.) When I export all Tables/Views except for my "z_results_log" table, and then try and Restore things, I get a successful restore in a few seconds.
    3.) When I export just the "z_results_log" table by itself, and then try and Restore things, I get a successful restore in a few seconds.

    So for some reason, phpMyAdmin won't play nice when I export *everything* and then try and restore *everything* from one .sql file?!
    When you export/import the z_results_log table is it only the table or is the table included in a database log?

    With the import of the z_result_log table, If it is an isolated file can you import the z_results_log table into the database with the other 30 tables?

    If the z_results_log is the only file exported inside a database can you import that database log?
    If you can import the above database log, can you save the z_results_log table to the database with the 30 tables?

    I appreciate that you are unable to export/import all 31 tables and at the moment do not know the solution to the problem.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  17. #17
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    This is beyond exasperating...

    I just tried to export the first 29 Tables/Views and now get this error...

    MySQL said: Documentation
    #1050 - Table 'article_placement_VIEW' already exists
    So the first 28 Tables/Views get created, but one of the Views pukes?!


    I don't know which version of MAMP I am running, because the idiots don't put the version anywhere in the app or the documentation, but I do see this in my browser...

    Code:
    phpMyAdmin - 2.11.7.1
    
    MySQL client version: 5.0.41

    Is there some way that I could just physically copy the files out of MAMP?

    Maybe at this path...

    Code:
    /Applications/MAMP/db/mysql/doubledee/

    If so, I'm not sure what all File and Directories I'd need to copy?!



    Quote Originally Posted by John_Betong View Post
    When you export/import the z_results_log table is it only the table or is the table included in a database log?

    With the import of the z_result_log table, If it is an isolated file can you import the z_results_log table into the database with the other 30 tables?

    If the z_results_log is the only file exported inside a database can you import that database log?
    If you can import the above database log, can you save the z_results_log table to the database with the 30 tables?

    I appreciate that you are unable to export/import all 31 tables and at the moment do not know the solution to the problem.
    I was starting to answer these, but now I can't even import the last two tables successfully...
    Code:
    z_result_log
    z_result_log_VIEW

    It appears that "z_result_log" gets imported okay, but then this associated View fails...
    Code:
     MySQL said: Documentation
    #1050 - Table 'z_result_log_VIEW' already exists

    So frustrating...


    Debbie

  18. #18
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,833
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Hi Debbie,

    When I export a database I always select "Create table if not exists".
    Rename the localhost database to "db_whatever_YYMMDD"
    Create a new blank "db_whatever" database.
    Import the "db_whatever" database.

    ======================//======================

    There are other options that have been frustrating - "Function to use when dumping data:" - there are three options, INSERT, UPDATE, REPLACE.

    ======================//======================

    With different Online and Localhost PhpMyAdmin versions I have also had problems with different table structures,
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  19. #19
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    But as far as I know, phpMyAdmin is also just a GUI for running mysqldump...
    Oh no, of course it's not. Phpmyadmin is written entirely in php and it's purpose is to be as much compatible as possible with all kinds of hosting environments but many shared hosts don't allow access to mysqldump. Phpmyadmin has been known to have problems with large databases or objects other than tables like views, stored procedures, etc., especially the old versions, and you version is ancient - if you upgraded you might have more luck...

    Quote Originally Posted by DoubleDee View Post
    And how much more reliable is MySQL WorkBench not only for Backup & Restores, but basics database maintenance as well??

    (I've heard people rip on phpMyAdmin in the past, but up until now, I have found it to be a real Godsend for someone who doesn't have the time or competency to do everything via command-line...)
    I don't use Workbench for basic db maintanance because I use HeidiSQL, which I find much easier to use and it's UI is really nice and friendly (beats phpMyAdmin hands down!). I use Workbench only for graphic data modelling and it works okay for a free tool. But it can be used for general maintenance as well, you'd have to try it out.

    The question is not phpMyAdmin vs command line because there are many tools other than phpMyAdmin that are not command-line. Speedwise phpMyAdmin will never catch up to standalone db management tools because the overhead of php execution and browser rendering can't be avoided.

    I would recommend HeidiSQL, it's not for Mac but supposedly it works fine via Wine and the author supports such installations. Also, there are some paid Mac-only programs that I haven't tested.


Bookmarks

Posting Permissions

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