2

I have a table with a column that is filled by a before insert trigger, this column is set to be NOT NULL and has no DEFAULT VALUE.

When I do a INSERT TABLE without passing this column, I receive the error: 1364 - Field 'column_name' doesn't have a default value. I'd search the web for a solution and have encountered this question: Field 'id' doesn't have a default value?. I then checked and changed the mysql_mode from:

"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

to:

"NO_ENGINE_SUBSTITUTION"

but the insert query still returns the same error.

What should I do to bypass this "strict" mode? Or is it something else? Please let me know if I need to include any config variable. I'm using mysql 5.6.39.

Mateus Neves
  • 304
  • 5
  • 16
  • 1
    If it has no default value but must be set, then what value shall it have? Is there a proper reason for the column not to have a default value? It seems you want it to have one. (Otherwise include the value you want to insert in your insert statement in the trigger.) – Thorsten Kettner Jan 25 '18 at 15:01
  • @ThorstenKettner it's a calculated value and could not have a default value. It's currently been inserted by a before insert trigger, as I said on the question, but the error persists. :/ – Mateus Neves Jan 25 '18 at 16:09
  • Ah, so your insert statement relies on the trigger to fill the column, but although the trigger is there to fill the column you still get the error. A bug probably. Or is the trigger not working as expected? Are you sure it fills the column in any case? – Thorsten Kettner Jan 25 '18 at 17:07
  • 1
    I've found this article from 2013: https://mysqlserverteam.com/before-triggers-and-not-null-columns-in-mysql/. This is actually a know bug, I'm going to try to update my mysql version. – Mateus Neves Jan 26 '18 at 11:29
  • See here for explanation and solutions: https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values – anoldermark Jun 27 '18 at 14:05

1 Answers1

0

This is a known bug for the mysql 5.6.39 version. And has been fixed since version 5.7.

Mateus Neves
  • 304
  • 5
  • 16