What causes MySQL not to enforce NOT NULL constraint?

Question

The following query surprised me and didn't fail, but inserted a record.

INSERT INTO buy_contacts(buys_id,contacts_id,buy_status,sites_id,record_status,date_modified,date_created)
VALUES(863999180,1367026068,"buy",2675631760,"active",NOW(),NOW());

A SELECT query shows it as being inserted.

+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
|  buys_id  | contacts_id | notes |  date_modified  |  date_created   | record_status |  sites_id  | buy_status | created_by_id | modified_by_id |
+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
| 863999180 |  1367026068 | NULL  | 1/10/2015 10:31 | 1/10/2015 10:31 | active        | 2675631760 | buy        |             0 |              0 |
+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+

My table schema (created by MySQL Workbench) is shown below. As seen, created_by_id and modified_by_id are both NOT NULL, do not have a DEFAULT value, and the query that inserted the record did not include these columns.

Why didn't MySQL reject the query due to a NOT NULL constraint and why did it insert values of zero for these columns?

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

// other tables are created...

CREATE TABLE IF NOT EXISTS `buy_contacts` (
  `buys_id` INT UNSIGNED NOT NULL,
  `contacts_id` INT UNSIGNED NOT NULL,
  `notes` TEXT NULL,
  `date_modified` DATETIME NOT NULL,
  `date_created` DATETIME NOT NULL,
  `record_status` VARCHAR(8) NOT NULL,
  `sites_id` INT UNSIGNED NOT NULL,
  `buy_status` CHAR(3) NOT NULL,
  `created_by_id` INT UNSIGNED NOT NULL,
  `modified_by_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`buys_id`, `contacts_id`),
  INDEX `fk_buys_has_contacts_contacts1_idx` (`contacts_id` ASC),
  INDEX `fk_buys_has_contacts_buys1_idx` (`buys_id` ASC),
  INDEX `fk_buy_contacts_record_status2_idx` (`record_status` ASC),
  INDEX `fk_buy_contacts_sites2_idx` (`sites_id` ASC),
  INDEX `fk_buy_contacts_buy_status1_idx` (`buy_status` ASC),
  INDEX `fk_buy_contacts_users1_idx` (`created_by_id` ASC),
  INDEX `fk_buy_contacts_users2_idx` (`modified_by_id` ASC),
  CONSTRAINT `fk_buys_has_contacts_buys1`
    FOREIGN KEY (`buys_id`)
    REFERENCES `buys` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buys_has_contacts_contacts1`
    FOREIGN KEY (`contacts_id`)
    REFERENCES `contacts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buy_contacts_record_status2`
    FOREIGN KEY (`record_status`)
    REFERENCES `record_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buy_contacts_sites2`
    FOREIGN KEY (`sites_id`)
    REFERENCES `sites` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buy_contacts_buy_status1`
    FOREIGN KEY (`buy_status`)
    REFERENCES `buy_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buy_contacts_users1`
    FOREIGN KEY (`created_by_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buy_contacts_users2`
    FOREIGN KEY (`modified_by_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

Check that you have STRICT_ALL_TABLES mode enabled and you have version 5.6 installed.

By default, MySQL uses obsolete "tolerant" mode, which was needed for non-transaction storage engines (MyISAM).