MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement

Question

I keep track of all the http_user_agents that visit me, with a simple hit counter. The below insert the http_user_agent in the DB, this field is Case Insensitive and is Unique. So when we try to insert it and it finds a DUPLICATE KEY, it adds 1 to the hits field.

The problem is my Auto Increment field still increases even though we did not insert a field. how can i prevent this?

$sql = "INSERT INTO `db_agency_cloud`.`tblRefHttpUsersAgent` SET `http_users_agent` = :UsersAgent, `created_ts` = NOW() ON DUPLICATE KEY UPDATE `hits` = `hits` + 1";

Here is the Table stucture:

CREATE TABLE `tblRefHttpUsersAgent`
(
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`http_users_agent` varchar(255) NOT NULL,
`hits` int(20) unsigned NOT NULL DEFAULT '1',
`created_ts` datetime NOT NULL,
`activity_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `http_users_agent` (`http_users_agent`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE is described as a "mixed-mode insert" for the purposes of InnoDB's AUTO_INCREMENT handling. Mixed-mode inserts are basically ones where the maximum number of required AUTO_INCREMENT values is known, but the amount that will actually be needed is not.

Mixed-mode inserts get handled specially by default, as described in the MySQL docs:

...for “mixed-mode inserts”... InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.

If you're using InnoDB, your alternatives are:

1. Avoid INSERT ... ON DUPLICATE KEY UPDATE.
2. Set the innodb_autoinc_lock_mode parameter to 0, for "traditional" autoincrement lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting.
3. (Recommended) Ignore the gaps in the AUTO_INCREMENT column.

Note: AUTO_INCREMENT handling is totally different under MyISAM, which does not exhibit this behavior.

Answer 2

The storage engine must increment the AUTO_INCREMENT value before inserting a row. It does not know if the insert will fail yet at that point. It cannot simply roll back the increment because there may be other inserts happening on other connections concurrently. This is normal behavior and not something you should (or can) change. The purpose of AUTO_INCREMENT is to provide unique identifiers, not an unbroken sequence of numbers.

Answer 3

Unfortunately the solution is at the application level if you don't want to affect auto-increment id's. Do a SELECT first and count the result rows. If 0 results, INSERT the data. If more than 0, UPDATE that row.

Answer 4

You can first calculate the maximum number of inserted rows and add 1 to it,

(SELECT MAX(`id`)+1 FROM `tblRefHttpUsersAgent`)

then alter the table AUTO_INCREMENT using some variables SET @NEW_ID and PREPARE / EXECUTE statements.

here is a simple solution for the same problem and below is the finished version if you like the solution of your own specific problem:

    $sql = 'SET @NEW_AI = (SELECT MAX(`id`)+1 FROM `tblRefHttpUsersAgent`);
                    SET @ALTER_SQL = CONCAT("ALTER TABLE `tblRefHttpUsersAgent` AUTO_INCREMENT =", @NEW_AI);
                    PREPARE NEWSQL FROM @ALTER_SQL;
                    EXECUTE NEWSQL;';

    $sql .= 'INSERT INTO `db_agency_cloud`.`tblRefHttpUsersAgent` SET `http_users_agent` = :UsersAgent, `created_ts` = NOW() ON DUPLICATE KEY UPDATE `hits` = `hits` + 1';