Bug #91714 mysqldump --skip-create-options fails on MySQL 8.0 server
Submitted: 19 Jul 2018 10:59 Modified: 11 Dec 2018 16:20
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0.11 OS:CentOS (6.6)
Assigned to: CPU Architecture:x86

[19 Jul 2018 10:59] Tsubasa Tanaka 
Description:
mysqldump with --skip-create-options fails by ER_WRONG_VALUE_FOR_VAR.

Because NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS sql_modes are removed in 8.0.

How to repeat:
# yum install -y https://dev.mysql.com/get/mysql80-community-release-el6-1.noarch.rpm
# yum install -y mysql-community-server
# mysqld --initialize-insecure --user=mysql
# /etc/init.d/mysqld start
# mysqldump --skip-create-options --all-databases
--
-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;

USE `mysql`;
mysqldump: Couldn't execute 'SET SQL_QUOTE_SHOW_CREATE=1 ,SQL_MODE=concat(@@sql_mode, _utf8mb4 ',NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS') ': Variable 'sql_mode' can't be set to the value of 'NO_KEY_OPTIONS' (1231)

Suggested fix:
Remove "SET sql_mode" from https://github.com/mysql/mysql-server/blob/mysql-8.0.11/client/mysqldump.cc#L2569-L2572
[19 Jul 2018 11:59] MySQL Verification Team 
Hello Tanaka-San,

Thank you for the report!

Thanks,
Umesh
[19 Jul 2018 12:06] MySQL Verification Team 
Bug #90791 marked as duplicate of this one
[26 Oct 2018 23:47] Peter VARGA 
Where is the problem that it is also not fixed in 8.0.13? The suggestion to fix the issue seems to be very simple.
[11 Dec 2018 16:20] Paul DuBois 
Fixed in 8.0.14.

mysqldump output could include SQL mode values that have been
removed.