How to solve mysql query error when using table name in capital letter?

How to solve mysql table name case sensitive issue when writing SQL query? lower_case_table_names Settings in MySQL 8.0.12.

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix.

 

You can solve this issue by reinitialzing MySql, while doing this you will loss all your data so you must export database as a first step.

To export/keep database as a backup refer to my below link where I have explained how to do it:

https://www.webonsky.com/how-to-import-and-export-large-database-in-mysql-using-command-line

 

Follow below steps in order to solve casesensitive issue:

 

Stop the MySQL service:

sudo service mysql stop

 

Delete the MySQL data directory:

sudo rm -rf /var/lib/mysql

 

Recreate the MySQL data directory (yes, it is not sufficient to just delete its content):

sudo mkdir /var/lib/mysql    

sudo chown mysql:mysql /var/lib/mysql

sudo chmod 700 /var/lib/mysql

 

Add lower_case_table_names = 1 to the [mysqld] section in /etc/mysql/mysql.conf.d/mysqld.cnf

 

Re-initialize MySQL with --lower_case_table_names=1

sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

 

Start the MySQL service:

sudo service mysql start

 

Retrieve the new generated password for MySQL user root:

sudo grep 'temporary password' /var/log/mysql/error.log

 

Change the password of MySQL user root by:

sudo mysql -u root -p

if you face any issue in login with temporary password, run below command to restart mysql:

sudo service mysql restart

 

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

 

Login with your root user, to check whether your database support caseinsensitive or not, execute below query to verify:

sudo mysql -u root -p

show variables like 'lower_case_%';

 

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | OFF   |

| lower_case_table_names | 1     |

+------------------------+-------+