Changing int column to bigint on table with blob column

Question

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?

Answer 1

The first thing I would do is create the temp table

CREATE TABLE ed2 LIKE external_documents;
ALTER TABLE ed2 MODIFY column mpi_id BIGINT UNSIGNED NOT NULL;

Next, create SQL script to INSERT row by row from external_documents to ed2 using this bash script (call the bashg script make_script.sh)

DB=mydb
RDSENDPT=mydb....amazon.com
SQL="SELECT MIN(id) FROM external_documents"
MIN_ID=$(mysql -h${RDSENDPT} -uroot -ppassword -D${DB} -ANe"${SQL}")
SQL="SELECT MAX(id) FROM external_documents"
MAX_ID=$(mysql -h${RDSENDPT} -uroot -ppassword -D${DB} -ANe"${SQL}")
(
echo "USE ${DB}"
for ID in $(seq ${MIN_ID} ${MAX_ID})
do
    echo "REPLACE INTO ed2 SELECT * FROM external_documents WHERE id=${ID};"
done
) > inserts.sql

Execute the Bash Script

chmod +x make_script.sh
./make_script.sh

Examine the SQL Script

less inserts.sql

It should look something like

USE mydb
REPLACE INTO ed2 SELECT * FROM external_documents WHERE id=1;
REPLACE INTO ed2 SELECT * FROM external_documents WHERE id=2;
...
REPLACE INTO ed2 SELECT * FROM external_documents WHERE id=35834;
REPLACE INTO ed2 SELECT * FROM external_documents WHERE id=35835;

If you are satisfied with the SQL script, then login to mysql on that RDS endpoint and execute

mysql> source inserts.sql

or pipe the SQL script

mysql -h... -uroot -ppassword -Dmydb < inserts.sql

Please test this with a small table and see if this works for you.

NOTE : You can actually run this SQL script repeatedly if there are still live writes.

If it works, then swap the tables

USE mydb
FLUSH TABLES external_documents;
RENAME TABLE external_documents TO old_external_documents,ed2 TO external_documents;

When reads and writes are verified to the new table, drop the old table.

DROP TABLE old_external_documents;

Answer 2

According to Online DDL Operations - Column Operations the operation "Changing the column data type" cannot use Instant / In Place algorithm and always cause Rebuilds Table action.

Try the next:

1. Add new column of needed datatype.
2. Copy existing data into new column.
3. Drop old column.
4. Rename new column.

All mentioned DDL operations can be performed using Instant or Inplace algorithm (depends on actual MySQL version, see "Syntax and Usage Notes").