We're looking to version control a limited database dump in a Git repo.

Developers may have a variety, albeit limited, of database servers installed on their workstations.

I'm discovering mysqldump --compact --compatible=ansi gets us pretty close to only showing only schema and data changes in diffs.

Unfortunately this conditional version execution comment diff on all tables remains an unsolved challenge:

diff --git a/a.sql b/b.sql
index c6cbdfc..8f225db 100644
--- a/a.sql
+++ b/b.sql
-/*!50503 SET character_set_client = utf8mb4 */;
+/*!40101 SET character_set_client = utf8 */;

It seems my MariaDB 10.4 mysqldump has "40101" statement hardcoded source as if (opt_compatible_mode & 3) to skip character set lines.

Based on git blame commit, this bitwise operation should evaluate mysql{323,40}? (MySQL 8 only supports ansi, so there's that limit, too.)

Q: Is a sed script to modify, or remove mismatching version statements my only ultimate solution here?

(Maybe a central server to normalize dumps could work, too?)

1 Answer 1


Developers may have a variety, albeit limited, of database servers installed on their workstations.

That's a red flag to me. All the developers on the team should use the exact same version of database software that they will eventually deploy to in production.

MariaDB and MySQL are not compatible; MariaDB started as a fork of MySQL in 2010, but by now both products have changed independently enough that we should stop thinking of them as "flavors" of the same database product. They are different products now.

You're trying to solve this with sed, when you should be solving it by making sure everyone on your team is running the same software.

This is a perfect case matching this quote from Jamie Zawinski in 1997:

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

At a past job, I solved this by developing a set of Ansible scripts to create a common development environment. The same scripts were used to create an AWS EC2 instance with the exact same software installed. Then I created a snapshot of the VM for developers to use on their workstations, and a snapshot of the EC2 instance from which I created an AMI to launch production cloud instances. When we upgraded anything, I then had a scripted method to create a new VM snapshot and a new AMI, so the developers could always work in a local environment that matched the production environment exactly.

  • Yes, I agree. "Maybe a central server to normalize dumps could work, too" idea seems like it could be the next achievable target. Team then has to align only half-way or thereabouts.
    – lkraav
    Jan 6, 2023 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.