1

I have a table with schema like this:

CREATE TABLE `things` (
  `thing_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`thing_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

And some sample data:

INSERT INTO things VALUES (NULL, 'thing 1'), (NULL, 'thing 2');

In my application, sometimes I want to bump the primary key of a row to be the highest in the table. For example, I want the thing_id of thing 1 changed to 3 (the table's current autoincrement value). Previously the table was MyISAM and this was accomplished as follows:

UPDATE things t
    JOIN (SELECT MAX(thing_id) + 1 AS max_id FROM things) v
SET t.thing_id = v.max_id
WHERE thing_id = 1;

That part still works. However, now with InnoDB, the next insert fails since doing that update leaves the table's autoincrement value still pointing to the same thing. So now if I do an insert now like this:

INSERT INTO things VALUES (NULL, 'thing 3');

I will get an error like:

Duplicate entry '3' for key 'PRIMARY'

This was not an issue with MyISAM. How can I accomplish the same thing using InnoDB without running into this error? My guess is that there's a better way to change the thing_id which will keep the table's autoincrement value intact, but that's where I'm stumped.

And lastly, here's a dbfiddle: https://www.db-fiddle.com/f/enJPVkwNN6hocjquw38BHD/0

But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108
  • You should never manipulate the autoincrement primary key. If you tell us more about your process we can probably give you an alternative solution. – Thallius Sep 11 '19 at 17:50
  • The process is, primary key of a row needs to be changed to highest in the table. There is no other option for this application. My best idea at the moment is to duplicate the row and remove the original. Was hoping to avoid that approach but it's feasible of InnoDB can't cope with this approach. – But those new buttons though.. Sep 11 '19 at 17:54
  • 1
    File a bug report: bugs..mysql.com -- `UPDATE` fails to update the max AI value for the table. – Rick James Sep 11 '19 at 18:01
  • 1
    @RickJames It seems to be already [fixed in 8.0](https://www.db-fiddle.com/f/ectVZdbGqCA3kHpmRNCFkA/0) – Paul Spiegel Sep 11 '19 at 18:03
  • @PaulSpiegel - The changelog for 8.0.0 discusses various changes that relate to the new "persisting" of the AI value. One item there implies that this `UPDATE` problem was fixed (either intentionally or coincidentally). – Rick James Sep 11 '19 at 20:23
  • @RickJames Thanks for pointing to the release notes. I found [Bug #199](https://bugs.mysql.com/bug.php?id=199) yesterday, but wasn't sure if it is really related, since not persisting the AI value isn't the issue here. However now I found the behavior documented [here](https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html): "[..] modifying an AUTO_INCREMENT column value in the middle of a sequence of INSERT statements could lead to “Duplicate entry” errors. [..]". It's changed in the [8.0 docs](https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html). – Paul Spiegel Sep 12 '19 at 17:21

1 Answers1

2

Reference: Innodb Auto Increment Initialization

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

Update: You can reset this counter using below query, works for InnoDB storage engine in MySQL 5.7

ALTER TABLE things AUTO_INCREMENT = 1;

Executing this is resetting auto-increment counter to Max + 1 value.

Dark Knight
  • 6,116
  • 1
  • 15
  • 37
  • I'm not sure how manually altering the table's `AUTO_INCREMENT` value is going to pan out in a production environment. Seems dangerous... – But those new buttons though.. Sep 11 '19 at 18:14
  • @billynoah, same is being used by `mysql` itself on server restart as this variable is stored in memory not in disk. – Dark Knight Sep 11 '19 at 18:33
  • 1
    I appreciate your suggestion but I think it feels wrong to be constantly correcting the table `AUTO_INCREMENT` value every time I perform this operation. I'm looking for a cleaner solution and based on comments it seems upgrading to a version that has this bug fixed is going to be a better option. I'll give you an upvote for the time and effort though. Cheers – But those new buttons though.. Sep 11 '19 at 18:53
  • That would be great. Yes in MySQL 8, it has been resolved, it auto take cares of such upgrade. It also supports that you manually can't set this variable to a lesser value than max value of table. – Dark Knight Sep 11 '19 at 18:55
  • 1
    It's too complicated. `ALTER TABLE things AUTO_INCREMENT = 1;` should [work](https://www.db-fiddle.com/f/vfCPbcLhtcm5nQafbY3ipe/0). But I'm not saing, that I'd do it in production. I'd rather copy&delete in transaction, if I couldn't upgrade. – Paul Spiegel Sep 11 '19 at 19:10
  • @PaulSpiegel, I checked it is working perfectly fine. How it works, can you let us know? – Dark Knight Sep 11 '19 at 19:17
  • @JitendraYadav I'm trying to find the corresponding documentation. People [here](https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) are claiming, that nothing will happen in InnoDB. But that's not true at least for MySQL 5.5-7 – Paul Spiegel Sep 11 '19 at 19:34
  • @PaulSpiegel, I checked it for `InnoDB` and every time after some id update if I try resetting it, MySQL table has updated `auto increment` counter. – Dark Knight Sep 11 '19 at 19:36