View connection status in the MySQL command-line prompt
While working on something unrelated, I came across the server status
flags that are exposed to client connections by the client library. I
have never used those flags myself, but as I understand they are meant
to provide some context to applications and proxies which would
otherwise have to do some fairly expensive things to obtain that
information.
What stroke me as odd is that there is no way to see those flags
from interactive sessions in the MySQL command-line client. Like
applications or proxies, human beings need context, especially when
multi-tasking and switching between multiple tabs or windows. That's
precisely the reason why the command prompt in the mysql client can be
customized via the prompt command: provide some context about the
current connection to the user. A custom prompt may include special
character sequences that will be replaced with current user name, server
host, server version and so on.
Adding a new special sequence to expose connection flags would be a
trivial patch and looked like a fun task for the weekend. The resulting
patch uses \x which is replaced in the prompt with a space-separated
list of strings corresponding to current connection's flags. The idea for
implementation was also inspired by the %x flag in the PostgreSQL
command-line client, which also provides some simple info about the
current transaction status in a single-character form (* when the
user is inside a multi-statement transaction block, ! on error, or empty
when not in a transaction).
MySQL client API provides a bit more information about connection
status, so encoding it as some 'magical' characters would be too
cryptic and counter-intuitive. Below is a list of API-defined flags
that I think might be relevant to interactive sessions and their
corresponding indicators used by \x:
SERVER_STATUS_IN_TRANS |
in_trans |
SERVER_STATUS_AUTOCOMMIT |
autocommit |
SERVER_QUERY_NO_GOOD_INDEX_USED |
no_good_index_used |
SERVER_QUERY_NO_INDEX_USED |
no_index_used |
SERVER_STATUS_DB_DROPPED |
db_dropped |
SERVER_STATUS_NO_BACKSLASH_ESCAPES |
no_backlash_escapes |
SERVER_QUERY_WAS_SLOW |
query_was_slow |
SERVER_STATUS_IN_TRANS_READONLY |
readonly |
Few examples to explain what it is. I'm changing the prompt to display current user, database and connection status:
$ mysql -uroot test
...
mysql> prompt \U:\d [\x]>
PROMPT set to '\U:\d [\x]> '
root@localhost:test [autocommit]>
So we can see we are in the AUTOCOMMIT mode even without checking the
corresponding system variable. Let's run some transactions. Note the
in_trans indicator appearing in the prompt when the connection is
inside a multi-statement transaction:
root@localhost:test [autocommit]> create table t(a int);
Query OK, 0 rows affected (0.04 sec)
root@localhost:test [autocommit]> insert into t values (1), (2), (3);
Records: 3 Duplicates: 0 Warnings: 0
root@localhost:test [autocommit]> begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost:test [in_trans autocommit]> insert into t values (4);
Query OK, 1 row affected (0.00 sec)
root@localhost:test [in_trans autocommit]> commit;
Query OK, 0 rows affected (0.01 sec)
root@localhost:test [autocommit]> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
root@localhost:test [state_changed]> insert into t values(5);
Query OK, 1 row affected (0.00 sec)
root@localhost:test [in_trans]> commit;
Query OK, 0 rows affected (0.00 sec)
root@localhost:test []>
I guess it's all self-explanatory. A little more interesting example:
checking if SELECT query was using an index right in the command
prompt without consulting (or even enabling) the slow query log or
PERFORMANCE_SCHEMA:
root@localhost:test []> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
root@localhost:test [autocommit state_changed]> select * from t limit 1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
root@localhost:test [autocommit no_index_used]> alter table t add index (a);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:test [autocommit]> select * from t limit 1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
root@localhost:test [autocommit]>
The no_good_index_used flag is a little weird, I'm not sure why it
exists as a separate flag. It is set by the server only in a single
case: if the query execution plans contain JOIN(s) without keys that
check for key usage after each row, which corresponds to Range checked
for each record in EXPLAIN output):
root@localhost:test [autocommit]> select * from t t1, t t2 where t1.a > t2.a limit 1;
+------+------+
| a | a |
+------+------+
| 2 | 1 |
+------+------+
1 row in set (0.00 sec)
root@localhost:test [autocommit no_good_index_used no_index_used]>
I don't spend much time in the MySQL command line, but I guess some
people may find this useful, so I'm going to submit the patch
upstream. The only problem is that the way the mysql client is
implemented makes it impossible to cover prompt with regression tests
(and there are currently none in the test suite).
I also looked into adding similar functionality to MySQL Shell, but its prompt customization is a little more involved (JSON themes, seriously?!). So it's probably a fun task for another weekend 🙂.
