# resetting auto\_increment\_increment and auto\_increment\_offset back to defaults

**URL:** <https://forums.percona.com/t/resetting-auto-increment-increment-and-auto-increment-offset-back-to-defaults/6572>\
**Category:** Other MySQL® Questions\
**Created:** [September 11, 2018, 7:17pm UTC](https://forums.percona.com/t/resetting-auto-increment-increment-and-auto-increment-offset-back-to-defaults/6572 "2018-09-11T19:17:36Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Daniel\_Kasak](https://avatars.discourse-cdn.com/v4/letter/d/94ad74/32.png) [@Daniel\_Kasak](https://forums.percona.com/u/Daniel_Kasak)\
**Post date:** [September 11, 2018, 7:17pm UTC](https://forums.percona.com/t/resetting-auto-increment-increment-and-auto-increment-offset-back-to-defaults/6572/1 "2018-09-11T19:17:36Z")

</div>

Hi all. I’ve done a successful “live migration” to a new server, by making use of auto\_increment\_increment and auto\_increment\_offset. One the old master, these were set to 2 and 1 respectively, whereas on the new, they were set to 2 and 2. Now, to stop us from burning through sequences faster than necessary, I’d like to set these back to 1 and 1. I’ve done a quick check of all auto\_increment values, by 1st locating all the tables using auto\_increments:

```auto
select * from information_schema.COLUMNS where table_schema = '#Q_DatabaseName#' and extra = 'auto_increment'

```

… then looping over each record and checking the auto\_increment value:

> [@](#):
>
> select auto\_increment from information\_schema.tables where table\_schema = ‘#Q\_DatabaseName#’ and table\_name = ‘#I\_AUTO\_INCREMENTS.TABLE\_NAME#’

… against the maximum current value in the table:

> [@](#):
>
> select max( #I\_AUTO\_INCREMENTS.COLUMN\_NAME# ) as max\_value from #I\_AUTO\_INCREMENTS.TABLE\_NAME#

The things in hashes are substituted at run-time. I didn’t find any cases where the current max value was beyond the auto\_increment value ( which could have theoretically happened if we had an insert on the old master during the migration, but no inserts on the new master beyond that point ).

Next, I tried setting the auto\_increment\_increment and auto\_increment\_offset values back to 1 and 1, and restarting various components, but I saw duplicate key violations where we previously hadn’t. I quickly set it back to 2 and 2, restarted things again, and the duplicate key violations disappeared. Unfortunately I can’t capture the SQL being generated to examine closely what tables were causing issues and investigate further.

Based on this, I assume I misunderstand how auto\_increment objects behave. I expected that new values being issued would be at least as large as the previous state, and would simply go back to incrementing by 1. Is my approach valid, and if not, what’s happening here?

Dan