Cuong Tran Cuong Tran - 23 days ago 7
MySQL Question

Django Migration Error with MySQL: BLOB/TEXT column 'id' used in key specification without a key length"

We have Django Model, use Binary Field for ID.

# Create your models here.
class Company(models.Model):
id = models.BinaryField(max_length=16, primary_key=True)
name = models.CharField(max_length=12)

class Meta:
db_table = "company"


We use MySQL Database and have error when migrate.

File "/home/cuongtran/Downloads/sample/venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 270, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'id' used in key specification without a key length")


Do you have any solution? We need to use MySQL and want to use the Binary Field for ID.

Thank you!

Answer

I think you cannot achieve this. Based on Django documentation it looks like use of binary fields is discouraged

A field to store raw binary data. It only supports bytes assignment. Be aware that this field has limited functionality. For example, it is not possible to filter a queryset on a BinaryField value. It is also not possible to include a BinaryField in a ModelForm.

Abusing BinaryField

Although you might think about storing files in the database, consider that it is bad design in 99% of the cases. This field is not a replacement for proper static files handling.

And based on a Django bug, it is most likely impossible to achieve a unique value restriction on a binary field. This bug is marked as wont-fix. I am saying most likely impossible as I did not find evidence to confirm that binary field is stored as a BLOB field but the error does allude to it.

Description

When I used a field like this:
text = models.TextField(maxlength=2048, unique=True)
it results in the following sql error when the admin app goes to make the table
_mysql_exceptions.OperationalError: (1170, "BLOB/TEXT column 'text' used in key specification without a key length")
After a bit of investigation, it turns out that mysql refuses to use unique with the column unless it is only for an indexed part of the text field:

CREATE TABLE `quotes` ( \`id\` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `text` longtext NOT NULL , \`submitTS\` datetime NOT NULL, `submitIP` char(15) NOT NULL, `approved` bool NOT NULL, unique (text(1000)));

Of course 1000 is just an arbitrary number I chose, it happens to be the maximum my database would allow. Not entirely sure how this can be fixed, but I figured it was worth mentioning.