Using OpenSUSE leap 15.1
10.2.29-lp18.104.22.168-x86_64 from vendor OpenSUSE (installed)
CREATE TABLE testtab2( my_id2 INT AUTO_INCREMENT, name2 VARCHAR(10), comment2 VARCHAR(20), PRIMARY KEY (my_id2) );
|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:
AUTO_INCREMENTcolumn normally has missing values. This happens because if a row is deleted, or an
AUTO_INCREMENTvalue 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.
AUTO_INCREMENTvalues 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.