[MDEV-3888] extended_keys=on does not work with "on duplicate key update" - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.28
Fix Version/s: 10.0.1, 5.5.29
Component/s: None
Labels:
None
Environment:
Ubuntu linux 10.04/12.04, amd64 32/96GB Ram

Description

CREATE TABLE `t1` (
  `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c2` bigint(20) unsigned NOT NULL,
  `c3` bigint(20) unsigned NOT NULL,
  `c4` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `uq` (`c2`,`c3`),
  KEY `c3` (`c3`),
  KEY `c4` (`c4`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

mysql> set session optimizer_switch='extended_keys=off';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
Query OK, 0 rows affected (0.00 sec)

mysql> set session optimizer_switch='extended_keys=on';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
ERROR 1032 (HY000): Can't find record in 't1'
mysql> truncate table t1;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
ERROR 1032 (HY000): Can't find record in 't1'

If the duplicate key exists in the table, extended_keys prevents updating of the data. If there is no duplicate, extended_keys allows insertion but subsequent updating via the "on duplicate key" update mechanism is prevented.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Dan Vande More added a comment - 2012-11-27 17:48

mysql> show variables like 'optimizer_switch';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Variable_name

Value

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

optimizer_switch

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
--------------------

version()

--------------------

5.5.28-MariaDB-log

--------------------
1 row in set (0.00 sec)

Show

Dan Vande More added a comment - 2012-11-27 17:48 mysql> show variables like 'optimizer_switch'; ----------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Variable_name Value ----------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on ----------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); -------------------- version() -------------------- 5.5.28-MariaDB-log -------------------- 1 row in set (0.00 sec)

Hide

Permalink

Sergei Golubchik added a comment - 2012-12-27 20:10

http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/igor@askmonty.org-20121205000607-tmedeaz0uz5wh3b4

Show

Sergei Golubchik added a comment - 2012-12-27 20:10 http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/igor@askmonty.org-20121205000607-tmedeaz0uz5wh3b4

extended_keys=on does not work with "on duplicate key update"

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile