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 AbleCommerce Gold forum. Please use your AbleCommerce username and password to Login. New Registrations are disabled.

Notification

Icon
Error
9.0.3 db upgrades that have SQL statistics
Options
Go to last post Go to first unread
Previous Topic Next Topic
Joe Payne2  
#1 Posted : Thursday, January 21, 2021 3:03:45 PM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 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.
Back to top

Wanna join the discussion?! Login to your AbleCommerce Forums forum account. New Registrations are disabled.
Back to top  
shaharyartiwana25816656  
#2 Posted : Friday, January 22, 2021 4:35:43 AM(UTC)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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!
Back to top
Joe Payne2  
#3 Posted : Friday, January 22, 2021 7:59:07 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 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
Back to top
shaharyartiwana25816656  
#4 Posted : Tuesday, February 2, 2021 5:12:32 AM(UTC)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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.
Back to top
Joe Payne2  
#5 Posted : Tuesday, February 2, 2021 5:21:43 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 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])
Back to top
thanks 1 user thanked Joe Payne2 for this useful post.
shaharyar on 2/2/2021(UTC)
shaharyartiwana25816656  
#6 Posted : Tuesday, February 2, 2021 6:22:37 AM(UTC)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 post(s)
Thank you very much!

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

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
Glad to help
Back to top
Users browsing this topic
Guest (6)
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.

Privacy Policy | Powered by YAF.NET 2.2.4.14 | YAF.NET © 2003-2024, Yet Another Forum.NET
This page was generated in 0.217 seconds.