1

Ι 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?

0
0

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.

0
0

I can't get an %ERRORLEVEL% returned by mysql, however you can get warnings printed to the console when LOAD DATA LOCAL INFILE fails to insert values into columns for whatever reason, by adding the --batch option to the command-line.

I'm using Windows with MySQL 8.0.22; this is the test command I'm using in a .cmd file:

@"C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysql.exe" --show-warnings --batch -h 127.0.0.1 -uxxx -pxxx --local_infile=1 test -e "LOAD DATA LOCAL INFILE '/temp/test.csv' INTO TABLE bugs FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"
@ECHO %ERRORLEVEL%

The /temp/test.csv I'm using contains:

1
two,test
3
4

The bugs table:

CREATE TABLE `bugs` (
  `i` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Output from running the batch file:

mysql: [Warning] Using a password on the command line interface can be insecure.
Warning (Code 1366): Incorrect integer value: 'two' for column 'i' at row 2
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
0

As you can see, there is a warning presented telling us that 'two' could not be inserted into the column for i. The row was also truncated since there is no column for the value 'test'.

The 0 on the last line is the %errorlevel% value.

I tried adding the LOAD DATA command to a stored proc so that I could create an exit handle for SQL Exceptions, however MySQL blocks the use of LOAD DATA inside a stored procedure. This is the error:

Error Code: 1314. LOAD DATA is not allowed in stored procedures

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.