Shulou(Shulou.com)06/01 Report--

Reason: the value of the original parameter lower_case_table_names in the database is 0, which needs to be modified to case-insensitive, that is, 1. However, after the revision, it was found that the table of the original uppercase letters could not be found.

Modification process

1, modify lower_case_table_names=1 in the parameter file

2. Restart the mysql service

Problem: found that after the modification, the tables of some uppercase letters in the database could not be found.

Test:

1, first set the library to be case-sensitive

Mysql > show variables like 'lower_case_table_names'

+-+ +

| | Variable_name | Value |

+-+ +

| | lower_case_table_names | 0 | |

+-+ +

1 row in set (0.00 sec)

2, create 2 tables with uppercase letters

Mysql > create table Tt (id int)

Query OK, 0 rows affected (0.10 sec)

Mysql > create table tT (id int)

Query OK, 0 rows affected (0.03 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | Tt |

| | tT |

+-+

2 rows in set (0.00 sec)

3. Set lower_case_table_names = 1 in the modified parameter / etc/my.cnf

Restart the database

View the value of the parameter at this time

Mysql > show variables like 'lower_case_table_names'

+-+ +

| | Variable_name | Value |

+-+ +

| | lower_case_table_names | 1 | |

+-+ +

1 row in set (0.00 sec)

4. View the database

Mysql > select * From tT

ERROR 1146 (42S02): Table 'test.tt' doesn't exist

Mysql > select * From Tt

ERROR 1146 (42S02): Table 'test.tt' doesn't exist

The question is:

It is found that when lower_case_table_names = 1 is modified, the previously created table names are not recognized.

Conclusion:

1. You can't change the case of the database at will, otherwise the uppercase table name will not be recognized.

2. The modification of lower_case_table_names parameters is non-dynamic and the database must be restarted.

How to modify the case format:

If the original database is case-sensitive, if you want to convert to case-insensitive, you mainly need to take the following three steps.

1. Export database data through mysqldump

2, change lower_case_table_names = 1 in the my.cnf file, and restart the mysql database.

3. Import the exported data into the mysql database

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.