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 keywords and reserved words in MySQL 5.7

Preface The keywords of MySQL and Oracle are not ...

How to solve the problem of -bash: /usr/bin/yum: No such file or directory after typing yum in linux

After entering yum in linux, the prompt: -bash: /...

The experience gained from comparing and analyzing the homepages of domestic social networking websites is shared with everyone (picture)

This article hopes to gain some insights through a...

Analyze the problem of Zabbix 5.0 disk automatic discovery and read-write monitoring

Automatically discover disks Configuration Key Va...

Friendly Alternatives to Find Tool in Linux

The find command is used to search for files in a...

Detailed explanation of the solution to Tomcat's 404 error

The 404 problem occurs in the Tomcat test. The pr...

Implementation of waterfall layout in uni-app project

GitHub address, you can star it if you like it Pl...

How to use Linux locate command

01. Command Overview The locate command is actual...

Summary of js execution context and scope

Table of contents Preface text 1. Concepts relate...

Flash embedded in web pages and IE, FF, Maxthon compatibility issues

After going through a lot of hardships, I searched...

mysql5.7.20 installation and configuration method graphic tutorial (mac)

MySQL 5.7.20 installation and configuration metho...

Tomcat uses thread pool to handle remote concurrent requests

By understanding how tomcat handles concurrent re...

Node.js+express message board function implementation example

Table of contents Message Board Required librarie...

A brief introduction to the simple use of CentOS7 firewall and open ports

Overview (official has more detailed description)...

Tutorial diagram of installing centos7.3 on vmware virtual machine

VMware Preparation CentOS preparation, here is Ce...