Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: forcing data with innodb

    Hi everyone,

    I am using innodb and FK constraints.

    I would like to know if there is a way to force a mysql restore without first having to load the data into all of the parent tables first.

    I was using the following
    Code:
    mysql -u root -p pass -f < myBackup.txt
    The "-f" switch indicates "force" but this does not work. mysql will continue to load the script without stopping but does not load the data into the child tables.

    I would like to just load the file and be done with it as with MyISAM. Is this possible?

    Thanks,

    Frank

  2. #2
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I got it. This did the trick.

    SET FOREIGN_KEY_CHECKS=0;


  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you using innodb?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    why are you using innodb?
    I always use Innodb because as I understand it, it is the only engine that offers FK constraints AND transactions. Am I wrong?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just checking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Interestingly if you're getting foreign key conflicts when you're trying to import that data, it would surely imply that there is no parent key existing. And by negating the foreign keys (by using SET FOREIGN_KEY_CHECKS=0) you are removing the purpose of the FKs to start with... unless of course i'm missing the point of what you're attempting to do here.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aschk, some dump tools do not spit out the CREATE TABLE statements in the correct sequence to allow for PKs to be created ahead of the FKs which reference them, hence the desire to turn of FK checking until the load has been completed

    i've never done this myself, though

    frank, what happens after you SET FOREIGN_KEY_CHECKS=1 -- what if there are undefined keys?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Damn those tools to hell! That's what I say!
    *looks at MySQL Administrator*

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by aschk
    Interestingly if you're getting foreign key conflicts when you're trying to import that data, it would surely imply that there is no parent key existing. And by negating the foreign keys (by using SET FOREIGN_KEY_CHECKS=0) you are removing the purpose of the FKs to start with... unless of course i'm missing the point of what you're attempting to do here.
    I don't follow you at all. The fact that mysql was complaining about foreign key constraint failing would certainly indicate that the parent table has an existing key.

    Yes, I believe that you are misunderstanding, probably from a lack of ability on my part to clearly explain what was happening.

    When you use the innodb engine and you have a lot of data to load, the sql is not always complied in the order that the tables need to be populated according to the constraints. the SET FOREIGN_KEY_CHECKS=0 turns that off, allowing me to load the data then is turned back on with SET FOREIGN_KEY_CHECKS=1

    See? learn something new every day.


  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    aschk, some dump tools do not spit out the CREATE TABLE statements in the correct sequence to allow for PKs to be created ahead of the FKs which reference them, hence the desire to turn of FK checking until the load has been completed

    i've never done this myself, though

    frank, what happens after you SET FOREIGN_KEY_CHECKS=1 -- what if there are undefined keys?
    Hey Rudy. Using SET FOREIGN_KEY_CHECKS=1 turns back on the constraints and I am no longer able to load data into a dependant table.

    What do you mean by undefined keys?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would turn it back on after you are finished loading

    what if you
    1. turn it off
    2. load the users table with ids 1, 2, 3,
    3. load the posts table with posts having userids 1, 2, 7
    4. turn it back on

    do you get an error message?

    (yes i know i could test this myself, but you've got everything at your fingertips there already...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    you would turn it back on after you are finished loading

    what if you
    1. turn it off
    2. load the users table with ids 1, 2, 3,
    3. load the posts table with posts having userids 1, 2, 7
    4. turn it back on

    do you get an error message?

    (yes i know i could test this myself, but you've got everything at your fingertips there already...)
    Sure, let me try it and I will be back shortly.

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Very strange.. I do get an error when I try to insert "7". Why?

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Just done a check on setting FOREIGN_KEY_CHECKS=1 after the import. Even if the keys in the child table DON'T exist in the parent table it's happy and seems to work ok. It will let you insert with no problems. However, of course, you're left with redundant data that should never have made it into the table in the first place.

    As an example I have provided the DDL below :

    Code:
    SET FOREIGN_KEY_CHECKS=0;
    
    DROP TABLE IF EXISTS main;
    CREATE TABLE main (
     ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50)
    ) engine=innodb;
    
    
    DROP TABLE IF EXISTS dependent;
    CREATE TABLE dependent (
     ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     p_id INT UNSIGNED NOT NULL,
     name VARCHAR(50),
     CONSTRAINT FOREIGN KEY (p_id) REFERENCES main(ID)
    ) ENGINE=INNODB;
    
    TRUNCATE main;
    TRUNCATE dependent;
    INSERT INTO main(name) VALUES('aerhaer'),('arbaebr'),('aerbearb'),('aerbeab'),('aerbeab');
    INSERT INTO dependent(p_id,name) VALUES(1,'34234'),(1,'AEHAEBR'),(2,'AEBAE'),(3,'AENEANJAN'),(4,'SY S JYSR '),(5,'SYJ SYJ'),(6,'SYJ SY'),(7,'SJY SRYJ'),(8,'S JSY J');
    
    SET FOREIGN_KEY_CHECKS=1;
    To test this after import do :
    Code:
    // This works
    INSERT INTO dependent(p_id,name) 
    VALUES(5,'earbaerb'); 
    
    // This fails on key constraint.
    INSERT INTO dependent(p_id,name) 
    VALUES(342,'earbaerb');
    In answer to your question regarding why it failed. Think about it.
    Your parent table (users) has ids 1,2,3
    Your child table (posts) is trying to insert a parent id into it that doesn't exist (7). Of course it will fail.
    Last edited by aschk; 09-18-07 at 09:20.

  15. #15
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by aschk
    In answer to your question regarding why it failed. Think about it.
    Your parent table (users) has ids 1,2,3
    Your child table (posts) is trying to insert a parent id into it that doesn't exist (7). Of course it will fail.
    Thanks for your efforts aschk.

    I understand why the insert failed but that is not what should be happening on a restore to the db in my case.

    When I went back and looked at the code I have used in my php that works and compared it to r937's example, I noticed that I forgot to include SET AUTOCOMMIT = 1;

    I tried the following and it does what I need it to:

    Code:
    SET AUTOCOMMIT = 1;
    SET FOREIGN_KEY_CHECKS = 0;
    SET SQL_MODE = ALLOW_INVALID_DATES;
    Setting this allows me to enter "Superman" into a date column if I want to.

Posting Permissions

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