Make Dolt work with mydumper

[timsehn]

https://github.com/mydumper/mydumper

A user is trying to load a database with myloader that he created with mydumper. It fails immediately in Dolt with:

** (myloader:736152): WARNING **: 22:23:15.511: Connection 5 - ERROR 1105: could not resolve initial root for database dbname/main

The database dbname is created but nothing else is imported.

[timsehn]

First bug:

$ mydumper -h dolthub-us-jails-6.dbs.hosted.doltdb.com -P 3306 -u <redacted> -p <redacted>

** (mydumper:31805): CRITICAL **: 12:17:51.572: Error executing SHOW SLAVE STATUS - ERROR 1105: syntax error at position 11 near 'SLAVE'

[timsehn]

SHOW SLAVE is deprecated and is being replaced by SHOW REPLICA.

[timsehn]

Made a bug in mydumper:

mydumper/mydumper#1680

But we may just have to support SHOW SLAVE STATUS.

[timsehn]

MyLoader fails with this error for a database I exported from MariaDB:

** (myloader:28536): WARNING **: 15:50:20.429: Set session failed: /*!40101 SET SESSION SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'  */

** (myloader:28536): CRITICAL **: 15:50:20.509: Connection 3 - ERROR 1105: value NO_AUTO_CREATE_USER was not found in the set
/*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/;

[jycor]

PRs supporting SHOW SLAVE STATUS:

• Add testing for show slave status #8600
• Add support for show slave status go-mysql-server#2767
• Add support for show slave status  vitess#381

[jycor]

PR supporting NO_AUTO_CREATE_USER sql_mode option:

• support NO_AUTO_CREATE_USER option in sql_mode go-mysql-server#2769

NO_AUTO_CREATE_USER sql_mode was deprecated bt MySQL, but enabled by default for MariaDB

[timsehn]

Now mydumper fails with

 11:12:55.158593 -0800 PST m=+3.148194917" connectionID=4 query="START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */"

[timsehn]

Now myloader fails on:

CREATE TABLE `help_topic` ( `help_topic_id` int(10) unsigned NOT NULL, `name` char(64) NOT NULL, `help_category_id` smallint(5) unsigned NOT NULL, `description` text NOT NULL, `example` text NOT NULL, `url` text NOT NULL, PRIMARY KEY (`help_topic_id`), UNIQUE KEY `name` (`name`) ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='help topics'"

Error is:

WARN[0069] error running query                           connectTime="2024-11-26 11:17:48.130184 -0800 PST m=+69.448642210" connectionDb=mysql connectionID=4 error="syntax error at position 375 near 'PAGE_CHECKSUM'" query="CREATE TABLE `help_topic` ( `help_topic_id` int(10) unsigned NOT NULL, `name` char(64) NOT NULL, `help_category_id` smallint(5) unsigned NOT NULL, `description` text NOT NULL, `example` text NOT NULL, `url` text NOT NULL, PRIMARY KEY (`help_topic_id`), UNIQUE KEY `name` (`name`) ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='help topics'"

I think we need to support PAGE_CHECKSUM and TRANSACTIONAL there.

[jycor]

We are missing MariaDB specific collations, namely utf8mb4_uca1400_ai_ci
MariaDB Docs

[CodeBleu]

Figured I'd link this here too 😄
dolthub/vitess#381 (comment)

mydumper/mydumper#1684

[jycor]

Conservatively bumping the reported MySQL version from 8.0.11 to 8.0.23 removed all errors relating to deprecated master/slave syntax: dolthub/go-mysql-server#2776

[jycor]

We are missing support for STOP REPLICA IO_THREAD and STOP REPLICA SQL_THREAD.

MySQL Docs

[jycor]

We need to match MySQL's EXPLAIN output:
dolthub/go-mysql-server#2784

[jycor]

Hey @CodeBleu, I believe we've reached a point where a Mydumper and Myloader actually works with dolt now!
At least a very basic dump and import seem to be working.

There are a few caveats and you may still see some errors, but it should work
More information in this blog:
https://www.dolthub.com/blog/2024-12-13-dolt-mydumper/

Please let us know how it works for you!