My favorites | Sign in
Project Home Issues Source
New issue   Search
  Advanced search   Search tips   Subscriptions
Issue 2974: Broken update when editing query results with only part of multicolumn PK visible
1 person starred this issue and may be notified of changes. Back to list
Status:  Fixed
Owner:  ----
Closed:  Feb 2013

Sign in to add a comment
Reported by, Sep 23, 2012
What exact steps will reproduce the problem?
1. Create/have a table with multicolumn PK
Create table testtable (acct_id int,dt date,somevalue int, primary key (acct_id,dt));
insert into testtable (1,'2012-01-01',1);
insert into testtable (1,'2012-02-01',1);
insert into testtable (2,'2012-01-01',1);
insert into testtable (2,'2012-02-01',2);
2. Use query window to select some data _but without at least one PK column_
select acct_id, somevalue from testtable where dt='2012-01-01'
3. Then edit query results in results window. For example, result of setting "somevalue" to 6 for acct_id=1 is this query:
UPDATE `testtable` SET `somevalue`=6 WHERE  `acct_id`=1 LIMIT 1;

Note that there is no condition for second part of PK (dt='2012-01-01') 

What was the expected output?
An error should be shown, indicating impossibility to make an update due to lack of information about PK fields values.

What happened instead?
Update of random record using only that PK fields that were present in resulting dataset. In example above some random record with acct_id=1 will be updated (ignoring condition on date).

Version used?
 HeidiSQL revision:
 MySQL Server version: 5.0.67
 Operating system: Microsoft Windows XP Professional
Oct 31, 2012
Ahem... up.
Oct 31, 2012
Project Member #2
Will check that asap.
Status: Accepted
Labels: Severity-Contaminates
Feb 24, 2013
It gets even "better" in (from at least 4304)
There is query generated for modification by that revisions:

UPDATE `testtable` SET `somevalue`=6 WHERE  `acct_id`=1;

And yes, it doesn't have the "LIMIT 1" clause anymore! So instead of updating ONE random record with acct_id=1, it now updates ALL records with acct_id=1.
If it is not to be fixed soon, please at least revert to not-so-desctructive "limit 1" behavior.
Feb 25, 2013
Project Member #4
With regard to the suddenly missing LIMIT clause, here you will find the discussion about that: (LIMIT causes "Statement is not safe to log in statement format"). So it's only applied on tables without primary keys.

The "updates with missing column from primary key" thing is going to be fixed by me now.
Feb 25, 2013
Project Member #5
This issue was closed by revision r4346.
Status: Fixed
Feb 25, 2013
Project Member #6

Bug was present since quite a long time. Thanks for reporting it!
Sign in to add a comment

Powered by Google Project Hosting