How to Configure MySQL to be Case Insensitive?

MySQL
1

I know that set the lower_case_tables_names can do it. But in Aiven MySQL, how should I do?

2
  1. Access your Aiven MySQL instance: Log in to the Aiven web console or use the Aiven command-line interface (CLI) to access your MySQL service.
  2. Modify the configuration: Aiven provides a way to modify certain parameters of your service through the web console or CLI. You’ll need to modify the my.cnf configuration file.
  3. Set lower_case_table_names: In your my.cnf file, set the lower_case_table_names parameter to the desired value. This parameter determines how the MySQL server handles table names’ case sensitivity.You can set it to either 0, 1, or 2:
  • 0: Table names are stored as specified and comparisons are case sensitive.
  • 1: Table names are stored in lowercase on disk and comparisons are not case sensitive. This is what you want for a case-insensitive setup.
  • 2: Table names are stored as given but compared in lowercase.
avn service update -t my-service-name -c "my.cnf:lower_case_table_names=1"
.

Replace my-service-name with the name of your Aiven MySQL service

SHOW VARIABLES LIKE 'lower_case_table_names';

This should return the value you set earlier.

I hope By following these steps, you should be able to configure MySQL to be case insensitive in your Aiven environment.

Best regard
Danish Hafeez | QA Assistant
ICTInnovations

3

Hello there i tried to use the command that you gave us but it says that option doesn’t exit
Can you provide more assistance? Thank You in Advance

4

This is a known issue that has been previously reported.

Here are some articles that could help with possible workarounds:
B.3.4.1 Case Sensitivity in String Searches
14.8 String Functions and Operators

5

Hello Jennjunod, and everyone.

unfortunately there are cases in which you don’t wrote your software, but you buy the software, and it came “as-is”, and require the case insensitive option on the db.

Has someone been able to set this parameter in the my.cnf ? Maybe opening a support ticket to AIVEN directly, they could operate manually on the instance ?

many thanks in advance, regards.