Insert NULL columns in NOT NULL with default value

Question

For a small background, we use Zend Framework 2 and Doctrine . The doctrine will always insert NULL for values ​​that we do not fill. This is usually normal, as if the field has a default value, then it SHOULD populate the field with this default value.

For one of our servers running MySQL 5.6.16 , a query such as the one below starts and runs fine. Although NULL inserted into a field that is not null, MySQL populates the field with the default value to insert.

On another server running MySQL 5.6.20 , we run the query below and it crashes because it complains that 'field_with_default_value' cannot be null.

 INSERT INTO table_name(id, field, field_with_default_value) VALUES(id_value, field_value, NULL); 

Doctrine itself does not support passing through "DEFAULT" in the queries it creates, so this is not an option. I believe that it should be something like a MySQL server, as if it works fine in one version, but not in another, but, unfortunately, I have no idea what it could be. Our SQL mode is also identical on both servers ( 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ).

I should probably mention, if I really run the above SQL in Workbench, it still doesn't work that way. So this is not a Doctrine problem, but definitely a MySQL problem.

Any help on this would be greatly appreciated.

Answer 1

According to the documentation, everything works as expected.

Test case:

 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.6.16 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL', @@SESSION.sql_mode 'sql_mode::SESSION'; +------------------------+------------------------+ | sql_mode::GLOBAL | sql_mode::SESSION | +------------------------+------------------------+ | NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION | +------------------------+------------------------+ 1 row in set (0.00 sec) mysql> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL', @@SESSION.sql_mode 'sql_mode::SESSION'; +------------------------+-----------------------------------------------------------------------------------------------------------------+ | sql_mode::GLOBAL | sql_mode::SESSION | +------------------------+-----------------------------------------------------------------------------------------------------------------+ | NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------+-----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE `table_name`; +------------+----------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------+ | table_name | CREATE TABLE `table_name` ( | | | `id` INT(11) UNSIGNED NOT NULL, | | | `field` VARCHAR(20) DEFAULT NULL, | | | `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' | | | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`) VALUES (1, 'Value', NULL); ERROR 1048 (23000): Column 'field_with_default_value' cannot be null 

Can you place the appropriate part of the structure of your table to find out how we can help?

UPDATE

MySQL 5.7, using triggers, can provide a possible solution to the problem:

Changes in MySQL 5.7.1 (2013-04-23, step 11)

...

• If a column is declared NOT NULL, it is not allowed to insert NULL into the column or update it to NULL. However, this restriction was applied even if there was a BEFORE INHERITANCE (or BEFORE UPDATING trigger) that set the column to a value other than NULL. Now the constraint is checked at the end of the statement in accordance with the SQL standard. (Bug #6295, Bug # 11744964).

...

Possible Solution:

 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.4-m14 | +-----------+ 1 row in set (0.00 sec) mysql> DELIMITER $$ mysql> CREATE TRIGGER `trg_bi_set_default_value` BEFORE INSERT ON `table_name` FOR EACH ROW BEGIN IF (NEW.`field_with_default_value` IS NULL) THEN SET NEW.`field_with_default_value` := (SELECT `COLUMN_DEFAULT` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'table_name' AND `COLUMN_NAME` = 'field_with_default_value'); END IF; END$$ mysql> DELIMITER ; mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`) VALUES (1, 'Value', NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT `id`, `field`, `field_with_default_value` FROM `table_name`; +----+-------+--------------------------+ | id | field | field_with_default_value | +----+-------+--------------------------+ | 1 | Value | myDefault | +----+-------+--------------------------+ 1 row in set (0.00 sec) 

Answer 2

MySQL actually works as intended, and this behavior seems to remain there . MariaDB also works the way it does now .

Removing "strict mode" ( STRICT_TRANS_TABLES and STRICT_ALL_TABLES ) should return to the previous behavior, but I personally have no luck (maybe I am doing something wrong, but both @@GLOBAL.sql_mode and @@SESSION.sql_mode do not contain strict mode) .

I think the best solution to this problem is to use default values ​​at the PHP level, rather than relying on the database to provide them. There is an existing answer that explains this pretty well. Comments are also helpful.

That way, you also get the added benefit that your models / entities will have a default value when creating an instance, and not after inserting into the database. In addition, if you want to put these values ​​to the user after insertion, you can do this without having to do an additional SELECT query after INSERT .

Another alternative to the default surface is to use the RETURNING article , as it is available in PostgreSQL, but not in MySQL (though). It may be added at some point in the future, but at the moment MariaDB has only DELETE . However, I believe that the presence of default values ​​at the PHP level is still higher; even if you never insert a record, it will still contain default values. I never came back and used the default value for the database, as that was the practice.

Answer 3

Based on my research, I would say that it can be either "you" or "MySQL". Check table definitions with SHOW CREATE TABLE table_name; . Pay attention to any fields defined using NOT NULL .

MySQL 5.6 Reference Manual: 13.2.5 INSERT Syntax :

Insert NULL into a column that was declared NOT NULL. For multi-line INSERT or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This number is 0 for numeric types, an empty string ('') for string types, and a null value for date and time types. INSERT IN ... SELECT statements are treated the same as inserting multiple rows because the server does not check the result set from SELECT until you see if it returns a single row. ( For a single-line INSERT, no warning appears when NULL is inserted into the NOT NULL column. Instead, the error fails with an error. )

This means that it doesn't matter which SQL mode you use. If you execute a single INSERT row (as per your code example) and insert a NULL value into a column defined using NOT NULL , it should not work.

At the same time, ironically, if you just omitted the value from the list of values, the following is indicated in the MySQL manual, in which case SQL mode matters:

If you are not working in strict SQL mode, any column is not explicitly specified, the value is set to the default value ( Explicit or Implicit ). For example, if you specify a list of columns that does not name all the columns in the table, unnamed columns have default values. The purpose of the default value is described in section 11.6 “Data Type Default Values”. See also section 1.7.3.3, “Restrictions on Invalid Data”.

So you cannot win! ;-) Joke. I would like to accept that NOT NULL in a field of a MySQL table really means that I will not accept the value NULL for a field when executing a single INSERT line, regardless of the SQL mode. ''

All of the above reads as follows:

To enter data in a NOT NULL column that does not have an explicit DEFAULT value, if the INSERT or REPLACE statement does not contain a value for the column, or the UPDATE statement sets the column to NULL, MySQL processes the column according to the current SQL mode:

If strict SQL mode is enabled , an error occurs for the transaction table and the rollback statement. For nontransactional tables, an error occurs, but if this happens for the second or next line of a multiline statement, the previous lines would be inserted.

If strict mode is not enabled, MySQL sets the column to an implicit default value for the column data type.

So, with a heart. Set your default values ​​in the business logic (objects) and let the data layer take direction from that. Databases by default seem like a good idea, but if they weren't there, would you skip them? If a tree falls into the forest ...

Answer 4

I encountered the same problem after updating MySQL. It turns out that there is a parameter that allows you to insert NULL values ​​into the NOT NULL mark fields and get the default value.

 explicit_defaults_for_timestamp=0 

This is documented at https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

Answer 5

If you skip a column (both name and value) from the statement, the default value will be used.

A few related tips:

• You should not have any "non-empty" default values ​​in your tables and should not have non-zero default values ​​for columns that are nullable. Let all the values ​​be set from the application.
• Do not place business logic on the database side.

Define the default value only if it is really necessary, and only for columns that do not allow null values ​​And remove the default settings when they are no longer needed. (they come in handy when starting the change table to set the value of the new column, but then immediately start the new (cheap!) change to remove the default value)

The above "empty" refers to the type: - 0 for numeric columns, - '' for varchar / varbinary columns, - '1970-01-01 12:34:56' for timestamps, - etc.

This saves the application a lot of trips to the database. If the created string is completely predictable, then the application does not need to read it after creation to find out what it has become. (this assumes: no triggers, no cascading)

In MySQL, we make only a few specific exceptions to these strict rules:

• Columns called mysql_row_foo are set by the database only. Examples:

    mysql_row_created_at timestamp (6) not null default '1970-01-01 12: 34: 56.000000',
     mysql_row_updated_at timestamp (6) null default null on update current_timestamp,
  

• Unique indexes for non-zero columns are encouraged to prevent data duplication. For example, for lookup.brand.name in the lookup.brand table, which looks like (id ++, name).

The mysql_row_foo columns are similar to the column attributes. For example, they are used by data synchronization tools. Normal applications do not read them, and they store their timestamps on the application side as epoch values. Examples:

  valid_until_epoch int unsigned not null default 0,
  last_seen_epoch_ms bigint not null default 0,