auto_increment incremented on non-affecting queries When performing the following query on 'sessions':

INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0)


while:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL,
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;


with trigger:

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));


the first time you execute the query, everything is fine and the following is inserted:

id  user_id date_created    date_modified   archived    valid_until
1   3       ...             ...             0           ...


now enter the same query two more times, and it will say:

No errors, 0 rows affected, taking 8,0ms


Now change the query to:

INSERT IGNORE INTO sessions (user_id, archived) VALUES (4, 0)


So basically you only changed the value of user_id.

You'd expect the next row to be:

id  user_id date_created    date_modified   archived    valid_until
**2**   4       ...             ...             0           ...


instead, you get: 

id  user_id date_created    date_modified   archived    valid_until
**4**   4       ...             ...             0           ...


So basically what's happening, is that the unique_key (id)'s auto_increment is being updated on every query, even though there are no actual new rows being made. 

This shouldn't be right, should it?

Server version:     5.6.22 Homebrew
This shouldn't be right, should it?


It's right.

Databases (not just MySQL) generally ensure that sequences will only ever increase (once a transaction is committed), but not that they will be without gaps. For instance, TFM says:


  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.


and:


  As of MySQL 5.1.22, InnoDB can avoid using the table-level AUTO-INC lock for a class of INSERT statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication. Further, if you are not using the binary log to replay SQL statements as part of recovery or replication, you can entirely eliminate use of the table-level AUTO-INC lock for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved. 


Consider the case where two uncommitted transactions exist in an MVCC system like InnoDB. They may both need a unique primary key before being committed, but the first transaction may fail. Hence you will end up with a gap.

You can't avoid this behaviour, so embrace your gappy primary keys :)

https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html