Appid: 9359F741-6D5D-AEF2-FF22-4834A2050400
Page in question “Coal_sample_data_transformation”
Code in question: “set location data relationships “ button.
I am iterating through a table to set a 1 to1 relation between that table and another. I am following this example on how to traverse more than 100 rows.
When the code completed, I noticed there were still null values left in the table, so I added a lot of logging.
I noticed as I iterate through the pages( see code below) I can see the number of rows being returned starts at 100 (which it should be until the very last page ) but at some point rows returned becomes 0 long before we get to the last page.
log example 1
start set location relationships
rowcount 11501
number of 100 row pages 116
offset 0
rows returned from load table objects 100
setting location relationship for W229095
here is the log from the very first page (offset 0) and you can see 100 objects were retrieved.
then I output the sample id being updated.
log example 2
offset 5800
rows returned from load table objects 0
here you can see the number of returned rows is zero which should not happen as I am on page 58 (of 100 records and I know there are 116 total pages ). the number of objects returned is 0 for the rest of my loop.
log example 3
set relationships complete.
elapsed time 41.6239 minutes
imported 5794 of 11501 records
so I can see here just over half of the records got updated. So it appears the app is loosing its connection to Backendless.
Let me know if there is any other information I can provide to penpoint the issue.
Thanks,
H
I have included some of the code(less) here for reference
Thank you for providing the details. Let me summarize the issue:
you load the total count of objects with the query location_data is null // receive 11501
then you load 100 objects with the same query location_data is null
for each record of the 100 items you assign relation location_data
then you load the next 100 objects from the 11501 records with the same query location_data is null
and finally when you updated half of the objects you started getting zero objects
Is that correct?
If the answer is yes see the following explanation:
you loaded the count of N objects with the query location_data is null
you loaded 100 objects from N
after assigning a relation location_data on the server the N is changed and now the total count is N-1
See the example:
you loaded 1000 objects where age < 30
you get the first 100 and change age=50, it means in the DataBase there are only 900 objects where age < 30
when you change offset to 100 you load from 100 to 200 of 900 and you set age=50 again
when you have done 5 iterations on the server remain only 500 records where age < 30 but your offset will be 500 and as a result you receive zero
if your main idea is to set a relationship for each of the 11501 where location_data is null I can recommend you always use offset=0 and in that case, you will continue to iterate until you go through each record without the relationship
Thanks for the response! I"m going to implement your changes and i’ll update with results.
one question (because I want to avoid the same mistake) my logic above would have been ok if I had left off the “where” clause correct? That is I would have walked through each row of the table. or is there a better way to walk through every row in a table ( with more than 100 rows. say a few thousand )
if you retrieve objects with the following query “relationColumn is null” and then establish a relationship for every single record the answer is yes, you can use the same query in each iteration.
but if there is a condition before assigning a relation and you know not all records receive its own relation it won’t work
btw, do you assign unique relations for each record? or this is a relation to the same object?
if you need to add the same relation for all records you can use the following API with one API Call:
I have two tables say A and B I was watching a Backendless video and Mark was saying if you have a many to one relationship from A to B you might be able instead to set up a 1 to 1 relationship if you go from B to A. It seems to me a 1 to 1 is easer maintain and I realized if I did it that way I could run all my queries against a single table and just use the relationships to get other fields I needed. so that’s what I’m doing. so all relationship fields in table B point to a single unique field in table A though there are multiple rows in B pointing to the same row of A.
also, thanks again for your explanation, once I looked at it I could see exactly what you were talking about. So I removed the offset ( that truly was a facepalm moment for me ) and let it run. It did however fail to complete. I took a look this morning and it appeared hung.
It showed that 100 rows were returned and it stared to work through the list logging each sample id as it went and in the middle it just stopped. it did not complete the loop it did not give an error and now I’m feeling extra stup0id because I should have put the set relationship in a into a try block to capture any errors, but since it never exited the loop and had not gone through its 100 rows that it did pull I don’t think the set relationship failure would have broken the entire loop.
I went ahead and restarted the process and it reported there were many rows that still were null so I know it really didn’t finish. and now I’m mad at myself because while I added logging for start/finish I have none for while its running so I cant say when it died, just that it was a long running job that got hung (no updates) overnight.
