problem with synchronization

[gvvelev]

I encountered a bug in SQLyog v4.0 BETA 5 while I was synchronizing the database on our 2 servers.

I ran one-way synchronization on all tables in the database.

All tables were synchronized except two. I got the following errors in the status window that said:

Field count not equal for '`countries`' table

Column NAME mismatch for '`settings`' table

I thought the structure of these tables on the target database was different than the ones on the source database. Then I emptied the target database using the menu option

DB->Empty Database…

and ran the Structure Synchronization tool and it created an .sql batch file which I used to create the tables in the destination database.

I re-ran Database Synchronization Wizard. The status window said the tables in the target database contained exactly as much records as the source database (nothing was inserted, updated or deleted) and the same errors I mentioned above.

I ran mysql on the destination server and typed

SELECT * FROM countries;

it returned an empty set.

If I execute SELECT on any table in the destination database mysql returns an empty set.

I look forward to receiving a reply. Thanks in advance.

George Velev

[Ritesh]

Database Sync Tool (SJA) requires the structure of source and target tables to be exactly same. Are you sure the tables countries and settings are having the same structure and column count?

Can you cut-n-paste the CREATE TABLE… stmt. for the tables out here? Can you tell me the versions of your source and target MySQL servers?

Quote:

If I execute SELECT on any table in the destination database mysql returns an empty set.

But you said:

Quote:

All tables were synchronized except two

I believe you are getting empty resultset only for the above two tables.

[gvvelev]

Hi,

You have misunderstood me.

All tables except two were synchronized BEFORE I emptied the destination database.

After I had reported my bug I tried the following:

I ran Tools->export data as batch scripts

and exported only the structure of the tables in the source database in an .sql file

then I ran this .sql file on the destination database. Thus all tables on source and destination database have the same structure.

Then I ran Tools->Database synchronization wizard and told it to do a one-way synchronization, expecting that the wizard would simply copy all the data from source to destination.

But it did not inserted, updated or deleted anything and said that

Field count not equal for '`countries`' table

Column NAME mismatch for '`settings`' table

which obviously isnt true because the table structure had already been copied.

And when I log in to mysql server and ran a SELECT query it returns an empty set on either table.

SQLyog' main window also says the tables are empty.

create statements:

drop table if exists `countries`;

CREATE TABLE `countries` (

`Country_ID` int(11) NOT NULL auto_increment,

`Country` varchar(30) NOT NULL default '',

`VAT` float NOT NULL default '0',

`Active` int(11) NOT NULL default '1',

`dpd` int(11) NOT NULL default '0',

`fedex` int(11) NOT NULL default '0',

PRIMARY KEY (`Country_ID`)

) TYPE=MyISAM;

I'm sorry I cannot post the CREATE statement of the 'settings' table because I would uncover a business secret.

Both servers have Mysql 4.0.20 running.

Source is running on FreeBSD 4.9

Destination is running on FreeBSD 4.10

I hope there is a solution. SQLyog looks very cool!

Regards,

George Velev

[gvvelev]

…one more thing.

I would like to clarify that both servers are remote servers.

George

[Ritesh]

Quote:

Field count not equal for '`countries`' table

This means that field count in the source and target servers are not equal. Considering the steps you are following, this looks strange.

Can you execute

Code:

show fields from `countries`

in both servers and see if there is any difference?

Quote:

Column NAME mismatch for '`settings`' table

Is the column NAME differentiated as VARCHAR and CHAR in the two servers?

[Ritesh]

One thing you can try is execute DB->Empty Database… in the target server and run the Sync process. SQLyog will create the table(s) in the target server and insert all the data from the source to the target.

[gvvelev]

Table countries

Source database

mysql> show fields from `countries`;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| Country_ID | int(11) | | PRI | NULL | auto_increment |

| Country | varchar(30) | | | | |

| VAT | float | | | 0 | |

| Active | int(11) | | | 1 | |

| dpd | int(11) | | | 0 | |

| fedex | int(11) | | | 0 | |

+

---

+

---

+

---

+

---

+

---

+

---

+

6 rows in set (0.00 sec)

mysql>

Target database

mysql> show fields from `countries`;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| Country_ID | int(11) | | PRI | NULL | auto_increment |

| Country | varchar(30) | | | | |

| VAT | float | | | 0 | |

| Active | int(11) | | | 1 | |

| dpd | int(11) | | | 0 | |

| fedex | int(11) | | | 0 | |

+

---

+

---

+

---

+

---

+

---

+

---

+

6 rows in set (0.00 sec)

mysql>

Table settings ( I've changed the names of the fields to make the table structure no more confidential). Neither field is called 'NAME' (I mean the table doesnt have a column 'NAME')

mysql> show fields from settings;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| setting1 | int(11) | | | 0 | |

| setting2 | int(11) | | | 0 | |

| setting3 | int(11) | | | 0 | |

| setting4 | int(11) | | | 0 | |

| setting5 | int(11) | | | 0 | |

| setting6 | int(11) | | | 5 | |

| Admin | int(11) | | PRI | NULL | auto_increment |

| setting7 | int(11) | | | 0 | |

| setting8 | int(11) | | | 6 | |

+

---

+

---

+

---

+

---

+

---

+

---

+

9 rows in set (0.00 sec)

mysql>

destination table

mysql> show fields from settings;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| setting1 | int(11) | | | 0 | |

| setting2 | int(11) | | | 0 | |

| setting3 | int(11) | | | 0 | |

| setting4 | int(11) | | | 0 | |

| setting5 | int(11) | | | 0 | |

| setting6 | int(11) | | | 5 | |

| Admin | int(11) | | PRI | NULL | auto_increment |

| setting7 | int(11) | | | 0 | |

| setting8 | int(11) | | | 6 | |

+

---

+

---

+

---

+

---

+

---

+

---

+

9 rows in set (0.00 sec)

mysql>

I am going to try what you advise me in your last post.

[gvvelev]

OK. I foud out what the problem was. It is a minor bug.

Let me explain what I did this morning from the beginning.

1. I chose SQLyog from the Start menu of Windows XP

2. From the drop-down “saved connections” I chose the destination mysql server

3. I pressed “connect”. SQLyog connected successfully to the destination mysql server.

4. from the “file” menu I chose “connect”

5. From the drop-down “saved connections” I chose the source mysql server

6. I pressed “connect” and SQLyog connected successfully to the source mysql server.

7. From the “tools” menu I chose “Database synchronization wizard”

8. the window “Welcome to SQLyog Database synchronization wizard” sprang out

9. I chose “Start a new synchronization session”

10. the window where I can choose the source and destination database springs out

11. the drop-down “Source – copy from” displayed the source server and its parameters are below,

the drop-down “Target – copy from” displayed the source server and the parameters of my localhost server (I mean the computer I am working on)

12. I pressed “next” and completed the steps to the end. I have synchronized the source database and the database on my localhost.

I had made a synchronization between the source mysql server and my localhost on another database before I tried to synchronize the source and target servers.

So – to recapitulate

The window “provide details for source and target databases” displays the last connected mysql server in both “source copy-from” and “target – copy from” drop-downs, but the parameters listed below the drop-downs are the parameters of the last synchronized mysql servers.

I am sorry I cannot provide you a screenshot of that window – host name, username and password, as well as database name are confidential.

I hope this will help to do the nessesary corrections to the source code of SQLyog. I continue testing with scheduled synchronization.

I have a question but it is not about bugs so I will post it in another section.

Regards,

George Velev

[Ritesh]

Aha! so you were selecting the wrong server.

By default, SQLyog will show last used connection parameters. You can select an existing connection details from Saved Connections dropdown.

This is a minor interface issue which we plan to improve upon in SQLyog 4.0 BETA 6 😀

[Shadow]

But If you have only one saved connection, then there is no way to get SQLyog retrive the saved parameters. You have to enter them manually!

[Ritesh]

This bug is already fixed in SQLyog Enterprise 4.0 BETA 6 😀

[Shadow]

I just started downloading it…

[Author]

Posts