Auto Increment Seed Values get revert back to old value on RDS

Question

I changed some table's auto_increment value manually by alter command, but on next day auto_increment seed value reverted back to old value.

Manually altered value (6000000000)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-02-28 18:48:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table fav_tab;
+-----------+-----------------------------------------------------------------------------------------------------------
| Table     | Create Table
+-----------+-----------------------------------------------------------------------------------------------------------
| fav_shows | CREATE TABLE `fav_tab` (
  `FAV_tab_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` bigint(20) unsigned NOT NULL,
  `SERIES_ID` varchar(50) DEFAULT NULL,
  `Creation_dt` datetime DEFAULT NULL,
  PRIMARY KEY (`FAV_tab_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `IDX_SeriesID` (`SERIES_ID`),
  CONSTRAINT `FK_fav_tab_user` FOREIGN KEY (`USER_ID`) REFERENCES `user` (`USER_ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6000000000 DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Next day Value changed automatically (207458739 )

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-03-01 11:23:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table fav_tab;
+-----------+-------------------------------------------------------------------------------------------------
| Table     | Create Table
+-----------+-------------------------------------------------------------------------------------------------
| fav_shows | CREATE TABLE `fav_tab` (
  `FAV_tab_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` bigint(20) unsigned NOT NULL,
  `SERIES_ID` varchar(50) DEFAULT NULL,
  `Creation_dt` datetime DEFAULT NULL,
  PRIMARY KEY (`FAV_tab_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `IDX_SeriesID` (`SERIES_ID`),
  CONSTRAINT `FK_fav_tab_user` FOREIGN KEY (`USER_ID`) REFERENCES `user` (`USER_ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=207458739 DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Please help me to understand, why this is happening and how we can resolve it?

Answer 1

For InnoDB tables this is expected and documented behavior, see

http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.