Mysql – Why is innodb strict mode not enabling

MySQL

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.

Best Answer

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.

Related Question