Using Amazon RDS & lower_case_table_names

Posted on Posted in AWS, RDS

Amazon RDS is a fantastic service for managed databases – a reliable solution which is hazzle-free and cluster-able.
In the background of Amazon RDS is a MySQL 5.1 Instance on an arbitrary Linux system. MySQL has one beloved “feature”; it is case sensitive – but to make it consistent and helpful – only on *nix systems. In our company I’ve set the standard just to use uppercases in SQL statements but even this won’t help you in case of GSQL or HQL queries, theses statements are build by the engines itself and so there is no possibility to force an uppercase field or table accessor.
As the most popular databases are not using case sensitive field or table names and even MySQL does make an exception on Windows this “feature” is complete garbage in my opinion.
Thankfully RDS does support lower_case_table_names since some weeks, I’ve changed that setting on RDS and could deploy Atlassian Jira successfully on AWS.

To manage this settings you need to download the RDSCLI Tools and set some parameters in the shell:
export AWS_RDS_HOME=dir where you unzipped the cli tools
export EC2_CERT=~/Desktop/aws-cert.pem
export EC2_PRIVATE_KEY=~/Desktop/aws-pk.pem
export EC2_REGION=eu-west-1
export EC2_AVAILABILITY_ZONE=eu-west-1a

I’m using the cert authentication in favor of the token. You can download your cert and pk (private key) from here.

After unpacking and setting export parameters you need to create a parameter group first:
rds-create-db-parameter-group lctn -f mysql5.1 -d "My first database parameter group"

Actually you can only set the parameters using the command line tools, so take this command to configure the newly created parameter group:
rds-modify-db-parameter-group lctn --parameters "name=lower_case_table_names, value=1, method=pending-reboot"

After all you can create the DB Instance on RDS:
rds-create-db-instance clouddb \
--allocated-storage 5 \
--db-instance-class db.m1.small \
--engine MySQL5.1 \
--backup-retention-period 7 \
--master-username mysuperuser \
--master-user-password thisisfancy \
--availability-zone eu-west-1a \
--db-parameter-group-name lctn \
--db-security-groups default

If you have already a running RDS instance and want to change that parameter group, you can use this command:
rds-modify-db-instance myoldInstance --db-parameter-group-name lctn
Please note that you will need to export your databases and import them again if there are already tables in your db which are using upper case names.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *