CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-functionsgoogle-cloud-storagegoogle-cloud-sql
explorer
explorer

Reputation: 73

Cloud Function to import data into CloudSQL from cloud storage bucket but getting already schema exist error

I'm trying to import data into CloudSQL instance from cloud storage bucket using cloud function.

How can i delete schema's before importing the data using a single cloud function?

I am using Node.js in cloud function.

error:

error: exit status 3 stdout(capped at 100k bytes): SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET stderr: ERROR: schema "<  >" already exists

https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/import in below code where do i need to put delete all existing schema's apart from public schema?

Entry point : importDatabase

index.js

    const {google} = require('googleapis');
    const {auth} = require("google-auth-library");
    var sqlAdmin = google.sqladmin('v1beta4');
    
    exports.importDatabase = (_req, res) => {
    async function doIt() {
    const authRes = await auth.getApplicationDefault();
    let authClient = authRes.credential;
      var request = {

        project: 'my-project',  // TODO: Update placeholder value.
    

        instance: 'my-instance',  // TODO: Update placeholder value.
    
        resource: {
          importContext: {

          kind: "sql#importContext",
          fileType: "SQL", // CSV
   
          uri: <bucket path>,

          database: <database-name>
          // Options for importing data as SQL statements.
          // sqlimportOptions: {
          //   /**
        },
    
        auth: authClient,
      };
    
      sqladmin.instances.import(request, function(err, result) {
      if (err) {
        console.log(err);
      } else {
        console.log(result);
      }
      res.status(200).send("Command completed", err, result); 
    });
  }

      doIt();
    
    };

package.json

{
  "name": "import-database",
  "version": "0.0.1",
  "dependencies": {
    "googleapis": "^39.2.0",
    "google-auth-library": "3.1.2"
  }
}

Upvotes: 1

Views: 1297

Answers (1)

Vaidehi Jamankar
Vaidehi Jamankar

Reputation: 1356

The error looks to be occurring due to a previous aborted import managed to transfer the "schema_name" schema, and then this subsequent import was done without first re-initializing the DB,check helpful document on Cloud SQL import One way to prevent this issue is to change the create statements in the SQL file from:

CREATE SCHEMA schema_name; to CREATE SCHEMA IF NOT EXISTS schema_name;

As far the removing of currently created schema is considered by default, only user or service accounts with the Cloud SQL Admin (roles/cloudsql.admin) or Owner (roles/owner) role have the permission to delete a Cloud SQL instance,please check the helpful document on cloudsql.instances.delete to help you understand the next steps.You can also define an IAM custom role for the user or service account that includes the cloudsql.instances.delete permission. This permission is supported in IAM custom roles.

As a best practice for import export operations, we recommend that you adopt the principle of least privilege, which in this case would mean creating a custom role and adding that specific permission and assigning it to your service account. Alternatively, the service account could be given the “Cloud SQL Admin” role, or the “Cloud Composer API Service Agent” role, which include this permission, and would therefore allow you to execute this command.

NOTE:It is recommended and advised to revalidate any delete actions performed as this may lead to loss of useful data.

Upvotes: 0

Related Questions