Why is innodb strict mode not enabling?

I tried adding this line in /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu)

innodb_strict_mode = 1

This is within the [mysqld] section, where I have seen this line in other examples. I know this config file is being read at startup because I get errors if I type in nonsense.

Nevertheless, SHOW VARIABLES lists this variable as OFF and apparently I can insert rows without specifying my non-default fields.

Why isn't this variable being set?

I have tried =on instead of =1, no difference. The config is otherwise unmodified from the installation default, AFAIK, full text here.

You can set this SESSION variable from the command line:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | OFF   |
+--------------------+-------+
1 row in set (0.01 sec)

Then, you do this:

mysql> set innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

Then, to check:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+

Then, on exiting and reconnecting, and rechecking the variable, it is again OFF - the default. Use SET GLOBAL innodb_strict_mode = ON if you don't want it to reset after logging off. But, setting it in the client will not make the change stick after a reboot of the server.

Edit my.cnf - I've never seen or touched mysqld.cnf.

Put the line(s) below in the [mysqld] section of my.cnf.

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
innodb_strict_mode = ON

(I recommend the sql_mode line also - otherwise, for example, certain GROUP BY queries will return (ahem...) anomalous results).

Bounce the server - and relog in.

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

It's all in the documentation here.


You can turn innodb_strict_mode ON or OFF on the command line when you
start mysqld, or in the configuration file my.cnf or my.ini. You can
also enable or disable innodb_strict_mode at runtime with the
statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is
either ON or OFF. Changing the GLOBAL setting requires the SUPER
privilege and affects the operation of all clients that subsequently
connect. Any client can change the SESSION setting for
innodb_strict_mode, and the setting affects only that client.

Maybe you don't have the SUPER privilege? Log in and run SHOW GRANTS to find out.

StackExchange Database Administrators Q#135844, answer score: 9