Brief analysis of the MySQL character set causing database recovery errors

Brief analysis of the MySQL character set causing database recovery errors

Importing data with incorrect MySQL character set encoding will prompt an error. This is the same as inserting data. If the saved data is different from the MySQL encoding, there will definitely be problems with importing garbled characters or inserting data being lost. Let's take a look at an example.

<script>ec(2);</script>

Database recovery error: Due to character set issues, the original database default encoding is latin1, and the encoding of the newly backed up database is utf8, which leads to recovery errors.

[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/11x-B-2014-06-18.sql 
ERROR 1064 (42000) at line 292: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[caption id=\"attachment_271\" align=\"aligncenter\" width=\"300\"]<a href=\"ht' at line 1

Repair method (not tested):

[root@Test ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' --default-character-set=latin1 t4x < /tmp/11x-B-2014-06-18.sql 
MySQL
-- MySQL dump 10.13 Distrib 5.5.37, for Linux (x86_64)
--
-- Host: localhost Database: t4x
-- ------------------------------------------------------
--Server version 5.5.37-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `t4x`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET utf8 */;
--
-- Table structure for table `wp_baidusubmit_sitemap`
--
DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_baidusubmit_sitemap` (
 `sid` int(11) NOT NULL AUTO_INCREMENT,
 `url` varchar(255) NOT NULL DEFAULT '',
 `type` tinyint(4) NOT NULL,
 `create_time` int(10) NOT NULL DEFAULT '0',
 `start` int(11) DEFAULT '0',
 `end` int(11) DEFAULT '0',
 `item_count` int(10) unsigned DEFAULT '0',
 `file_size` int(10) unsigned DEFAULT '0',
 `lost_time` int(10) unsigned DEFAULT '0',
 PRIMARY KEY (`sid`),
 KEY `start` (`start`),
 KEY `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
0
1
[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/t4x-B-2014-06-17.sql 
ERROR 1064 (42000) at line 295: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''i' at line 1

MySQL

--MySQL dump 10.11
--
-- Host: localhost Database: t4x
-- ------------------------------------------------------
--Server version 5.0.95-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- 
-- Current Database: `t4x`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t4x` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `t4x`;
--
-- Table structure for table `wp_baidusubmit_sitemap`
--
DROP TABLE IF EXISTS `wp_baidusubmit_sitemap`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_baidusubmit_sitemap` (
 `sid` int(11) NOT NULL auto_increment,
 `url` varchar(255) NOT NULL default '',
 `type` tinyint(4) NOT NULL,
 `create_time` int(10) NOT NULL default '0',
 `start` int(11) default '0',
 `end` int(11) default '0',
 `item_count` int(10) unsigned default '0',
 `file_size` int(10) unsigned default '0',
 `lost_time` int(10) unsigned default '0',
 PRIMARY KEY (`sid`),
 KEY `start` (`start`),
 KEY `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Character set related:

MySQL

mysql>show variables like '%character_set%';
--------------------------------------------- 
| 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql>set names gbk;
mysql>show variables like '%character_set%';
--------------------------------------------- 
| Variable_name | Value |
--------------------------------------------- 
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql>system cat /etc/my.cnf | grep default #Client sets the character set client below default-character-set=gbk
mysql>show variables like '%character_set%';
--------------------------------------------- 
| Variable_name | Value |
--------------------------------------------- 
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
--------------------------------------------- 
mysql> system cat /etc/my.cnf|grep character-set-server #Client sets the character set mysqld below character-set-server = cp1250 
mysql> show variables like '%character_set%';
-------------------------- -------------------------------------------- 
| Variable_name | Value |
-------------------------- -------------------------------------------- 
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | cp1250 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | cp1250 |
| character_set_system | utf8 |
| character_sets_dir | /byrd/service/mysql/5.6.26/share/charsets/ |
-------------------------- -------------------------------------------- 
8 rows in set (0.00 sec)

Some other setting methods:

Modify the character set of the database

  mysql>use mydb
  mysql>alter database mydb character set utf-8;

Create a database and specify the character set of the database

  mysql>create database mydb character set utf-8;

Modify through the configuration file:

Modify /var/lib/mysql/mydb/db.opt

default-character-set=latin1
default-collation=latin1_swedish_ci

for

default-character-set=utf8
default-collation=utf8_general_ci

Restart MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart

Modify via the MySQL command line:

mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)

Check:

mysql> show variables like 'character_set_%';
--------------------------------------------- 
| 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.03 sec)
mysql> show variables like 'collation_%';
---------------------- ----------------- 
| Variable_name | Value |
---------------------- ----------------- 
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
---------------------- ----------------- 
3 rows in set (0.04 sec)

Summarize

The above is all the content of this article about briefly analyzing the problem of MySQL character set causing database recovery errors. I hope it will be helpful to everyone. If you have any questions, please leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!

You may also be interested in:
  • How to modify the database encoding (database character set) and the character encoding of the table in MySQL
  • Sharing of MySQL character set and database engine modification methods
  • When DW is linked to the MySQL database, how to solve the problem of garbled characters in Chinese when creating a character set
  • How to unify the character set on an existing mysql database

<<:  Vue encapsulates a TodoList example and implements the application of browser local cache

>>:  Linux system to view CPU, machine model, memory and other information

Recommend

Detailed explanation of how to implement secondary cache with MySQL and Redis

Redis Introduction Redis is completely open sourc...

WeChat applet implements simple calculator function

This article shares the specific code for the WeC...

Notes on element's form components

Element form and code display For details, please...

Ten Experiences in Presenting Chinese Web Content

<br /> Focusing on the three aspects of text...

Some suggestions for HTML beginners and novices, experts can ignore them

Feelings: I am a backend developer. Sometimes when...

Detailed explanation of DIV+CSS naming rules can help achieve SEO optimization

1. CSS file naming conventions Suggestion: Use le...

Detailed explanation of MySQL basic operations (Part 2)

Preface This article contains 1. Several major co...

A brief introduction to React

Table of contents 1. CDN introduction 1.1 react (...

Improve the efficiency of page code modification and master the core knowledge of HTML language

When building a website, HTML language may seem un...

Introduction to the usage of props in Vue

Preface: In Vue, props can be used to connect ori...

MySQL Packet for query is too large problem and solution

Problem description: Error message: Caused by: co...

Understanding and application analysis of mysql pessimistic locking and optimistic locking

This article uses examples to describe MySQL pess...

JS quickly master ES6 class usage

1. How to construct? Let's review the common ...

A brief discussion on Linux signal mechanism

Table of contents 1. Signal List 1.1. Real-time s...

MySQL DeadLock troubleshooting full process record

【author】 Liu Bo: Senior Database Manager at Ctrip...