MySQL Trigger无法在PHP中运行

Question

TL;DR: Query works from MySQL CLI but NOT from PHP, as if the triggers were not fired by PHP.

I have a mySQL structure which looks like this:

• entity is the "parent" table with id as PK
• contact is a "child" table with entity_id as PK and FK on entity
• person is a "grand-child" table with entity_id as PK and FK on contact

I have created some triggers so that when I insert a new row in contact, it first inserts a new row in entity and uses the new id as entity_id. Idem for person and contact. Triggers are all made on the same model, here's one:

DELIMITER #
DROP TRIGGER IF EXISTS contact_insert_trig;
CREATE TRIGGER contact_insert_trig BEFORE INSERT ON `contact`
FOR EACH ROW BEGIN
    INSERT INTO entity (cat_id) values (1);
    SET NEW.entity_id = (SELECT id FROM entity ORDER BY id DESC LIMIT 1);
    SET NEW.entity_cat_id = 1;
END; #

So, for instance, when I insert a new row in person, the first trigger creates a row in contact which, via the second trigger, creates a row in entity, all with matching ids.

This WORKS in MySQL but NOT in PHP. The same query:

INSERT INTO person (first_name, last_name) VALUES ("Bob", "McIntosh")

works in the mySQL CLI (all rows are created and match up) but fails when called with mysqli->query() in PHP7 with the error:

Field 'entity_id' doesn't have a default value

How can I make it work in PHP as well? Or do I have to go through the process of creating all parent tables manually?

Answer 1

This did the trick, now queries work from PHP as well :

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

(or don't turn on STRICT_TRANS_TABLES).