Mysql – Changing data type from mediumint to int without downtime

alter-tabledatatypesMySQLreplication

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 |

Best Answer

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.

Related Solutions

Sql-server – Changing a column data type from datetime to float

With no data in the column you can just drop it and add it again.

alter table MyTable drop column MyColumn;

go

alter table MyTable add MyColumn float;

If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column.

exec sp_rename 'MyTable.MyColumn', 'Temp_MyColumn', 'COLUMN';

go

alter table MyTable add MyColumn float;

go

update MyTable 
set MyColumn = convert(float, Temp_MyColumn) 
where Temp_MyColumn is not null;

go

alter table MyTable drop column Temp_MyColumn;

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Sql-server – Changing a column data type from datetime to float

Mysql – Adding index to large thesql tables

Related Question