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
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
2. Length of character.
We use the Golang function,
len(), to count the number of characters in a string such as for
A very interesting case that we missed was inserting string
VARCHAR(7) column type.
is 8, not 7. In Golang, the
len() function counts the number of bytes in a string unless the string is converted
rune type, then it counts the number of characters in a string.
3. Insert binary string to spatial types
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
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.