ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)

Question

Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing.

The tables are in a production MySQL 5.6.19a database running on Amazon RDS.

How can we do an ALTER like this without disrupting the production reads and inserts that are happening all the time?

ALTER TABLE MYTABLE CHANGE id id BIGINT NOT NULL AUTO_INCREMENT;

Here is DDL for the table:

CREATE TABLE `MYTABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteId` int(11) NOT NULL,
  `filter` varchar(10) NOT NULL DEFAULT 'ALL',
  `date` varchar(10) NOT NULL,
  `cards` varchar(250) NOT NULL,
  `apples` varchar(45) NOT NULL,
  `carrots` varchar(45) NOT NULL,
  `corn` varchar(45) NOT NULL,
  `peas` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`siteId`,`filter`,`date`,`cards`),
  KEY `date_k` (`date`),
  KEY `cards_k` (`cards`),
  KEY `apples_k` (`apples`),
  KEY `siteId_k` (`siteId`)
) ENGINE=InnoDB AUTO_INCREMENT=1748961482 DEFAULT CHARSET=utf8

Answer 1

If you have enough space, you can create a copy of the actual table and do the work on that:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

Then you can change the column as desired:

ALTER TABLE tbl_name MODIFY COLUMN col_name BIGINT AUTO_INCREMENT;

Once the process is done, you can rename the tables:

RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2;

Then drop the original table, and you should have the spected result.

Answer 2

percona toolkit is the way to go, at least if you are not super short in time. The conversion took on our table (500Gb, master-slave setup) when we tested it a bit more than 24h, in production it took (with better hardware) almost 1 month (funny sidenote we had about 30 days before we would run out of ids, therefore we started already to plan for plan B and C, working with offline backups, removing slaves,... ). The delay was mainly due to the waiting of the replication happening towards the slaves (we allowed max 50sec time lag). Also make sure to limit the number of concurrent threads. We have more than 2M inserts/day and many million reads.

Also be aware that once the coversion has started you can't stop it (or at least we didn't find any way to restart it) :-(

Answer 3

Well....

KEY TOP_QUERIES_LAST_30DAYS_fk (siteId) is redundant with the PRIMARY KEY, so you may as well DROP it.

INT UNSIGNED would get you to 4 billion, will that suffice?

Consider changing filter to an ENUM.

Do you have 1.75 billion rows? Or did you "burn" a lot of ids? If so, maybe we can fix that? For example REPLACE and certain flavors of INSERT will toss ids. INSERT...ON DUPLICATE KEY can usually replace REPLACE. A 2-step process can avoid INSERT IGNORE's burning of ids.

Back to the question...

See if pt-online-schema-change will do the trick: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html