0

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
6
  • The move from 5.6 to 5.7 finished succesfully? No traces left of the 5.7 binaries on the slave? Jul 21, 2021 at 14:21
  • @GerardH.Pille I assume so. This is an RDS instance, so I don't have control over the binaries it's using. The DB is running fine in every other respect
    – Machavity
    Jul 21, 2021 at 14:22
  • hm this should run dbfiddle.uk/…
    – nbk
    Jul 21, 2021 at 15:07
  • Strange that the error doesn't correspond to the insert statement. There wouldn't be an insert trigger on that table? Jul 21, 2021 at 16:09
  • I take it back. I was looking at the live server, which has no triggers. This one had one that was removed. 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
    – Machavity
    Jul 21, 2021 at 19:02

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.