MySQL Bugs: #14841: Updating foreign key of single row changes all rows

Bug #14841	Updating foreign key of single row changes all rows
Submitted:	10 Nov 2005 17:00	Modified:	23 Oct 2006 14:09
Reporter:	Chris Knowles	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.14,1.1.20	OS:	Windows (Windows 2000)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	Result Set

Description:
I was executing a query that returned a single row from a table. I edited a foreign key and by mistake entered a "#" (without quotes) at the end and pressed the return key. I then corrected the key (removed the "#") and clicked "Apply Changes". All rows were updated with that id not just that row.

How to repeat:
I'll use an example of a table "table" with foreign key "fkey"

- SELECT * FROM table WHERE fkey = 120
- Clicked Edit
- Changed fkey to "123#" (without quotes)
- Pressed return key
- Noticed mistake and changed fkey to 123
- Clicked Apply Changes
- All fkey's were changed to 123

I realised the browser does any changes in increments, so noticing the mistake does not matter.

This may apply to other special characters as well, as I cannot retry (I have restored the db table in question).

Suggested fix:
Check for special characters such as "#" and possibly others if they do the same thing.

Thank you for a problem report. Your version is a bit old, so, please, can you try to repeat the same steps with 1.1.17 and inform about the results?

I would, but every time I install the Windows (x86) for 1.1.17, the Query Browser still reports itself as 1.1.14 in "About". Either this is a simple screen bug, or I'm not being upgraded properly. This bug is still there after I've run the 1.1.17 installer (which may or may not have worked).

Could you deinstall QB once more. The delete the directory c:\Program Files\MySQL\Query Browser 1.1

Then go Start->Run and enter "regedit". Go to HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Query Browser 1.1 and delete this key.

Then re-do the installation. This will give you a clean install.

Thanks.

Still does the same after a clean install.

Sorry, but I was not able to repeat the problem according to your description with QB 1.1.17 and MySQL 5.0.15 on XP.

So, please, inform about the MySQL server version used and describe all the steps (all SQL statements one need to perform) to repeat the problem.

I can confirm that this problem occurs in 1.1.17 on my computer. I followed the procedure and got the same (scary) result, it updated the complete set.

I'm using the Windows SQL Query browser, connecting to a Linux MySQL 4.1.11 (Debian).

I haven't added a foreign key constraint to the database between the two tables though (kept giving errors), that might be helpful information.

Please allow me to comment on my own comment... I added a foreign key, and the problem persisted. However, it is even easier to reproduce than I thought:

1. select * from foo where id < 10 and fkey = 1     [where clause is optional]
2. edit
3. change the value of fkey in a random record to "2#"
4. apply

And that results in ALL records (even those not in selection with id's > 10, even those that don't have "fkey = 1") to have their value updated to '2'.

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".

Since this bug has been abandoned for a while, I decided to look a bit further (now with 1.1.20), and found the actual problem: the update statement created by MySQL Query Browser looks like this:

UPDATE `database`.`table` SET `column` = 2# WHERE `ID` = 3

and pasting this into the browser itself shows that the '#' is of course interpreted as the start of a COMMENT! so basically mistyping an accidental '#' in a nummeric field causes the WHERE part to be commented out...

The workaround of course is not to type a # accidentally, but my suggestion would be to ensure that update statements created by editing table data do some pre-checking on the data before it is passed to MySQL...

confirming that this bug is still present on version 1.1.20.  A small typo can lead to a massive loss of data, due to the WHERE clause of an UPDATE being commented out.  The query browser should impose some limits on what characters can be entered into a numeric field.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html