We have a PHP script that has been running from the command line on a WAMP server for a few years. I am now converting it to run on Linux (AWS EC2 AMI instance). The script basically loads data from a csv into a mysql table. It is almost working but for some reason it skips the data in the 1st column of the 1st row. Here is the sql:
LOAD DATA LOCAL INFILE '/var/scripts/_output/ref_dtl_output.csv' REPLACE INTO TABLE MY_TABLE.ref_dtl FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'
The files it's loading are anywhere from a few hundred bytes to > 100K bytes. In every case, whether it's getting thousands of rows, or 1 row, the 1st column of the 1st row is always "0" or an empty string. I have checked every csv, and there is data there. This never happened on the Windows server.
I tried running the SQL above from within the PHP script, and I also tried using HeidiSQL to import it and got the same results.
CREATE TABLE `ref_dtl` ( `site_id` INT(11) NOT NULL, `business_date` DATE NOT NULL, `trans_seq` INT(10) NOT NULL, `dtl_seq` INT(10) NOT NULL, `parent_trans_seq` INT(10) NOT NULL, `parent_dtl_seq` INT(10) NOT NULL, `ref` CHAR(20) NOT NULL, `ref_type` CHAR(3) NOT NULL, PRIMARY KEY (`site_id`, `business_date`, `trans_seq`, `dtl_seq`, `parent_trans_seq`, `parent_dtl_seq`), INDEX `site_id` (`site_id`), INDEX `business_date` (`business_date`), INDEX `trans_seq` (`trans_seq`), INDEX `dtl_seq` (`dtl_seq`), INDEX `parent_trans_seq` (`parent_trans_seq`), INDEX `parent_dtl_seq` (`parent_dtl_seq`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
Any ideas for how to fix?