How to set innodb_strict_mode = OFF globally in Cloud SQL

[Vinícius Corrêa]

Hi, folks!

I'm moving a MySQL DB from on-premise to Cloud SQL and the developer are facing a problem related to NULL values in NOT NULL columns.

Previously, they are able to INSERT a new record without set a value in a NOT NULL column due to innodb_strict_mode parameter which are set to OFF in on-premise today. After moving to Cloud SQL they are not able to use this feature, once the innodb_strict_mode are ON.

An alternative will be set this value in application level, perhaps I want to keep this setting in DB level. Trying to use SET GLOBAL innodb_strict_mode = OFF didn't work for me.

Thanks! 

[Katayoon (Cloud Platform Support)]

Hi Vinícius,

I should note that the feature request on ‘enabling “innodb_strict_mode” by default’ already exists in the Issue Tracker and you may star the case to receive updates on it.

For the time being, you may try the following steps to add the table to your database:

1.  Open your create_script.sql file with a text editor, and add one line above the "CREATE TABLE" line, the file should look like this afterwards:  Do not add the lines or the "..." , those are only meant to make it easier to see the file in this post.

-------------------------------------------------------------------------------

SET innodb_strict_mode = 0 ;

CREATE TABLE `history_data` (

 `id` bigint(20) NOT NULL AUTO_INCREMENT,

 `coffee` longtext,

 .....

-------------------------------------------------------------------------------

2.  Add the table to your database instance, replacing HOSTNAME with the hostname of the server, replacing DATABASE with the name of the database, and ensure that the path to the script is correct.

$ mysql -uroot -h HOSTNAME DATABASE < ~/path/to/create_script.sql

3.  Verify that the table has been created

[Vinícius Corrêa]

Hi, Katayoon.

I've tried it without success.

In this case I want to disable the innodb_strict_mode.

Thanks!

[Katayoon (Cloud Platform Support)]

Yes, you are right. The feature request seems to be like the default behavior of MySQL 5.7. However Cloud SQL product team welcome all the feature requests.

More details about the error you receive or more clarification on the steps you have taken to add the table to your database would make us able to assist you further. For any technical question on how to add your table to your database, you may post your full detailed question to Stack Overflow.