View Issue Details

IDProjectCategoryView StatusLast Update
0020465mantisbtdb schemapublic2016-06-12 00:42
ReporterdregadAssigned Todregad 
PrioritynormalSeverityminorReproducibilityN/A
Status closedResolutionfixed 
Product Version1.3.0-beta.1 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0020465: Reduce size of username and email fields to allow utf8mb4 charset
Description

Schema update step 196 updated the user table's username and email fields to 255 chars in length (see 0008017).

This is preventing use of utf8mb4 charset, as MySQL is not able to cope with the field's length and triggers the following error

1071: Specified key was too long; max key length is 767 bytes

Following the discussion in 0020431, we'll reduce the column's size to 191 chars. This should satisfy the original requirement to increase the field's size to allow storing email addresses (0008017) as well as keep MySQL / InnoDB happy with 4-byte characters.

TagsNo tags attached.

Relationships

related to 0008017 closeddregad Increase the size of the username field 
child of 0020431 assigneddregad Use utf8mb4 charset for new MySQL installations 

Activities

atrol

atrol

2016-01-01 04:31

developer   ~0052217

This should satisfy the original requirement to increase the field's size to allow storing email addresses
It doesn't, as mentioned by libregeek see 0008017:0014826

dregad

dregad

2016-01-01 16:52

developer   ~0052222

The suggestion from vboctor was to increase to either

  • RFC 2821 max length = 320 chars = 64 (local-part) + 1 (@) + 255 (domain), or
  • maximum size allowed for a db varchar

As explained before, we are facing a DB restriction due to the unique index, 4-byte unicode chars and InnoDB limitation, therefore we can't have a varchar longer than 191 chars without incurring the risk of seeing duplicate keys. I think it's preferable to prevent use of an overly long e-mail

Considering libregeek's statement

Combining these two will make up 319characters which may not be necessary in the near future.

and the fact that his original request was to increase the field to varchar(64), I would assume it is acceptable to set it to 191 chars even if it's not fully compliant with the RFC.

I also have personally never seen an e-mail address longer than 70 chars, and that was mostly due to the "local-part" bit, which is limited to 64 chars).

As a side note, I am not aware of any 255 char limit on varchars fields in any supported RDBMS (MySQL limit is 65535 bytes for the entire row; Oracle is 4000 bytes; PostgreSQL is 1 GB although the actual limitation may be lower; DB2 is 32704 bytes).

dregad

dregad

2016-01-01 16:58

developer   ~0052223

@libregeek, is there any issue from your end if the maximum size of the username field is 191 chars instead of 255 as per the original fix for 0008017 ?

dregad

dregad

2016-01-01 19:25

developer   ~0052229

Pull request https://github.com/mantisbt/mantisbt/pull/699

libregeek

libregeek

2016-01-05 04:19

reporter   ~0052247

@dregard:
191 is perfectly OK for my environment. As I said earlier, all our email addresses are less than 64 characters.

dregad

dregad

2016-06-11 05:05

developer   ~0053315

Reopened following @vboctor's note [1]

[1] https://github.com/mantisbt/mantisbt/commit/ecd12610576e71764a318d8ead1c753ab7a30d16

Related Changesets

MantisBT: master ecd12610

2015-12-30 19:03:40

dregad

Details Diff
Reduce user.username column size to 191 chars

The varchar(255) implemented to address issue 0008017 was causing issues
with utf8mb4 encoding due a limitation in the size of indexes in MySQL.

Fixes 0020465
mod - admin/schema.php Diff File
mod - core/constant_inc.php Diff File

MantisBT: master 76cb1ad2

2015-12-30 19:03:40

dregad

Details Diff
Reduce email and realname columns to 191 chars

The varchar(255) implemented to address issue 0008017 was causing issues
with utf8mb4 encoding due a limitation in the size of indexes in MySQL.

Fixes 0020465
mod - admin/schema.php Diff File

Issue History

Date Modified Username Field Change
2015-12-31 18:27 dregad New Issue
2015-12-31 18:27 dregad Relationship added child of 0020431
2015-12-31 18:27 dregad Assigned To => dregad
2015-12-31 18:27 dregad Status new => assigned
2015-12-31 18:27 dregad Relationship added related to 0008017
2016-01-01 04:31 atrol Note Added: 0052217
2016-01-01 16:52 dregad Note Added: 0052222
2016-01-01 16:58 dregad Note Added: 0052223
2016-01-01 19:25 dregad Note Added: 0052229
2016-01-05 04:19 libregeek Note Added: 0052247
2016-06-10 19:02 dregad Changeset attached => MantisBT master ecd12610
2016-06-10 19:02 dregad Status assigned => resolved
2016-06-10 19:02 dregad Resolution open => fixed
2016-06-10 19:02 dregad Fixed in Version => 1.3.0-rc.2
2016-06-11 05:05 dregad Status resolved => assigned
2016-06-11 05:05 dregad Resolution fixed => reopened
2016-06-11 05:05 dregad Fixed in Version 1.3.0-rc.2 =>
2016-06-11 05:05 dregad Summary Reduce size of username field to allow utf8mb4 charset => Reduce size of username and email fields to allow utf8mb4 charset
2016-06-11 05:05 dregad Description Updated View Revisions
2016-06-11 05:05 dregad Note Added: 0053315
2016-06-11 11:44 dregad Changeset attached => MantisBT master 76cb1ad2
2016-06-11 11:44 dregad Status assigned => resolved
2016-06-11 11:44 dregad Fixed in Version => 1.3.0-rc.2
2016-06-11 11:59 atrol Resolution reopened => fixed
2016-06-12 00:42 vboctor Status resolved => closed