📋

Description

This error occurs when a SQL statement attempts to insert or update data, but a non-nullable column is not provided with an explicit value and lacks a defined `DEFAULT` value in its schema. MariaDB cannot automatically assign a value, preventing the operation.

💬

Error Message

Variable '%s' doesn't have a default value

🔍

Known Causes

3 known causes

⚠️

Incomplete INSERT Statement

An `INSERT` statement omits a non-nullable column that does not have a `DEFAULT` value defined in the table schema.

⚠️

Column Lacks Default Definition

A `NOT NULL` column was created without an explicit `DEFAULT` clause, preventing MariaDB from assigning a value when none is provided.

⚠️

Strict SQL Mode Enabled

MariaDB's `sql_mode` is configured to be strict, enforcing that all `NOT NULL` columns must have a value or a `DEFAULT` defined.

🛠️

Solutions

3 solutions available

1. Define a Default Value for the Variable easy

Explicitly set a default value for the system variable in your MariaDB configuration.

1

Identify the variable name from the error message (e.g., '%s' will be the actual variable name).

2

Edit your MariaDB configuration file. This is typically `my.cnf` or `my.ini` on Linux/macOS or Windows respectively. Common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or within the MariaDB installation directory on Windows.

3

Locate the `[mysqld]` section in the configuration file.

4

Add or modify a line to define the default value for the missing variable. Replace `variable_name` with the actual name from the error and `default_value` with a suitable value.

[mysqld]
variable_name = default_value

5

Save the configuration file and restart the MariaDB service for the changes to take effect.

# On Linux/macOS (using systemd):
sudo systemctl restart mariadb

# On Windows:
# Open Services (services.msc) and restart the MariaDB service.

2. Set the Variable Dynamically (Temporary Fix) easy

Set the system variable's value for the current session or globally without modifying configuration files.

1

Connect to your MariaDB server using a client like `mysql` or MariaDB Workbench.

mysql -u your_username -p

2

Execute the `SET GLOBAL` or `SET SESSION` command to define the variable. Replace `variable_name` with the actual name from the error and `default_value` with a suitable value.

-- For the current session only:
SET SESSION variable_name = 'default_value';

-- For all new connections (requires SUPER privilege):
SET GLOBAL variable_name = 'default_value';

3

If you used `SET GLOBAL`, restart the MariaDB server for the change to be fully applied if it's a variable that requires a restart.

# On Linux/macOS (using systemd):
sudo systemctl restart mariadb

# On Windows:
# Open Services (services.msc) and restart the MariaDB service.

3. Review MariaDB Version and Documentation medium

Ensure the variable is intended to be set and understand its purpose for your MariaDB version.

1

Identify the exact variable name from the error message.

2

Check your MariaDB server version using the following command:

SELECT VERSION();

3

Consult the official MariaDB documentation for your specific version to understand the variable. Search for the variable name on the MariaDB Knowledge Base or the official documentation website.

4

Determine if the variable is a user-defined variable that was expected to be set by an application or script, or if it's a system variable that should have a default. If it's a system variable, the previous solutions apply. If it's expected to be user-defined, ensure the application or script is correctly setting it.

🔗

Related Errors

5 related errors

MariaDB Error 1230: Missing Variable Default Value