MySQL – How to ALTER Primary Key Column from INT to BIGINT in Production

innodbMySQL

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

Best Answer

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.

Related Question