1

I'm wondering about the topic of moving all MyISAM tables to InnoDB in RDS. Mostly because I have this annoying warning every day in the RDS console. One thing is pretty clear, the tables of the customer applications must be altered to InnoDB (if possible). No question about that. But what worries my is the "system" tables. If I run this query:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM'

I get lot of tables with MyISAm tables that are not from the customer database but the MySQL itself, for example:

information_schema | COLUMNS                   | MyISAM |
information_schema | EVENTS                    | MyISAM |

Let me call it "system tables".

It is kind of mandatory to also ALTER this "system tables" to InnoDB? I did not found any specific of this tables, and the warning message of RDS is not clear about that:

DB Instance db-byhours-56 contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html#Overview.BackupDeviceRestrictions

Any ideas or comments?

Thanks !

question bonus: what about other kind of engine like MEMORY? I have some customer with few tables like that engine. Can be this engine also a problem to get good snapshots?

2

The MyISAM tables in the mysql schema are fully supported in RDS snapshots. It's the MyISAM tables elsewhere that are potentially problematic.

Never alter the tables in the mysql schema. That will leave you with a server that may never start up again.

Important

Do not convert MySQL system tables in the mysql database from MyISAM to the InnoDB type. This is an unsupported operation.

https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html

The exception for the tables in mysql is because RDS takes care to flush and lock those during a snapshot, so they come out okay on the other side. This isn't documented but can be observed.

The tables in information_schema are not real tables. Leave those alone as well.

The data in MEMORY tables is transient, and not persisted to disk, so they won't survive a snapshot or even a restart. Their definitions should be okay but RDS is really intended for use only with InnoDB. (The FEDERATED storage engine is disabled.)

  • 1
    Agree with the above. RDS prevents altering the engine for the information_schema or mysql tables in mysql RDS. Try to run "alter table information_schema.COLUMNS ENGINE = InnoDB" and you get a "Access denied for user 'sa'@'%' to database 'information_schema'" – firstTimeCaller Oct 3 '18 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.