Strange replication failure on date field under MySQL 5.7

Question

This problem cropped up yesterday and I'm quite stumped. I have a primary RDS cluster that is running MySQL 5.6 and RDS is forcing everyone to 5.7 soon. I have a smaller standalone replication slave we use for some heavy reporting queries. The slave was moved to 5.7 yesterday (I think RDS didn't allow the primary to upgrade while the slave was upgrading), and replication ran fine for about 6 hours, but finally stopped hard with this 1292 SQL error

Error 'Incorrect date value: '' for column 'delivery_date' at row 1' on query. 
Default database: 'dbname'. Query: 'INSERT shipment SET
                    carrier = "Shipper",
                    bill_number = "1234",
                    tracking_num = "1234",
                    invoice_date = "2021-07-08",
                    fee = "1.00",
                    ship_date = "2021-06-10",
                    delivery_date = NULL,
                    zip_from = "ABCD",
                    zip_to = "",
                    recipient = "||",
                    shipper = "COMPANY",
                    notes = "||||",
                    found = "No"'

This error doesn't make sense. That field allows NULL so it shouldn't be an issue. Here's the structure

CREATE TABLE `shipment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `carrier` varchar(45) NOT NULL,
  `bill_number` varchar(45) NOT NULL,
  `tracking_num` varchar(45) NOT NULL,
  `invoice_date` date NOT NULL,
  `fee` decimal(8,2) NOT NULL,
  `ship_date` date DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `recipient` varchar(255) DEFAULT NULL,
  `shipper` varchar(255) DEFAULT NULL,
  `zip_from` varchar(5) DEFAULT NULL,
  `zip_to` varchar(5) DEFAULT NULL,
  `notes` text,
  `found` enum('No','Yes','PO','Invoice','Event','Manual','Fee','Supplies') NOT NULL DEFAULT 'No',
  PRIMARY KEY (`id`),
  UNIQUE KEY `carrier` (`carrier`,`bill_number`,`tracking_num`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This is the sql-mode from the config

sql-mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I've tried removing STRICT_TRANS_TABLES and even most of the whole set of configs there (RDS won't let you blank the field) and no luck. Our development server has used 5.7 for quite some time and accepts the query without complaint (both are 5.7.34). Skipping doesn't help because there are several dozen records it needs to update like this so it still breaks.

Apparently there was a trigger not in the live DB that is still on the replication server

CREATE DEFINER=root@% TRIGGER invoice_insert 
BEFORE INSERT ON shipment 
FOR EACH ROW BEGIN IF NEW.delivery_date = "" THEN SET NEW.delivery_date = NULL; 
END IF; 
END

Answer 1

In the trigger, change

IF NEW.delivery_date = ""

to

IF NEW.delivery_date < '1971-01-01'

That will catch empty string and '1970-01-01' and any timezone variation on that "zero" date. It will fail if it is already NULL`, but that won't change the action of the Trigger.

(In the future, be sure to include any relevant Triggers in the Question.)