非严格模式下MariaDB无法插入空值问题求助
Hey there, let's break down what's going on here and fix that insertion issue you're facing.
First, let's clarify the key differences between your old MySQL 5.6 setup and the new MariaDB 10.1 environment. Even though your sql_mode shows only NO_ENGINE_SUBSTITUTION, there's another critical setting in MariaDB that might be enforcing strict checks on NOT NULL columns—innodb_strict_mode.
Step 1: Verify the innodb_strict_mode setting
Run this query to check if it's enabled:
SHOW VARIABLES LIKE 'innodb_strict_mode';
If the value is ON, that's likely the culprit. Unlike MySQL 5.6, MariaDB enables innodb_strict_mode by default in many cases, and this setting overrides the non-strict sql_mode behavior for InnoDB tables. When it's on, inserting a NULL into a NOT NULL column (even without a default) will throw an error instead of falling back to an implicit default (like an empty string for text fields, or 0 for numeric fields).
Step 2: Test with a temporary fix
To confirm this is the issue, disable innodb_strict_mode for your current session first:
SET SESSION innodb_strict_mode = OFF;
Try inserting your data again—this should behave just like your old MySQL 5.6 environment did.
Step 3: Make the change permanent
If the temporary fix works, update your MariaDB configuration file (usually located at /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf on Debian 9) to add or modify this line:
innodb_strict_mode = OFF
Then restart the MariaDB service to apply the change system-wide:
sudo systemctl restart mariadb
Bonus: Check session-level sql_mode
Sometimes applications or database drivers override the global sql_mode when establishing a connection. Double-check your session-specific setting with:
SHOW SESSION VARIABLES LIKE 'sql_mode';
If this includes strict mode values (like STRICT_TRANS_TABLES or STRICT_ALL_TABLES), you'll need to adjust your application's connection settings to avoid overriding the global mode.
That should resolve the insertion error you're seeing. Let me know if you run into any issues while implementing this!
内容的提问来源于stack exchange，提问作者Romulo
