CODEWITHSUNDEEP
mysqlsqllinuxterminalcentos
CodeMed
CodeMed

Reputation: 9201

unable to set foreign key checks =0 from terminal

I am trying to run the following code from a linux (CentOS 7) terminal: 

mysql -u root -p -e "SET FOREIGN_KEY_CHECKS = 0"; mysql -u root -p -Nse 'show tables' DATABASE_NAME  -u root -p| while read table; do mysql -u root -p -e "truncate table $table" DATABASE_NAME; done;

But it apparently does not process the set foreign key checks command because in response I get: 

ERROR 1701 (42000) at line 1: Cannot truncate a table referenced in a foreign key constraint (`atest`.`PARTICIPANT_2`, CONSTRAINT `FK_0a036647645f4e5e950470cb2dc` FOREIGN KEY (`PARTICIPANT_ACT_HJID`) REFERENCES `atest`.`ACT` (`HJID`))

Also, it continues to ask me for the password. I expect it to ask 3 times for the password, but not to repeat the three requests again and again. How can I change my code above to get it to run correctly, and with a minimal number of requests for password?

EDIT:

The following modification appears to be asking for the password for EVERY table in the loop. There are 500+ tables. How can I fix this so it only asks for the password a couple times? 

mysql -u root -p -Nse 'show tables' DATABASE_NAME | while read table; do mysql -u root -p -e "SET FOREIGN_KEY_CHECKS = 0; truncate table $table" atest; done;

Upvotes: 0

Views: 806

Answers (1)

spencer7593
spencer7593

Reputation: 108490

The foreign_key_checks is a session variable, it's in effect only for the current session, until it's changed, or until the session ends.

You are creating multiple database session, each new session starts with it's own foreign_key_checks variable, set to the default (inherited from the global setting). (I'm not going to mention how you change the default, because you do not really want to go there.)

One option would be to perform the SET FOREIGN_KEY_CHECKS statement in the same session as the TRUNCATE TABLE statement.

Use a semicolon as a statement separator. As a demonstration of running two statements:

mysql -u me -pXX -e "show variables like 'foreign%'; show variables like '%packet%';"

Upvotes: 2

Related Questions