Home > other >  Django cannot migrate on Galera but MariaDB standalone works
Django cannot migrate on Galera but MariaDB standalone works

Time:01-18

I'm using Django together with MariaDB, I now moved my application to K8s and my Django migration don't want to run through, instead the whole migration process fails. On my local development system I'm using a standalone MariaDB instance where everything is working fine. How can it be that the same process is not working against a Galera-Cluster, here the output of my application is the following while trying to migrate all the tables:

python manage.py migrate
Operations to perform:
  Apply all migrations: App, App_Accounts, App_Storages, admin, auth, contenttypes, database, django_celery_results, sessions, sites
Running migrations:
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying App_Storages.0001_initial... OK
  Applying App_Accounts.0001_initial...Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django_prometheus/db/common.py", line 71, in execute
    return super().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1170, "BLOB/TEXT column 'avatar_path' used in key specification without a key length")

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

Traceback (most recent call last):
  File "/app/manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/venv/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/venv/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/migrations/migration.py", line 130, in apply
    operation.database_forwards(
  File "/venv/lib/python3.11/site-packages/django/db/migrations/operations/models.py", line 96, in database_forwards
    schema_editor.create_model(model)
  File "/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 447, in create_model
    self.execute(sql, params or None)
  File "/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 199, in execute
    cursor.execute(sql, params)
  File "/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django_prometheus/db/common.py", line 71, in execute
    return super().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'avatar_path' used in key specification without a key length")

Why do I get this error only with a Galera-Cluster but not with my local MariaDB instance? And how can I possibly fix this.

This is how the field looks like in my models.py:

avatar_path = models.TextField(verbose_name="Avatar Path", blank=True, null=True, editable=True, max_length=1000, unique=True)

I would actually expect that my migrations will getting processed the same way as with my local MariaDB deployment, which is obviously not the case.

UPDATE:

After deleting the field avatar_path I'm running into exactly the same problem with other fields. Seems this must be a DB engine related issue. But I'm always running into this when using models.TextField it seems.

Not sure how much it matters but this is the my.cnf config file the Galera-Cluster runs:

[client]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
plugin_dir=/opt/bitnami/mariadb/plugin

[mysqld]
performance_schema = on
default_storage_engine=InnoDB
basedir=/opt/bitnami/mariadb
datadir=/bitnami/mariadb/data
plugin_dir=/opt/bitnami/mariadb/plugin
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
bind_address=0.0.0.0

## Character set
collation_server=utf8_unicode_ci
init_connect='SET NAMES utf8'
character_set_server=utf8

## MyISAM
key_buffer_size=16M
myisam_recover_options=FORCE,BACKUP

## Safety
skip_host_cache
skip_name_resolve
max_allowed_packet=256M
max_connect_errors=1000000
#sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate_is_now=1

## Binary Logging
log_bin=mysql-bin
expire_logs_days=7
# Disabling for performance per http://severalnines.com/blog/9-tips-going-production-galera-cluster-mysql
sync_binlog=0
# Required for Galera
binlog_format=row

## Caches and Limits
tmp_table_size=2G
max_heap_table_size=2G
query_cache_type=0
query_cache_limit=4M
query_cache_size=256M
max_connections=3000
thread_handling = pool-of-threads
thread_stack = 256K
thread_cache_size = 4
thread_pool_size = 8
thread_pool_oversubscribe = 3
open_files_limit=65535
table_definition_cache=4096
table_open_cache=4096

## InnoDB
innodb=FORCE
innodb_strict_mode=1
# Mandatory per https://github.com/codership/documentation/issues/25
innodb_autoinc_lock_mode=2
# Per https://www.percona.com/blog/2006/08/04/innodb-double-write/
innodb_doublewrite=1
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_log_files_in_group=2
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
# 80% Memory is default reco.
# Need to re-evaluate when DB size grows
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=10
innodb_read_io_threads=8
innodb_write_io_threads=6
innodb_log_buffer_size = 256M
innodb_file_format=Barracuda

## Logging
log_error=/opt/bitnami/mariadb/logs/mysqld.log
slow_query_log_file=/opt/bitnami/mariadb/logs/mysqld.log
log_queries_not_using_indexes=1
slow_query_log=1

## SSL
## Use extraVolumes and extraVolumeMounts to mount /certs filesystem
# ssl_ca=/certs/ca.pem
# ssl_cert=/certs/server-cert.pem
# ssl_key=/certs/server-key.pem

[galera]
wsrep_on=ON
wsrep_provider=/opt/bitnami/mariadb/lib/libgalera_smm.so
wsrep_sst_method=mariabackup
wsrep_slave_threads=8
wsrep_cluster_address=gcomm://
wsrep_cluster_name=galera
wsrep_sst_auth="root:"
wsrep_provider_options="gcache.size=2G; gcache.page_size=2G;"
# Enabled for performance per https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=80000
# MYISAM REPLICATION SUPPORT #
wsrep_replicate_myisam=ON

[mariadb]
plugin_load_add=auth_pam

## Data-at-Rest Encryption
## Use extraVolumes and extraVolumeMounts to mount /encryption filesystem
# plugin_load_add=file_key_management
# file_key_management_filename=/encryption/keyfile.enc
# file_key_management_filekey=FILE:/encryption/keyfile.key
# file_key_management_encryption_algorithm=AES_CTR
# encrypt_binlog=ON
# encrypt_tmp_files=ON

## InnoDB/XtraDB Encryption
# innodb_encrypt_tables=ON
# innodb_encrypt_temporary_tables=ON
# innodb_encrypt_log=ON
# innodb_encryption_threads=4
# innodb_encryption_rotate_key_age=1

## Aria Encryption
# aria_encrypt_tables=ON
# encrypt_tmp_disk_tables=ON

Thanks in advance

CodePudding user response:

Rather than using TextField that translates to Text, use models.CharField(verbose_name="Avatar Path", blank=True, null=True, editable=True, max_length=1000, unique=True).

While MDEV-371 Unique indexes for blobs (and Text) was implemented in MariaDB, (and the difference in version between your local and Galera version is likely the reason for the error). There have been a few bugs around it recently so using CharField translates to varchar which doesn't have this problem.

  • Related