ERROR code 1452 mysql in windows and but not in linux

I have a django project that creates tables after manage.py migrate. I use some LOAD DATA LOCAL INFILE MySQL queries for both. I have 7 tables. All the first 6 tables are populated the same way in both Linux and Windows. Except for the last table that has some foreign keys in previous ones.

Linux MySQL Output

Windows MySQL Output

Both Windows and Linux tables are in InnoDB and the parent table has exactly the same data.

Table information in Linux

Table information in Windows

Section Table keys in Linux

Section Table keys in Windows

INSERT INTO `seas_database`.`section_t`
(`eSession`,`eDays`,`dYear`,`nSectionNumber`,`nSectionCapacity`,`nEnrolled`,`bIsBlocked`,`tStartTime`,`tEndTime`,`cCoffCode_ID`,`cFaculty_ID`,`cRoom_ID`)
VALUES
("Spring","F",2009,1,250,43,0,"14:00","15:30","AAT101","T001","C10210-S");

Proof that AAT101 exists in Windows

Yet i get this error in Windows ONLY. Not in Linux.

Error Code: 1452. 
Cannot add or update a child row: 
a foreign key constraint fails 
(`seas_database`.`section_t`, CONSTRAINT `section_t_cCoffCode_ID_ade53504_fk_cooffered` FOREIGN KEY (`cCoffCode_ID`) 
REFERENCES `coofferedcourse_t` (`cCoffCode_ID`))

Linux MySQL version :

mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

Windows MySQL version:

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe  Ver 8.0.27 for Win64 on x86_64 (MySQL Community Server - GPL)

If it helps, the csv for which the LOAD DATA INFILE was ran, it was modified and created by Pandas from an xlsx file.

The solution turned out to be indeed platform specific. The line terminating character in the auto generated csv file i was loading data from, varied in Windows and Linux. So, while in linux i never faced an issue with the code below,

-- Populating CoOfferedCourse_T
LOAD DATA LOCAL
INFILE "path/to/foo.csv" 
INTO TABLE coofferedcourse_t 
FIELDS TERMINATED BY "\t"
IGNORE 1 LINES 
(@COFFER_COURSE_ID,@COFFERED_WITH)
SET cCoffCode_ID=@COFFERED_WITH,cCourse_ID=@COFFER_COURSE_ID;

In windows, i had to add

LINES TERMINATED BY '\r\n'

After which the table states in both platforms became identical

Back to Top