Error
Error Code:
1175
MariaDB Error 1175: Safe Update Mode Constraint
Description
Error 1175 indicates that you are attempting to modify a table using an `UPDATE` or `DELETE` statement while MariaDB's `SQL_SAFE_UPDATES` mode is active. This safety feature prevents operations that don't specify a `WHERE` clause involving a key column, designed to protect against unintended changes to large sets of data.
Error Message
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
Known Causes
3 known causesSQL_SAFE_UPDATES Enabled
MariaDB's `SQL_SAFE_UPDATES` session variable is active, enforcing that `UPDATE` and `DELETE` statements must target rows via a key column.
Missing or Non-Key WHERE Clause
The `UPDATE` or `DELETE` statement does not include a `WHERE` clause, or the `WHERE` clause uses a column that is not a primary key or unique index.
Accidental Omission of Key Column
You intended to use a key column in your `WHERE` clause but inadvertently used a non-key column or made a typo, leading to the safety check trigger.
Solutions
3 solutions available1. Temporarily Disable Safe Update Mode easy
Quickly bypass the safe update mode for a single session.
1
Connect to your MariaDB server using your preferred client (e.g., `mysql` command-line client, DBeaver, MySQL Workbench).
2
Execute the following SQL command to disable safe update mode for the current session:
SET SQL_SAFE_UPDATES = 0;3
Now, execute your `UPDATE` statement. It will proceed without the safe update constraint.
-- Your UPDATE statement here
UPDATE your_table SET column_name = 'new_value' WHERE some_id = 123;4
It is highly recommended to re-enable safe update mode immediately after your operation to maintain safety:
SET SQL_SAFE_UPDATES = 1;2. Modify UPDATE Statement to Include a WHERE Clause with a KEY Column medium
The safest approach: ensure your updates target specific rows using indexed columns.
1
Identify the primary key or a frequently used index (KEY) column in the table you are updating.
2
Analyze your `UPDATE` statement and determine which rows you intend to modify.
3
Rewrite your `UPDATE` statement to include a `WHERE` clause that filters by the identified KEY column. This ensures that only specific rows are affected.
UPDATE your_table
SET column_name = 'new_value'
WHERE primary_key_column = some_specific_id;
-- Or using another indexed column:
-- UPDATE your_table
-- SET column_name = 'new_value'
-- WHERE indexed_column = some_indexed_value;4
If you truly intend to update all rows, consider the implications and ensure this is your desired outcome before proceeding. If so, you might temporarily disable safe updates (see Solution 1) or reconsider the necessity of updating all rows.
3. Globally Disable Safe Update Mode (Use with Extreme Caution) advanced
Permanently disable safe update mode for all users and sessions. Not recommended for production environments.
1
Connect to your MariaDB server as a user with administrative privileges (e.g., `root`).
2
Open the MariaDB configuration file. The location varies by operating system and installation method, but common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or within `/etc/mysql/conf.d/`.
3
Locate the `[mysqld]` section in the configuration file.
4
Add or modify the following line within the `[mysqld]` section to set `sql_safe_updates` to `0`:
[mysqld]
sql_safe_updates = 05
Save the configuration file.
6
Restart the MariaDB service for the changes to take effect. The command to restart varies by your operating system's init system (e.g., `systemctl`, `service`).
sudo systemctl restart mariadb7
After restarting, safe update mode will be disabled globally. **This is generally not recommended for production environments as it removes a crucial safety net against accidental data loss.**