AnySQL.net English

    We may have development database and production database. We may have the same data stored in the same table on multiple databases just because the data volume is too large to fit in one database. When we are making the structure change, we hope they are in consistent state.

    You may want to compare table structure for different tables, for example, we define one table for each day's data, or each month's data. And also you may want to compare the index difference between these tables.

    I always perform the structure changes for multiple tables across multiple databases. In order to reduce the human errors, I have to write an effective script to check them and send email to me if something was wrong. The script I wrote is named "compare_column.pl".

    With this powerful perl script (Download), What I need to do is to create a configuration table to specify which tables on which databases to be compared, then create a job to run it weekly or daily.

    I will tell you how to create the configuration later.

    I defined 3 compare column method. They are :

    1, TABLE. For example : Table EMP on all the database should have the same structure.

    2, HOST. For example : Table EMP and table EMPLOYEE should have the same structure on production database.

    3, ALL. For example: Table EMP on test database should have the same columns with table EMPLOYEE on production database.

    With this three compare methods, we can easily compare any tables on any databases. The compare are not limited on two tables compare, for example, you can compare ten tables in one time if they suppose to have the same structure.

    We will create the configuration file and run it for each method in later chapters.

    I will introduce the first compare column method to you. I have a database named "prod" (schema "anysql") and a database named "test" (schema "scott"). There are a table named "EMP" on both databases. I modify the column "ENAME" length, and add a distinct column to each of the table for the demo. The configuration file is named "test.cfg", and contain the following lines.

TABLE:  -
   anysql/anysql@prod, scott/tiger@test | -
   EMP, DEPT

    The minus operation means continue to next line. In this configuration, we will compare "EMP" and "DEPT" tables on both databases. Now run the following command :

compare_column.pl -f test.cfg

    I get the following output on screen :

Comparing structure by TABLE DEPT ...
Comparing structure by TABLE EMP ...
     [MISMATCH] ENAME VARCHAR2(20) :
                       scott@test
     [MISMATCH] ENAME VARCHAR2(10) :
                       anysql@prod
     [MISSING ] COL_PROD :
               scott@test
     [MISSING ] COL_TEST :
               anysql@prod

    You can see that the table "DEPT" are in consist state, but for the table "EMP", there is a column type mismatch for the column "ENAME", and column "COL_TEST" is missing on database "PROD", and column "COL_PROD" is missing on database "TEST".

    By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.

    I will introduce the second compare column method to you. I have a database named "prod" (schema "anysql"). There are two tables named "EMP" and "EMP_BAK". As business requirement, these two tables should have same columns. I rename the "EMP" table in previous case to "EMP_BAK", and recreate the "EMP" table for the demo. The configuration file is named "test.cfg", and contain the following lines.

HOST: anysql/anysql@prod | EMP, EMP_BAK

    In this configuration, we will compare "EMP" and "EMP_BAK" tables. Now run the following command :

compare_column.pl -f test.cfg

    I get the following output on screen :

Comparing structure on HOST anysql@prod...
Tables:
       EMP      EMP_BAK
     [MISMATCH] ENAME VARCHAR2(10) :
                       EMP_BAK
     [MISMATCH] ENAME VARCHAR2(30) :
                       EMP
     [MISSING ] COL_PROD :
               EMP

    You can see that the tables "EMP" and "EMP_BAK" are in inconsistent state. The is one column ("ENAME" column) type mismatch, and one column ("COL_PROD") is missing on table "EMP".

    By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.

    I will introduce the third compare column method to you. I want to compare the table "EMP_BAK" on database "prod" with table "EMP" on database "TEST". As business required, we need these two tables on different databases to be consistent. I create a configuration as following :

ALL: -
   EMP_BAK!anysql/anysql@prod | -
   EMP!scott/tiger@test

    The minus operation means continue to next line. Now run the following command :

compare_column.pl -f test.cfg

    I get the following output on screen :

Comparing structure of tables on different hosts ...
Tables
       emp_bak  EMP
     [MISMATCH] ENAME VARCHAR2(20) :
                       EMP!test
     [MISMATCH] ENAME VARCHAR2(10) :
                       emp_bak!prod
     [MISSING ] COL_PROD :
               EMP!test
     [MISSING ] COL_TEST :
               emp_bak!prod

    You can see that the tables "EMP" and "EMP_BAK" are in inconsistent state. The is one column ("ENAME" column) type mismatch, and one column ("COL_PROD") is missing on test database (table : "EMP"), and one column ("COL_TEST") missing on production database (table : "EMP_BAK").

    By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.