0

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?

Improve this question

2 Answers 2

Reset to default
0

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

Improve this answer
1
  • Thank you @Rick James for your quick response Commented Dec 23, 2022 at 6:53
0

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?)
Improve this answer
1
  • Thanks for your answer, sorry for the lack of information in my question, I edited the comment. Commented Dec 22, 2022 at 10:14

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.