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
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.