mysql> command-line prompt is iconic, shown in countless documentation pages, forum posts, tutorials and manuals. It’s immediately identifiable – one look, and you immediately know the context in which commands are being executed. So it’s only with good reason that we would consider changing the prompt value to something else, and Daniël van Eeden provided a compelling suggestion to modify this to provide user, host and database context. Because the mysql prompt is user-configurable, this is easy to do dynamically:
mysql> prompt something> PROMPT set to 'something> ' something> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) something>
Using the special character sequences defined at the bottom of one of the documentation pages, the prompt can supply with a number of different and useful information which provides context in the command line:
something> prompt \u@\h [\d]> PROMPT set to '\u@\h [\d]> ' root@localhost [(none)]> prompt \h:\p [\d]> PROMPT set to '\h:\p [\d]> ' localhost:3310 [(none)]> prompt \D> PROMPT set to '\D> ' Mon Mar 23 17:09:58 2015>
The prompt can also be set as a command-line argument to the client, or via the configuration file read by the client at startup. Users who manage many servers and need information identifying to which instance a given command line client is connected might want to leverage the configuration file option, as this will affect all client instances started by default.
We actually changed the default prompt to “\u@\h [\d]> ” as Daniël proposed, but pulled it back out of 5.7 after using it internally for a few days and realizing a few issues. This post aims to document why we reverted to the original
mysql> prompt, the options we’ve considered as alternatives, and a call for feedback.
mysql> prompt length aligns with the prompt length for continuation prompts, where queries span multiple lines. This makes it easy to copy SQL commands from terminals:
mysql> SELECT 1, -> NOW();
When using prompts with differing length, copy operations pick up extraneous characters, requiring users to manipulate the contents to transform it back into executable SQL:
root@localhost [(none)] > SELECT 1, -> NOW();
An option would be to make the buffer the continuation prompt to the same length as the original prompt. With larger and dynamic prompts, the results might be unsatisfactory – the example above takes up 25 characters of my 80-character-wide terminal.
MySQL allows users to connect without specifying a default database, and that’s not going to change anytime soon. This results in “[(none)]” being displayed in the prompt. When I first encountered the prompt change, I had to ask what the “none” meant – I had no idea what that meant. Informed users will only need to be told once (or never, if they are smarter than me), but it’s bound to confuse less-experienced users.
Misleading user name
The MySQL ACL system makes displaying user names an interesting exercise. With wildcards, anonymous users and proxy accounts, there’s any number of possible values for “user name” to display. Do you want to know the user name you supplied to the client, the user name portion of the account used to authenticate, or the user name portion of the account associated with the privileges you have? They are different:
shell> bin\mysql.exe -ublah -P3310 Welcome to the MySQL monitor. Commands end with ; or \g. ... blah@localhost [(none)] > show grants; +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+ 1 row in set (0.00 sec)
Similarly for proxy users:
pa@localhost [(none)] > SELECT USER(), CURRENT_USER(), @@session.proxy_user; +--------------+----------------+----------------------+ | USER() | CURRENT_USER() | @@session.proxy_user | +--------------+----------------+----------------------+ | pa@localhost | pb@localhost | 'pa'@'localhost' | +--------------+----------------+----------------------+ 1 row in set (0.00 sec) pa@localhost [(none)] > show grants; +----------------------------------------+ | Grants for pb@localhost | +----------------------------------------+ | GRANT USAGE ON *.* TO 'pb'@'localhost' | +----------------------------------------+ 1 row in set (0.00 sec)
Like the [(none)] value, experienced MySQL users will understand the difference between USER(), CURRENT_USER() and @@session.proxy_user, but this can easily confuse users new to MySQL.
Server vs. client host
I’m used to seeing user@host define MySQL accounts, but that’s not what \u@\h actually provides. As noted above, the user is the user name supplied to the client, rather than the account. But the host is the server host, not the host of the account.
shell> ipconfig ... Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IPv4 Address. . . . . . . . . . . : 192.168.2.64 ... shell> bin\mysql -utodd -h192.168.2.54 Welcome to the MySQL monitor. Commands end with ; or \g. ... email@example.com [(none)] > select user(); +---------------+ | user() | +---------------+ | todd@GRAPHITE | +---------------+ 1 row in set (0.02 sec)
Note that using the \U option shows the full user name/host returned from USER():
firstname.lastname@example.org [(none)] > prompt \U > PROMPT set to '\U > ' todd@GRAPHITE >
It makes sense to display the host a client is connected to, rather than the host component of the account used to connect. But it does allow for confusion about what is being displayed.
Other common use cases
Daniël’s suggestion works great for DBAs who manage multiple MySQL installations and need to ensure they are executing commands against the intended instance. It’s less useful for single-host deployments, or for use cases like MySQL Sandbox, where multiple instances may all exist locally. I frequently have 5+ MySQL instances running locally for various testing purposes, and the proposed default prompt wouldn’t help me distinguish between them at all. Such use cases would be better served by a prompt value like “\h:\p > “:
root@localhost [(none)] > prompt \h:\p > PROMPT set to '\h:\p > ' localhost:3310 >
The need Daniël identified is something we want to address, but the issues listed above gave us second thoughts about changing the default prompt. We’ve identified some possible alternatives, which I’m listing below:
Add prompt help
The definition of special sequences isn’t easy to find in the documentation, and even some experienced MySQL users are unfamiliar with the options that exist. Those that do know the possibilities for customizing prompts sometimes find it difficult to quickly recall the special sequences they might need. We’d like to add this information to the client, so that it is readily available:
mysql> prompt ? *** PROMPT FORMAT OPTIONS *** \d - The default database \u - Your user name \h - The server host ...
Define prompt macros
We know that \u@\h [\d]> reflects the needs of a common use case, as does \h:\p >. Another common use case likely involves outputting the date/time in the prompt for tracking purposes. Another common use case might be to simply include the default database.
We can provide macros which are easily remembered and cycled through, so that users don’t have to build their own prompts for these common use cases. For example:
mysql> prompt \1 PROMPT set to '\u@\h using \d> ' root@localhost using (none)> prompt \2 PROMPT set to '\h:\p> ' localhost:3310> prompt \3 PROMPT set to '\D> ' Fri Mar 20 14:08:59 2015> prompt Returning to default PROMPT of mysql> mysql>
One possible solution is to leverage multi-line prompts, so that contextual information is provided on one line, while the familiar
mysql> prompt is retained:
root@localhost [(none)] > prompt \u@\h [\d]\nmysql> PROMPT set to '\u@\h [\d]\nmysql> ' root@localhost [(none)] mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) root@localhost [(none)] mysql>
This makes copying SQL easy, but still displays context information for a specific use case, and which may confuse less-experienced users.
Per-instance custom prompt definitions
As noted earlier, it’s easy enough to define a custom prompt dynamically, as a command-line option, or even globally within a configuration file. It could be useful if the custom prompts could be defined on a per-connection basis, and stored with other connection options in mysql_config_editor. Doing so would allow users to connect to a specific instance, and have a specific (non-default) prompt automatically associated.
Please let us know if you think the reasoning behind our decision to leave the prompt as-is is unsound in any way, or if you have particular affinity for any of the proposed alternatives. Thanks!
4 thoughts on “Command line prompt”
IMO, your reasoning about the default prompt is convincing.
OTOH, I fully agree about the need (for users with multiple instances) to confirm which instance they are connected to.
So: What about an “identify” command in the command-line client?
I imagine a command that reports both the client and the server host, ditto the versions, and probably also the account (user@host) whose privileges are used when executing commands.
The default prompt would remain as-is, and those users who would like to check the connection (and also get it logged) would call a single command.
Thanks for your work, and for communicating about it!
Thanks for the feedback! I had a very similar thought, although mine was more about customizing the output of the client STATUS (\s) command. I think that just about every important data point mentioned is found there:
There’s surely “extra” information in this output, and I had a hard time justifying in my own mind an additional client command just to skip the charset, protocol version, and server stats.
To me, there are three questions:
I haven’t reached any hard conclusions myself – feel free to share your thoughts on this.
Just a quick note: Daniël correctly pointed out to me that the ability to easily copy and paste SQL snippets only applies to block-oriented terminals (like those found on Windows). Line-oriented terminals (e.g., GNOME Terminal) have issues when copying multi-line statements, and the length of the leading prompt has no impact on that.
your reply suffers from lines being partially hidden, the reader must guess a bit.
Still, just three remarks:
1) The “status” output you quote does not contain host names (or IP addresses, if name resolution is troublesome) for the server or client host. (Ok, it shows this client is running locally, but that’s all.)
2) Yes, some of the info is not needed for the purpose we are discussing here. I’m not sure whether there should be a separate command or not – but if “status” continues not to report the hosts, then I desire something separate that does.
3) Very often, we humans tend to forget that other people may have different needs or preferences. We who read and write here are an exclusive minority: Even if we want to have identifying info, the majority of users probably doesn’t. Therefore IMO “status” (or whatever it is called) should remain a separate command.
4) About copy-paste: Yes, in a Unix/Linux terminal window the continuation prompt will also get copied, but still the vertical alignment is helpful for reading. And as soon as the copy is taken via an editor, rectangle source areas may be available.
That’s my very personal opinion.