mysql error 1231_MySQL ERROR 1231 (42000):Variable 'character_set_client' can't be set to the value ...

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

I've a MySQL 5.0.84 running in a Slackware 13.0 Staging server and wanted to copy a single table to another server which was built to use Ubuntu 14.04 OS for some other testing. I've taken a mysqldump of that table and copied to the testing server. I get the following error when I try to restore the dump file.

ERROR 1231 (42000):Variable 'character_set_client' can't be set to the value of 'NULL'

Please help me how to fix this error. Thanks!

回答1:

I did some search in internet and fixed it finally.

Added the following text at the beginning of the mysqldump file and the restore was successful. /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @[email protected]@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

回答2:

I have modified /*!40101 SET character_set_client = @saved_cs_client */;

to /*!40101 SET character_set_client = 'utf8' */;

in the dump file after the code for creating table.

回答3:

Try to make the maximum allowed packet size arbitrarily high temporarily by typing in: mysql> set global max_allowed_packet=10000000000;

回答4:

on my way,open the .sql file,in the end ,do del: /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */; /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_COLLATION_CONNECTION */;

and source it again,then work well for me!

回答5:

Well, thanks to all the answers, It just helped me solving my issue. But copying and pasting codes from above didn't work for me. So, I exported sql file which was already in my database and from there I took the code and added the following to the beginning of my sql file to import -- phpMyAdmin SQL Dump -- version 4.7.0 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Aug 16, 2017 at 07:24 AM -- Server version: 10.1.25-MariaDB -- PHP Version: 7.1.7 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */;

Because this top section was missing in my sql document. Then the import was successful. I hope this helps someone else too.