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!
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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 @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="206f6c647f6368617261637465727f7365747f636c69656e741d60">[email protected]</a>@CHARACTER_SET_CLIENT */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c18e8d859e8289809380829584939e9284959e938492948d9592fc81">[email protected]</a>@CHARACTER_SET_RESULTS */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="602f2c243f232f2c2c2134292f2e3f232f2e2e252334292f2e5d20">[email protected]</a>@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d09f9c948f84999d958f8a9f9e95ed90">[email protected]</a>@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="eaa5a6aeb5bfa4a3bbbfafb5a9a2afa9a1b9d7aa">[email protected]</a>@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1a55565e455c55485f535d5445515f434559525f595149275a">[email protected]</a>@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7b34373f24282a372436343f3e463b">[email protected]</a>@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="98d7d4dcc7cbc9d4c7d6d7ccddcba5d8">[email protected]</a>@SQL_NOTES, SQL_NOTES=0 */;
Method 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.
Method 3
Try to make the maximum allowed packet size arbitrarily high temporarily by typing in:
mysql> set global max_allowed_packet=10000000000;
/via http://injustfiveminutes.com/2013/02/14/errors-restoring-mysql-database-dump-on-wamp-environment/
Method 4
on my way,open the .sql file,in the end ,do del:
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="99dad1d8cbd8dacddccbc6cadccdc6dad5d0dcd7cda4d9d6d5dd">[email protected]</a>_CHARACTER_SET_CLIENT */; /*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c88b80899a898b9c8d9a979b8d9c979a8d9b9d849c9bf58887848c">[email protected]</a>_CHARACTER_SET_RESULTS */; /*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="084b474444495c414746574b4746464d4b5c414746354847444c">[email protected]</a>_COLLATION_CONNECTION */;
and source it again,then work well for me!
Method 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 @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a3ecefe7fce0ebe2f1e2e0f7e6f1fcf0e6f7fce0efeae6edf79ee3">[email protected]</a>@CHARACTER_SET_CLIENT */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="abe4e7eff4e8e3eaf9eae8ffeef9f4f8eefff4f9eef8fee7fff896eb">[email protected]</a>@CHARACTER_SET_RESULTS */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="135c5f574c505c5f5f52475a5c5d4c505c5d5d5650475a5c5d2e53">[email protected]</a>@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.
Method 6
I changed from
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3b78737a697a786f7e6964687e6f647877727e756f067b74777f">[email protected]</a>_CHARACTER_SET_CLIENT */;
to
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5b18131a091a180f1e0904081e0f041817121e150f661b">[email protected]</a>@CHARACTER_SET_CLIENT */;
it worked for me
Method 7
I worked for me by adding these 3 lines at the start of the SQL dump file.
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d09f9c948f9398918291938495828f8395848f939c99959e84ed90">[email protected]</a>@CHARACTER_SET_CLIENT */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bbf4f7ffe4f8f3fae9faf8effee9e4e8feefe4e9fee8eef7efe886fb">[email protected]</a>@CHARACTER_SET_RESULTS */; /*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3a75767e65797576767b6e73757465797574747f796e737574077a">[email protected]</a>@COLLATION_CONNECTION */;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0