Compare Tables?

[smine]

can SQLyog compare tables? i don't really want to synchronize anything, just find out if 2 tables are identical or different. if SQLyog is not the right tool for this, do you have any suggestions? i was using information_schema and CHECKSUM but MySQL documentation says CHECKSUM(table1)=CHECKSUM(table2) does not guarantee that table1=table2.

[navyashree.r]

Sure Smine,

Undoubtedly, SQLyog can perform this action.

You can use “Schema Synchronization tool” from Powertools menu or simply click Ctrl+Q.

It's a visual comparison/synchronization tool where you can just 'compare' between different databases across different MySQL servers. It shows the differences between tables, indexes, columns and routines of two databases, and generating scripts to bring them in Sync.

Just by comparing the databases, it won't sync the databases. Only when you click on EXECUTE ALL button, it syncs. So, you can see the difference by clicking on COMPARE button.

For more info please read our HELP document.

Regards,

Navya

[smine]

'navyashree.r' wrote on '24:

Sure Smine,

You can use “Schema Synchronization tool” from Powertools menu or simply click Ctrl+Q.

hi. thanks! i tried Schema Synchronization Tool, but it was not quite what i need. i need to compare the tables including data. Schema Synch appears to compare only the structure of the tables.

can SQLyog perform data comparison, too? if not, do you know of some other tool to do that?

[peterlaursen]

Data Comparison is available in the Data Synchronization feature. If you do not want to actually change anything use the option to 'generate sync script'.

[smine]

yes, sorry, i am stupid. i was using Schema Sync. i am using Database Sync now. thanks!

[smine]

okay, i am using Database Synchronization Wizard. i ask it to only generate a script and not modify any data (One-way sync with “Don't delete extra rows…” checked). i receive the error message in sja.log

Code:

Data Sync script generation started at Fri Mar 26 09:21:06 2010
`ric_lookup` 124011 124011 Error No. 1036 Table 'ric_lookup' is read only
`security_lookup` 276216 276216 Error No. 1036 Table 'security_lookup' is read only

if i am only generating a script, why does it care about read-only? because of the error, i do not see the number of Inserts, Updates, Deletes in the Database Sync window.

[peterlaursen]

Good point.  I think we should skip checking for that condition if user selected to 'generate sync script' only.

[nithin]

Hello,

Please tell,

1) OS where SQLyog is running

2) MySQL server version of source and target (and OS where server installed)

3) Please execute this on both source and target:

SHOW GRANTS FOR 'username'@'host';

[peterlaursen]

Also execute:

SHOW VARIABLES LIKE 'read_only';

Refer to:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_read_only

and

http://bugs.mysql.com/bug.php?id=35732

[smine]

1) Win XP Pro v2002 SP3

2) source and target are same machine: MySQL v5.0.45; OS: redhat-linux-gnu (Sleepycat Software: Berkeley DB 4.1.24: (July 4, 2007))

3) source: GRANT SELECT, EXECUTE; target: not in the list of tables of SHOW GRANTS results

4) read_only='OFF'

[nithin]

1) We do not understand “target: not in the list of tables of SHOW GRANTS results”. Please detail this.

2) Could you try to add global SUPER privilege to user? Just to check if server 5.0.45 is affected by same bug as the one we linked to above.

[nithin]

Also please tell are those Innodb tables?

Also execute these queries at source db and target db and tell if any error:

SELECT * FROM FOR UPDATE;

SELECT * FROM LOCK IN SHARE MODE;

Regards

Nithin

[smine]

'nithin' wrote on '30:

1) We do not understand “target: not in the list of tables of SHOW GRANTS results”. Please detail this.

2) Could you try to add global SUPER privilege to user? Just to check if server 5.0.45 is affected by same bug as the one we linked to above.

1) the only SHOW GRANTS command i can get to work is just “SHOW GRANTS” (without the “FOR [email=””]'user'@'host'[/email]). when i execute that command, the database i am calling the target is not in the list of results

2) it will not be possible to change any permissions on these databases, not even for testing. i do not have access to them and system administrator will not allow any changes

[smine]

'nithin' wrote on '30:

Also please tell are those Innodb tables?

Also execute these queries at source db and target db and tell if any error:

SELECT * FROM FOR UPDATE;

SELECT * FROM LOCK IN SHARE MODE;

Regards

Nithin

all databases are MyISAM.

for the first query i get the following for both source and target

Query : SELECT * FROM mytable FOR UPDATE

Error Code : 1036

Table 'mytable' is read only

for the second query, both source and target complete successfully with no errors or warnings.

[peterlaursen]

ok .. now see: http://dev.mysql.com…king-reads.html

“For index records the search encounters, SELECT … FOR UPDATE blocks other sessions from doing SELECT … LOCK IN SHARE MODE or from reading in certain transaction isolation level.”  So what is your transaction isolation levels?  Check 

SHOW VARIABLES LIKE 'tx_isolation'

Also:

http://dev.mysql.com…ar_tx_isolation

and

http://dev.mysql.com…repeatable-read

There have been several server bugs reported with tx-isolation-levels but we also have overlooked something.  But we never had a similar report and cannot reproduce this.  So a bug wiht 5.0.45 is a possibility. It seems that even MySQL people have problems getting the fine-grained details in their head – for instance: http://ronaldbradfor…/tx_isolation/.

If upgrading the server is out of question then at least try change tx_level to READ-COMMITTED (ie

Code:

SET @@global.tx_isolation = 'READ-COMMITTED'

before running the sync tool.

[peterlaursen]

 “For index records the search encounters, SELECT … FOR UPDATE blocks other sessions from doing SELECT … LOCK IN SHARE MODE”.  

Well .. we are doing SELECT … FOR UPDATE + SELECT … LOCK IN SHARE MODE.  However only *other sessions* should be blocked.  

Are you using HTTP-tunnel? 

[peterlaursen]

Also you could try to execute UNLOCK TABLES.

[peterlaursen]

One more question: Do you have a Primary Key defined on the table?

[smine]

'peterlaursen' wrote on '01:

…So what is your transaction isolation levels? Check

SHOW VARIABLES LIKE 'tx_isolation'

tx_isolation='REPEATABLE-READ'

i will try READ-COMMITTED

[smine]

'peterlaursen' wrote on '02:

One more question: Do you have a Primary Key defined on the table?

yes, both source and target have the same Primary Key, which consists of 3 columns.

[smine]

'peterlaursen' wrote on '01:

Are you using HTTP-tunnel?

we are not using HTTP tunneling.

[smine]

'peterlaursen' wrote on '01:

If upgrading the server is out of question then at least try change tx_level to READ-COMMITTED (ie

Code:

SET @@global.tx_isolation = 'READ-COMMITTED'

i receive:

Query : SET @@global.tx_isolation = 'READ-COMMITTED'

Error Code : 1227

Access denied; you need the SUPER privilege for this operation

i guess the universe does not want me to run the sync tool

[peterlaursen]

Then let us try to either understand or beat the universe.

[peterlaursen]

Unfortunately universe is still a mystery.

I did: 

Code:

GRANT SELECT ON source.* TO 'peter'@'localhost';
GRANT SELECT ON target.* TO 'peter'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON source.* TO 'laursen'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON target.* TO 'laursen'@'localhost';

— actually “GRANT SELECT ON ” is ALL privileges what is required if only script generation is wanted.

SHOW VARIABLES LIKE 'tx%' — returns: “tx_isolation REPEATABLE-READ” (default) on the server.  Not the issue.  Sorry for bringing this into the discussion. The server is 5.1.45.

I connect as 'peter' to source and 'laursen' to target. I tried all possible combinations with and without script option with both an empty and populated database as target. Database contains a mix of InnoDB and MyISAM tables (mostly InnoD:cool:.  I manipulated data and reversed source and target several times so that both INSERT, UPDATES and DELETES took place. The issue is not reproducible for me. There also is no logical reason why it should be.

I think I will have to conclude that this is a problem with your hosting and their configuration (they may have patched the server). It would also be very strange if we did not have any reports about this before and did not encounter it ourselves.  Can you try (a) server(s) where you are in control (like a local server) instead of this hosted/shared server?

[smine]

'peterlaursen' wrote on '07:

Unfortunately universe is still a mystery. … I think I will have to conclude that this is a problem with your hosting and their configuration (they may have patched the server).

perhaps i can bribe our administrator to help understand this problem. the universe is a big place, there must be an answer out there somewhere!

thanks you for all your help. i will report back when i have new information.

[Author]

Posts