Changing int column to bigint on table with blob column
0
votes
2
answers
236
views
I have an aws hosted RDS production table called external_documents as defined below. It has about 35k rows and each blob is about 0.5 MB.
CREATE TABLE external_documents
(
id
bigint unsigned NOT NULL AUTO_INCREMENT,
filename
varchar(191) NOT NULL,
mime_type
varchar(191) NOT NULL,
mpi_id
int unsigned NOT NULL,
created_at
timestamp NULL DEFAULT NULL,
updated_at
timestamp NULL DEFAULT NULL,
contents
longblob NOT NULL,
deleted_at
timestamp NULL DEFAULT NULL,
title
varchar(191) DEFAULT NULL,
file_size
int unsigned DEFAULT NULL,
category
varchar(191) DEFAULT NULL,
modality
varchar(191) DEFAULT NULL,
PRIMARY KEY (id
),
KEY external_documents_mpi_id_title_index
(mpi_id
,title
)
) ENGINE=InnoDB AUTO_INCREMENT=35836 DEFAULT CHARSET=utf8mb3 COMMENT='Externally derived documents (e.g. lab results from a fax server)'
We are changing the mpi_id
column from int to bigint. The problem is that the contents
column is a longblob, and changing the key takes hours and blocks access to the table during that time.
**What I've tried**
1. I tried to use pecorna tools pt-online-schema-change
but I don't have process privilege, so that won't work.
2. gh-ost
has a similar limitation.
3. I tried to make a duplicate table using create table ed2 select * from external_documents
, but it blocked access to the external_documents table.
4. I tried to make a duplicate (empty) table using create table ed2 like external_documents
, which completed very quickly, but when I tried to copy the data into the new table using insert into ed2 select * from external_documents
, again it blocked the original table for 20 mins.
5. I tried using online ddl, e.g. ALTER TABLE external_documents modify column mpi_id bigint unsigned not null, ALGORITHM=INPLACE, LOCK=NONE;
but I got ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
6. Similarly, ALTER TABLE external_documents modify column mpi_id bigint unsigned not null, ALGORITHM=instant, LOCK=NONE;
gives ERROR 1221 (HY000): Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE
The table has row_type=dynamic, and since there are only 20 bytes of the blob stored on-page, I figured that this should work faster. Any other recommendations?
Asked by mankowitz
(156 rep)
Aug 21, 2023, 04:59 PM
Last activity: Jun 20, 2025, 11:04 PM
Last activity: Jun 20, 2025, 11:04 PM