Insert on MariaDb fails when ommitting non-default fields

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.

Answer 2

Basically you set a constraint that the value of val must not be NULL and it fails because you try to set it NULL.

(0 is not the same as NULL.)

The default value is set during CREATE:

CREATE TABLE `setup_int` (
    `key` VARCHAR(50) NOT NULL,
    `val` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`key`)
)