‍
‍
‍

QR Code contains TinyURL of this article.MySQL_Diff:
Database Schema Difference Reconciliation

‍
MySQL_Diff

Problem

Keeping database schemata synchronised is difficult. When a database schema doesn’t match the code that references it the outcome can be unpredictable and can even result in data corruption.1

Any of the following might result in schemata going out of sync:

  • Multiple developers have made changes to a schema;
  • A developer has changed multiple schemata independently;
  • The development cycle is of a long enough period to allow a developer to forget about her changes;
  • A developer has left the team, mid-project, without documentation or handover of her schema revisions.

Solution

I have written a small PHP script, MySQL_Diff, to assist in MySQL schema difference resolution.

Features

  • Fast schemata comparison;
  • Runs from the browser or command-line;
  • Visual difference tool;
  • Assists with Continuous Integration;
  • Unlimited, independent configuration files;
  • Object Oriented design.

Quick Start

  • Download MySQL_Diff;
  • Unzip the archive;
  • Copy/transfer the resulting MySQL_Diff-master folder to your web space;
  • Edit MySQL_Diff-master/config/example.ini with your configuration details;
  • Point your web-browser at http://yourdomain.com/MySQL_Diff-master/db_diff.php.

Beyond the Basics

Configuration File(s)

We direct MySQL_Diff with configuration files. Let’s take a look at one now:

[db_1]
host     = '127.0.0.1'
username = 'root'
password = 'letmein'
database = 'employees_1'
[db_2]
host     = '127.0.0.1'
username = 'root'
password = 'letmein'
database = 'employees_2'
[behaviour]
strict                = FALSE
ignore_comments       = TRUE
ignore_auto_increment = TRUE
[email]
to      = '[email protected]'
from    = '[email protected]'
subject = 'Database Schema Comparison'

As you can see, our configuration files follow the standard “.ini” format and we parse them with PHP’s parse_ini_file function. We organise the .ini files with sections, one each for the two databases that we want to compare, one to direct the behaviour of the script and any optional configuration that your application or framework might require for integration.

The two database configurations include all the properties that PDO requires for your connection to a MySQL server.

The behaviour configuration has just three directives:

  • strict: if set to true then we will test the two database schemata for an exact match and any differences will constitute a mismatch. Additionally, if strict is true then MySQL_Diff will not evaluate the ignore_comments and ignore_auto_increment settings, whatever their settings;
  • ignore_comments: if set to true then differences in schemata comments will not constitute a mismatch;
  • ignore_auto_increment: if set to true then differences in the auto_increment indices of the schemata will not constitute a mismatch.

The Controller

The Controller must have the following components in order for MySQL_Diff to function:

  • Autoloader;
  • Configuration Loader/Parser.

The Controller must, of course, instantiate the MySQL_Diff class and should also be able to handle the result in some way, for example: rendering to the browser or emailing the result.

A working example Controller, db_diff.php, is included in the MySQL_Diff repository.

Running from the Browser

One can run MySQL_Diff from the web-browser if the Controller allows it (you can run the example Controller db_diff.php from the browser). You can see from the output of the example controller that MySQL_Diff describes tables that don’t exist in either schema and also visually highlights differences in tables common to each database. From this, it is easy to see what to change to bring the schemata to parity.

If one doesn’t specify a configuration file at run-time (either via GET or POST) MySQL_Diff will automatically load the newest or most recently modified one. We instruct the program to use a specific configuration file by setting a config variable in our URL: the value of which should exclude the .ini extension and must only consist of upper or lower-case alphanumeric characters, the hyphen and/or underscore (a-z, A-Z, -, _).

For example: /db_diff.php?config=My_Config-1

Running from the Command Line

MySQL_Diff is arguably at its most useful when it runs on the CLI.

It can be an actor in a continuous integration workflow. For example, on one website I maintain, a Bash script runs MySQL_Diff during a pre-commit hook. If MySQL_Diff finds differences between the development and live databases then the commit is automatically aborted and the script emails me to advise me of the database disparity. This prevents those embarrassing errors that result from code referencing tables or fields that don’t exist or that are of an incorrect type, thus reducing downtime.

The example controller illustrates how to get MySQL_Diff to interact with the CLI. You might also find my “PHP From the Command Line” article helpful in this case.

Acknowledgements

Contribute

  1. Of course we should be programming defensively to trap these potential errors. ↩︎