mysql - unmatched - sql query to compare two table structures




MySQL query to show difference between development and production schema (4)

I would like to have a query using the schema database in MySQL
that shows the difference between the columns, triggers and stored procedures between two database schema's: production and development.

Query, not tools
I've seen Compare two MySQL databases
Which lists the tools that can perform this task, but I would like to know is if there is a query that can perform this task.
Please only suggest queries, I really do not want to know about tools, command line hacks or such.

I am looking to see if the production database and development database are out of sync.
And which fields, procedures etc where added or changed, so I can update the production database if I roll out a new update of the client software that uses the database.

I'm using MySQL 5.1 latest version.


All of the data that you're after should be in the tables in the information_schema database.

You might be able to do the comparison with some kind of a join that only shows you the differences but trying to do it in queries or a single query seems like an overly complicated way of approaching the problem, I think you're shooting yourself in the foot.

The quick and easy solution would be to to diff either the contents of a mysqldump --no-data of each database or to pull out the data from the information schema and diff that.


Johan you have already narrowed you answer domain by saying you want a "Query" to compare databases :)

However my suggestion for you is to think about "Binary Logging" . I have used it successfully for a similar purposes.

       a) enable logs 
       b) Go through all binary logs files 
       c) grep desired statements
       d) at then end purge/reset binary logs  ie. RESET MASTER 

Obviously you will do this on production db.

Other ways might be : How to synchronize development and production database


This is an oldy, but it works. Building on devart's example I went ahead and built the comparison for procedures and functions:

SET @source_db = 'qls_projects_for_comparison';
SET @target_db = 'qls_projects';

-- Pick one and comment out the other
-- SET @routine_type = 'FUNCTION';
SET @routine_type = 'PROCEDURE';

-- Get the ones only in the source
SELECT
  'Only in SOURCE' exist_type, C1.ROUTINE_NAME, C1.ROUTINE_SCHEMA,         
C1.ROUTINE_TYPE, C1.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as     'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES WHERE 
ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @source_db) C1
LEFT JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C2.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones only in the target
SELECT
  'Only in TARGET' exist_type, C2.ROUTINE_NAME, C2.ROUTINE_SCHEMA,             
C2.ROUTINE_TYPE, C2.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as 'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
RIGHT JOIN (SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C1.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones in both and compare the bodies of the routines 

SELECT 
'In both schemas' exist_type
, C2.ROUTINE_NAME
, C2.ROUTINE_SCHEMA
, C2.ROUTINE_TYPE
, C2.LAST_ALTERED
, C1.DEFINER as 'Source Definer'
, C2.DEFINER as 'Target Definer', 
IF(C1.ROUTINE_DEFINITION=C2.ROUTINE_DEFINITION, 'Matches','Does Not Match') 
as 'Compare Definitions'
FROM (    
   (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
  INNER JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
    ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)

t1 compare to t2

select 
 (case t1.table_name=t2.table_name when 1 then concat(t1.table_name,"==",t2.table_name) else concat(t1.table_name,"!=",t2.table_name) end) as table_name,
 (case t1.column_name=t2.column_name when 1 then concat(t1.column_name,"==", t2.column_name) else concat(t1.column_name,"!=", t2.column_name) end) as column_name,
 (case t1.ORDINAL_POSITION=t2.ORDINAL_POSITION when 1 then concat(t1.ORDINAL_POSITION,"==", t2.ORDINAL_POSITION) else concat(t1.ORDINAL_POSITION,"!=", t2.ORDINAL_POSITION) end) as ORDINAL_POSITION
......--columns in information_schema
from columns t1 left join (select * from columns where table_schema='t2') t2 on t2.table_name=t1.table_name and t2.column_name=t1.column_name where t1.table_schema='t1'; 

hope this help!!





information-schema