MetaDataType bug

November Happy Hour will be moved to Thursday December 5th.

AI OnAI Off

Home / Forums / Developers (Main Products) Forum / Problems and bugs /

MetaDataType bug

Andreas J

Vote:

When upgrading to later versions of Episerver, the SQL scripts will include a change for MetaDataTypes. So, if you previously had a somewhat older version, you would have a MetaDataType LongString with the type ntext and length of 16. Any MetaField that used this type, would probably also have a length of 16.

INSERT INTO [dbo].[MetaDataType] ([DataTypeId], [Name], [FriendlyName], [Description], [Length], [SqlName], [AllowNulls], [Variable], [IsSQLCommonType], [DefaultValue]) VALUES (32, N'LongString', N'Long String', N'Meta Data Type', 16, N'ntext', 1, 0, 0, N'''''')

Following a more recent version, the commerce update SQL file (10.0.26.0.sql) includes a statement to change the length of, for example, LongString:

UPDATE MetaDataType SET [Length] = -1,[SqlName] = N'nvarchar', [Variable] = 1 WHERE [Name] = N'LongString'

So, we're now in spot where we have old meta fields with data type LongString and a length of 16, but with a meta data type with type nvarchar and length of 16. Whenever a new meta field is added, it will run the stored procedure [mdpsp_sys_CreateMetaClassProcedure], which generates other stored procedures and therefore also the signatures of these stored procedures.

In our case, it changed this signature:

ALTER procedure [dbo].[mdpsp_avto_LineItemEx_Update]
@ObjectId int,@Language nvarchar(20)=null,@CreatorId nvarchar(100),@Created datetime,@ModifierId nvarchar(100),@Modified datetime,@Retval int out,@f2 bit,@f6 ntext,@f8 ntext,@f4 ntext,@f5 ntext,@f3 ntext,@f7 ntext,@f1 ntext as

... to ...

ALTER procedure [dbo].[mdpsp_avto_LineItemEx_Update]
@ObjectId int,@Language nvarchar(20)=null,@CreatorId nvarchar(100),@Created datetime,@ModifierId nvarchar(100),@Modified datetime,@Retval int out,@f2 bit,@f6 nvarchar(16),@f8 nvarchar(16),@f4 nvarchar(16),@f5 nvarchar(16),@f3 nvarchar(16),@f7 nvarchar(16),@f1 nvarchar(16) as

As you can see, ntext parameters was replaced with nvarchar(16) which means that those parameters will be truncated before they are stored.

This happened the first time we added a new meta field (which triggers [mdpsp_sys_CreateMetaClassProcedure]) after upgrading commerce to 13.25. Probably this can have pretty serious effect.

#248775

Feb 17, 2021 12:07

Quan Mai

Vote:

Hi,

This should have been fixed in 13.25. Can you run mdpsp_sys_CreateMetaClassProcedureAll to see if it fixes the problem for you?

#248782

Feb 17, 2021 14:12

Andreas J

Vote:

@Quan - on the contrary, I think the issue was introduced when the file 10.0.26.0.sql was included, which seems to be 13.25.

Your suggestion would probably only do things worse. Current state, after upgrading to 13.25 (and now 13.28), is that we have meta fields with data type LongString (which changed SQL type from ntext to nvarchar) and with a Length of 16 ( = the length of the MetaDataType when the meta field was created). So, if we would run mdpsp_sys_CreateMetaClassProcedureAll, we would get the issue I described in the first post (ntext parameter turned into nvarchar(16)) on all mdpsp_avto_X_Update SPs, which will truncate all those parameters. This is my experience at least.

Instead, the thing I must do first is to fix all meta fields that is of type LongString and has a Length of 16. The easy way is probably to set the Length to -1 for these cases. When that is done, I can run the SP mdpsp_sys_CreateMetaClassProcedure for that meta class. This will change the parameters from ntext to nvarchar(max).

declare @MetaClassId int
select @MetaClassId = MetaClassId from MetaClass where Name = 'LineItemEx'

update mf set mf.Length = -1
from MetaField mf
inner join MetaClassMetaFieldRelation r on r.MetaFieldId = mf.MetaFieldId 
inner join MetaClass mc on mc.MetaClassId = r.MetaClassId
where mc.MetaClassId = @MetaClassId and mf.DataTypeId = 32 and mf.Length = 16

EXEC mdpsp_sys_CreateMetaClassProcedure @MetaClassId

If I'm right, it means that anyone that has LongString meta fields since before 13.25 will have these issues the next time they add meta fields (or trigger mdpsp_sys_CreateMetaClassProcedure).

#248785

Feb 17, 2021 17:16

Quan Mai

Vote:

I see. It seems to be complicated. I will dig into this when time permits

#248790

Feb 17, 2021 21:04

Giang Tran

Vote:

Hi Andreas,

I think your solution can fix the issue.

You should run update for length = -1 of MetaFied table for DataTypeId = 11 (MetaDataType.Name = 'NText'), DataTypeId = 32 (MetaDataType.Name = 'LongString') and DataTypeId = 33 (MetaDataType.Name = 'LongHtmlString'). So the "WHERE" clause became:

WHERE mc.MetaClassId = @MetaClassId AND mf.DataTypeId IN(11, 32, 33) AND mf.Length = 16

Hope this help you to resolve the issue.

Best regards.

#248859

Feb 19, 2021 3:18

Andreas J - Feb 19, 2021 9:57

Out of curiosity, is this something you will include in a upcoming release as well?

mkh - Nov 11, 2021 8:39

I stumbled upon this issue this week, and thanks to Andreas I can now see what the issue is. Could you please comment on whether you have (or will) include this in a release, as Andreas asked?

Andreas J - Aug 12, 2022 9:35

@mkh The fix was included in the file 10.0.29.0.sql, included in the Episerver Commerce Core nuget package.

This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.