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 🙂.