Microsoft.Azure.Cosmos.Table LocationMode.SecondaryOnly RA-GRS Exception This operation can only be executed against the primary storage location

Question

I was having as tough time getting Microsoft.Azure.Cosmos.Table to automatically initialise the SecondaryUri when parsing a connection string that used a SAS token.

So I ended up explicitly specifying the TableSecondaryEndpoint in the connection string, that works but I'm unable to query the secondary because the SDK throws an Exception before even attempting the request.

In my testing, I have identified that this is a regression not present in Microsoft.WindowsAzure.Storage.Table 8.7.0 (The basis for Microsoft.Azure.Cosmos.Table 1.0.6)

Expert opinions very welcome that this point. Thank you.

Project code for this Exception here (also copied below): https://github.com/golfalot/SOshowAzureTableBug

Side issue detailing the SecondaryUri initialisation problem raised here: https://github.com/Azure/azure-cosmos-table-dotnet/issues/36

using System;
using System.Collections.Generic;

using LEGACY_STORAGE = Microsoft.WindowsAzure.Storage;
using LEGACY_RETRY = Microsoft.WindowsAzure.Storage.RetryPolicies;
using LEGACY_TABLE = Microsoft.WindowsAzure.Storage.Table; //8.7.0 because this is the base for 1.0.6

using NEWEST_TABLE = Microsoft.Azure.Cosmos.Table; // version 1.0.6
using Microsoft.Azure.Cosmos.Table; // had to add this to get access CreateCloudTableClient extension method

using System.Diagnostics;

namespace SOshowAzureTableBug
{
    class Program
    {
        // the SAS token is immaterial in reproducing the problem
        const string connectionTableSAS = "TableSecondaryEndpoint=http://127.0.0.1:10002/devstoreaccount1-secondary;TableEndpoint=http://127.0.0.1:10002/devstoreaccount1;SharedAccessSignature=immaterial";
        static void Main(string[] args)
        {

            /* Legacy Table SDK */
            var storageAccountLegacy = LEGACY_STORAGE.CloudStorageAccount.Parse(connectionTableSAS);
            var tableClientLegacy = storageAccountLegacy.CreateCloudTableClient();
            Debug.Assert(tableClientLegacy.StorageUri.SecondaryUri != null); // demonstrate SecondaryUri initialised

            var tableRequestOptionsLegacy = new LEGACY_TABLE.TableRequestOptions () { LocationMode = LEGACY_RETRY.LocationMode.SecondaryOnly };
            tableClientLegacy.DefaultRequestOptions = tableRequestOptionsLegacy;

            var tableLegacy = tableClientLegacy.GetTableReference("foo"); // don't need table to exist to show the issue
            var retrieveOperation = LEGACY_TABLE.TableOperation.Retrieve(string.Empty, string.Empty, new List<string>() { "bar" });

            var tableResult = tableLegacy.Execute(retrieveOperation);
            Console.WriteLine("Legacy PASS");


            /* Newset Table SDK */
            var storageAccountNewest = NEWEST_TABLE.CloudStorageAccount.Parse(connectionTableSAS);
            var tableClientNewest = storageAccountNewest.CreateCloudTableClient(new TableClientConfiguration());
            Debug.Assert(tableClientNewest.StorageUri.SecondaryUri != null); // demonstrate SecondaryUri initialised

            var tableRequestOptionsNewest = new NEWEST_TABLE.TableRequestOptions() { LocationMode = NEWEST_TABLE.LocationMode.SecondaryOnly };
            tableClientNewest.DefaultRequestOptions = tableRequestOptionsNewest;

            var tableNewset = tableClientNewest.GetTableReference("foo"); // don't need table to exist to show the issue
            var retrieveOperationNewset = NEWEST_TABLE.TableOperation.Retrieve(string.Empty, string.Empty, new List<string>() { "bar" });

            /* throws Microsoft.Azure.Cosmos.Table.StorageException
             * Exception thrown while initializing request: This operation can only be executed against the primary storage location
             */
            var tableResultNewset = tableNewset.Execute(retrieveOperationNewset);

            Console.WriteLine("Press any key to exit");
            Console.Read();
        }
    }
}

Answer 1

I believe you've encountered a bug with the SDK.

When I try the following code, I get the same error as you:

        var account = CloudStorageAccount.Parse(connectionString);

        var requestOptions = new TableRequestOptions()
        {
            LocationMode = LocationMode.SecondaryOnly
        };
        var client = account.CreateCloudTableClient();
        client.DefaultRequestOptions = requestOptions;
        var table = client.GetTableReference("myTable");
        var op = TableOperation.Retrieve("", "");
        var result1 = table.Execute(op);

I decompiled the library code and found the culprit source code:

if (commandLocationMode == CommandLocationMode.PrimaryOnly)
                {
                    if (restCMD.LocationMode == LocationMode.SecondaryOnly)
                    {
                        throw new InvalidOperationException("This operation can only be executed against the primary storage location.");//This is the error that gets thrown.
                    }
                    Logger.LogInformational(executionState.OperationContext, "This operation can only be executed against the primary storage location.", Array.Empty<object>());
                    executionState.CurrentLocation = StorageLocation.Primary;
                    restCMD.LocationMode = LocationMode.PrimaryOnly;
                }

However, if I don't set DefaultRequestOptions at client level and specify it below in Execute method, I don't get the error but then it's because the primary endpoint is hit instead of secondary (I checked that in Fiddler).

        var account = CloudStorageAccount.Parse(connectionString);

        var requestOptions = new TableRequestOptions()
        {
            LocationMode = LocationMode.SecondaryOnly
        };
        var client = account.CreateCloudTableClient();
        var table = client.GetTableReference("myTable");
        var op = TableOperation.Retrieve("", "");
        var result1 = table.Execute(op, requestOptions);

Workaround

If your objective is to query entities from secondary location, then you can use ExecuteQuery method on CloudTable like shown below. This works (Again, I checked in Fiddler).

        var account = CloudStorageAccount.Parse(connectionString);

        var requestOptions = new TableRequestOptions()
        {
            LocationMode = LocationMode.SecondaryOnly
        };
        var client = account.CreateCloudTableClient();
        client.DefaultRequestOptions = requestOptions;
        var table = client.GetTableReference("myTable");
        TableQuery query = new TableQuery();
        var result = table.ExecuteQuery(query).ToList();