Dolphie Works with Dolt

REFERENCE
2 min read

Dolt is a drop in-replacement for MySQL, which means it can be used with any MySQL-compatible tool. One such tool is Dolphie, a real-time analytics tool for MySQL. Dolphie is an open source tool with a UI that works entirely in the terminal, and is mostly written in Python.

In this guide, we'll show you how to use Dolphie with a running Dolt server.

dolt_dolphie.png

Setup

You can install Dolphie through Python PIP like so:

$ pip install dolphie

Alternatively, there are various installation methods listed on their README.

In one terminal, start a Dolt server:

$ dolt sql-server --user=root

In another terminal, start Dolphie:

$ dolphie --user=root

Note: it is important to specify the --user=root flag for both of these

You should see something like this: dolphie1.png

In another terminal, connect to the Dolt server using your favorite MySQL compatible client. In this example, we'll be using the built-in dolt sql shell.

$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit. "\help" for help.
tmp2/main> select sleep (10);

You should see the following in the Dolphie terminal: dolphie2.png

Challenges

Initially, Dolt and Dolphie did not work well together. We discovered several bugs on both sides that needed to be fixed.

Dolphie Fixes

I primary develop on Windows, and right away, Dolphie did not work on Windows. Fortunately, Charles Thompson, a maintainer of Dolphie, quickly fixed this issue.

Additionally, Thompson also resolved an issue where a missing performance_schema database would cause Dolphie to crash, and another issue where there is a type mismatch between a Dict value and the SQL_Delay variable.

Dolt Fixes

Meanwhile, there were issues on the Dolt side as well. That we needed to fix.

Firstly, we were missing support for SHOW SLAVE STATUS, which is a deprecated MySQL command; it is not updated to SHOW REPLICA STATUS. Fortunately, it was mostly just renames so Jason was able to resolve the issue quickly.

Next, we were missing the system variable: innodb_buffer_pool_size. The fix was super straightforward.

After that we were getting an error when querying the information_schema.processlist.state column with LIKE query. It turns out the data in the column was way larger than the column size, so just increased the column size.

Finally, we had to deal with a silly MySQL quirk, where certain columns from the information_schema preserve their original column name casing, while others do not.

Most tables in MySQL when queried will match the casing specified by the query.

For example:

mysql> select ABC from t;
+-----+
| ABC |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

mysql> select abc from t;
+-----+
| abc |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

However, for what ever reason, the information_schema tables do not follow this rule.

tmp2/main*> select schema_name from information_schema.schemata;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| tmp2               |
+--------------------+
2 rows in set (0.00 sec)

We actually account for this inconsistency in Dolt.

Unfortunately, we missed an inconsistency in this inconsistency; the information_schema.processlist table, despite being in the information_schema database, does not follow this rule.

mysql> select id from information_schema.processlist limit 1;
+----+
| id |
+----+
| 2  |
+----+
1 row in set (0.00 sec)

This was causing problems in Dolphie, because it was looking for the column ID in the information_schema.processlist table, but the column was actually named id. So we had to address this issue in Dolt, through this PR and this PR.

Conclusion

We hope this guide helps you get started with Dolphie and Dolt. We are dedicated to making Dolt a full on drop-in replacement for MySQL, and happy to improve compatibility with any MySQL-compatible tool. If you have any questions or need help, feel free to reach out to us on Discord.

SHARE

JOIN THE DATA EVOLUTION

Get started with Dolt

Or join our mailing list to get product updates.