Re: Errors with Information_Schema in Create Target Results
Posted by: Peter Brawley
Date: October 30, 2021 08:52AM

That'll confirm matching rowcounts, nothing more.

It's safe to assume that two dbs on different servers are identical if a complete dump from one was imported without error on the other with identical mysql versibns and my.cnf/ini settings.

If versions or settings might be an issue and absolutely certain identicality is required, them for any db you can export the 2nd db back to the 1st server under a different name and run per-table comparisons on tables in the two dbs using this procedure ...

drop procedure if exists comparetables;
delimiter go
create procedure comparetables( tbla char(64), tblb char(64), columnlist char(255) )
begin
  set @sql = concat( " SELECT MIN(TableName) as TableName, ", columnlist,  
                     " FROM ( ",
                     "  SELECT '", tbla, "' as TableName, ", columnlist,  
                     "  FROM ", tbla, 
                     "  UNION ALL ",
                     "  SELECT '", tblb, "' as TableName, ", columnlist,
                     "  FROM ", tblb, 
                     ") AS tmp ", 
                     " GROUP BY ", columnlist, 
                     " HAVING COUNT(*) = 1 ", 
                     " ORDER BY 1" 
                   );
  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
end;
go
delimiter ;

... but with a big db, it'll take a while.

Or see "Compare two databases" at https://www.artfulsoftware.com/infotree/queries.php.



Edited 1 time(s). Last edit at 10/30/2021 09:12AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Errors with Information_Schema in Create Target Results
October 30, 2021 08:52AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.