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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              fimbulvetr Dan Vande More
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: