LOAD DATA INFILE Duplicate entry on PRIMARY KEY auto_increment id

I'm working on a big-data project that involves running numerous queries (searches, updates, and inserts) on my MySQL database. The database receives approximately 10 requests per second. Additionally, I need to insert large batches of records (around 100,000 records per batch) into the database on a daily basis. While this process generally works fine, I'm encountering "Duplicate entry" exceptions on the primary key, which is the 'id' column with auto-increment functionality.

The confusing part is that the 'id' column is not included in the LOAD DATA INFILE statement, so it's unclear how a duplicate entry exception is occurring on the primary key when MySQL is responsible for generating the key.

Here are some key details about my setup:

Table structure:

create table urls
(
    id           bigint unsigned auto_increment primary key,
    type         tinyint unsigned             null,
    queuemktime  int unsigned     default '0' not null,
    url          varchar(1024)                null,
    subdomain    varchar(256)                 null,
    domain       varchar(256)                 null,
    tld          varchar(256)                 null,
    country      varchar(2)       default ''  not null,
    constraint url
        unique (url)
)
    collate = utf8_bin
    auto_increment = 745448895;

LOAD DATA INFILE statement:

LOAD DATA INFILE '/path/to/batch_1.csv' IGNORE INTO TABLE urls FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (url, subdomain, domain, tld, country);

Sample content of the batch file:

http://blog.scrimmage.co/,blog,scrimmage.co,.co,XX
http://corporate.tastyk.us/,corporate,tastyk.us,.us,XX
http://store.vmrtruckparts.com.au/,store,vmrtruckparts.com.au,.com.au,XX

The exception I'm receiving is as follows:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '745285936' for key 'urls.PRIMARY'

I would greatly appreciate any insights or suggestions on why this duplicate entry exception is occurring despite the auto-increment functionality of the 'id' column. Thank you for your help in advance!

After trying several things I found the solution. While I did not expect it, MySQL itself was the cause of the duplicate auto_increment id's.

While I still do not exactly know why this is the fix, the problem was solved after setting the system variable innodb_autoinc_lock_mode to 1 (“consecutive” lock mode). It was 2 (“interleaved” lock mode), which is the default since of version 8.0. The only thing I could think of is that the consecutive lock mode is a bit safer but performs a little less, but I don't notice it.

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes