During an upgrade of a quality assurance (QA) instance of OpenText Content Server 10.0.0 SP2 Update 12 to Content Server 10.5.0 Update 2014-06 I ran into an incident at the RetentionUpdateOrder table.

[01/14/2015 04:25:31 PM] : Applying the database schema upgrade
[01/14/2015 04:25:31 PM] : Error executing statement
[01/14/2015 04:25:31 PM] : create table RetentionUpdateOrder ( … )
[01/14/2015 04:25:31 PM] : Error: Error executing an Sql statement.
[01/14/2015 04:25:31 PM] : Error: Error executing an Sql statement.
[01/14/2015 04:25:31 PM] : The Module Upgrade Failed Due To Errors

The error, as it turns out, was caused because my database already had a table called “RetentionUpdateOrder” in it.  Great, now what?

A quick look at my QA and production Content Server 10.0 databases revealed that the table in question existed in both.  This was not a table that I added, altered, or otherwise modified, so it had to come from the core installation of Content Server or one of the modules that my employer has elected to install.

I needed to know how big the scope of this problem was.  As it turned out, RetentionUpdateOrder was not alone.  Three of it’s friends were also giving me a little bit of heartburn during the upgrade; they are: RetentionUpdateOrder, RetentionUpdateRetry, RetentionUpdateFailed, and RetentionUpdateLog.

WARNING: This post talks about modifications to your Content Server database.  You should only directly alter your Content Server database under the supervision of a trained OpenText engineer.

Once I knew which tables involved in the upgrade, I used the following SQL to figure that out how much data was going to have to deal with.  Thankfully, using the following SQL command, the results all came back as zero rows.

use [yourContentServerDBName]
go
select COUNT(*) from RetentionUpdateOrder
go
select COUNT(*) from RetentionUpdateRetry
go
select COUNT(*) from RetentionUpdateFailed
go
select COUNT(*) from RetentionUpdateLog
go

To workaround this issue, wrote a short little SQL script to backup the tables in my database, and then drop the original so I could restart the Content Server database schema upgrade process and have it finish successfully.  Here’s the rough utility SQL script that I wrote to clean up the table situation.

use [yourContenServerDBName]
go
select * into RetentionUpdateOrder_CS10_5_Backup
    from RetentionUpdateOrder
go
if OBJECT_ID(‘livelink..RetentionUpdateOrder’,’U’) is not null
    drop table RetentionUpdateOrder
go
select * into RetentionUpdateRetry_CS10_5_Backup
    from RetentionUpdateRetry
go
if OBJECT_ID(‘livelink..RetentionUpdateRetry’,’U’) is not null
    drop table RetentionUpdateRetry
go
select * into RetentionUpdateFailed_CS10_5_Backup
    from RetentionUpdateFailed
go
if OBJECT_ID(‘livelink..RetentionUpdateFailed’,’U’) is not null
    drop table RetentionUpdateFailed
go
select * into RetentionUpdateLog_CS10_5_Backup
    from RetentionUpdateLog
go
if OBJECT_ID(‘livelink..RetentionUpdateLog’,’U’) is not null
    drop table RetentionUpdateLog
go

To jump start the database schema upgrade again, in my browser, I navigated back to the admin home page at ?func=admin.index and then jumped over to the database upgrade page at ?func=admin.dbupgrade.  (You may be prompted to log back in as ‘admin’.)

Once I restarted the database schema upgrade, it was a few short minutes until I got the following message back from my Content Server 10.5 instance:

[01/16/2015 05:45:03 PM] : Applying the database schema upgrade
[01/16/2015 05:45:03 PM] : Applying the data upgrade
[01/16/2015 05:45:03 PM] : The database upgrade completed with no errors.

Outstanding!  Your results may vary from mine. It is always to have a good set of backups, rollback plans and we should always test upgrades in test instances before moving on to QA, DR, and production.  If you get into trouble, or have questions, don’t forget to contact the OpenText Support Team.

UPDATE:

After talking to OpenText support, two important bits of information have surfaced about this issue.  They are:

1. The four RetentionUpdate tables discussed here are for the Archive Storage Provider module and NOT the Records Manager module.

2. If you have an aversion to writing SQL, you can avoid all of this by installing the Archive Storage Provider 10.0.2 module update before attempting to upgrade to Content Server 10.5.0.  Naturally, this is the method preferred by the OpenText Support team and I agree with them.  If you chose to use the backup/drop table method I described here, that is a viable workaround.  Any updates or alterations of a Content Server/Livelink database should be done only with guidance from Support.