Error

Error Code: 1861

MariaDB Error 1861: Silent NULL Conversion Prohibited

📦 MariaDB

📋

Description

This error occurs when MariaDB attempts to process a `NULL` value for a column that is defined as `NOT NULL`, and the current `SQL_MODE` prevents the database from performing a silent conversion or assigning a default value. It typically indicates a data integrity violation due to an attempt to store invalid data in a strict environment.

💬

Error Message

cannot silently convert NULL values, as required in this SQL_MODE

🔍

Known Causes

4 known causes

⚠️

Attempting to insert NULL into NOT NULL column

You are trying to insert or update a row with a `NULL` value into a column that has been explicitly defined with the `NOT NULL` constraint.

⚠️

Strict SQL_MODE Enabled

The database's current `SQL_MODE` (e.g., `STRICT_TRANS_TABLES`, `NO_ZERO_DATE`) prevents MariaDB from silently converting or assigning a default value when a `NULL` is provided for a `NOT NULL` column.

⚠️

Missing DEFAULT Value for NOT NULL Column

A `NOT NULL` column without an explicit `DEFAULT` value is omitted in an `INSERT` statement, and the `SQL_MODE` disallows implicit default assignment, leading to an attempt to insert `NULL`.

⚠️

Altering Column to NOT NULL with Existing NULLs

You are attempting to change a column's definition to `NOT NULL` while the column currently contains `NULL` values in existing rows, and the `SQL_MODE` disallows this operation without explicit handling.

🛠️

Solutions

4 solutions available

1. Temporarily Disable Strict Mode for the Session easy

Allows NULL conversions for the current database connection without altering server configuration.

Connect to your MariaDB server using a client (e.g., `mariadb` command-line client, MySQL Workbench, DBeaver).

Execute the following SQL command to temporarily disable strict mode for the current session. This will allow NULL values to be inserted into columns that expect a non-NULL value, as long as the column definition itself permits NULLs.

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

Now, re-run the SQL statement that was causing the error.

Note: This change is only for the current session. If you disconnect and reconnect, strict mode will revert to its configured value.

2. Modify Table Schema to Allow NULLs medium

Updates the table definition to explicitly permit NULL values in the offending column.

Identify the table and column that is causing the 'Silent NULL Conversion Prohibited' error. This typically happens when you try to insert or update a row with a NULL value in a column that is defined as NOT NULL, but the `sql_mode` includes `STRICT_TRANS_TABLES` or `STRICT_ALL_TABLES`.

Connect to your MariaDB server.

Execute the following SQL command to alter the table. Replace `your_table_name` with the actual table name and `your_column_name` with the column name that needs to allow NULLs.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype NULL;

Replace `datatype` with the original data type of the column (e.g., `INT`, `VARCHAR(255)`, `DATETIME`).

After successfully altering the table, try running your original SQL statement again.

3. Adjust Global SQL Mode Configuration advanced

Permanently changes the server's SQL mode to exclude strict NULL conversion checks.

Connect to your MariaDB server as a user with `SUPER` or `SYSTEM_VARIABLES_ADMIN` privileges (e.g., `root`).

Check the current global SQL mode.

SHOW GLOBAL VARIABLES LIKE 'sql_mode';

Determine which strict modes are causing the issue. Common culprits are `STRICT_TRANS_TABLES` and `STRICT_ALL_TABLES`.

To permanently change the global `sql_mode`, you need to edit the MariaDB configuration file. The location of this file varies by operating system (e.g., `/etc/my.cnf`, `/etc/mysql/my.cnf`, `/etc/my.cnf.d/server.cnf`).

Open the configuration file with a text editor.

Locate the `[mysqld]` section. If it doesn't exist, create it.

Add or modify the `sql_mode` line. You'll want to remove `STRICT_TRANS_TABLES` and/or `STRICT_ALL_TABLES` from the existing value.

[mysqld]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE"

Save the configuration file.

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

Verify the global SQL mode has been updated.

SHOW GLOBAL VARIABLES LIKE 'sql_mode';

Now, try running your SQL statement again.

4. Explicitly Specify NULL in Your SQL Statement easy

Ensures that NULL values are correctly handled by explicitly stating them.

When inserting or updating data, ensure that any explicit NULL values are written using the `NULL` keyword, not as empty strings or other representations that might be implicitly converted.

For example, if you were trying to insert a row and a column should be NULL, use:

INSERT INTO your_table_name (column1, column2, column3)
VALUES ('value1', NULL, 'value3');

If your application code is generating the SQL, ensure it's correctly formatting `NULL` values.

🔗

Related Errors

5 related errors