Analysis of Error Reporting in Recovery Database Caused by mysql Character Set
- 2021-09-12 02:30:31
- OfStack
mysql character set encoding error of the import data will prompt error, this and insert data 1 sample, if the saved data and mysql encoding is not 1 sample, then there will definitely be import garbled code or insert data loss problem, let's look at 1 example below.
<
script
>
ec(2);
<
/script
>
Recovery database error: Due to character set problems, the original database default encoding is latin1, and the newly backed database encoding 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 measured):
[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 correlation:
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 Set 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 Set 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 setup methods:
Modify the character set of the database
mysql>use mydb
mysql>alter database mydb character set utf-8;
Create a database the character set of the specified database
mysql>create database mydb character set utf-8;
Modify through configuration file:
Revision/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 from 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)
View:
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 the whole content of this article about the brief analysis of mysql character set, which leads to the error report of recovery database, hoping to be helpful to everyone. If you have any questions, you can leave a message at any time, and this site will reply to you in time. Thank you friends for your support to this site!