Upgrade R11SR1 to R12 error

[AbleMods]

I'm seeing a SQL schema error in the error log after upgrading a client yesterday:

Could not validate ac_transactions Updates
Exception: The index '_dta_index_ac_Transactions_48_837578022__K3_1_2_4_5_6_7_8_9_10_11_12_13_14_15_16_17' is dependent on column 'AVSResultCode'. ALTER TABLE ALTER COLUMN AVSResultCode failed because one or more objects access this column. Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at CommerceBuilder.Licensing.h.b(SqlConnection A_0) at CommerceBuilder.Licensing.h.f(SqlConnection A_0)

I've never seen this before. Any suggestions on a fix?

I suspect something added by the SQL Database Profile/Analyzer has now created an unexpected constraint. That would explain the odd _dta_index name. Maybe I should just delete that index?

[Katie]

I suspect something added by the SQL Database Profile/Analyzer has now created an unexpected constraint

Never seen this error before. Did this start happening right after you ran SQL Analyzer? If so, then yes, I would suspect that something changed that AC doesn't like.

[AbleMods]

No it didn't happen right away. It started right after we deployed the R12 upgrade.

I think the schema upgrade code is trying to deploy a schema change, but the indexes added by the analyzer long ago are interferring with it. At least that's my theory anyways.

[Katie]

We did change the AVSResultCode column. I don't know why analyzer would want to add an index to this field though. Seems odd...

Size and data type of three fields changed for c_Transactions table from CHAR(1) to VARCHAR(16)

AVSResultCode CHAR(16) NULL
CVVResultCode CHAR(16) NULL
CAVResultCode CHAR(16) NULL

Is this enough info to get it fixed?

Thanks

[AbleMods]

I think it will be, thanks Katie.

I deleted that index this morning and cleared the error log. Zero errors since, but that's not a sure sign. I think the schema updates only fire during the app startup, so we'll have to wait until the app pool recycles to know for certain.

If log is still clear tomorrow after, then deleting the index was the solution.

Not sure if the schema updates could detect conflicting indexes like, probably would be helpful to look into though. Can't imagine I'm the only guy out there running SQL Profiler against Able databases to improve site performance.

Seems like I'm always bringing the weird stuff to you

[compunerdy]

Weirdo

[Katie]

Lol...

I'm not a big fan of the auto-upgrade thing. I also feel like we really could do a better job with notifications and any database issues like the one you ran into. It would make our lives easier for sure.

Thanks,
Katie

[AbleMods]

I'm not a big fan of the auto-upgrade thing

At first, I wasn't thrilled with it. I (personally) like having control over exactly when the database schema is updated because it's a logical mental step in the overall process of a site upgrade.

But, after having done several Gold upgrades now, I have to say it is rather convenient. It's one less step I have to think about. And for the most part, it's been pretty darn reliable in making schema changes to a huge variety of customized installs. So once you consider how varied my installs are, it's really a pretty well-designed feature. Nobody can write something that works EVERY time in EVERY situation. The schema update feature is really a solid piece of work imho.

A separate log for schema upgrades would definitely be useful in certain circumstances. Right now, everything gets smashed into a single ac_ErrorLogs table and it's really, really hard to notice anything important in there anymore. Especially on a site that's customized and the developer didn't make an effort to clean up sloppy code or over-utilized the error log as their debug tool.

7.x to Gold upgrades do display any upgrade errors in the browser, but that's your only shot at seeing them - if somehow you accidentally make that popup go away, the details are lost forever so far as I know. Not likely, but I'd sure feel better knowing if that popup disappeared then I have a log file somewhere with the same info.

[Katie]

A separate upgrade log - now that's a good idea. I'll open a request in Jira and see if the developers agree.

[corgalore]

Katie,

I was researching Able upgrades when I came across this thread.

I just upgraded a site by installing a fresh copy of R12 and logged in against a database that was previously running R7.

Did the software automatically upgrade the schema from R7 all the way to R12? The admin footer now shows AbleCommerce GoldR12SR1 (build 9266) , but I need to know if any schema is now missing.

Thanks!

[nadeem]

Did the software automatically upgrade the schema from R7 all the way to R12?

Yes, your upgrade gone perfect. All the schema related updates being made behind the scenes by the auto upgrade service. So you don't have to worry about any missing schema.

[corgalore]

Nadeem,

Okay, good. I was worried that it only made the schema changes from R11 to R12.

[nadeem]

BTW, even if you upgrade any of the AbleCommerce 7.0.3 through 7.0.7 to Ablecommerce Gold, the upgrade service will take care of all the schema changes.