Error During Upgrade from 7.0.7 to Gold R6

[joebeazelman]

I am migrating a site from version 7.0.3 to AbleCommerce Gold R6. The process of upgrading to version 7.0.7 was straight forwards and I was able to browse the site without any apparent errors. I then upgraded to AC Gold R6 and I encountered a null id AssertionFailure Error (see attachment). I proceeded with the upgrade process and I got several SQL errors:


Initializing Data

Please be patient as this may take a few moments...


Database Upgrade Complete

As part of the upgrade process, a rebuild of your search indexes was started. If you have upgraded a database with lots of data, this could take a few minutes, and the process needs to complete before searches of your store data will be fully functional. You can check on and monitor the progress of the index rebuild by going to the Website > Indexes menu of your merchant administration.

The errors listed below occurred while upgrading the database:

SQL: IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name = N'IsRead' and Object_ID = Object_ID(N'ac_OrderNotes')) BEGIN ALTER TABLE ac_OrderNotes ADD IsRead bit NOT NULL DEFAULT 1 END ALTER TABLE [ac_Addresses] ADD [IsBilling] [bit] NOT NULL DEFAULT(0)

Error: Column names in each table must be unique. Column name 'IsBilling' in table 'ac_Addresses' is specified more than once.

SQL: UPDATE ac_Addresses SET IsBilling = 1 Where AddressId IN (SELECT AddressId FROM ac_Addresses INNER JOIN ac_Users ON ac_Addresses.AddressId = ac_Users.PrimaryAddressId)

Error: Invalid column name 'PrimaryAddressId'.

SQL: ALTER TABLE [ac_BasketItems] ADD [CreatedDate] [DATETIME] NULL

Error: Column names in each table must be unique. Column name 'CreatedDate' in table 'ac_BasketItems' is specified more than once.

SQL: Alter TABLE ac_CatalogNodes ADD [Id] [int] IDENTITY(1,1) NOT NULL

Error: Column names in each table must be unique. Column name 'Id' in table 'ac_CatalogNodes' is specified more than once.

SQL: ALTER TABLE [ac_Categories] ADD [WebpageId] [int] NULL

Error: Column names in each table must be unique. Column name 'WebpageId' in table 'ac_Categories' is specified more than once.

SQL: IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name = N'HtmlHead' and Object_ID = Object_ID(N'ac_Categories')) BEGIN ALTER TABLE [ac_Categories] ADD [HtmlHead] [nvarchar](MAX) NULL END ALTER TABLE [ac_Categories] ADD [MetaDescription] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaDescription' in table 'ac_Categories' is specified more than once.

SQL: ALTER TABLE [ac_Categories] ADD [MetaKeywords] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaKeywords' in table 'ac_Categories' is specified more than once.

SQL: ALTER TABLE [ac_Categories] DROP COLUMN [DisplayPage]

Error: ALTER TABLE DROP COLUMN failed because column 'DisplayPage' does not exist in table 'ac_Categories'.

SQL: ALTER TABLE [ac_Categories] DROP COLUMN [Theme]

Error: ALTER TABLE DROP COLUMN failed because column 'Theme' does not exist in table 'ac_Categories'.

SQL: Alter Table ac_CategoryParents Add [Id] [int] IDENTITY(1,1) NOT NULL

Error: Column names in each table must be unique. Column name 'Id' in table 'ac_CategoryParents' is specified more than once.

SQL: ALTER TABLE [ac_Links] ADD [MetaDescription] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaDescription' in table 'ac_Links' is specified more than once.

SQL: ALTER TABLE [ac_Links] ADD [MetaKeywords] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaKeywords' in table 'ac_Links' is specified more than once.

SQL: ALTER TABLE [ac_ProductImages] ADD [Moniker] [varchar](255) NULL

Error: Column names in each table must be unique. Column name 'Moniker' in table 'ac_ProductImages' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD [WebpageId] [int] NULL

Error: Column names in each table must be unique. Column name 'WebpageId' in table 'ac_Products' is specified more than once.

SQL: IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name = N'HtmlHead' and Object_ID = Object_ID(N'ac_Products')) BEGIN ALTER TABLE [ac_Products] ADD [HtmlHead] [nvarchar](MAX) NULL END ALTER TABLE [ac_Products] ADD [MetaDescription] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaDescription' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD [MetaKeywords] [nvarchar](1000) NULL

Error: Column names in each table must be unique. Column name 'MetaKeywords' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD GTIN NVARCHAR(30) NULL

Error: Column names in each table must be unique. Column name 'GTIN' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD GoogleCategory NVARCHAR(300) NULL

Error: Column names in each table must be unique. Column name 'GoogleCategory' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD Condition NVARCHAR(20) NULL

Error: Column names in each table must be unique. Column name 'Condition' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD Gender NVARCHAR(10) NULL

Error: Column names in each table must be unique. Column name 'Gender' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD AgeGroup NVARCHAR(10) NULL

Error: Column names in each table must be unique. Column name 'AgeGroup' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD Color NVARCHAR(20) NULL

Error: Column names in each table must be unique. Column name 'Color' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD Size NVARCHAR(20) NULL

Error: Column names in each table must be unique. Column name 'Size' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD AdwordsGrouping NVARCHAR(50) NULL

Error: Column names in each table must be unique. Column name 'AdwordsGrouping' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD AdwordsLabels NVARCHAR(256) NULL

Error: Column names in each table must be unique. Column name 'AdwordsLabels' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD AdwordsRedirect NVARCHAR(256) NULL

Error: Column names in each table must be unique. Column name 'AdwordsRedirect' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD PublishFeedAsVariants BIT DEFAULT 0 NOT NULL

Error: Column names in each table must be unique. Column name 'PublishFeedAsVariants' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD RowVersion INT DEFAULT 1 NOT NULL

Error: Column names in each table must be unique. Column name 'RowVersion' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] ADD OrderBy DECIMAL(10,2) NULL

Error: Column names in each table must be unique. Column name 'OrderBy' in table 'ac_Products' is specified more than once.

SQL: ALTER TABLE [ac_Products] DROP COLUMN [DisplayPage]

Error: ALTER TABLE DROP COLUMN failed because column 'DisplayPage' does not exist in table 'ac_Products'.

SQL: ALTER TABLE [ac_Products] DROP COLUMN [Theme]

Error: ALTER TABLE DROP COLUMN failed because column 'Theme' does not exist in table 'ac_Products'.

SQL: ALTER TABLE [ac_ProductVariants] ADD [RowVersion] [int] DEFAULT 1 NOT NULL

Error: Column names in each table must be unique. Column name 'RowVersion' in table 'ac_ProductVariants' is specified more than once.

SQL: ALTER TABLE [ac_ProductVariants] ADD GTIN NVARCHAR(30) NULL

Error: Column names in each table must be unique. Column name 'GTIN' in table 'ac_ProductVariants' is specified more than once.

SQL: ALTER TABLE [ac_ProductVariants] ADD ImageUrl VARCHAR(255) NULL

Error: Column names in each table must be unique. Column name 'ImageUrl' in table 'ac_ProductVariants' is specified more than once.

SQL: ALTER TABLE [ac_ProductVariants] ADD ThumbnailUrl VARCHAR(255) NULL

Error: Column names in each table must be unique. Column name 'ThumbnailUrl' in table 'ac_ProductVariants' is specified more than once.

SQL: ALTER TABLE [ac_ProductVariants] ADD IconUrl VARCHAR(255) NULL

Error: Column names in each table must be unique. Column name 'IconUrl' in table 'ac_ProductVariants' is specified more than once.

SQL: ALTER TABLE [ac_Users] DROP COLUMN [PrimaryAddressId]

Error: ALTER TABLE DROP COLUMN failed because column 'PrimaryAddressId' does not exist in table 'ac_Users'.
The database has been upgraded.

Continue To Admin


Am I missing an upgrade step? AbleCommerce's documentation said I can go directly to R6 from 7.0.7.

[Katie]

I then upgraded to AC Gold R6 and I encountered a null id AssertionFailure Error (see attachment). I proceeded with the upgrade process and I got several SQL errors

What steps did you take to upgrade? For Gold, you want to do a new installation, then use the Specify Database option and point to your AC7.0.7 database. Then you will select the option "This is an existing AC7 database to be upgraded". I just want to be sure that you followed these steps first.

Also, some of the errors indicate that the database column already exists. Did you by chance upgrade the database a second time?

e.g. Column name 'RowVersion' in table 'ac_ProductVariants' is specified more than once.

[joebeazelman]

I did exactly what you mentioned. I upgraded only once aside from the 7.0.7 upgrade which went well. From the documentation, running the upgrade more than once shouldn't cause any issues. The "column already exists" errors are probably a result of me running through the exception error (See screenshot). Interestingly, I can navigate the site, but all the product categories are empty and navigating to admin gives me a 404 error. Either way, the unhandled exception should be caught. I'm wondering if this has anything to do with the web.config file.

[jmestep]

If you have your site set to use Lucene search, you need to re-index right after you upgrade or switch it over to SQL search right after you upgrade.

[Katie]

The re-index happens automatically, but if you read the description above, it can continue to take place after the upgrade.

As part of the upgrade process, a rebuild of your search indexes was started. If you have upgraded a database with lots of data, this could take a few minutes, and the process needs to complete before searches of your store data will be fully functional. You can check on and monitor the progress of the index rebuild by going to the Website > Indexes menu of your merchant administration.

[joebeazelman]

I ignored the database errors and I am able to navigate the site, but I wonder if this has anything to do with the slow performance and database timeout issues I am having.

[joebeazelman]

The error I am getting here has nothing to do with my other error that has been just been solved (viewtopic.php?f=65&t=17684). It turns out that we were trying to implement a unit of measure system by adding extra fields to the product table. It turns out that the upgrade process doesn't like it. The solution is to remove it since we are no longer using it anyway. Interestingly, this appears in the anonymous user maintenance document and not in its own:



http://help.ablecommerce.com/upgrades/a ... enance.htm:


Manual database cleanup and fixing maintenance errors

Using the Dataport, or changing the database outside of the AbleCommerce, can sometimes lead to errors such as this -

System.ArgumentException: An item with the same key has already been added.

If this should occur, you will need to open the Products table and remove any columns that shouldn't be there.


It's fairly reasonable to expect developers to mess with AC's schema. A simple admonishment during the upgrade process would have prevented this issue from occurring. Something like: "Did you mess with our schema? Remove your changes and rerun the upgrade process!"