省略非默认字段时，在MariaDb上插入失败

Question

Table Structure:

CREATE TABLE `setup_int` (
    `key` VARCHAR(50) NOT NULL,
    `val` INT(11) NOT NULL,
    PRIMARY KEY (`key`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

As you can see the fields are NOT NULL-Fields and have no default value.

We updated to MariaDB 10.1.21 and have the following issue now: The following query fails without any error message!

INSERT INTO `setup_int` (`key`) VALUES ('test');

The reason seems to be the missing default value.

• If I add an default value to the table, the insert is successfull.
• If I change the NOT NULL to NULL, the insert is successfull.
• IF I do it on MariaDB 10.1.20, the insert is successfull.
• If I do the insert with both fields the insert is successfull.

This works:

INSERT INTO `setup_int` (`key`,`val`) VALUES ('test',0);

The question is: What can I do (some setting, or something else) to make mariadb 10.1.21 to handle this situation like before. I cannot alter all tables right now and i cannot downgrade right now.

The main issue is that the query executed by PHP returns true although the insert failed!

Answer 1

Take a look here: https://stackoverflow.com/a/2503938/1973205

And then you can set the variables like this: https://mariadb.com/kb/en/mariadb/sql-mode/

As a pure logic fact, it's correct that the query fails.