View Issue Details

IDProjectCategoryView StatusLast Update
0020465mantisbtdb schemapublic2016-06-12 00:42
Reporterdregad Assigned 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 14:03

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
Affected Issues
0008017, 0020465
mod - admin/schema.php Diff File
mod - core/constant_inc.php Diff File

MantisBT: master 76cb1ad2

2015-12-30 14:03

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
Affected Issues
0008017, 0020465
mod - admin/schema.php Diff File