| Bug #30146 | on duplicate key update does insert and update at the same time | ||
|---|---|---|---|
| Submitted: | 31 Jul 2007 12:59 | Modified: | 31 Jul 2007 13:10 |
| Reporter: | Kristian Koehntopp | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.42-enterprise-gpl | OS: | Any |
| Assigned to: | Dean Ellis | CPU Architecture: | Any |
[31 Jul 2007 13:08]
Kristian Koehntopp
root on mysql.sock [kris]> select * from pkexample; +----+------+-------------+ | id | uniq | val | +----+------+-------------+ | 1 | 1 | neu und neu | | 2 | 2 | zwei | +----+------+-------------+ 2 rows in set (0.00 sec) root on mysql.sock [kris]> insert into pkexample values (3, 1, "neu") on duplicate key update val = concat(values(val), " und neu"); Query OK, 0 rows affected (0.00 sec) root on mysql.sock [kris]> select * from pkexample; +----+------+-------------+ | id | uniq | val | +----+------+-------------+ | 1 | 1 | neu und neu | | 2 | 2 | zwei | +----+------+-------------+ 2 rows in set (0.00 sec)
[31 Jul 2007 13:10]
Kristian Koehntopp
15:09 Isotopp> dean: ah ok, i understand that one as well.
15:09 Isotopp> dean: the values(val) produces "neu", because that is
the value that had been inserted had the insert
worked.
15:09 Isotopp> dean: the concat then produces the value that is
already there
15:09 Isotopp> dean and 0 rows affected is only logical.
15:10 Isotopp> dean: ok, so it "works".
Description: See how to repeat: An "INSERT ... ON DUPLICATE KEY UPDATE" behaves strangely. How to repeat: root on mysql.sock [kris]> show create table pkexample\G *************************** 1. row *************************** Table: pkexample Create Table: CREATE TABLE `pkexample` ( `id` int(10) unsigned NOT NULL auto_increment, `uniq` int(10) unsigned NOT NULL, `val` varchar(80) collate latin1_german1_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq` (`uniq`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci 1 row in set (0.11 sec) Combination insert and update should not happen: Before: root on mysql.sock [kris]> select * from pkexample; +----+------+-----------------------+ | id | uniq | val | +----+------+-----------------------+ | 1 | 1 | old | | 2 | 2 | old | | 3 | 3 | another insert extend | +----+------+-----------------------+ 3 rows in set (0.00 sec) Command: root on mysql.sock [kris]> insert into pkexample values (4, 1, "another insert" ) on duplicate key update val = concat(values(val), " extend"); Query OK, 2 rows affected (0.00 sec) Result: root on mysql.sock [kris]> select * from pkexample; +----+------+-----------------------+ | id | uniq | val | +----+------+-----------------------+ | 1 | 1 | another insert extend | | 2 | 2 | old | | 3 | 3 | another insert extend | +----+------+-----------------------+ 3 rows in set (0.00 sec)