sys_journal_field max length is not respected

[david631]

The "Max length" of the "value" field in the "sys_journal_field" table is "8,000". This field was last updated on Jan. 13, 2017. Querying the database for sys_journal_field records created at or after Jan. 14, 2017 shows that the real max length of the "value" field in this table is 356,103. The sys_id of the sys_journal_field record in question reveals this journal was imported via an import script from a different system. Is it possible that entries created via script ignore the max length limit set for this field?

This is the script I used to pull the length and sys_id of the journal with the longest value in the database:

var journalRecord = new GlideRecord('sys_journal_field');
journalRecord.addNotNullQuery('value');
journalRecord.addEncodedQuery("sys_created_on>=javascript:gs.dateGenerate('2017-01-14','00:00:00')");
journalRecord.query();

var maxLengthValue = 0;
var journalSysId = '';

while (journalRecord.next()) {
  if (!gs.nil(journalRecord.getValue('value'))) {
    var lengthValue =  journalRecord.getValue('value').length;
    if (maxLengthValue < lengthValue) {
      maxLengthValue = lengthValue;
      journalSysId = journalRecord.getValue('sys_id');
    }
  }
}

gs.info("The longest journal in the system has the length {0} and the sys_id {1}", maxLengthValue, journalSysId);

[Erik Stolberg · Accepted solution]

"Provides a logical limit for the size of string fields to determine how the system displays them in the user interface and how to map them to physical database data types."

"The system maps the field length to the closest physical data type available on the database."

The sys_journal_field.value is of Dictionary XML type String, which in MySQL is MEDIUMTEXT, which has a maximum length of 16,777,215 characters.

So sys_dictionary.max_length isn't a hard limit as much as a physical data type determining factor. I've seen description fields on tasks where they are populated from the inbound email body and the character counter shows "-15435 characters remaining of 4000 characters" and none of the data is ever truncated from the field.

If you want to fully limit the character count, you would have to write a client script or maybe a before business rule to get the character count and abort submission/database activity.