A problem has risen on my sja sync system – that by the way runs nicely.
I have a database with “order lines” that are created remotely and synced to the server once in a while. I have had that setup running for a while now.
All with primary keys nicely and so on.
I have some reports that the “order lines” are set back ie. the user alters the data on the server – and suddenly the data is reversed to is prior contents.
I have checked the sja xml files – and all states the server as the source and so on – so this is not what is happening. And this only occurs rarely.
Examining a specific happening gives me;
1) the line has been created remotely and synced to the server
2) 2 or 3 times the lines has been edited on the server
3) at a given time the sync runs on the remote – and sadly reverts the data on the server to the old remote version
And the problem is that this is the old data it somehow decides to update the server with. The data originates from my remote location. My problems is that it decided to update the server.
At this point it is correct that my server order line and remote order line are different – but it should not update the server. I am having no other sync problem.
I have 3 syncs running (attached);
A) “get from server” – the small and fast – oneway
I understand that this is the culprit of the problem: “I have some reports that the “order lines” are set back ie. the user alters the data on the server – and suddenly the data is reversed to is prior contents.” .
What indications do you have that this is caused by SJA? You can verify what SJA does by running SJA with the option to generate a sync script. I suggest that you for a while run sync with both options: direct sync + sync script. If this occurs again check the lastest sync script generated.
Do you need help to setup the XML job file with those options?
Frankly I rather believe that this user who claims that he edited data did not save his changes. I do no know what interface this user uses. It could be user's fault, a buggy client application as well as a transaction not committed (if transactions are used).
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?
yes – as soon as SJA has detected a row that should be UPDATED an UPDATE statement will be sent to target.
2) versions do no matter.
All this I do not understand:
“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 …..”
I think I will to ask for a more specific test case. Or at least please try to detail as much as you can. Too many words here but nothing we can use really. If ther is an issue we need to reproduce and debug. There is no other way to fix issues.
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…. ?
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?
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.
Please confirm that I understood. I will write an example/testcase in SQL:
Code:
CREATE DATABASE sbase;
USE sbase;
CREATE TABLE `onetable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`txt` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `onetable`(`id`,`txt`) VALUES (1,'x'),(2,'b'),(3,'c'),(4,'d');
CREATE DATABASE tbase;
USE tbase;
CREATE TABLE `onetable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`txt` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `onetable`(`id`,`txt`) VALUES (1,'a'),(2,'b'),(3,'c'),(5,'blah');
— This will (with a 2-way sync)
— * update row-1 from sbase to tbase
— * insert row-4 from sbase to tbase
— * insert row-5 from tbase to sbase
— and you want/expect it to work with the minimal privileges like here:
GRANT SELECT, INSERT ON sbase.* TO suser@localhost;
GRANT SELECT, INSERT, UPDATE ON tbase.* TO tuser@localhost;
“But in my case the target WILL update the source in the second run”
OK, so I understand with this particular setup you simply *want to* prevent changes on the (s) to write back toby managing privileges on. Right?
There is a 'abort on error' setting. What is your setting here – YES or NO? I think that NO will simply proceed to next statement (but still you will get an error recorded in the log for every UPDATE that failed).
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?
A two-way sync will never update from target to source except for the situation where data on target have changed in the time-slice between the two 'sync-rounds'. An that situation is what you try to prevent by denying UPDATE privilege. And that leads then to failures with INSERT?
I am sorry but this discussion is too 'wordy'. It would be better if you provided reproducible steps in terms of SQL.
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)
To reproduce and understand the problem (with my test case)
CREATE DATABASE sbase;
USE sbase;
CREATE TABLE `onetable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`txt` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `onetable`(`id`,`txt`) VALUES (1,'x'),(2,'b'),(3,'c'),(4,'d');
CREATE DATABASE tbase;
USE tbase;
CREATE TABLE `onetable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`txt` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `onetable`(`id`,`txt`) VALUES (1,'a'),(2,'b'),(3,'c'),(5,'blah');
— This will (with a 2-way sync)
— * update row-1 from sbase to tbase
— * insert row-4 from sbase to tbase
— * insert row-5 from tbase to sbase
— and you want/expect it to work with the minimal privileges like here:
GRANT SELECT, INSERT ON sbase.* TO suser@localhost;
GRANT SELECT, INSERT, UPDATE ON tbase.* TO tuser@localhost;
I frist run a one-way sync sbase>tbase (wiht “don't delete” option checked and using suser and tuser for conenction respectively). It inserts one row and updates one row on tbase. Now table in tbase looks like
id txt
1 x
2 b
3 c
4 d
5 blah
Next I UPDATE manually (simulating what happens on your environment) b >> y in 2nd row of table in tbase and run a 1-way sync in the other direction. SJA returns an error in the log:
Table SrcRows TgtRows Inserted Updated Deleted
========================= ======= ======= ======== ======= =======
`onetable` 5 4 There was an error while executing a query. The query and the error has been logged in the log file.
1 0 0
Total time taken – 0 sec(s)
This is happening with INSERTED what is pretty strange as nothing should prevent INSERT (I think it is logging the error the wrong place in this case and the error description is definitely not very good). But I think we understand you now and I will ask developers to debug exactly what happens. It may take some days before we can reply. Could you please confirm the validity and usability of my test case?
I think it is a reasonable request to to be able to skip INSERTS or UPDATES (just like we can now skip DELETES with a one-way sync). I would be little bit of a 'hack' I think to handle this with server-side privilege management – but maybe an acceptable workaround for now.
but note that it inserts one row and updates and deletes none.
Table in sbase looks like after sync
id txt
1 x
2 b
3 c
4 d
5 blah
(note that 2nd row is still 'b' and not 'y' here)
the sja.log has this ” UPDATE command denied to user 'suser'@'localhost' for table 'onetable' “.
So the only problem I see is a cosmetical issue that an error prints where it disturbs the output in the console. The server-side privileges prevent the operations you do not want and you get the expected result from the sync as far as I can see. What say?
Anyway we should check what exactly raises the message and consider a more descriptive error message (like “not all operations could be performed due to lack of privivleges”) after the tabular view.
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.
“somehow the INSERT gives an error when the user is not granted UPDATE”. I do not think it does. With my test case it does not. Only the console output is not clear (the error text occurs just below the INSERT in the header).
I think the reason why we print the error at once is that if SJA should abort or crash it may not be able to print it if it is saved in memory for later printing. But a developer has promised to look into code and find how and why the error is generated.
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.
andersbc
Memberpeterlaursen
Participant
