How do I remove and restore table and column comments on Amazon RDS for MySQL and Amazon Aurora for MySQL?

Short description

When you have tables and columns that include an invalid utf8mb3 character, you might experience the following errors when you perform a major version upgrade:

"[ERROR] [MY-013140] [Server] Comment for field 'example-schema_name.table_name' contains an invalid utf8mb3 character string: '\xD7\xF6\xD6'."

"[ERROR] [MY-013140] [Server] Comment for field 'example-schema_name.table_name.column_name' contains an invalid utf8mb3 character string: '\xC4\xEA\xB5'."

If you have only one comment in the table or column that you need to remove, then run the following commands:

Remove a table comment:

Note: Replace example-database with the database name, example-table with the table name.

ALTER TABLE example-database.example-table COMMENT '';

Remove a column comment:

Note: Replace example-database with the database name, example-table with the table name, example-column with the column name.

ALTER TABLE example-database.example-table MODIFY COLUMN example-column column_definition comment='';

Resolution

Note: The following steps are for tables and columns that have a large amount of comments with invalid utf8mb3 characters.

To remove and restore table and column comments on Amazon RDS for MySQL or Amazon Aurora for MySQL, complete the following steps:

1. From a blue/green deployment green environment, back up the database metadata.
   Note: Replace example-green-rds-host with the Amazon RDS green environment host and example-admin with the admin.

   mysqldump --single-transaction --no-data --all_databases -h example-green-rds-host -u example-admin -p > metadata_backup.sql

2. Create a backup_table_comment.sql for backup all table comments:

   SELECT 
     CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' COMMENT "', table_comment, '";') AS sql_script 
   FROM information_schema.tables 
   WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND TABLE_COMMENT != '';

3. Create a backup_column_comment.sql for backup all column comments:

   SELECT
     CONCAT("ALTER TABLE `",TABLE_SCHEMA,"`.`",TABLE_NAME,"` MODIFY `",
     COLUMN_NAME,"` ",COLUMN_TYPE,
     " ",
     IF(!ISNULL(CHARACTER_SET_NAME) AND !ISNULL(COLLATION_NAME),CONCAT(' CHARACTER SET ',CHARACTER_SET_NAME,' COLLATE ',COLLATION_NAME),' '),
     IF(!ISNULL(COLUMN_DEFAULT),
     CONCAT(" DEFAULT ",
     CASE WHEN COLUMN_DEFAULT='CURRENT_TIMESTAMP' THEN COLUMN_DEFAULT ELSE CONCAT("'",COLUMN_DEFAULT,"'") END),''),
     IF(IS_NULLABLE='YES',' ',' NOT NULL '),
     " ",
     EXTRA,
     " ",
     " COMMENT '", column_comment, "';"
     ) AS sql_script
   FROM
     information_schema.COLUMNS
   WHERE 
     TABLE_SCHEMA NOT IN ('information_schema','mysql','sys','performance_schema')  AND COLUMN_COMMENT != '';

4. Create a remove_table_comment.sql for remove all table comments:

   SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` COMMENT "";') AS sql_script 
   FROM information_schema.tables
   WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

5. Create a remove_column_comment.sql for remove all column comments:

   SELECT
     CONCAT("ALTER TABLE `",TABLE_SCHEMA,"`.`",TABLE_NAME,"` MODIFY `",
     COLUMN_NAME,"` ",COLUMN_TYPE,
     " ",
     IF(!ISNULL(CHARACTER_SET_NAME) AND !ISNULL(COLLATION_NAME),CONCAT(' CHARACTER SET ',CHARACTER_SET_NAME,' COLLATE ',COLLATION_NAME),' '),
     IF(!ISNULL(COLUMN_DEFAULT),
     CONCAT(" DEFAULT ",
     CASE WHEN COLUMN_DEFAULT='CURRENT_TIMESTAMP' THEN COLUMN_DEFAULT ELSE CONCAT("'",COLUMN_DEFAULT,"'") END),''),
     IF(IS_NULLABLE='YES',' ',' NOT NULL '),
     " ",
     EXTRA,
     " ",
     " COMMENT '';"
     ) AS sql_script
   FROM
     information_schema.COLUMNS
   WHERE 
     TABLE_SCHEMA NOT IN ('information_schema','mysql','sys','performance_schema');

6. To generate ALTER table SQL files, run the previous sql comments that you created:
   Note: Replace example-green-rds-host with the Amazon RDS host and example-admin with the admin.

   mysql -h example-green-rds-host -u example-admin -p -s < backup_table_comment.sql  > restore_table_comment_ddl.sql
   
   mysql -h example-green-rds-host -u example-admin -p -s < backup_column_comment.sql >  restore_column_comment_ddl.sql
   
   mysql -h example-green-rds-host -u example-admin -p -s < remove_table_comment.sql  > remove_table_comment_ddl.sql
   
   mysql -h example-green-rds-host -u example-admin -p -s < remove_column_comment.sql > remove_column_comment_ddl.sql

   Note: If the previous restore ddl comments in sql show random characters, then add --default-character-set=utf8 to force the client character-set.

7. Run remove_table_comment_ddl.sql to remove all table comments on the green environment:
   Note: Replace example-green-rds-host with the Amazon RDS host and example-admin with the admin.

   mysql -h example-green-rds-host -u example-admin -p < remove_table_comment_ddl.sql

8. Run remove_column_comment_ddl.sql to remove all column comments on the green environment:
   Note: Replace example-green-rds-host with the Amazon RDS host and example-admin with the admin.

   mysql -h example-green-rds-host -u example-admin -p < remove_column_comment_ddl.sql

9. Perform a major version upgrade:
   For Amazon RDS for MySQL, see Modifying an Amazon RDS DB instance.
   For Amazon Aurora for MySQL, see Modifying an Amazon Aurora DB cluster.

10. Run restore_table_comment.sql to recover table comments on the green environment:
    Note: Replace example-green-rds-host with the Amazon RDS host and example-admin with the admin.

    mysql -h example-green-rds-host -u example-admin -p < restore_table_comment_ddl.sql

11. Run restore_column_comment.sql to recover column comments on the green environment:
    Note: Replace example-green-rds-host with the Amazon RDS host and example-admin with the admin.

    mysql -h example-green-rds-host -u example-admin -p < restore_column_comment_ddl.sql

12. Identify a maintenance window to switchover the DB service.