Rare Intermittent error with foundsets

[dpearce]

Hi,

I have a routine for syncing an offline client with my mysql database. Based on a date_modified field, I compare records since the last update and then update then by looping through each column.

It works great, except that once in a while we get records where it has overwritten the local record with another record from the server. I have been trying to narrow this down and before this happens (on the sync before) I think we always get a strange error message and then on the next sync the issue then seems to happen.

Firstly the error message is a NullPointer Exception.

Code: Select all

Time   Thread   Level   Category   Message
2011-03-16 07:34   AWT-EventQueue-0   ERROR   com.servoy.j2db.util.Debug   Throwable
java.lang.NullPointerException
       at com.servoy.j2db.dataprocessing.FoundSet.int(Unknown Source)
       at com.servoy.j2db.dataprocessing.FoundSet.for(Unknown Source)
       at com.servoy.j2db.dataprocessing.FoundSet.getRecord(Unknown Source)
       at com.servoy.j2db.dataprocessing.FoundSet.has(Unknown Source)
       at com.servoy.j2db.dataprocessing.FoundSet.get(Unknown Source)
       at org.mozilla.javascript.ScriptRuntime.getProp(ScriptRuntime.java:726)
       at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1904)
       at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
       at com.servoy.j2db.scripting.e.call(Unknown Source)
       at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1254)
       at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2031)
       at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
       at com.servoy.j2db.scripting.e.call(Unknown Source)
       at com.servoy.j2db.develop.debugger.k.a(Unknown Source)
       at com.servoy.j2db.develop.debugger.k.access$100(Unknown Source)
       at com.servoy.j2db.develop.debugger.k$2.run(Unknown Source)
       at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
       at java.awt.EventQueue.dispatchEvent(EventQueue.java:633)
       at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:296)
       at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:211)
       at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:201)
       at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:196)
       at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:188)
       at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

My code is to make sure the local record wants to be synced and then to load the local and remote records into a foundset and then sync them column by column in a loop. So i have now trapped the error to make sure that the id's are the same once loaded. Ignoring the complexity of which record i want to sync, I have trapped this within an if statement to prevent the issue:

Code: Select all

   //Load the remote record into the remote foundset
      remotefs.loadRecords("select id from so_appointments where id=?", [dataset.getValue(i,1)])
      var success=databaseManager.refreshRecordFromDatabase(remotefs,1)
      //New Records first
      if (dataset_local.getMaxRowIndex()<1)
      {
      localfs.newRecord(false,true)
      localfs.grund='New'
      databaseManager.saveData()
      localfs.id=dataset.getValue(i,1)
      application.output('New Record: '+localfs.id)
      newrec=1
      }
      else
      {
      localfs.loadRecords("select id from so_appointments where id=?", [dataset.getValue(i,1)])

      }
      
      if (localfs.id == remotefs.id)
      
      {
         //Check for conflicts
         if (localfs.rw_report_status !='Force Download' && newrec==0 && (localfs.rw_final_report !=null && remotefs.rw_final_report ==null) || (localfs.rw_admin_idtype !=null && remotefs.rw_admin_idtype ==null))
         {
         localfs.rw_report_status='Syncronisation Conflict'
         application.output ('Conflict: '+localfs.id)
         globals.syncconflict +=1
         databaseManager.saveData()
         
         }
      
         else
         {
   
         application.output ('Remote FS ID: '+remotefs.more_1+'-'+remotefs.id+'  Local ID: '+localfs.more_1+'-'+localfs.id)
         //Now lets loop through the column names and update all except the ID
            for ( var j = 0 ; j <localCN.length  ; j++ )
            {
   
            
            if ((localCN[j] !='id' && remotefs[localCN[j]] !=null && remotefs[localCN[j]] !='') || remotefs[localCN[j]] ==0)
            {localfs[localCN[j]]=remotefs[localCN[j]]}
         
            }
   
            databaseManager.saveData()
   
      
            
         var date_mod = utils.dateFormat(remotefs.date_modified, 'yyyy-MM-dd HH:mm:ss')
         var localid=localfs.id

         var done = plugins.rawSQL.executeSQL("reportwriter","so_appointments","update so_appointments set date_modified = ? where id= ?", [date_mod,localid])
            if (done)
            {
            //flush is required when changes are made in db
            plugins.rawSQL.flushAllClientsCache("reportwriter","so_appointments")
            }
      
         }
      }
      
      else
      {
      application.output ('Error: Local-'+localfs.more_1+'-'+localfs.id+' Remote-'+remotefs.more_1+'-'+remotefs.id)
      }
   }
   

So I just trapped one record following the sync with the error in the following console output:

Code: Select all

Remote FS ID: Hill-606278  Local ID: Hill-606278
Remote FS ID: Hill-606277  Local ID: Hill-606277
Remote FS ID: McGuigan-599855  Local ID: McGuigan-599855
Error: Local-McGuigan-599855 Remote-Boardman-599856
Remote FS ID: Wake-599859  Local ID: Wake-599859
Remote FS ID: Johal-599465  Local ID: Johal-599465


You can see that despite successfully syncing the McGuigan Record, the next record in the loop failed to load in the new "Boardman" local record.

This is happening maybe on one or two records each week, so it is a rare error, but leads to the loss of crucial data for my customers.

I guess I have at least now managed to trap it and work out what on earth was causing the issue, but it appears very intermittent and is based an Java error which halts the sync and then when you do a new sync you get this bizarre occasional records not being loaded locally.

The next time I run the sync (that is if I then move the date modified forward on the server manually, as my date last modified for the solution has then changed) it works fine without restarting.

This is servoy 3.5.12.

Any clues or suggestions as to how i might correct this behaviour, or is it a question of maybe loop through the load record again maybe 3 times to see if it can be corrected on the fly within the script if the id's are not the same. I dont want to get into a blind loop i guess, but I suppose i could just try 3 times and if it isnt working abort the sync and try again, having not changed the date_solution_last_synced which i use to limit the overall sync.

David

[jcompagner]

can you try with the latest 5.2 release?

A null pointer shouldn't happen in what you see there, but it could be that that is long fixed in a 4 or 5 release.

[dpearce]

Johan,

yes, the problem is it is a very intermittent error, but obviously with 20-30 of our doctors using the "Offline" Solution, it is problematic if records get overwritten with other peoples data.

The main issue is that, I think a move to Servoy 4/5 for an "Offline" solution is much harder to implement, install and keep going compared to servoy 3.5 where you can just turn the developer stuff off.

It may be my fault, with all of my exposure to Sanneke's programming techniques over the last 6 months, I was doing these syncs using two forms and loading the records onto the forms, but i decided to be a bit neater and use foundsets. I think this is when this rare error started.

I guess this may be fixed, but as it is hard to reproduce, only effecting me maybe once every 10 days, that It is not worth recoding it in 4 or 5.

I was thinking either to go back to using two forms, or loading the records, doing the comparison and then having a loop of 3 attempts to load the record, otherwise to abort the sync, leaving the date last synced as the old date and telling my users to sync again.

I did error trap the error and it was at the line comparing the localfs.id to the remotefs.id, but in the console there were ids coming up which were the same. However the actual mismatch then happened on the next sync where it did not give a java error, but came obviosuly failed to load in the same record into the foundset, leaving the previous record.

If there is nothing obvious, then I think that a workaround to stop wrong records being synced, maybe with a loop of three goes to try and load in that local record or abort and try again may be the answer.

David