Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need to change the type to int or change it to unsigned without any downtime.

My setup is master-slave MySQL 5.6 on Amazon RDS. I have one master and 4 slaves.

  • I tried to change the column type to int on slave but it break the replication.
  • Records are changing, because of this it is not possible to copy the table to another table.
  • I cannot use pt-table-sync on Amazon RDS to sync another table and then do the changes on new table.

Is there any way to change to int without locking the table?

Update: I am not sure if the attribute is signed or unsigned. I am not able to find where it signed or not. I run show columns from table and here is the result:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
|     id         | mediumint(8) | NO   | PRI | NULL    | auto_increment |
share|improve this question
    
Does the table have foreign keys ? –  RolandoMySQLDBA Aug 5 at 18:01
    
No, it does not have foreign keys –  usef_ksa Aug 5 at 18:05
1  
Check the output of SHOW CREATE TABLE tablename; But I think it's (the default) signed. It would show mediumint unsigned otherwise. –  ypercube Aug 5 at 18:28
    
It is the default (signed) –  usef_ksa Aug 5 at 19:41

2 Answers 2

While Rolando has my vote, because he is right in your case, I want to answer your original question (for you in the future and for others) regarding percona toolkit:

pt-table-sync is not what you want. Think about pt-online-schema-change. While there are some hard corners, it works on RDS with the right configuration or minimal changes on the script. pt-table-sync will also fix the issues with rds in the next release. There are other alternatives for online schema changes, oak-toolkit and Facebook ones.

As an alternative, you need a spare slave that can suffer the lag for the time the ALTER is running and then doing a controlled switchover.

share|improve this answer
    
+1 for the ALTER on Slave + controlled switchover. Such an approach would be torture if the table has constraints. +1 !!! –  RolandoMySQLDBA Aug 5 at 16:55

Look carefully at your question. You said you are using MEDIUMINT UNSIGNED.

  • The range of MEDIUMINT SIGNED is -8,388,608 to 8,388,607
  • The range of MEDIUMINT UNSIGNED is 0 to 16,777,215
  • This is from the MySQL Documentation

At present, you are not in danger of hitting the max value.

UPDATE 2014-08-04 14:28 EDT

If you are willing to schedule downtime and we are only talking, at most, 8 million rows, run this:

#
# STEP 01: Disable binary logging in your session
#
SET SQL_LOG_BIN = 0;
#
# STEP 02: Make Temp Table With id Converted to INT UNSIGNED
#
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
#
# STEP 03: Take the Table Offline
#
ALTER TABLE mytable RENAME mytable_old;
#
# STEP 04: Load the Temp Table
#
INSERT INTO mytable_new SELECT * FROM mytable_old;
#
# STEP 05: Bring the Table Online
#
ALTER TABLE mytable_new RENAME mytable;
#
# STEP 06: Remove the Old Copy of the Table
#
DROP TABLE mytable_old;
#
# STEP 01: Enable binary logging in your session
#
SET SQL_LOG_BIN = 1;

Here are the steps without comments

SET SQL_LOG_BIN = 0;
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE mytable RENAME mytable_old;
INSERT INTO mytable_new SELECT * FROM mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
SET SQL_LOG_BIN = 1;

Run this script on your Slaves first. This will give you an idea how long to schedule the downtime on the Master. Then, run this script on the Master during the downtime. It will not replicate to the Slaves because of SET SQL_LOG_BIN = 0;.

UPDATE 2014-08-04 14:44 EDT

I just realized you have Amazon MySQL RDS. Please do the following:

  • Take a snapshot of a Slave
  • Spin up the Snapshot as Standalone
  • Then, run the script to give you an idea how long the downtime will be\
  • Drop that test Instance

SET SQL_LOG_BIN = 0; and SET SQL_LOG_BIN = 1; will not work in RDS because of not having SUPER. That being the case, just run:

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE mytable RENAME mytable_old;
INSERT INTO mytable_new SELECT * FROM mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

on the Master.

share|improve this answer
    
I updated the question,sorry for misleading info. I do not know if it is signed or unsigned because it has the default value. –  usef_ksa Aug 5 at 17:47
    
Nice idea for testing how long downtime. I can see there is a downtime :( –  usef_ksa Aug 5 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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