What are the Reasons for a SQL Diff tool showing differences for 2 identical stored procs on 2 different db servers
Utilising a number of different SQL "diff/comparison" tools I am getting differences reported for stored procedures and tables which are identical (in everything but the case of certain keywords - bear with me (the expectation is for the case to be the same)) between a database living on 2 different database开发者_StackOverflow servers.
The "client" db admin tools and the comparison tools I am usgin, have been both configured to try and capitalise keywords and also not capitalise keywords in order to provide consistency.
I have also ran the same script to create the sprocs on both db servers. The mySQL database servers hosting the databases involved in the comparison are both the same version;version 5.1
What mySQL server configuration settings do I need to concern myself with, so that I can run comparisons and NOT see differences flagged where they don't actually exist...
The intention here is that I get an accurate report of differences and can then confidently generate database scripts including/excluding changes during promotion, sort of what you'd expect from using a SQL diff tool!!
Thanks,
In my experience, when two schemas were declared different, although I expected them to be exact, was due to
- slight difference in the MySQL version, so white space and capitalization in the table definitions (
CREATE TABLE
s) were different - different default charsets (at the server, db or table level) on the different servers
- different ordering of indexes on a table (due to me dropping and re-adding it on one of the servers)
++
- The
AUTO_INCREMENT
value in theCREATE TABLE
If your two servers are not both the same OS, it is possible that there is a difference in newline characters
http://en.wikipedia.org/wiki/Newline
精彩评论