Error 106 Frustration

[DCA]

We are so close to going live with this system, but we've struck a brick wall and need advice. First some background:

 

The original system was built several years ago and has 50+ tables. Only 31 of those need to be synced to the mobile version of the file, which will be loaded onto iPad's to take into the field. Of those 31 tables, only half contain data which will be modified in the mobile version. The resat are mainly resource tables. We managed to get the sync process working perfectly on copies of the main file, but it refuses to sync to the actual file.

 

It's extremely frustrating because the copy it will sync to, is an exact backup copy of the database. But, when I take a copy of the mobile file, change the external data source reference to the live file, it always returns an error 106. I have painstakingly checked every single one of the 31 relationships in both files at least 10 times and it still won't work. I even started switching off individual relationships in both files (renaming the TO's in the process) as part of a process of elimination, but each sync was taking several hours, before stopping with an error 106. I gave up after about five attempts and no success.

 

I'm at a loss, my client is becoming angry at the delay and I can't think what else to try. Any and all advice is most welcome.

 

Marc

[DCA]

Let my rephrase my question. Is there any way I can get FM EasySync to show me the name of the table it's looking for when it generates the 106 error? Knowing that will greatly simplify my troubleshooting process (which has now lasted two weeks).

[bcooney]

Can't you step thru the process in debugger and see the where the error occurs?

[GisMo]

Enable Push Debug in the settings on the local file. it will show you the table and fields being synced. Double check all of your ES table relationships and ES fields...I had a mistake where ES_Exclude was set as text and not a number and it was not working until I made the fix. 

[DCA]

This may turn out to be premature, but I think I may have identified the cause of the problem and inadvertently fixed it. Thanks largely to the advice offered by GisMo, I turned on the push and pull debug options. As painful as it was dismissing four separate dialogs for every one of 1,284 records, it did prove where it was failing. The problem was with the main Projects table. It would have been the last one I would have switched off in my process of elimination. Whereas the dialogs for all the other tables showed the correct table name eg ES_TableName1, ES_TableName2 etc., the one for ES_Projects appeared in the dialog as ?[[bR]]ES_Projects.

 

Here's where it gets a little weird. In the process of disabling all of the relationships except for ES_Projects and the one before it (which only had 24 records to sync), I deleted the table name ES_Projects and retyped it in exactly the same way. When I ran it again, the sync process worked perfectly. I can only assume that the TO name had somehow become corrupted. By re-typing the name in, it corrected it.

 

I've now switched back on all 31 relationships and am in the process of running it. It will take several hours, so I'll report back on the final result tomorrow.

 

Thanks for the advice.

Marc

[GisMo]

Maybe you did some copy pasting of tables and renaming and somehow that boogered up the names internally with filemaker. Glad you figured it out! What you did was a good way to debug, is to try one table at a time..This isolates your issue quickly!..as long as your data to push/pull from that table is not a lot it would go quick

[DCA]

Finally got it working. Renaming the TO solved the problem. It took several days to do the initial sync though. Slow and unreliable internet and loads of data in the initial sync meant having to make several attempts. I ended up pre-loading the data and then syncing and it's now all good and shipped off to the client to download and install on their iPad's.

 

Feeling very relieved. Thanks for the help guys.

 

Marc

[DCA]

My sync process is finally working. I managed to identify the cause of the problem and found a workaround. Unfortunately, I still haven’t found the cause or a solution, just a workaround. As I’ve always said when it comes to anything FileMaker related, where there’s a need, there’s a workaround. So, here’s my journey and I hope that if this ever rears its’ ugly head to anyone else, this post will help diagnose and solve the problem.

 

Whenever I ran the sync process in my mobile file, it would always fail with an error 106 (Table missing). I had thoroughly checked every single TO and relationship in both files literally hundreds of times and everything was exactly as it should be.

 

Eventually, I ran the script with the debug pull payload option switched on. If you’re ever tried that, you’ll know how painful it is. After hitting the enter key several thousand times (four times per record), it eventually stopped, allowing me to see the problem. The primary table in the process was using a TO named ES_Projects, however the script was showing it as ?[[bR]]ES_Projects. I have no idea what the ?[[bR]] prefix means or what caused it. At least I knew where the problem was occurring. I deleted the TO’s in both files and replaced them with new ones and even renamed them to ES_PROJ.

 

I ran the sync script again and it failed yet again with a 106 error. The difference this time is that it had processed more records than the previous attempt. Working backwards through the list of TO’s and manually finding the number of records to be processed, I identified that the problem was with the next table after the projects one. I disabled the relationship in both files and renamed the TO’s and ran it again. The same problem occurred, this time with the next table. The conclusion I came to was that the sync process would get to a certain point and then fail. If I disabled the process at the fault, it would simply move the problem to the next table.

 

I was able to identify the table name variable and put it onto the EasySync Payloads layout as a merge variable so I could at least see the faulty table name. Each and every time, the failure point included the same prefix of ?[[bR]]. After digging through the pull payload script, I found where the $table_name variable was set and added a variable after it which set $table_name to Substitute($table_name; “?[[bR]]”; “”). On the first attempt, it still failed. I went back in and found the next occurrence of the set variable, copied my script step to below that and ran it again.

 

Lo and behold, the sync worked perfectly and has ever since. As I said at the beginning, this is a workaround and not a solution. The process is still adding the prefix, I’ve just figured out how to repair it. I still have no idea what the prefix means or how it got there. If anyone can shed any light on it, I’d be most appreciative. I’m just relieved that it’s over…for now.

 

Marc

[timdietrich]

Marc --

 

When syncing with debug mode enabled, just before the "Table Name for Payload Record" dialog is displayed (and shows the table name as "?[[bR]]ES_Projects"), there is a dialog that shows what the entire payload record is. The dialog title is something like "Payload Record n"...

 

What does it show for the value of that record?

 

Also, if there is a record before that one, what is its value?

 

-- Tim

[DCA]

Tim,

 

Considering I've lost close to $50K in other work while trying to sort this out, I'm reluctant to waste any more time trying to figure out the exact cause. I'll just wait and see if the problem occurs again during our final testing.

 

Marc

 

Marc --

 

When syncing with debug mode enabled, just before the "Table Name for Payload Record" dialog is displayed (and shows the table name as "?[[bR]]ES_Projects"), there is a dialog that shows what the entire payload record is. The dialog title is something like "Payload Record n"...

 

What does it show for the value of that record?

 

Also, if there is a record before that one, what is its value?

 

-- Tim

[Douglas Alder]

Hi Tim,

First let me say thank you for all the hard work you have put into this solution. It is amazingly intricate and seems like it must have taken you a lot of time to create. To release it open source is truly great.

That said, I am seeing the same issue with the "[[bR]]ES_Projects" text creating an error. Coincidentally it is the exact same table name as Marc had issues with.

The script seems to have problems when the it moves from the last record of the Equipment table, to the first record of the Projects table. I put all the fields for the equipment table on the ES_Projects Layout and looked through the fields, but there does not seem to be any anomalous data.

One unusual field called 'Selected Field' contained this type of data: datatablename::fieldname. It was not an essential field, so I removed it and tried again. It still failed. 

I have checked all my field names in the Equipment table (and other tables) and they contain no '|' or '?' characters.

I also deleted a batch of records (including the last record) in the Equipment table, to shorten the testing time and to see if any of the records in particular were giving issues. When I rerun the script I get the same error when it switches to Projects.

Any suggestions aside from the workaround suggested above?

Thanks.

Doug

[DCA]

Good luck with that Douglas. If you do figure out the cause and an actual solution, please post it here. I'd certainly be interested, even though we've pretty much given up on FM EasySync for our solution.

Marc

[timdietrich]

Doug,

The "?" is usually an indication that either an ExecuteSQL call has failed (meaning that the SELECT statement was invalid for some reason) or that something else that required dynamic evalulation failed. Are you sure that all of the ES_ fields have been setup correctly? And do you get this error for every sync, or only under certain conditions?

Tim

[ignotum]

WAY old topic but I found that if I had records with missing ES_UUIDs I would get a 106 error.

 

I can verify that in my situation the problem was data. I enabled PULL debug and saw ?ES for a table name. I cleaned up bad data on the Master file manually and the problem went away.

 

Edited January 11, 2018 by ignotum

[ignotum]

(thought I had new info.... maybe)

Edited February 1, 2018 by ignotum

[Lee Smith]

I sent you a Private Message on Oct  4, 2017, Please read it now.

[ignotum]

Explain this:

A new table was added to a solution that has been syncing with no problems for a long time.

This new table contains fields with data in non-Latin characters.

Sync fails with a 106 error.

Remove the table from the sync ("x" out their TOs in the client & Master) and sync works.

Re-enable that table to sync without touching anything else and the sync still works.

Ideas?