Fix Amazon Aurora MySQL error 63955: Virtual index ID overflow

Your Amazon Aurora MySQL cluster looks healthy. Reads and writes succeed and every metric is green. Then a routine CREATE INDEX fails with error 63955, and from then on no index that includes a virtual column can be created on the cluster until you rebuild its databases.

Error Code: 63955. 
Index cannot be created because virtual index ID limit (4294967295) was reached.

• What it means: the cluster has used up the internal IDs Aurora MySQL uses when it evaluates indexes that contain a virtual column. This error does not affect existing data or ordinary indexes, but new indexes on VIRTUAL generated columns, functional indexes, and multi-valued indexes fail.
• What helps short term: converting the generated column in the failing index from VIRTUAL to STORED, so that the index no longer contains a virtual column. This does not reset the IDs, and every new index that contains a virtual column still fails.
• What fixes it: a logical dump and restore into a new cluster, with binary log replication to keep the cutover short.
• How to get warning next time: run Aurora MySQL 3.10.0 or later, and alarm on the VIRTUAL_INDEX_ID_OVERFLOW_WARNING message in the error log.

Aurora MySQL assigns each index an internal identifier, the virtual index ID, to track index metadata for undo log management. When you create indexes through DDL operations, these operations consume the identifiers over the life of the cluster, and Aurora MySQL does not provide a way to reset them in place. Large, long-lived clusters with heavy data definition language (DDL) activity can reach the limit. Typical examples are multi-database clusters, schema-per-tenant software as a service (SaaS) platforms, and applications that continuously create, drop, and rebuild indexes.

The impact is narrow but serious. Existing tables continue serving reads and writes, this error does not cause data loss or corruption, and you can still create indexes on ordinary columns. However, CREATE INDEX, ALTER TABLE ... ADD INDEX, and CREATE TABLE fail when the new index contains a virtual column. Table-rebuilding ALTER TABLE statements on tables that already have such an index fail too. Until you rebuild the database, schema migrations and releases that add these indexes are blocked, and so is onboarding new tenants in a schema-per-tenant design whose tables use them.

In this post, we explain the limit and how to recognize error 63955. We then walk through the documented resolution, a logical dump and restore, including the binary log setup, user and grant migration, validation, cutover, and rollback limits. Finally, we set up the monitoring that turns the next occurrence into a scheduled project rather than an incident.

Which engines and versions this applies to

Error 63955 is specific to Amazon Aurora MySQL-Compatible Edition, referred to as Aurora MySQL throughout this post. The code does not exist in upstream MySQL, and the condition does not affect Amazon RDS for MySQL. The Virtual index ID overflow errors documentation says “RDS might throw error code 63955.” That wording appears because you manage Aurora clusters through the Amazon Relational Database Service (Amazon RDS) API and console. Any Aurora MySQL cluster with enough accumulated DDL can reach the limit. What differs by version is whether you get advance warning.

Starting with Aurora MySQL version 3.10.0, the engine writes a warning to the MySQL error log as the limit approaches. Earlier versions give no advance warning, so the first sign is a failed CREATE INDEX. Aurora MySQL 3.10 is also a long-term support (LTS) release. For both reasons, this post restores into 3.10 or later rather than into the source’s version.

To check your version, run the following statement, or look at the Engine version field on the cluster’s Configuration tab in the Amazon RDS console.

SELECT AURORA_VERSION();

For the authoritative description of this limit, refer to Virtual index ID overflow errors and the Aurora MySQL 3.10.0 release notes.

The virtual index ID limit

The documentation states that Aurora MySQL limits virtual index ID values to prevent an issue caused by the undo format in MySQL, and that a cluster with an index above the limit might not be available. When an index approaches the limit, or when you attempt to create one above it, Aurora MySQL returns warning or error code 63955 to the session that ran the statement.

Three points prevent the most common misreadings.

• Aurora MySQL checks the limit only for indexes that contain a virtual column. Every index that the cluster creates uses up an ID, but Aurora MySQL compares the ID with the limit only when the new index includes a virtual column. Indexes on ordinary columns keep working, even on a cluster past the limit. The next section lists the index types that count.
• The number in the error message is a fixed ceiling, not a reading. The client error always prints 4294967295. The error-log warning is different: it includes the ID of the index being created, so subtracting that ID from 4294967295 shows how much headroom is left. In alerting, match the error-log tags shown in Setting up the error-log alarm, not the number.
• The warning is the only place the current ID is reported. No RDS API, Amazon CloudWatch metric, or Amazon CloudWatch Database Insights view shows it, so the error-log warning is your early signal.

Failing the DDL is the protective behavior. Aurora refuses the statement rather than create an index above the limit, so reads and writes on existing tables continue. Because Aurora MySQL does not provide a way to reset the identifier in place, the documented resolution is a rebuild rather than a configuration change.

Which indexes count against the limit

An index counts if it contains a virtual column, whether you define the column or MySQL creates one automatically.

• A secondary index on a VIRTUAL generated column, for example c INT AS (a+b) VIRTUAL with KEY (c). A composite index counts if any one of its columns is virtual.
• A functional index (MySQL 8.0.13 and later), such as KEY ((a+b)) or KEY ((LOWER(name))). MySQL builds these on a hidden virtual generated column, so they count even if you never declared a generated column.
• A multi-valued index on a JSON array, such as KEY ((CAST(j->'$.tags' AS UNSIGNED ARRAY))). These also use a hidden virtual column.

Indexes on ordinary columns and on STORED generated columns still use up IDs, but Aurora MySQL does not check them against the limit.

Recognizing error 63955

Any statement that creates an index containing a virtual column fails. A functional index on a small table is enough to demonstrate it.

CREATE INDEX idx_email_lower ON app_db.customer_profile ((LOWER(email)));
Error Code: 63955. Index cannot be created because virtual index ID limit (4294967295) was reached.

The two signals surface in different places, and that decides what you can monitor.

• Error 63955 is returned to the client session that ran the statement. You see it in your SQL client, your application or schema-migration tool logs, or your deployment pipeline.
• The error log records a warning tagged VIRTUAL_INDEX_ID_OVERFLOW_WARNING (MY-014078) on Aurora MySQL 3.10.0 and higher, as the limit approaches. The log line does not contain 63955, but it does include the ID of the index being created. The session that ran the DDL also receives warning 63955, but the statement succeeds, and most applications and schema-migration tools do not surface SQL warnings. A cluster can therefore write the warning for weeks without anyone noticing.

In the error log, the warning and the error look like the following. The ID values are illustrative.

[Warning] [MY-014078] [InnoDB] [VIRTUAL_INDEX_ID_OVERFLOW_WARNING] Index `idx_email_lower` in table `app_db/customer_profile` has id 4000000123 and is approaching virtual index id limit of 4294967295.
[ERROR] [MY-014079] [InnoDB] [VIRTUAL_INDEX_ID_OVERFLOW_ERROR] Index `idx_email_lower` in table `app_db/customer_profile` cannot be created because index id 4294967296 is above virtual index id limit of 4294967295.

To confirm the diagnosis without touching a production table, create a functional index like the example above on a small, existing, non-critical table, and then drop it. An index on an ordinary column is not a valid test, because it succeeds even on a cluster past the limit. Use an existing table rather than creating one, because CREATE TABLE with an index that contains a virtual column can fail the same way. For retrieving the error log, refer to Monitoring Amazon Aurora log files.

Converting to STORED: A short-term workaround

Because the limit applies only to indexes that contain a virtual column, converting the generated column in a failing index from VIRTUAL to STORED and rebuilding the index takes that index out of the check. In one case, the customer could create new indexes again after this change. It is a workaround, not a fix: the conversion does not reset the IDs, and any new index that contains a virtual column, including a functional or multi-valued index, still fails. Plan the logical rebuild anyway.

Weigh these trade-offs before you use it.

• The conversion rebuilds the table. Changing a column from VIRTUAL to STORED copies the table, which takes time on large tables, and the stored values add storage. The rebuild fails with error 63955 if the table has another index that still contains a virtual column.
• STORED is only valid on generated columns. Applying it to an ordinary column returns Error Code: 1064, a syntax error.

Solution overview

The recommended resolution in the AWS documentation is to recreate your database with a logical dump and restore. A logical rebuild recreates the schema and reloads the data, rather than copying the existing physical structures. Snapshot restore, Aurora cloning, point-in-time recovery, and Blue/Green deployments all produce a physical copy of the volume, and none of them is a documented way to clear this condition.

The procedure has these stages:

1. Turn on binary logging on the source cluster.
2. Clone the source and record the binary log position of the clone.
3. Dump the clone with MyDumper and restore into a new Aurora MySQL 3.10 cluster with MyLoader.
4. Migrate users and grants.
5. Validate the new cluster.
6. Replicate from the source to the new cluster, starting at the recorded position.
7. Cut over by stopping traffic, letting replication catch up, and switching endpoints.

The documentation does not mandate a new cluster as the target. Reloading in place on the same cluster is possible, but it takes the affected databases offline for the whole reload and removes the rollback path. This post uses a new cluster.

Prerequisites

• AWS Identity and Access Management (IAM) permissions to create and modify Aurora DB clusters, instances, and cluster parameter groups (rds:CreateDBCluster, rds:CreateDBInstance, rds:RestoreDBClusterToPointInTime for the clone, rds:ModifyDBCluster, rds:ModifyDBClusterParameterGroup, rds:RebootDBInstance, rds:DescribeEvents), plus logs:PutMetricFilter, logs:PutRetentionPolicy, and cloudwatch:PutMetricAlarm for the alarm.
• A custom DB cluster parameter group on the source cluster. You cannot change binlog_format in the default group.
• An Amazon Elastic Compute Cloud (Amazon EC2) host in the same Region and virtual private cloud (VPC) as the cluster, with MyDumper and MyLoader installed. This post was tested with MyDumper and MyLoader v1.0.11-1 against Aurora MySQL 3.10.3. Pin the version you test with.
• Amazon Elastic Block Store (Amazon EBS) for the dump. Dump files are commonly about a third of the size that information_schema reports, but size the volume with headroom, because the ratio depends on your data.
• A maintenance window for one reboot of the source writer, described in the next section.

Step 1: Turn on binary logging on the source

Replication from the source to the new cluster needs binary logging on the source. On Aurora MySQL, binlog_format defaults to OFF, and changing it from OFF requires a reboot of the cluster. On many clusters, this reboot is the first planned downtime of the migration, so schedule it like one. If binlog_format is already set on your source, skip to the retention step.

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name my-cluster-params \
--parameters "ParameterName=binlog_format,ParameterValue=MIXED,ApplyMethod=pending-reboot"
aws rds reboot-db-instance --db-instance-identifier my-cluster-writer

Then connect to the source writer and retain binary logs for longer than the whole dump, restore, validation, and replication window. Aurora MySQL accepts up to 2,160 hours (90 days).

CALL mysql.rds_set_configuration('binlog retention hours', 168);
CALL mysql.rds_show_configuration;

If the retention period ends before replication starts, the source deletes binary logs the new cluster still needs, and you must start the migration again. For the procedure, refer to Setting up binary log replication for Aurora MySQL.

Step 2: Clone the source and record the binary log position

Dump from an Aurora clone rather than from the source. No production workload competes with the dump, no writer-side DDL can terminate its connections, and nothing writes to the clone, so every MyDumper thread sees the same data. Create the clone after binary logging is on. For the procedure, refer to Cloning a volume for an Amazon Aurora DB cluster.

Replication must start at the source’s binary log position at the moment the clone was created. Do not use a position that MyDumper records from the clone, which describes the clone’s own binary log and would make replication skip or repeat writes. Aurora records the right position as an event on the clone’s writer instance.

aws rds describe-events \
--source-type db-instance \
--source-identifier my-cluster-clone-writer \
--duration 1440 \
--query "Events[?contains(Message, 'Binlog position')].[Date,Message]" \
--output table

The output contains a line like the following. Save the file name and position.

Binlog position from crash recovery is mysql-bin-changelog.000003 4278

The same line also appears in the clone writer’s MySQL error log.

Step 3: Dump the clone and restore into a new cluster

Create the new cluster on the current Aurora MySQL 3.10 LTS release or later. Binary log replication supports a target that is the same version as the source or newer, and the rebuild is the cheapest moment to get the version that writes the advance warning.

Before the dump, set max_execution_time to 0 in the clone’s parameter group. MyDumper SELECT statements on large tables can exceed a non-zero value, and the dump fails with Query execution was interrupted, maximum statement execution time exceeded. The parameter is dynamic. If the dump drops with Lost connection to MySQL server during query, check whether net_read_timeout, net_write_timeout, or wait_timeout have been lowered in your parameter groups, and raise them for the migration window.

Store the credentials in a defaults file rather than on the command line, so that the password does not appear in process listings.

cat > ~/.mydumper.cnf <<'EOF'
[mydumper]
user=admin
password=<password>
[myloader]
user=admin
password=<password>
EOF
chmod 600 ~/.mydumper.cnf

Dump all user databases from the clone’s cluster endpoint, excluding the system schemas.

mydumper \
--defaults-file=$HOME/.mydumper.cnf \
--host=<clone-cluster-endpoint> \
--outputdir=/mnt/full-cluster-dump \
--threads=32 \
--rows=500000 \
--compress \
--events \
--routines \
--triggers \
--sync-thread-lock-mode=SAFE_NO_LOCK \
--regex '^(?!(mysql|sys|information_schema|performance_schema)(\.|$))' \
--verbose=3

MyDumper detects Aurora and refuses to run in its default lock mode, so you must set --sync-thread-lock-mode. Without it, the dump stops with this message:

We support LOCK_ALL and SAFE_NO_LOCK modes for RDS/Aurora. Select one of them to configure --sync-thread-lock-mode

SAFE_NO_LOCK takes no global lock, and it fails the dump if the worker threads do not start at the same point in time, so a dump that completes is consistent. The --regex filter is required, not just tidy. MyDumper applies the pattern to database.table for tables and to the bare schema name for schema-level objects, such as each schema’s CREATE DATABASE statement. The (\.|$) part therefore excludes a system schema name only when a dot or the end of the name follows it. Without it, the filter also skips customer schemas whose names start with the same words, such as sys_audit or mysql_app, and the dump leaves them out without a warning. Without the filter at all, MyDumper reaches the non-transactional table mysql.general_log_backup and stops the consistent dump. A successful run logs lines like these:

Using --trx-tables options, binlog coordinates will not be accurate if you are writing to non transactional tables.
Executing in SAFE_NO_LOCK mode. This backup will fail if all threads are not in the same point in time, which ensures consistency
Transactions started, unlocking tables
Finished dump at: 2026-11-10 15:07:21

The log also reports @@tokudb_version not found - ERROR 1193. MyDumper checks for the TokuDB engine, which Aurora does not have, so you can ignore this line. The warning about --trx-tables and binlog coordinates does not affect this procedure, because replication starts from the position in the clone’s event in Step 2, not from the position MyDumper records.

Then restore into the new cluster.

myloader \
--defaults-file=$HOME/.mydumper.cnf \
--host=<new-cluster-writer-endpoint> \
--directory=/mnt/full-cluster-dump \
--threads=32 \
--verbose=3

The new cluster is empty, so MyLoader needs no option to drop or overwrite existing tables. At the end of the restore, MyLoader compares checksums of each table’s structure and indexes, the views, and the events against values recorded in the dump, and logs a line such as Structure checksum confirmed for shop.orders for each one, followed by Restore completed. These checks cover structure, not row data, so still run the validation in Step 5.

In one migration, a database that information_schema reported at about 400 GB produced a dump of about 115 GB. The reported size includes secondary index storage, which the dump carries only as DDL, plus InnoDB page overhead and fragmentation. Treat the ratio as an illustration, not a planning figure.

Step 4: Migrate users and grants

This is the step most likely to break a cutover, because it surfaces only when the application points at the new cluster and nothing can authenticate. MyDumper and MyLoader do not migrate accounts, which live in the mysql system schema.

On Aurora MySQL 3, which is compatible with MySQL 8.0, GRANT no longer creates a user, and SHOW GRANTS does not include the password. Replaying grants alone therefore fails against accounts that do not exist on the new cluster. Generate SHOW CREATE USER statements first, then SHOW GRANTS, and replay them in that order.

Run the following on the clone, not on the source. The clone matches the binary log position you recorded in Step 2. Any account created or changed on the source after the clone was taken reaches the new cluster through replication in Step 6, instead of appearing in both the script and the binary log. The query skips the RDS-reserved accounts, the AWS-managed roles, and the primary (admin) user, which already exists on the new cluster.

SET SESSION print_identified_with_as_hex = ON;
SELECT CONCAT('SHOW CREATE USER ', QUOTE(user), '@', QUOTE(host), ';')
FROM mysql.user
WHERE user NOT IN ('rdsadmin', 'rdsrepladmin', 'rdsproxyadmin', 'admin',
'mysql.sys', 'mysql.session', 'mysql.infoschema',
'rds_superuser_role')
AND user NOT LIKE 'AWS\_%'
UNION ALL
SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';')
FROM mysql.user
WHERE user NOT IN ('rdsadmin', 'rdsrepladmin', 'rdsproxyadmin', 'admin',
'mysql.sys', 'mysql.session', 'mysql.infoschema',
'rds_superuser_role')
AND user NOT LIKE 'AWS\_%';

Replace admin with your primary user name. print_identified_with_as_hex makes the password hashes print as hexadecimal, because caching_sha2_password hashes can contain characters that do not survive a copy and paste.

Run the generated statements, and save their output as a script with every CREATE USER statement before any GRANT. User-defined roles are accounts in mysql.user, so they come through the same way, as locked accounts with no password. The CREATE USER output for a user also carries that user’s DEFAULT ROLE, and the role it names must already exist when the statement runs. Move the CREATE USER statements for roles (the ones that end in ACCOUNT LOCK) to the top of the script. Replay the script on the new cluster after the data has loaded, because table-level and routine-level grants fail against objects that do not exist yet. Because the hashes transfer unchanged, application passwords stay the same.

On Aurora MySQL version 2, which is compatible with MySQL 5.7, the same two-statement approach works, without the print_identified_with_as_hex setting.

For a role app_read and a user app_ro that has the role as its default, the replay script looks like this (hashes shortened):

CREATE USER `app_read`@`%` IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
CREATE USER `app_ro`@`%` IDENTIFIED WITH 'mysql_native_password' AS '<hash>' DEFAULT ROLE `app_read`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT USAGE ON *.* TO `app_read`@`%`;
GRANT SELECT ON `shop`.* TO `app_read`@`%`;
GRANT USAGE ON *.* TO `app_ro`@`%`;
GRANT `app_read`@`%` TO `app_ro`@`%`;

After the replay, log in to the new cluster as one or two application accounts and run a query they depend on. That confirms both the password hash and the grants before you cut over.

Step 5: Validate the new cluster

Validate more than row counts. COUNT(*) misses lost routines, triggers, events, views, and secondary indexes. Run the following on the clone and on the new cluster, and compare the output.

SELECT s.schema_name,
(SELECT COUNT(*) FROM information_schema.tables t
WHERE t.table_schema = s.schema_name AND t.table_type = 'BASE TABLE') AS tables_,
(SELECT COUNT(*) FROM information_schema.views v
WHERE v.table_schema = s.schema_name) AS views_,
(SELECT COUNT(DISTINCT st.table_name, st.index_name) FROM information_schema.statistics st
WHERE st.table_schema = s.schema_name) AS indexes_,
(SELECT COUNT(*) FROM information_schema.routines r
WHERE r.routine_schema = s.schema_name) AS routines_,
(SELECT COUNT(*) FROM information_schema.triggers tr
WHERE tr.trigger_schema = s.schema_name) AS triggers_,
(SELECT COUNT(*) FROM information_schema.events e
WHERE e.event_schema = s.schema_name) AS events_
FROM information_schema.schemata s
WHERE s.schema_name NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY s.schema_name;

Then compare exact SELECT COUNT(*) results on your largest tables. For critical tables, CHECKSUM TABLE on both sides gives a stronger check, but it reads every row, so run it on the clone rather than on the source.

Finally, run the statement that failed on the old cluster. It should now succeed.

CREATE INDEX idx_email_lower ON app_db.customer_profile ((LOWER(email)));

Step 6: Replicate and cut over

Start replication on the new cluster from the source, using the binary log file name and position you recorded in Step 2. For the procedures, refer to Setting up binary log replication for Aurora MySQL, which covers mysql.rds_set_external_source and mysql.rds_start_replication.

If replication stops on an account statement, such as error 1396 (Operation CREATE USER failed), the replay script and the binary log contain the same account change. Check that you generated the script on the clone, as described in Step 4. If the account already matches the source, skip the duplicate statement with mysql.rds_skip_repl_error and replication continues.

When replication has caught up, cut over:

1. Stop application traffic to the source cluster.
2. Confirm that replication lag on the new cluster is zero, then stop replication.
3. Switch endpoints, using one of the options in the next section.
4. Resume traffic.

The dump and restore happen before this window, so the application is offline only for the switchover. Test the sequence in a non-production environment before you schedule it.

Keeping your application endpoints unchanged

• Rename the cluster identifiers. Rename the old cluster, for example from my-cluster to my-cluster-old, then rename the new cluster to the original name. The cluster endpoints follow the new identifier. Instance endpoints and custom endpoints do not, so rename those separately if your applications use them. Clients also keep the old DNS response until its time to live (TTL) expires, so allow for that in the cutover window. Refer to Modifying an Amazon Aurora DB cluster.
• Use an Amazon Route 53 CNAME. If applications connect through your own DNS name, update the CNAME at cutover. The same TTL consideration applies.
• Use Amazon RDS Proxy. Retarget the proxy to the new cluster without changing connection strings. Refer to Using Amazon RDS Proxy.

Considerations and limitations

• The binary log reboot. Turning on binary logging on the source requires a reboot, as described in Step 1.
• Rollback only lasts until the first schema change. For a rollback path, configure reverse replication from the new cluster to the old one before cutover. This requires binary logging on the new cluster as well, which you can turn on before it takes traffic. The old cluster is still at the identifier limit, so any index DDL that runs on the new cluster after cutover replicates back, fails with 63955, and stops reverse replication. Freeze index DDL until you have decided not to roll back, or accept that rollback is valid only until the first schema change.
• Dump the clone, not the live cluster. Replication starts from the clone’s binary log position in Step 2, so the dump must come from the same clone. A dump of the live cluster would not line up with that position.
• In-place reload. Reloading on the same cluster avoids a second cluster but takes the databases offline for the whole reload and has no rollback.

Cost considerations

This migration runs paid resources in parallel. Plan for the following.

• Two Aurora clusters. The source and the new cluster both bill for instance hours and storage during migration and validation.
• An Aurora clone. The clone bills for its DB instances. Its storage cost starts small and grows as the clone and source diverge.
• An Amazon EC2 dump host and the Amazon EBS volume that holds the dump files.
• Binary log retention on the source, which adds storage for the retained logs.

Keep the validation window as short as your testing allows, and clean up as soon as validation passes.

Clean up

Most of the clean-up is ordinary deletion. Terminate the Amazon EC2 dump host, delete the Amazon EBS volume that held the dump (it is not deleted with the instance unless its DeleteOnTermination attribute is set), and delete the clone’s instances and then the clone cluster. For the procedure, refer to Deleting Aurora DB clusters and DB instances.

Three steps are specific to this migration.

Stop replication. On the new cluster, and on the old one if you configured reverse replication, run the following. The second procedure has a different name on Aurora MySQL version 2. Refer to Configuring, starting, and stopping binary log replication.

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_source;

Clear binary log retention on any cluster you keep that no longer replicates, so that it stops retaining log files. The value 0 is not accepted.

CALL mysql.rds_set_configuration('binlog retention hours', NULL);

Delete the old cluster last, with a final snapshot. Do this only after the new cluster has served production traffic normally and you no longer need the rollback path. The old cluster holds the only copy of your pre-migration data. Turn off deletion protection, delete its instances, then delete the cluster with a final snapshot.

aws rds modify-db-cluster --db-cluster-identifier my-cluster-old \
--no-deletion-protection --apply-immediately
aws rds delete-db-cluster --db-cluster-identifier my-cluster-old \
--final-db-snapshot-identifier my-cluster-old-final \
--no-delete-automated-backups

Prevention

• Alarm on the virtual index ID overflow messages in the error log. This is your only advance signal. The next section walks through the setup.
• Reduce unnecessary DDL churn. Avoid repeatedly creating and dropping indexes, and treat table-rebuilding ALTER TABLE as drawing down a finite budget.
• Limit indexes that contain virtual columns on clusters with heavy DDL. Aurora MySQL does not check indexes on ordinary columns or STORED generated columns against the limit.
• Plan a periodic logical rebuild for databases with very heavy DDL activity, so that the work happens on your schedule.
• Stay on Aurora MySQL 3.10.0 or later. Earlier versions give no advance warning.

Setting up the error-log alarm

Turn on error-log export to Amazon CloudWatch Logs for the cluster.

aws rds modify-db-cluster \
--db-cluster-identifier my-cluster \
--cloudwatch-logs-export-configuration '{"EnableLogTypes":["error"]}'

Aurora creates the log group /aws/rds/cluster/my-cluster/error with no expiry, so set a retention period.

aws logs put-retention-policy \
--log-group-name /aws/rds/cluster/my-cluster/error \
--retention-in-days 90

Create a metric filter that matches the warning. The pattern matches the two tags that Aurora MySQL writes to the error log: VIRTUAL_INDEX_ID_OVERFLOW_WARNING as the limit approaches, and VIRTUAL_INDEX_ID_OVERFLOW_ERROR when an index is refused. Match the tags rather than the message text. Filter patterns are case sensitive, and the error-log message spells id in lowercase, unlike the client error. The bare number 63955 is also a poor match, because it can appear in log sequence numbers, thread IDs and byte counts.

aws logs put-metric-filter \
--log-group-name /aws/rds/cluster/my-cluster/error \
--filter-name aurora-virtual-index-id-warning \
--filter-pattern '?"VIRTUAL_INDEX_ID_OVERFLOW_WARNING" ?"VIRTUAL_INDEX_ID_OVERFLOW_ERROR"' \
--metric-transformations \
metricName=VirtualIndexIdWarning,metricNamespace=Custom/AuroraMySQL,metricValue=1,defaultValue=0

Test the pattern before relying on it. A healthy cluster has no warning line to test with, so the following command uses a sample line in the format that Aurora MySQL writes. The output should show one match.

aws logs test-metric-filter \
--filter-pattern '?"VIRTUAL_INDEX_ID_OVERFLOW_WARNING" ?"VIRTUAL_INDEX_ID_OVERFLOW_ERROR"' \
--log-event-messages '[Warning] [MY-014078] [InnoDB] [VIRTUAL_INDEX_ID_OVERFLOW_WARNING] Index `idx_b` in table `app_db/orders` has id 4000000123 and is approaching virtual index id limit of 4294967295.'

Then create the alarm. Aurora writes the error log to disk every five minutes and rotates it hourly, so a one-hour period is appropriate.

aws cloudwatch put-metric-alarm \
--alarm-name aurora-virtual-index-id-warning-my-cluster \
--alarm-description "Aurora MySQL virtual index ID overflow warning or error" \
--namespace Custom/AuroraMySQL \
--metric-name VirtualIndexIdWarning \
--statistic Sum \
--period 3600 \
--evaluation-periods 1 \
--threshold 1 \
--comparison-operator GreaterThanOrEqualToThreshold \
--treat-missing-data notBreaching \
--alarm-actions arn:aws:sns:us-east-1:111122223333:my-dba-topic

--treat-missing-data notBreaching matters because a healthy cluster can go hours without writing to the error log. Metrics extracted from log events are billed as custom metrics.

Covering every cluster

A metric filter applies to one log group, and each cluster has its own. The following loop sets up export, retention, the filter, and the alarm for every Aurora MySQL cluster in the Region. Log export needs no parameter group change, so the loop also works on clusters that use the default parameter group.

FILTER='?"VIRTUAL_INDEX_ID_OVERFLOW_WARNING" ?"VIRTUAL_INDEX_ID_OVERFLOW_ERROR"'
TOPIC=arn:aws:sns:us-east-1:111122223333:my-dba-topic
for c in $(aws rds describe-db-clusters \
--query "DBClusters[?Engine=='aurora-mysql'].DBClusterIdentifier" --output text); do
aws rds modify-db-cluster --db-cluster-identifier "$c" \
--cloudwatch-logs-export-configuration '{"EnableLogTypes":["error"]}' >/dev/null
lg="/aws/rds/cluster/$c/error"
until aws logs describe-log-groups --log-group-name-prefix "$lg" \
--query 'logGroups[0].logGroupName' --output text | grep -q "$lg"; do sleep 30; done
aws logs put-retention-policy --log-group-name "$lg" --retention-in-days 90
aws logs put-metric-filter --log-group-name "$lg" \
--filter-name aurora-virtual-index-id-warning --filter-pattern "$FILTER" \
--metric-transformations "metricName=VirtualIndexIdWarning-$c,metricNamespace=Custom/AuroraMySQL,metricValue=1,defaultValue=0"
aws cloudwatch put-metric-alarm --alarm-name "aurora-virtual-index-id-warning-$c" \
--namespace Custom/AuroraMySQL --metric-name "VirtualIndexIdWarning-$c" \
--statistic Sum --period 3600 --evaluation-periods 1 --threshold 1 \
--comparison-operator GreaterThanOrEqualToThreshold \
--treat-missing-data notBreaching --alarm-actions "$TOPIC"
done

The metric name includes the cluster identifier, so each alarm tracks one cluster. For an infrastructure-as-code version, the metric filter and alarm map to the AWS::Logs::MetricFilter and AWS::CloudWatch::Alarm AWS CloudFormation types.

For reference, refer to Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs and Creating metrics from log events using filters.

Conclusion

Error 63955 means the cluster has used up the virtual index IDs Aurora MySQL uses when it evaluates indexes that contain a virtual column, including functional and multi-valued indexes. Converting a generated column to STORED is a short-term workaround for one index, no physical copy clears the condition, and the documented resolution is a logical dump and restore into a new cluster.

Plan four things carefully: the reboot to turn on binary logging, the binary log position of the clone, users and grants as their own step, and the rollback limit after the first schema change. Restore into Aurora MySQL 3.10 or later, and alarm on the VIRTUAL_INDEX_ID_OVERFLOW_WARNING message in the error log so the next cluster gives you notice.

If you are unsure which restore path fits your cluster and engine version, raise a case with AWS Support before you commit to a migration window.

For more detail, refer to Virtual index ID overflow errors, the Aurora MySQL 3.10.0 release notes, and Migrate very large databases to Amazon Aurora MySQL using MyDumper and MyLoader.

About the author