MySQL Bugs: #104851: mysql RC select data not return all, just return first row of batch.

Bug #104851	mysql RC select data not return all, just return first row of batch.
Submitted:	7 Sep 2021 4:05	Modified:	7 Sep 2021 13:04
Reporter:	blue jerins	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.23-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	rc

Description:
I have multiple concurrency thread to execute the task, each task execute by separate id range. like sql:

select id from xxx where id between 1 and 1001 and accountant_time = '2021-05-31 00:00:00' and enter_accounts_state = 1 and enter_ce_state = 2 and ebs_summary_state = 2 and is_del = 0

first select the id collection and then delete the data use id, like sql below:

DELETE FROM xxx WHERE confirm_state = 3 AND id IN ( 1, 2, 3, ..., 1001);

the above two steps in one transaction, and total about 8000 tasks to execute,
only 7 or 8 task abnormal, the first step expect return 1001 rows, but only return the first row of the batch. if I add 'for update' for the select sql, it works normal.

if I change the batch count to 888 or 555, it the same result that some task only return first row of that batch.

I printed the general_log and not found the ids executed repeated.

also I tried in single thread, it worked normal.

the following list is the env;
version: 5.7.23-log
transaction_isolation: READ-COMMITTED
tx_isolation: READ-COMMITTED
innodb_locks_unsafe_for_binlog: off

How to repeat:
I ran many times use the same code and same data, also 7 or 8 batch count would be left, and only first row of that batch selected. but the left data not same every times.

Hi Mr. jerins,

Thank you for your bug report.

However, it is not a bug.

InnoDB is a MVCC engine, which is why normal SELECTs run in their own snapshot , without need for strict locking of the rows. So, if you want to do it correctly, you have to add FOR UPDATE clause.

Also, you do not need to run the transaction in two steps. You can simply run DELETE DML with the same filtering conditions as SELECT statement.

Not a bug.

Thanks for the reply.
I know it not strict in RC mode, but I can not understand why it only returns the first row of the batch. When I change the batch count to 555 or 888. It also just returns the first row of the batch in very view batches。

Looking forward to your reply, thanks.

Hi,

We do not know your schemas, relations nor values.

You should try running it in the read-only mode or in consistent mode  (both described in the manual) and if you get the same result, then it is up to your data. Otherwise, it is due to the timing between different concurrent threads.

Hi, thanks for reply:

It ran normal in read-only mode and also the data is not problem. And all of the data already exist in db before the tasks execute.

It really seems that due to the timing between different concurrent threads.

But it is regular that only return first row，is it any reference manual ?

Here is my table schema:

CREATE TABLE `payment_proceeds_current` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT comment '',
  `proceeds_code` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `source_code` varchar(225) CHARACTER  NOT NULL comment '',
  `source_name` varchar(225) CHARACTER  NOT NULL comment '',
  `payment_water_code` varchar(128) DEFAULT NULL,
  `payment_type_code` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `payment_type_name` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `exchange_time` timestamp NOT NULL comment '',
  `accountant_time` timestamp NOT NULL comment '',
  `financial_system_code` varchar(255) CHARACTER  NOT NULL comment '',
  `financial_system_name` varchar(255) CHARACTER  NOT NULL comment '',
  `account_book_type` varchar(64)  DEFAULT NULL comment '',
  `account_type_code` varchar(255) CHARACTER  NOT NULL comment '',
  `account_type_name` varchar(255) CHARACTER  NOT NULL comment '',
  `nostro_bank_account_number` varchar(255) CHARACTER  NOT NULL comment '',
  `service_line_code` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `service_line_name` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `exchange_currency_code` varchar(255) CHARACTER  NOT NULL comment '',
  `exchange_currency_name` varchar(255) CHARACTER  NOT NULL comment '',
  `exchange_money_amount` bigint(17) NOT NULL comment '',
  `default_exchange_rate` varchar(255)  DEFAULT NULL comment '',
  `exchange_rate` decimal(32,18) DEFAULT NULL comment '',
  `nostro_currency_code` varchar(255)  DEFAULT NULL comment '',
  `nostro_currency_name` varchar(255)  DEFAULT NULL comment '',
  `nostro_money_amount` bigint(17) DEFAULT NULL comment '',
  `degest_comment` varchar(2048) DEFAULT NULL comment '',
  `product_name` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `reciprocal_bank_account_name` varchar(512) DEFAULT NULL comment '',
  `reciprocal_bank_account_number` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `confirm_money_amount` bigint(17) DEFAULT NULL comment '',
  `un_confirm_money_amount` bigint(17) NOT NULL comment '',
  `charge_against_code` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `charge_against_state` tinyint(3) DEFAULT NULL comment '',
  `charge_against_reason` varchar(512) CHARACTER  DEFAULT NULL comment '',
  `enter_accounts_state` tinyint(3) NOT NULL comment '',
  `confirm_state` tinyint(3) NOT NULL comment '',
  `creater_name` varchar(255)  DEFAULT NULL comment '',
  `is_del` tinyint(1) DEFAULT '0' comment '',
  `is_advance` varchar(2)  DEFAULT 'N' comment '',
  `source_proceeds_id` bigint(11) DEFAULT NULL comment '',
  `enter_ce_state` tinyint(3) DEFAULT NULL comment '',
  `summary_proceeds_id` bigint(11) DEFAULT NULL comment '',
  `summary_proceeds_code` varchar(255)  DEFAULT NULL comment '',
  `enter_lock` tinyint(3) DEFAULT NULL comment '',
  `create_by` bigint(20) DEFAULT NULL comment '',
  `create_time` timestamp NULL DEFAULT NULL comment '',
  `update_by` bigint(20) DEFAULT NULL comment '',
  `update_time` timestamp NULL DEFAULT NULL comment '',
  `merchant_number` varchar(255)  DEFAULT NULL comment '',
  `account_transaction` varchar(255)  DEFAULT NULL comment '',
  `business_name` varchar(255)  DEFAULT NULL comment '',
  `business_type` varchar(255)  DEFAULT NULL comment '',
  `payments_direction` tinyint(3) DEFAULT NULL comment '',
  `merchant_order_number` varchar(255)  DEFAULT NULL comment '',
  `bank_db_urid` varchar(255)  DEFAULT NULL comment '',
  `bank_match_book_batch_number` varchar(255)  DEFAULT NULL comment '',
  `confirm_lock` tinyint(3) NOT NULL comment '',
  `confirm_platform_code` varchar(255)  DEFAULT NULL comment '',
  `payment_type_processing_mode` varchar(255) CHARACTER  DEFAULT NULL comment '',
  `balance_money` bigint(17) DEFAULT NULL comment '',
  `subhead_code` varchar(255)  DEFAULT NULL comment '',
  `subhead_code_name` varchar(255)  DEFAULT NULL comment '',
  `sub_account` varchar(255)  DEFAULT NULL comment '',
  `summary_state` tinyint(3) DEFAULT NULL comment '',
  `account_summary_type` tinyint(3) DEFAULT NULL comment '',
  `product_sum_name` varchar(100)  DEFAULT NULL comment '',
  `budget_item` varchar(100)  DEFAULT NULL comment '',
  `payment_nature` varchar(20)  DEFAULT NULL comment '',
  `ebs_summary_state` tinyint(3) DEFAULT '1' comment '',
  `previous_id_within_subhead` bigint(11) DEFAULT NULL comment '',
  `update_time_second` timestamp NULL DEFAULT NULL,
  `source_type` tinyint(1) DEFAULT '1' comment '',
  `business_key` varchar(64)  DEFAULT NULL comment '',
  `auto_recognition_rule_id` int(11) DEFAULT NULL comment '',
  `is_auto_recognition` tinyint(4) DEFAULT NULL comment '',
  PRIMARY KEY (`id`),
  KEY `proceeds_third_index` (`proceeds_code`,`bank_db_urid`) USING BTREE comment '',
  KEY `idx_proceeds_bpm_index_bank` (`bank_db_urid`),
  KEY `idx_financial_bank_account` (`financial_system_code`(6),`nostro_bank_account_number`(26)),
  KEY `idx_query_proceeds_index` (`payment_water_code`),
  KEY `idx_push_bank_proceeds` (`account_type_code`,`confirm_state`),
  KEY `idx_push_ebs` (`enter_ce_state`,`confirm_state`),
  KEY `idx_summary_proceeds_id` (`summary_proceeds_id`),
  FULLTEXT KEY `idx_recofultext` (`reciprocal_bank_account_name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=144602864 DEFAULT CHARSET=utf8mb4 COMMENT='';

Yes, it is , because it does depend on the timing.

This problem should disappear if you rewrite your DML as instructed.