MySQL database table collation inconsistency results in a query that does not work on the index

Source: Internet
Author: User

MySQL database table collation inconsistency results in a query that does not work on the index

Table is more descriptive: Add the Worktask table in the MySQL database to the Ishaspic field.

How to Do: (1) Database Worktask table New Add whether there is a picture field ishaspic; new field, error

[SQL] ALTER TABLE worktask add Ishaspic Int (ten) Null;
[ERR] 1034-incorrect key file for table ' Worktask '; Try to repair it

Solution: Create a new Worktask table, add the Ishaspic field, and insert the data from the original Worktask table into the new table.

(2) Production environment deployment change code, after the application started, a period of time the background error

Org.hibernate.exception.GenericJDBCException:Could Not open connection

Java.sql.SQLException:An attempt by a client to checkout a Connection have timed out.

(3) Monitoring the database service, found that the query SQL suddenly slowed down, the database server CPU Resource usage is very high.

(4) Change fallback, error still exists.

The original application can run normally, and now can not run properly, monitoring the database, found that the SQL query execution time suddenly slowed down, the execution of SQL alone, the discovery is also very slow to reach the hundred second level.

Problem locating: When you create a new Worktask table, you change the collation of the table so that it does not have the same collation as the table, causing the table to query the SQL statement and the index does not work.

Solution: (1) The query SQL is split, and (2) the collation of the data table is adjusted to ensure that the collation of the table data table is consistent.

*************************************************************************************

The collation in MySQL. The character set and collation are often selected when creating a new MySQL database or table. The character set used by the database everybody knows what's going on, what sort of rules is that?

Collation: Refers to a comparison rule for different characters under a specified character set. Its characteristics include the following points:

1, two different character sets cannot have the same collation

2, two character sets have a default collation

3, there are some common naming rules. such as the _ci end indicates case insensitive (caseinsensitive), _cs is case sensitive, and _bin indicates binary comparison (binary).

In the MySQL database, you can use show collation to see the various sorting rules supported, I use MySQL 5.6.12-log version of the database, support 219 kinds of collation.

MySQL database table collation inconsistency results in a query that does not work on the index

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.