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
up vote 19 down vote accepted

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.

  • I used this approach because I was able to turn off writes (which are all batch mode) to the table while doing the copy. Took about 36 hours. – Mark Hansen Mar 27 '15 at 19:38
  • and you want to keep these three in one transaction. (lock/unlock) – Sławomir Lenart Dec 13 '16 at 16:44

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) :-(

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

  • Can I use the percona with Amazon RDS? Yes, we have "burned" a lot of IDs, the table actually has about 330 million rows. – Mark Hansen Mar 19 '15 at 23:39
  • I don't know about pt & RDS. If you could eliminate the burning, you have another ~330M ids before you run out of room. – Rick James Mar 20 '15 at 17:16

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.