View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0020465 | mantisbt | db schema | public | 2015-12-31 18:27 | 2016-06-12 00:42 |
Reporter | dregad | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | N/A |
Status | closed | Resolution | fixed | ||
Product Version | 1.3.0-beta.1 | ||||
Target Version | 1.3.0-rc.2 | Fixed in Version | 1.3.0-rc.2 | ||
Summary | 0020465: 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. | ||||
Tags | No tags attached. | ||||
|
|
The suggestion from vboctor was to increase to either
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
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). |
|
@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 ? |
|
Pull request https://github.com/mantisbt/mantisbt/pull/699 |
|
@dregard: |
|
Reopened following @vboctor's note [1] [1] https://github.com/mantisbt/mantisbt/commit/ecd12610576e71764a318d8ead1c753ab7a30d16 |
|
MantisBT: master ecd12610 2015-12-30 14:03 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 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 |