Hi,
Is there any tool through we can get the sql query for data difference in two databases

For comparing the schema, a quick and dirty technique is to run mysqldump to a text file for each one and then use diff (or a GUI equivalent like BeyondCompare).

If you use the --order-by-primary-key, it should also work for the data.

or use an appropriate bit of SQL, bearing in mind that MySQL can easily join tables in different databases.

off hand the access query wizard will create the skeleton SQL you need.

differences in two tables or differences in two databases?

for the latter, use a tool

the marketplace for third-party database tools is wide open, and i am confident that there are tools out there for this specific purpose

both. leastaways it worked for me, both databases were runing the same physical server, but in MySQL paralance they were separate schema, separate databases. admittedly not that big, but the two could be stitched together on an ad hoc basis

how does your access query wizard detect a table that exists in one database but not the other? how does it detect a column in one table but not the other?

sorry access was to give the exampl of the sql
the query I'd run would be in MySQL Query browser.

I was trying to think of a quick and easy way of locating the correct SQL.. Im not suggesting for one minute that that sort of requirement work should be done in Access when there are better candidates elsewhere.

I'm happy to admit Im dumb, but I'd hope NOT that dumb.

okay, so using MySQL, how would you write the query to detect when one database has a table that isn't in the other one? using MySQL, how would you write the query to detect when one table has a column that isn't in the other one?

I wouldn't, but the OP did request an ability to detect data differences, which I interpretated as same table, same columns and the OP was looking for discrepancies in data, not the table or column design

yes, i realize that's what you interpreted (not interpretated )

where the hell is that original poster, anyway?




Edit: i just noticed, this is post #11,111 -- w00h00!!!!

The reason I suggested a dump is because it's a ten minute job to figure out the arguments to mysqldump and diff and you can see what's changed. The OP might be gone because he has his answer.

You just do select colId from foobase.whateversystemtable where colId IS NOT IN (SELECT colId from barbase.whateversystemtable)... I think, I don't do much MySQL, but I'm pretty sure they have catalog tables like most other DBMSs. (Edit: well, I was thinking databases, not tables, so there'd be a where clause in there.)

I mean, it gets tricky when you have to check the types and downright annoying if there are different versions / products / whatever, but the nice thing about the relational model is that whatever's in the catalog is really all there is.

There is tool Navicate for MySQL through which you can find schema as well as data differances