Problem on Step 1 of DB Upgrade from 1.1.8 to 2.0.0

Submitted by swharrell on Wed, 10/07/2009 - 17:04
in

I am running the Maintenance Program 2.0 to upgrade an existing 1.1.8 database, on a MS SQL 2005 server, to 2.0.0. The existing DB has been, and still is, working fine but, when I run the upgrade option I receive "Column Names in each table must be unique. Column name 'md5Hash' in table 'Names' is specified more than once." at step 1. I have looked at the table in question and the column 'md5Hash' only appears once.

‹ log4j Error in initialization of database: "specified key was too long max key length is 1000 bytes" ›

Possible solution

Submitted by Nathan on Thu, 10/08/2009 - 10:05.

It looks like you tried to upgrade the database before and didn't complete it. The column md5Hash is only created/used in AT version 1.5 and above so it being in a 1.1.8 version indicates a failed upgrade at some point. suggestion is to restore from backup, or try deleting md5Hash column and retry upgrade (you probable will run into other problems though since other things may have been modified in the previous upgrade attempt).

RE: Possible solution

Submitted by swharrell on Wed, 10/14/2009 - 11:30.

Nathan,
Thank you for the reply. You may be receiving and email forwarded from Sibyl concerning this problem. After some research I found that I had "attempted" an upgrade to 1.1.8 from 1.0.39 back in April of this year and experienced the same problem. Sibyl was the one who responded to my request for assistance at that time.

Since this seems to be an ongoing problem for our DB and the end users apparently plan to continue using AT, is there a way to fix this and retain the DB contents so that when the next upgrade come about we don't run into the same problem again?

I know in your reply you stated that I can try deleting the md5Hash column. If I do this, and let's say the rest of the upgrade goes through without a hitch, will the column be recreated in the upgrade or will I need to recreate it myself. Since the column seems to be a SQL UID will recreation of the column cause any problems.

Would it be better to maybe create a backup of the current DB, delete it and create a 2.0 DB using the Maintenance program and then import the table data from the backup?

I'm just looking for a way to get our DB and client up to the latest version preserving the current data and not experience the same problem on the next upgrade.

Thanks for you time and help.

Yes the column will

Submitted by Nathan on Wed, 10/14/2009 - 11:47.

Yes the column will automatically be recreated on upgrade and that shouldn't cause any problems. That said, I would advice you to try this on a copy of the database and not the production version.

As for migrating data manually I don't recommend that, since the newer version of the AT populates a few fields automatically, for example the md5Hash.

You can also send me a copy of this database and I will take a look at it.

RE: Yes the column will

Submitted by swharrell on Wed, 10/14/2009 - 15:52.

I created a copy of the DB on our development server, deleted the md5Hash column and attempted an upgrade. I now receive a 'UniqueNames' is not a constraint. message on step 1.

Give me the information on where and how to send you a copy of the DB and I will.

Thank you.