The Error Message You May Never See

Written by

Albert Heinle

Technology is neither good nor bad; nor is it neutral. - Kranzberg’s 1st Law of Technology

‍

Errors can be bad, but in this case, it turns out to be a relief.

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Have you ever seen this error message?

If you have seen it, then you should buy your systems administrator, IT infrastructure team, or DevOps team a beer (or whatever celebratory beverage you choose).

This message means that the team has updated your MySQL configuration. They have changed the defaults. Namely, they have set the system variable SQL_SAFE_UPDATES = 1.

What does this error mean?

The error means that MySQL database engine is configured to be in safe mode. Safe mode is designed to protect users (particularly beginners) from destructive or costly behaviors. SQL_SAFE_UPDATES mode is helpful for cases when you might have issued an UPDATE or DELETE statement but forgotten the WHERE clause indicating which rows to modify. Normally, such statements update or delete all rows in the table, this is rarely intended. SQL_SAFE_UPDATES allows users to only modify rows using the WHERE or a LIMIT clause (or both). And when either limitation is missing the MySQL database engine throws the ERROR 1175.

Imagine you are in charge of a banking database and suddenly set everyone’s bank balance to 0, and not just the person who created a transaction.

‍

An example table:


mysql> select * from transactions;

+------+--------+---------------+
| id   | amount | description   |
+------+--------+---------------+
|    1 |  102.1 | Transaction 1 |
|    2 |  107.1 | Transaction 2 |
|    3 |   97.1 | Transaction 3 |
+------+--------+---------------+
3 rows in set (0.00 sec)

‍

In the default MySQL configuration, SQL_SAFE_UPDATES=0, the following SQL command does not trigger an error.

UPDATE transactions SET amount = 0;

But it is able to alter the rows in the transactions table unconditionally.

mysql> select * from transactions;
+------+--------+---------------+
| id   | amount | description   |
+------+--------+---------------+
|    1 |      0 | Transaction 1 |
|    2 |      0 | Transaction 2 |
|    3 |      0 | Transaction 3 |
+------+--------+---------------+
3 rows in set (0.00 sec)

While this is not the intended update, it is possible to protect data from unintended user actions using a simple configuration parameter.

Setting SQL_SAFE_UPDATES=1 would trigger the ERROR 1175.

ERROR 1175 (HY000): You are using safe update mode and you tried 
to update a table without a WHERE that uses a KEY column.

And with SQL_SAFE_UPDATES=1, MySQL requires UPDATE and DELETE to specify a condition using the WHERE clause for the records it wants to update.

UPDATE transactions SET amount = 0 WHERE account_id  = 1;

How to fix it

You can confirm your current configuration by querying your MySQL database:

SHOW GLOBAL VARIABLES LIKE 'sql_safe_updates';

Which in the default configuration will NOT have the sql_safe_updates enabled. And will return:

mysql> SHOW GLOBAL VARIABLES LIKE 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

Find the configuration option file (my.cnf or my.in) and add:

[mysqld]
sql_safe_updates	= 1

And restart your MySQL server, sudo systemctl restart mysqld (or variant for your infrastructure).

And restart your MySQL server, sudo systemctl restart mysqld (or variant for your infrastructure).

Humor sidenote: When running the MySQL server, you can also enable this setting by calling the executable with the parameter `--i-am-a-dummy` (mysql-client also supports –i-am-a-dummy and –safe-updates as valid command-line parameters).

Scan your configurations

This feels like a simple thing. That good developers should catch and prevent in their software development process. But it shows how the default configurations for a lot of the software applications we use everyday may not be configured to protect data, be secure, or otherwise be optimal for your specific usage.

At CoGuard, we believe that infrastructure as code includes the configurations of the deployment environment. Where does this live? Is it dev? Is it build? Is it deployment? It has implications for all users. We've built a tool that can be added to the development, build and deploy processes that can discover configuration files for IaC, containers, networks and applications. Install it tfo day to see if how your configurations stand up to security best practices and common security compliance frameworks.

Get started for free today »

Troubleshooting Configurations