Skip to content

Error "Column length too big for column 'description'" on MySQL backend #672

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
RicardoJeronimo opened this issue Apr 7, 2025 · 15 comments · May be fixed by #673
Open

Error "Column length too big for column 'description'" on MySQL backend #672

RicardoJeronimo opened this issue Apr 7, 2025 · 15 comments · May be fixed by #673

Comments

@RicardoJeronimo
Copy link

RicardoJeronimo commented Apr 7, 2025 •
edited
Loading

While running some tests for PR #670, I noticed I'm not able to successfully run manage.py migrate --run-syncdb --fake-initial with a MySQL backend. The following error appears:

Operations to perform:
  Apply all migrations: admin, arch, auth, contenttypes, django_celery_beat, domains, errata, hosts, modules, operatingsystems, packages, reports, repos, security, sessions, sites, taggit
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying arch.0001_initial... 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... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying django_celery_beat.0019_alter_periodictasks_options... OK
  Applying domains.0001_initial... OK
  Applying operatingsystems.0001_initial... OK
  Applying packages.0001_initial... OK
  Applying packages.0002_delete_erratum_delete_erratumreference... OK
  Applying packages.0002_auto_20250207_1319... OK
  Applying packages.0003_auto_20250207_1746... OK
  Applying packages.0004_alter_package_options_alter_packagecategory_options_and_more... OK
  Applying packages.0005_alter_package_packagetype... OK
  Applying security.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1074, "Column length too big for column 'description' (max = 21845); use BLOB or TEXT instead")
(...)

This is a clean install from source and I tested this with MySQL 8 and 9, same result on both versions. The command manage.py makemigrations always returns No changes detected.
The command runs successfully with SQLite, by the way, just not with MySQL.
@RicardoJeronimo RicardoJeronimo changed the title Error "Column length too big for column 'description'" with MySQL backend Error "Column length too big for column 'description'" on MySQL backend Apr 7, 2025
@furlongm furlongm linked a pull request Apr 8, 2025 that will close this issue
Open
@furlongm
Copy link
Owner

furlongm commented Apr 8, 2025

Thanks for the bug report - can you check if #673 fixes the issue for you?

@RicardoJeronimo
Copy link
Author

RicardoJeronimo commented Apr 8, 2025 •
edited
Loading

Now I'm getting the following:

Operations to perform:
  Synchronize unmigrated apps: admindocs, bootstrap3, celery, django_extensions, django_filters, humanize, messages, rest_framework, staticfiles, util
  Apply all migrations: admin, arch, auth, contenttypes, django_celery_beat, domains, errata, hosts, modules, operatingsystems, packages, reports, repos, security, sessions, sites, taggit
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying arch.0001_initial... 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... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying django_celery_beat.0019_alter_periodictasks_options... OK
  Applying domains.0001_initial... OK
  Applying operatingsystems.0001_initial... OK
  Applying packages.0001_initial... OK
  Applying packages.0002_delete_erratum_delete_erratumreference... OK
  Applying packages.0002_auto_20250207_1319... OK
  Applying packages.0003_auto_20250207_1746... OK
  Applying packages.0004_alter_package_options_alter_packagecategory_options_and_more... OK
  Applying packages.0005_alter_package_packagetype... OK
  Applying security.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
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')
(...)

@furlongm
Copy link
Owner

furlongm commented Apr 8, 2025

Could you try with a smaller number and see what works?

@RicardoJeronimo
Copy link
Author

RicardoJeronimo commented Apr 8, 2025 •
edited
Loading

Not sure if you were asking to change the same 2 lines as #673, but I did it anyway:

('description', models.CharField(blank=True, max_length=255, null=True)),
('description', models.CharField(max_length=255)),

With that value the migration security.0001_initial completed but the process hanged on security.0005_reference_cve_references:

Operations to perform:
  Synchronize unmigrated apps: admindocs, bootstrap3, celery, django_extensions, django_filters, humanize, messages, rest_framework, staticfiles, util
  Apply all migrations: admin, arch, auth, contenttypes, django_celery_beat, domains, errata, hosts, modules, operatingsystems, packages, reports, repos, security, sessions, sites, taggit
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying arch.0001_initial... 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... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying django_celery_beat.0019_alter_periodictasks_options... OK
  Applying domains.0001_initial... OK
  Applying operatingsystems.0001_initial... OK
  Applying packages.0001_initial... OK
  Applying packages.0002_delete_erratum_delete_erratumreference... OK
  Applying packages.0002_auto_20250207_1319... OK
  Applying packages.0003_auto_20250207_1746... OK
  Applying packages.0004_alter_package_options_alter_packagecategory_options_and_more... OK
  Applying packages.0005_alter_package_packagetype... OK
  Applying security.0001_initial... OK
  Applying security.0002_alter_cve_options... OK
  Applying security.0003_alter_cve_description_alter_cwe_description... OK
  Applying security.0004_alter_cve_options... OK
  Applying security.0005_reference_cve_references...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')
(...)

@furlongm
Copy link
Owner

Do any of the solutions here help? https://stackoverflow.com/questions/29782081/django-db-utils-operationalerror-1071-specified-key-was-too-long-max-key-le

@RicardoJeronimo
Copy link
Author

I went through (almost) all of the presented solutions and none of them worked.
I was already using utf8 and utf8_general_ci as stated in INSTALL.md:

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "patchman";
+----------------------------+
| DEFAULT_CHARACTER_SET_NAME |
+----------------------------+
| utf8mb3                    |
+----------------------------+
mysql>
mysql> SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'patchman' LIMIT 1;
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb3_general_ci     |
+------------------------+

If I change it to utf8mb4 and utf8mb4_general_ci I get the error much earlier in the process, at packages.0003_auto_20250207_1746:

(...)
 Applying packages.0003_auto_20250207_1746...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')
(...)

@furlongm
Copy link
Owner

Can you run ./manage.py dbshell and show table status from DBNAME; so we can see what the tables look like?

@RicardoJeronimo
Copy link
Author

Sure, here are the tables after these changes:

MySQL [patchman]> SHOW TABLE STATUS FROM patchman;
+-------------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name                                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-------------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| arch_machinearchitecture            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| arch_packagearchitecture            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_group                          | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_group_permissions              | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:56:57 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_permission                     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_user                           | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_user_groups                    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:56:57 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| auth_user_user_permissions          | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_admin_log                    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_clockedschedule  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:56:59 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_crontabschedule  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:56:59 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_intervalschedule | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_periodictask     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2025-04-17 08:56:59 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_periodictasks    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_celery_beat_solarschedule    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:56:59 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_content_type                 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:58 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| django_migrations                   | InnoDB |      10 | Dynamic    |   51 |            321 |       16384 |               0 |            0 |         0 |             52 | 2025-04-17 08:56:57 | 2025-04-17 08:57:01 | NULL       | utf8mb3_general_ci |     NULL |                |         |
| domains_domain                      | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:56:59 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| operatingsystems_os                 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:00 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| operatingsystems_osgroup            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:00 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| packages_package                    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| packages_packagecategory            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:00 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| packages_packagename                | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:00 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| packages_packageupdate              | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:57:00 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cve                        | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cve_cvss_scores            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cve_cwes                   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cve_references             | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cvss                       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_cwe                        | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
| security_reference                  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2025-04-17 08:57:01 | NULL                | NULL       | utf8mb3_general_ci |     NULL |                |         |
+-------------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
31 rows in set (0.039 sec)

@furlongm
Copy link
Owner

Starting with a fresh mysql db, I was able to reproduce the initial issue, and #673 seems to fix that. I cannot reproduce the second issue. Would it be possible to try again with a fresh mysql db and #673 ?

@RicardoJeronimo
Copy link
Author

Every comment I made in this thread has been with a fresh MySQL database, but I just made a fresh clone of main branch (and applied changes in #673) and I'm still getting the same error on security.0005_reference_cve_references.

As of right now, I'm using the mysql:latest tag running MySQL version 9.2:

bash-5.1# mysql --version
mysql  Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL)

Since I'm installing from source, maybe I'm missing a step? Here is my Dockerfile.

@furlongm
Copy link
Owner

I am using mariadb and don't see the issue:

# mysql --version
mysql  Ver 15.1 Distrib 10.11.9-MariaDB, for Linux (x86_64) using  EditLine wrapper

I will try to reproduce with your version.

Can you reduce the max_length of the URLField in the migration and see if that helps?

@RicardoJeronimo
Copy link
Author

I changed the line you suggested on security/migrations/0005_reference_cve_references.py:

('url', models.URLField(max_length=255)),

Only for it to hang on errata.0002_alter_erratumreference_unique_together:

Operations to perform:
  Synchronize unmigrated apps: admindocs, bootstrap3, celery, django_extensions, django_filters, humanize, messages, rest_framework, staticfiles, util
  Apply all migrations: admin, arch, auth, contenttypes, django_celery_beat, domains, errata, hosts, modules, operatingsystems, packages, reports, repos, security, sessions, sites, taggit
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying arch.0001_initial... 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... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying django_celery_beat.0019_alter_periodictasks_options... OK
  Applying domains.0001_initial... OK
  Applying operatingsystems.0001_initial... OK
  Applying packages.0001_initial... OK
  Applying packages.0002_delete_erratum_delete_erratumreference... OK
  Applying packages.0002_auto_20250207_1319... OK
  Applying packages.0003_auto_20250207_1746... OK
  Applying packages.0004_alter_package_options_alter_packagecategory_options_and_more... OK
  Applying packages.0005_alter_package_packagetype... OK
  Applying security.0001_initial... OK
  Applying security.0002_alter_cve_options... OK
  Applying security.0003_alter_cve_description_alter_cwe_description... OK
  Applying security.0004_alter_cve_options... OK
  Applying security.0005_reference_cve_references... OK
  Applying repos.0001_initial... OK
  Applying operatingsystems.0002_initial... OK
  Applying operatingsystems.0003_os_arch... OK
  Applying operatingsystems.0003_osgroup_codename... OK
  Applying operatingsystems.0004_alter_osgroup_unique_together... OK
  Applying taggit.0001_initial... OK
  Applying taggit.0002_auto_20150616_2121... OK
  Applying taggit.0003_taggeditem_add_unique_index... OK
  Applying taggit.0004_alter_taggeditem_content_type_alter_taggeditem_tag... OK
  Applying taggit.0005_auto_20220424_2025... OK
  Applying modules.0001_initial... OK
  Applying hosts.0001_initial... OK
  Applying hosts.0002_initial... OK
  Applying hosts.0003_host_modules... OK
  Applying hosts.0004_remove_host_tags_host_tags... OK
  Applying hosts.0005_rename_os_host_osvariant... OK
  Applying operatingsystems.0005_rename_osgroup_osrelease_rename_os_osvariant_and_more... OK
  Applying errata.0001_initial... OK
  Applying errata.0002_alter_erratumreference_unique_together...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')
(...)

Next, I changed errata/migrations/0001_initial.py:

('url', models.URLField(max_length=255)),

This took the process to the next step, showing the error on security.0008_alter_reference_url:

Operations to perform:
  Synchronize unmigrated apps: admindocs, bootstrap3, celery, django_extensions, django_filters, humanize, messages, rest_framework, staticfiles, util
  Apply all migrations: admin, arch, auth, contenttypes, django_celery_beat, domains, errata, hosts, modules, operatingsystems, packages, reports, repos, security, sessions, sites, taggit
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying arch.0001_initial... 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... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying django_celery_beat.0019_alter_periodictasks_options... OK
  Applying domains.0001_initial... OK
  Applying operatingsystems.0001_initial... OK
  Applying packages.0001_initial... OK
  Applying packages.0002_delete_erratum_delete_erratumreference... OK
  Applying packages.0002_auto_20250207_1319... OK
  Applying packages.0003_auto_20250207_1746... OK
  Applying packages.0004_alter_package_options_alter_packagecategory_options_and_more... OK
  Applying packages.0005_alter_package_packagetype... OK
  Applying security.0001_initial... OK
  Applying security.0002_alter_cve_options... OK
  Applying security.0003_alter_cve_description_alter_cwe_description... OK
  Applying security.0004_alter_cve_options... OK
  Applying security.0005_reference_cve_references... OK
  Applying repos.0001_initial... OK
  Applying operatingsystems.0002_initial... OK
  Applying operatingsystems.0003_os_arch... OK
  Applying operatingsystems.0003_osgroup_codename... OK
  Applying operatingsystems.0004_alter_osgroup_unique_together... OK
  Applying taggit.0001_initial... OK
  Applying taggit.0002_auto_20150616_2121... OK
  Applying taggit.0003_taggeditem_add_unique_index... OK
  Applying taggit.0004_alter_taggeditem_content_type_alter_taggeditem_tag... OK
  Applying taggit.0005_auto_20220424_2025... OK
  Applying modules.0001_initial... OK
  Applying hosts.0001_initial... OK
  Applying hosts.0002_initial... OK
  Applying hosts.0003_host_modules... OK
  Applying hosts.0004_remove_host_tags_host_tags... OK
  Applying hosts.0005_rename_os_host_osvariant... OK
  Applying operatingsystems.0005_rename_osgroup_osrelease_rename_os_osvariant_and_more... OK
  Applying errata.0001_initial... OK
  Applying errata.0002_alter_erratumreference_unique_together... OK
  Applying errata.0003_delete_erratumreference_alter_erratum_references... OK
  Applying errata.0004_rename_packages_erratum_fixed_packages... OK
  Applying errata.0005_erratum_affected_packages_and_more... OK
  Applying errata.0006_alter_erratum_options... OK
  Applying errata.0007_alter_erratum_fixed_packages... OK
  Applying hosts.0006_migrate_to_tz_aware... OK
  Applying hosts.0007_alter_host_tags... OK
  Applying hosts.0008_alter_host_options... OK
  Applying hosts.0009_host_errata... OK
  Applying modules.0002_auto_20240204_2214... OK
  Applying modules.0003_alter_module_options... OK
  Applying modules.0004_alter_module_options... OK
  Applying operatingsystems.0006_osrelease_cpe_name_osvariant_codename... OK
  Applying operatingsystems.0007_alter_osrelease_unique_together... OK
  Applying operatingsystems.0008_alter_osrelease_options_alter_osvariant_options... OK
  Applying reports.0001_initial... OK
  Applying reports.0002_report_modules... OK
  Applying reports.0003_remove_report_accessed... OK
  Applying reports.0004_migrate_to_tz_aware... OK
  Applying reports.0005_alter_report_options... OK
  Applying repos.0002_alter_repository_repotype... OK
  Applying repos.0003_migrate_to_tz_aware... OK
  Applying repos.0004_rename_file_checksum_mirror_package_checksum... OK
  Applying repos.0005_rename_package_checksum_mirror_packages_checksum... OK
  Applying repos.0006_mirror_errata_checksum_mirror_modules_checksum... OK
  Applying security.0006_alter_cve_options_alter_cvss_unique_together... OK
  Applying security.0007_remove_cve_title... OK
  Applying security.0008_alter_reference_url...Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/Django-4.2.20-py3.11.egg/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')
(...)

However, I can't find any migration file on security/migrations with that name in order to troubleshoot further

@furlongm
Copy link
Owner

Ah, it looks like it's creating a migration for the change. So you'd need to change the max_length in both the model and migration so they are matching, then it won't create a new migration.

@RicardoJeronimo
Copy link
Author

That worked, the process completed successfully!

Here is the (truncated) git diff:

(...)

--- a/errata/migrations/0001_initial.py
+++ b/errata/migrations/0001_initial.py
@@ -19,7 +19,7 @@ class Migration(migrations.Migration):
             fields=[
                 ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                 ('er_type', models.CharField(max_length=255)),
-                ('url', models.URLField(max_length=2000)),
+                ('url', models.URLField(max_length=255)),
             ],
         ),
         migrations.CreateModel(

(...)

--- a/security/migrations/0001_initial.py
+++ b/security/migrations/0001_initial.py
@@ -27,7 +27,7 @@ class Migration(migrations.Migration):
                 ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                 ('cwe_id', models.CharField(max_length=255, unique=True)),
                 ('name', models.CharField(blank=True, max_length=255, null=True)),
-                ('description', models.CharField(blank=True, max_length=65535, null=True)),
+                ('description', models.CharField(blank=True, max_length=255, null=True)),
             ],
         ),
         migrations.CreateModel(
@@ -36,7 +36,7 @@ class Migration(migrations.Migration):
                 ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                 ('cve_id', models.CharField(max_length=255, unique=True)),
                 ('title', models.CharField(blank=True, max_length=255, null=True)),
-                ('description', models.CharField(max_length=65535)),
+                ('description', models.CharField(max_length=255)),
                 ('reserved_date', models.DateTimeField(blank=True, null=True)),
                 ('published_date', models.DateTimeField(blank=True, null=True)),
                 ('rejected_date', models.DateTimeField(blank=True, null=True)),

(...)

--- a/security/migrations/0005_reference_cve_references.py
+++ b/security/migrations/0005_reference_cve_references.py
@@ -15,7 +15,7 @@ class Migration(migrations.Migration):
             fields=[
                 ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                 ('ref_type', models.CharField(max_length=255)),
-                ('url', models.URLField(max_length=2000)),
+                ('url', models.URLField(max_length=255)),
             ],
             options={
                 'unique_together': {('ref_type', 'url')},

(...)

--- a/security/models.py
+++ b/security/models.py
@@ -29,7 +29,7 @@ from util import get_url, fetch_content, tz_aware_datetime, error_message
 class Reference(models.Model):

     ref_type = models.CharField(max_length=255)
-    url = models.URLField(max_length=2000)
+    url = models.URLField(max_length=255)

     class Meta:
         unique_together = ['ref_type', 'url']

@furlongm
Copy link
Owner

Awesome, thanks for testing!

Could you also test what is the highest possible value there?

I chose 2000 based on https://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers/417184#417184 so would like to avoid truncating, and breaking, URLs if that is possible...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

reduce max charfield length for mysql furlongm/patchman
2 participants
@furlongm @RicardoJeronimo