MySQL Bugs: #76762: Deleting data through stored procedure (Error Code: 1175)

Bug #76762	Deleting data through stored procedure (Error Code: 1175)
Submitted:	20 Apr 2015 20:38	Modified:	21 May 2015 6:52
Reporter:	Piotr Szawdyński	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.3.2	OS:	Windows (Microsoft Windows 7 Home Premium Service Pack 1)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Calling stored procedure shouldn't return mentioned error because of missing WHERE condition in DELETE statement because it exists in query inside stored procedure.

How to repeat:
-- Query:

CALL Usun_GenerowaneNiezatwierdzoneDane(1)

-- Stored procedure:

CREATE DEFINER=`ddt10`@`%` PROCEDURE `Usun_GenerowaneNiezatwierdzoneDane`( IN iDxGraId INT )
BEGIN
  DELETE ASxAvg FROM ASxAvg INNER JOIN DxLosowanie WHERE ASxAvg.DxLosowanieId=DxLosowanie.DxLosowanieId AND DxLosowanie.DxGraId=iDxGraId AND DxLosowanie.IsConfirmed=0;
END

-- Result:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

-- Expected result:
Should work.

Changed severity.

Other way to reproduce issue without stored procedure:

DELETE tab1 FROM tab1 INNER JOIN tab2 WHERE tab1.t1id=tab2.t1id AND tab2.something=1 AND tab2.somethingElse=0;

Hello Piotr Szawdyński,

Thank you for the report.
This is intended behavior i.e  "Safe Updates" is enabled and it forbid UPDATE and DELETE queries to execute that lack a corresponding key in a WHERE clause, or lack a LIMIT clause. Setting this option requires a MySQL server reconnection.

This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably accidentally change or delete a large number of rows. Please reference manual - https://dev.mysql.com/doc/workbench/en/wb-preferences-sql-editor.html

Workaround, as suggested you can turn off this option or use proper keys in the where clause.

Thanks,
Umesh

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