MySQL Bugs: #110549: mysql shell ignoring some CLI arguments passed to it on upgrade check utility

Bug #110549	mysql shell ignoring some CLI arguments passed to it on upgrade check utility
Submitted:	29 Mar 2023 12:12	Modified:	9 May 2023 11:02
Reporter:	Eduardo Ortega (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When trying to run the upgrade check utility via shell integration just like in the example at https://dev.mysql.com/doc/mysql-shell/8.0/en/command-line-integration-overview.html, it ignores the user and password passed to it and instead prompts for the password for user root:

```
root@some-host~# mysqlsh -- util check-for-server-upgrade --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
Please provide the password for 'root@/mnt%2Fvitess%2Fmysql%2Fdatadir%2Fmysql.sock':
```

Notice that this happens after having `sudo`ed. If run without sudo, it does work:

```
user@some-host:~% mysqlsh -- util check-for-server-upgrade --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
{
    "serverAddress": "localhost:3306",
...
```

However, we need to run with sudo because our my.cnf is not world readable. There seems to be nothing strange in the env of the root user after sudo that explains this:

```
root@somme-host:~# env|grep -v MYSQL_PWD
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=00:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.zst=01;31:*.tzst=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.wim=01;31:*.swm=01;31:*.dwm=01;31:*.esd=01;31:*.jpg=01;35:*.jpeg=01;35:*.mjpg=01;35:*.mjpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.m4a=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.oga=00;36:*.opus=00;36:*.spx=00;36:*.xspf=00;36:
LESSCLOSE=/usr/bin/lesspipe %s %s
LANG=C.UTF-8
SUDO_GID=3519
USERNAME=root
SUDO_COMMAND=/bin/bash
USER=root
PWD=/root
HOME=/root
LC_CTYPE=C.UTF-8
LC_TERMINAL=iTerm2
SUDO_USER=eduardo.ortega
LC_TERMINAL_VERSION=3.4.18
XDG_DATA_DIRS=/usr/local/share:/usr/share:/var/lib/snapd/desktop
SUDO_UID=3519
MAIL=/var/mail/root
SHELL=/bin/bash
TERM=xterm-256color
TMOUT=64800
SHLVL=1
LOGNAME=root
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
LESSOPEN=| /usr/bin/lesspipe %s
_=/usr/bin/env
```

How to repeat:
See description for steps.

Suggested fix:
Respect arguments passed in the CLI

Connection options must be passed before the --. Only options for the command being executed are allowed after the --.

So in the case that was included, the correct command is:

 mysqlsh --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   -- util check-for-server-upgrade  --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf

Hi, Alfredo:

Thanks for checking. In that case,  the documentation at https://dev.mysql.com/doc/mysql-shell/8.0/en/command-line-integration-overview.html#mysql-... would need to be updated, because it does show it after the `--`. Furthermore, it does not explain why it works when not sudo-ed and fails when sudo-ed.

In addition, using the command you provided actually results in an error:

```
some-user@some-host:~%  mysqlsh --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   -- util check-for-server-upgrade  --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
WARNING: Using a password on the command line interface can be insecure.
ERROR: Argument connectionData: Invalid connection options, expected either a URI or a Connection Options Dictionary
```

So something seems definitely odd with how to upgrade check utility gets and uses the user & password.

For extra context, I see the failure after using `sudo` in our Ubuntu 18.04 servers. I don't see that when using `sudo` on my local Mac laptop.

So, after a bit of extra investigation on the subject, there seem to be two issues here:

1. The one originally reported on this bug: under some circumstances, the check upgrade util ignores the user, host and password passed to it after the `--`. Notice that the documentation indicates that passing them after the `--` is valid, both in [1] and [2].
2. If the user, host and password are moved before the `--` and some additional parameters are passed to the upgrade check util (e.g.  output format JSON), the utility fails with error this ERROR: Argument connectionData: Invalid connection options, expected either a URI or a Connection Options Dictionary

[1]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html#mysql-shel...
[2]: https://dev.mysql.com/doc/mysql-shell/8.0/en/command-line-integration-overview.html

Hi Eduardo,

I'm verifying this as documentation for sure needs to be updated to better reflect this.

Thanks for the effort

Posted by developer:
 
Starting with 8.0.32, Shell reads standard MySQL option defaults files (i.e. my.cnf/my.ini) from standard locations. It's possible that when logged in as root, Shell finds such file and tries to create a global session using data from that file (hence the password prompt for an unrelated user).

This can be verified using --print-defaults command line option.

Hi:

That does not seem to be what is happening here:

```
root@some-host:~# mysqlsh  -- util check-for-server-upgrade --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
Please provide the password for 'root@/mnt%2Fvitess%2Fmysql%2Fdatadir%2Fmysql.sock':
Ctrl+C Cancelled

root@some-host:~# mysqlsh --print-defaults  -- util check-for-server-upgrade --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
mysqlsh would have been started with the following arguments:
--socket=/mnt/vitess/mysql/datadir/mysql.sock -- util check-for-server-upgrade --user=my_user --password=***** --host=localhost --port=3306 --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf
```

`--print-defaults` is not showing any additional user arguments being read from other option files.

Posted by developer:
 
Hi Eduardo

If you look at the output with print defaults, it is actually showing extra arguments.

You original call used the following arguments:

    mysqlsh -- util check-for-server-upgrade --user=my_user --password=$MYSQL_PWD --host=localhost --port=3306   --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf

While the output with print defaults indicates it is using:

    mysqlsh --socket=/mnt/vitess/mysql/datadir/mysql.sock -- util check-for-server-upgrade --user=my_user --password=***** --host=localhost --port=3306 --target-version=8.0.27 --output-format=JSON --config-path=/etc/mysql/my.cnf

This indicates that there's some options file defining a value for the "socket" option

On a case like this 2 sessions would be established:

- A global session using: --socket=/mnt/vitess/mysql/datadir/mysql.sock
- The session to be used in the upgrade checker using: --user=my_user --password=***** --host=localhost --port=3306

Since the password is not specified for the first session, the Shell prompts for it: expected behavior

The reason why the problem appears with sudo and not without it, would be because the options file is available in one case and not on the other

You can verify this by executing the command using --print-defaults in both cases.

As for the problem described by Pawel about the usage of Null for missing optional parameters, that is a bug we are already addressing.

A way to prevent the behavior of the unexpected prompt, is by using the --no-defaults shell argument, so no option files are read and the utility works only with the explicit options you are providing.

As for this report, I agree we should improve the documentation to warn about the connection data defined in option files.

Posted by developer:
 
Updated MySQL Shell 8.0.33 documentation with notes in the command syntax and utilities sections explaining the default behavior if option files are present and how to ignore them if necessary.
Thank you for the bug report.