0

I've a table with a 5 million of records. Suddenly Database table stopped inserting a record. while record exists in parent table and record insertion is same in child table as It supposed to be.

But do not understand what actually happened. I've also make sure by selecting the field from navicat foreign key filter.

This is the screenshot of insert query in child table and parent table (select query) where we can see the record.

enter image description here

Query:

INSERT INTO payment_covered_by ( pay_cov_covered_by_payment_id, pay_cov_payment_id, pay_cov_invoice_id, pay_cov_amount )
VALUES
    ( '10', '10', '1235681', '20.00' );

Parent table structure is

CREATE TABLE `payments` (
  `pay_id` int(11) NOT NULL AUTO_INCREMENT,
  `pay_co_id` int(11) NOT NULL,
  `pay_customer_id` bigint(20) DEFAULT NULL,
  `pay_user_id` int(11) DEFAULT NULL,
  `pay_method` int(11) DEFAULT NULL,
  `pay_date` date NOT NULL,
  `pay_total_amount` decimal(10,2) NOT NULL,
  `pay_inv_id` bigint(20) DEFAULT NULL,
  `pay_inv_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`pay_id`) USING BTREE,
  KEY `pay_method` (`pay_method`) USING BTREE,
  KEY `pay_user_id` (`pay_user_id`) USING BTREE,
  KEY `pay_customer_id` (`pay_customer_id`) USING BTREE,
  KEY `pay_co_id` (`pay_co_id`) USING BTREE,
  KEY `pay_inv_id` (`pay_inv_id`) USING BTREE,
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`pay_method`) REFERENCES `payments_method` (`paymethod_id`),
  CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`pay_co_id`) REFERENCES `company` (`co_id`) ON DELETE CASCADE,
  CONSTRAINT `payments_ibfk_3` FOREIGN KEY (`pay_customer_id`) REFERENCES `customs` (`custom_id`) ON DELETE SET NULL,
  CONSTRAINT `payments_ibfk_4` FOREIGN KEY (`pay_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2674670 DEFAULT CHARSET=utf8

Child Table

CREATE TABLE `payment_covered_by` (
  `pay_cov_id` int(11) NOT NULL AUTO_INCREMENT,
  `pay_cov_covered_by_payment_id` int(11) NOT NULL,
  `pay_cov_payment_id` int(11) NOT NULL,
  `pay_cov_invoice_id` bigint(20) NOT NULL,
  `pay_cov_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`pay_cov_id`) USING BTREE,
  KEY `pay_cov_covered_by_payment_id` (`pay_cov_covered_by_payment_id`) USING BTREE,
  KEY `pay_cov_payment_id` (`pay_cov_payment_id`) USING BTREE,
  KEY `pay_cov_invoice_id` (`pay_cov_invoice_id`) USING BTREE,
  CONSTRAINT `payment_covered_by_ibfk_1` FOREIGN KEY (`pay_cov_covered_by_payment_id`) REFERENCES `payments` (`pay_id`) ON DELETE CASCADE,
  CONSTRAINT `payment_covered_by_ibfk_2` FOREIGN KEY (`pay_cov_payment_id`) REFERENCES `payments` (`pay_id`) ON DELETE CASCADE,
  CONSTRAINT `payment_covered_by_ibfk_3` FOREIGN KEY (`pay_cov_invoice_id`) REFERENCES `invoices` (`inv_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45397 DEFAULT CHARSET=utf8

enter image description here

10
  • The database says the parent record does not exist; you say it does. Who should we believe, and why?
    – mustaccio
    Sep 17, 2021 at 14:36
  • @mustaccio I've updated the question and here's the link of query as a proof i.stack.imgur.com/dMl6m.png Sep 17, 2021 at 15:15
  • Why are you inserting character literals into integer and decimal columns? Have you tried using correct data types?
    – mustaccio
    Sep 17, 2021 at 15:20
  • I've tried by adding a record directly in table editor. Sep 17, 2021 at 15:23
  • Are your select and insert queries definitely executed against the same database?
    – dbdemon
    Sep 17, 2021 at 15:24

1 Answer 1

1

A possible solution may be to rebuild the indexes:

ALTER TABLE payments ENGINE=InnoDB;
ALTER TABLE payment_covered_by ENGINE=InnoDB;

Caveat: If the tables are large, they may take a long time.

5
  • before run 1- It is safe? because I've more than 5-10 million of records inside a database. issue is only happening in production. 2- but what happened sudden? Sep 17, 2021 at 17:29
  • Corrupted indexes in InnoDB are extremely rare. If that is the problem, I have no explanation. Those 'simple' queries rebuild the indexes. Feel free to take a full dump of your database first.
    – Rick James
    Sep 17, 2021 at 17:44
  • okay dumping and then try Sep 17, 2021 at 17:46
  • thank you. I run this query, then all is good from last 12 hours. ALTER TABLE payment_covered_by ENGINE=InnoDB; But I've a question I've updated two old tables (new column added by running Laravel migration) which is directly connected with this table. now I'm curious, Do I've to again run this command every time If I update those tables. Sep 18, 2021 at 14:44
  • 1
    @AbdulRehman - You should not need it. Nor should you have needed it in the first place. My Answer comes in the category of "if all else fails, do this".
    – Rick James
    Sep 18, 2021 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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