Solution to the Chinese garbled characters problem in MySQL under Ubuntu

Find the problem

I have been learning Django recently and encountered the following problem when inserting data in conjunction with MySQL data:

/usr/local/lib/python2.7/dist-packages/Django-1.11.dev20170117002028-py2.7.egg/django/db/backends/mysql/base.py:109: Warning: Incorrect string value: '\xE6\x88\x90\xE5\x8A\x9F...' for column 'json' at row 1
 return self.cursor.execute(query, args)
[07/Feb/2017 12:15:21] "GET /index/ HTTP/1.1" 200 250

Chinese cannot be inserted into MySQL database~! ~!

View database encoding

mysql> show create database bangjob;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| bangjob | CREATE DATABASE `bangjob` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

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.00 sec)

Modify database encoding

mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)

View the modified results

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.00 sec)

There will be no problem in continuing to insert it at this time.

It would be nice if it were that simple, because such changes will become invalid after restarting MySQL! ! !

Continue to look for other methods

sudo gedit /etc/mysql/my.cnf

Add the following information to the corresponding node in the my.cnf file:

[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
[mysql]
default-character-set=utf8

Then restart MySQL:

/etc/init.d/mysql start

If you can restart, check the database encoding again:

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)

It would be nice if it were really like this. Things are never as simple as we imagine:

When restarting the MySQL service, it was found that it was always in a waiting state (PS: I guess a deadlock occurred or something). At this time, execute:

mysql -u root -p

An exception will be thrown:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I just want to modify the code, why is it so hard?

I have tried many ways to solve this problem (restart, restore). . . . .

sudo /etc/init.d/mysql status

Check the status of mysql: mysql respawn/post-start, (post-start) process 55665

These methods can't solve the problem, so let's start with the log...

Find the log file /var/log/mysql/error.log

Continue to find a solution to ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) .

Answer:

[mysqld] Change default-character-set=utf8' to character_set_server=utf8

OK, we can finally restart MySQL, and the encoding we set after the restart will still be effective.

Of course, the database created before needs to be recreated T_T

Because show create database bangjob; shows that the data created before is still encoded in latin1

Summarize

The above is the full content of this article. I hope that the content of this article can be of some help to your study or work. If you have any questions, you can leave a message to communicate.

You may also be interested in:

How to solve the problem of Chinese garbled characters when inserting table data into MySQL
Solve the problem of Chinese garbled characters when inserting data into MySQL by Tomcat under Linux
Detailed explanation of the solution to garbled characters when JDBC connects to MySQL to process Chinese
Detailed explanation of the Chinese garbled characters problem in MySQL database
Solution to Chinese garbled characters when operating MySQL database in CMD
How to solve the Chinese garbled problem when servlet adds data to mysql
Solve the problem of garbled Chinese characters in Mysql5.7
Solution to the problem of Chinese garbled characters when inserting data into JSP MySQL
Solution to the Chinese garbled code problem in Mac Mysql database
Solution to the Chinese garbled code problem in the decompressed version of MYSQL

<<: How to redirect to https through nginx load balancing

>>: How to create a child process in nodejs