| Bug #59297 | Can't find record in 'tablename' on update inner join (5.1.54) | ||
|---|---|---|---|
| Submitted: | 5 Jan 2011 8:23 | Modified: | 10 Mar 2011 9:37 |
| Reporter: | SangChul SIM | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.54 | OS: | Any (Linux(2.6.18-8.1.15.el5, i386), FreeBSD(7.0,i386), Windows XP) |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
| Tags: | partitioning update, regression | ||
[5 Jan 2011 11:02]
Valeriy Kravchuk
Verified just as described on Windows XP:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.54-community MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE IF NOT EXISTS `GOODS_ALL_MS` (
-> `NAVI` char(2) NOT NULL,
-> `CATEGORY_ID` char(2) NOT NULL,
-> `GOODS_SEQ` int(10) unsigned NOT NULL,
-> `GOODS_TITLE` varchar(255) DEFAULT NULL,
-> `GOODS_CATEGORY_DESC` varchar(1000) DEFAULT NULL,
-> PRIMARY KEY (`NAVI`,`CATEGORY_ID`,`GOODS_SEQ`),
-> KEY `goods_all_navi_idx` (`NAVI`),
-> KEY `goods_all_cat_idx` (`NAVI`,`CATEGORY_ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY KEY (NAVI)
-> PARTITIONS 20 */;
Query OK, 0 rows affected (1.16 sec)
mysql> INSERT INTO `GOODS_ALL_MS` (`NAVI`, `CATEGORY_ID`, `GOODS_SEQ`, `GOODS_TI
TLE`,
-> `GOODS_CATEGORY_DESC`) VALUES
-> ('07', '03', 343, '1', '07_03_343'),
-> ('01', '04', 343, '2', '01_04_343'),
-> ('01', '06', 343, '3', '01_06_343'),
-> ('01', '07', 343, '4', '01_07_343'),
-> ('01', '08', 343, '5', '01_08_343'),
-> ('01', '09', 343, '6', '01_09_343'),
-> ('03', '03', 343, '7', '03_03_343'),
-> ('03', '06', 343, '8', '03_06_343'),
-> ('03', '07', 343, '9', '03_07_343'),
-> ('04', '03', 343, '10', '04_03_343'),
-> ('04', '06', 343, '11', '04_06_343'),
-> ('05', '03', 343, '12', '05_03_343'),
-> ('11', '03', 343, '13', '11_03_343'),
-> ('11', '04', 343, '14', '11_04_343')
-> ;
Query OK, 14 rows affected (0.09 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from test.GOODS_ALL_MS a where a.NAVI = '03' AND a.CATEGORY_ID =
'06' AND
-> a.GOODS_SEQ = 343 ;
+------+-------------+-----------+-------------+---------------------+
| NAVI | CATEGORY_ID | GOODS_SEQ | GOODS_TITLE | GOODS_CATEGORY_DESC |
+------+-------------+-----------+-------------+---------------------+
| 03 | 06 | 343 | 8 | 03_06_343 |
+------+-------------+-----------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from test.GOODS_ALL_MS a where a.NAVI = '03'
AND
-> a.CATEGORY_ID = '06' AND a.GOODS_SEQ = 343 ;
+----+-------------+-------+------------+-------+-------------------------------
---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+-------------------------------
---------------+---------+---------+-------------------+------+-------+
| 1 | SIMPLE | a | p16 | const | PRIMARY,goods_all_navi_idx,goo
ds_all_cat_idx | PRIMARY | 16 | const,const,const | 1 | |
+----+-------------+-------+------------+-------+-------------------------------
---------------+---------+---------+-------------------+------+-------+
1 row in set (0.03 sec)
mysql> update test.GOODS_ALL_MS a
-> , (
-> select '03' as NAVI, '06' as CATEGORY_ID, '343' as GOODS_SEQ, 'last' as
-> GOODS_CATEGORY_TITLE
-> ) b
-> set a.GOODS_CATEGORY_DESC = b.GOODS_CATEGORY_TITLE
-> where a.NAVI = '03'
-> AND a.CATEGORY_ID = '06'
-> AND a.GOODS_SEQ = 343;
ERROR 1032 (HY000): Can't find record in 'goods_all_ms'
Workaround is not to use multiple table update syntax:
mysql> update test.GOODS_ALL_MS a
-> set a.GOODS_CATEGORY_DESC = 'last'
-> where a.NAVI = '03'
-> AND a.CATEGORY_ID = '06'
-> AND a.GOODS_SEQ = 343;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[22 Jan 2011 23:24]
Mattias Jonsson
Regression from bug#52455.
[24 Jan 2011 12:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/129439 3565 Mattias Jonsson 2011-01-24 Bug#59297: Can't find record in 'tablename' on update inner join Regression introduced in bug#52455. Problem was that the fixed function did not set the last used partition variable, resulting in wrong partition used when storing the position of the newly retrieved row. Fixed by setting the last used partition in ha_partition::index_read_idx_map.
[10 Mar 2011 9:37]
Jon Stephens
Documented in the 5.1.57, 5.5.11, and 5.6.3 changelogs as follows:
A problem with a previous fix for poor performance of
INSERT ON DUPLICATE KEY UPDATE statements on tables having many
partitions caused the handler function for reading a row from a
specific index to fail to store the ID of the last used partition.
This caused some statements to fail with -Can't find
record- errors.
See also BUG#52455.
Closed.
Description: When executing a update table that is partitioned, the result is failed with error "can't find record" in 5.1.54 version. while the result is ok in 5.1.40 version. How to repeat: # mysql 5.1.40 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 5.1.40-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; mysql> drop table GOODS_ALL_MS; Query OK, 0 rows affected (1.04 sec) mysql> CREATE TABLE IF NOT EXISTS `GOODS_ALL_MS` ( -> `NAVI` char(2) NOT NULL, -> `CATEGORY_ID` char(2) NOT NULL, -> `GOODS_SEQ` int(10) unsigned NOT NULL, -> `GOODS_TITLE` varchar(255) DEFAULT NULL, -> `GOODS_CATEGORY_DESC` varchar(1000) DEFAULT NULL, -> PRIMARY KEY (`NAVI`,`CATEGORY_ID`,`GOODS_SEQ`), -> KEY `goods_all_navi_idx` (`NAVI`), -> KEY `goods_all_cat_idx` (`NAVI`,`CATEGORY_ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> /*!50100 PARTITION BY KEY (NAVI) -> PARTITIONS 20 */; Query OK, 0 rows affected (0.45 sec) mysql> INSERT INTO `GOODS_ALL_MS` (`NAVI`, `CATEGORY_ID`, `GOODS_SEQ`, `GOODS_TITLE`, `GOODS_CATEGORY_DESC`) VALUES -> ('07', '03', 343, '1', '07_03_343'), -> ('01', '04', 343, '2', '01_04_343'), -> ('01', '06', 343, '3', '01_06_343'), -> ('01', '07', 343, '4', '01_07_343'), -> ('01', '08', 343, '5', '01_08_343'), -> ('01', '09', 343, '6', '01_09_343'), -> ('03', '03', 343, '7', '03_03_343'), -> ('03', '06', 343, '8', '03_06_343'), -> ('03', '07', 343, '9', '03_07_343'), -> ('04', '03', 343, '10', '04_03_343'), -> ('04', '06', 343, '11', '04_06_343'), -> ('05', '03', 343, '12', '05_03_343'), -> ('11', '03', 343, '13', '11_03_343'), -> ('11', '04', 343, '14', '11_04_343') -> ; Query OK, 14 rows affected (0.05 sec) Records: 14 Duplicates: 0 Warnings: 0 mysql> select * from test.GOODS_ALL_MS a where a.NAVI = '03' AND a.CATEGORY_ID = '06' AND a.GOODS_SEQ = 343 ; +------+-------------+-----------+-------------+---------------------+ | NAVI | CATEGORY_ID | GOODS_SEQ | GOODS_TITLE | GOODS_CATEGORY_DESC | +------+-------------+-----------+-------------+---------------------+ | 03 | 06 | 343 | 8 | 03_06_343 | +------+-------------+-----------+-------------+---------------------+ 1 row in set (0.00 sec) mysql> explain partitions select * from test.GOODS_ALL_MS a where a.NAVI = '03' AND a.CATEGORY_ID = '06' AND a.GOODS_SEQ = 343 ; +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ | 1 | SIMPLE | a | p16 | const | PRIMARY,goods_all_navi_idx,goods_all_cat_idx | PRIMARY | 16 | const,const,const | 1 | | +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ 1 row in set (0.00 sec) mysql> update test.GOODS_ALL_MS a -> , ( -> select '03' as NAVI, '06' as CATEGORY_ID, '343' as GOODS_SEQ, 'last' as GOODS_CATEGORY_TITLE -> ) b -> set a.GOODS_CATEGORY_DESC = b.GOODS_CATEGORY_TITLE -> where a.NAVI = '03' -> AND a.CATEGORY_ID = '06' -> AND a.GOODS_SEQ = 343 -> ; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test.GOODS_ALL_MS a where a.NAVI = '03' AND a.CATEGORY_ID = '06' AND a.GOODS_SEQ = 343 ; +------+-------------+-----------+-------------+---------------------+ | NAVI | CATEGORY_ID | GOODS_SEQ | GOODS_TITLE | GOODS_CATEGORY_DESC | +------+-------------+-----------+-------------+---------------------+ | 03 | 06 | 343 | 8 | last | +------+-------------+-----------+-------------+---------------------+ 1 row in set (0.00 sec) mysql> explain partitions select * from test.GOODS_ALL_MS a where a.NAVI = '03' AND a.CATEGORY_ID = '06' AND a.GOODS_SEQ = 343 ; +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ | 1 | SIMPLE | a | p16 | const | PRIMARY,goods_all_navi_idx,goods_all_cat_idx | PRIMARY | 16 | const,const,const | 1 | | +----+-------------+-------+------------+-------+----------------------------------------------+---------+---------+-------------------+------+-------+ 1 row in set (0.00 sec) but, I have an error message in 5.1.54 like this below. ERROR 1032 (HY000): Can't find record in 'GOODS_ALL_MS'