SJA does not work …

[peterlaursen]

I'm serious – SJA does not work when the PK is created from a varchar field.

In the actual case the field contain a complete windows filename such as “d:folder1folder2filenename.ext” .

🙁 🙁 🙁 🙁 🙁 🙁 🙁 🙁

It was a problem with my old 4.0-RC version. Thought it was a BETA-problem. But now that I have purchased the real stuff I see it's still the same. SJA starts, perfectly identifies the number of records in the each of the tables – and finishes in about 20 seconds (with a sync job that should last at last 30 minutes with my 128 kbit/sec OUT ADSL-line). It informs me that “Inserts 0 updates 0 deletes 0”. At least SJA's honest to that point!

Could it be an escape-character-problem (character “” as we know is an escape character in MySQL). That however is handled correctly by Sqlyog in case of importing a dump and other ordinary SQL-statements. But I cant see what commands are used with the SJA. Som REPLICATE command ??

It's is the same if I create a PK from more fields where the filname-field is one of them.

[Ritesh]

What is your source and target MySQL versions?

Can you send me sample data and table structure to reproduce the error?

[peterlaursen]

Both target and server are 4.0.24 in this case.

I'll send the data later – right now I'm unable to change the PK of that table since since there is a job running with the database …

[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.

[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

[Ritesh]

Very strange. I just worked on your issue and everything works as expected. I am running 4.1.7 on both source and target server. I created two tables as told by you. The two table data after inserting were:

and

There wont be one extra 1 as given in the insert into statement as 1 is an escape character.

I did a sync as you said and it worked correctly.

Then I changed the data as:

Ran the sync again and everything was fine.

[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 😀

[Ritesh]

I guess I have to open up my software archive and install v4.0.x and run the test case. Will do tomorrow first thing as I reach my office.

[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]

@ 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]

and these two

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

[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]

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)

[Ritesh]

peterlaursen wrote on May 23 2005, 01:42 PM:

@ 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

Well I know how to run different versions of MySQL on the same machine, its just that they are not running currently. So I have to install and configure it 😀

[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]

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

[Ritesh]

The above bug has been fixed in our 4.07 development tree.

[Author]

Posts