How to compare two identical databases?

A project came into my hands where both the source code and the database differed in what was on production, on testing and in the repository. I needed to unify it. How to efficiently compare two identical databases and find the differences? I found and tried out several tools for comparing databases.

The world of web applications is a world of the masses and therefore the average quality is lower. So you come across all sorts of things. Even development where neither the application changes nor the database are versioned.

Database versioning

Tools for PHP have existed for years, such as phpmig, nextras/migrations or Phinx , which serve to describe changes to the database structure and to move smoothly between versions. The transition between versions of the database structure can be both forward to a new version and backward. Backward changes are used, for example, when an application upgrade has brought new problems that prevent it from being used. The prepared rollback migration is then used and getting the application back into a usable state is fast.

To document the changes, however, at least a plain list of ALTER SQL commands would be enough, those that need to be executed so that the structure and possibly also the data of the database can be upgraded for the new version of the application.

But how to solve the problem if we have two versions of the application, for example production and testing, where the databases differ not only in data but also in structure? And the original programmer left no information about the changes? Then there is nothing left but to compare the databases. Probably nobody wants to do it by hand even for small projects, so how to compare databases automatically?

Tools for comparing databases

The graphical tool MySQL Workbench should be able to compare databases, but I don't use it because this SW crashes on me and I didn't look into it any further.
I also came across several other, often console tools, which are usually more efficient for professional developer work.

In the end I chose, tried out and successfully used the software DBDiff

If you are just getting started with databases, take a look at the article Programming in SQL: PostgreSQL, MySQL/MariaDB.

DBDiff

The program requires version PHP7, but that is commonly available these days on the machines (in the environments) of PHP developers.

The DBDiff program can be downloaded from GitHub, so it is enough to clone the repository with the git command:

git clone https://github.com/DBDiff/DBDiff.git

Usage

First you need to configure the program. Create a configuration file, for example config.cgf, and write the access credentials into it. So write into the config file the access credentials for the databases we want to compare.
The program is then run as follows:

./dbdiff  --config=config.cgf   server1.example.com:server2.example.com

The program creates a file with information about the comparison in the form of SQL commands ./migration.sql.

The --type=all switch compares everything in the database, but with sizeable databases it takes up all the available operating memory.
PHP has a defined memory limit so that all the operating memory installed in the computer cannot be consumed. That is why it is necessary to adjust the memory_limit value in the file ./src/DBDiff.php, for example like this:

ini_set('memory_limit', '1512M');

Another and probably better option, if you want to compare all the content and not deal with the operating memory limit, is that the program can compare the database gradually, table by table. Example of use:

./dbdiff  --config=config.cgf
server1.example.com.category:server2.example.com.category
--type=all --output=migration_category.sql

Evaluation of the program

What did the DBDiff program recognize?
After looking into the generated migration script, it is clear that the program recognized:

• new tables
• added rows,
• added columns in a table

What does the DBDiff program not solve?

For the table with added columns it did not generate an update to set the values into the new columns. I solved it by making an export of the whole table with data and for the subsequent import using update. Dropping the table and importing did not work, because the table contains keys to foreign tables.

What does the DBDiff program recognize but not solve?

The DBDiff program recognized, but did not generate a sufficient SQL script for a changed comment on a table. Specifically, it generated a script that did an alter table, but without inserting that new comment.