SET GLOBAL sql_mode ='STRICT_ALL_TABLES'

[Gillian]

I am logged in as root on MySQL and cannot set the global sql_mode. I would like to include the mode " STRICT_ALL_TABLES"

mysql> SET GLOBAL sql_mode ='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Any ideas?

Thanks

Gillian

[pivanof]

You need to change sql_mode using Cloud SQL API or UI. See https://cloud.google.com/sql/docs/mysql/flags.

[Gillian]

Hi

Thanks for the reply.

This SQL_MODE is not an option as a FLAG, since it is not a system variable. I looked through all those flags already.

According the MySQL:  To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf 

But in GCP, we do not have access to command line or my.cnf file.

Still researching on GCP to see how to do that.

Pulling my hair out

Thanks

Gillian

[pivanof · Accepted solution]

Actually sql_mode is a system variable. And I see it in the list here https://cloud.google.com/sql/docs/mysql/flags#mysql-s, which means you can change it in Cloud SQL.

[Gillian]

You are correct. Sorry.

I was looking for STRICT_ALL_TABLES as a flag.

But SQL_MODE is the flag then I choose the options.

That worked.

Thanks for your help Pivanof.

Have a great day.

Gillian