| Bug #46997 | ON DUPLICATE UPDATE does not react on change in one field | ||
|---|---|---|---|
| Submitted: | 29 Aug 2009 22:19 | Modified: | 4 Sep 2009 9:14 |
| Reporter: | Miran Cvenkel | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.1.31-community, 4.1, 5.0, 5.1, next bzr | OS: | Any (MS Windows, Linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | duplicate, EFFECT, no, on, UPDATE | ||
[29 Aug 2009 22:25]
Miran Cvenkel
even went and order columns in update sql in order as they appear in table, no change , c_i does not want to update.
[29 Aug 2009 22:34]
Miran Cvenkel
hmm, went to mysql query analyzer, changed 0 in c_i column to something randome else. Now it works. Tested it in a_i column, same thing . 0 data is causing the problem. ON DUPLICATE UPDATE does not see 0 as something that needs to be changed.
[29 Aug 2009 22:52]
Miran Cvenkel
mybe this is not a bug at all if '' = 0
[30 Aug 2009 6:44]
Valeriy Kravchuk
Sorry, but the only key you have on this table is: PRIMARY KEY (`id_galery`) So, duplicates in any other column are not identifiable. Please, check.
[3 Sep 2009 21:25]
Miran Cvenkel
the bottom line here is that MYSQL consider this (on field type of INT): if(field_of_type_int='' ... if(field_of_type_int=0 ... as same thing, so update wouldn't take place if there was 0 in database at condition if(field_of_type_int='' looks strange.
[4 Sep 2009 5:21]
Sveta Smirnova
Thank you for the feedback. Verified as described: INSERT INTO `galery_1_plant` (`id_galery`,`a_i`,`b_c`,`c_i`,`d_c`) VALUES (0,10,'45',0,'34'); select * from galery_1_plant; id_galery a_i b_c c_i d_c 0000000000 10 45 0 34 INSERT ... ON DUPLICATE KEY UPDATE ... select * from galery_1_plant; id_galery a_i b_c c_i d_c 0000000000 1 3 0 4
[4 Sep 2009 7:32]
Sergei Golubchik
of course, it does. see http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html
[4 Sep 2009 7:42]
Sveta Smirnova
Sergei, correct. But look into table definition: ... `a_i` int(10) unsigned NOT NULL, ... `c_i` int(10) unsigned NOT NULL, ... a_i and c_i have same type. But why a_i is updated while c_i is not?
[4 Sep 2009 9:14]
Sergei Golubchik
let me guess... because he compares with zero and c_i in fact *is* zero ? :)
Description: /*sample table,data*/ /*had myisam, long col names, changed all that, no change*/ /*each time update on second col i.e. 'c_i' does not work*/ CREATE TABLE `galery_1_plant` ( `id_galery` int(10) unsigned zerofill NOT NULL COMMENT 'ključ na galery_1', `a_i` int(10) unsigned NOT NULL, `b_c` varchar(100) COLLATE utf8_slovenian_ci DEFAULT NULL, `c_i` int(10) unsigned NOT NULL, `d_c` varchar(100) COLLATE utf8_slovenian_ci DEFAULT NULL, PRIMARY KEY (`id_galery`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC; INSERT INTO `galery_1_plant` (`id_galery`,`a_i`,`b_c`,`c_i`,`d_c`) VALUES (0000000000,10,'45',0,'34'); How to repeat: /*if you look after ON DUPLICATE KEY UPDATE*/ /*there are values 1,2,3,4*/ /*change any of 1,2,4 works ok, i.e update happens*/ /*change of 2 has no effect, update does not happen, no error either*/ INSERT INTO galery_1_plant ( id_galery, a_i, c_i, b_c, d_c ) values ( 0, 0, 0, '', '' ) ON DUPLICATE KEY UPDATE a_i = if(a_i='',a_i,1), c_i = if(c_i='',c_i,2), b_c = if(b_c='',b_c,'3'), d_c = if(d_c='',d_c,'4') Suggested fix: have no idea.