Bug #59370 safe_update option completely misunderstood in WB
Submitted: 8 Jan 2011 21:03 Modified: 10 Mar 2011 13:09
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5..2.31 CE OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[8 Jan 2011 21:03] Peter Laursen
Description:
I was trying to reproduce http://bugs.mysql.com/bug.php?id=59366

I had all sorts of inconsistencies, error meessaging referencing non-esiting configuration options. I think I 'boiled it down' like this: WB explains the --safe_updates option incorrectly. 

http://dev.mysql.com/doc/refman/5.1/en/mysql-tips.html#safe-updates
"With --safe-updates, you can delete rows only by specifying the key values that identify them."

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sql_safe_update...
"If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause."

But in WB it simply becomes "Forbid UPDATE and DELETE statements without a WHERE-clause". This is completely wrong.  If there is no key/index on the column(s) referenced in the WHERE-clause UPDATE/DELETE fails if --safe-updates is set server side.

How to repeat:
Try --safe-updates set and not set server side. Execute a statement with a WHERE-clause on a column that does not have an index, like 

CREATE TABLE `fixit` (
  `ID` int(10) DEFAULT NULL,
  `D` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `TABID` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Execute from WB SQL-editor (with the option checked)

UPDATE `fixit` set `D` 0 'xyz' where ÌD` = 1;
and 
UPDATE `fixit` set `D` 0 'xyz'

(note 2*2 cartesian cases).

Suggested fix:
I am not sure if WB actually sets the variable on the server or handles it client-side.  So I won't advice on the fix.  But WB has misunderstood what --safe-updates is about (or server docs are wrong - what I doubt). The key point is not if there is a WHERE/LIMIT clause but whether there is an index or not.
[8 Jan 2011 21:04] Peter Laursen
the --safe-update option as (wrongly) defined in WB

Attachment: safe.jpg (image/jpeg, text), 72.77 KiB.

[8 Jan 2011 21:12] Peter Laursen
Related server documentation request posted here:
http://bugs.mysql.com/bug.php?id=59371
[8 Jan 2011 21:15] Peter Laursen
I could express myself more clear: 

The key point is not if there is a WHERE/LIMIT clause but whether there is an index or not *if WHERE is used*. (LIMIT is clear of course)
[9 Jan 2011 10:35] Valeriy Kravchuk
Indeed, when I checked the same bug report I had found out that WB in safe update mode does NOT allow statement like

UPDATE t SET ... WHERE id = 1

if there is no key defined on id column. So, looks like option description in Preferences should be corrected.
[10 Mar 2011 13:09] Tony Bedford
An entry has been added to the 5.2.34 changelog: 

      The description given in the Workbench Preferences dialog 
      for the --safe-updates option was incorrect.