Table isn't found due to case-sensitivity MySQL query using .NET MySQL Connector

Question

I am using an old website that I've created using .NET 4.0 with XSD (Table Adapters). I've migrated my database from MySQL 5.4 to MySQL 5.7 on AWS RDS.

The problem that I have is that when I run the website, it can't find the table because in some of the queries it searches for a table with a capital letters in the beginning. If I change the table name to with capital letter, it finds it. It's an old project, and I prefer not messing around with the old code.

I use .NET MySQL Connector.

show variables where Variable_name='lower_case_table_names'

The above returns '0' which means 'lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.'

Is there a way to update the web.config or do something in the MySQL database to make it work so the query will count as case insensitive?

Answer 1

Try with like where Variable_name like 'lower_case_table_names