Upgrade failing on 7.0.88.40

[pricerc]

https://service.jiwa.com.au/browse/DEV-3327 suggests this was fixed, but I'm getting:

Code: Select all

System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.RE_Main' and the index name 'IX_RE_Main_SlipNo'. The duplicate key value is (123170-2).


on an upgrade.

I've got the SQL to sort it out, but I thought I should raise it in case there's something that needs fixing.

[Mike.Sheen]

Any chance you can give us the results of the following query BEFORE the upgrade?

Code: Select all

SELECT SlipNo FROM RE_Main WHERE SlipNo LIKE '123170%' ORDER BY SlipNo

The script we use to append -n to the end in the case of duplicates obviously isn't quite smart enough to deal with the scenario you've encountered - we can improve that but It'd be useful to see exactly what dataset you have.

[pricerc]

The offending SlipNo is

123170-2

[Mike.Sheen]

So... I guess the answer to my question is no?

[pricerc]

The only one duplicate in this database, with this version of the upgrade tool (I'm loading up 7.2 SR2).

I think a previous version of your scripts left me with more than that, so there's that at least

[pricerc]

So... I guess the answer to my question is no?

No. That was the answer to your question :

Code: Select all

123170-2

*edit: except it shows up twice

I'm going to do a re-run of the upgrade a bit later today (I've got some pre-upgrade cleanup of dodgy data that I've been scripting), and I can check again on the Jiwa 6 version of the database, but I'm pretty sure that's what the slip number is (I'm sure I remember it as an offender from when I first tried the upgrade about 6 months ago).

[pricerc]

Ok,

So I've restored the pre-upgrade data. This is what we have:

Code: Select all

SlipNo      SlipDate   LastSavedDateTime
123170      2006-04-05 2006-04-11 14:31:05.000
123170-2    2006-04-11 2006-04-11 14:30:28.000
123170      2014-09-29 2014-10-01 11:41:42.000


[pricerc]

considering that:

Code: Select all

      select [SlipNo] from RE_Main
      group by SlipNo
      having count(*) > 1


gives me 3102 rows, you haven't done badly

[pricerc]

Since I'm here talking about upgrades.

The dodgy data I am scripting out is in RA_ShipLines, RA_CreditLines and RA_CreditLineDetails, referencing deleted inventory items. There are just a few, and they're all related to ancient transactions. They were breaking 7.0.42.00880,02385 & 02640.
They can be checked with

Code: Select all

select * FROM RA_ShipLines where not InventoryID in (select InventoryID from IN_Main);

select * from RA_CreditLines where not InventoryID in (select InventoryID from IN_Main);

select * from RA_CreditLineDetails where not InvID in (select InventoryID from IN_Main);


and deleted with

Code: Select all

delete FROM RA_ShipLines output deleted.* where not InventoryID in (select InventoryID from IN_Main);

delete from RA_CreditLines output deleted.* where not InventoryID in (select InventoryID from IN_Main);

delete from RA_CreditLineDetails output deleted.* where not InvID in (select InventoryID from IN_Main);


(just 4 records between the 3 tables for me, involving two InventoryIDs)


I previously had problems with upgrade steps 7.0.64.00530, but that's fine now.

My script also drops a bunch of locally-added indexes (some of which I'm putting back afterwards), and drops all statistics and DTA-created indexes (some of which caused trouble in an earlier attempt; I'm not sure if they still do, but in this case, they're so old that they are probably no longer relevant anyway).

We also had a problem with SettingID clashes between CR_, DB_ and IN_ custom settings (because they didn't used to share a table), which I resolved by making the ID's the DisplayOrder prefixed with the CR_, DB_ and _IN. This had the added benefit of making it easier to code plugin logic that used the IDs.

I'll be changing my script to change just one of the 'problem' RE_Main records, so the standard script will still kick in for all the other ones.

[Mike.Sheen]

Ok,

So I've restored the pre-upgrade data. This is what we have:

Code: Select all

SlipNo      SlipDate   LastSavedDateTime
123170      2006-04-05 2006-04-11 14:31:05.000
123170-2    2006-04-11 2006-04-11 14:30:28.000
123170      2014-09-29 2014-10-01 11:41:42.000


Ok - thanks - I suspected it might have been something like this... just wanted to be sure I wasn't dealing with some other edge case. The script executed during upgrade immediately before the script which produces your error looks like this:

Code: Select all

-- Renumber duplicate GRN No's
UPDATE WorkingTable SET WorkingTable.SlipNo = LTRIM(RTRIM(WorkingTable.SlipNo)) + '-' + CAST(MyTempTable.RowNumber AS VARCHAR(5))
FROM RE_Main WorkingTable
JOIN
   (
      SELECT ROW_NUMBER() OVER(PARTITION BY WorkingTable2.SlipNo ORDER BY WorkingTable2.SlipNo) AS [RowNumber], WorkingTable2.PackSlipID
      FROM RE_Main WorkingTable2
   ) AS MyTempTable
   ON MyTempTable.PackSlipID = WorkingTable.PackSlipID
WHERE WorkingTable.SlipNo IN
   (
   SELECT SlipNo
   FROM RE_Main
   GROUP BY slipNo
   HAVING COUNT(SlipNo) > 1
   )

Which I guess we thought was pretty clever and neat at the time - but sadly doesn't deal with the situation you've presented us.

I've logged this as DEV-7420.