Good answer… A takeaway from this is - database restructuring is to be avoided, unless necessary. (Which I 100% agree - especially for a production system)
-John On Thu, Feb 23, 2012 at 1:46 PM, Mueller, Doug <[email protected]> wrote: > Everyone, > > Just a couple of comments on this thread (including lots of comments that > have > come in over the past few hours). > > First, it is not a surprise to us that there are differences in lengths in > the > DB and in the Remedy length under some conditions. This is actually a > designed > behavior of the system (which I will explain). There are other conditions > under where a difference is benign -- but not expected. Finally, there > may be > conditions where a difference is NOT benign and is definitely not expected. > > First, the majority of cases, the field length in the DB will match the > field > length in the AR System definition. > > Now, in the case of mismatches.... > > For those cases where the DB length is GREATER than the length in the AR > System > definition, this is a situation that is expected and as designed. It > occurs > when you have a character field that has a maximum length such that a > varchar > or nvarchar or equivalent type in the database you are using is created > (generally 4000 to 8000 bytes in the currently supported databases) and you > make a change to the AR System definition to make it SHORTER. In this > case, > we shorten the length definition at the AR System level but do not > restructure > the database. Several reasons for this. One, the database stores varchar > and > nvarchar (and the like) types using only the space you need. So, it does > not > take the full length definition. This means that the same amount of space > is > used in the database for both lengths -- it is about the data content. > Two, it > saves a DB restructure operation from being performed to restructure the > column > to match the new length. So, the end result is a more efficient operation > with > no downside from the perspective of data storage. It also has a side > effect of > not truncating data for cases where customers have changed the length, > realized > that it is not correct, and then made it bigger again. > > NOTE: If you make the length LONGER -- even by 1 byte -- from what the AR > System > thinks it currently is (regardless of what the DB length actually is as > that > length may be longer from a previous shortening of the length), we DO > cause a > DB restructure and will change the length of the DB column to match the AR > System definition. > > So, for systems that have been around a while and have lots of different > upgrades and customizations that have been performed, you may end up with > quite > a few cases where the AR System length is shorter than the DB length. All > interaction with the system and create/modify/merge operations will enforce > the AR System length for all new records. > > > The second scenario is join forms. We do have field limit definitions for > join > fields, but many of the characteristics are from the underlying field. > This > includes the field type and length and other things. But, in the metadata > tables, we copy the definition of the underlying fields into the join > definition > when we create the join. > > I suspect you are seeing the field length difference on join fields as an > issue > where the underlying field has changed length after the join was created. > That > value is NOT used in the metadata for the join field -- yes, it is there, > but it > is not used. The actual value is the value on the underlying field > definition > not on the field at the join level. > > Now, this is the benign difference. It is different but it doesn't matter > as > the value has no meaning in the join. Sure, the system should be > propogating > that change as a "documentation" change in the metadata, but as the > metadata are > internal tables for internal to the system use, it probably was not > thought to > be worth the time and energy to propagate a piece of information that > didn't > matter. It does not cause any runtime or definition time issue. It will > be > visible only to someone trying to deep dive and interpret the metadata > tables > directly. > > Finally, we come to the situation where if you have it is definitely NOT > expected and can cause odd errors in the system (although it should not > cause > crashes or hangs but it can definitely cause odd SQL errors). > > If you have a situation where the AR System length is LONGER than the DB > length > for a field of type char/varchar/nvarchar and the field is a regular field > and > not a join or a view form, this is not as expected. This would be an > inconsistency that we cannot account for and where there could be issues > generating odd SQL errors during runtime. Again, I would expect that to > be the > worst result, not a crash or hang but just odd errors when something is > longer > than the DB length but not than the AR length (and some DBs would just > quietly > truncate the value to the DB length). > > > I hope this helps to clarify what you should see in the metadata tables if > you > do start playing around with our internal definitions. One situation that > is > fully as expected (shorter AR System length that DB length). One situation > where the value doesn't really matter and is not really used (AR System > length > different than underlying table length when the field is on a join form -- > look > at the length of the actual field that the join field points to as that is > the > real length). And, unclear whether any example shows this case, One > situation > that is an issue if it occurs where the DB length is SHORTER than the AR > System > length. > > Hopefully, this explanation helps clarify what you are seeing and offers an > explanation about why it is all OK -- unless that is you are seeing the > third > situation and then we should take a look at how that could have happened > on your > system (and a quick workaround to simply increase the length of the field > by 1 > byte which forces the AR System to change the length to that new length > and then > to reduce the length by 1 byte to get back your original restriction (and > the > DB length will be 1 more than your AR System definition). > > Doug Mueller > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of LJ LongWing > Sent: Thursday, February 23, 2012 9:20 AM > To: [email protected] > Subject: Not Matching Lengths > > List, > Our Dev server started crashing a few days ago and we had a VERY hard time > trying to identify the cause. After several days of trial and error, one > of > the developers on the team noticed that a field length in the DB was not > the > same as a field length in Remedy. After further troubleshooting it was > found that the server stopped responding after doing a query/insert into > that table using that field ID....the only 'fix' I could find to get them > back in sync was to delete the field and re-create it. We haven't had the > server crash since I did that....so it got me thinking that there might be > other fields with similar problems, so I started looking...I was shocked by > what I found and want to run it by some other people on the list to see if > they come across similar results. The Query I have provided below is > running just fine on SQL Server 2008 R2...I don't know the equivalent for > Oracle...but I would like to know if other people find a large amount of > columns where the two values don't match. With the below Query I came up > with 382 mismatches, many of which are on system generated forms, but a > majority of which are on my custom forms. This is an OLD system...it's > been > around since at least 6.0...and upgraded and modified in almost every major > revision since its inception. We are currently on 7.6.4 SP1....and I can > confirm with a form that I created yesterday...I created a 255 character > field...then shrunk it to 30, and it is on my list of mismatches....curious > about your results...please reply back :) > > select a.name, f.fieldName, fc.maxLength as 'RemedyMaxLength', > c.CHARACTER_MAXIMUM_LENGTH as 'DBMaxLength' > from arschema a > inner join field f on f.schemaId = a.schemaId > inner join field_char fc on a.schemaId = fc.schemaId and f.fieldId = > fc.fieldId > inner join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = > 'T'+CAST(a.schemaId AS char) AND 'C'+CAST(fc.fieldId as char) = > c.column_name > where fc.maxLength != c.CHARACTER_MAXIMUM_LENGTH > AND c.CHARACTER_MAXIMUM_LENGTH != 2147483647 > and f.fieldId != 1 > > Name Field Name RemedyMaxLength DBMaxLength > AR System Currency Label Catalog Short Description 4 254 > AR System Currency Ratios Short Description 4 254 > Alert Events UNUSED 5 254 > Server Events Unused_Descr 3 254 > Server Statistics Short Description 3 254 > User Password 30 255 > User Computed Grp List 255 4000 > AR System Administrator Preference Short Description 128 254 > AR System User Central File Short Description 128 254 > AR System User Preference Short Description 128 254 > ReportSelection OriginalFormServer 128 254 > ReportType Short Description 128 254 > FB:Alarm Events Monitor ID 15 254 > FB:History Short Description 128 254 > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax [email protected] _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
