How to make AWS RDS MySql 5.6 case-insensitive?

Question

I am migrating my windows based Spring - Hibernate application to Amazon AWS. I am getting issues with the Database since the table names in Amazon RDS starts with uppercase letter when hibernate creates them.

eg: userlogin_permissions in Windows local Mysql changes to Userlogin_permissions.

Due to this above error , my server cannot connect to the table and is not working.

I tried using

<prop key="org.hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>

but is not helping.

The following is my xml structure :

  <beans profile="awsSql">
        <bean class="org.apache.tomcat.jdbc.pool.DataSource" id="dataSource">
            <property name="url" value="jdbc:mysql://-----------"/>
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="username" value="@@@@@@@"/>
            <property name="password" value="##########"/>
        </bean>
        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="persistenceUnitName" value="***-jpa"/>
            <property name="dataSource" ref="dataSource"/>
            <property name="jpaDialect">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>
            </property>
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                    <property name="showSql" value="false"/>
                    <property name="generateDdl" value="true"/>
                    <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect"/>
                </bean>
            </property>
            <property name="jpaProperties">
                <props>
                    <prop key="org.hibernate.envers.auditTablePrefix"></prop>
                    <prop key="org.hibernate.envers.auditTableSuffix">_AUD</prop>
                    <prop key="org.hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
                    <!--<prop key="org.hibernate.ejb.naming_strategy">org.hibernate.cfg.DefaultNamingStrategy</prop>-->

                    <!--<prop key="hibernate.cache.provider_class">org.hibernate.cache.SingletonEhCacheProvider</prop>-->
                    <!--<prop key="hibernate.cache.provider_configuration">/META-INF/cache/ehcache.xml</prop>-->
                    <prop key="hibernate.cache.use_second_level_cache">false</prop>
                    <prop key="hibernate.cache.use_query_cache">false</prop>
                    <!--<prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>-->
                    <prop key="hibernate.generate_statistics">false</prop>
                </props>
            </property>
        </bean>

Is there any way in which I can either make RDS use case-insensitive MYSQL , or make hibernate consistent in all OS.

I have already gone through the other related questions in here , but couldn't solve my problem.

I tried changing the parameter to 1 in aws console which did not solve the porblem. I will try the Cli commands also — hybrid, May 18 '16 at 01:58

score 7 · Accepted Answer · edited May 23 '17 at 11:59

7

You're looking to change the lower_case_table_names system variable and setting the value to 1.

How to change MySQL table names in Linux server to be case insensitive?

You'll have to create a new parameter group in RDS and then associate it to a mysql instance which will most likely require a restart. To see the instructions to do it via command-line that can be found below.

http://www.kulawik.de/blog/2011/02/lower_case_table_names/

edited May 23 '17 at 11:59

Community

1
1

answered May 18 '16 at 02:10

Saqib Rokadia

629
7
16

2

note: You can't change this variable if the database already exists. In that case the only thing that you can do is create a new database and configure the `parameter group` that contains the setting `lower_case_table_names = 1` to be the parameter group of the new database. If you try to add the parameter group to an existing MySql 8 database then you'll see an error appear when you try to save the database modification. – Maurice Jan 15 '22 at 19:28

How to make AWS RDS MySql 5.6 case-insensitive?

1 Answers1