[aur-dev] [PATCH] MySQL unique index on Email column too long

[Lukas Fleischer]

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