#3254 (ok 4.0) Editing correct row when unique key missing

3.3.7
fixed
nobody
1
2013-06-11
2011-03-31
Anonymous
No

phpMyAdmin 3.3.7
MySQL 5.1.41-3ubuntu12.9-log

See attached sql for table structure and data

Steps to recreate

1. Execute
"SELECT first_name, last_name FROM users;"

2. Edit any record changing any of the shown fields

Click go ( Save and return to the last page)

Problem

The sql shown to execute is
"UPDATE `test`.`users` SET `first_name` = 'john' WHERE `users`.`first_name` = 'jane' AND `users`.`last_name` = 'doe' LIMIT 1 ;"

I find this a little misleading as it's not what the would have been run.

Discussion

  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-03-31

    CREATE SQL

     
  • Marc Delisle

    Marc Delisle - 2011-03-31

    Which statement did you expect?

    P.S. this is not the latest phpMyAdmin stable version.

     
  • Marc Delisle

    Marc Delisle - 2011-03-31
    • status: open --> pending
     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-03-31
    • status: pending --> open
     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-03-31

    It should show the actual sql that was run no?

    Should be something like
    "UPDATE `test`.`users` SET `first_name` = 'john' WHERE `users`.`id` = 'NUM' `users`.`first_name` = 'jane' AND `users`.`last_name` = 'doe' LIMIT 1 ;"

    As the other would just update the first record returned which might not be the one you want.
    It could cause problems to mysql beginners.

    P.S. haven't had a chance to upgrade yet

     
  • Marc Delisle

    Marc Delisle - 2011-03-31
    • status: open --> pending-works-for-me
     
  • Marc Delisle

    Marc Delisle - 2011-03-31

    It shows the actual SQL that was run.

    In this case, the id was not part of the result set so it could not be used to generate the UPDATE query.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-04-01

    Yeah it does actually run the returned sql statement,
    Depending on the order of the edit it can update the wrong record because there is no unique identifier for the row's
    ( even tho the id is their when you edit the line ).

     
  • Marc Delisle

    Marc Delisle - 2011-04-01

    In your bug description you wrote
    SELECT first_name, last_name FROM users;

    so the id is not displayed in the results. This is not a bug of incorrect UPDATE statement displayed.

    About what happens when you Edit such a row, I think there might already be a feature request on this; you may want to open one.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-04-01

    Sorry lem9,

    I think were misunderstanding each other. The problem is when you select any data with out a unique identifier and edit a records column where there are two or more with the same value. even tho on the edit screen the unique identifier for the row is shown it's not used. so the update is not applied to the correct record only the first record that matches. Which is a bug as it's not what the user would expect.

     
  • Marc Delisle

    Marc Delisle - 2011-04-01

    I changed the bug summary.

     
  • Marc Delisle

    Marc Delisle - 2011-04-01
    • summary: Incorrect update statement shown after Correct execution --> Editing correct row when unique key missing from results
    • status: pending-works-for-me --> open
     
  • Rouslan Placella

    When using the inline edit, it gets worse. The LIMIT clause is omitted and every row matching the criteria gets modified.
    With the provided sample data, if you use inline edit to change 'jane' to 'john', 2 records are updated.

    Rouslan

     
  • onebighack

    onebighack - 2011-04-03

    shouldn't the "where clause" be unique for a table, and not depend on the sql query run by the user .

     
  • Marc Delisle

    Marc Delisle - 2011-04-03

    onebighack: some tables have a unique key, some don't. A logic change would be required here, to always fetch all columns internally and construct the best possible WHERE clause.

    It gets more tricky when you realize that a query may involve more than one table.
    I'm talking both about traditional Edit and inline edit.

     
  • onebighack

    onebighack - 2011-04-06

    leaving aside joins; why don't we check index of the selected table, and if we find a unique key then we can use that for the where clause irrespective of the whether that column is displayed in the query or not, and if we don't find one use all columns of that table.

     
  • Marc Delisle

    Marc Delisle - 2011-04-19

    onebighack: this is what I meant by "always fetch all columns internally".

     
  • Marc Delisle

    Marc Delisle - 2012-10-08
    • summary: Editing correct row when unique key missing from results --> (ok 4.0) Editing correct row when unique key missing
    • priority: 5 --> 1
    • status: open --> open-fixed
     
  • Marc Delisle

    Marc Delisle - 2012-10-08

    There is now a fix for version 4.0: if the column(s) for a unique key are not present in the result set, there are no Edit and Delete links

     
  • Marc Delisle

    Marc Delisle - 2013-05-03
    • Status: open-fixed --> closed-fixed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-fixed --> fixed
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks