AbleCommerce Shopping Cart Software

Posted: **Thu Jul 11, 2013 8:23 am**

We recently upgraded from RC4 to RC5 and we are now unable to add a new product.

The issue seems to be that the AdwordsPublish column in the ac_products table was not deleted as mentioned in the upgrade documentation. Other changes to the database were successfully changed such as the ExcludedDestination column in the ac_products table. I have confirmed that the correct AddProduct.aspx has been added as part of the RC5 upgrade.

Is there a way to re-run the script to update the database? I don't want to make manual changes to the database unless I absolutely have to.

Has someone else also reported this problem or is it just me again?

Thanks!

Code: Select all

GenericADOException 
   could not insert: [CommerceBuilder.Products.Product][SQL: INSERT INTO ac_Products (RowVersion, StoreId, Name, Price, CostOfGoods, MSRP, Weight, Length, Width, Height, ManufacturerId, Sku, ModelNumber, WebpageId, TaxCodeId, ShippableId, WarehouseId, InventoryModeId, InStock, AvailabilityDate, InStockWarningLevel, ThumbnailUrl, ThumbnailAltText, ImageUrl, ImageAltText, Summary, Description, ExtendedDescription, VendorId, CreatedDate, LastModifiedDate, IsFeatured, IsProhibited, AllowReviews, AllowBackorder, WrapGroupId, ExcludeFromFeed, DisablePurchase, MinQuantity, MaxQuantity, VisibilityId, IconUrl, IconAltText, IsGiftCertificate, UseVariablePrice, MinimumPrice, MaximumPrice, HidePrice, Title, MetaDescription, MetaKeywords, SearchKeywords, HtmlHead, GTIN, GoogleCategory, Condition, Gender, AgeGroup, Color, Size, AdwordsGrouping, AdwordsLabels, ExcludedDestination, AdwordsRedirect, PublishFeedAsVariants, OrderBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] at CommerceBuilder.DomainModel.EntityWithTypedId`1.Save() at AbleCommerce.Admin.Products.AddProduct.SaveProduct() at AbleCommerce.Admin.Products.AddProduct.SaveButton_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Cannot insert the value NULL into column 'AdwordsPublish', table 'aerodevgold.dbo.ac_Products'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(IDbCommand insert, ISessionImplementor session) at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)  
   http://dev.aerobed.com/Admin/Products/AddProduct.aspx?CategoryId=5 
   /Admin/Products/AddProduct.aspx

Posted: **Fri Jul 12, 2013 5:20 am**

I just tested on a site that went from R3 to R5 and got the same problem. I will post a bug with Able. You can run this script:
ALTER TABLE ac_products DROP COLUMN [adwordspublish]

Posted: **Fri Jul 12, 2013 5:40 am**

Don't try it yet, it won't work so I need to bone up on SQL server indexes
If you try to drop it, you get this error
The index '_dta_index_ac_Products_14_1925581898__K31_K41_K18_K19_K34_K1_2_3_4_5_6_7_8_9_10_11_12_13_15_16_17_20_21_22_23_24_25_26_27_28_' is dependent on column 'adwordspublish'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN adwordspublish failed because one or more objects access this column.

Posted: **Fri Jul 12, 2013 7:22 am**

Thanks for reporting this Judy.

I actually found this issue in our bug system, and it was supposedly reported and fixed during testing before R5 was released. Now, obviously there is still some issue with the database changes during upgrade and we'll have to identify what that is.

Katie

Posted: **Fri Jul 12, 2013 8:21 am**

Thanks Judy for confirming this. Although I don't enjoy finding bugs, it's nice to know I'm not the only one.

Katie - I will wait for your team's response for the patch.

Posted: **Sun Jul 14, 2013 9:09 pm**

We have tried to reproduce this using difference sequences of upgrade to R5 but we are still not able to reproduce this. Are we missing something?

Is it because of some manual indexes created that we do not have in stock AC Gold installs?

Can you check your app.log file and see if you can find find any useful information? The upgrade service must have tried to drop the ''adwordspublish" column and it must have failed. We are unable to reproduce this failure in stock AC Gold releases.

Posted: **Mon Jul 15, 2013 12:00 am**

All efforts to create a situation where this could be reproduced have failed. It is not clear why the column fails to drop. Directly dropping the column does not work because of the default value constraints, but we have taken care of this in our upgrade code. We have something like this to remove the constraints.

sql = @"DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
SELECT TOP 1 @sql = N'alter table ac_Products drop constraint ['+dc.NAME+N']'
from sys.default_constraints dc
JOIN sys.columns c
ON c.default_object_id = dc.object_id
WHERE
dc.parent_object_id = OBJECT_ID('ac_Products')
AND c.name = N'AdwordsPublish'
IF @@ROWCOUNT = 0 BREAK
EXEC (@sql)
END";

Posted: **Mon Jul 15, 2013 9:14 am**

I just upgraded a test site from R4 to R5 without any trouble. I had even created a couple test products before upgrading that were using all available Adwords fields.

We really need to figure out why your 2 installs didn't work. Do you have any customizations?

Posted: **Tue Jul 16, 2013 4:39 am**

I think I might have found our problem with the indexes. I apologize- I would have suspected the answer if Daniel had not had the issue also. We do use the data tuning advisor on our databases and I do sometimes see error messages at upgrade time, but the upgrade usually is OK. The index that was a problem in dropping the adwordspublish field was called _dta_index xxx.
I finally thought to Google it this morning and found out that it is from the data tuning advisor (dta, duh).
I had looked for something in your sql script to drop the adwords publish field and couldn't find it. So that is why I had confirmed the problem. For future reference, where is that code?

Posted: **Tue Jul 16, 2013 4:41 am**

For some technical reasons the upgrade service code is not part of the available source code.

Posted: **Tue Jul 16, 2013 4:45 am**

Our upgrade service is running this

Code: Select all

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
SELECT TOP 1 @sql = N'alter table ac_Products drop constraint ['+dc.NAME+N']'
from sys.default_constraints dc
JOIN sys.columns c
ON c.default_object_id = dc.object_id
WHERE
dc.parent_object_id = OBJECT_ID('ac_Products')
AND c.name = N'AdwordsPublish'
IF @@ROWCOUNT = 0 BREAK
EXEC (@sql)
END

followed by this

Code: Select all

ALTER TABLE ac_products DROP COLUMN [adwordspublish]

AbleCommerce Shopping Cart Software

Unable to add new product

Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product

Re: Unable to add new product