While using MySQL Workbench, we tried to execute an UPDATE command that did not use the Key of the table in the WHERE clause. In our case, it did not matter and it was expected to perform that update without that restriction.
When we tried to execute the code though, we got the following error:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
and the following solution from Workbench:
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
We did not want to close the session for many reasons (we had data in other tabs, we would lose the undo history, we would have to revert the change later on, etc.).
To solve this issue without reconnecting nor changing the configuration of the tool, we appended the following code above the update statement and executed both lines together (we selected the two commands and pressed CTRL+Shift+Enter):
SET SQL_SAFE_UPDATES = 0;
This line of code, instructed the tool to ignore Error 1175 and allowed us to complete our task.
When trying to issue the command mysqldump -h "mysql.example.com" -u "username" -p"password" db table_A table_B to get the MySQL dump for two tables (table_A and table_B), you will get the error mysqldump: Got error: 1044: Access denied for user 'username'@'ip' to database 'db' when doing LOCK TABLES when your account…
The following code will remove the prefix http://wow.example.com from all rows that match the where clause: Update `my_table` set `my_column` = TRIM( LEADING 'http://wow.example.com' FROM `my_column`) WHERE (`my_column` LIKE '%http://wow.example.com/%'); The next block will remove the suffix index.php from all entries that match the where clause: Update `my_table` set `my_column`…
2022-06-07 7:17:02 3051 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'). 2022-06-07 7:17:02 3051 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255). While checking the logs of a…
You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both).
For example:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;
The LIMIT clause works just fine and avoids the risk of using DROP unintentinally when disabling SQL_SAFE_UPDATES in the query source makes possible.
go to edit menu select preferences then click on preferences
after select SQL Editor
then uncheck the safe update check box at the last and click ok button and close MySQL server workbench 8.0 CE open MySQL server workbench 8.0 CE and then run the update command
SET SQL_SAFE_UPDATES=0;
Is not neccessary and can be unintentionally destructive. I learned the following while researching this concern…
4.5.1.6.4 Using the –safe-updates Option
Source: https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html
You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both).
For example:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;
The LIMIT clause works just fine and avoids the risk of using DROP unintentinally when disabling SQL_SAFE_UPDATES in the query source makes possible.
go to edit menu select preferences then click on preferences
after select SQL Editor
then uncheck the safe update check box at the last and click ok button and close MySQL server workbench 8.0 CE open MySQL server workbench 8.0 CE and then run the update command
Otherwise use MySQL 8.0 Command Line Client Mode.