Mysql – AWS RDS MySQL database created but cannot create indexes or change collation

amazon-rdsMySQLmysql-5.6

AWS RDS MySQL 5.6.x issue:

Created a MySQL database on the AWS RDS platform. Created a dozen tables, imported data to all tables and can SELECT from all tables.

Granted all privileges to user on database schema.

grant all privileges on mysqldb.* to 'username'@'%';

However, cannot create indexes or change collation. The following commands just hang and never return:

alter schema mysqldb default character set utf8 default collate utf8_general_ci;
create index ix_mytable on mytable(mycol);

And there are no error messages reported in the error log on the AWS RDS dashboard or from within MySQL Workbench.

I have a local MySQL database (from which I exported the table data and moved the data to AWS RDS) on which the above-mentioned SQL works without issues.

Any ideas?

Best Answer

Solution:

a. create a user-defined parameter group and use it in the existing database instance instead of the default parameter group.

b. change the character set to utf-8.

I can now create indexes and change the table and database collation.

So the issue was the lack of the user-created parameter group and a parameter group with the correct character set (i.e. utf-8).

Related Question