Strange replication failure on date field under MySQL 5.7

All we need is an easy explanation of the problem, so here it is.

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 [email protected]% TRIGGER invoice_insert 
BEFORE INSERT ON shipment 
FOR EACH ROW BEGIN IF NEW.delivery_date = "" THEN SET NEW.delivery_date = NULL; 
END IF; 
END

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply