Django auth + MySQL migrations, row size too large

Question

This migration file fails which is included in the contrib.auth

/Users/xxx/.virtualenvs/xxxx-0LdyW30-/lib/python3.8/site-packages/django/contrib/auth/migrations/0012_alter_user_first_name_max_length.py

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('auth', '0011_update_proxy_permissions'),
    ]

    operations = [
        migrations.AlterField(
            model_name='user',
            name='first_name',
            field=models.CharField(blank=True, max_length=150, verbose_name='first name'),
        ),
    ]

error:

web_1 | MySQLdb._exceptions.OperationalError: (1118, 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs')

note: I'm using a docker-compose environment

UPDATE: init migrations in django.contrib.auth.models -- /Users/xxx/.virtualenvs/xxxx-0LdyW30-/lib/python3.8/site-packages/django/contrib/auth/migrations/0001_initial.py

import django.contrib.auth.models
from django.contrib.auth import validators
from django.db import migrations, models
from django.utils import timezone


class Migration(migrations.Migration):

    dependencies = [
        ('contenttypes', '__first__'),
    ]

    operations = [
        migrations.CreateModel(
            name='Permission',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(max_length=50, verbose_name='name')),
                ('content_type', models.ForeignKey(
                    to='contenttypes.ContentType',
                    on_delete=models.CASCADE,
                    to_field='id',
                    verbose_name='content type',
                )),
                ('codename', models.CharField(max_length=100, verbose_name='codename')),
            ],
            options={
                'ordering': ['content_type__app_label', 'content_type__model', 'codename'],
                'unique_together': {('content_type', 'codename')},
                'verbose_name': 'permission',
                'verbose_name_plural': 'permissions',
            },
            managers=[
                ('objects', django.contrib.auth.models.PermissionManager()),
            ],
        ),
        migrations.CreateModel(
            name='Group',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(unique=True, max_length=80, verbose_name='name')),
                ('permissions', models.ManyToManyField(to='auth.Permission', verbose_name='permissions', blank=True)),
            ],
            options={
                'verbose_name': 'group',
                'verbose_name_plural': 'groups',
            },
            managers=[
                ('objects', django.contrib.auth.models.GroupManager()),
            ],
        ),
        migrations.CreateModel(
            name='User',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('password', models.CharField(max_length=128, verbose_name='password')),
                ('last_login', models.DateTimeField(default=timezone.now, verbose_name='last login')),
                ('is_superuser', models.BooleanField(
                    default=False,
                    help_text='Designates that this user has all permissions without explicitly assigning them.',
                    verbose_name='superuser status'
                )),
                ('username', models.CharField(
                    help_text='Required. 30 characters or fewer. Letters, digits and @/./+/-/_ only.', unique=True,
                    max_length=30, verbose_name='username',
                    validators=[validators.UnicodeUsernameValidator()],
                )),
                ('first_name', models.CharField(max_length=30, verbose_name='first name', blank=True)),
                ('last_name', models.CharField(max_length=30, verbose_name='last name', blank=True)),
                ('email', models.EmailField(max_length=75, verbose_name='email address', blank=True)),
                ('is_staff', models.BooleanField(
                    default=False, help_text='Designates whether the user can log into this admin site.',
                    verbose_name='staff status'
                )),
                ('is_active', models.BooleanField(
                    default=True, verbose_name='active', help_text=(
                        'Designates whether this user should be treated as active. Unselect this instead of deleting '
                        'accounts.'
                    )
                )),
                ('date_joined', models.DateTimeField(default=timezone.now, verbose_name='date joined')),
                ('groups', models.ManyToManyField(
                    to='auth.Group', verbose_name='groups', blank=True, related_name='user_set',
                    related_query_name='user', help_text=(
                        'The groups this user belongs to. A user will get all permissions granted to each of their '
                        'groups.'
                    )
                )),
                ('user_permissions', models.ManyToManyField(
                    to='auth.Permission', verbose_name='user permissions', blank=True,
                    help_text='Specific permissions for this user.', related_name='user_set',
                    related_query_name='user')
                 ),
            ],
            options={
                'swappable': 'AUTH_USER_MODEL',
                'verbose_name': 'user',
                'verbose_name_plural': 'users',
            },
            managers=[
                ('objects', django.contrib.auth.models.UserManager()),
            ],
        ),
    ]

repo url: https://github.com/0mars/biostar-central-docker

show create table auth_user
    -> ;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auth_user | CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) NOT NULL,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(254) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

You need to show us the table definition. And what was being changed in the "migration". — Rick James, Jan 09 '21 at 01:48
I'm sorry; can you find out what `ALTER` statements that code generates. — Rick James, Jan 09 '21 at 15:38
The problem is that this package is in django itself! so I cannot modify it, shall I file a bug report ? — Omar, Jan 09 '21 at 16:31
If the tables are already built, go to the server via phpmyadmin, Workbench, or the mysql commandline tool and get `SHOW CREATE TABLE`. — Rick James, Jan 09 '21 at 17:35
Check the collation of the table and columns. Make it utf8_general_ci if it's not. — Ajith Gopi, Jan 15 '21 at 17:00
@AhmedShehab there are no custom models, project is available updated the question — Omar, Jan 16 '21 at 05:27

score 0 · Answer 1 · answered Jan 12 '21 at 22:03

0

Quick Fix:

Use TextField instead of CharField for first_name

Maths:

The max size of the data that be stored in the MYSQL row is 65535 bytes. (Read more about MySQL row size limitation here)
Assuming the database is using utfmb4 character set, it uses 1 to 4 bytes per character. (Refer here)
Assuming that all the characters are using 4 bytes, so a total of 65535/4 = 16383 characters per rows. So the sum of max_length of all the charfield must be less 16383 characters.

To debug this issue properly, check the character set of your database.

answered Jan 12 '21 at 22:03

sun_jara

1,736
12
20

still the error doesn;t make sense at all, first_names usually doesn;t get beyond 150chars – Omar Feb 02 '21 at 15:02
first solution is impossible: web_1 | django.core.exceptions.FieldError: Local field 'first_name' in class 'UserExt' clashes with field of the same name from base class 'User'. – Omar Feb 02 '21 at 15:21
For me Django was not allowing more than 96 Columns in one table, so I broke up the table into separate (anyways 96 was too much) and then it worked. – DragonFire Aug 13 '22 at 04:52

score -1 · Answer 2 · answered Jan 13 '21 at 19:58

-1

In the mysql my.cnf or command settings, a possible solution is to disable innodb_strict_mode.

docker compose example:

version: '3.7'

services:
  db:
    image: mysql:5.7
    command: --innodb_strict_mode=0
    ports:
      - '3306:3306'
    environment:
      MYSQL_DATABASE: 'db_name'
      MYSQL_USER: 'username'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'sa_password'

answered Jan 13 '21 at 19:58

Michael Lindsay

1,290
1
8
6

will have a look now – Omar Jan 14 '21 at 13:05

Django auth + MySQL migrations, row size too large

2 Answers2