Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Question Importing huge data in mysql

    Hi

    When importing a huge data file in mysql say 50000 rows if you get an error in middle of the import regarding foreign keys, how do you fix it?

    One of my friend was recently asked this question in an interview and he wasnt able to answer it.

    Can someone pls tell me the answer?


    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Temporarily disable the foreign key constraint (ALTER TABLE xxx DROP CONSTRAINT fkname), do the import, then use a query or queries to find the problem, fix the data, re-establish the foreign key.

    Finding the bad field shouldn't be hard.

    Let's say you had table
    Code:
    CREATE TABLE users ( 
        userid INT PRIMARY KEY,
        ...
    );
    CREATE TABLE postings (
        postid INT PRIMARY KEY,
        postedby INT,
        CONSTRAINT posting_user_fk FOREIGN KEY (postedby) REFERENCE users(userid),
        ...
    );
    And when you import some data into the postings table you get a foreign key error.

    So you drop the foreign key ALTER TABLE postings DROP FOREIGN KEY posting_user_fk, do the import, and then do something like:
    Code:
    SELECT P.postid
    FROM postings AS P LEFT JOIN users AS U ON P.postedby = U.userid
    WHERE U.userid IS NULL
    Presto. That finds all the records with problems, and then you can go in and fix just those records (or delete them). When they are all fixed, re-create the FOREIGN KEY.
    Last edited by Old Pedant; 09-15-2013 at 11:18 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-16-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Let's suppose you are using an online system and you aren't allowed to drop the foreign key, even briefly.

    Okay, so clone the postings table structure into a different database:
    Code:
    CREATE DATABASE tempfix;
    CREATE TABLE tempfix.postings LIKE postings
    drop the foreign key ONLY in that cloned table.
    Import the table dump into the tempfix database instead of the main database.
    Perform that same SELECT query, but using the cloned table:
    Code:
    SELECT P.postid
    FROM tempfix.postings AS P LEFT JOIN users AS U ON P.postedby = U.userid
    WHERE U.userid IS NULL
    That will find the problems. Fix them in that tempfix.postings table. Then simply copy the records from tempfix.postings to postings.

    ******
    I could probably come up with other ways if it were important.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-16-2013)

  • #4
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Thanks for the reply.


  •  

    Posting Permissions

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