Help needed with the MySQL max index length problem for Django 1.10

[Tim Graham]

I merged the often requested increase of User.username max_length to 254 characters [1] a few weeks ago, however, the ticket was reopened pointing out this issue:

"This patch breaks on MySQL installations using the utf8mb4 charset, because it breaks the index length limit - it comes out at a maximum of 254 * 4 = 1016 bytes whilst by default InnoDB can only index 767 bytes. I found this because I am using utf8mb4 in django-mysql's tests.

Django encourages using the utf8 charset (3 bytes per character - cannot store emojis), although there has been some discussion for moving to utf8mb4 in #18392. One can index 254 character utf8mb4 fields in MySQL by using a couple settings as described in that ticket, Django could enforce those, or the field could be changed to just 191 characters instead which is the maximum indexable (767 // 4)."

Do we have any MySQL enthusiasts willing to champion a patch (or at least a decision design about the best way to proceed) for #18392 [2] to resolve this?

[1] https://code.djangoproject.com/ticket/20846
[2] https://code.djangoproject.com/ticket/18392

[Collin Anderson]

Hi All,

I finally looked at this more today. I started working on the INDEX (col1(191)) solution from #18392, but unfortunately I don't think we can use that solution in this case because it's a UNIQUE index. (I still think it's best solution for non-unique indexes.)

I think these are our options (in my humble order of preference), none of them ideal:

1. Make username max_length=191 or some other nice number less 191.

2. Revert completely back to username max_length=30

3. Tell mysql folks they can only use the insecure[1] version of utf8 unless do some really complex reconfiguration (see "Hardest" solution in the article [2]) to get utfmb4 support. Based on the fact that they're using mysql and not PostgreSQL in the first place, many mysql users are probably are unable to make the needed changes.

The good news is that mysql 5.7.7 (which, will _start_ to get a lot of real use in 16.04) changes some defaults[3] to make the "hardest" solution easier (just need to set ROW_FORMAT=DYNAMIC). MariaDB hasn't changed the default [4]. I hope they do before RHEL/CentOS 8. Even then it's a long wait before most people have it.

Collin

[1] https://youtu.be/qFfjJ8pOrWY?t=2534

[2] https://serversforhackers.com/mysql-utf8-and-indexing

[3] http://bugs.mysql.com/bug.php?id=68453#c430229

[4] https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_large_prefix

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ceb190b5-5218-446c-a6a0-0aea75e7fd6b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim Graham]

Ugh, I guess I'm in favor of max_length=191. It'll just be awkward to explain that one in the docs.

[Aymeric Augustin]

At that point, I'd prefer picking an arbitrary length that makes sense for the underlying data rather than one based on MySQL's current limitations.

Name length sounds like an reasonable proxy for username length. A quick Google search turns up http://www.historyrundown.com/top-5-people-with-the-longest-names/

If we skip the pathological cases — e.g. people with one name per letter of the alphabet — the first sensible name in that list is Picasso with 122 characters: "Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso"

The original ticket, #20846, discussed a length between 75 and 150.

The argument for 254 was "email as username". In that case, Django's native User with distinct username and email fields isn't appropriate. A custom user model storing the the username/email in a unique EmailField is better. MySQL users can specify the appropriate max_length — which we should document — there.

I'd jut give username max_length=120. (Sorry, Picasso.)

-- 

Aymeric.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/78e114b4-9595-484a-bc33-f87635a2e7c0%40googlegroups.com.

[Collin Anderson]

I propose 150 to err on the longer side: https://github.com/django/django/pull/5891

[Collin Anderson]

Though wouldn't mind 180 either.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ae4782fd-0f78-4b8a-9a94-af987e07e400%40googlegroups.com.

[Florian Apolloner]

On Monday, December 21, 2015 at 5:32:43 PM UTC+1, Tim Graham wrote:

"This patch breaks on MySQL installations using the utf8mb4 charset, because it breaks the index length limit - it comes out at a maximum of 254 * 4 = 1016 bytes whilst by default InnoDB can only index 767 bytes. I found this because I am using utf8mb4 in django-mysql's tests.

Can the original Author of that quote shed more light on "breaking"? To my knowledge it just limits the size of the index to the first 767 byte and MySQL will give you a warning, so we should be fine, no?

[Collin Anderson]

I think mysql used to just give a warning and maybe now it's an error? It can currently be reproduced with just a blank project (using django master, mysql 5.5.46-0ubuntu0.14.04.2):

mysql -e'create database mb4test charset utf8mb4'

django-admin startproject mb4test

cd mb4test

vi mb4test/settings.py  # set up db settings

./manage.py migrate

Operations to perform:

  Apply all migrations: auth, admin, sessions, contenttypes

Running migrations:

  Rendering model states... DONE

  Applying contenttypes.0001_initial... OK

  Applying auth.0001_initial... OK

  Applying admin.0001_initial... OK

  Applying admin.0002_logentry_remove_auto_add... OK

  Applying contenttypes.0002_remove_content_type_name... OK

  Applying auth.0002_alter_permission_name_max_length... OK

  Applying auth.0003_alter_user_email_max_length... OK

  Applying auth.0004_alter_user_username_opts... OK

  Applying auth.0005_alter_user_last_login_null... OK

  Applying auth.0006_require_contenttypes_0002... OK

  Applying auth.0007_alter_validators_add_error_messages... OK

  Applying auth.0008_alter_user_username_max_length...Traceback (most recent call last):

  File "/home/collin/django1.10/django/db/backends/utils.py", line 64, in execute

    return self.cursor.execute(sql, params)

  File "/home/collin/django1.10/django/db/backends/mysql/base.py", line 112, in execute

    return self.cursor.execute(query, args)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 226, in execute

    self.errorhandler(self, exc, value)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler

    raise errorvalue

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 217, in execute

    res = self._query(query)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 378, in _query

    rowcount = self._do_query(q)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 341, in _do_query

    db.query(q)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/connections.py", line 280, in query

    _mysql.connection.query(self, query)

_mysql_exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "./manage.py", line 10, in <module>

    execute_from_command_line(sys.argv)

  File "/home/collin/django1.10/django/core/management/__init__.py", line 349, in execute_from_command_line

    utility.execute()

  File "/home/collin/django1.10/django/core/management/__init__.py", line 341, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/home/collin/django1.10/django/core/management/base.py", line 290, in run_from_argv

    self.execute(*args, **cmd_options)

  File "/home/collin/django1.10/django/core/management/base.py", line 339, in execute

    output = self.handle(*args, **options)

  File "/home/collin/django1.10/django/core/management/commands/migrate.py", line 177, in handle

    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)

  File "/home/collin/django1.10/django/db/migrations/executor.py", line 92, in migrate

    self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)

  File "/home/collin/django1.10/django/db/migrations/executor.py", line 121, in _migrate_all_forwards

    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)

  File "/home/collin/django1.10/django/db/migrations/executor.py", line 198, in apply_migration

    state = migration.apply(state, schema_editor)

  File "/home/collin/django1.10/django/db/migrations/migration.py", line 123, in apply

    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)

  File "/home/collin/django1.10/django/db/migrations/operations/fields.py", line 201, in database_forwards

    schema_editor.alter_field(from_model, from_field, to_field)

  File "/home/collin/django1.10/django/db/backends/base/schema.py", line 482, in alter_field

    old_db_params, new_db_params, strict)

  File "/home/collin/django1.10/django/db/backends/base/schema.py", line 634, in _alter_field

    params,

  File "/home/collin/django1.10/django/db/backends/base/schema.py", line 110, in execute

    cursor.execute(sql, params)

  File "/home/collin/django1.10/django/db/backends/utils.py", line 79, in execute

    return super(CursorDebugWrapper, self).execute(sql, params)

  File "/home/collin/django1.10/django/db/backends/utils.py", line 64, in execute

    return self.cursor.execute(sql, params)

  File "/home/collin/django1.10/django/db/utils.py", line 94, in __exit__

    six.reraise(dj_exc_type, dj_exc_value, traceback)

  File "/home/collin/django1.10/django/utils/six.py", line 685, in reraise

    raise value.with_traceback(tb)

  File "/home/collin/django1.10/django/db/backends/utils.py", line 64, in execute

    return self.cursor.execute(sql, params)

  File "/home/collin/django1.10/django/db/backends/mysql/base.py", line 112, in execute

    return self.cursor.execute(query, args)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 226, in execute

    self.errorhandler(self, exc, value)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler

    raise errorvalue

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 217, in execute

    res = self._query(query)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 378, in _query

    rowcount = self._do_query(q)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/cursors.py", line 341, in _do_query

    db.query(q)

  File "/home/collin/mb4test/lib/python3.4/site-packages/MySQLdb/connections.py", line 280, in query

    _mysql.connection.query(self, query)

django.db.utils.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

--

You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ac1bd7c0-6692-4caf-8087-0dc0208e6588%40googlegroups.com.

[Adam Johnson]

This has always been an error on MySQL afaik, it would allow broken data integrity if it were just a warning.

Moving the length below 191 does seem to be the safest fix.

[Tim Graham]

How does this documentation explanation for the length of User.username sound?

"This length should be sufficient for most use cases. If you need a longer length, please use a custom user model. If you want to use a unique EmailField and you use MySQL with the utf8mb4 encoding (recommended for proper Unicode support), you'll need to specify max_length=191 because MySQL can only create unique indexes with 191 characters in that case by default."