Why does PHP PDO ignore NOT NULL constraints?

Question

I'm inserting values into my MariaDB using PDO in PHP. However, when I violate a NOT NULL constraint, nothing happens. I want an Exception to be thrown in that case.

This is how I created the table:

CREATE TABLE person (
    id INT auto_increment NOT NULL,
    firstname varchar(255) NULL,
    lastname varchar(255) NULL,
    telephone varchar(255) NULL,
    alias1 varchar(255) NOT NULL,
    alias2 varchar(255) NULL,
    CONSTRAINT PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

Here is the PHP script, I use for testing:

<?php

$conn = new PDO(
    'mysql:host=localhost;dbname=tibsi-data', 'root', '',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

try {
    $stm = $conn->prepare("INSERT INTO person (firstname) VALUES (:firstname)");
    $stm->bindValue(':firstname', 'Mike');

    $success = $stm->execute();
    if (!$success) {
        throw new Exception($stm->errorInfo()[2]);
    }
    $stm->closeCursor();

    $insertId = $conn->lastInsertId();

    header('HTTP/1.1 200 OK');
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(array('id' => $insertId));
    exit;
} catch (Exception $e) {
    header('HTTP/1.1 500 Internal Server Error');
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(array('error' => $e->getMessage()));
    exit;
} catch (Error $e) {
    header('HTTP/1.1 500 Internal Server Error');
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(array('error' => $e->getMessage()));
    exit;
}

As you can see alias1 does not have a default value and I omit it in my INSERT statement. I do get an error when I try the same thing using an SQL Script. I expect this to throw an exception in PHP resulting in a 500 response. Instead, it inserts the row using an empty string for alias1. I never set this as a default. Is there some attribute I can set to get the behavior I want?

Answer 1

See https://mariadb.com/kb/en/null-values/

If a NULL value is single-row inserted into a column declared as NOT NULL, an error will be returned. However, if the SQL mode is not strict (default until MariaDB 10.2.3), if a NULL value is multi-row inserted into a column declared as NOT NULL, the implicit default for the column type will be inserted (and NOT the default value in the table definition). The implicit defaults are an empty string for string types, and the zero value for numeric, date and time types.

Since MariaDB 10.2.4, by default both cases will result in an error.

The last statement applies if the sql_mode set strict mode. Strict mode is set on by default in MariaDB 10.2.4 and later, but you may have changed that option at the instance or session level. See https://mariadb.com/kb/en/sql-mode/

Check your current sql_mode in a query client:

SELECT @@sql_mode;

I'm not sure why the documentation refers to multi-row inserts specifically. The same thing happens if you insert a single row and strict mode is not enabled.