MySQL Bugs: #90807: mysqldump omits data charset

Bug #90807	mysqldump omits data charset
Submitted:	9 May 2018 12:42	Modified:	9 May 2018 14:12
Reporter:	Emember US	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.6.39-cll-lve	OS:	Windows
Assigned to:		CPU Architecture:	Other (64-bit)
Tags:	mysqldump charset

Description:
Command-line tool mysqldump omits to specify non-default table data charset.
This leads to portability issues between databases with different 
default charset configurations.

How to repeat:
load script:

DROP TABLE IF EXISTS `test_charset`;
CREATE TABLE `test_charset` (
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `test_charset` WRITE;
SET NAMES utf8;
INSERT INTO `test_charset` VALUES ('АБВГД');
UNLOCK TABLES;

mysqldump result:

DROP TABLE IF EXISTS `test_charset`;
CREATE TABLE `test_charset` (
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `test_charset` WRITE;
INSERT INTO `test_charset` VALUES ('АБВГД');
UNLOCK TABLES;

NOTE:
"SET NAMES utf8" statement specifying non-default table charset is missing
from result.

Suggested fix:
add missing statement

Thank you for the bug report. What is missing here:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41 Source distribution 2018-MAY-08

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > CREATE DATABASE bugdump;
Query OK, 1 row affected (0,00 sec)

mysql 5.6 > use bugdump
Database changed
mysql 5.6 >
mysql 5.6 > DROP TABLE IF EXISTS `test_charset`;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql 5.6 > CREATE TABLE `test_charset` (
    ->   `name` varchar(255) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `test_charset` WRITE;
SET NAMES utf8;
INSERT INTO `test_charset` VALUES ('АБВГД');
UNLOCK TABLES;Query OK, 0 rows affected (0,22 sec)

mysql 5.6 > LOCK TABLES `test_charset` WRITE;
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > SET NAMES utf8;
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > INSERT INTO `test_charset` VALUES ('АБВГД');
Query OK, 1 row affected (0,03 sec)

mysql 5.6 > UNLOCK TABLES;
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > exit
Bye
miguel@tikal:~/dbs $ 5.6/bin/mysqldump --socket=/tmp/mysql56.sock -uroot -p  bugdump
Enter password:
-- MySQL dump 10.13  Distrib 5.6.41, for Linux (x86_64)
--
-- Host: localhost    Database: bugdump
-- ------------------------------------------------------
-- Server version       5.6.41

/*!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 */;

--
-- Table structure for table `test_charset`
--

DROP TABLE IF EXISTS `test_charset`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_charset` (
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_charset`
--

LOCK TABLES `test_charset` WRITE;
/*!40000 ALTER TABLE `test_charset` DISABLE KEYS */;
INSERT INTO `test_charset` VALUES ('АБВГД');
/*!40000 ALTER TABLE `test_charset` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-05-09 10:23:38

Your test reproduces the bug.
What was your question then?

I reported:

NOTE:
"SET NAMES utf8" statement specifying non-default table charset is missing
from result.

Thank you for the feedback.

SET NAMES utf8 is in the beginning of the dump:

-- MySQL dump 10.13  Distrib 5.6.41, for Linux (x86_64)
--
-- Host: localhost    Database: bugdump
-- ------------------------------------------------------
-- Server version       5.6.41

/*!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 */;