SQL upgrade script 4.10 to 4.20 Failed with 'ALTER TABLE ALTER COLUMN SentOnUtc failed'

1 month ago
Trying to upgrade from 3.90 to 4.20 by running the 3.9 to 4.0 script, then 4.0 to 4.1, and then 4.1 to 4.2 script. The first two scripts run with out any errors but the 4.1 to 4.2 script fails with the following error:

(1 row affected)
Msg 5074, Level 16, State 1, Line 2657
The index 'idx_DCh_43_42_QueuedEmail' is dependent on column 'SentOnUtc'.
Msg 4922, Level 16, State 9, Line 2657
ALTER TABLE ALTER COLUMN SentOnUtc failed because one or more objects access this column.


Please help me figure out what I need to do to get it working.
1 month ago
The upgrade instructions say:

----------------------
(upgrading from 3.X to 4.X): If you want to upgrade from a version 3.90 to the latest version, you would need to install 4.00 first (over the existing database), run the 3.90 to 4.00 migration SQL script, and then upgrade to 4.10, 4.20 etc

https://docs.nopcommerce.com/user-guide/installing/upgrading.html
-------------------

If I have an existing database full of customers and data, how do I 'Install v4.0 OVER THE TOP OF THE EXISTING DATABASE without losing all my data?
1 month ago
I have not done it but you can try and see if that is what they mean

Take a backup of the 3.9 Database

Run the Install
Dont select the create database option
Enter the existing database name, etc
Dont select the create Sample Data option
1 month ago
Thanks!

I tried that and when I point the 4.20 installer at the existing 3.90 database, the installer runs but then throws the error:

     "Setup failed: Sequence contains more than one element"

So I go the \App_Data\Settings.txt file from the 3.90 install and put it in the \App_Data\ directory and re-named it ".dataSettings.json" which points to the same database.

However, when I run the installer again, I get the same setup failed message.

Not sure how to proceed installing the v4.2 database over the top of the 3.90 database without losing all my customer info while getting the install script to run without failing.

Man, this is such a pain and the both the install and upgrade process is so poorly documented that I might have to move to wordpress and WooCommerce instead.

The current installation guide is so sad that it doesn't even tell you the path to go to run the installer and none of the upgrade steps that are provided work.
1 month ago
matrix1000 wrote:
(upgrading from 3.X to 4.X): If you want to upgrade from a version 3.90 to the latest version, you would need to install 4.00 first (over the existing database), run the 3.90 to 4.00 migration SQL script, and then upgrade to 4.10, 4.20 etc


Its a bit confusing - I was thinking it meant

You run the 3.9->4.0 script to upgrade database to 4.0
Then run the 4.0 installer on the 4.0 database
- I just checked that this creates dataSettings.json from setting.txt
- Not sure what else it might do
Then you should be able to run the v4.0 website on this database as an interim check
Then continue on with the scripts 4.0->4.1, 4.1->4.2
1 month ago
Thanks!

It wont run the installer because the error basically says that it wants to run the database script but it sees existing tables in the database so it errors out.
1 month ago
RE:  "...The index 'idx_DCh_43_42_QueuedEmail' ..."

That's an unusual index name; not standard nopC naming convention.  3.90 and 4.00  did not have an index on SentOnUtc.  I don't have 4.10 installed thus can't tell you if it does...   I'd say just DROP the index.   4.20 does have index on that column.  If you don't see it after upgrade, then create it:

CREATE NONCLUSTERED INDEX [IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended] ON [dbo].[QueuedEmail]
(
  [SentOnUtc] ASC,
  [DontSendBeforeDateUtc] ASC
)
INCLUDE (   [SentTries])