How can I test that a SQL "CREATE TABLE" statement is equivalent to an existing table?

Question

For each table in my MySQL database I have a mytablename.sql file which contains the CREATE TABLE statement for that table.

I want to add a test to check that noone has added/altered a column in the live database without updating this file - that it, I want to check that I can re-create an empty database using these scripts.

How can I do this reliably?

One option would be using SHOW COLUMNS FROM mytable (or DESCRIBE mytable), which, on the command line would output in a tabular form like this:

+---------+------------------+------+-----+---------------------+----------------+
| Field   | Type             | Null | Key | Default             | Extra          |
+---------+------------------+------+-----+---------------------+----------------+
| pk_guid | int(10) unsigned | NO   | PRI | NULL                | auto_increment | 
| d_start | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
| d_end   | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
+---------+------------------+------+-----+---------------------+----------------+

And then create a temporary table and compare the results.

This would be fine, except that if any columns have been added to the live database then the results might not be in the same row order.

Unfortunately, it doesn't seem to be possible to use ORDER BY with SHOW COLUMNS.

Another option would be to use SHOW CREATE TABLE, but that includes information such as the AUTO_INCREMENT counter value, which I don't care about.

Is there a better way of doing this?

Answer 1

you wrote:

One option would be using SHOW COLUMNS ... on the command line .... [but the columns] might not be in the same row order.

You might force this order, post hoc. On a UNIX-ish system, you can simply:

$ mysql -Bse 'SHOW COLUMNS FROM possibly_altered' | sort

and compare that to similarly pared and sorted SHOW output on a temp table made by your .sql file. (The -Bs suppresses some of that mysql(1) fancy formatting and headers, not needed and somewhat inconvenient for a more programmatic comparison.)

Answer 2

I've done this using a somewhat ghetto/duct-tape PHP shell script across database servers (dev/production setup) in the past. Given you have 2 servers, and one of them is "right":

• grab a list of columns per each table to check. SHOW CREATE TABLE works, or just SELECT * FROM x LIMIT 1 if you aren't interested in possibly altered data types / defaults.
• array_sort() the field names alphabetically, and loop through each comparing whatever potential changes you're interested in.

Not recommending this though! I used this because the sysadmins didn't think anything of granting full db permissions to some outsourcing folks, so it was just a quick safety net, nothing as planned or permanent as unit tests.

---

A more elegant & cleaner way to do this (limited to MySQL5+ though?) would be to use the information_schema system database, particularly the COLUMNS table. If you don't have 2 servers, a separate database to hold/copy column info (e.g. a clone of information_schema with the latest "correct" schema) would work too. This would let you diff for new/dropped tables, indices, triggers, procedures, users/permissions, etc. as well.