panic: Error migrating database: Error 1050: Table 'cloud_operations' already exists

[arpitvipulsharma]

Hello Team,
I am pushing gcp-service-broker app to Open Source CF. I am getting following error:

017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR panic: Error migrating database: Error 1050: Table 'cloud_operations' already exists
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR goroutine 1 [running]:
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR gcp-service-broker/db_service.New.func1()
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR /tmp/gobuildpack.gopath385159442/.go/src/gcp-service-broker/db_service/db_service.go:37 +0x155
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR sync.(*Once).Do(0x5632a6738898, 0xc420195ec8)
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR /tmp/contents525594273/deps/0/go1.8.5/go/src/sync/once.go:44 +0xc0
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR gcp-service-broker/db_service.New(0x5632a66f2400, 0xc4201639e0, 0xc4202167c0)
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR /tmp/gobuildpack.gopath385159442/.go/src/gcp-service-broker/db_service/db_service.go:39 +0x6b
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR main.main()
2017-12-28T19:53:40.68+0100 [APP/PROC/WEB/0] ERR /tmp/gobuildpack.gopath385159442/.go/src/gcp-service-broker/server.go:40 +0x211

Can anyone help me on this?

[mkjelland]

Hi @arpitvipulsharma! I have seen this issue happen when the manifest.yml has json that isn't formatted correctly in it. Are you using the manifest that is in this repo? If so, what additional properties did you add to the manifest?

[arpitvipulsharma]

Hi @mkjelland ! Yes I am using manifest file which is in this repo. I have defined db host and user as follows:

applications:
  - name: gcp-service-broker
    product_version: "3.1.2"
    metadata_version: "1.0"
    label: 'GCP Service Broker'
    description: Easily configure and grant access to GCP products
    memory: 1G
    buildpack: go_buildpack
    env:
      DB_HOST: Here I have defined my db host ip
      DB_USERNAME: Here I have defined my db user
      DB_PASSWORD: Here I have defined my db user password
      SERVICES: '[
        {
          "id": "b9e4332e-b42b-4680-bda5-ea1506797474",
          "description": "A Powerful, Simple and Cost Effective Object Storage Service",
          "name": "google-storage",
          "bindable": true,
          "plan_updateable": false,
          "metadata": {
            "displayName": "Google Cloud Storage",
            "longDescription": "A Powerful, Simple and Cost Effective Object Storage Service",
            "documentationUrl": "https://cloud.google.com/storage/docs/overview",
            "supportUrl": "https://cloud.google.com/support/",
            "imageUrl": "https://cloud.google.com/_static/images/cloud/products/logos/svg/storage.svg"
          },
....

[arpitvipulsharma]

Hi @mkjelland ! Any suggestion regarding this?

applications:
  - name: gcp-service-broker
    product_version: "3.1.2"
    metadata_version: "1.0"
    label: 'GCP Service Broker'
    description: Easily configure and grant access to GCP products
    memory: 1G
    buildpack: go_buildpack
    env:
        DB_HOST:  ..........................
        DB_USERNAME: ...............
        DB_PASSWORD: ....................
        DB_PORT: '3306'
        SECURITY_USER_NAME: .....................
        SECURITY_USER_PASSWORD: .........................
        SERVICES: '[
        {
          "id": "b9e4332e-b42b-4680-bda5-ea1506797474",
          "description": "A Powerful, Simple and Cost Effective Object Storage Service",
          "name": "google-storage",
          "bindable": true,

[arpitvipulsharma]

Have found a dirty workaround to circumvent this issue. I observed that under migrations.go, the actual create table sql command for cloud_operations exists. So, I replaced the CREATE TABLE with CREATE TABLE IF NOT EXISTS for cloud_operations table in the migrations.go file and pushed the changes to cf. After this, gcp-service-broker app got deployed successfully. However, I am still unsure why does the migrations code is reporting an error even if the create table command is successful. I verified this by manually dropping all the tables from the servicebroker database and observed that the original code did create the table. Will further investigate this issue and share any findings.

[mkjelland]

Hey @arpitvipulsharma! Those fields look good, but how are you setting the ROOT_SERVICE_ACCOUNT_JSON field?

[arpitvipulsharma]

name: gcp-service-broker
    product_version: "3.1.2"
    metadata_version: "1.0"
    label: 'GCP Service Broker'
    description: Easily configure and grant access to GCP products
    memory: 1G
    buildpack: go_buildpack
    env:
      DB_HOST: ............
      DB_USERNAME: ...................
      DB_PASSWORD: ....................
      DB_PORT: '3306'
      SECURITY_USER_NAME: servicebroker
      SECURITY_USER_PASSWORD: ...................
      ROOT_SERVICE_ACCOUNT_JSON: '{
                                    "type": "service_account",
                                    "project_id": ".............",
                                    "private_key_id": "......................................................",
                                    "private_key": "-----BEGIN PRIVATE KEY-----\nMIIE...............................................................+..................................................OgcZHDQtPgfadfafaUKOi3qnpBYEvDvfadfadfa2Wi............................................................................................................DPwZu\nOTkK0BRgh7iy----END PRIVATE KEY-----\n",
                                    "client_email": "servicebroker@.............................",
                                    "client_id": "....................................",
                                    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
                                    "token_uri": "https://accounts.google.com/o/oauth2/token",
                                    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
                                    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/servicebroker%40cf-gcloud.iam.gserviceaccount.com"
                                  }'

[mkjelland]

Can you try putting the ROOT_SERVICE_ACCOUNT_JSON object all on one line?

[babuganesh1982]

Hi @mkjelland
I tried it this morning and looks like there are 2 parts to this issue -

• like you mentioned, putting the ROOT_SERVICE_ACCOUNT_JSON in one line with proper formatting gets rid of the error [panic: Error migrating database: json: cannot unmarshal array into Go value of type map[string]string]

• where as the error which says [panic: Error migrating database: Error 1050: Table 'cloud_operations' already exists] needs a fix, I tried replacing the CREATE TABLE with CREATE TABLE IF NOT EXISTS for cloud_operations table as suggested by arpitvipulsharma and the application was successfully deployed

Thank you
Best Regards
Babu