Understanding mode_default 0 auto 0 in MySQL/MariaDB Configuration

1. Check SQL Mode in MySQL/MariaDB

The most likely issue relates to SQL mode settings. SQL mode controls how MySQL validates and executes SQL statements.

For neighbouring MySQL/MariaDB tuning topics, see MariaDB Maximum Table Size (innodb_file_per_table), Fix: cacheEngine=none in MySQL/MariaDB, and MariaDB Limits — Understanding and Managing Database Limits.

Check Current SQL Mode

Run the following SQL command in your MySQL/MariaDB client:

SELECT @@sql_mode;

Expected Output Example:

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

If you see mode_default 0 auto 0 or an empty result, the configuration might be incorrect and needs to be properly set.

2. Set a Proper SQL Mode

To fix an incorrect SQL mode, set it to a standard configuration that enforces data integrity and proper SQL behavior.

1. Connect to your MySQL/MariaDB server with administrative privileges.
2. Run the following command to set the global SQL mode:

   SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. Verify the change took effect:

   SELECT @@sql_mode;

This change is temporary and will be lost when the server restarts. For a permanent fix, proceed to the next section.

3. Permanently Fix SQL Mode in my.cnf

To ensure your SQL mode settings persist after server restarts, you need to edit the MySQL configuration file.

1. Open MySQL Configuration File

The location varies by operating system:

• Ubuntu/Debian: /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf
• CentOS/RHEL/Fedora: /etc/my.cnf

sudo nano /etc/mysql/my.cnf

2. Add/Modify the SQL Mode

Find the [mysqld] section in the configuration file. If it doesn't exist, create it. Then add or modify the sql_mode directive:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3. Restart MySQL/MariaDB

Apply the changes by restarting the database service:

# For MariaDB
sudo systemctl restart mariadb

# For MySQL
sudo systemctl restart mysql

Now the SQL mode will persist after server reboots.

Linux VDS

High performance for your projects

• Root access and flexible setup
• Control panel
• NVMe disks
• DDR5

View pricing

4. Check Storage Engine Defaults

If the issue relates to storage engines (MyISAM, InnoDB, etc.), you should check and potentially change the default engine.

Check Available Storage Engines

Run this SQL command to see all available engines and their status:

SHOW ENGINES;

Set Default Storage Engine

If the default engine is incorrect, you can set it in the my.cnf file:

1. Edit the configuration file again:

   sudo nano /etc/mysql/my.cnf

2. Add or modify the default_storage_engine directive in the [mysqld] section:

   [mysqld]
   default_storage_engine = InnoDB
   sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3. Restart MySQL/MariaDB to apply the change:

   sudo systemctl restart mysql

Summary of Fixes

Following these steps should resolve any issues related to the mode_default 0 auto 0 configuration and ensure MySQL/MariaDB is working correctly!