[andersbc]

Hi Peter,

I will.

The only thing I can say 100% sure is that i have run my setup with only INSERT and SELECT granted to the source – and I have records not being inserted.

I'll search logs and make a testcase….

Thanks!

Anders

[andersbc]

Hi Peter,

Ill check your case. Though it seems ok.

Basically somehow the INSERT gives an error when the user is not granted UPDATE. This fits with my earlier experience of rows not showing – that only should have been inserted.

Thanks!

Anders

[andersbc]

Hi Peter,

Yes exactly.

I did try to run this for a while with only “INSERT and SELECT” granted from the target to the source – but i got a lot of records not INSERTED from the target. It seems that an INSERT consists of an “INSERT and latter UPDATE”? (when running from the target to the source).

The second I granted UPDATE – the missing data from the target to the source was visible.

It hard to make sql's. Basically the problem only occurs if you have a massive set of data and/or a slow line to sync. The slower the line and the more data; the more often i occurs.

It would be nice to not allow a target remote system have UPDATE access to the source – this would keep a nice integrity of the source.

Could I solve this by doing a,

a) one way sync from source to target (with UPDATE and INSERT granted on the target)

😎 one way sync from target to source (with INSERT only granted on the source)

– or is that simply the two way sync?

Or any other way I could solve this?

Thanks!

Anders

[andersbc]

Hi Peter,

The abort on error is “no”.

I did try to resolve it this way – but if the sync user only has SELECT and INSERT access to the source – I still get in trouble with rows not being inserted into the source from the target (I don't know why).

Do we agree upon the fact that the target should never be able to update rows on the source – only insert rows?

Anders

[andersbc]

Hi Peter,

Yes – of two reasons.

A) I do not want my remote locations to have UPDATE access to my table(s). This keeps the integrity nice.

😎 It would be a “workaround” for the above issue.

As i understand the twoway these process occurs;

– the source will UPDATE and INSERT to the target (first run of the twoway sync)

– the target will only INSERT to the source (second run)

But in my case the target WILL update the source in the second run (especially if the sync takes a long time for one db)

Anders

[andersbc]

Hi Peter,

I thing I can explain what happens now. Ill try to explain nicely.

Its a two way setup with a source and a target specified – see attached pdf.

In a two way setup the source-target is reversed in the second sync. But what happens if the data is changed between the first and second sync? then it is synced back to the data from the first sync.

This is what I experience.

Anders

[andersbc]

Hi Peter,

Yes – or I didn't explain well.

I am running a system where remote locations sync. with a server. The remote location runs the sja as a two way sync. The server is set as SOURCE and the remote locations as TARGET.

The result of this is; the SOURCE will be able to ADD and CORRECT data when missing or there are differences (hence able to UPDATE and INSERT). This is correct.

Then the TARGET cannot CORRECT the SOURCE but only ADD (hence not UPDATE only INSERT).

My question is the; the user I use for access to the mysql SOURCE needs (I tested this) SELECT, UPDATE and INSERT. It seems logical that it only needs SELECT and INSERT? but why the UPDATE?

Anders

[andersbc]

Hi Peter,

Ill try to identify some test data. The problem is that it only occurs once in a while.

A question though: I tried to only grant the user (mysql) for the sja tool to: INSERT, SELECT and INDEX. It seem to work in the beginning. But now I have syncs that do not work correctly. I introduce UPDATE on the users grant list – and the data comes back.

So the sja tool will detect a change, then INSERT and simple version of the line and then use the UPDATE to insert the rest?

I would rather, only grant INSERT rights to the user – than INSERT and UPDATE…. ?

Anders

[andersbc]

Hi Peter,

The system is built so that you enter a “edit page” – press save – the system updates the information and presents the user with the result. So the user actually edits the data – on the server. After that, somehow, the sja updates with the remote old version.

I can see in the mysql log files – that I have generated, that; the user correctly updates the data on the server, views it a couple of times, then a large set of updates occurs – SQL lines from the sja tool. That updates the record with the old version. Then the user discovers this – and updates his record from menory again – and the all is fine. The problem will not arise again.

As I see it – my xml files only allow updates; server->remote and inserts:server<->remote. Because i run with unique PK – and, as I understand, if a record exists on both sides, and are different – the server will rule.

That i my problem; how can the remote then update something on the server. It knows it exists on the server – because it updates with a ” WHERE …..”

The strange thing is that this occurs randomly and rarely. If I increase the amount of syncs jobs over time; I get more of them.

Just some questions to narrow down the problem;

When the sja syncs a record – it makes a checksum – and updates directly after if necessary? (not after a bunch of checksums or so) The time between checksum and update is tiny?

The mysql server versions does not match 100% on server and remote – but they are 5.X – is that a problem?

[andersbc]

Hi,

Sorry for the late reply on your fine and fast anwser.

Can´t I email you? i think you would need my full data dumps – and they are over 2mb…

Anders

[andersbc]

Hi Peter,

It is because i get the “Duplicate Key Error With Where Clause” error in my setup. I haven't done a lot of debugging – and the pre 6.X release worked in my test environment to the full.

I can try to replicate my problem with data for you?

I can make a full restore to my slave and when i use the sja7 it will sync nicely – if i then change some data that “looks” alike ie. has small changes in content – it will fail the the “Duplicate Key Error”.

On the top of my head I guess that it is the cheksum that isn't unique enough….

Thanks a lot for the 6.X 😉

Anders

[andersbc]

Hi Peter,

Thanks for the quick reply. The download link http://webyog.com/downloads/oldies/sja656.tar.gz – is no good… it gives me an archive on 328kb not extracting.

I would much appreciate a linux sja version prior til 7.X…. maybe at: ac”at”noren.dk

Anyway the sja should give an error of some kind.

Anders

[andersbc]

Hi Peter,

Did some digging – and found the problem.

If I alter the “localhost” part – to “127.0.0.1” – it runs!

7.15

Do you have a linux sja version prior to 7.X?

Anders

[andersbc]

Hi,

I also had this problem and reverted to 6.XX version of the sja.exe i found.

It seemed to come mainly, in my database, when two records were added close to each other in time (with a datetime adding in a field). So a row that looks the same but have seconds or so difference in the datetime filed generated this for me.

my 15 cents…

Anders

[andersbc]

Hi Peter,

No problem.

Forgot to note that the job file works with the windows version ie. sja.exe.

Anders

[Author]

Posts