When I try loading a file of CSV data using the --local-infile option, I get the following error:

> 
> ERROR 1452 (23000) at line 2: Cannot add or update a child row: a foreign key constraint fails (`realtax`.`city_desc`, CONSTRAINT `city_desc_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
> 
> 
> 

The error happens at the first line of the CSV file (i.e., no rows get loaded at all).

Here is the actual mysql load command:

```
load data LOCAL infile 'out/desc.in' into table city_desc fields terminated by ',' lines terminated by '\n';

```

Here is the first line of the CSV file:

```
# head -1 out/desc.in
NULL,1000,"R4","B1","NWC125","SUNRISE ACRES #1 W 120 FT OF S 75",0.207

```

When I use the mysql CLI, however, I am able to load the first line without issue. Here is the result:

> 
> mysql> insert into city\_desc values (NULL,1000,"R4","B1","NWC125","SUNRISE ACRES #1 W 120 FT OF S 75",0.207);  
> 
>  Query OK, 1 row affected (0.04 sec)
> 
> 
> 

Here is the line of table data to which it refers:

> 
> mysql> select \* from city limit 1;  
> 
>  +------+---------+-----------------+  
> 
>  | id | prop\_id | pidn |  
> 
>  +------+---------+-----------------+  
> 
>  | 1000 | 10208 | S912999001L0800 |  
> 
>  +------+---------+-----------------+  
> 
>  1 row in set (0.00 sec) 
> 
> 
> 

Here are the SQL definitions for each table:

```
create table if not exists city 
(
    id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    prop_id         int,
    pidn            varchar(100)
) AUTO_INCREMENT=1000, engine=innodb;

create table if not exists city_desc
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cityid          int not null,
    zoning          varchar(20), 
    state_cd        varchar(20), 
    map_id          varchar(20), 
    legal_desc      varchar(100), 
    legal_acres     numeric(5,5),
    foreign key (cityid) references city(id)
    on update cascade on delete cascade
) AUTO_INCREMENT=500000, engine=innodb;

create index propid_city on city(prop_id);
create index pidn_city on city(pidn);

```

I read the other threads about turning off the referential checks using:

> 
> SET foreign\_key\_checks = 0;
> 
> 
> 

but instead of hacking a fix, I would like to know the reason why this works manually but not with the --local-infile option. The foreign reference is there (record id 1000), so why wouldn't it work?

UPDATE: Here is the information I received after running SHOW ENGINE INNODB STATUS right after the failed insert:

```
------------------------
LATEST FOREIGN KEY ERROR
------------------------
121212 13:50:55 Transaction:
TRANSACTION 0 901853, ACTIVE 0 sec, process no 3217, OS thread id 140224990570240 inserting, thread declared inside InnoDB 492
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 9 row lock(s), undo log entries 9
MySQL thread id 15389, query id 1442612 localhost root
load data LOCAL infile 'out/desc.in' into table city_desc fields terminated by ',' lines terminated by '\n'
Foreign key constraint fails for table `realtax`.`city_desc`:
,
  CONSTRAINT `city_desc_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `cityid` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000000; asc     ;; 1: len 4; hex 800ea15e; asc    ^;;

But in parent table `realtax`.`city`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 800003e8; asc     ;; 1: len 6; hex 00000007aed1; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;; 3: len 4; hex 800027e0; asc   ' ;; 4: len 15; hex 533931323939393030314c30383030; asc S912999001L0800;;

```