MariaDB auto increment problem

Using OpenSUSE leap 15.1
10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed)

CREATE TABLE testtab2(
    my_id2 INT AUTO_INCREMENT,
    name2 VARCHAR(10),
    comment2 VARCHAR(20),
    PRIMARY KEY (my_id2)
);

testtab.txt:

|Roger |hurray   |
|George|today    |
|Paul  |yesterday|
|John  |last year|
|Ringo |tomorrow |

LOAD DATA LOCAL INFILE 'testtab.txt' INTO TABLE testtab FIELDS TERMINATED BY '|';

works fine with linenumbers (my_id) 1-5

INSERT INTO testtab (name,comment) VALUES ('Beatles','band');

this gets linenumber (my_id) 8 – should be 6.

If testtab.txt have 8 records and using INSERT INTO 1 record this gets linenr (my_id) 16.
This happens in other tables too – one moved from 199 to 256.

Anyone who can solve the problem?

This is not a problem, but expected, documented behaviour:

An AUTO_INCREMENT column normally has missing values. This happens because if a row is deleted, or an AUTO_INCREMENT value is explicitly updated, old values are never re-used. […] With InnoDB, values can be reserved by a transaction; but if the transaction fails (for example, because of a ROLLBACK) the reserved value will be lost.

Thus AUTO_INCREMENT values can be used to sort results in a chronological order, but not to create a numeric sequence.

Most RDBMSes work this way. The identity/sequence/autoincrement value is not incremented by a random number. What happens is each session reserves and caches a certain, often configurable, number of identity/sequence/autoincrement values, and unused values from the cache are discarded if the session ends before they are all exhausted.

For example, session A reserves values 1 through 5 for itself, then session B reserves 6 to 10. Session A uses values 1, 2, and 3 and terminates, so 4 and 5 are never used.

If for some reason you require a gapless sequence (which in reality is very rarely needed), you’ll have to implement it yourself, but note that this will be a performance bottleneck under any nontrivial workload.