Wednesday, February 28, 2018

The confusing strategy for MySQL shell

Where the hell is it?

The MySQL shell is a potentially useful tool that has been intentionally made difficult to use properly.

It was introduced, with much fanfare, with the MySQL Document Store, as THE tool to bridge the SQL and no-SQL worlds. The release was less than satisfactory, though: MySQL 5.7.12 introduced a new feature (the X-protocol plugin) bundled with the server. The maturity of the plugin was unclear, as it popped out of the unknown into a GA release, without any public testing. It was allegedly GA quality, although the quantity of bug reports that were filed soon after the release proved otherwise. The maturity of the shell was known as "development preview", and so we had a supposedly GA feature that could only be used with an alpha quality tool.

The situation with the MySQL shell got worse in a few months. A new product was brewing (MySQL Group Replication) and went rapidly from something released in the Labs without docs to being part of the regular server distribution, and it was evolving into a more complex and ambitious project (the InnoDB Cluster) which used the MySQL shell as its main tool.

Since the announcement of InnoDB Cluster, using the MySQL shell has been a nightmare. You saw examples in blog posts and presentations, and when you tried them at home, they did not work. There were different releases of MySQL shell with the same version number but different capabilities, depending on whether they were released through the main downloads site or through the labs.

When I asked why the shell wasn't distributed with the server, like the other tools, I was told that a non-GA product could not be released with a GA server. Considering that the Document Store is still walking around with a Pre-Production status legal notice, this was an odd excuse.

Still, I kept waiting, trying to figure out how to pair a given version of MySQL shell with a given version of the server. Unlike the server, there are no release notes for the shell, so every release was a surprising experience.

Eventually, the MySQL shell reached the GA state, with which merit I can't tell. Given the obstacles in the path to its usage, I doubt it has had any serious testing from the community. Despite the state being GA, it keeps being released separately, leaving the puzzled users with the ungrateful task of determining with which server version that shell could be used safely.

With the upcoming release of MySQL 8.0, a new version of MySQL shell appeared, with a colorful prompt and new features that the GA shell doesn't have. The public perception of the tool keeps getting more confused. In the presentations given by the MySQL team we see the new shell doing wonders, while the GA shell keeps its monochromatic features. Shall I use the 8.0.x shell with a 5.7 server or should I stick with the 1.0 version?

In MySQL 8.0, the situation is still divided. Both products (the server and the shell) are, as of today, not GA yet. It would make sense to finally end the craziness and put the two things together, so that users don't have to hunt around for the right shell version. But the two products are still released separately.

How can I do stuff with MySQL shell?

So far, we have only seen the availability of the shell. What about the functionality?

I have heard that Oracle wants to convert the shell into the only tool to deal with MySQL. I can't prove it, as Oracle doesn't release its development plans to the public, but I can see the emphasis on the shell in talks and articles authored by MySQL team engineers. If this is the plan, I think it needs a lot more work.

If you try to use MySQL shell the same way as the regular "mysql" client, you get in trouble soon.

mysqlsh --user root --password=msandbox --port=5721 --host
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating a Session to 'root@'
Your MySQL connection id is 38
Server version: 5.7.21 MySQL Community Server (GPL)
No default schema selected; type \use  to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, 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

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

I see two problems here:

  • The warning about the password on the command line is legitimate. The trouble is that there is no alternative. mysqlsh does not support --defaults-file, and there is no way of giving a password other than directly at invocation. There is an option "--passwords-from-stdin" which does not seem to work, and even if it did, I can't see the advantage of using the password from a pipe.
  • The default mode is Javascript. I can see that this makes operations simpler when you want to perform setup tasks for InnoDB Cluster, but certainly doesn't help me to use this tool as the primary drive for database management. There is a "--sql" option that does what I expect, but if this is not the default, I can't see this replacement being very successful.
  • Due to the previous items, using the tool in batch mode (with -e "SQL commands") is impossible, as every invocation will start with the freaking password warning.

I'm afraid that it's too late to take action for MySQL 8.0. The MySQL team is probably packaging the GA release while I write these notes. But I offer some suggestions nonetheless.

Wish list

  1. Package MySQL shell with the server. Past experience shows that the MySQL team keeps adding features into a GA release, thus exposing users to the risk of getting the wrong tool for the job. Having the shell and the server in the same tarball will help users pick the right version for the task. This is similar to what happens with mysqldump: using the tool from 5.5 with a 5.7+ server will not work properly. There is no reason for mysqlsh to be treated differently.
  2. Make sure that all the features of the mysql client work seamlessly in mysqlsh. Perhaps run the test suite replacing mysql with mysqlsh and pick up from there.
  3. Make the MySQL shell compatible with other tools. Specifically, it should support option files (--defaults-file, --defaults-extra-file, --defaults-group-suffix, --no-defaults)

In short, if the plan is to replace mysql with mysqlsh, put the thing in the open, and please make sure it can do what users can reasonably expect.


Anonymous said...

Does not support --login-path either, which could help in removing the password warning..

Todd Farmer said...

You make some great points, but I disagree with your assertion that the shell is made "intentionally" difficult to use. I'm quite sure the opposite is intended, but the gaps - and there are many, including those you reference here - expose how much effort goes into building a full-featured next-generation shell. That's surely why it's still labeled experimental and not packaged with GA product.

That you see value in the possibilities of MySQL shell is exactly what Oracle aimed for in releasing it. If there's enough demand for the cool features to be made available in the context of supported, integrated, enterprise-class releases, they'll make that investment. If the shell isn't compelling to users, why would they bother?

Right now, it's a fun experiment. Oracle needs to decide whether to invest in making a viable product, or to let it die on the vine. It's currently in limbo.

Giuseppe Maxia said...

Thanks for your remarks.
I stand by my point about intentionality.
It was intentional that a key component of InnoDB Cluster was distributed separately and with no direct link to which server version it is good for. The objection to the shell being not linked to the right server was raised on the very day the shell was disclosed to the world, and repeated until today.

I said it was intentional because MySQL had not one, but many chances of distributing the tool properly, i.e. in the package containing the server it is supposed to be used with, and has not done so, despite being asked several times (did you miss the part where we had two tools with the same version and different capabilities at the same time?)
For the same reason I can state that InnoDB Cluster is made unnecessarily difficult to install, despite the public promise that this would be an "integrated" product. It is integrated, except when you need to install it, given that you need to collect the components from three different packages (server, shell, router).

If you knowingly espose users to the risk of getting the wrong components (making them fail before they begin) I call this attitude *intentionally* weakening a product strategy. You may disagree. I am, in fact, just a voice from the community.

Philip Olson said...

One minor note: Shell release notes do exist:

Giuseppe Maxia said...

Thanks for the release notes pointer. I looked for them in the downloads page and the the shell user's guide. Those two are the obvious places where I would look for a changes log.
Now I see that they are visible in the documentation index.

Mike Zinner said...

Hi Giuseppe,

Thanks for looking at the MySQL Shell, we appreciate your efforts to help us making it better!

One quick word on the DocStore before I focus on the MySQL Shell. I think everybody will agree that releasing a "development preview" on top of a current GA was a bit of mixed messaging and caused confusion in the community. We could have done better there. On the other hand it allowed us to include the community very early, give them a way to try out the DocStore in direct comparison to their production deployments and therefore influence the completely new X DevAPI from ground up. Something that cannot be done in pre-GA versions.

The good news is that with MySQL 8 we will be able to set this straight, so we are eagerly looking forward to the MySQL 8 GA release.

Now let me switch to the MySQL Shell.

The MySQL Shell is not meant as a pure drop-in replacement for the mysql command line client. The mysql command line client will stay for good.

The MySQL Shell has a different focus, which is to become a strong DevOPs tool for MySQL and help to make MySQL fit well into DevOPs environments. This means that things will be a bit different compared to existing MySQL tools. But in order to lower the learning curve we try to support many things people are accustomed to, like the familiar command line parameter options.

Let me comment on some of the points you raised.

- --passwords-from-stdin is meant to allow mysqlsh to be spawned by another process that feeds it a password through pipes. We also have plans for login-path support. The problem with passing a password on the command line is that it will be logged and exposed in bash history. Same goes for storing plaintext passwords in files. Nobody should be doing that, it is a bad security risk and practice. If --passwords-from-stdin is not working for you, could I ask you to open a bug report and we will look into it.

- MySQL Shell 8.0.5 will have an option to switch to a different default than JavaScript. So you can default to SQL or Python.

- Since MySQL Shell is positioned as a DevOPs tool it is important to always have the latest version installed. MySQL Shell 8 will have full support for MySQL 5.7 as well. Even if you have MySQL 5.7 it is advised that you install the latest MySQL Shell 8. Let me give you the following example. MySQL Shell 8.0.4 introduced the Upgrade Checker utility. It will be updated with every new MySQL release to add new checks to ensure one can upgrade from MySQL Server 5.7 to MySQL Server 8. MySQL Server 5.7 does not know about the requirements for 8, but MySQL Shell 8 does. So it is straight forward to install the latest MySQL Shell on the machine that is running MySQL Server 5.7 to check if it is ready for the upgrade. More about this can be read here

- We would love to package the MySQL Shell with the MySQL Server. But at the moment this would mean to pull in a dependency on JavaScript V8 engine and Python on the MySQL Server package. We are working to find a way around this, so stay tuned.

Again, thanks for blogging about the MySQL Shell. And please keep testing the upcoming MySQL Shell 8.0.5 as well!


Giuseppe Maxia said...

Thanks for your detailed comments. I need to follow up with some of the topics that you've touched.

* I understand the point of not wanting to burden the server package with the dependency of Python and Javascript. However, given the emphasis given to group replication and InnoDB Cluster, it would be good to release a package that includes the appropriate shell to the server. It can be a different package, so we will have the regular server package, and the enhanced server package with all the things needed to run group replication.

* --passwords-from-stdin works only when I use the shell in batch mode. What I want is the ability to use the shell in interactive mode without typing the password every time. With the MySQL classic client, I use option files (--defaults-file=filename). This is what is missing. Is this going to happen?

* Using the latest shell with the latest server is easy. What is not easy is using the appropriate shell with an older version. As we proceed with more releases, there will be more versions of MySQL and more shells, and mixing them is not always recommended. Who wants to use older versions apart from me, who maintain a tool that makes the deployment of multiple versions easy? Support engineers, for example, who need to adapt to whichever version their customers are using.

* BTW, there is a topic that I haven't touched, as I just realized it now. MySQL shell 1.0.x includes a "generic Linux" tarball, while the 8.0.4 version doesn't have it. We have here a paradox. The usefulness of the tarball is that you can install it in a non-standard path for testing, without disturbing the same tool installed server-wide. But we have the GA version, the one that should be safe to install server wide, which offers a tarball, and the development release, the one that users should be suspicious about, that forces you to install server-wide.

Todd Farmer said...


Fair enough - I'm just one community voice, also! I try not to assume bad intention of others - I find I often get it wrong. You have great technical points, and I'm glad to see Oracle staff engaging constructively.

Mike Zinner said...

Hi Giuseppe,

Let me clarify one of the statements from my previous post, to make things more clear. This should also help to give you an answer on your followup question.

Of course - in the end the MySQL Shell is going to replace the command line client, but we are not there yet today. First, we have to reached parity with the mysql command line client on feature level, have to resolved some remaining issues and have the community preferring the MySQL Shell over the old client (which is starting even today).

Now that we have this clarified, let me comment on your points.

- Packaging: As said before, we would love to have MySQL Shell in the server package today. But we don't want to rush it and alienate some people by not doing this the right way. We want to address maintainability and packaging issues with the V8 engine, possibly replacing it, clean up some other stuff and make it fit into the MySQL Server package well. Starting to ship different variations is again a bit confusing for end users, when the actual burden should be on the MySQL development team to do their homework.

- --passwords-from-stdin alternative for interactive mode: I will increase the priority of --login-path support so you can start using that soon.

- Supporting older MySQL Server versions: The rule is simple, you always should use the latest MySQL Shell, regardless of the MySQL Server version. There is a MySQL Server 5.7 on the system? Update the MySQL Shell package to 8, it will also fully support the MySQL Server 5.7 version. Same is true once we go MySQL 9. In your case, when you maintain a tool that deals with multiple version, this makes you life even easier. Your tool only needs to include one version of MySQL Shell for all different MySQL Server version, always the latest version of MySQL Shell.

- generic Linux tarball - Will be back for MySQL Shell 8.0.5. We had to remove it before, because there was an issue with Python before which made the tarball not really generic.

I hope this helps!


Giuseppe Maxia said...

Hi Mike,
Thanks for expanding on your previous comments.
However, I still haven't seen an answer about the --defaults-file support. I don't care about the --login-path: it is designed to discourage its usage in an automated environment and for my sandboxes is no help at all. Let me explain: I can *use* --login-path for a non-standard deployment, but I can't *set* the login credentials without interactive input. Since I usually work with deployments that are created dynamically, this utility that wants to use my home directory and demands my attention at the keyboard would prevent me from running automated tasks.

Mike Zinner said...

Hi Giuseppe,

A quick update on this. I have been discussing the topic with Alfredo. While we cannot add direct support for storing plain text passwords in configuration files (like it is done in --defaults-file) due to Oracle security policies, Alfredo is looking at adding support for custom external password provider scripts/programs.

The basic idea is that the MySQL Shell would call that external password provider with the URI of where you want to connect to and the script would give it the password. The executable can then be something that fetches password from mac/linux keychain, secure vault from windows or anything you like, even something that reads passwords from my.cnf

This should give you the flexibility you need while at the same time allow you to use a secure store for the passwords.

If this sounds interesting, please file a feature request for the MySQL Shell and we will look into it.


Giuseppe Maxia said...

Hi Mike,
Thanks for the update.
While I appreciate your offer, and I would test the new feature if it is available, I don't think it is the right solution.
While the password is the most urgent matter, the usefulness of the options file is for more than passwords. The appealing of --defaults-file is that I can use the same source file for every tool in the MySQL set (mysqld_safe, mysqld, mysql, mysqldump, mysqlpump, mysqladmin). Your proposal would force users to implement an option file parser to use with mysqlsh with a different syntax.
If the only way of getting options from a file to mysqlsh is to use a different syntax I will adapt, but I will certainly not ask for it.
What I am asking is consistency and uniformity among MySQL tools. The security policy is not an argument I can buy easily. I note that the options files are supported for all the existing tools even in the next-to-be-GA MySQL 8, and then I can see that "mysqlpump", developed under Oracle watch, supports --defaults-file without apparent problems. Is the MySQL team following two standards?