Database setup

You can manage some database settings using Managed Service for MySQL interfaces.

Set SQL mode (sql_mode)

You can set or change the value of the sql_mode variable defining the SQL mode for the database. This operation restarts the cluster hosts.

If you don't have the Yandex.Cloud command line interface yet, install it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

Set the SQL modes in the --set parameter, for example:

$ yc managed-mysql cluster update-config
     --cluster-name=<cluster name>
     --set '"sql_mode=NO_KEY_OPTIONS,NO_TABLE_OPTIONS"'

Pay close attention to quotation marks: the entire string must constitute the parameter value, including sql_mode=.

You can get the cluster name when you list clusters in the folder.

Pass the sqlMode array in the new MySQL configuration by making the update query.

Change the character set and collation rules (CHARACTER SET, COLLATE)

To configure the database character settings, use the ALTER DATABASE command. To do this, you need to run a query on behalf of a user with the ALL or ALTER privilege for the applicable database, for example:

ALTER DATABASE dbname CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_unicode_ci';

To apply settings to the database tables along with the database, convert the tables with the same settings:

ALTER TABLE dbname.tablename CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';