MySQL Bugs: #112115: mysqlsh displays incorrect connection details

Bug #112115	mysqlsh displays incorrect connection details
Submitted:	18 Aug 2023 17:01	Modified:	9 Apr 21:27
Reporter:	Matthew Boehm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S2 (Serious)
Version:	8.0.33, 8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The mysqlsh displays inaccurate details about the connection. Please note that there is a MySQL server running on db06-b and db06-a. In both examples shown below, the connection is TCP to db06-a

[root@db06-b ~]# mysqlsh -h db06-a
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'foouser@/vd1%2Fmysql%2Fdata%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 1369
Server version: 5.7.42-46-log Percona Server (GPL), Release 46, Revision e1995a8bb71
No default schema selected; type \use <schema> to set one.
MySQL db06-a:3306 ssl JS >

Note the incorrect line "Creating a Classic session to ...mysql.sock' This is an incorrect statement. Mysqlsh is connected via TCP to db06-a, NOT to localhost 06-b.

More evidence:

MySQL db06-a:3306 ssl JS > util.checkForServerUpgrade()
The MySQL server at /vd1%2Fmysql%2Fdata%2Fmysql.sock, version 5.7.42-46-log -
Percona Server (GPL), Release 46, Revision e1995a8bb71, will now be checked for
compatibility issues for upgrade to MySQL 8.0.33...

Once again, note that the prompt correctly shows db06-a:3306 yet the very next line says connected via localhost socket.

How to repeat:
Use mysqlsh to connect to a remote mysql when a mysql also exists on the same machine. The mysqlsh client outputs incorrect details about the connection. Very confusing.

Suggested fix:
Don't display incorrect information.

Hello Matthew,

Thank you for the report and feedback.
I tried to reproduce the this at my end but not seeing any issues and it works as expected i.e. no discrepancies noticed such as using socket etc for remote connections.

-- support-cluster03
5.7.42 instance is running, system user with all the privileges

systemuser 21921  0.0  0.0 114896  3236 pts/0    S    12:03   0:00 /bin/sh bin/mysqld_safe --no-defaults --basedir=/export/home/tmp/ushastry/mysql-5.7.42 --datadir=/export/home/tmp/ushastry/mysql-5.7.42/112115 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=/export/home/tmp/ushastry/mysql-5.7.42/112115/log.err --log-error-verbosity=3 --secure-file-priv= --local-infile=1
systemuser 22117  0.0  0.1 1374424 216224 pts/0  Sl   12:03   0:01 /export/home/tmp/ushastry/mysql-5.7.42/bin/mysqld --no-defaults --basedir=/export/home/tmp/ushastry/mysql-5.7.42 --datadir=/export/home/tmp/ushastry/mysql-5.7.42/112115 --plugin-dir=/export/home/tmp/ushastry/mysql-5.7.42/lib/plugin --core-file --log-error-verbosity=3 --secure-file-priv= --local-infile=1 --log-error=/export/home/tmp/ushastry/mysql-5.7.42/112115/log.err --pid-file=support-cluster03.pid --socket=/tmp/mysql.sock --port=3306

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> create user 'systemuser';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'systemuser';
Query OK, 0 rows affected (0.00 sec)

-- support-cluster04
5.7.42 instance is running

systemuser  4564  0.0  0.0 114896  3152 pts/3    S    12:28   0:00 /bin/sh bin/mysqld_safe --no-defaults --basedir=/export/home/tmp/ushastry/binaries/mysql-5.7.42 --datadir=/export/home/tmp/ushastry/binaries/mysql-5.7.42/112115 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=/export/home/tmp/ushastry/binaries/mysql-5.7.42/112115/log.err --log-error-verbosity=3 --secure-file-priv= --local-infile=1
systemuser  4837  0.0  0.1 1236472 192000 pts/3  Sl   12:28   0:00 /export/home/tmp/ushastry/binaries/mysql-5.7.42/bin/mysqld --no-defaults --basedir=/export/home/tmp/ushastry/binaries/mysql-5.7.42 --datadir=/export/home/tmp/ushastry/binaries/mysql-5.7.42/112115 --plugin-dir=/export/home/tmp/ushastry/binaries/mysql-5.7.42/lib/plugin --core-file --log-error-verbosity=3 --secure-file-priv= --local-infile=1 --log-error=/export/home/tmp/ushastry/binaries/mysql-5.7.42/112115/log.err --pid-file=support-cluster04.pid --socket=/tmp/mysql.sock --port=3306

-- connect to remote 5.7.42 instance running on support-cluster03

bin/mysqlsh --mysql -h support-cluster03
Please provide the password for 'umshastr@support-cluster03':
Save password for 'umshastr@support-cluster03'? [Y]es/[N]o/Ne[v]er (default No):
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'umshastr@support-cluster03'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 5
Server version: 5.7.42 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  support-cluster03:3306 ssl

^^ As you can see, it is using tcp instead of socket to connect remote instance.

Is there anything I'm missing here? Please let me know.
Also, kindly share any configurations used unintentionally by the mysql shell ( from optional file under client or mysqlsj group). 
Any chance you can run mysql shell in debug mode and share the log as a private note to investigate this issue? Thank you.

regards,
Umesh

Test

Hello Umesh.

Continuing to use my hostnames, mysqlsh is installed on 06-b. Inside /etc/my.cnf, I have the following:

[client]
socket=/vd1/mysql/data/mysql.sock

Inside /root/.my.cnf:

[client]
username=percona
password=foobar1234##

I then execute a remote TCP connection to db06-a:

[root@db06-b ~]# mysqlsh --mysql -h db06-a
MySQL Shell 8.0.33

(sorry. I was getting Error 500 when trying to post all of this at once)

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'percona@/vd1%2Fmysql%2Fdata%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 6970
Server version: 5.7.42-46-log Percona Server (GPL), Release 46, Revision e1995a8bb71
No default schema selected; type \use <schema> to set one.

MySQL  db06-a:3306 ssl  JS > 
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  db06-a:3306 ssl  SQL >
SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| db06-a     |
+------------+
1 row in set (0.0002 sec)

MySQL  db06-a:3306 ssl  JS>
MySQL Shell version 8.0.33

Connection Id:                6973
Current schema:
Current user:                 percona@192.168.155.77
SSL:                          Cipher in use: ...
Using delimiter:              ;

Notice that shell says "Creating classic session to percona@<socket>" but it is not really doing that. It has indeed created a TCP connection do db06-a as evidenced by the prompt, status lines, and by the SELECT @@hostname SQL.

(Side note: Thank you for implementing ctrl-d and .my.cnf support. If we can get sql history enabled by default, then I think I can finally replace the old client with mysqlsh)

(so sorry for this noise. I keep getting 'Access Denied' You don't have permission to access "http://splash.oracle.com/bug.php?" on this server when I try to post all of it. I must be hitting some sort of injection attack detector)

Thank you for the details, let me give it a try and come back to you if anything further required.

Sincerely,
Umesh

Hello Matthew,

Thank you for the details, and sorry for the delay in getting back to you on this. I created ~/my.cnf with the client section you provided and attempted to connect from host A to host B:

 bin/mysqlsh --mysql -h support-cluster03
MySQL Shell 8.0.34

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'percona@/tmp%2Fmysql.sock'
MySQL Error 1045 (28000): Access denied for user 'percona'@'HostA' (using password: YES)

^^ agree, access denied but "Creating a Classic session to 'percona@/tmp%2Fmysql.sock'" <-- was confusing as it should use tcp based rather than socket.

Verifying for now.

regards,
Umesh

Posted by developer:
 
Added the following note to the MySQL 8.4.0 release notes:
  
  MySQL Shell returned a message regarding a socket-specific connection to the localhost 
  although the connection was TCP and to a remote host. This occurred if a socket path was 
  specified either in the configuration file or on the command line.

As of this release, the transport to use is determined by the right-most parameter on the command line.