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

Today, a colleague inquired about the problem of mysql, and it was a bit strange that an error was reported when accessing a certain table in the main library of mysql, but it was OK on the standby database. Even if it's out of sync

It can't be said that the main library cannot be accessed.

Since it is active and standby, let's start with the synchronization of active and standby. All kinds of show master status\ G, show binary logs, show slave status\ G on the repository.

On the repository, you can see:

Salve_IO_Runing: NO

Salve_SQL_Runing: YES

Obviously it is out of sync. We can see that Relay_Master_Log_File is mysql-bin.000064, but what show binary logs looks at on the main library is: 67pc68pct 69

There is no more 64pr 65pr 66.

In the preparation of the database also saw that the error of Last_IO_Error is very obvious, 1236 serious error, Could not find first log file name in binary log index file.

Confirmed out of sync, so why can't the tables of the main library be accessed? Permission confirmed, no problem. The most common problem encountered should be the case problem, which is also mysql.

Where you've done a bad job. Query all lowercase tables in the main database, no errors are reported, and queries with mixed case tables all report errors, with the same prompt:

ERROR 1146 (42S02): Table 'craw.sitePageConfig' doesn't exist

Query the lower_case_table_names parameter under my.cnf and find that sure enough, the parameter is 1, and the default is 0. Check the modification date of the my.cnf file, which is 2 days ago.

The my.cnf on the library is half a year old. After the cause has been roughly found, how can it be solved? Change it back?

Change it back (change it to 0) it should be OK. To be verified. In fact, you will encounter this kind of error under Linux/Unix, but not under windows. Why. Because in MySQL

The database is equivalent to the directory under the data directory, and the table is equivalent to the files under the directory, while the Linux/Unix system is case-sensitive (windows is not sensitive), creating a mixed case

After the library or table, we modify the parameter lower_case_table_names=1, and MySQL translates all table names in SQL to lowercase (for case insensitivity)

However, there are no all lowercase files or directories (tables or libraries) under Linux. There is a situation that can not be found.

If you develop under the windows platform and put it on Linux after that, you are likely to encounter this error. Case sensitivity must be removed.

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.