Mysql – csv import fails without throwing an error

csvimportMySQL

Ι am trying to import a csv file into an existing mysql 5.7 table.

Here is the command (the db is in docker with bind mount for persistence)

mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 jira_statistics -e "LOAD DATA LOCAL INFILE '/home/centos/bugs.csv' INTO TABLE bugs FIELDS TERMINATED BY ','"

However:

mysql> select * from bugs;
Empty set (0.05 sec)

The file cannot be loaded because there is a foreign key constraint. I need to be able to catch errors like this (and any others that might occur) as an exit code since I will be scripting a large number of imports like this.

There is no error returned to the command line, and the docker logs of the MySQL container show no errors.

What is more, the exit code of the mysql command is 0!

Any ideas?

Best Answer

Encapsulate the LOAD in a Stored Procedure. In there, capture the error and expose it to your script.

However, if LOAD DATA is not allowed in a Stored Proc, then you really need to write application code, not depend on on-line shell scripts.

Related Solutions

MySQL Import CSV – Only Half the Lines Imported

You might have to check the last character of each line

Instead of this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\n';

Try this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\r\n';

How to Import CSV with Multiple Field Separators in MySQL

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!

Best Answer

Related Solutions

MySQL Import CSV – Only Half the Lines Imported

How to Import CSV with Multiple Field Separators in MySQL

Related Question