The problem that MySQL database table data cannot be obtained normally in centos

Previously, due to the customer's requirement to install system applications in centos, they had the opportunity to get in touch with centos. By the way, they also learned about General centos operations and MySQL-related operations in centos.

Currently, the technical framework uses JSF (richfaces, facelets) + spring + JPA (openjpa). Instead of using database-dependent triggers or stored procedures, all business logic is executed on the Web server, therefore, you do not need to worry about porting the database. However, a system that runs normally in Windows cannot obtain table content in Linux. Because the database initialization was initially reflected in several manually maintained table data, it was finally found that the database table name was case-sensitive!

Procedure:

1. In Windows, use the mysql client tool "navicat" to export the current database to the test. SQL file. Note: In the exported. SQL file, all table names are in lower case.
2. Copy the exported. SQL file to Linux and import it to the required database.
3. Start the Web server and access the service. Problem: many tables cannot be accessed.
4. Use "show" in MySQL
   Tables; "command. It is found that the table names are repeated. The duplicate table names are case sensitive. The capitalized table names are the same as the corresponding entity bean names. Problem Found: The table name is case-insensitive and the data cannot be accessed. (After MySQL is installed in Linux, the table name is case-sensitive by default, but the column name is case-insensitive. In Windows, the table name is case-insensitive by default)

There are two solutions to the above problems:

• In the JPA object bean declaration, the "name" attribute of "table" is used to specify the table name. The table name should be in lowercase as much as possible, and different words should be connected using underscores (for example:@ Table (name = "sys_user ")

  )

• Use MySQL parameters in Linux to make MySQL Case Insensitive (
  Lower_case_table_names = 1

  )

The first method is a common solution, but developers need to change all the entity beans and re-compile them. The second method is suitable for the situation that the system has entered the production environment, in addition, it requires some professional knowledge and experience on how to modify the MySQL configuration in Linux, and has high technical requirements for implementers.

How to modify the case-insensitive MySql in centos:

After logging on with the root account, add lower_case_table_names = 1 in/etc/My. CNF [mysqld] and restart the mysqld service. After the service is restarted, it is set to be successful: The table name is case-insensitive.

For details about the lower_case_table_names parameter, the lower_case_table_names parameter has two values: [0, 1], 0: case sensitive, and 1: case insensitive.

Note:

For the persistent layer, use the DDL automatically generated by spring to create the corresponding persistent layer database tables and indexes to simplify the implementation.