V8-archive: "Required" fails on some fields

[aliemba]

v8.5.4

Altering

To active state and save results in (this is from two different fields where the "truncated" file is the current filename)

And the result is that the fields required state is unaltered.

[rijkvanzanten]

I'm also seeing

"Statement could not be executed (22004 - 1138 - Invalid use of NULL value)" on my end

[hemratna]

@aliemba @rijkvanzanten Can you please confirm this is the steps to reproduce this issue?

[rijkvanzanten]

This is happening on making a field required from settings @hemratna, not when saving a required field 🙂

[hemratna]

@aliemba Can you please verify that all the entry in a that collection.column is not null?

When we mark any fields as required, Directus make that column Not null runs below query.

ALTER TABLE table_name  ALTER COLUMN column_name INT(10) NOT NULL;

As per the MySQL behavior

all existing NULL values within the column must be updated to a non-null value before the ALTER command can be successfully used and the column made NOT NULL. Any attempt to set the column to NOT NULL while actual NULL data remains in the column will result in an error and no change will occur.

[rijkvanzanten]

@hemratna Using the demo schema, go to Settings > Collection & Fields > Customers and try making the birthday field required. You'll get an error:

Error: "Statement could not be executed (22004 - 1138 - Invalid use of NULL value)"

Also, while you're true that MySQL can't make a column required that has rows with no value in it, Directus should never return a 500 error. In that case, we should return a 400 with a message like "Can't make field required: there are items in this collection with no value for this field".

[aliemba]

Sorry for my late reply. Yes the above is what I see. And it is related to tables with data already in it.

I understand this from a database perspective, but from a usage perspective it is not logical to get an error at all.

As we can't change the nature of mysql; we could force the user to make an effort into choosing a default value when creating a filed?

ex. when making fields that are "boolean" of nature one should be forced to choose a default value.
(also if you don't you end up with values that are "NULL", "1" and "0", probably not what you need)

[rijkvanzanten]

That would be best case scenario: API returns a 400 bad request with an error code indicating that there are fields that don't have a value yet when trying to make it required. The app can then show a separate modal asking you what to make the value for the items that don't have a value yet.