06-19-2009 09:44 AM - edited 06-19-2009 09:46 AM
Apologies if this is not etiquette but have copied thebelow thread from the news groups as it does not seem to be getting any attention and thought it may here.
I have since the below update managed to find a failed trans and locate the tef containg the failed trans and the transaction does just contact a select, we are seeing this issue mainly on the below tables but lots of them but obviously with different ID's.
The other thing I noticed from the TEF containing the failing transaction was the site was the syncserver rather than the host or other user sitecode also the globalID was C4L2 the activity leaders sitecode, where as all other transactions (that had a globalID) are in the format similar to A9J64A6001K3
I still don't however understand why it is syncing out select statements in the first place.
ERROR: ParamSQLUpd - Error applying ParamSQL transaction A0EU2J7 : List index out of bounds (0). SQL: SELECT STARTDATE,ALARMTIME FROM ACTIVITY WHERE ACTIVITYID = 'VC4L2A30003O' (Blob size : 608) Activity RelatedActivity Users = |UYYQKA00000X|Table Name = ACTIVITYParamCount:2Param0 = (varDate) 20/06/2009 08:00:00Param1 = (varDate) 19/06/2009 08:45:05 SQL = SELECT STARTDATE,ALARMTIME FROM ACTIVITY WHERE ACTIVITYID = 'VC4L2A30003O' ERROR: ParamSQLUpd - Error applying ParamSQL transaction A0ETE5F : List index out of bounds (0). SQL: SELECT MODIFYUSER,LASTHISTORYDATE,LASTHISTORYBY FROM ACCOUNT WHERE ACCOUNTID = 'ADJJWA100000' (Blob size : 741) Table Name = ACCOUNTParamCount:3Param0 = (varString) UYYQKA00000OParam1 = (varDate) 16/06/2009 12:24:45Param2 = (varString) UYYQKA00000O SQL = SELECT MODIFYUSER,LASTHISTORYDATE,LASTHISTORYBY FROM ACCOUNT WHERE ACCOUNTID = 'ADJJWA100000'
Any ideas anyone?
I guess the question is why are these selects being sent out to the remotes
when they will achieve nothing.
Anyone else seeing these errors in failed trans or elsewhere?
Sage any ideas on this?
"Trevor Walker" wrote in message
I have two separate customers having sync problems with SLX 7.5.1. One
uses SQL 2005 and the other uses SQL 2008. Both are getting errors like
those below on the server and the remotes. Multiple remote users have
these errors (actually the 2008 is getting it on both and I have only
verified the 2005 on a remote user so far).
For the SQL 2008 client, they should have the same 22.214.171.1242 version of
SyncClient and SyncServer. I haven't been able to verify on the remotes,
but the SLX install package sent for distribution to the users does
install the proper version (I just verified) and I don't know how they
could have installed anything else.
ParamSQLUpd - Error applying ParamSQL transaction A0YFOJS : List index out
of bounds (0). SQL: SELECT LONGNOTES FROM ACTIVITY WHERE ACTIVITYID =
ParamSQLUpd - Error applying ParamSQL transaction A0YDHUG : List index out
of bounds (0). SQL: SELECT MODIFYDATE,MODIFYUSER FROM CONTACT WHERE
CONTACTID = 'CAZPNA000JRT'
ParamSQLUpd - Error applying ParamSQL transaction A0Y60FX : List index out
of bounds (0). SQL: SELECT MODIFYUSER,MODIFYDATE,LASTHISTORYDATE FROM
OPPORTUNITY WHERE OPPORTUNITYID = 'OAZPNA000KKI'
ParamSQLUpd - Error applying ParamSQL transaction A0Y60FY : List index out
of bounds (0). SQL: SELECT MODIFYUSER,MODIFYDATE FROM ACCOUNT WHERE
ACCOUNTID = 'ADHDKA3000XE'
ParamSQLUpd - Error applying ParamSQL transaction A0YGIUE : List index out
of bounds (0). SQL: SELECT MODIFYDATE FROM OPPORTUNITY WHERE OPPORTUNITYID = 'OAZPNA000K1U'
Thanks for any assistance.
06-23-2009 01:22 AM
I managed to reproduce this over a 7.5.1 Eval, however this was just the once and I am unsure which series of events caused the issue but it did happen so appears the issue is there in Eval.
I will try and determine which series of events is causing the issue.
06-30-2009 09:36 AM - edited 06-30-2009 09:37 AM
I spoke with Sage and they had me check the primary keys on the tables on the remotes. For some reason when the remotes were cut all of the primary keys disappeared. I cut a test database and it seems to have the same primary keys as the main DB. I checked one of the few failed trans of this type that happened on the main DB and the one table it was referring to had no primary key.
The remotes are missing changes. According to Sage, if there are 20 changes within a TEF and the 2nd change fails, the remaining 18 will not apply. All of the TEFs that failed with this type of error on the remotes had a large number of changes in them unfortunately. Luckily it seems that this seldom happened on the main DB so recutting remotes, and verifying the primary keys remain, should fix this. I am still doing some testing.
07-01-2009 08:37 AM
Thanks for the update, I have checked on one of the affected remotes and the pimary keys are there so can't see this being the issue, at least in our case, additionally we are not having this problem against the host only remotes or at least I have not seen any from the host at this stage.
What does concern me is the second part of you post, that Sage advised if the tef contains 20 transactions and the second one fails the rest will not apply, I am sure this cannot be the case as virtually all remotes I have ever seen have failed trans at some point and if this is the case then there is a huge problem with data on the remotes.
If this is correct then something needs to be done about this ASAP.
Can someone from Sage chip in and clarify this.
What happens in the scenario where for example the second transaction of a twenty transaction tef fails?
07-07-2009 12:25 PM
This isue was caused by a defect in conflict resolution. The result was that SyncServer would send out a select statement to remotes instead of an update statement for transactions being returned to the remote. This would happen when two remotes had a conflict
The issue has been addressed in 7.5.1 Hot Fix 25
Defect # 1-69384
This is currently in customer validation, however if you have an immediate need you can contact support and ask for the "Customer Validation" version of the hot fix.
07-08-2009 12:51 AM
Many thanks for you update, I assume as there is a fix in validation you were able to reproduce the issue, are you able to give me the exact steps to reproduce, as I have been unable to determine the exact cause so have been unable to reproduce at will.
I assume there is a specific set of conflict resolution rules that play a part in this occurring, any information you can provide would be much appreciated.
In regard to the Hot Fix I assume I will be able to get the customer validation version via UK support?
07-08-2009 09:54 AM
The hot fix has just been validated and should be available for general download from support online in a few days.
I will get reproducable steps for you and repost today.
07-09-2009 03:28 PM
Steps to Reproduce:
1) Conflict resolution set to Remote wins
2) Two remotes make a change to the same record during the same sync cycle
3) The remote that fails conflict resolution will receive a transaction with the select statement.
That should be all that is necessary to reproduce the issue.