MySQL Error 1073 occurs when trying to use a BLOB column as a key or index without specifying a prefix length. This happens because BLOB columns store variable-length data that MySQL cannot fully index, requiring either a table engine change, prefix length specification, or column type modification.
MySQL Error 1073 (ER_BLOB_USED_AS_KEY) occurs when you attempt to create an index, primary key, or unique constraint on a BLOB column without specifying a prefix length. BLOB (Binary Large Object) columns are designed to store large binary data like images, video files, or other binary content. Because BLOB data is variable-length and can be very large, MySQL cannot use the entire column as a key—it needs to know exactly how many bytes to index. The error indicates that your table engine (like MyISAM) doesn't support BLOB indexing, or you've tried to use a BLOB column as a primary key without providing a length constraint.
First, examine your CREATE TABLE statement to locate which column is causing the error:
DESC your_table_name;Look for any BLOB, TEXT, or similar variable-length binary types that are part of a PRIMARY KEY or UNIQUE constraint.
The most common solution is to add a length prefix when using BLOB/TEXT in indexes. Instead of:
CREATE TABLE my_table (
data BLOB,
PRIMARY KEY (data)
);Use a prefix length:
CREATE TABLE my_table (
id INT PRIMARY KEY AUTO_INCREMENT,
data BLOB,
UNIQUE KEY (data(100))
);The number (100) specifies that only the first 100 bytes of the BLOB column should be indexed.
If the column doesn't need to store truly large binary data, consider using VARCHAR instead:
CREATE TABLE my_table (
id INT PRIMARY KEY AUTO_INCREMENT,
hash_value VARCHAR(255) UNIQUE
);VARCHAR(255) can be fully indexed without a prefix length. VARCHAR(256) or larger will be auto-converted to TEXT, causing the error. Keep VARCHAR columns under 255 characters for full indexing.
The safest approach is to use an auto-increment integer as the primary key and remove BLOB columns from key definitions:
CREATE TABLE my_table (
id INT PRIMARY KEY AUTO_INCREMENT,
data BLOB,
metadata VARCHAR(255)
);
CREATE INDEX idx_metadata ON my_table (metadata(100));This allows you to store BLOB data without key constraint issues.
After making changes, test the corrected statement:
DROP TABLE IF EXISTS test_blob_table;
CREATE TABLE test_blob_table (
id INT PRIMARY KEY AUTO_INCREMENT,
file_data BLOB,
file_hash VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test_blob_table (file_data, file_hash) VALUES (
x'48656C6C6F', 'abc123');
SELECT * FROM test_blob_table;If no errors appear, the fix is successful.
PREFIX LENGTH CONSIDERATION: When specifying a prefix length for BLOB/TEXT columns in indexes, choose a balance between specificity and index size. Shorter prefixes (like 10-50) create smaller indexes but may have more hash collisions. Longer prefixes (like 255-500) are more specific but use more disk space. For most use cases, 100-255 bytes is appropriate.
FOREIGN KEY CONSTRAINT: BLOB/TEXT columns cannot be referenced in FOREIGN KEY constraints at all, even with prefix length, because indexes on those columns must include a prefix. If you need a foreign key on a string column, use VARCHAR with a length under 255 bytes instead.
INN0DB STORAGE: While InnoDB technically supports prefix indexes on BLOB columns better than MyISAM, the same prefix length rule applies. InnoDB is the default in modern MySQL (5.7+) and handles BLOB columns more gracefully, but the indexing limitation remains.
ORMFRAMEWORK CONSIDERATIONS: ORMs like Sequelize, TypeORM, or Doctrine may auto-generate problematic schema statements. If using an ORM, explicitly define column indexes in your model configuration rather than relying on automatic @PrimaryKey or @Unique decorators on BLOB columns.
VARCHAR AUTO-CONVERSION: Be aware that VARCHAR(256) and larger are automatically converted to TEXT in MySQL, triggering the same error. If you need a TEXT field as a key, always use a prefix length in your index definition.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL