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:
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:
… against the maximum current value in the table:
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