Bug #118230 When innodb_buffer_pool_size is set to 134217728, the update value is abnormal.
Submitted: 20 May 2:24 Modified: 20 May 4:43
Reporter: fent allen Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.41 OS:Linux
Assigned to: MySQL Verification Team CPU Architecture:x86

[20 May 2:24] fent allen 
Description:
When innodb_buffer_pool_size is set to 134217728, An error occurred when updating the timestamp_field field

How to repeat:
For details about table_test.sql, An error occurred when updating the timestamp_field field. see the attachment.

Normal scenario:
set global innodb_buffer_pool_size=10737418240;
source table_test.sql

mysql> show global variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 10737418240 |
+-------------------------+-------------+
1 row in set (0.01 sec)

mysql> UPDATE { OJ ( test.update_v_tbtest ) } NATURAL RIGHT OUTER JOIN update_tbtest_no_part_02 USE INDEX FOR ORDER BY () IGNORE KEY FOR ORDER BY (idx_f) SET update_v_tbtest.tinyint_field =( (FLOOR(0.1)) & ((select 1 from update_tbtest_part_2 where id = 1)) ) , update_v_tbtest.text_field =( (select ('abcdefg' not REGEXP char_field) from update_tbtest_part_2 where id = 1) ) , update_v_tbtest.timestamp_field = (((select '2018-09-02 10:16:13' from update_tbtest_part_2 where id = 1)) + INTERVAL (update_v_tbtest.tinyint_field) DAY ) WHERE (update_v_tbtest.id=228138 or update_v_tbtest.id=228238 or update_v_tbtest.id=228338 or update_v_tbtest.id=228438 or update_v_tbtest.id=228538 or update_v_tbtest.id=228638 or update_v_tbtest.id=228738 or update_v_tbtest.id=228838) AND (update_tbtest_no_part_02.id=228138 or update_tbtest_no_part_02.id=228238 or update_tbtest_no_part_02.id=228338 or update_tbtest_no_part_02.id=228438 or update_tbtest_no_part_02.id=228538 or update_tbtest_no_part_02.id=228638 or update_tbtest_no_part_02.id=228738 or update_tbtest_no_part_02.id=228838);
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM update_v_tbtest WHERE (id=228138 or id=228238 or id=228338 or id=228438 or id=228538 or id=228638 or id=228738 or id=228838) ORDER BY id ;
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
| id     | tinyint_field | smallint_field | bigint_field | float_field | datetime_field      | timestamp_field     | char_field | varchar_field | text_field |
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
| 228138 |             0 |              1 |       228138 |      228138 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228138     | aaaa228138    | 1          |
| 228238 |             0 |              1 |       228238 |      228238 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228238     | aaaa228238    | 1          |
| 228338 |             0 |              1 |       228338 |      228338 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228338     | aaaa228338    | 1          |
| 228438 |             0 |              1 |       228438 |      228438 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228438     | aaaa228438    | 1          |
| 228538 |             0 |              1 |       228538 |      228538 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228538     | aaaa228538    | 1          |
| 228638 |             0 |              1 |       228638 |      228638 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228638     | aaaa228638    | 1          |
| 228738 |             0 |              1 |       228738 |      228738 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228738     | aaaa228738    | 1          |
| 228838 |             0 |              1 |       228838 |      228838 | 2018-08-08 00:00:00 | 2018-09-03 10:16:13 | 228838     | aaaa228838    | 1          |
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
8 rows in set (0.00 sec)

Error scenarioļ¼š
set global innodb_buffer_pool_size=134217728;
source table_test.sql

mysql> show global variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

mysql> UPDATE { OJ ( test.update_v_tbtest ) } NATURAL RIGHT OUTER JOIN update_tbtest_no_part_02 USE INDEX FOR ORDER BY () IGNORE KEY FOR ORDER BY (idx_f) SET update_v_tbtest.tinyint_field =( (FLOOR(0.1)) & ((select 1 from update_tbtest_part_2 where id = 1)) ) , update_v_tbtest.text_field =( (select ('abcdefg' not REGEXP char_field) from update_tbtest_part_2 where id = 1) ) , update_v_tbtest.timestamp_field = (((select '2018-09-02 10:16:13' from update_tbtest_part_2 where id = 1)) + INTERVAL (update_v_tbtest.tinyint_field) DAY ) WHERE (update_v_tbtest.id=228138 or update_v_tbtest.id=228238 or update_v_tbtest.id=228338 or update_v_tbtest.id=228438 or update_v_tbtest.id=228538 or update_v_tbtest.id=228638 or update_v_tbtest.id=228738 or update_v_tbtest.id=228838) AND (update_tbtest_no_part_02.id=228138 or update_tbtest_no_part_02.id=228238 or update_tbtest_no_part_02.id=228338 or update_tbtest_no_part_02.id=228438 or update_tbtest_no_part_02.id=228538 or update_tbtest_no_part_02.id=228638 or update_tbtest_no_part_02.id=228738 or update_tbtest_no_part_02.id=228838);
Query OK, 8 rows affected (0.02 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM update_v_tbtest WHERE (id=228138 or id=228238 or id=228338 or id=228438 or id=228538 or id=228638 or id=228738 or id=228838) ORDER BY id ;
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
| id     | tinyint_field | smallint_field | bigint_field | float_field | datetime_field      | timestamp_field     | char_field | varchar_field | text_field |
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
| 228138 |             0 |              1 |       228138 |      228138 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228138     | aaaa228138    | 1          |
| 228238 |             0 |              1 |       228238 |      228238 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228238     | aaaa228238    | 1          |
| 228338 |             0 |              1 |       228338 |      228338 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228338     | aaaa228338    | 1          |
| 228438 |             0 |              1 |       228438 |      228438 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228438     | aaaa228438    | 1          |
| 228538 |             0 |              1 |       228538 |      228538 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228538     | aaaa228538    | 1          |
| 228638 |             0 |              1 |       228638 |      228638 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228638     | aaaa228638    | 1          |
| 228738 |             0 |              1 |       228738 |      228738 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228738     | aaaa228738    | 1          |
| 228838 |             0 |              1 |       228838 |      228838 | 2018-08-08 00:00:00 | 2018-09-02 10:16:13 | 228838     | aaaa228838    | 1          |
+--------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+------------+
8 rows in set (0.00 sec)