[aur-dev] [PATCH] MySQL unique index on Email column too long
Using unique indexes on varchar fields with a character count of more than 255 on InnoDB tables with utf8 encoding produces an error in MySQL. Fixes FS#47038 Signed-off-by: Stefan Auditor <stefan.auditor@erdfisch.de> --- schema/aur-schema.sql | 2 +- upgrading/4.2.0.txt | 6 ++++++ 2 files changed, 7 insertions(+), 1 deletion(-) diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index 98e8be0..27cee7a 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -25,7 +25,7 @@ CREATE TABLE Users ( AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, Username VARCHAR(32) NOT NULL, - Email VARCHAR(256) NOT NULL, + Email VARCHAR(255) NOT NULL, HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0, Passwd CHAR(32) NOT NULL, Salt CHAR(32) NOT NULL DEFAULT '', diff --git a/upgrading/4.2.0.txt b/upgrading/4.2.0.txt index c195f41..d2d8f3c 100644 --- a/upgrading/4.2.0.txt +++ b/upgrading/4.2.0.txt @@ -15,3 +15,9 @@ CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides); ---- ALTER TABLE Users MODIFY Email VARCHAR(256) NOT NULL; ---- + +3. Resize the email address field: + +---- +ALTER TABLE Users MODIFY Email VARCHAR(255) NOT NULL; +---- -- 2.6.2
On Wed, 11 Nov 2015 at 14:28:31, Stefan Auditor wrote:
[...] diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index 98e8be0..27cee7a 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -25,7 +25,7 @@ CREATE TABLE Users ( AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, Username VARCHAR(32) NOT NULL, - Email VARCHAR(256) NOT NULL, + Email VARCHAR(255) NOT NULL,
Given that we need to change the size of this field anyway, it might make sense to further reduce it to 254 characters, see e.g. [1].
HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0, Passwd CHAR(32) NOT NULL, Salt CHAR(32) NOT NULL DEFAULT '', diff --git a/upgrading/4.2.0.txt b/upgrading/4.2.0.txt index c195f41..d2d8f3c 100644 --- a/upgrading/4.2.0.txt +++ b/upgrading/4.2.0.txt @@ -15,3 +15,9 @@ CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides); ---- ALTER TABLE Users MODIFY Email VARCHAR(256) NOT NULL; ---- + +3. Resize the email address field: + +---- +ALTER TABLE Users MODIFY Email VARCHAR(255) NOT NULL; +---- [...]
These are instructions for people that upgrade from 4.1.0 to 4.2.0. It makes no sense to tell them to change the field size to 256 and immediately change it to something lower afterwards. Please adjust the already existing query instead. Thanks! [1] https://www.rfc-editor.org/errata_search.php?eid=1690
Using unique indexes on varchar fields with a character count of more than 255 on InnoDB tables with utf8 encoding produces an error in MySQL. Also as per https://www.rfc-editor.org/errata_search.php?eid=1690 it seems that the maximum length for email addresses is limited to 254 characters. Fixes FS#47038 Signed-off-by: Stefan Auditor <stefan.auditor@erdfisch.de> --- schema/aur-schema.sql | 2 +- upgrading/4.2.0.txt | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index 98e8be0..315a75c 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -25,7 +25,7 @@ CREATE TABLE Users ( AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, Username VARCHAR(32) NOT NULL, - Email VARCHAR(256) NOT NULL, + Email VARCHAR(254) NOT NULL, HideEmail TINYINT UNSIGNED NOT NULL DEFAULT 0, Passwd CHAR(32) NOT NULL, Salt CHAR(32) NOT NULL DEFAULT '', diff --git a/upgrading/4.2.0.txt b/upgrading/4.2.0.txt index c195f41..1f92ec5 100644 --- a/upgrading/4.2.0.txt +++ b/upgrading/4.2.0.txt @@ -13,5 +13,5 @@ CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides); 2. Resize the email address field: ---- -ALTER TABLE Users MODIFY Email VARCHAR(256) NOT NULL; +ALTER TABLE Users MODIFY Email VARCHAR(254) NOT NULL; ---- -- 2.6.2
On Thu, 12 Nov 2015 at 09:34:23, Stefan Auditor wrote:
Using unique indexes on varchar fields with a character count of more than 255 on InnoDB tables with utf8 encoding produces an error in MySQL. Also as per https://www.rfc-editor.org/errata_search.php?eid=1690 it seems that the maximum length for email addresses is limited to 254 characters.
Fixes FS#47038
Signed-off-by: Stefan Auditor <stefan.auditor@erdfisch.de> --- schema/aur-schema.sql | 2 +- upgrading/4.2.0.txt | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) [...]
Applied, thanks!
participants (2)
-
Lukas Fleischer
-
Stefan Auditor