| Bug #74140 | New Version 6.2.3.12313 generated sometimes wrong UPDATE Queries... | ||
|---|---|---|---|
| Submitted: | 29 Sep 2014 15:42 | Modified: | 5 Nov 2014 2:23 |
| Reporter: | Martin Seysen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
| Version: | 6.2.3.12313 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | generated, GUI, regression, workbench | ||
[29 Sep 2014 16:49]
MySQL Verification Team
Thank you for the bug report. Please provide a dump file with insert data so is possible to run the queries. Thanks.
[30 Sep 2014 8:06]
Martin Seysen
dump file with some data (INSERTs)
Attachment: dump.sql (text/x-sql), 2.50 KiB.
[30 Sep 2014 10:00]
MySQL Verification Team
Thank you for the test case. I see 2 different query set with 6.2.3 and 6.1.7 for the same update. // 6.2.3 UPDATE `test`.`table` SET `dstamp`='1111' WHERE `uid`='0'; // 6.1.7 UPDATE `test`.`table` SET `dstamp`='1111' WHERE `id`='2' and`uid`='0' and`dstamp`='0'; Thanks, Umesh
[5 Nov 2014 2:23]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Workbench 6.2.4 release, and here's the changelog entry: MySQL Workbench generated incorrect UPDATE queries for tables without a Primary Key even where unique keys were present. Thank you for the bug report.
Description: Workbench generated wrong UPDATE queries for a table without PRIMARY KEY... How to repeat: Following table is given: CREATE TABLE `table` ( `id` varchar(100) NOT NULL, `uid` bigint(20) unsigned NOT NULL, `level` mediumint(9) NOT NULL DEFAULT '0', `cstamp` int(11) NOT NULL, `dstamp` int(11) NOT NULL, UNIQUE KEY `unique` (`id`,`uid`,`dstamp`), KEY `uid` (`uid`,`level`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; There is no PRIMARY KEY but an UNIQUE KEY. Data in this table is something like following: | id | uid | level | cstamp | dstamp | =============================== | 1 | 0 | 3 | 1391701710 | 0 | | 1 | 1 | 2 | 1391701710 | 0 | | 1 | 123456 | 1 | 1391701710 | 0 | | 1 | 7463529567 | 0 | 1391701710 | 0 | | 2 | 0 | 3 | 1397577347 | 0 | | 2 | 1 | 2 | 1397577347 | 0 | | 2 | 123456 | 1 | 1397577347 | 0 | | 2 | 3856438345 | 0 | 1397577347 | 0 | | ...| ... | ... | ... | ... | No I make a SELECT with WHERE on a specific id e.g. SELECT * FROM `table` WHERE `id` = 2 I got this result: | id | uid | level | cstamp | dstamp | =============================== | 2 | 0 | 3 | 1397577347 | 0 | | 2 | 1 | 2 | 1397577347 | 0 | | 2 | 123456 | 1 | 1397577347 | 0 | | 2 | 3856438345 | 0 | 1397577347 | 0 | Then I change the dstamp for this 4 rows with the GUI to something not zero and press "Apply". Workbench first shows me the UPDATE Statements as following: UPDATE `db`.`table` SET `dstamp`='1411994456' WHERE `uid`='0'; UPDATE `db`.`table` SET `dstamp`='1411994456' WHERE `uid`='1'; UPDATE `db`.`table` SET `dstamp`='1411994456' WHERE `uid`='123456'; UPDATE `db`.`table` SET `dstamp`='1411994456' WHERE `uid`='3856438345'; And that is wrong! The statement would update all rows with uid = 0 and not only that one with id = 2. Before I updated to this new version of Workbench it works perfectly. I often made changes to this table this way. Suggested fix: There is no PRIMARY KEY to identify a specific row, but there is an UNIQUE KEY which is not used for the UPDATE's generated through Workbench. I think Workbench just has to use the the columns from the UNIQUE KEY or forbid making updates this way, isn't it? I think Workbench should use the same filter from my former SELECT. But it doesn't. It makes it's own new filter for the UPDATE and that one is weird. It uses the first column that contains different value in my special SELECT or it uses the first column found in my UNIQUE KEY, I don't know what's happening exactly. And that's why I thought, that if there is no PRIMARY KEY that Workbench looks for a UNIQUE KEY. Could that be wrong in some case?