Topic: Dictionary out of step- Dictionary upgrade fails

It is possible for the dictionary on an R6 system gets out of step with the master. The only known reason for this is an issue which occurred back in 2010, when it was discovered that running multiple updates together meant that only the first upgrade was processed.   This was fixed in November 2011 with a new upgrade program. I am not aware of issues since then.
There is no easy way of knowing if a dictionary is out of step as the errors can be subtle and not show up for a long time.  If you suspect a problem, because searches or reports do not work as expected then please contact me (mike@Lfield.co.uk)   I may be able to help to temporarily sort the problem,  but after that further investigation will be required.

I charge £40.00 to check a dictionary and if necessary to provide a special set of updates to bring the dictionary into line with the Master (up to 0000004K).  In order to provide this service I will need a copy of your database. I can accept a backup, or a copy of the actual database taken after a detach.  This can be provided on a DVD, memory stick or via Dropbox. All SQL versions can be accepted.  There is no need to stop using Recorder.  After checking I will provide a special dictionary upgrade which will bring your dictionary into line with the master.  I would normally turn this around within 48 hours. Please email me mike@Lfield.co.uk if you would like to use this service.  It will help if you let me know what version of SQL you are using.   

Note*  – The data will not be retained once the work is complete and will not be passed on to anyone else in any format.  I reserve the right to charge for any time taken in completing additional confidentiality agreements, as they can take more time than processing the data.

Mike Weideli


Re: Dictionary out of step- Dictionary upgrade fails

Thanks for your assistance in this.
As requested i have put together a summary of our problem and solution:
A problem  with the dictionary upgrade, from the error message caused by the sql in '0000003E.sql', it looks as if the FK constraint 'FK_TAXON_VERSION_TAXON' was preventing deletion of entries in the TAXON table because there were related entries in the TAXON_VERSION table (See forum post: http://forums.nbn.org.uk/viewtopic.php? … 6#p25386). This error meant that our database dictionary was in an inconsistent state and a database restore was required.
Apparently there was a problem back in 2010 where it was discovered that some dictionary updates when applied together meant that one of them wasn’t properly applied (see forum post http://forums.nbn.org.uk/viewtopic.php? … 9#p25389).
The fix for the dictionary to be put back into sync (also on the previously linked post) was to transfer a copy of our database bak file for Mike to generate a special set of dictionary updates which would re-sync to the master dictionary. We received these (really quickly actually) and applied them.
There was one further issue, because recorder database had to be restored, the dictionary version sequence was out and I got a terminal error from recorder dictionary upgrade application. This restriction on upgrades stops you from upgrading dictionary out of sequence, however in this case the dictionary sequence needed to be changed to allow the new updates. The current dictionary update version is held in the SETTING table under 'Dict seq', so just had to update this using:
SET DATA = '0000003E' -- use the update previous to new
WHERE NAME = 'Dict Seq'
The update then applied without error. Rebuilt indexes ok and now we are back in sync.
Thanks again Mike,



Re: Dictionary out of step- Dictionary upgrade fails

Hi Mike,
Thanks again for sorting out our dictionary problem last upgrade.
The most recent one (3F to 3N), has caused the upgrade executable to crash during updating TLI table, could this be due to an upgrade file sequence overlap? I will check, it may be due to something our end.
The 'special' ones you provided for us last time were 3F-3H, am i correct in assuming the files 3F-3H will be ignored by the upgrade executable?
Let me know


Re: Dictionary out of step- Dictionary upgrade fails

There shouldn't be a problem with the  overlap. You shoudl have been on 3H at the start so the upgrade woudl start with  3I. Upgrades  3F, 3G and 3H in the latest set are blank so don't affect anything. Can you tell me what the message is. There are no deletions of TLI or TVK's in this group of updates and only Organism table deletions, which shouldn't cause any  erros.

Mike Weideli


Re: Dictionary out of step- Dictionary upgrade fails

Hi Mike,
Yes you are right, seems to have worked fine now, sorry to have bothered you.