Need version control on your existing database? Dolt is a SQL database that can replace your current database but gives you version control features just like Git. The easiest way to migrate a MySQL database is to dump and import to Dolt. We always supported importing using SQL but some of the syntax a standard mysqldump generates did not work in Dolt. We went on a quest to improve this experience, and I'm happy to report importing MySQL dumps works much better now. This blog will explain how we did it.

MySQL dump imports is part of Dolt's first hour experience project that we started a couple of months ago.

Finding and Resolving Issues

It was pretty obvious when we ran our first moderately complex mysqldump and tried importing it to Dolt that we had a lot of work to do. It was almost embarrassing. Why had we not tried importing a mysqldump earlier? We were more focused on the OLTP SQL paths, not the import paths.

We filled a MySQL database with different data types, keys, indexes, constraints, triggers and stored procedures. Then, we run mysqldump and imported the result into Dolt.

Import a dump file using

$ mysqldump my_db --result-file=my_dump_file.sql
$ dolt sql < my_dump_file.sql

Any issue during the import will result in a failed import and no data will be imported. It was very simple to identify then resolve the issues as we ran this command over and over. Once we have a successful import, we validate the data with tests to check its accuracy. A quick way to check if all of your tables, triggers and stored procedures are imported is to list them using SHOW statements. We do not support stored functions yet.

About 60% of the issues we found were related to unsupported features in Dolt. Here are some of the issues we found, and some notes and limitations for each:

1. Character set introducer.

MySQL adds database specific character sets such as _utf8mb4 in front of string literals used in functions. We added parsing support, but we currently only support the _utf8mb4 character set. Default character set can be specified when running mysqldump by using --default-character-set=utf8mb4.

2. Length of character.

We use the Golang function, len(), to count the number of characters in a string such as for VARCHAR type. A very interesting case that we missed was inserting string Macorís into VARCHAR(7) column type.len("Macorís") is 8, not 7. In Golang, the len() function counts the number of bytes in a string unless the string is converted to []rune type, then it counts the number of characters in a string.

3. Insert binary string to spatial types

By default, mysqldump dumps spatial type values into a binary string. This is an edge case that we missed, and we now support handling of binary strings as well as hexadecimal strings for insertion in spatial type columns.

4. Triggers have reference to a non-existent table

Our workflow for running a query is parse, analyze, then execute. We handle any table or column references by making sure they exist in the analyzer step. This process uncovered some exceptions needed in our workflow. MySQL allows users to create a trigger referencing a non-existent table inside 'trigger_body', but a table that a trigger is on must exist. MySQL returns an error when it is triggered in runtime because of the non-existent table it references. On the other hand, Dolt previously did not allow referencing a non-existent table anywhere in the query as the analyzer resolves every table reference at once. We made CREATE TRIGGER an exception to this rule to mimic MySQL's behavior.

5. SRID for spatial type columns

We are slowly but surely adding more support for spatial data types. SRID is used for column definition for spatial types in CREATE TABLE statements. We now support Cartesian (SRID 0), which represents x and y coordinates on flat surface and Geospatial (SRID 4326), which represents latitude and longitude coordinates on the Earth's surface.

Play around with your database on Dolt

We keep track of supported and unsupported features on our documentation page for SQL Language Support or you can search specific issues on GitHub issues. We recommend checking them out for supported and unsupported features that your database might need. Try MySQL imports on Dolt with your database! If you find any failed cases during MySQL dump imports, you can file a new issue or reach out to us on Discord.