MySQL database issues installed under ubuntu16.04

1 Character Set issues

Problem: Executing the./manage.py createdb command in Django appears:

Django.db.utils.OperationalError: (1366, "Incorrect string value: ' \\xC4\\x9A\\xC2\\x81vi ... ' for column ' file ' at row 1" )

To view the MySQL database:

Mysql> Show variables like '%char% '; +--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | UTF8                       | | character_set_connection | UTF8                       | | character_set_database   | latin1                     | | character_set_filesystem | binary                     | | character_set_ Results |    UTF8 |                       | character_set_server     | latin1                     | | character_set_system     | UTF8                       | | Character_sets_dir       |/usr/share/mysql/charsets/|+--------------------------+----------------------------+8 Rows in Set (0.01 sec)

Need to change the value of latin1 to UTF8, modify the method: Edit the file/etc/mysql/my.cnf, add content:

[Client]default-character-set=utf8[mysqld]character-set-server=utf8collation-server=utf8_general_ci

Then restart the MYSQD service:

[Email protected]:/etc/mysql# service MySQL Restart

Go to MySQL authentication:

Mysql> Show variables like '%char% '; +--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | UTF8                       | | character_set_connection | UTF8                       | | character_set_database   | UTF8                       | | character_set_filesystem | binary                     | | character_set_results    | UTF8                       | | Character_set_server |     UTF8 |                       | character_set_system     | UTF8                       | | character_sets_dir       |/usr/ share/mysql/charsets/|+--------------------------+----------------------------+8 rows in Set (0.01 sec)

Summarize:

1 about the configuration file path

as can be seen, the server first reads the/etc/my.cnf file, and if it finds that the file does not exist, then attempts to read from a few subsequent paths in turn.

2 Global Variables

This can be viewed in the case of non-login and permissions: mysqladmin variables-p
This operation is equivalent to logging in using the command show global variables;

3 Database Storage Directory

[Email protected]:/etc/mysql# ls/var/lib/mysqlauto.cnf           debian-5.7.flag  ibdata1      ib_logfile1  MySite  performance_schemaib_buffer_pool   ib_logfile0  ibtmp1       mysql   parchdb          sys

4 MY.CNF

Http://www.blogjava.net/hunter129/archive/2009/01/18/251744.html

Reference:

1 Http://wiki.ubuntu.com.cn/MySQL

Other references:

Option Description
character_set_client
This is what character set the user tells the MySQL query to use.

character_set_connection
after MySQL is queried by the user, it is converted to the Character_set_connection set character set by Character_set_client.

Character_set_results
MySQL converts the stored data into a set of character sets in Character_set_results sent to the user.

Comsenz discuz! does not use set NAMES character
the SET NAMES ' x ' statement is equivalent to these three statements:
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x
and Comsenz discuz! inside is
@mysql_query ("SET character_set_connection= $dbcharset, character_set_results= $dbcharset, character_set_client =binary ", $this->link);

SET character_set_client = binary;
querying using binary character sets

The character set of these three places will solve the garbled problem to a large extent, then what is the function of the three settings?
character_set_client
Specifies the encoding of the SQL statement, if set to Binary,mysql as binary to handle, character_set_connection specifies the code that MySQL uses when running SQL statements, that is, the program sent to MySQL The SQL statement will first be translated from the encoding specified by MySQL from character_set_client to the encoding specified by character_set_connection, if Character_set_clien specifies binary, then MySQL will execute the SQL statement according to the encoding specified by Character_set_connection.

when executing an SQL statement, such as inserting a field into a database, the field also has encoding settings, and if the encoding setting of the field differs from the character_set_connection designation, MySQL converts the inserted data into the encoding of the field setting. Conditional judgments in SQL statements are similar to the execution of SQL INSERT statements.

when SQL executes like the client returns data, it converts the data from the field-specified encoding to the character_set_results-specified encoding, and if Character_set_results=null does not do any conversion actions, (Note that this is set to null is not equal to no settings, MySQL will inherit the global settings when not set), the work is more useful is to use MySQL to transcode, different encoded database sharing data.

option Configuration
configuration file path:/full/path/mysql/bin/my.ini (or my.conf)

[Client]
Default-character-set=utf8

[MySQL]
Default-character-set=utf8

[Mysqld]
init_connect= ' SET collation_connection = Utf8_unicode_ci '
init_connect= ' SET NAMES UTF8 '
Character-set-server=utf8
Collation-server=utf8_unicode_ci
Skip-character-set-client-handshake

* Note:
Using the Default-character-set setting in mysqld, MySQL boot will be error-enabled.

Reference Documents
http://dev.mysql.com/doc/refman/5.5/en/charset-configuration.html
http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf
http://www.iteye.com/problems/11006
http://jianzhong5137.blog.163.com/blog/static/98290492010821115435501/
http://www.itpub.net/thread-1187897-1-1.html
HTTP://IMYSQL.CN/?Q=NODE/20

Set MySQL database display encoding: set charset UTF8;
View MySQL database encoding: Show variables like ' character% ';
In this process, if there is a database encoding problem:
Set MySQL database encoding to UTF-8:
First, check the current database encoding.


To use a statement:
Show variables like '%character% ';
Show variables like '%collation% ';


If this is not the case, you need to set the MySQL encoding to Utf-8. The steps are as follows:


If the MySQL Sever Instance Configuration Wizard is installed when you install MySQL, you only need to start


The program can be set up accordingly. As described in the following, the default encoding needs to be set to UTF8
If you do not have the program, you need to manually modify the MySQL encoding.
1. Edit the MySQL configuration file
MySQL configuration file under Windows generally under the system directory or in the MySQL installation directory named My.ini, you can


Search, Linux is generally/etc/my.cnf
--Add the following to the [Mysqld] tab:
Default-character-set = UTF8
Character_set_server = UTF8
Note: If something like "DEFAULT-CHARACTER-SET=GBK" already exists under this tab, just modify it.
--Add a line under the [MySQL] tab
Default-character-set = UTF8
--Add a line under the [Mysql.server] tab
Default-character-set = UTF8
--Add a line under the [Mysqld_safe] tab
Default-character-set = UTF8
--Add a line under the [Client] tab
Default-character-set = UTF8
2. Restart MySQL Service


Windows can operate in Service Manager, or you can use the command line:
net stop MySQL Enter
net start MySQL return
The service name may not necessarily be MySQL, please press your own settings
Linux is now using service MySQL restart
If a startup failure occurs, check that the configuration file has no setup errors
3. View Settings results
Log on to the MySQL command line client: Open command line
Mysql–uroot–p Enter
Enter password
Execute after entering MySQL: Show variables like "% character%";
The results should look similar to the following:
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
If the encoding is still not UTF8, check the configuration file or use the MySQL command to set it up:
Set character_set_client = UTF8;
Set character_set_server = UTF8;
Set character_set_connection = UTF8;
Set character_set_database = UTF8;
Set character_set_results = UTF8;
Set collation_connection = Utf8_general_ci;
Set collation_database = Utf8_general_ci;
Set collation_server = Utf8_general_ci;
Other than that:
You can use the following command when you create a database:
Create database app_relation character set UTF8;
Use app_relation;
SOURCE App_relation.sql;
The commands to modify the database encoding are:
ALTER DATABASE app_relation character set UTF8;
----------------------------------------------------
1. SET NAMES ' UTF8 ';
It is equivalent to the following three-sentence instruction:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = UTF8;

Recently, in the project group used by the MySQL database, the insertion of data garbled, on this issue to make a summary, we start from the most basic point, to the root causes of errors and solutions.

Basic concepts

? A character (Character) is the smallest of the ideographic symbols in a human language. such as ' A ', ' B ' and so on;
? Given a series of characters, each character is given a numeric value that represents the corresponding character, which is the encoding of the character (Encoding). For example, we give the character ' a ' a value of 0, give the character ' B ' a value of 1, then 0 is the encoding of the character ' a ';
? Given a series of characters and given the corresponding encoding, all of these characters and the set of encoded pairs is the character set (Character set). For example, given a word list characters {' A ', ' B '}, {' A ' =>0, ' B ' =>1} is a character set;
? Word Fu She (Collation) refers to the comparison rules between characters in the same character set;
? After you have determined the character Fu She, you can define what is equivalent and the size relationship between characters on a character set.
? Each character Fu She uniquely corresponds to a character set, but a character set can correspond to a variety of characters Fu She, one of which is the default word Fu She, (default Collation);
? The word Fu She name in MySQL follows the naming convention: begins with the character set name of the word Fu She, _ci (which is case insensitive), _cs (which is case sensitive), or _bin (which means comparison by encoded value). For example: Under the word Fu She "Utf8_general_ci", the characters "a" and "a" are equivalent;

MySQL Character set settings

? System variables:
–character_set_server: Default internal operation character Set
–character_set_client: The character set used by the client source data
–character_set_connection: Connection Layer Character Set
–character_set_results: Query result Character Set
–character_set_database: The default character set for the currently selected database
–character_set_system: System metadata (field name, etc.) character set
– There are also variables that begin with Collation_ to describe the character Fu She.

? Specify the character set of the text string with introducer:
– Format: [_charset] ' string ' [COLLATE collation]
For example
SELECT _latin1 ' string ';
SELECT _utf8 ' Hello ' COLLATE utf8_general_ci;
– Text strings modified by Introducer are converted directly to internal character set processing without excessive transcoding during the request.

The character set conversion process in MySQL

1. When MySQL server receives the request, it converts the request data from character_set_client to character_set_connection;
2. Convert the request data from character_set_connection to the internal operation character set before doing an internal operation, and determine the following method:
-Use the character set value for each data field;
-If the above value does not exist, the default CHARACTER set value of the corresponding data table is used (MySQL extension, non-SQL standard);
-If the above value does not exist, the default CHARACTER set value of the corresponding database is used;
-If the above value does not exist, use Character_set_server to set the value.

3. Convert the operation result from the internal operation character set to Character_set_results.

We are now back to analyze the garbled problem we have:
A our field does not have a character set, so using a table's dataset
b Our table does not specify a character set, which uses the database stored character set by default
C Our database does not specify a character set when it is created, so use Character_set_server to set the value
D We didn't deliberately modify the specified character set for character_set_server, so using MySQL default
e MySQL default character set is Latin1, therefore, we use the latin1 character set, and our character_set_connection character set is UTF-8, inserting Chinese garbled also inevitably.

FAQ Resolution
? FAQ-1 inserting UTF8 encoded data into a data table with a default character set of UTF8, setting the connection character set to UTF8 when queried
– The Character_set_client, Character_set_connection, and Character_set_results are latin1 when plugged in according to the MySQL server's default settings;
– The data for the insert operation will undergo the LATIN1=>LATIN1=>UTF8 character set conversion process, in which each inserted kanji will be saved from the original 3 bytes to 6 bytes;
– The results of the query will undergo the UTF8=>UTF8 character set conversion process, returning the saved 6 bytes intact, resulting in garbled characters. Reference:
? UTF8 encoded data is inserted before the data table with the default character set of Latin1 is set to UTF8 (The mistake we encountered was this one.）
– Character_set_client, Character_set_connection, and character_set_results are all UTF8 when inserted according to the connection character set;
--the insertion data will be converted to a utf8=>utf8=>latin1 character set, and if the original data contains Unicode characters outside the \U0000~\U00FF range, it will be converted to "?" because it cannot be represented in the latin1 character set. (0x3f) symbol, which cannot be restored at a later time, regardless of the connection character set settings. The conversion process is as follows:


some means of detecting character set problems
? SHOW CHARACTER SET;
? SHOW COLLATION;
? SHOW VARIABLES like ' character% ';
? SHOW VARIABLES like ' collation% ';
? SQL function Hex, LENGTH, Char_length
? SQL Functions CharSet, COLLATION

recommendations when using the MySQL character set
? When creating a database/table and doing database operations, try to explicitly indicate the character set used, rather than relying on MySQL's default settings, or the MySQL upgrade may cause a lot of trouble;
? Both the database and the connection character set use Latin1, although in most cases can solve the garbled problem, but the disadvantage is that it is not possible to do SQL operations in a character unit, generally the database and the connection character set are UTF8 is a better choice;
? When using MySQL CAPI (the API for MySQL to provide a C-language operation), use Mysql_options to set the Mysql_set_charset_name property to UTF8 immediately after initializing the database handle, so that you do not have to explicitly use the set The names statement specifies the connection character set and resets the connection character set to UTF8 when the long connection is broken with mysql_ping;
? For the MySQL PHP API, the General page-level PHP program has a short running time, after connecting to the database explicitly set the SET NAMES statement once the connection character sets, but when using a long connection, please keep the connection smooth and after disconnecting with set The names statement explicitly resets the connection character set.

Other Precautions
? The Default_character_set setting in MY.CNF only affects the connection character set when the MySQL command connects to the server, and does not have any effect on applications that use the Libmysqlclient library!
? SQL function operations on fields are usually performed in the internal operation character set and are not affected by the connection character set settings.
? Bare strings in SQL statements are affected by the connection character set or introducer settings, which can produce completely different results for operations such as comparisons, and you need to be careful!

Summary
according to the above analysis and suggestions, we solve the problem we should use what method we should be more clear in mind. Yes, when creating the database, specify the character set, do not go through the modification of the default configuration to achieve the purpose, of course you can also use the form of the specified table character set, but it is easy to omit, especially when many people are involved in the design, more prone to fault.

Although not advocated by modifying the MySQL default character set to solve, but for how to modify the default character set, I still give some methods, for your reference only.

MySQL default character set
MySQL Specifies that the character set can be refined to a single database, a table, and a column. Traditional programs do not use a complex configuration when creating databases and data tables, they use the default configuration.
(1) When compiling MySQL, a default character set is specified, and this character set is latin1;
(2) When installing MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, this value is inherited from the compile-time specified;
(3) When starting mysqld, you can specify a default character set in the command line arguments, if not specified, this value inherits from the configuration in the configuration file, at this time Character_set_server is set to this default character set;
(4) Install MySQL Select Multi-language support, the installer will automatically set the Default_character_set in the configuration file to UTF-8, to ensure that by default all the tables of all the columns of the database with UTF-8 storage.
viewing the default character set
    (by default, MySQL's character set is Latin1 (Iso_8859_1), and how to view it in the above we have given the relevant commands
Modifying the default character set
(1) The simplest method of modification is to modify the character set key values in the MySQL my.ini file,
such as Default-character-set = UTF8
character_set_server = UTF8
after modifying, restart the MySQL service
(2) There is also a way to modify the character set, that is, the command to use MySQL
mysql> SET character_set_client = UTF8;
mysql> SET character_set_connection = UTF8;
mysql> SET character_set_database = UTF8;
mysql> SET character_set_results = UTF8;
mysql> SET character_set_server = UTF8;
mysql> SET collation_connection = UTF8;
mysql> SET collation_database = UTF8;
mysql> SET collation_server = UTF8;

The default character set for the table is UTF8 and the query is sent by UTF-8 encoding, and the database is still garbled. There may be a problem on the connection connection layer. The workaround is to execute the following sentence before sending the query: SET NAMES ' UTF8 '; it corresponds to the following three-sentence instruction:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = UTF8;

MySQL database issues installed under ubuntu16.04