| Bug #72476 | Modifying in Workbench causes update trigger to modify all records instead of 1 | ||
|---|---|---|---|
| Submitted: | 28 Apr 2014 23:42 | Modified: | 15 Sep 2014 17:28 |
| Reporter: | Gordon Ramshackle | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Workbench | Severity: | S1 (Critical) |
| Version: | 6.1 | OS: | Windows (8.1 64-bit) |
| Assigned to: | CPU Architecture: | Any | |
[28 Apr 2014 23:42]
Gordon Ramshackle
[7 May 2014 14:31]
Chuck Bell
Filed under wrong category. Reset to WB proper.
[24 May 2014 16:41]
Gordon Ramshackle
Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Red White Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Blue Blue Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red Blue Car2 Red Blue Car3 Blue Blue Car4 Red Blue Car5 Red Blue
[24 May 2014 16:41]
Gordon Ramshackle
Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Red White Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Blue Blue Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red Blue Car2 Red Blue Car3 Blue Blue Car4 Red Blue Car5 Red Blue
[24 May 2014 16:41]
Gordon Ramshackle
Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Red White Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Blue Blue Car4 Red White Car5 Red White Vehicle Color SeatColor Car1 Red Blue Car2 Red Blue Car3 Blue Blue Car4 Red Blue Car5 Red Blue
[24 May 2014 17:01]
Gordon Ramshackle
Here's a clearer, visual explanation of the bug: We start with the following sample data. There is update trigger that checks if the color was changed. If it was, "SeatColor" is updated to the same color. Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Red White Car4 Red White Car5 Red White If I change the color of "Car3" to "Blue", the "SeatColor" field will change to blue. This is the desired result. Vehicle Color SeatColor Car1 Red White Car2 Red White Car3 Blue Blue Car4 Red White Car5 Red White This is what happens if the color field is changed in the Workbench result grid, if the results were displayed using "select vehicle, color, seatcolor from Cars where color='Red'". The "SeatColor" for all records in the result grid are updated with the action intended for "Car3" Vehicle Color SeatColor Car1 Red Blue Car2 Red Blue Car3 Blue Blue Car4 Red Blue Car5 Red Blue This is clearly a "Workbench" bug because it does not occur through other means, such as running an UPDATE command such as: "UPDATE Cars SET Color='Blue' WHERE Vehicle='Car3'" // This updates data properly. The bug does not occur if all cars are listed rather just "Red" cars. In that case, "Car3" remains in the result grid, thus, no bug. The bug occurs when the results are filtered, for this example, show only "Red" cars. When the color of "Car3" is changed to "Blue", it is removed from the result grid because the grid is currently displaying only "Red" cars. When the "update trigger" changes the "SeatColor" of "Car3" to "Blue", rather just "Car3" being affected, all cars in the result grid are affected and all their "SeatColor" fields are changed to "Blue".
[24 May 2014 17:12]
Gordon Ramshackle
To clear up the above example, the last set of data is a tabular representation of the results, not the actual results in the Workbench grid. In workbench, the record for "Car3" is removed because Workbench is only displaying "Red" cars.
Vehicle Color SeatColor
Car1 Red Blue
Car2 Red Blue
Car3 Blue Blue // This row is removed from the result grid because
// Workbench is only showing "Red" cars.
Car4 Red Blue
Car5 Red Blue
[15 Aug 2014 14:14]
MySQL Verification Team
Please provide a dump file with create statement and data inserts. Thanks.
[15 Aug 2014 17:22]
Gordon Ramshackle
The bug appears to have been fixed. My real-life case was a 2-table scenario. The sample I provided was a 1-table scenario, which was simpler to recreate and simple to verify and which I did verify. I currently use Workbench 6.1.6.11834 build 1642 Community. I don't have a record of the 6.1 build with the bug. The 1-table scenario now does not complete the sequence of events the resulted in data corruption. The sequence halts with an error saying the table is in use by a trigger and cannot be updated. The 2-table scenario now only updates the proper rows rather than all the rows that was displayed in the Workbench output grid. I would have like to see an indication that someone had worked on my bug submission., an indication that the bug existed and was fixed due to my bug submission. At least make me feel that the time I put in to help was worthwhile. I would hate to see my submission put under “Can't repeat”, “Not a bug”, “Won't fix” etc. Anyone can use an older version of 6.1 to recreate the bug but the world will never know how and why the “obscure” bug was fixed. Also, the 1-table fix looks more like a quick-fix than a proper solution but it at least halts the possible corruption of data.
[15 Aug 2014 17:28]
MySQL Verification Team
Thank you for the feedback. If the issue isn't more repeatable on 6.1.7 then this bug report will be closed. Thanks.
[16 Sep 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
