[peterlaursen]

another possibility is:

Get “MySQL Administrator” from http://www.mysql.com. It provides you with a lot useful information of the server and abitlities to change settings. But you must be able to connect directly to the MySQL port to use it. It offers no tunnelling options.

The two tools thast I wouldn't be without when running a local server is “MySQL Administrator” and “SQLyog”. There is a little bit of overlapping functions (user management, backup), by MA is particularlu usefull when working with the server environment, Sqlyog is particularly usefull when working with data.

[peterlaursen]

I don't know why this authentication error happened befor. Now I'm able to run SJA with “localhost” as one connection “my_global_ip” as the other with MySQL 5.04. That also results in erors but not quite the same as before. But the differences of behaviour could be due to different charset implementation og the different server versions ??

Starting with these data in both tables at source

ID mytext

---

---

1 dfdfdfdf

2 dfdfdfdf

3 dfdf\dfdf

and an empty target

********************************

First sync:

Sync started at Tue May 24 17:58:03 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 0 3 0 0

`numberindex` 3 0 3 0 0

Total time taken – 2 sec(s)

resulting in correct data for both tables at the target

ID mytext

---

---

1 dfdfdfdf

2 dfdfdfdf

3 dfdf\dfdf

**********************************

Then changing data at the source like

ID mytext

---

---

1 dfdfdfdf

2 dfdfhhdf

3 dfdf\jjdf

and sync'ing

Sync started at Tue May 24 18:02:02 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 3 0 0 2

`numberindex` 3 3 0 2 0

Total time taken – 3 sec(s)

Results in data i charindex-table (numberindex is of course correct):

ID mytext

---

---

1 dfdfdfdf

2 dfdfdfdf

3 dfdf\dfdf

PLEASE NOTE inconsistency: it says 2 deleted, but they are untouched!

*************************************

Next sync with NO change in source

Sync started at Tue May 24 18:06:33 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 3 0 0 2

`numberindex` 3 3 0 0 0

Total time taken – 3 sec(s)

Same inconsticency as above !!

************************************

Source data changed like

ID mytext

---

---

1 dfdfdfdf

2 dfdfhhdf

3 dfdfjjdf

Sync started at Tue May 24 18:10:58 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 3 2 0 2

`numberindex` 3 3 0 2 0

Total time taken – 3 sec(s)

results in data of charindex-table

ID mytext

---

---

1 dfdfdfdf

2 dfdfhhdf

2 dfdfdfdf

3 dfdfjjdf

3 dfdf\dfdf

[peterlaursen]

1) I'm not allowed to use ssh with one of the servers involved.

2) I want to work with a GUI-client – thats why I use SQLyog.

I perfectly know that SQLyog is a client. What I imagine is ANOTHER client running in the server environment and which can be CONTROLLED or at least INITIATED by Sqlyog

[peterlaursen]

@ sdpippin

Has your XP been installed “upon” a win-2K installation then ??

[peterlaursen]

I tried this “fake and fool” trick to run SJA from “localhost” to “my_global_ip” with MySQL ver 5.04. But that results in a MySQL server errer

Quote:

Error no. 1251.  Client does not support authetication protocol requested by server; consider upgrading MySQL client

… well I think I do that all the time .. 😀

But then unfortunately I'm only able to test with the MySQL version of my webhostin (4.0.24)

I can see that Ritesh has been working with this database of mine where I created an aoocunt for him at my webhosting.

The data in there look weird too …

[peterlaursen]

somethin disappeared – shoul read like

I set up a 1-way sync from “localhost” to ..

>>>

I set up a 1-way sync from “localhost” to “my_global_ip” (with a dyndns-subdomain)

[peterlaursen]

More infor now:

I set up a 1-way sync from “localhost” to , repeating every step as I did with my webhosting. That make one DB at my local server “source” – another DB at my local server “target” . Data exactly the same as before. No tunneling – direct connection.

1st sync

*******

sync to an empty table is OK (as before)

Below are excerpts from the log from the follwing sync's

2st sync

********

after changing data like

(1,'dfdfdfdf');

(2,'dfdfhhdf');

(3,'dfdf\jjdf');

Sync started at Mon May 23 18:30:47 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 3 0 0 2

`numberindex` 3 3 0 2 0

Total time taken – 8 sec(s)

3nd sync (without changing any data at all)

*********

Sync started at Mon May 23 18:32:41 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 1 1 0 1

`numberindex` 3 3 0 0 0

Total time taken – 9 sec(s)

4th sync (still without changing any data)

********

Sync started at Mon May 23 18:35:09 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 2 0 0 1

`numberindex` 3 3 0 0 0

Total time taken – 9 sec(s)

5th sync

********

after changing data like

(1,'dfdfdfdf');

(2,'dfdfhhdf');

(3,'dfdfjjdf');

Sync started at Mon May 23 18:37:17 2005

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`charindex` 3 2 2 0 1

`numberindex` 3 3 0 2 0

Total time taken – 9 sec(s)

And there are now 4 rows in table “charincex” just like before !! Completely the same!

Conclusion:

**********

1) it's not the fault of my webhost.

2) And not a tunnelling error either since this time it was with direct conn to port 3306.

3) It has NOTHING to do with PHP, since php isn't used here at all.

4) My best guess: (On the precondition that the test by Ritesh is done correctly and he didn't misunderstand the problem) some server version mismatch with the API or just a plain error in servercode.

When I'm able to switch to another MySQL-server-version tomorrow or wednesday I'll see what happens. 😡

[peterlaursen]

and these two

(of course you must have different datadir for each server version at least 4.x.x and 5.x.x)

[peterlaursen]

@ ritesh

you can easily have 4.0.x , 4.1.x and 5.0.x installed at the same time. With Mysql-Administrator it's a two minute job to change the server variables, stop the server and restart the server …

changes these 2 values

[peterlaursen]

@ ritesh

There never was any problems with table “numberindex” only with table “charindex”.

I used the “numberindex” -table only to illustrate that an integer-type-PK was OK, but a string-type-PK was not if it contained .

Hope you got that point ?

[peterlaursen]

yeah .. that's strange then.

My webhosting is running 4.0.24 and that's the only serverversion that I can use with SJA at the moment.

The only REAL one rather. I think can “fake and fool” the program with specifying localhost as one server and my global ip as the other and thus test with other server versions. But I won't be able to change serverversions until wednesday I think.

In must be a bug with the serverversion or maybe an error with the php-setup at my webhosting.

I'll ask Thomas Janum if that could be an error with the php-config at the webhosting. What he does not know about php isn't worth knowing…

Besides, I believe I found another solution. The Access database where the data come from has a PK made from a specially binary Access datatype (named 'replication type'). However with ODBC it can be converted to a 23 character string like '00AE4C1F-4F9E-476B-9447-8'. This one is OK as a PK for sync and should be as good as the filename.

thank you for working with the issue! I think you can't do more.

But if someone running 4.0.24 has the chance to test it would be very welcome 😀

[peterlaursen]

@ ritesh

There are a lot of big varchars and some mediumtext's – most of which are empty or only contain relatively few characters.

So your theory about BLOBs and the like could very well be true. But wil SJA have to “unfold” those variables. A varchar[255] containing the string 'abc' takes 4 bytes on the server – one “control byte” and three data bytes. Why calculate checksums of 255 bytes when there is only 3 bytes of data? Same argumentation goes for BLOBS. At least skip calculating checksums where data fields are empty! If any optimization like this is possible it should be implemented …

It's is more than 2 times as fast to do INSERT as UPDATE. Why ? Could it have something with the php-configuration at the server to do ?

[peterlaursen]

try to read through this one:

http://www.webyog.com/forums/index.php?act…02&hl=collation

If it is a problem of fonts displaying it could help as described to

“Issue a “SET NAMES charset_name” command from SQL window and query your database afterwards.”

If that is not the problem, you must be more specific in describing the problem. But there are som Caracter set -related issues with Sqlyog that are not solved yet (but should be in about a month's time with version 4.1.0)

[peterlaursen]

What I meant:

To illustrate the problem a very simple way create two bases with identical tables and data but different PK

>>>>

To illustrate the problem a very simple way create two TABLES with identical COLUMNS and data but different PK

[peterlaursen]

To illustrate the problem a very simple way create two bases with identical tables and data but different PK

*********************************************************

Table 1)

create database if not exists `test`;

USE `test`;

/*Table structure for table `numberindes` */

drop table if exists `numberindes`;

CREATE TABLE `numberindes` (

`numberfield` bigint(20) NOT NULL auto_increment,

`charfield` varchar(255) default NULL,

PRIMARY KEY (`numberfield`)

) TYPE=MyISAM;

/*Data for the table `numberindes` */

insert into `numberindes` values (1,'dfdfdfdf');

insert into `numberindes` values (2,'dfdf\dfdf');

insert into `numberindes` values (3,'dfdf\\dfdf');

(my comment: data are 'dfdfdfdf','dfdfdfdf' and 'dfdf\dfdf'in case you want to enter them from the keyboard)

********************************************************************************

********

Table 2)

create database if not exists `test`;

USE `test`;

/*Table structure for table `charindex` */

drop table if exists `charindex`;

CREATE TABLE `charindex` (

`numberfield` bigint(20) default NULL,

`charfield` varchar(255) NOT NULL default '',

PRIMARY KEY (`charfield`)

) TYPE=MyISAM;

/*Data for the table `charindex` */

insert into `charindex` values (1,'dfdfdfdf');

insert into `charindex` values (2,'dfdf\dfdf');

insert into `charindex` values (3,'dfdf\\dfdf');

(same comment as above)

********************************************************************************

**********

Create empty tables with identical structure and PK at another server, and run SJA as one way sync from the server with data in it with both tables. Both syncronize (in this case)

********************************************************************************

*************

Now change the data at the source to

(1,'dfdfdfdf');

(2,'dfdfhhdf');

(3,'dfdf\jjdf');

and run SJA – the two rows with and \ in the data are deleted.

Also try rerun SJA a couple of times and it seems quite unpredictable! Sometimes it inserts, sometimes it deletes sometimes it does nothing. But it never updates!

*********************************************************

Now delete the backslashes from the data so that they are

(1,'dfdfdfdf');

(2,'dfdfhhdf');

(3,'dfdfjjdf');

and run SJA – and you'll get this funny result (se picture):

*********************************************************

Of course the table with the number-PK is always correct. As long as there has never been backslashes in the varchar-field at sync-time it also seems to work with the varchar-field as the PK.

**********************************************************

I'm running this from my local machine to my webhosting where I'm connecting with HTTP-tunnelling. I can give you access to both If you can't reproduce the problem otherwise.

[Author]

Posts