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

INSERT .. ON DUPLICATE KEY UPDATE does not update

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.1
    • Fix Version/s: N/A
    • Component/s: Server
    • Labels:
      None

      Description

      Test case:

      CREATE TABLE `v` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
      ) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
       
      INSERT v values (1);
       
      CREATE TABLE `vp` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `v_id` int(10) unsigned NOT NULL,
        `p_id` int(10) unsigned NOT NULL,
        `ppp` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
        KEY `FK_vp_v` (`v_id`)
      ) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
       
      INSERT vp VALUES (12, 1, 100, 'text12');
       
      INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);
      SELECT * FROM vp;
       
      DROP TABLE vp, v;
      

      Result from 10.1.29:

      REATE TABLE `v` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
      ) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
      INSERT v values (1);
      CREATE TABLE `vp` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `v_id` int(10) unsigned NOT NULL,
      `p_id` int(10) unsigned NOT NULL,
      `ppp` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
      KEY `FK_vp_v` (`v_id`)
      ) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
      INSERT vp VALUES (12, 1, 100, 'text12');
      INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);
      Warnings:
      Warning	1364	Field 'v_id' doesn't have a default value
      Warning	1364	Field 'p_id' doesn't have a default value
      SELECT * FROM vp;
      id	v_id	p_id	ppp
      12	1	100	test12-2
      DROP TABLE vp, v;
      

      See that column ppp is not updated even when insert with id 12 causes duplicate key error.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              jplindst Jan Lindström
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration