Upgrade failing on 7.0.88.40  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 9:11 am

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby Mike.Sheen » Fri Apr 12, 2019 11:19 am

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 12:48 pm

The offending SlipNo is

123170-2
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby Mike.Sheen » Fri Apr 12, 2019 12:50 pm

So... I guess the answer to my question is no?
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 12:51 pm

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 :)
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 12:54 pm

Mike.Sheen wrote: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).
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 1:20 pm

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
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 1:22 pm

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 :)
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40

Postby pricerc » Fri Apr 12, 2019 1:40 pm

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Upgrade failing on 7.0.88.40  Topic is solved

Postby Mike.Sheen » Fri Apr 12, 2019 3:55 pm

pricerc wrote: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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Next

Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 6 guests

cron