ApplicationXtender - Administrator - Table Mismatch when Upgrading

ApplicationXtender Administrator - "TableMismatch" error received when attempting to upgrade ApplicationXtender with some users logged into the system

Applies to

ApplicationXtender Desktop 16.3, 16.6, 8.0, 8.1
ApplicationXtender Web Access.NET 16.3, 8.0, 8.1
ApplicationXtender Web Services 16.3, 16.6, 8.0, 8.1

Summary

In ApplicationXtender Administrator, when upgrading to a newer version of ApplicationXtender, a "TableMismatch" error occurs.

Cause

In ApplicationXtender Administrator (AppXtenderAdmin) when upgrading the data source, new columns are added. Those columns cannot be added if the tables are locked due to users still being logged in and using the system. This results in new columns missing such as the IDXID in the ae_dt# tables. Other new columns or fields added during the upgrade are MODTS and FTTS to ae_dt# tables.

Upon further investigation, you see these errors from AXRestEventLog regarding the column:

IDXID":AXRest Event Log from the server:XtenderSolutions.AXEngineApi.AXDocumentException: ORA-00904: "IDXID": invalid identifier ---> Oracle.DataAccess.Client.OracleException: ORA-00904: "IDXID": invalid identifier
at XtenderSolutions.CMData.CMDocument.CheckOutAsWorkingCopy(CMConnection conn, CMApplication app, String comment, FileCopyProc fileCopy)
at XtenderSolutions.AXEngineApi.Application.AXDocumentBase.OnCheckoutEx(String strChkoutComments)

Running a Validation check on AppXtenderAdmin shows this error:

XSDatabaseLib Verbose: 0 : Enter XtenderSolutions.Administration.Database.AXDBVerifyStatus VerifyTable(XtenderSolutions.Administration.Database.AXDBTableDef, Boolean, XtenderSolutions.Administration.Database.AXDBIndexDef[]).
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4088761Z
XSDatabaseLib Verbose: 0 : Verifying table ae_dt1...
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4088761Z
XSDatabaseLib Verbose: 0 : Verified column idxid on table ae_dt1: TableMismatch (column missing).
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4713639Z
XSDatabaseLib Verbose: 0 : Leave VerifyTable.
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4713639Z
XSDatabaseLib Information: 0 : Verified Datasource (7d1a1ccc-3dd3-407e-b118-a93568dfc3c2) TableMismatch.
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4713639Z
XSDatabaseLib Verbose: 0 : Leave VerifyDataSourceTables.
ProcessId=8248
ThreadId=15
DateTime=2019-12-04T19:06:00.4713639Z

You verified that the database version is 8.02.000 as shown in the ae_cfg table. But validating the data source it will now fail even though it supposedly upgraded.

Resolution

In this situation, you need to manually add these missing fields.

Besides idxid, add modts and ftts fields for ae_dt# tables.

Steps:
This is an example if the AppID is 1 for the affected application.

Remove all the indexes of that table,

Run the following upgrade script

ALTER TABLE [dbo].[ae_dt1] ADD idxid int null, modts datetime null,ftts datetime null;

Recreate index

primeind - docid
ae_dt1_index1 - docid, idxid
ae_dt1_index2 - modts
ae_dt1_index3 - ftts
index1 - field1
index2 - field2
... (each field column should have its own index here)
DISCLAIMER: The above information is provided without warranty either expressed or implied. This information is intended only for those who are familiar with the SQL programming language demonstrated and the tools used to create and activate procedures. You should have a backup of your database before you begin to make the suggested changes to your database. If you are not familiar with the programming demonstrated in these examples, do not attempt to run the SQL statements that are provided.