Resolve MySQL error 'Field 'storing' doesn't have a default value' on INSERT for BLOB NOT NULL columns

Learn why MySQL shows 'Field 'storing' doesn't have a default value' on INSERT with a BLOB NOT NULL column, and fix it by allowing NULL then updating binary.

Trying to insert a row for an image and hitting “Field 'storing' doesn't have a default value”? This happens when a table expects a value for a non-nullable BLOB column but you insert a row without providing that BLOB. Below is a concise walkthrough of why this error appears and how to resolve it cleanly without changing your application flow.

Table and code that surface the error

The table structure shows a BLOB column that is NOT NULL and has no default. Inserting a row that omits this column will fail.

mysql> show columns from cover;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| idx      | smallint     | NO   |     | NULL    |                |
| nomefile | varchar(255) | YES  |     | NULL    |                |
| sizefile | varchar(255) | YES  |     | NULL    |                |
| mimetype | varchar(255) | YES  |     | NULL    |                |
| storing  | blob         | NO   |     | NULL    |                |
| id       | smallint     | NO   | PRI | NULL    | auto_increment |
+----------+--------------+------+-----+---------+----------------+

In the application, a row is created when none exists, and the binary is attached later via UPDATE. The INSERT that only sets the key field triggers the error because storing is defined as NOT NULL without a default.

def push_cover(self, key):
    file_opts = QFileDialog.Options()
    file_opts |= QFileDialog.DontUseNativeDialog
    picked_path, _ = QFileDialog.getOpenFileName(
        self, "Open Image", "/media/hdb1/", "Images (*.png *.xpm *.jpg)", options=file_opts
    )
    sql_check = "SELECT * FROM `cover` WHERE idx = %s " % (key)
    if picked_path:
        dbh, cur = Functions.openDB(self, dbname)
        cur.execute(sql_check)
        rows_found = cur.rowcount
        if rows_found == 0:
            sql_insert = "INSERT INTO `cover` (idx) VALUES (%s)" % (key)  # error originates here
            cur.execute(sql_insert)
            dbh.commit()
        f_info = QFileInfo(picked_path)
        f_size = f_info.size()
        name_base = f_info.completeBaseName()
        name_ext = f_info.completeSuffix()
        full_name = name_base + '.' + name_ext
        bin_blob = open(picked_path, 'rb').read()
        dbh, cur = Functions.openDB(self, dbname)
        mime_kind = 'image/jpeg'
        sql_update = (
            "UPDATE `cover` SET nomefile = %s, sizefile = %s, mimetype = %s, storing = %s WHERE idx = %s "
        )
        try:
            cur.execute(sql_update, (picked_path, f_size, mime_kind, bin_blob, key))
            dbh.commit()
        except Error as e:
            Functions.handle_error(self, message=e)
            self.container.close()
            return
        Functions.message_handler(self, "Cover inserita correttamente")
        self.container.close()
    else:
        Functions.message_handler(self, "LEAVING EVERYTHING\nBYE BYE")
        self.container.close()

What is actually going wrong

The column storing is defined as BLOB NOT NULL. A BLOB type does not accept a default value, and because the INSERT only provides idx, there is no value supplied for storing and nothing to fall back on. MySQL rejects the INSERT before you get a chance to push the binary in the subsequent UPDATE.

Fix that preserves the insert-then-update flow

If a newly inserted row can legitimately exist without the image yet, make the BLOB column nullable so that the initial INSERT succeeds. After that, you can UPDATE the row with the image data exactly as the application already does.

ALTER TABLE cover MODIFY storing BLOB NULL DEFAULT NULL;

Once storing accepts NULL, the INSERT that only sets idx will complete, and the later UPDATE that sets storing with the binary will work as intended.

Why it matters

Separating row creation from binary upload is a common pattern in CRUD flows around media storage. Understanding how MySQL handles BLOB defaults and NOT NULL constraints prevents hard-to-diagnose write failures and lets you keep the application logic simple: create the record first, enrich it with the heavy payload afterward.

Takeaways

When you need to insert a row before the binary payload is ready, ensure the BLOB column can temporarily hold NULL. In this case, altering storing to be NULL DEFAULT NULL resolves the error and keeps the existing Python code and update path intact. If the image must always be present at creation time, provide a value at INSERT, but if your workflow adds it later, making the BLOB nullable is the cleanest path.