Some ids in the database have inconsistent length values - int(x).

[patrickatwsrn]

Bug report

Summary

I just noticed some inconsistencies in the database.

Most primary key ids use int(10), but some have int(9) or int(11). Some string-based keys also don't have consistant length values (100 or 191) even though they interact with each other.

Observed behavior

• modx_active_users - internalKey - int(9)

• modx_media_sources_contexts - int(11) BUT modx_media_sources - id - int(10)

• modx_media_sources_elements - int(11) BUT modx_media_sources - id - int(10)

  • modx_context_resource - context_key varchar(191) BUT modx_context - key - varchar(100)
  • modx_context_setting - context_key - varchar(191) BUT modx_context - key - varchar(100)

• modx_site_tmplvar_templates - templateid - int(11) BUT modx_site_templates - id - int(10)

• modx_user_settings - user - id(11) BUT modx_users - id - int(10)

Some extras also have inconsistant values (if somebody is interested in a proper bug report, let me know):

• modx_fc_profiles_usergroups - usergroup - int(11)

• modx_fc_sets - id - int(10)

• modx_formalicious_fields - id - int(10) - the other 5 tables have int(11)

• modx_seosuite_social - resource_id(11) - doesn't make sense if the id for modx_site_content is int(10)

• all versionx tables are using int(11)

Expected behavior

Numeric ids should be either int(10) OR int(11) across the board. String ids shold be consistant. varchar(191)

Implications

As far as I understand it doesn't seem to matter at the end of the day.

If I'm wrong here and in fact onto something that might become a problem, it would be great if somebody could share his knowledge about this topic.

Cheers,

Patrick

Edit: If I'm reading the specs right, the length of an int-base id could be 1 and still fit any number up to 4294967295 (unsigned). The length only says how integers are displayed in external applications:

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as the command-line client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20). Source: https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch04.html

[opengeek]

All integer fields representing primary and foreign keys should be unsigned INT fields, which would effectively be int(11), regardless of the specified "width." The consistency definitely should be addressed with regards to the unsigned attribute and width. But it is effectively not a problem in real-world usage. Keep this open, and let's address it.