LOL...Nice -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Thursday, February 23, 2012 3:50 PM To: [email protected] Subject: Re: Not Matching Lengths (server hang)
LJ, Correct. We don't pay attention to the current DB size, just the current AR size. So, if you make it longer than the current AR size, we will issue a restructure command to the DB. Doug -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of LJ LongWing Sent: Thursday, February 23, 2012 2:33 PM To: [email protected] Subject: Re: Not Matching Lengths (server hang) So to restructure, I don't need to make my 'new' size larger than the current DB size, just larger than the current ar size? -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Thursday, February 23, 2012 2:52 PM To: [email protected] Subject: Re: Not Matching Lengths (server hang) LJ, Not sure what was causing the problem in your situation. Obviously the answer is that it could have been any number of things.... I just don't know what. As it was a crash, it is obviously something that the system didn't handle well. Definitely submit a bug on the issue -- indicating that the field lengths were different for the crash situation -- and we can see if we can isolate what the problem was. Now as for correcting. If you ever make the length LONGER, we will restructure the column. So, instead of deleting and recreating (where data would be lost), you could have done the following: Change the length to 1 LESS than you desire the length to be. This will not cause the DB to restructure. Change the length to exactly what you desire it to be (so increase by 1). This WILL cause the DB to restructure and recreate the field with the length you want it to be regardless of what the current DB column length is. So, a solution for how to make them match if you want. But, no explanation for the column width problem. As the server didn't crash, a question is whether there was an issue in the DB or in the AR System itself. Either way is bad, but the only way to know is to debug it and find out where the delay was and then what caused it. Anyway, I hope the idea of how to change the length to force a DB restructure was useful. Doug Mueller -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of LJ LongWing Sent: Thursday, February 23, 2012 12:45 PM To: [email protected] Subject: Re: Not Matching Lengths (server hang) Doug....that does make sense...thank you for the in depth analysis....let me ask you this question. I was going through my system doing performance tuning. I found a situation where a Web Service was creating records on output of the call... I make a call to WS and it returns a result to form 'ChildForm'. The WS definition defines a Distinguished Key and Foreign Key. I found that for each record that comes back in the output, it does a lookup using that field combination to determine if it needs to create a new child record, or update the previous record. In some circumstances I found that when the WS output had 500 records, and each query was taking about .2 seconds...it was taking about 100 seconds to create all of the child records because of this query....so I decided to create a compound index on those two fields and significantly increased my performance in these situations. So, while creating these indexes, I got an error on one situation stating that my index was > 255 characters and may cause a problem on some DB's. I then analyzed the data in the columns that I was indexing, and shrunk the size of the field to match the actual size of the data (instead of the default 255 characters of a char field), and re-created the indexes without receiving the warning. So...a few days later I started experiencing server crashes and after several days of troubleshooting, having SQL Logging on at the time of the crash, the last SQL that happened against the DB was query issued by the Java WS Plugin trying to determine if it needed to create a new record or update an existing one...the query was issued, the DB returned an 'OK'...and that's the last thing in the log. The server was still in memory....but all CPU and IO drop to 0, and you cannot connect to the server in any way. Only way to restore the service was to restart it. After discovering the discrepancy between AR length and DB length, I started seeing what I could do to get Remedy to re-synchronize the length....being unable to get it to do it, the final action was to delete and re-create the field. When it was re-created, I handed the system back over to the other developers and haven't had a single crash since. So...you say that it's not an issue, and I truly trust your statement on that mark....but I must then beg the question 'what was causing my crash'? -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Thursday, February 23, 2012 12:46 PM To: [email protected] Subject: Re: Not Matching Lengths 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" ____________________________________________________________________________ ___ 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" ____________________________________________________________________________ ___ 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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
