CODEWITHSUNDEEP
mysqlsql
mux
mux

Reputation: 475

How to check if a MySQL schema was changed

Suppose I want to check if the schema defined in a MySQL database is equal to the one defined in the code - a business requirement to ensure there is no difference between the 2.

I am able to do the comparison by querying the information_schema tables. However, in some cases, some objects return different from the way I created them. Database represents them differently.

Is there a way to do the comparison on the database or use an internal function of MySQL?

Upvotes: 0

Views: 813

Answers (1)

The Impaler
The Impaler

Reputation: 48865

Formally, you are creating the index using a "natural form" of the expression (the one you type), while MariaDB's engine is returning the "canonical form" of it. The only way of comparing them for equality is to produce and store the canonical form of the index.

The trick I have used is to run the whole SQL script with the schema creation in a separate database (or schema). Then you can retrieve and store the canonical form of it. Once you have the canonical form you can use it to compare with the TEST, STAGING, QA, UAT, and PROD schemas with certainty.

With a little bit of coding you can automate the process and the whole thing can be done in a jiffy. Remember, you'll probably need to do this time and time again, when you deploy new versions of the code.

Now, you'll need to make sure you are running this parallel process in a MariaDB engine with the same version as the target one, to ensure all of them produce the exact same form.

Having said all that, I developed a library called "Sentinel" that compares whole schemas and provides the differences at runtime. That way it snapshots the DEV database, checks the TEST and PROD database during startup, and reports any differences in the logs. I have only tested it recently in Oracle, DB2, and PostgreSQL, so I don't remember how well it fares in MariaDB (that is supposedly supported).

Upvotes: 2

Related Questions