error 500 when synchronizing

Discussions about syncing data via Bluetooth, Dropbox, FTP, and using CSWeb
Forum rules
New release: CSPro 8.0
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

By the way, by looking at the sync_history, what are the fields file_revision, time_stamp and server_revision?
How can I convert the time_stamp to an actual date?
Best
Ari
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: error 500 when synchronizing

Post by josh »

Ari - I feel like we had this problem where some cases were not getting uploaded before with a different one of your surveys but I can't remember if or how you solved it.

Timestamp is a standard unix timestamp - seconds since Jan 1, 1970. This website converts back and forth https://www.epochconverter.com. You can also convert it using sqlite queries: https://stackoverflow.com/questions/343 ... formatting or using CSPro logic https://www.csprousers.org/help/CSPro/t ... ction.html.

File revision is a link to the id column of the file_revisions table in the csdb. Each case in the cases table also has a file revision number (last_modified_revision) which links to the id of the file_revisions table and indicates when that case was last added/modified. When uploading cases we upload only those cases who's last_modified_revision is greater than the file_revision from the last sync_history entry with the same server. So what Aaron is saying is that if you delete all the rows in the sync history table in a csdb file the next time you upload it will upload all the cases instead of just the new/modified cases.

Server revision is the revision number from the sync_history table in the CSWeb database. When we upload cases to the server the server adds a new entry into its sync_history table and sends back the id of that new row. When uploading the next time we send that server revision to the server and it checks to make sure it has that revision in it's sync_history table. If it does not have it then it tells CSPro to upload all the cases instead of just the new/modified cases. This means that you if you clear the sync_history table on the server the next time you upload from any device it will upload all the cases.
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

Hi Josh,
I do not remember having this kind of a problem before with one of our surveys.
I mean, not updating the server, from a client.
Anyway, as you explained on your note, I could delete all the sync_history data in the server to be able to force everybody to upload everything the next time they do a put, is that correct?
Is it safe to do so?
Can I do it anytime, or I should wait for a period of time when nobody is accessing the server? (at night for example).
Best
Ari
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

Instead of deleting the sync_history from the client database, I´ve tried to simulate it by executing a cspro batch program to read/write the file, generating an output file. As I saw it in the SQLite, the sync_history of this output file is empty, of course.
Then I tried to upload this output file into the server, and, I do not know why, it did not update the server!
Even with the empty sync_history!
What am I doing wrong?
Best
Ari
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: error 500 when synchronizing

Post by josh »

I was thinking of this exchange viewtopic.php?f=11&t=3993&p=12418#p12418 but I don't see any resolution.

Are there cases in the csdb file that are not already on the server based on the case UUID? If you the cases in the csdb are already on the server (server has a case with same UUID) then the ones in the csdb may not overwrite the ones on the server. When you look at the sync.log on the client how many cases does it say it uploaded?
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

Follows the sync.log data
2020-08-02 15:15:22,140 INFO: Connect to server http://www.2clv.com.br/csweb74/api/ using saved credentials
2020-08-02 15:15:22,140 INFO: Connect to server http://www.2clv.com.br/csweb74/api/
2020-08-02 15:15:22,760 INFO: Server API version: 1.1
2020-08-02 15:15:22,761 INFO: Connection successful. Server id: 852995f1-c6b3-4bae-8f1a-14952401c8e5
2020-08-02 15:15:22,867 INFO: Syncing data: COVID_ELSI2_10_Entrevista.csdb direction PUT universe ""
2020-08-02 15:15:22,867 INFO: First time PUT with this server
2020-08-02 15:15:22,868 INFO: Total new/modified cases since last sync: 2
2020-08-02 15:15:23,352 INFO: Chunk time Seconds : 0.481644 Cases: 2 Kb: 6.01 mbps 0.0998248
2020-08-02 15:15:23,352 INFO: Uploaded chunk of 2 cases
2020-08-02 15:15:23,548 INFO: New server revision = 27172
2020-08-02 15:15:23,548 INFO: Sync PUT completed.
2020-08-02 15:15:23,548 INFO: Uploaded 2 cases
2020-08-02 15:15:23,688 INFO: Disconnecting from server

My test file has just 2 cases.
I look at the server data after downloading it and the data in the server is the old one, the change is not reflected there.
The uuids are the same in the test case and the server.
What you are saying is that if the uuids are the same the software does not update the server?
Even if you change the data in the client for that specific case, it is not updated in the server?
I always understood that if data is changed in the client the server is updated accordingly.
How come we always worked with that kind of assumption and it never happened before?
Did you change this behavior from 7.3 to 7.4?
What do we have to do to update the data in te server?

Do you mind to have a look at my files in the server and in the test case?
What should I send you?
a. the server´s direction (in the sync.log)

test file attached covid_elsi2_10_entrevista.csdb
case id: 00172

to see any change look at field w12_observacao, the server has "ari - nao atualiza o servidor"
the test data has nothing in this field. w10_observacao is also different in the server and the test
Attachments
COVID_ELSI2_10_Entrevista.zip
(15.35 KiB) Downloaded 171 times
Best
Ari
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: error 500 when synchronizing

Post by josh »

The case on the server is not always updated even when the uuid is the same. We also use the vector_clock to determine whether or not the server or the client has the more up to date version of the case. This comes into play only when you have multiple different devices modifying the same case. The idea is to prevent a situation where tablet A and tablet B both have the same version of the case say because tablet A synced directly with tablet B, then tablet A makes a modification and uploads the case to the server and later on tablet B also uploads to the server but has the original version of the case. In that scenario you want to keep the version of the case that was uploaded by table A since it is was more recently modified and reject the old version from tablet B.

The vector is a list of the number of times the case has been modified by each of the devices that have modified it. So it is a basically a table of device id and a number of times modified. When the case is uploaded to the server, CSWeb compares the vector clock that it has with the vector clock of the uploaded case and if the server vector clock is newer then it does not modify the case.

This appears to be what is happening in your case. If I look at case with uuid 2d6b05c2-e060-4648-a83b-3c31388c8e95 for example the vector clock on the server is:

0a0027000006 3
c0f8da2f25ff 8
fcaa14309422 1

and the vector clock in the csdb that you are uploading is:

0a0027000006 1
c0f8da2f25ff 8

This means that the version on the server was modified 3 times by device 0a0027000006, 8 times by device c0f8da2f25ff and once by device fcaa14309422 while the version you are updating was modified only once by device 0a0027000006 and never by device fcaa14309422. So to CSWeb the version you are trying to upload is out of date and is ignored.

All of that is probably a lot more detail than you wanted to know but the bottom line is that if you have multiple users modifying the same case then to avoid having conflicting edits you should always download the latest version of the case from the server before making local modifications. When you download the server version of the case you make a copy of the vector clock and then add your revision to the vector clock. That way when you upload the modified version of case then it uploads with a newer vector clock and will overwrite what is on the server.
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

Thanks Josh.
I think I understood all of what you-ve written about the uuids and timestamps, and the dates.
And maybe this is the reason why I could not update this specific case. The reason I tried to do that was that the interviewer (some of them) were not updating the server properly.
That is, a single interviewer working with a single case (at the beginning, before I entered in the game), s/he did part of the interview, upload the server, then came back and did the last part of the interview, upload it, but that final part was not recorded in the server.
The case key is composed of the person id (5 digits) and the social security number of the interviewer (here it is called CPF, 11 digits), and we did that specifically to avoid this kind of duplication.
No matter what the interviewer does with this particular case that it cannot be updated. This is happening with some interviewers and with some cases (not all of the interviewers and not all of their cases). Coincidentally, this started to happen when we increased the memory on the server (I know, shouldn´t be it), but it very strange, we are working with this kind of client/server environment a very long time, and it never occurred before.
The solution to edit the sync_history on their machines is too risky for them to do that themselves, besides the fact that, even if I do that myself using TeamViewer I would need first to install an SQLLite tool to delete the sync_history.
Does it have any other way to "force" the server to accept the changes?
The alternative I´m envisaging is to bring their dbs to my machine, and, using a cspro batch program, to select those cases, copying them and concatenate those files with the data coming from the server. Do you have any other idea?
Best
Ari
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: error 500 when synchronizing

Post by josh »

If they open the case on their machine, change a value and then change it back again to make CSPro think the case was modified, and then sync that will make their version of the case newer and it will be uploaded. Once they modify the case on their machine it will increment the number of changes in the vector clock so CSWeb will consider it newer and will accept it. To make sure that CSPro marks the case as changed you may need to have them change a value, move to the next field, go back to the previous field, change it back and then save the case.

The limitation of the above is that it means manually updating a lot of cases. An alternative is to clear the sync history and the clock on the server which will force a full upload for all the users on the next sync. You could do this with the following queries on the database on the server:

Code: Select all

UPDATE MYDICT_DICT SET clock='[]'

Code: Select all

DELETE FROM cspro_sync_history where dictionary_id = (SELECT id FROM cspro_dictionaries WHERE dictionary_name = 'MYDICT_DICT')
where MYDICT_DICT is the name of the dictionary.

As whenever you run delete queries on the server you should probably make a backup of the database first just in case.

After running those queries, the next time any user uploads to the server it will overwrite any existing cases with the uploaded values.
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: error 500 when synchronizing

Post by AriSilva »

Thanks Josh,
My alternative would be deleting the dictionary in the server (after downloading it, of course). This would force all he clients to "update" the db.
Yours is a much better (and cleaner) solution.
I´ll ask Carlos to do that, and if it does not work for whatever reason, I´ll apply my plan B.
Best
Ari
Post Reply