LOAD DATA INFILE interrup

Question

First MySQL command line:

use usersbase;
LOAD DATA LOCAL INFILE 'D:/base/users.txt' 
 INTO TABLE users
FIELDS TERMINATED BY ',';

Second:

use usersbase;
set session transaction isolation level read uncommitted;
select count(1) from users;

How to stop lodaing from file, if i see, that users table have n rows, and i dont need more? How to save current loaded rows, and stop loading?

Sql does not have a way of cleanly KILLing load data infile (or any other statement for that matter) without rolling the whole thing back https://serverfault.com/questions/915432/msql-best-way-to-stop-a-load-data-infile-command — P.Salmon, Mar 23 '21 at 15:54
*How to stop lodaing from file, if i see, that users table have n rows, and i dont need more?* [KILL Statement](https://dev.mysql.com/doc/refman/8.0/en/kill.html) — Akina, Mar 23 '21 at 16:07
@P.Salmon Not true for LOAD DATA - if it fails then the rows already loaded are not removed (no rollback). — Akina, Mar 23 '21 at 16:08
@Akina, Cool, Didn't know that., Does disagree with https://stackoverflow.com/questions/14703733/effect-of-kill-query-on-insert-and-load-data-infile-statements-on-a-myisam-table though — P.Salmon, Mar 23 '21 at 16:11
@Akina, yes, but after killing, if i doing something with this table, all rows disapeared. Example : `kill 54; select count(1) from users; ALTER TABLE users ADD ID INT( 9 ) NOT NULL; select count(1) from users;` Result: count 50000 ; count 0 — Danny, Mar 23 '21 at 16:15

score 0 · Accepted Answer · edited Apr 17 '21 at 16:44

Try this:

Use LOAD DATA INFILE .. IGNORE ...
Add temporary trigger to this table like

CREATE TRIGGER prevent_excess_lines_insertion
BEFORE INSERT 
ON users
FOR EACH ROW 
BEGIN
    IF 50000 < (SELECT COUNT(*) FROM USERS) THEN
        SET NEW.id = 1;
    END IF;
END

When the line is loaded then the amount of rows in the table (except the line to be inserted) is counted and compared with pre-defined rows amount (50000).

If current rows amount is less then the row is inserted.

If predefined amount is reached then some predefined value (1) is assigned to primary key column. This causes unique constraint violation, which is ignored due to IGNORE modifier.

In this case the whole file will be nevertheless loaded (but only needed rows amount will be inserted).

If you want to break the process then remove IGNORE modifier and replace SET statement with SIGNAL which sets generic SQL error, and loading process will be terminated.

Do not forget to remove the trigger immediately after performing the import.

Note that COUNT(*) in InnoDB can be pretty slow on large tables. Doing it before each insert might make the load take a while. – Barmar

This is true :(

You may use user-defined variable instead of querying the amount of rows in a table. The trigger will be

CREATE TRIGGER prevent_excess_lines_insertion
BEFORE INSERT 
ON users
FOR EACH ROW 
BEGIN
    IF (@needed_count := @needed_count - 1) < 0 THEN
        SET NEW.id = 1;
    END IF;
END

Before insertion you must set this variable to the amount of rows to be loaded, for example, SET @needed_count := 50000;. Variable must be set in the same connection strictly !!! And variable's name must not interfere with another variables names if they're used.

Note that `COUNT(*)` in InnoDB can be pretty slow on large tables. Doing it before each insert might make the load take a while. — Barmar, Mar 23 '21 at 16:39

LOAD DATA INFILE interrup

1 Answers1