Turn off foreign key checks when restoring a mysql file dump

• ปัญหาคือ  dump structure และ load outfile  ออก จาก MySQL จากเครื่องหนึ่ง ไปเครื่องอื่น
• โดย mysqldump เอาแต่โครงสร้างออกมา และ load outfile เอา data ออกต่างหาก ขั้นตอนนี้ไม่พบปัญหา
• ขั้นตอน mysql  โหลดไฟล์ structure เข้าก็ไม่พบปัญหา
• แต่ขั้นตอนการ load infile มีปัญหาคือ เรื่องการทำ foreign key ระหว่าง  table กันทำให้เกิด error ไม่สามารถนำข้อมุลเข้าได้

1452 (23000): Cannot add or update a child row: a foreign key constraint fails (... FOREIGN KEY ...) REFERENCES ...

Solved

• ต้องสั่งปิดการอ้างอิง FK ก่อน แล้ว dump ข้อมูลเข้า 
• ข้อมูลเข้าเสร็จ แล้ว
• จากนั้นจึงเปิดใช้ FK คืน

mysql --login-path=rob --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dump.sql

• หรือใน python ก็จะประมาณนี้

mydb = mysql.connector.connect(

  host="host",

  user="user",

  password="password",

  database="db"

)

mycursor = mydb.cursor()

try:

mycursor.execute("SET SESSION FOREIGN_KEY_CHECKS=0;")

mycursor.execute("load data infile ...")

mydb.commit()

except Exception as ex:

print(ex)

finally:

mycursor.execute("SET SESSION FOREIGN_KEY_CHECKS=1;")

mydb.close()

mycursor.close()

• สรุปง่ายๆ ทำประมาณนี้

SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable;
SET FOREIGN_KEY_CHECKS = 1;

Ref

• https://akrabat.com/turn-off-foreign-key-checks-when-restoring-a-mysql-file-dump/
• https://electrictoolbox.com/load-data-mysql-foreign-key-constraints/