How to check if a MySQL index expression was changed using information schema-CodePudding

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 case of index expression (for example, (ABS(2*c))) or check constraints, the database returns a slightly different expression than what is used in creation time. I think it's a string version of the expression AST.

Here are 2 examples that show how I created the indexes and how they are returned from the database.

(ABS(2*c))) => (abs((2 * `c`)))
(upper(c) lower(c) '!') => (((upper(`c`)   lower(`c`))   _latin1'!'))

Is there a way to do the comparison on the database or use an internal function (using a query), or any other way to check if my indexes were changed without writing some parsing code?

CodePudding user response：

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).