Unable To Sync 2 Databases

[peg110]

I have SQLyog Enterprise – MySQL GUI v5.21. (NOT a trial version) I got this SPECIFICALLY for the database synchronization feature.

I am running it (currently) on a Microsoft Windows Server 2003 for Small Business Server Service Pack 1 IP Address 192.168.254.30 (although I have tried doing the following on an XP SP2 machine with identical results… read on.)

I have 2 MySQL Databases on my Network:

– The first (Running on a Redhat Linux 7.3) is mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)

— IP Address 192.168.254.105

— Named: Johan

– The Second (running on a Fedora Core 4) is mysql Ver 14.7 Distrib 4.1.11, for redhat-linux-gnu (i386)

— IP Address 192.168.254.105

— Named: FC4

I have full root privileges on all machines and there is a mysql user called root also with FULL Privileges setup on BOTH mysql database all password protected.

I have connections setup within the SQLyog for BOTH servers (First connection saved as Johan Second one saved as FC4). I am able to Query, insert, delete, update,etc… on both servers using the SQLyog with their respective connections (individually) without problem.

I would like to have server 2 (FC4) take over duties of Server 1 (Johan) so I want to get a copy of the database over. Enter SQLyog Database Synchronization Wizard:

I launch the wizard and selected items as follows:

Welcome To SQLyog Database Synchronization Wizard

– Start a new Synchronization Session

– Select Next >

Provide details about SOURCE and TARGET MySQL Servers

– SOURCE Connection selected Johan (verified Other connection details for SERVER are correct)

– TARGET Connection selected FC4 (verified Other connection details for SERVER are correct)

– SOURCE Database selected appropriate database name

– TARGET Database selected appropriate database with the SAME name as Source but that is EMPTY of data

---

Both Target and Source are using ROOT with the appropriate ROOT Password

---

---

Both Target and Source Databases are Running on Port 3306

---

---

Both Target and Source Databases are using the Default Charset

---

– Select Next >

Advanced options for data synchronization

– Selected a One way synchroniztiaon No other options selected for the “Advanced Options” section

– Select Next >

Select table(s) that you want to synchronize

– Selected [All]… No other options selected for the “Select Table(s)” section

– Select Next >

Send results from Query at scheduled intervals

– Selected to Run immediately

– Log file: C:Program FilesSQLyog Enterprisesja.log

– Session XML File: C:Program FilesSQLyog Enterprisesjasession.xml

– Select Next >

After selecting the NEXT on that last section, I get a window stating

“Completing the SQLyog Database Synchronization Wizard” wrote:

SQLyog Job Agent Version 5.21

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Fri Jan 19 08:56:12 2007

ERROR: 1045, Access denied for user: 'root'@'192.168.254.30' (Using password: YES)

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

Also states that “Source” and “Target” are complete.

I check the error log for “Complete error details” and i just get the same “ERROR: 1045, Access denied for user: 'root'@'192.168.254.30' (Using password: YES)” that was displayed on the screen (along with all the other failed attempts).

Then (here's the Kicker), I press NEXT a final time, and I get a dialog box stating that “The wizard was completed SUCCESSFULLY.” Ok, I suppose the WIZARD completed successfully, but the JOB did not!

So the question is… What am I doing wrong?

(oh yeah, my user ROOT on MySQL has permissions from ANY host as well)

[peterlaursen]

Code:

Ok, I suppose the WIZARD completed successfully, but the JOB did not!

Yes .. that is what it says!

Obviously you cannot authenticate!

1) Please doublecheck user and password

2) Run the wizard but select to save the jobfile (and not execute).  Are user details in the jobfile OK?

3) Try a 'Notifications Service' job on that server. for instance juste execute 'SELECT'1';” …

Questions:

1) do you use any kind of tunnelling?

2) if not does user have access from remote machines?

[peg110]

peterlaursen wrote on Jan 20 2007, 02:31 AM:

Code:

Ok, I suppose the WIZARD completed successfully, but the JOB did not!

Yes .. that is what it says!

Obviously you cannot authenticate!

That's what the error indicates, however, I am able to use SQLYog to access both servers individually, access tables, Query, update, delete, create tables and databases, and virtually anything I want. So IS authentication REALLY the issue? I dunno… that's why I am here.

peterlaursen wrote:

1) Please doublecheck user and password DONE. Everything is OK!

2) Run the wizard but select to save the jobfile (and not execute). Are user details in the jobfile OK? Yes… See Code Block below.

3) Try a 'Notifications Service' job on that server. for instance juste execute 'SELECT'1';” …

Same result as the SYNC job. But again, I can run the query manually and it works fine.

Questions:

1) do you use any kind of tunnelling? NO

2) if not does user have access from remote machines? YES

Here is the “Saved” job file.

Code:

192.168.254.105
root PASSWORD 3306 [default]
DBTABLE

192.168.254.102
root PASSWORD 3306 [default]
DBTABLE

All tests or attempts to resolve this problem (listed above) were run from a Windows XP sp2 machine at ip address 192.168.254.30.

Any thoughts would be wonderful.

P.S. I haven't gotten back to this post in a while because of other pressing issues, this fell off the radar. Now… It's back. Again, HELP (please).

[peterlaursen]

Now … quite some time has passed since version 5.21. This verson was one of the first Open Source releases and there were some what we call 'refactoring bugs' in there.

The memory of the radar has expired. A new image of the enemy has to be spotted! 🙂

We have to concentrate on the 6.x code tree! For the simple reason that if there was and still is an issue we cannot 'fix backwards'. Only in future versions we can!

Could you upgrade to latest 6.x build? Or install the 6.x trial? If both are not possible please create a ticket and we will find a solution!

[peg110]

peterlaursen wrote on Aug 22 2007, 03:40 PM:

Now … quite some time has passed since version 5.21. This verson was one of the first Open Source releases and there were some what we call 'refactoring bugs' in there.

The memory of the radar has expired. A new image of the enemy has to be spotted! 🙂

We have to concentrate on the 6.x code tree! For the simple reason that if there was and still is an issue we cannot 'fix backwards'. Only in future versions we can!

Could you upgrade to latest 6.x build? Or install the 6.x trial? If both are not possible please create a ticket and we will find a solution!

Well, I upgraded and it seems to work now. That is atleast for the FIRST time. (getting the structure and the initial round of data across.) As a side note, I did manage to get the 5.21 version to work as long as the user was NOT root.

Anyway, as I said I did get it to work with the 6.05 version. BUT after I tried to run it a subsequent time (to actually SYNC the databases), I got a lot of errors.

Code:

Sync started at Fri Aug 24 07:25:10 2007

`articles` 34 34 1st Phase : 0 0 1st Phase + 2nd Phase : 0 0 16
`articles_toc` 13 13 1st Phase : 0 0 1st Phase + 2nd Phase : 0 0 8
`pages` 4 4 1st Phase : 0 0 1st Phase + 2nd Phase : 0 0 4
`phpbb2_auth_access` 13 13 WARNING: No primary key found in table `phpbb2_auth_access`. This might result in error.1st Phase : 0 0 1st Phase + 2st Phase: 0 0 0
`phpbb2_banlist` 79 79 1st Phase : 0 0 1st Phase + 2nd Phase : 0 0 0
`phpbb2_categories` 5 5 1st Phase : 0 0 1st Phase + 2nd Phase : 0 0 0
`phpbb2_config` 61 61 Error No. 2013

Lost connection to MySQL server during query

1st Phase : 0 0

MySQL server has gone away – Could not get FIELD information for '`phpbb2_confirm`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_disallow`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_forum_prune`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_forums`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_groups`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_posts`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_posts_text`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_privmsgs`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_privmsgs_text`' in the SOURCE dtabase
MySQL server has gone away – Could not get FIELD information for '`phpbb2_ranks`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_search_results`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_search_wordlist`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_search_wordmatch`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_sessions`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_smilies`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_themes`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_themes_name`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_topics`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_topics_watch`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_user_group`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_users`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_vote_desc`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_vote_results`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_vote_voters`' in the SOURCE database
MySQL server has gone away – Could not get FIELD information for '`phpbb2_words`' in the SOURCE database

So now what?

[peterlaursen]

If you sync first time to an empty database, there is no real sync going on – only a copy process. Syncing to empty target and target with data is not comparable.

To me it simply looks as if connection was lost 2nd time you sync'ed! After that “SHOW FULL FIELDS FROM …” retrives nothing for every table that follows and the sync for every table will then have to be skipped.

[peg110]

peterlaursen wrote on Aug 24 2007, 08:11 AM:

If you sync first time to an empty database, there is no real sync going on – only a copy process. Syncing to empty target and target with data is not comparable.

To me it simply looks as if connection was lost 2nd time you sync'ed! After that “SHOW FULL FIELDS FROM …” retrives nothing for every table that follows and the sync for every table will then have to be skipped.

Ok, but WHY?

Why will it (everytime) Lose it's connection after the first (essentially) database copy?

Why will it not sync?

[peterlaursen]

Because it is a crap server, a crap connection or a crap program!

2-1 in favour of SQLyog! 😛

Jokes apart: please answer:

* Is it 100% consistently reproducable that sync always stops the very same place? it may then be a data specific error. But I have been using data sync with phpbb2 quite a lot about 1 year ago wihout problems. In the example you pasted 6 complete tables were sync'ed (actually they were all in sync!) – the error occured while sync'ing the 7th. Row counts were retrieved but not checksums it seems.

Now try only this table! Is the error consistenly reproducable with the `phpbb2_config` table?

* How do you connect? direct, SSH or HTTP tunnel? You never experience that you lose connection for instance when importing huge amounts of data? Running data sync over HTTP needs a reasonable stable HTTP connection!

[Author]

Posts