gcloud sql instances patch fails with invalid data error

Question

When trying to add high availability on an existing Cloud SQL instance using:

gcloud sql instances patch $INSTANCE --project $PROJECT --availability-type regional

the process fails with this message

The following message will be used for the patch API method. {"project": "$PROJECT", "name": "$INSTANCE", "settings": {"availabilityType": "REGIONAL", "databaseFlags": [{"name": "sql_mode", "value": "TRADITIONAL"}, {"name": "default_time_zone", "value": "+01:00"}]}} ERROR: (gcloud.sql.instances.patch) HTTPError 400: The incoming request contained invalid data.

It also fails using the web interface. Gcloud version Google Cloud SDK [280.0.0] This is the output of the log (not much help that I can see):

2020-02-14 11:01:34,476 DEBUG    root            Loaded Command Group: [u'gcloud', u'sql', u'instances']
2020-02-14 11:01:34,510 DEBUG    root            Loaded Command Group: [u'gcloud', u'sql', u'instances', u'patch']
2020-02-14 11:01:34,517 DEBUG    root            Running [gcloud.sql.instances.patch] with arguments: [--availability-type: "regional", --project: "$PROJECT", INSTANCE: "$INSTANCE"]
2020-02-14 11:01:35,388 INFO     ___FILE_ONLY___ The following message will be used for the patch API method.

2020-02-14 11:01:35,398 INFO     ___FILE_ONLY___ {"project": "$PROJECT", "name": "$INSTANCE", "settings": {"availabilityType": "REGIONAL", "databaseFlags": [{"name": "sql_mode", "value": "TRADITIONAL"}, {"name": "default_time_zone", "value": "+01:00"}]}}

2020-02-14 11:01:35,865 DEBUG    root            (gcloud.sql.instances.patch) HTTPError 400: The incoming request contained invalid data.
Traceback (most recent call last):
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\lib\googlecloudsdk\calliope\cli.py", line 981, in Execute
    resources = calliope_command.Run(cli=self, args=args)
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\lib\googlecloudsdk\calliope\backend.py", line 807, in Run
    resources = command_instance.Run(args)
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\lib\surface\sql\instances\patch.py", line 306, in Run
    return RunBasePatchCommand(args, self.ReleaseTrack())
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\lib\surface\sql\instances\patch.py", line 278, in RunBasePatchCommand
    instance=instance_ref.instance))
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\lib\googlecloudsdk\third_party\apis\sql\v1beta4\sql_v1beta4_client.py", line 697, in Patch
    config, request, global_params=global_params)
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\bin\..\lib\third_party\apitools\base\py\base_api.py", line 731, in _RunMethod
    return self.ProcessHttpResponse(method_config, http_response, request)
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\bin\..\lib\third_party\apitools\base\py\base_api.py", line 737, in ProcessHttpResponse
    self.__ProcessHttpResponse(method_config, http_response, request))
  File "C:\Users\udAL\AppData\Local\Google\Cloud SDK\google-cloud-sdk\bin\..\lib\third_party\apitools\base\py\base_api.py", line 604, in __ProcessHttpResponse
    http_response, method_config=method_config, request=request)
HttpBadRequestError: HttpError accessing <https://sqladmin.googleapis.com/sql/v1beta4/projects/$PROJECT/instances/$INSTANCE?alt=json>: response: <{'status': '400', 'content-length': '269', 'x-xss-protection': '0', 'x-content-type-options': 'nosniff', 'transfer-encoding': 'chunked', 'vary': 'Origin, X-Origin, Referer', 'server': 'ESF', '-content-encoding': 'gzip', 'cache-control': 'private', 'date': 'Fri, 14 Feb 2020 10:01:35 GMT', 'x-frame-options': 'SAMEORIGIN', 'alt-svc': 'quic=":443"; ma=2592000; v="46,43",h3-Q050=":443"; ma=2592000,h3-Q049=":443"; ma=2592000,h3-Q048=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000', 'content-type': 'application/json; charset=UTF-8'}>, content <{
  "error": {
    "code": 400,
    "message": "The incoming request contained invalid data.",
    "errors": [
      {
        "message": "The incoming request contained invalid data.",
        "domain": "global",
        "reason": "invalidRequest"
      }
    ]
  }
}
>
2020-02-14 11:01:35,868 ERROR    root            (gcloud.sql.instances.patch) HTTPError 400: The incoming request contained invalid data.
2020-02-14 11:01:35,898 DEBUG    root            Metrics reporting process started...

Edit:

When using the gcloud cli command:

gcloud patch with 3 input parameters

1. Both $PROJECT and $INSTANCE do exist since I can gcloud sql databases list --instance $INSTANCE --project $PROJECT and it works fine.
2. availability-type=regional it's documented so should work

I'm not constructing the request manually, I'm using gcloud CLI

When using the console.cloud.google.com web interface:

Main menu -> SQL -> select instance -> Enable High Availability. It's a button, no parameters added by myself.

Both return the same error "The incoming request contained invalid data." Can't see how I may be doing it wrong.

Answer 1

Please check your data in the incoming request.

I used the Method: instances.patch and it worked as expected for me.

project instance-name

request body:

  "settings": {
    "availabilityType": "REGIONAL",
    "databaseFlags": [
      {
        "name": "sql_mode",
        "value": "TRADITIONAL"
      },
      {
        "name": "default_time_zone",
        "value": "+01:00"
      }
    ]
  }
}

Curl command:

  'https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project/instances/your_instancet?key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"settings":{"availabilityType":"REGIONAL","databaseFlags":[{"name":"sql_mode","value":"TRADITIONAL"},{"name":"default_time_zone","value":"+01:00"}]}}' \
  --compressed```

Response 200:

{
  "kind": "sql#operation",
  "targetLink": "https://content-sqladmin.googleapis.com/sql/v1beta4/projects/your-project/instances/your-instance",
  "status": "PENDING",
  "user": "@cloud.com",
  "insertTime": "2020-02-14T12:35:37.615Z",
  "operationType": "UPDATE",
  "name": "3f55c1be-97b5-4d37-8d1f-15cb61b4c6cc",
  "targetId": "your-instance",
  "selfLink": "https://content-sqladmin.googleapis.com/sql/v1beta4/projects/wave25-vladoi/operations/3f55c1be-97b5-4d37-8d1f-15cb61b4c6cc",
  "targetProject": "your-project"
}