Column CustomerFavoriteProductAutoId used in code bit not in update scripts

Imar Spaanjaars

Posted on 22/05/2019 08:28:26

We're running into SQL exceptions when using favorites lists indicating that the column CustomerFavoriteProductAutoId does not exist in table comCustomerFavoriteProducts.

I found that column listed in Create_DefaultEmpty.sql when the table is created and in CustomerProductListProduct.vb where it's used (since very recently, I believe). However, I don't see it in any of the upgrade scripts, so when you upgrade a site from before end of 2016 to the latest 9.6, things will stop working because of the missing column.

Should the column be created as part of the update scripts?

Imar

Replies

Nicolai Pedersen

Posted on 22/05/2019 10:36:28

I've asked QA to look into this.

BR Nicolai

Imar Spaanjaars

Posted on 22/05/2019 11:13:23

Thanks!

Oleg Rodionov

Posted on 24/05/2019 06:14:27

Hi all,

I've created new TFS 65169 against the issue, it will be fixed on a further hotfix/release.Thanks.

BR, Oleg QA

Imar Spaanjaars

Posted on 26/05/2019 21:14:49

Hi Oleg,

Thanks! I looked at the fix and I wonder if it's going to work. First the DDL statement:

ALTER TABLE [EcomCustomerFavoriteProducts] ADD [CustomerFavoriteProductAutoId] IDENTITY NOT NULL

The column definition is missing a data type. I believe the correct statement would be:

ALTER TABLE [EcomCustomerFavoriteProducts] ADD [CustomerFavoriteProductAutoId] INT IDENTITY NOT NULL

Secondly, the Where condition:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='EcomCustomerFavoriteProducts' AND COLUMN_NAME='CustomerFavoriteProductAutoId'

Doesn't this say: If there is a table called EcomCustomerFavoriteProducts and it already has a column called CustomerFavoriteProductAutoId? Shouldn't the check for the column be using <>

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='EcomCustomerFavoriteProducts' AND COLUMN_NAME <> 'CustomerFavoriteProductAutoId'

making this read as "If there is a table called EcomCustomerFavoriteProducts and it does not already have a column called CustomerFavoriteProductAutoId"?

Thanks!

Kim Søjborg Pedersen

Posted on 20/06/2019 20:00:33

What was the solution to this? Just upgraded from 9.6.6 to 9.6.10 and the site crashed with this SQL exception. Had to roll back.

Regards
Kim

Vladimir Shushunov

Posted on 21/06/2019 01:17:41

Hi Kim,

Could you try to rerun 2112 update package for ecom on 9.6.10 (Settings\System\System information -> UPDATES -> ecom)?

Best regards,

Vladimir

Vladimir Shushunov

Posted on 21/06/2019 01:26:51

Hi Imar,

1. It is not clean MS transact SQL - it has some DW specific stuff

2. No. The condition try to find a column with name = 'CustomerFavoriteProductAutoId'. if condition returns 0 - the SQL update statement is runned .

If condition will have <> - it returns number of columns in EcomCustomerFavoriteProducts table.

Best regards,

Vladimir

Kim Søjborg Pedersen

Posted on 21/06/2019 08:30:29

Hi Vladimir

Yes I tried the red button, before I rolled back to 9.6.6, but the SQL exception was still there.

/Kim

Vladimir Shushunov

Posted on 21/06/2019 09:10:56

well... there should be a blue button near: "download update log".

If you have the chance, please attach it to the topic then I will able to analyze the problem.

Best regards,

Vladimir

Kim Søjborg Pedersen

Posted on 21/06/2019 09:55:10

Hi Vladimir

I'm not sure what kind of information is in there, can I send it to your e-mail?

/Kim

Vladimir Shushunov

Posted on 21/06/2019 13:26:59

Hi Kim,

Sure! send to vshdynamicweb-cms.com

Best regards,

Vladimir

Imar Spaanjaars

Posted on 21/06/2019 17:08:52

>> No. The condition try to find a column with name = 'CustomerFavoriteProductAutoId'. if condition returns 0 - the SQL update statement is runned .

That makes sense. Thanks!

You must be logged in to post in the forum

Developer forum

Column CustomerFavoriteProductAutoId used in code bit not in update scripts

Replies