Error
Error Code:
1861
MySQL Error 1861: Silent NULL Conversion Issue
Description
MySQL Error 1861 indicates that the database cannot silently convert a NULL value as required by the current SQL_MODE. This typically occurs when an operation attempts to insert or update a NULL into a column defined as NOT NULL, or when strict SQL modes prevent implicit NULL handling.
Error Message
cannot silently convert NULL values, as required in this SQL_MODE
Known Causes
3 known causesInserting NULL into NOT NULL Column
Attempting to insert or update a row where a NULL value is provided for a column explicitly defined with a NOT NULL constraint.
Strict SQL_MODE Enabled
The active SQL_MODE (e.g., STRICT_TRANS_TABLES, NO_ZERO_DATE) disallows silent conversion of NULL values to default or non-NULL values, enforcing stricter data integrity.
Implicit Data Type Conversion
MySQL is attempting an implicit data type conversion involving a NULL value, which is prohibited by the current strict SQL_MODE settings.
Solutions
4 solutions available1. Temporarily Disable Strict Mode for Silent NULL Conversion easy
This is a quick fix to allow the operation to complete by temporarily relaxing SQL mode.
1
Connect to your MySQL server using a client like the MySQL command-line client or a GUI tool.
2
Execute the following SQL command to set the `sql_mode` for the current session. Replace `YOUR_CURRENT_SQL_MODE` with the output of `SELECT @@SESSION.sql_mode;` if you want to preserve other settings, or use a simplified mode.
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';3
Re-run the query or operation that was causing the error.
4
The `sql_mode` will revert to its original setting when the session ends.
2. Modify `sql_mode` Globally (with Caution) medium
Permanently adjust the MySQL server's `sql_mode` to allow silent NULL conversions. This affects all connections and should be done with careful consideration of its implications.
1
Access your MySQL server's configuration file. This is typically named `my.cnf` or `my.ini` and is located in your MySQL installation directory or `/etc/mysql/`.
2
Locate the `[mysqld]` section in the configuration file.
3
Add or modify the `sql_mode` directive. Remove `STRICT_TRANS_TABLES` and `STRICT_ALL_TABLES` if they are present and causing the issue. A common and less strict mode is `NO_ENGINE_SUBSTITUTION`. If you need to keep other modes, ensure you don't include the strict modes that cause this error. For example: `sql_mode = 'NO_ENGINE_SUBSTITUTION'` or `sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'`.
[mysqld]
sql_mode = 'NO_ENGINE_SUBSTITUTION'4
Save the configuration file.
5
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql # For systemd-based systems
# or
sudo service mysql restart # For older init systems6
Re-run the query or operation that was causing the error.
3. Explicitly Handle NULL Values in Queries medium
Modify your SQL queries to explicitly handle potential NULL values, preventing the need for strict mode to enforce this.
1
Identify the specific query or queries that are triggering the error. This usually involves an `INSERT` or `UPDATE` statement where a column that does not allow NULL is being assigned a NULL value, or a `SELECT` query with implicit conversions.
2
For `INSERT` statements, ensure that you are providing a valid value for non-nullable columns. If a column is nullable, you can explicitly insert `NULL` or omit it if it has a default value.
INSERT INTO your_table (column1, column2) VALUES (value1, NULL);3
For `UPDATE` statements, similarly ensure valid assignments.
UPDATE your_table SET column1 = value1 WHERE id = 1;4
If the error occurs during data retrieval or aggregation, use functions like `COALESCE()` or `IFNULL()` to provide a default value for NULLs.
SELECT COALESCE(column_with_nulls, 'default_value') FROM your_table;5
Re-run the modified query.
4. Modify Table Schema to Allow NULLs medium
Change the table definition to explicitly allow NULL values for columns that were previously non-nullable and are now causing issues.
1
Connect to your MySQL server.
2
Use `DESCRIBE your_table;` to identify the column(s) that are causing the error and are defined as `NOT NULL` but are receiving `NULL` values.
DESCRIBE your_table;3
Execute an `ALTER TABLE` statement to modify the column definition. Replace `your_table`, `your_column`, and `appropriate_data_type` accordingly. If the column should allow NULL, change `NOT NULL` to `NULL`.
ALTER TABLE your_table MODIFY your_column appropriate_data_type NULL;4
If the column had a default value and you are making it nullable, you might want to explicitly set a default value if `NULL` is not desired as the default.
ALTER TABLE your_table MODIFY your_column appropriate_data_type DEFAULT 'some_default_value' NULL;5
Re-run the query or operation that was causing the error.