Bug #69051 mysqldump: unknown option '--secure-auth'
Submitted: 24 Apr 2013 12:53 Modified: 3 Feb 2014 14:22
Reporter: Jørgen Thomsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.11 OS:Linux
Assigned to: CPU Architecture:Any
Tags: mysqldump secure-auth secure_auth

[24 Apr 2013 12:53] Jørgen Thomsen
Description:
mysqldump will not run if secure-auth is specified in the [client] section of my.cnf

How to repeat:
add secure-auth to the [client] section in my.cnf and execute mysqldmp

Suggested fix:
make mysqldump handle the secure-auth parameter
[25 Apr 2013 9:45] Umesh Shastry
Hello Jørgen,

Thank you for the report.
I can not repeat described behavior.

Could you please provide conf file, command that you are trying to run and error details?

####
[root@ushastry mysql-5.6.11-release]# bin/mysqldump -u root -p --port=3309 --protocol=TCP test --set-gtid-purged=OFF
Enter password: 
-- MySQL dump 10.13  Distrib 5.6.11, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.6.11-debug-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 */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` 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 2013-04-25 15:12:58

[root@ushastry mysql-5.6.11-release]# more my_slave.cnf 
[client]
secure_auth=ON

[mysqld]
core-file
log_bin
basedir = /home/ushastry/mybuilds/mysql-5.6.11-release
datadir = /tmp/5611_slave
port = 3309
server_id = 2
socket = /tmp/5611_slave/sock

enforce_gtid_consistency = ON
gtid_mode = ON
log_slave_updates
innodb_flush_log_at_timeout=27000
innodb_flush_log_at_trx_commit=2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
core_file_size = unlimited
[mysqldump]
secure_auth=ON
[25 Apr 2013 9:47] Umesh Shastry
"Before MySQL 5.6.5, this option is disabled by default. As of MySQL 5.6.5, it is enabled by default"..

Please see, http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_secure-auth
[25 Apr 2013 10:14] Jørgen Thomsen
/usr/local/mysql-5.6.11-linux-glibc2.5-x86_64/bin/mysqldump
mysqldump: unknown option '--secure-auth'
or
mysqldump: unknown variable 'secure-auth=on'
Depending on enabled option in my.cnf

[client]
#secure-auth=off
secure-auth
#secure-auth=on

[mysqldump]
quick
max_allowed_packet = 32M

 ldd /usr/local/mysql-5.6.11-linux-glibc2.5-x86_64/bin/mysqldump
        linux-vdso.so.1 =>  (0x00007fff6f1b0000)
        libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x0000003e38000000)
        librt.so.1 => /usr/lib64/librt.so.1 (0x0000003e38c00000)
        libdl.so.2 => /usr/lib64/libdl.so.2 (0x0000003e38400000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x0000003e3bc00000)
        libm.so.6 => /usr/lib64/libm.so.6 (0x0000003e38800000)
        libgcc_s.so.1 => /usr/lib64/libgcc_s.so.1 (0x0000003e39800000)
        libc.so.6 => /usr/lib64/libc.so.6 (0x0000003e37c00000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003e37800000)

/usr/local/mysql-5.6.11-linux-glibc2.5-x86_64/bin/mysqldump --help
mysqldump  Ver 10.13 Distrib 5.6.11, for linux-glibc2.5 (x86_64)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved
[25 Apr 2013 10:17] Jørgen Thomsen
And also

/usr/local/mysql-5.6.11-linux-glibc2.5-x86_64/bin/mysqldump
mysqldump: unknown variable 'secure-auth=off'
[25 Apr 2013 11:33] Umesh Shastry
Thank you for the feedback.
Verified as described .

Workaround: Pls remove secure_auth settings from configuration file as it is enabled by default http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_secure-auth

How to repeat:

Set secure-auth or secure_auth in client or mysqldump group in configuartion file and try to use mysqladmin or mysqldump

[client]
#secure-auth=off
secure-auth
#secure-auth=on

or

[client]
#secure-auth=off
secure_auth
#secure-auth=on

D:\ushastry\mysql-5.6.10-winx64\bin>mysqldump
mysqldump: unknown option '--secure-auth'

D:\ushastry\mysql-5.6.10-winx64\bin>
D:\ushastry\mysql-5.6.10-winx64\bin>
D:\ushastry\mysql-5.6.10-winx64\bin>mysqladmin.exe
mysqladmin.exe: unknown option '--secure-auth'

D:\ushastry\mysql-5.6.10-winx64\bin>

##

D:\ushastry\mysql-5.6.10-winx64\bin>mysqladmin.exe
mysqladmin.exe: unknown option '--secure_auth'

D:\ushastry\mysql-5.6.10-winx64\bin>mysqldump
mysqldump: unknown option '--secure_auth'
[25 Apr 2013 11:36] Umesh Shastry
## 5.6.11

Set secure-auth or secure_auth in client or mysqldump group in configuration file and try to use mysqladmin or mysqldump

[client]
#secure-auth=off
secure-auth
#secure-auth=on

or

[client]
#secure-auth=off
secure_auth
#secure-auth=on

D:\ushastry\mysql-5.6.11-winx64\bin>mysqldump
mysqldump: unknown option '--secure-auth'

D:\ushastry\mysql-5.6.11-winx64\bin>
D:\ushastry\mysql-5.6.11-winx64\bin>
D:\ushastry\mysql-5.6.11-winx64\bin>mysqladmin.exe
mysqladmin.exe: unknown option '--secure-auth'

D:\ushastry\mysql-5.6.11-winx64\bin>

##

D:\ushastry\mysql-5.6.11-winx64\bin>mysqladmin.exe
mysqladmin.exe: unknown option '--secure_auth'

D:\ushastry\mysql-5.6.11-winx64\bin>mysqldump
mysqldump: unknown option '--secure_auth'
[1 May 2013 14:09] Todd Farmer
Bug#69027 was closed as a duplicate of this bug report.  Please note that the impacts of this bug affect libmysql-based clients generally (including PHP, C/ODBC, etc.) - it is not just mysqldump-related.
[4 Sep 2013 11:11] Keith Hyland
Problem also exists in version 5.6.13
[9 Oct 2013 17:48] Leandro Morgado
This is also verified on 5.6.14 and not just for mysqldump. Other client utilities (mysqldump, mysql_upgrade, mysqladmin, possibly more) are affected:

shell> mysql -uancient -p123badpw 
Warning: Using a password on the command line interface can be insecure.
ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

shell> mysql -uancient -p123badpw --skip-secure-auth
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

shell> mysqlcheck -uancient -p123badpw --skip-secure-auth test
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqlcheck: unknown option '--skip-secure-auth'

shell> mysqlcheck -uancient -p123badpw  test
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

shell> mysqlcheck -uancient -p123badpw --default-auth=mysql_old_password  test
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

shell> mysql_upgrade -uancient -p123badpw --default-auth=mysql_old_password --skip-secure-auth --verbose
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysql_upgrade: unknown option '--skip-secure-auth'

shell> mysqladmin -uancient -p123badpw --default-auth=mysql_old_password --skip-secure-auth ping
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: unknown option '--skip-secure-auth'

shell> mysqladmin -uancient -p123badpw --default-auth=mysql_old_password ping
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)'

shell> mysqldump -uancient -p123badpw test
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

shell> mysqldump -uancient -p123badpw --skip-secure-auth test 
Warning: Using a password on the command line interface can be insecure.
mysqldump: unknown option '--skip-secure-auth'

shell> mysqldump -uancient -p123badpw --secure-auth='OFF' test 
Warning: Using a password on the command line interface can be insecure.
mysqldump: unknown variable 'secure-auth=OFF'

shell> mysqldump -uancient -p123badpw --default-auth=mysql_old_password test 
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect
[15 Nov 2013 13:42] Arkadiusz Miskiewicz
Shouldn't my.cnf be read by libmysqclient library users by default?

So my.cnf with:
[client]
secure_auth=OFF

would actually work for things like php and so on.
[3 Feb 2014 14:22] Paul Dubois
Noted in 5.6.17, 5.7.4 changelogs.

The mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport,
mysqlslap, and mysqlshow programs now support a --secure-auth option
that prevents sending passwords to the server in old (pre-4.1)
format. This option is enabled by default; use --skip-secure-auth to
disable it.
[29 Mar 2014 8:46] Laurynas Biveinis
5.6$ bzr log -r 5784
------------------------------------------------------------
revno: 5784
committer: Anirudh Mangipudi <anirudh.mangipudi@oracle.com>
branch nick: 5.6
timestamp: Fri 2014-01-31 12:11:29 +0530
message:
  Bug#16723046 MYSQLDUMP: UNKNOWN OPTION '--SECURE-AUTH'
  Problem:
  Since after 5.6.7 the secure-auth option was by default set to
  true from false in mysql clients. But the support for this option
  was added only in mysql client. Other client tools were missing this
  option and hence were resulting in the error, 
  Unknown option '--secure-auth', when trying to connect to the server
  Solution:
  The secure-auth parameter was added to the mysql client utilities which
  connect to the server i.e., Mysqldump, Mysqladmin, Mysqlcheck, Mysqlbinlog, 
  MysqlImport, Mysqlslap, Mysqlshow, and also mysql_client_fw which now prevents 
  the error.
[8 Dec 2014 21:00] Mark McKinstry
It looks like mysql_upgrade didn't get this fix so it still throws an error. 

Can it be fixed with this bug or should a new one be opened?