Cannot setting buffer pool load and dump at startup and shutdown

[jiraiya1]

Hi everybody.
I have a fairly large SQL cloud(Mysql 5.6) instance.
I want after restart, mysql will automatically load buffer pool state.
I read the docs here, but I can't find the 2 database flags on the UI(console):
Innodb_buffer_pool_dump_at_shutdown
Innodb_buffer_pool_load_at_startup.
I tried using the command 'SET GLOBAL ' but it said ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
I don't understand where I went wrong, thank you for your help.

Tks.

[ms4446 · Accepted solution]

Google Cloud SQL, as a managed database service, often doesn't provide all the settings and privileges that are available in a self-hosted MySQL setup. Some of these restrictions are due to ensuring the stability, safety, and scalability of the managed service.

Regarding your issue:

1. SUPER Privilege: In Google Cloud SQL, you don't have the SUPER privilege. This is by design to prevent certain operations that can potentially interfere with the managed environment.

2. Database Flags: While Google Cloud SQL provides a set of customizable flags for your MySQL instance, not all MySQL flags might be available for adjustment through the console. This is a limitation of the managed service, and the available flags might vary depending on the MySQL version and other factors.

To resolve your issue:

To enable automatic buffer pool loading in Google Cloud SQL for MySQL 5.6, you need to use the gcloud command-line tool.

1. Enable automatic buffer pool loading.

gcloud sql instances patch <INSTANCE_NAME> --database-flags innodb_buffer_pool_dump_at_shutdown=ON,innodb_buffer_pool_load_at_startup=ON

2. Restart the instance.

gcloud sql instances restart <INSTANCE_NAME>

3. Verify that automatic buffer pool loading is enabled.

gcloud sql instances describe <INSTANCE_NAME>

The output of this command will include a section called "settings", which will list the values of the innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup flags.

If the flags are set to ON, then automatic buffer pool loading is enabled.

[jiraiya1]

Thank you for reply.

I followed the steps as instructed, but get error.

❯ gcloud --project MY_PROJECT sql instances patch MY_DB --database-flags innodb_buffer_pool_dump_at_shutdown=ON,innodb_buffer_pool_load_at_startup=ON
The following message will be used for the patch API method.
{"name": "MY_DB", "project": "MY_PROJECT", "settings": {"databaseFlags": [{"name": "innodb_buffer_pool_dump_at_shutdown", "value": "ON"}, {"name": "innodb_buffer_pool_load_at_startup", "value": "ON"}]}}
WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/mysql/flags - to see if your instance
will be restarted when this patch is submitted.

Do you want to continue (Y/n)? Y

ERROR: (gcloud.sql.instances.patch) HTTPError 400: The requested flag is either misspelled or unsupported by this database type.

Thank you.

[ms4446]

I understand that you are getting the error "The requested flag is either misspelled or unsupported by this database type" when trying to patch your MySQL 5.6 instance in Google Cloud SQL.

As I mentioned in my previous response, this error message indicates that the flag you are trying to set might not be supported in the Google Cloud SQL environment for your specific version or configuration of MySQL 5.6.

While the flags innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup were introduced starting from MySQL 5.6.2, it's possible that they might not be supported or allowed to be modified in the Google Cloud SQL environment for MySQL 5.6.

To verify if these flags are supported in your Google Cloud SQL environment, mysql -V

[treovot]

@ms4446  @jiraiya1  I got the same issues , with mysql cloud 5.6 , I hit cli gcloud and got errors,

ERROR: (gcloud.sql.instances.patch) HTTPError 400: The requested flag is either misspelled or unsupported by this database type.

[ms4446]

The error message "The requested flag is either misspelled or unsupported by this database type" indicates that the flag you are trying to set might not be supported in the Google Cloud SQL environment for your specific version or configuration of MySQL 5.6. Can you please confirm the version of MySQL.  mysql -V