Sample Header Ad - 728x90

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