logo
Welcome to our new AbleCommerce forums. As a guest, you may view the information here. To post to this forum, you must have a registered account with us, either as a new user evaluating AbleCommerce or an existing user of the application. For all questions related to the older version of Gold and earlier, please go to forums.ablecommerce.com. Please use your AbleCommerce username and password to Login. Forums Registration.

Notification

Icon
Error

Options
Go to last post Go to first unread
Joe Payne @ AbleMods LLC  
#1 Posted : Thursday, January 21, 2021 3:03:45 PM(UTC)
Joe Payne @ AbleMods LLC

Rank: Advanced Member

Groups: HelpDesk
Joined: 11/9/2018(UTC)
Posts: 156

Thanks: 43 times
Was thanked: 7 time(s) in 7 post(s)
During a recent upgrade test on a large, heavily modified install I encountered a specific error.

Apparently at some point in the past, SQL Performance Analyzer was executed against the store database. This created several SQL statistics indexes against certain tables.

It appears the AC9 upgrade might just be accommodating for this scenario. However in my case the initial query to drop statistics returned more than 1 entry. So I don't think it was able to drop the statistics indexes prior to altering schemas on columns. This caused a cascade series of upgrade errors.

Quote:


The errors listed below occurred while upgrading the database:

SQL: DECLARE @drops_stat_sql NVARCHAR(MAX); SELECT @drops_stat_sql = STUFF((SELECT DISTINCT ' DROP STATISTICS ' + SCHEMA_NAME(ob.Schema_id) + '.' + OBJECT_NAME(s.object_id) + '.' + s.name DropStatisticsStatement FROM sys.stats s INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys' AND Auto_Created = 0 AND User_Created = 1), 1, 1, ''); EXEC [sp_executesql] @drops_stat_sql;

Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SQL: -- -- Alter column [OrderBy] on table [ac_BasketItems] -- ALTER TABLE [ac_BasketItems] ALTER COLUMN [OrderBy] [int] NOT NULL
Error: The statistics '_dta_stat_1778105375_1_15' is dependent on column 'OrderBy'. The statistics '_dta_stat_1778105375_15_3' is dependent on column 'OrderBy'. The statistics '_dta_stat_1778105375_3_1_15' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_CatalogNodes] -- ALTER TABLE [ac_CatalogNodes] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The statistics '_dta_stat_1666104976_3_1_4' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_OptionChoices] -- ALTER TABLE [ac_OptionChoices] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The index '_dta_index_ac_OptionChoices_6_1954106002__K2_K1_K10_3_4_5_6_7_8_9_11_12' is dependent on column 'OrderBy'. The statistics '_dta_stat_1954106002_10_2' is dependent on column 'OrderBy'. The statistics '_dta_stat_1954106002_10_1_2' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [Quantity] on table [ac_OrderItems] -- ALTER TABLE [ac_OrderItems] ALTER COLUMN [Quantity] [int] NOT NULL

Error: The index '_dta_index_ac_OrderItems_6_562101043__K6_K1_K17_2_3_4_5_7_8_9_10_11_12_13_14_15_16_18_19_20_21_22_23_24_25_26_27_28_29' is dependent on column 'Quantity'. ALTER TABLE ALTER COLUMN Quantity failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_OrderItems] -- ALTER TABLE [ac_OrderItems] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The index '_dta_index_ac_OrderItems_6_562101043__K6_K1_K17_2_3_4_5_7_8_9_10_11_12_13_14_15_16_18_19_20_21_22_23_24_25_26_27_28_29' is dependent on column 'OrderBy'. The statistics '_dta_stat_562101043_1_17' is dependent on column 'OrderBy'. The statistics '_dta_stat_562101043_17_6' is dependent on column 'OrderBy'. The statistics '_dta_stat_562101043_1_6_17' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [Frequency] on table [ac_OrderItems] -- ALTER TABLE [ac_OrderItems] ALTER COLUMN [Frequency] [int] NOT NULL

Error: The index '_dta_index_ac_OrderItems_6_562101043__K6_K1_K17_2_3_4_5_7_8_9_10_11_12_13_14_15_16_18_19_20_21_22_23_24_25_26_27_28_29' is dependent on column 'Frequency'. ALTER TABLE ALTER COLUMN Frequency failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_ProductImages] -- ALTER TABLE [ac_ProductImages] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The index '_dta_index_ac_ProductImages_6_2085582468__K2_K5_1_3_4_6' is dependent on column 'OrderBy'. The statistics '_dta_stat_2085582468_5' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_ProductOptions] -- ALTER TABLE [ac_ProductOptions] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The index '_dta_index_ac_ProductOptions_6_117575457__K1_K4_K3_2' is dependent on column 'OrderBy'. The statistics '_dta_stat_117575457_4_3' is dependent on column 'OrderBy'. The statistics '_dta_stat_117575457_3_1' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

SQL: -- -- Alter column [OrderBy] on table [ac_RelatedProducts] -- ALTER TABLE [ac_RelatedProducts] ALTER COLUMN [OrderBy] [int] NOT NULL

Error: The index '_dta_index_ac_RelatedProducts_6_245575913__K1_K4_2_3' is dependent on column 'OrderBy'. ALTER TABLE ALTER COLUMN OrderBy failed because one or more objects access this column.

Wanna join the discussion?! Login to your AbleCommerce Forums forum account. Forums Registration.

shaharyartiwana25816656  
#2 Posted : Friday, January 22, 2021 4:35:43 AM(UTC)
shaharyartiwana25816656

Rank: Advanced Member

Groups: Admin, Developers, Registered, HelpDesk, Authorized User
Joined: 10/5/2018(UTC)
Posts: 433

Thanks: 1 times
Was thanked: 36 time(s) in 36 post(s)
Hi,

I have registered a report to investigate this issue.
We'll try to reproduce the issue. Please do share any details or steps to reproduce the issue. That will be a great help.

Thanks for your support!
Joe Payne @ AbleMods LLC  
#3 Posted : Friday, January 22, 2021 7:59:07 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Advanced Member

Groups: HelpDesk
Joined: 11/9/2018(UTC)
Posts: 156

Thanks: 43 times
Was thanked: 7 time(s) in 7 post(s)
Some further testing...

By running the initial query directly in T-SQL, I can output-to-text all the commands to drop the statistics. Copy/paste that to a new query window and now all stats are dropped prior to upgrade. SQL doesn't like something about that STUFF() command but at least all the individual DROP commands work exactly as needed.

The next wall I hit is custom indexes. Any table that has a custom index on a column being hit with ALTER COLUMN during the upgrade will also fail the upgrade. But we can't really just drop all indexes - there's a bunch that Able needs. Not entirely sure how to handle that smoothly during install. It would be handy to have the install render a list of all non-standard indexes on all tables in the db. But I don't know how you would be able to tell a 'standard' Able index versus one that was created later in life.

If there was a way to know if an index on a table was created by Able or not created by Able, the solution would be simple.

In this particular case, I was able to use the error list to determine which indexes needed to be removed. One interesting note: They all have the same starting name.

Quote:

/* drop indexes */
drop index _dta_index_ac_OptionChoices_6_1954106002__K2_K1_K10_3_4_5_6_7_8_9_11_12 on ac_optionchoices
drop index _dta_index_ac_OrderItems_6_562101043__K6_K1_K17_2_3_4_5_7_8_9_10_11_12_13_14_15_16_18_19_20_21_22_23_24_25_26_27_28_29 on ac_orderitems
drop index _dta_index_ac_ProductImages_6_2085582468__K2_K5_1_3_4_6 on ac_productimages
drop index _dta_index_ac_ProductOptions_6_117575457__K1_K4_K3_2 on ac_productoptions
drop index _dta_index_ac_RelatedProducts_6_245575913__K1_K4_2_3 on ac_relatedproducts
shaharyartiwana25816656  
#4 Posted : Tuesday, February 2, 2021 5:12:32 AM(UTC)
shaharyartiwana25816656

Rank: Advanced Member

Groups: Admin, Developers, Registered, HelpDesk, Authorized User
Joined: 10/5/2018(UTC)
Posts: 433

Thanks: 1 times
Was thanked: 36 time(s) in 36 post(s)
Hi Joe,

We have tried reproducing the issue by creating some custom SQL statistics on our sample database but were unable to reproduce the issue.

Can you please help us in reproducing the issue.
Joe Payne @ AbleMods LLC  
#5 Posted : Tuesday, February 2, 2021 5:21:43 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Advanced Member

Groups: HelpDesk
Joined: 11/9/2018(UTC)
Posts: 156

Thanks: 43 times
Was thanked: 7 time(s) in 7 post(s)
I'm about 90% sure these statistics came from running SQL Database Tuning Advisor some time long ago.

The trouble you'll have in recreating the steps is your wizard against your db won't make the exact same recommendations as it will on mine. I can see where you're looking for user-created statistics in the upgrade. It's possible that my stats may be flagged differently somehow.

As far as the offending indices, here are a few examples on my ac_BasketItems table. Run these scripts on your test db, then try some testing against it. Maybe these will throw the error for you. But again, creating them manually as opposed to how they were originally created may still make the upgrade miss them.

Let me know if I can help any further.

/****** Object: Statistic [_dta_stat_1778105375_1_15] Script Date: 2/2/2021 6:17:20 AM ******/
CREATE STATISTICS [_dta_stat_1778105375_1_15] ON [dbo].[ac_BasketItems]([BasketItemId], [OrderBy])

/****** Object: Statistic [_dta_stat_1778105375_15_3] Script Date: 2/2/2021 6:18:07 AM ******/
CREATE STATISTICS [_dta_stat_1778105375_15_3] ON [dbo].[ac_BasketItems]([OrderBy], [BasketId])

/****** Object: Statistic [_dta_stat_1778105375_27] Script Date: 2/2/2021 6:18:29 AM ******/
CREATE STATISTICS [_dta_stat_1778105375_27] ON [dbo].[ac_BasketItems]([FrequencyUnitId])

/****** Object: Statistic [_dta_stat_1778105375_3_1_15] Script Date: 2/2/2021 6:18:46 AM ******/
CREATE STATISTICS [_dta_stat_1778105375_3_1_15] ON [dbo].[ac_BasketItems]([BasketId], [BasketItemId], [OrderBy])

/****** Object: Statistic [_dta_stat_1778105375_3_7_18_16] Script Date: 2/2/2021 6:19:02 AM ******/
CREATE STATISTICS [_dta_stat_1778105375_3_7_18_16] ON [dbo].[ac_BasketItems]([BasketId], [TaxCodeId], [WishlistItemId], [WrapStyleId])

thanks 1 user thanked Joe Payne @ AbleMods LLC for this useful post.
shaharyartiwana25816656 on 2/2/2021(UTC)
shaharyartiwana25816656  
#6 Posted : Tuesday, February 2, 2021 6:22:37 AM(UTC)
shaharyartiwana25816656

Rank: Advanced Member

Groups: Admin, Developers, Registered, HelpDesk, Authorized User
Joined: 10/5/2018(UTC)
Posts: 433

Thanks: 1 times
Was thanked: 36 time(s) in 36 post(s)
Thank you very much!

I am able to reproduce the issue by running these queries.
thanks 1 user thanked shaharyartiwana25816656 for this useful post.
Joe Payne @ AbleMods LLC on 2/2/2021(UTC)
Joe Payne @ AbleMods LLC  
#7 Posted : Tuesday, February 2, 2021 6:34:36 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Advanced Member

Groups: HelpDesk
Joined: 11/9/2018(UTC)
Posts: 156

Thanks: 43 times
Was thanked: 7 time(s) in 7 post(s)
Glad to help
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.