Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version to the same version number as the fresh install.

Basically I want to make sure that the upgrade scripts worked properly and all the proper columns and constraints are in the upgraded version just as they are in fresh install. I thought a simple diff/sdiff would be good enough for this but I ran into the problem with that: Column order.

The column order between the fresh install and the upgrade are different giving many false positive "differences". It is easy to see when looking at the output that the same column is simply in a different spot but with hundreds of tables it would take a long time to check and identify these false positives.

Is there a better way to compare these two dumps that will ignore column order (or a way to force the dumps to have columns in alphabetical order or something similar) or an entirely different approach that would work better. I am admittedly fairly new to MySQL and am just learning as I go so any help would be much appreciated.

Thanks.

share|improve this question
    
Are the mysqldumps already loaded in two different MySQL Instances ??? –  RolandoMySQLDBA Mar 22 '13 at 15:07
    
One of the dumps, the fresh install dump, i have made and will give to the person doing the upgrade of the customer system. My hope is that they can do teh upgrade of the system and then compare it to the dump i have provided them from a fresh install which is a 'known good' schema. So they will have access to one of the database as it is running in the server they are upgrade but the other one will be just a dump file –  HumanTarget Mar 22 '13 at 15:09
add comment

1 Answer 1

up vote 0 down vote accepted

Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS. You will need the following:

  • MySQL Instance with the old data loaded
  • MySQL Instance with the new data loaded
  • Some Patience and Scripting

With this setup in mind, here is what you need to do:

STEP 01) Use Query to Present Columns Ordered Per Database/Table

Here is the Query:

SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;

This will give you every table followed by a CSV list of alphabetically-sorted columns

STEP 02) Launch this Column Query From Both MySQL Instances

IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}"  > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt  > column_listing.txt
cat column_listing2.txt >> column_listing.txt

Run the Column Query and Collect the Results in column_listing.txt

STEP 03) Perform Unique Sort ; Check For Duplicate Table Names

sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}

If everything is identical, DIF_LINES should 0. Otherwise, there are duplicates present.

STEP 04) If There Are Duplicates, Find Them

diff column_listing1.txt column_listing2.txt

Give it a Try !!!

UPDATE 2013-03-22 13:04 EDT

You can also check other metadata tables such as

TABLE_CONSTRAINTS

mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

STATISTICS

mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

REFERENTIAL_CONSTRAINTS

mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql>

Setup queries that perform the same styled comparison

share|improve this answer
    
Thanks, I will give this a try now. Any thoughts on how to check and make sure the constraints are also the same between the two? –  HumanTarget Mar 22 '13 at 16:29
    
Thank you very much !!! –  RolandoMySQLDBA Apr 2 '13 at 12:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.