Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3888

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.28
    • 10.0.1, 5.5.29
    • None
    • None
    • 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.

      Attachments

        Activity

          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)

          fimbulvetr Dan Vande More added a comment - 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)
          serg Sergei Golubchik added a comment - http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/igor@askmonty.org-20121205000607-tmedeaz0uz5wh3b4

          People

            igor Igor Babaev (Inactive)
            fimbulvetr Dan Vande More
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.