Dolt is a MySQL-compatible database that allows branching, diffing, and merging database changes. In this blog post, we will discuss how Dolt is being used with a network automation solution called Nautobot and the recent fixes we've had to make at Dolt to fully support Nautobot.

What is Nautobot?

First off, what is Nautobot? In NTC's own words:

Nautobot is a Network Source of Truth and Network Automation Platform built as a web application atop the Django Python framework.

Nautobot fully automates creating and maintaining network configurations. By integrating Nautobot with Dolt, you can improve your automation process further by having full version history, branches, and the ability to review changes before merging them to the "main" or "production" branch.

Benefits of Nautobot and Dolt Integration

1. Accuracy: Dolt provides Git-like version control for your data, allowing you to track changes, collaborate, peer review, and revert to previous versions with ease. Integrating Nautobot with Dolt ensures that your network data is always accurate and you always know why a particular change was made.

2. Collaboration: With Dolt, multiple team members can work on the same data independently. Each network engineer can have their own branch of the configuration, allowing small patch fixes to continue as a big network topology refactor is built and reviewed. This enables better collaboration across your organization and ensures that everyone is working with the most current network data.

3. Improved data integrity: Nautobot's validation and enforcement features, combined with Dolt's version control, ensure that your network data is accurate and consistent. If tests catch an error, simply roll back to the previous version and discuss the problematic changes with the engineer who authored them. This reduces the risk of production errors and promotes overall data integrity.

4. Historical view: Nautobot and Dolt integration allows you to manage your network data in a single, centralized location, complete with a log of every single change. This allows you to easily identify problematic network changes.

Integrating Nautobot with Dolt

Collaborating with Network To Code - Nautobot's maintainers - we added Dolt support to Nautobot by developing the Nautobot Version Control plugin. This plugin allows versioning of Nautobot data in Dolt, and provides a web interface for viewing and managing changes. The plugin allows human review of proposed changes to production data, use of automated CI/CD pipelines, simple rollbacks, ability to maintain multiple branches of changes, and more.

It has been over a year since we built and launched the Nautobot plugin. Both Dolt and Nautobot have changed a lot in the meantime. NTC started getting a lot of interest in the Version Control plugin and asked us to test the latest Nautobot with the latest Dolt. This is when we found more than a few issues in Dolt.

MySQL Compatibility Issues

The main cause of issues with running Nautobot on Dolt is the fact that Dolt is not (yet) 100% compatible with MySQL. This is something we are actively working towards, but it is a long and difficult process. Some of the issues we've encountered and fixed are described below.

Django and complex SQL

Nautobot is built atop Django, a Python ORM framework, and Django ends up writing some pretty complicated SQL queries which heavily use JOINS, nested queries, and CTEs. Some of these cases are so esoteric or specific, that we've never seen these errors in the wild. But these are queries that MySQL supports, so we have to support them too.

Here's a fun one, try and see if you can spot how this broke Dolt:

SELECT *
FROM `dcim_site` WHERE (NOT (EXISTS(
  SELECT (1) AS `a`
  FROM `dcim_powerpanel` U1
  WHERE (U1.`name` = 'test-power-panel1' AND U1.`site_id` = `dcim_site`.`id`)
  LIMIT 1)
) AND NOT (EXISTS(
  SELECT (1) AS `a`
  FROM `dcim_powerpanel` U1
  WHERE (U1.`name` = 'test-power-panel2' AND U1.`site_id` = `dcim_site`.`id`)
  LIMIT 1)
))
ORDER BY `dcim_site`.`_name` ASC

Here are some curious cases we've recently identified and fixed:

• This WHERE...IN bug ended up being an issue with OrderedDistinct dropping the parent row data during execution, which caused an index lookup to fail.
• This EXISTS subquery with a false condition bug happened because we were replacing the subquery with an empty table, but forgot to give the table the same schema as the subquery.
• Two different subquery bugs happened when we incorrectly tried hoisting subquery filters out of the subquery.
• The complex query up above is from this bug, and includes identical aliases in different subqueries, which causes issues when we hoist the internals of these subqueries higher up in the tree. We are using TableAliases to find and rename these aliases.

Large numbers

Dolt has not always been consistent about promoting data types when the data crosses one type's limits. This can easily happen with a recursive CTE like the one below, which calculates the Fibonacci sequence:

set @fib_limit=1000;
WITH RECURSIVE my_cte AS
(
  SELECT 1 as f, 1 as next_f
  UNION ALL
  SELECT next_f, f+next_f FROM my_cte WHERE f < @fib_limit
)
SELECT * FROM my_cte;

In this case, Dolt creates a schema for my_cte based on the starting values, 1 and 1. These types are tinyint, and have a maximum signed value of 127 and maximum unsigned value of 255. This is a problem when f and next_f are both greater than 127, which happens on the 12th iteration of the CTE. Dolt would then throw an error, because the value 144 is too large for a tinyint.

We fixed this issue with a small overhaul of how Dolt handles the promotion of numeric types.

JSON compatibility

This is a minor issue that in our case was impacting tests. We saw that some Nautobot tests were passing with MySQL, but failing with Dolt. These tests were storing data into a JSON field, then retrieving the JSON and comparing it to the initial version. It took some digging, but ultimately we were able to isolate the issue to how Dolt formats JSON data.

As outlined in the official MySQL documentation, MySQL also formats JSON data, but how it does this may be subject to change:

To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases. ... The order of two objects that are not equal is unspecified but deterministic.

We have updated Dolt to follow MySQL's current behavior, but you should be aware that depending on this behavior may be dangerous. We recommend not treating JSON as a string, but instead using the built-in JSON functions to compare and act on JSON data.

Memory usage

Nautobot Core comes with more than 6,000 tests, and Django runs each of these tests in a transaction. One random Monday, the tests started failing fairly quickly with an obscure error: the Docker service db was unreachable. When we looked into the container, we saw that the db container exited with error code 137. We were running out of RAM, but why?!

It took a bit of digging, but we eventually figured out that Dolt was re-adding child refs to an internal dictionary, even when we didn't have to. It turned out to be a simple fix, but it took a while to find the root cause.

Hosted Dolt

Hosted DoltDB is our solution for cloud Dolt databases. Part of our Nautobot work this time around included running Nautobot and the Nautobot Version Control plugin on Hosted Dolt.

This, it turns out, is very easy to do, and is something you can try out right now!

0. Clone our nautobot-plugin-version-control repo and checkout the pavel/update-dolt branch
   • This work is currently in review and will be available from Nautobot's plugin repo soon
1. Create a new database in Hosted Dolt
2. Wait for the instance to start
3. Take down the following information
   • Host (e.g. dolthub-ntc-demo.dbs.hosted.doltdb.com)
   • Username (e.g. v35enisi49i4btse)
   • Password (e.g. FFjQ4Au8fSn8r38YJ4kzSIdV9GknK5iF)
4. Download the hosted_ca.pem certificate file and save it to development/hosted_ca.pem
5. Copy development/creds_hosted.example.env to development/creds_hosted.env
6. Update development/creds_hosted.env with the information from step 3
   • DB_HOST should be the host from step 3
   • DOLT_USER should be the username from step 3
   • DOLT_PASSWORD should be the password from step 3
7. Run the following command to start a local Nautobot Docker container, which will use the Hosted Dolt database from step 3
   • invoke clean-start --use-hosted-dolt
8. The above command will take a while to run, as we are building Docker containers, migrating the database, and loading data into the database.
9. Navigate to http://localhost:8080 and login with the default credentials
   • Username: admin
   • Password: admin
10. You should now see a Nautobot instance running with the Nautobot Version Control plugin enabled and connected to Hosted Dolt!

Conclusion

Getting Nautobot's six thousand test cases running against Dolt has been a wild roller-coaster ride, and one that we are happy to have undertaken. The improvements we've made to Dolt definitely improved our codebase, and is part of the reason we are very excited to announce Dolt 1.0! We hope that this has been an educational read and that you are excited to try out Nautobot + Dolt for yourself!

Learn more

You can learn more about Nautobot + Dolt from one of our existing blog posts:

• Announcing Nautobot + Dolt
• Dolt + Nautobot: Version Control for Configuration

Nautobot has also made a series of informative Intro and Demo videos. You can find them here:

Version Control Intro Series:

• #1 - Defining the problem
• #2 - Version Control Comes to Nautobot
• #3 - Version Control App Top-Level Navigation
• #4 - Managing Pull Requests in the Version Control App

Version Control Demo Series:

• #1 - Setting Up Your Own Demo Environment
• #2 - A Version Control Enabled Workflow
• #3 - Adding a CI Pipeline to a Version Control Workflow
• #4 - Interacting With the Version Control App via APIs

Finally, here are the GitHub repos for both Nautobot and the Nautobot Version Control plugin:

• Nautobot
• Nautobot Version Control Plugin