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
root
@%
TRIGGERinvoice_insert
BEFORE INSERT ONshipment
FOR EACH ROW BEGIN IF NEW.delivery_date = "" THEN SET NEW.delivery_date = NULL; END IF; END