Mysql – Amazon Aurora MySQL5.6 Performance Schema column datatype modified and can’t be corrected

amazon-rdsauroraMySQLmysql-5.6performance

I'm having trouble getting performance_schema to work in a few of my Aurora instances. Brand new instances where I enable performance_schema work just fine, but several instances that I've had for some time seem to be completely broken and I don't know why. The error logs indicate that the problem is that many columns within performance_schema are of the wrong type.

Excerpt of logs for detail:

2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_current: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history_long: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).

Not all columns are affected in this way but most appear to be. Is there any reason why this might have occurred? Is there a way to fix this? I assume Amazon support should be able to correct it on the back end but they have been less than helpful thus far.

Best Answer

This appears to be the result of the MySQL engine upgrade to version 5.7.3 or later, where the definition of some tables in the performance schema have changed.

If you were running standard MySQL, mysql_upgrade would have fixed that. You'll need to contact Amazon support for ways to upgrade your existing databases to the new engine version.

In the worst case you'd need to create new copies of each affected database and move the data.

Related Question