1

I have a BASH program that inserts into my DB somes lines. Unfortunately, when MYSQL encounter an error(especially foreign key constraint errors), the entire SQL request stops.

Isn't there a way to skip the lines where the foreign key constraint doesn't apply, and keep adding the others?

    insertVtigerInfos() {
$mysql -h $db_address -u $db_user -p$db_passwd $db_name -e "delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' REPLACE INTO TABLE $db_vtiger_name fields terminated by ';'"
}


    ----------------------démarrage---------------------------------
Tue May  6 16:03:13 CEST 2014
ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`RH2QUALIF`.`info_vtiger`, CONSTRAINT `info_vtiger_ibfk_1` FOREIGN KEY (`nom_caisse`) REFERENCES `definition_ip_switch` (`nom_caisse`) ON DELETE CASCADE ON UPDATE CASCADE)
-------------------------fin------------------------------------
Tue May  6 16:03:14 CEST 2014

Updating thread with sample : Table toward whom the foreign key constraint applies :

+-------------+------------+
| nom_caisse  | quat_octet |
+-------------+------------+
| BCP         | NULL       |
| CEA         | NULL       |

Table having the foreign key constraint :

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| code_site       | varchar(32) | NO   | PRI | NULL    |       |
| nom_caisse      | varchar(32) | NO   | MUL | NULL    |       |
| ip_routeur      | varchar(32) | NO   |     | NULL    |       |

Data trying to be inserted :

131001M0;CEA;<ip>
131001G0;CEALS;<ip>

When mysql tries to insert the rows, the 1st is OK, the 2nd is not (because CEALS is not in the table which the foreign key is. But mysql doesn't add ANY row because he found one not matching... I just want it to add the 1st row...

Gui O
  • 363
  • 4
  • 8
  • 22
  • 3
    Try `SET FOREIGN_KEY_CHECKS = 0;` but after execution of your script set it to 1 – M Khalid Junaid May 06 '14 at 14:15
  • This will add all the lines, even the ones that don't match. I just want mysql to keep continuing after finding foreign key errors – Gui O May 06 '14 at 14:17
  • As per [docs](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html) *Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency* – M Khalid Junaid May 06 '14 at 14:22
  • See here: http://stackoverflow.com/questions/5366436/continue-sql-query-even-on-errors – ceving May 06 '14 at 14:24
  • The --force option don't care about foreign key errors. It still stops – Gui O May 07 '14 at 06:00

2 Answers2

1

The only solution i found is to do that :

$mysql -f -h $db_address -u $db_user -p$db_passwd $db_name -e "SET foreign_key_checks=0;delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' REPLACE INTO TABLE $db_vtiger_name fields terminated by ';';SET foreign_key_checks=1;"

and then sending another request which delete the rows according to the foreign key.

delete from info_vtiger where NOT EXISTS ( select nom_caisse from definition_ip_switch where definition_ip_switch.nom_caisse=info_vtiger.nom_caisse ) ;

Unfortunately, the foreign key constraint is no more useful in this case. posting it as a solution, if anyone have some other ways to do it...

Gui O
  • 363
  • 4
  • 8
  • 22
0

If your requirement is just to insert new records but not replace to existing records then you can use as per below:

$mysql -h $db_address -u $db_user -p$db_passwd $db_name -e "delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' INSERT IGNORE INTO TABLE $db_vtiger_name fields terminated by ';'"
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Mysql send me an error on this insert ignore : ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT IGNORE INTO TABLE info_vtiger fields terminated by ';'' at line 1 – Gui O May 07 '14 at 05:58
  • @Gui O: First use '$vtiger_temporary_file' as $vtiger_temporary_file without enclosing it in single quote, if still get error then share this file sample data at least one row data. – Zafar Malik May 07 '14 at 06:05
  • Another solution i can use is to set the foreign_key_checks=0, insert then put it back to value 1. Then sending another request as : delete from info_vtiger where nom_caisse NOT IN defintion_ip_switch ?? – Gui O May 07 '14 at 06:33
  • set foreign_key_checks=0 means you are allowing rows in child table even does not exist in its parent table means you are making your data inconsistant..but if you are sure that your data is correct and there is no need to further check this data then you can do it...further now your error message is related with syntax error related with load infile not foreign key so if you share the details then your problem can be sorted out. – Zafar Malik May 07 '14 at 06:41