MySQL Error: Can't find record in table_name

Question

I use MySQL version 5.7.39, ENGINE=InnoDB, Isolation = Read-Uncommitted:

Table product with structure:

CREATE TABLE `product` (
  `category_id` varchar(50) NOT NULL,
  `product_seq` bigint(20) unsigned NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `release_date` date NOT NULL,
  `price` bigint(20) unsigned NOT NULL,
  `expiry_date` date NOT NULL,
  `manufacturing_date` date NOT NULL,
  `deleteFlag` varchar(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`,`product_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table product_detail with structure:

CREATE TABLE `product_detail` (
  `id` varchar(50) NOT NULL,
  `product_seq` bigint(20) unsigned NOT NULL,
  `dt_id` bigint(20) unsigned NOT NULL,
  `cal_id` bigint(20) unsigned NOT NULL,
  `abbreviations_nm` varchar(5) DEFAULT NULL,
  `production` varchar(255) NOT NULL,
  `raw_materials` varchar(1024) DEFAULT NULL,
  `type` varchar(1024) DEFAULT NULL,
  `period` bigint(20) NOT NULL,
  `deleteFlag` varchar(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

One batch runs every day at a fixed time, when it runs it truncates and inserts new data into table product_detail (It's on another system so I don't have any information about the SQL statement).

At the same time batch running, my system run query to get data from 2 table above:

SELECT p.category_id, p.product_name, pd.product_seq , pd.period, pd.production
FROM product p
LEFT JOIN product_detail pd ON p.product_seq = pd.product_seq
ORDER BY p.category_id, pd.product_seq

Sometimes (Rarely happens) it returns an error log when executing SQL: Can't find record in 'product_detail'

This error log return by Outsystems so it doesn't have any other specific warnings or errors log.

I found a thread report this bug on MySQL: https://bugs.mysql.com/bug.php?id=1574

However, I still don't understand what causes the error and how to reproduce it for investigation. Can anyone help me to explain the problem?

Answer 1

Plan A:

I understand that you don't have control over the other process, but let me say that "truncates and inserts new data" is asking for trouble and inefficiency. That process should

1. Load the data into a new table
2. RENAME TABLE table TO old_table, new_table TO table;
3. DROP TABLE old_table;

That way, the data is unavailable only for a very brief time during step 2.

Plan B:

If you can't get them to fix that, you should find another way to do your task.

Plan C:

Well, maybe you can at least program defensively -- Repeat until successful:

1. Catch the error
2. Sleep for 1 second
3. try again.

Plan D:

Or... (This is especially ugly.)

1. LOCK TABLE product, product_detail READ;
2. Perform your query
3. UNLOCK TABLES;

But that could take a long time -- if you are running while it is running. I don't know if you could experience some "timeout".

Answer 2

You are using InnoDB, correct?
You have suitable transactions around the inserts and deletes, correct?

A guess is that you did something like this:

BEGIN;
SELECT ...;  -- in prep for the coming DELETE
blah, blah
DELETE ...; -- based on what the SELECT found
COMMIT;

Without FOR UPDATE on the SELECT, another thread might reach for the same row and not find it. (Or some other combination of events.)

For further discussion, please provide

SHOW CREATE TABLE ...
The statements in both transactions (one for each client).
Any specific WARNINGS (you are checking for them, correct?)
Any specific ERRORs (you are checking for them, correct?)