forcing data with innodb

Frunkie · #1 (**permalink**) 09-17-07, 00:08

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

Frunkie · #2 (**permalink**) 09-17-07, 01:42

I got it. This did the trick.

SET FOREIGN_KEY_CHECKS=0;

r937 · #3 (**permalink**) 09-17-07, 05:22

why are you using innodb?

Frunkie · #4 (**permalink**) 09-17-07, 05:37

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?

r937 · #5 (**permalink**) 09-17-07, 06:05

just checking

aschk · #6 (**permalink**) 09-18-07, 06:55

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.

r937 · #7 (**permalink**) 09-18-07, 07:01

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?

aschk · #8 (**permalink**) 09-18-07, 07:06

Damn those tools to hell! That's what I say!
*looks at MySQL Administrator*

Frunkie · #9 (**permalink**) 09-18-07, 07:25

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.

Frunkie · #10 (**permalink**) 09-18-07, 07:28

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?

r937 · #11 (**permalink**) 09-18-07, 07:48

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...)

Frunkie · #12 (**permalink**) 09-18-07, 07:51

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.

Frunkie · #13 (**permalink**) 09-18-07, 08:05

Very strange.. I do get an error when I try to insert "7". Why?

aschk · #14 (**permalink**) 09-18-07, 08:15

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.

Frunkie · #15 (**permalink**) 09-18-07, 08:32

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech