Can Cloud SQL be exported synchronously? Or how can failures be caught and retried if asynchronous?

Question

Short Summary

Cloud SQL exports can sometimes fail.
Can I make the export requests behave synchronously so the failures are easy to retry?
Or is there a good way to retry exports in an asynchronous approach?

Full Description

I'm migrating code from App Script into Node.js and I've encountered an issue. The code exports the results of a Cloud SQL query into a CSV file. Cloud SQL can't do parallel exports so this sometimes results in the following error being thrown:

Error: Operation failed because another operation was already in progress.

In App Script the approach was to wait for 6 seconds then try again, with a limit of 10 retries.

Doing this was simple because the code behaved synchronously:

for(exportAttempt=1; exportAttempt<=10; exportAttempt++) {
    Utilities.sleep(6000); 
    // Use the url fetch service to issue the https request and capture the response
    var response = UrlFetchApp.fetch(api, parameters);
    response = JSON.parse(response);

    if(exportAttempt == 10) {
        throw('Exceeded the limit of 10 failed export requests to REST API.');
    } 
    if(response.status != undefined) {
        _log_('DEBUG', 'Export attempt ' + exportAttempt + ' successful.');
        exportAttempt=10;
    }
    if(response.error != undefined) {
        _log_('DEBUG', 'Attempt number ' + exportAttempt + ' errored. ' + JSON.stringify(response));
    }
}

Replicating the export functionality in Node.js was possible with the following code but it behaves asynchronously:

var {google} = require('googleapis');
var sqladmin = google.sqladmin('v1beta4');

var uri = 'gs://' + csBucket + '/' + csFileName;

google.auth.getApplicationDefault(function(err, authClient) {
    if (err) {
        _log_('ERROR', 'Authentication failed because of ' + err);
        return false;
    }

    if (authClient.createScopedRequired && authClient.createScopedRequired()) {
        var scopes = [
            'https://www.googleapis.com/auth/cloud-platform',
            'https://www.googleapis.com/auth/sqlservice.admin'
        ];    
        authClient = authClient.createScoped(scopes);
    }

    var request = {
        project: projectId,
        instance: sqlInstance, 

        resource: {
            exportContext: {
                kind: "sql#exportContext",
                fileType: fileType,
                uri: uri,
                databases: [sqlSchema], 
                csvExportOptions: {
                    selectQuery: exportSQL 
                }
            }
        },
        auth: authClient
    };

    sqladmin.instances.export(request, function(err, result) {
        if (err) {
            //The problem with the exception is that it stops the Cloud Function.
            //It isn't thrown up to the parent/calling function to be used for a retry. 
            _log_('ERROR', 'Export failed because of ' + err);
            throw(err)                
        } else {
            _log_('DEBUG', 'result');
            _log_('DEBUG', result);
        }
    });
});

This means the error causes an immediate failure. I can't find a way to throw the error up to the parent/calling function to manage with a retry.

Possible duplicate of [JavaScript ES6 promise for loop](https://stackoverflow.com/questions/40328932/javascript-es6-promise-for-loop) — ponury-kostek, May 15 '19 at 10:24
It looks like you've found a solution - can you please add it below as an answer and mark it as accepted? — kurtisvg, May 15 '19 at 23:23

score 0 · Accepted Answer · answered May 16 '19 at 07:46

Solution

I tried a recursive approach in the end. I originally didn't think this was possible because the compiler complained about the use of async and await but I just needed to find the right place to put them.

sqladmin.instances.export(request, async function(err, result) {
    if (err) {
        if(attempt == 10) throw('Retries exceeded');

        _log_('ERROR', 'Export error.  Retry attempt ' + attempt);
        await _sleep_(6000);                
        attempt++;
        await exportCSV(<all>,<the>,<same>,<parameters>,<passed>,<in>,attempt);
    } else {
        _log_('DEBUG', 'result');
        _log_('DEBUG', result);
    }

...

function _sleep_(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
}

Hi, What is exportCSV? is it sqladmin.instances.export? – Vova Mar 17 '21 at 14:10 — Vova, Mar 17 '21 at 14:10

Can Cloud SQL be exported synchronously? Or how can failures be caught and retried if asynchronous?

Short Summary

Full Description

1 Answers1

Solution