public void TestShardConnectionOpenException() { try { Action executeOnOpen = () => { throw new InsufficientMemoryException(); }; var shardConnections = CreateConnections(10, executeOnOpen); var mockCmd = new MockSqlCommand(); mockCmd.CommandText = "Select 1"; using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 100)) { cmd.CommandText = "select 1"; cmd.ExecuteReader(); } } } catch (Exception ex) { if (ex is MultiShardAggregateException) { var maex = (MultiShardAggregateException)ex; Logger.Log("Exception message: {0}.\n Exception tostring: {1}", ex.Message, ex.ToString()); throw (maex.InnerException).InnerException; } throw; } }
private async Task <MultiShardDataReader> ExecAsync(MultiShardConnection conn, MultiShardCommand cmd) { cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; return(await cmd.ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None)); }
public void TestFailedCommandWithConnectionCloseCmdBehavior() { Parallel.For(0, 100, i => { try { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select * from table_does_not_exist"; cmd.CommandType = CommandType.Text; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { } } } } } catch (Exception ex) { Console.WriteLine("Encountered exception: {0} in iteration: {1}", ex.ToString(), i); } finally { Console.WriteLine("Completed execution of iteration: {0}", i); } }); }
public void TestCreateConnectionWithNoShards() { using (MultiShardConnection conn = new MultiShardConnection(new Shard[] { }, String.Empty)) { Assert.Fail("Should have failed in the MultiShardConnection c-tor."); } }
public void TestShardCommandSucceedHandler() { var shardConnections = CreateConnections(10, () => { }); ConcurrentDictionary <ShardLocation, bool> passedLocations = new ConcurrentDictionary <ShardLocation, bool>(); using (var conn = new MultiShardConnection(shardConnections)) { Func <CancellationToken, MockSqlCommand, DbDataReader> executeReaderFunc = (token, cmd) => { Thread.Sleep(TimeSpan.FromSeconds(2)); return(new MockSqlDataReader()); }; MockSqlCommand mockCmd = new MockSqlCommand(); mockCmd.ExecuteReaderFunc = executeReaderFunc; mockCmd.CommandText = "Select 1"; using (MultiShardCommand cmd = MultiShardCommand.Create(conn, mockCmd, 10)) { cmd.ShardExecutionSucceeded += new EventHandler <ShardExecutionEventArgs>((obj, eventArgs) => { Assert.IsTrue(shardConnections.Select(x => x.Item1).Contains(eventArgs.ShardLocation), "The ShardLocation passed to the event handler does not exist in the set of passed in ShardLocations"); passedLocations[eventArgs.ShardLocation] = true; }); CommandBehavior behavior = CommandBehavior.Default; cmd.ExecuteReader(behavior); } } Assert.AreEqual(shardConnections.Count, passedLocations.Count, "Not every ShardLocation had its corresponding event handler invoked."); }
public void TestSelectNoRows(string commandText, MultiShardExecutionPolicy policy) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.ExecutionPolicy = policy; // Read first using (MultiShardDataReader sdr = cmd.ExecuteReader()) { Assert.AreEqual(0, sdr.MultiShardExceptions.Count); while (sdr.Read()) { Assert.Fail("Should not have gotten any records."); } Assert.IsFalse(sdr.HasRows); } // HasRows first using (MultiShardDataReader sdr = cmd.ExecuteReader()) { Assert.AreEqual(0, sdr.MultiShardExceptions.Count); Assert.IsFalse(sdr.HasRows); while (sdr.Read()) { Assert.Fail("Should not have gotten any records."); } } } } }
public void TestQueryShardsAsync() { // Create new sharded connection so we can test the OpenAsync call as well. // using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable"; cmd.CommandType = CommandType.Text; using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result) { int recordsRetrieved = 0; while (sdr.Read()) { recordsRetrieved++; var dbNameField = sdr.GetString(0); var testIntField = sdr.GetFieldValue <int>(1); var testBigIntField = sdr.GetFieldValue <Int64>(2); Logger.Log("RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, RecordCount: {3}", dbNameField, testIntField, testBigIntField, recordsRetrieved); } Assert.AreEqual(recordsRetrieved, 9); } } } }
public void TestQueryShardsInvalidConnectionSync() { var badShard = new ShardLocation("badLocation", "badDatabase"); var bldr = new SqlConnectionStringBuilder(); bldr.DataSource = badShard.DataSource; bldr.InitialCatalog = badShard.Database; var badConn = new SqlConnection(bldr.ConnectionString); try { using (var conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { conn.GetShardConnections().Add(new Tuple <ShardLocation, DbConnection>(badShard, badConn)); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "select 1"; cmd.ExecuteReader(); } } } catch (Exception ex) { if (ex is MultiShardAggregateException) { var maex = (MultiShardAggregateException)ex; Logger.Log("Exception encountered: " + maex.ToString()); throw ((MultiShardException)(maex.InnerException)).InnerException; } throw; } }
public MultiShardAggregateException TestSelectFailure(string commandText, MultiShardExecutionPolicy policy) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.ExecutionPolicy = policy; // ExecuteReader should fail MultiShardAggregateException aggregateException = AssertExtensions.AssertThrows <MultiShardAggregateException>(() => cmd.ExecuteReader()); // Sanity check the exceptions are the correct type foreach (Exception e in aggregateException.InnerExceptions) { Assert.IsInstanceOfType(e, typeof(MultiShardException)); Assert.IsInstanceOfType(e.InnerException, typeof(SqlException)); } // Return the exception so that the caller can do additional validation return(aggregateException); } } }
public void TestShardCommandCancellationHandler() { // Create connections to a few shards var shardConnections = CreateConnections(10, () => { }); var mockCmd = new MockSqlCommand(); var cmdStartEvent = new ManualResetEvent(false); mockCmd.ExecuteReaderFunc = (token, cmd) => { while (true) { if (token == null) { break; } token.ThrowIfCancellationRequested(); Thread.Sleep(500); cmdStartEvent.Set(); } return(new MockSqlDataReader()); }; mockCmd.CommandText = "select 1"; ConcurrentDictionary <ShardLocation, bool> passedLocations = new ConcurrentDictionary <ShardLocation, bool>(); using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 300)) { cmd.ShardExecutionCanceled += new EventHandler <ShardExecutionEventArgs>((obj, eventArgs) => { Assert.IsTrue(shardConnections.Select(x => x.Item1).Contains(eventArgs.ShardLocation), "The ShardLocation passed to the event handler does not exist in the set of passed in ShardLocations"); passedLocations[eventArgs.ShardLocation] = true; }); try { // start the Cancel on a separate thread Task executeTask = Task.Run(() => { cmdStartEvent.WaitOne(); cmd.Cancel(); }); cmd.ExecuteReader(); executeTask.Wait(); Assert.Fail("We should always be throwing an exception."); } catch (Exception ex) { Assert.IsTrue(ex is OperationCanceledException, "OperationCanceledException expected. Found {0}!", ex.ToString()); } } } Assert.AreEqual(shardConnections.Count, passedLocations.Count, "Not every ShardLocation had its corresponding event handler invoked."); }
/// <summary> /// Executes the SQL command and returns the output in text format. /// </summary> private static string ExecuteCommand(MultiShardConnection conn, string commandText) { try { StringBuilder output = new StringBuilder(); output.AppendLine(); int rowsAffected = 0; using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.CommandTimeout = s_commandLine.QueryTimeout; cmd.CommandTimeoutPerShard = s_commandLine.QueryTimeout; // Execute command and time with a stopwatch Stopwatch stopwatch = Stopwatch.StartNew(); cmd.ExecutionPolicy = s_commandLine.ExecutionPolicy; cmd.ExecutionOptions = s_commandLine.ExecutionOptions; using (MultiShardDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) { stopwatch.Stop(); // Get column names IEnumerable <string> columnNames = GetColumnNames(reader).ToArray(); // Create table formatter TableFormatter tableFormatter = new TableFormatter(columnNames.ToArray()); // Read results from db while (reader.Read()) { rowsAffected++; // Add the row to the table formatter object[] values = new object[reader.FieldCount]; reader.GetValues(values); tableFormatter.AddRow(values); } // Write formatter output output.AppendLine(tableFormatter.ToString()); } output.AppendLine(); output.AppendFormat("({0} rows affected - {1:hh}:{1:mm}:{1:ss} elapsed)", rowsAffected, stopwatch.Elapsed); output.AppendLine(); } return(output.ToString()); } catch (MultiShardAggregateException e) { return(e.ToString()); } }
public void TestSchemaMismatchErrorPropagation() { // First we need to alter the schema on one of the shards - we'll choose the last one. // string origColName = "Test_bigint_Field"; string newColName = "ModifiedName"; MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, origColName, newColName); // Then create new sharded connection so we can test the error handling logic. // We'll wrap this all in a try-catch-finally block so that we can change the schema back // to what the other tests will expect it to be in the finally. // try { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { // Need to do a SELECT * in order to get the column name error as a schema mismatcherror. If we name it explicitly // we will get a command execution error instead. // cmd.CommandText = "SELECT * FROM ConsistentShardedTable"; cmd.CommandType = CommandType.Text; using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result) { // The number of errors we have depends on which shard executed first. // So, we know it should be 1 OR 2. // Assert.IsTrue( ((sdr.MultiShardExceptions.Count == 1) || (sdr.MultiShardExceptions.Count == 2)), string.Format("Expected 1 or 2 execution erros, but saw {0}", sdr.MultiShardExceptions.Count)); int recordsRetrieved = 0; while (sdr.Read()) { recordsRetrieved++; var dbNameField = sdr.GetString(0); } // We should see 9 records less 3 for each one that had a schema error. int expectedRecords = ((9 - (3 * sdr.MultiShardExceptions.Count))); Assert.AreEqual(recordsRetrieved, expectedRecords); } } } } finally { MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, newColName, origColName); } }
// GET: Captain public ActionResult Index() { Dictionary <int, int> votes = new Dictionary <int, int>(); //Perform a multi shard query to tally all the votes // Get the shards to connect to var shards = _shardMap.GetShards(); // Create the multi-shard connection using (var conn = new MultiShardConnection(shards, Util.SqlHelper.GetCredentialsConnectionString())) { // Create a simple command using (MultiShardCommand cmd = conn.CreateCommand()) { // Because this query is grouped by CustomerID, which is sharded, // we will not get duplicate rows. cmd.CommandText = @"SELECT Captain, COUNT(*) AS Votes FROM [dbo].[Votes] GROUP BY [Captain]"; // Allow for partial results in case some shards do not respond in time cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; // Allow the entire command to take up to 30 seconds cmd.CommandTimeout = 30; // Execute the command. // We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires. using (MultiShardDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { votes.Add(reader.GetInt32(0), reader.GetInt32(1)); } } } } var vm = new ViewModels.Captain.IndexViewModel(); foreach (var captain in _captainContext.Captains) { int voteCount = 0; votes.TryGetValue(captain.ID, out voteCount); vm.Captains.Add(new ViewModels.Captain.IndexViewModelCaptain() { ID = captain.ID, Name = captain.Name, Votes = voteCount }); } return(View(vm)); }
public void TestMultiShardConnectionConstructorEvaluatesShards() { MultiShardTestUtils.DropAndCreateDatabases(); ShardMap shardMap = MultiShardTestUtils.CreateAndGetTestShardMap(); List <Shard> shards = shardMap.GetShards().ToList(); MultiShardConnection conn = new MultiShardConnection(shards.ToConsumable(), dummyConnectionString); AssertExtensions.AssertSequenceEqual(shards, conn.Shards); }
public static void Main(string[] args) { try { // Parse command line arguments s_commandLine = new CommandLine(args); if (!s_commandLine.IsValid) { s_commandLine.WriteUsage(); return; } // Get Shard Map Manager ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( GetConnectionString(), ShardMapManagerLoadPolicy.Eager); Console.WriteLine("Connected to Shard Map Manager"); // Get Shard Map ShardMap map = smm.GetShardMap(s_commandLine.ShardMap); Console.WriteLine("Found {0} shards", map.GetShards().Count()); // Create connection string for MultiShardConnection string connectionString = GetCredentialsConnectionString(); // REPL Console.WriteLine(); while (true) { // Read command from console string commandText = GetCommand(); if (commandText == null) { // Exit requested break; } // Evaluate command string output; using (MultiShardConnection conn = new MultiShardConnection(map.GetShards(), connectionString)) { output = ExecuteCommand(conn, commandText); } // Print output Console.WriteLine(output); } } catch (Exception e) { // Print exception and exit Console.WriteLine(e); return; } }
// POST api/CustomLogin public HttpResponseMessage Post(LoginRequest loginRequest) { Guid shardKey; // SEND A QUERY TO ALL SHARD TO DETECT OUR SHARD!!!! // SAVE companiesId to shardKey! using (MultiShardConnection conn = new MultiShardConnection(WebApiConfig.ShardingObj.ShardMap.GetShards(), WebApiConfig.ShardingObj.connstring)) { using (MultiShardCommand cmd = conn.CreateCommand()) { // CHECK SCHEMA // SQL INJECTION SECURITY ISSUE cmd.CommandText = "SELECT CompaniesID FROM [mpbdm].[Accounts] JOIN [mpbdm].[Users] ON [mpbdm].[Users].Id = [mpbdm].[Accounts].User_Id WHERE email='" + loginRequest.email + "'"; cmd.CommandType = CommandType.Text; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; // Async using (MultiShardDataReader sdr = cmd.ExecuteReader()) { bool res = sdr.Read(); if (res != false) { shardKey = new Guid(sdr.GetString(0)); } else { return(this.Request.CreateResponse(HttpStatusCode.Unauthorized, "Account doesn't exist!")); } } } } // Connect with entity framework to the specific shard mpbdmContext <Guid> context = new mpbdmContext <Guid>(WebApiConfig.ShardingObj.ShardMap, shardKey, WebApiConfig.ShardingObj.connstring); Account account = context.Accounts.Include("User").Where(a => a.User.Email == loginRequest.email).SingleOrDefault(); if (account != null) { byte[] incoming = CustomLoginProviderUtils.hash(loginRequest.password, account.Salt); if (CustomLoginProviderUtils.slowEquals(incoming, account.SaltedAndHashedPassword)) { ClaimsIdentity claimsIdentity = new ClaimsIdentity(); claimsIdentity.AddClaim(new Claim(ClaimTypes.NameIdentifier, account.User.Email)); // Custom Claim must be added to CustomLoginProvider too !! claimsIdentity.AddClaim(new Claim("shardKey", account.User.CompaniesID)); var customLoginProvider = new CustomLoginProvider(handler); LoginResult loginResult = customLoginProvider.CreateLoginResult(claimsIdentity, Services.Settings.MasterKey); MobileLoginResult res = new MobileLoginResult(account, loginResult); return(this.Request.CreateResponse(HttpStatusCode.OK, res)); } } return(this.Request.CreateResponse(HttpStatusCode.Unauthorized, "Invalid username or password")); }
public void TestShardCommandRetryExhaustion() { var retryPolicy = new RetryPolicy(2, TimeSpan.FromMilliseconds(100), TimeSpan.FromSeconds(10), TimeSpan.FromMilliseconds(100)); var shardConnections = new List <Tuple <ShardLocation, DbConnection> >(); // Create ten mocked connections, half of them will throw exceptions on Open for (int i = 0; i < 10; i++) { string database = string.Format("Shard{0}", i); int j = i; Action executeOnOpen = () => { if (j < 5) { throw new TimeoutException(); } }; var mockCon = new MockSqlConnection(database, executeOnOpen); shardConnections.Add(new Tuple <ShardLocation, DbConnection>(new ShardLocation("test", database), mockCon)); } var mockCmd = new MockSqlCommand(); mockCmd.ExecuteReaderFunc = (t, c) => new MockSqlDataReader(); mockCmd.CommandText = "select 1"; using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 300)) { cmd.ExecutionOptions = MultiShardExecutionOptions.None; cmd.RetryPolicy = retryPolicy; cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; MultiShardDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default); // Validate the right exception is re-thrown Assert.IsTrue(rdr.MultiShardExceptions.Count == 5, "Expected MultiShardExceptions!"); foreach (MultiShardException ex in rdr.MultiShardExceptions) { Assert.IsTrue(ex.InnerException is TimeoutException, "Expected TimeoutException!"); } // Validate that the connections for the faulted readers are closed for (int i = 0; i < 5; i++) { Assert.IsTrue(shardConnections[i].Item2.State == ConnectionState.Closed, "Expected Connection to be Closed!"); } } } }
public void TestMultiShardConnectionConstructorEvaluatesShardLocations() { List <ShardLocation> shardLocations = new List <ShardLocation> { new ShardLocation("server1", "db1"), new ShardLocation("server2", "db2"), new ShardLocation("server3", "db3") }; MultiShardConnection conn = new MultiShardConnection(shardLocations.ToConsumable(), dummyConnectionString); AssertExtensions.AssertSequenceEqual(shardLocations, conn.ShardLocations); }
public void TestShardCommandBehavior() { var shardConnections = CreateConnections(10, () => { }); using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = conn.CreateCommand()) { CommandBehavior behavior = CommandBehavior.SingleResult; behavior &= CommandBehavior.SingleRow; cmd.ExecuteReader(behavior); } } }
public void TestShardCommandRetryBasic() { var retryPolicy = new RetryPolicy(4, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(10), TimeSpan.FromMilliseconds(100)); var openRetryCounts = new int[10]; var shardConnections = new List <Tuple <ShardLocation, DbConnection> >(); // Create ten mocked connections, each will retry retryPolicy.RetryCount - 1 times, // and keep it's own actual retry count in one of the elements of openRetryCounts for (int i = 0; i < 10; i++) { string database = string.Format("Shard{0}", i); // We want to close on the value of i int j = i; Action executeOnOpen = () => { if (openRetryCounts[j] < (retryPolicy.RetryCount - 1)) { Logger.Log("Current retry count for database: {0} is {1}", database, openRetryCounts[j]); openRetryCounts[j]++; throw new TimeoutException(); } }; var mockCon = new MockSqlConnection(database, executeOnOpen); shardConnections.Add(new Tuple <ShardLocation, DbConnection>(new ShardLocation("test", database), mockCon)); } var mockCmd = new MockSqlCommand(); mockCmd.ExecuteReaderFunc = (t, c) => new MockSqlDataReader(); mockCmd.CommandText = "select 1"; using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 300)) { cmd.ExecutionOptions = MultiShardExecutionOptions.None; cmd.RetryPolicy = retryPolicy; cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; cmd.ExecuteReader(CommandBehavior.Default); } } for (int i = 0; i < openRetryCounts.Length; i++) { Assert.AreEqual(retryPolicy.RetryCount - 1, openRetryCounts[i]); } }
public void TestShardCommandCancellation() { // Create connections to a few shards var shardConnections = CreateConnections(10, () => { }); var mockCmd = new MockSqlCommand(); var cmdStartEvent = new ManualResetEvent(false); mockCmd.ExecuteReaderFunc = (token, cmd) => { while (true) { if (token == null) { break; } token.ThrowIfCancellationRequested(); Thread.Sleep(500); cmdStartEvent.Set(); } return(new MockSqlDataReader()); }; mockCmd.CommandText = "select 1"; using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 300)) { try { // start the Cancel on a separate thread Task executeTask = Task.Run(() => { cmdStartEvent.WaitOne(); cmd.Cancel(); }); cmd.ExecuteReader(); executeTask.Wait(); Assert.Fail("We should always be throwing an exception."); } catch (Exception ex) { Assert.IsTrue(ex is OperationCanceledException, "OperationCanceledException expected. Found {0}!", ex.ToString()); } } } }
public DataSet MultiShardQuery(string commandText) { DataSet _ds = new DataSet(); try { RetryPolicy.DefaultExponential.ExecuteAction(() => { using (MultiShardCommand cmd = new MultiShardConnection(lsm.GetShards(), shardConnectionString).CreateCommand()) { cmd.CommandText = commandText; cmd.CommandType = CommandType.Text; cmd.ExecutionOptions = MultiShardExecutionOptions.None; //04/23/2015 by Mark Berman - switched to Partial results versus CompleteResults as timeout exception was being thrown cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { if (sdr.Read()) { // the multi-shard query does not return a dataset or datarow. // we have to manually re-create a table structure, then fill it with data. object[] sqlValues = new object[sdr.FieldCount]; sdr.GetSqlValues(sqlValues); DataTable dtValues = new DataTable(); foreach (var column in sqlValues) { dtValues.Columns.Add(new DataColumn { DataType = column.GetType() }); } _ds.Tables.Add(dtValues); _ds.Tables[0].Rows.Add(sqlValues); while (sdr.Read()) { sdr.GetSqlValues(sqlValues); _ds.Tables[0].Rows.Add(sqlValues); } } } } }); } catch { _ds = null; } return(_ds); }
public void TestInvalidMultiShardConnectionString() { MultiShardConnection conn; try { conn = new MultiShardConnection(_shardMap.GetShards(), connectionString: null); } catch (Exception ex) { Assert.IsTrue(ex is ArgumentNullException, "Expected ArgumentNullException!"); } try { conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardMapManagerConnectionString); } catch (Exception ex) { Assert.IsTrue(ex is ArgumentException, "Expected ArgumentException!"); } // Validate that the ApplicationName is updated properly var applicationStringBldr = new StringBuilder(); for (int i = 0; i < ApplicationNameHelper.MaxApplicationNameLength; i++) { applicationStringBldr.Append('x'); } string applicationName = applicationStringBldr.ToString(); SqlConnectionStringBuilder connStringBldr = new SqlConnectionStringBuilder(MultiShardTestUtils.ShardConnectionString); connStringBldr.ApplicationName = applicationName; conn = new MultiShardConnection(_shardMap.GetShards(), connStringBldr.ConnectionString); string updatedApplicationName = new SqlConnectionStringBuilder (conn.GetShardConnections()[0].Item2.ConnectionString).ApplicationName; Assert.IsTrue(updatedApplicationName.Length == ApplicationNameHelper.MaxApplicationNameLength && updatedApplicationName.EndsWith(MultiShardConnection.ApplicationNameSuffix), "ApplicationName not appended with {0}!", MultiShardConnection.ApplicationNameSuffix); }
public void TestSimpleSelect(MultiShardExecutionPolicy policy) { // What we're doing: // Grab all rows from each test database. // Load them into a MultiShardDataReader. // Iterate through the rows and make sure that we have 9 total. // using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable"; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; cmd.ExecutionPolicy = policy; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { int recordsRetrieved = 0; Logger.Log("Starting to get records"); while (sdr.Read()) { recordsRetrieved++; string dbNameField = sdr.GetString(0); int testIntField = sdr.GetFieldValue <int>(1); Int64 testBigIntField = sdr.GetFieldValue <Int64>(2); string shardIdPseudoColumn = sdr.GetFieldValue <string>(3); string logRecord = string.Format( "RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, shardIdPseudoColumnField: {3}, RecordCount: {4}", dbNameField, testIntField, testBigIntField, shardIdPseudoColumn, recordsRetrieved); Logger.Log(logRecord); Debug.WriteLine(logRecord); } sdr.Close(); Assert.AreEqual(recordsRetrieved, 9); } } } }
public void TestSelectNonQuery(string commandText, MultiShardExecutionPolicy policy) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.ExecutionPolicy = policy; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { Assert.AreEqual(0, sdr.MultiShardExceptions.Count); // TODO: This is a weird error message, but it's good enough for now // Fixing this will require significant refactoring of MultiShardDataReader, // we should fix it when we finish implementing async adding of child readers AssertExtensions.AssertThrows <MultiShardDataReaderClosedException>(() => sdr.Read()); } } } }
private static List <dynamic> ExecuteMultiShardQuery(ref ShardingClient sharding, string connectionString) { dynamic result = new List <dynamic>(); var multiShardConnection = new MultiShardConnection(sharding.ShardMap.GetShards(), connectionString); using (var cmd = multiShardConnection.CreateCommand()) { cmd.CommandText = @"SELECT * FROM dbo.Blogs"; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandTimeoutPerShard = 60; using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { result = reader.ToExpandoList(); } } } return(result); }
public void TestShardCommandTimeoutException() { var shardConnections = CreateConnections(10, () => { }); Func <CancellationToken, MockSqlCommand, DbDataReader> executeReaderFunc = (token, cmd) => { Thread.Sleep(TimeSpan.FromSeconds(2)); return(new MockSqlDataReader()); }; var mockCmd = new MockSqlCommand(); mockCmd.ExecuteReaderFunc = executeReaderFunc; mockCmd.CommandText = "Select 1"; using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 1)) { cmd.ExecuteReader(); } } }
public void TestSelect_PartialFailure_PartialResults() { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = GetPartialFailureQuery(); cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { // Exactly one should have failed Assert.AreEqual(1, sdr.MultiShardExceptions.Count); // We should be able to read while (sdr.Read()) { } } } } }
public void TestShardCommandFaultHandler() { var shardConnections = CreateConnections(10, () => { }); Func <CancellationToken, MockSqlCommand, DbDataReader> executeReaderFunc = (token, cmd) => { throw new InsufficientMemoryException(); }; var mockCmd = new MockSqlCommand(); mockCmd.ExecuteReaderFunc = executeReaderFunc; mockCmd.CommandText = "Select 1"; ConcurrentDictionary <ShardLocation, bool> passedLocations = new ConcurrentDictionary <ShardLocation, bool>(); using (var conn = new MultiShardConnection(shardConnections)) { using (var cmd = MultiShardCommand.Create(conn, mockCmd, 1)) { cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; cmd.CommandTimeout = 300; cmd.ShardExecutionFaulted += new EventHandler <ShardExecutionEventArgs>((obj, eventArgs) => { Assert.IsTrue(shardConnections.Select(x => x.Item1).Contains(eventArgs.ShardLocation), "The ShardLocation passed to the event handler does not exist in the set of passed in ShardLocations"); passedLocations[eventArgs.ShardLocation] = true; Assert.IsInstanceOfType(eventArgs.Exception, typeof(InsufficientMemoryException), "An incorrect exception type was passed to the event handler."); }); try { // We want to execute to completion so we get to the validation at the end of the function. cmd.ExecuteReader(); } catch { } } } Assert.AreEqual(shardConnections.Count, passedLocations.Count, "Not every ShardLocation had its corresponding event handler invoked."); }
public void TestSelectNonQuery(string commandText, MultiShardExecutionPolicy policy) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.ExecutionPolicy = policy; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { Assert.AreEqual(0, sdr.MultiShardExceptions.Count); // TODO: This is a weird error message, but it's good enough for now // Fixing this will require significant refactoring of MultiShardDataReader, // we should fix it when we finish implementing async adding of child readers AssertExtensions.AssertThrows<MultiShardDataReaderClosedException>(() => sdr.Read()); } } } }
public MultiShardAggregateException TestSelectFailure(string commandText, MultiShardExecutionPolicy policy) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = commandText; cmd.ExecutionPolicy = policy; // ExecuteReader should fail MultiShardAggregateException aggregateException = AssertExtensions.AssertThrows<MultiShardAggregateException>(() => cmd.ExecuteReader()); // Sanity check the exceptions are the correct type foreach (Exception e in aggregateException.InnerExceptions) { Assert.IsInstanceOfType(e, typeof(MultiShardException)); Assert.IsInstanceOfType(e.InnerException, typeof(SqlException)); } // Return the exception so that the caller can do additional validation return aggregateException; } } }
public void TestQueryShardsAsync() { // Create new sharded connection so we can test the OpenAsync call as well. // using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable"; cmd.CommandType = CommandType.Text; using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result) { int recordsRetrieved = 0; while (sdr.Read()) { recordsRetrieved++; var dbNameField = sdr.GetString(0); var testIntField = sdr.GetFieldValue<int>(1); var testBigIntField = sdr.GetFieldValue<Int64>(2); Logger.Log("RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, RecordCount: {3}", dbNameField, testIntField, testBigIntField, recordsRetrieved); } Assert.AreEqual(recordsRetrieved, 9); } } } }
public void TestShardResultFailures() { ProxyServer proxyServer = GetProxyServer(); try { // Start up the proxy server. Do it in a try so we can shut it down in the finally. // Also, we have to generate the proxyShardconnections *AFTER* we start up the server // so that we know what port the proxy is listening on. More on the placement // of the connection generation below. // proxyServer.Start(); // PreKillReads is the number of successful reads to perform before killing // all the connections. We start at 0 to test the no failure case as well. // for (int preKillReads = 0; preKillReads <= 10; preKillReads++) { // Additionally, since we are running inside a loop, we need to regenerate the proxy shard connections each time // so that we don't re-use dead connections. If we do that we will end up hung in the read call. // List<Tuple<ShardLocation, DbConnection>> proxyShardConnections = GetProxyShardConnections(proxyServer); using (MultiShardConnection conn = new MultiShardConnection(proxyShardConnections)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT db_name() as dbName1, REPLICATE(db_name(), 1000) as longExpr, db_name() as dbName2 FROM ConsistentShardedTable"; cmd.CommandType = CommandType.Text; cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default)) { int tuplesRead = 0; while (sdr.Read()) { // Read part of the tuple first before killing the connections and // then attempting to read the rest of the tuple. // tuplesRead++; try { // The longExpr should contain the first dbName field multiple times. // string dbName1 = sdr.GetString(0); string longExpr = sdr.GetString(1); Assert.IsTrue(longExpr.Contains(dbName1)); if (tuplesRead == preKillReads) { proxyServer.KillAllConnections(); } // The second dbName field should be the same as the first dbName field. // string dbName2 = sdr.GetString(2); Assert.AreEqual(dbName1, dbName2); // The shardId should contain both the first and the second dbName fields. // string shardId = sdr.GetString(3); Assert.IsTrue(shardId.Contains(dbName1)); Assert.IsTrue(shardId.Contains(dbName2)); } catch (Exception ex) { // We've seen some failures here due to an attempt to access a socket after it has // been disposed. The only place where we are attempting to access the socket // is in the call to proxyServer.KillAllConnections. Unfortunately, it's not clear // what is causing that problem since it only appears to repro in the lab. // I (errobins) would rather not blindly start changing things in the code (either // our code above, our exception handling code here, or the proxyServer code) until // we know which socket we are trying to access when we hit this problem. // So, the first step I will take is to pull additional exception information // so that we can see some more information about what went wrong the next time it repros. // Assert.Fail("Unexpected exception, rethrowing. Here is some info: \n Message: {0} \n Source: {1} \n StackTrace: {2}", ex.Message, ex.Source, ex.StackTrace); throw; } } Assert.IsTrue((tuplesRead <= preKillReads) || (0 == preKillReads), String.Format("Tuples read was {0}, Pre-kill reads was {1}", tuplesRead, preKillReads)); } } } } } finally { // Be sure to shut down the proxy server. // string proxyLog = proxyServer.EventLog.ToString(); Logger.Log(proxyLog); proxyServer.Stop(); } }
public void MyTestInitialize() { _shardMap = MultiShardTestUtils.CreateAndGetTestShardMap(); _shardConnection = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString); }
private async Task<MultiShardDataReader> ExecAsync(MultiShardConnection conn, MultiShardCommand cmd) { cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; return await cmd.ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None); }
public void TestQueryShardsInvalidConnectionSync() { var badShard = new ShardLocation("badLocation", "badDatabase"); var bldr = new SqlConnectionStringBuilder(); bldr.DataSource = badShard.DataSource; bldr.InitialCatalog = badShard.Database; var badConn = new SqlConnection(bldr.ConnectionString); try { using (var conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { conn.ShardConnections.Add(new Tuple<ShardLocation, System.Data.Common.DbConnection>(badShard, badConn)); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "select 1"; cmd.ExecuteReader(); } } } catch (Exception ex) { if (ex is MultiShardAggregateException) { var maex = (MultiShardAggregateException)ex; Logger.Log("Exception encountered: " + maex.ToString()); throw ((MultiShardException)(maex.InnerException)).InnerException; } throw; } }
public void TestInvalidMultiShardConnectionString() { MultiShardConnection conn; try { conn = new MultiShardConnection(_shardMap.GetShards(), connectionString: null); } catch (Exception ex) { Assert.IsTrue(ex is ArgumentNullException, "Expected ArgumentNullException!"); } try { conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardMapManagerConnectionString); } catch (Exception ex) { Assert.IsTrue(ex is ArgumentException, "Expected ArgumentException!"); } // Validate that the ApplicationName is updated properly var applicationStringBldr = new StringBuilder(); for (int i = 0; i < ApplicationNameHelper.MaxApplicationNameLength; i++) { applicationStringBldr.Append('x'); } string applicationName = applicationStringBldr.ToString(); SqlConnectionStringBuilder connStringBldr = new SqlConnectionStringBuilder(MultiShardTestUtils.ShardConnectionString); connStringBldr.ApplicationName = applicationName; conn = new MultiShardConnection(_shardMap.GetShards(), connStringBldr.ConnectionString); string updatedApplicationName = new SqlConnectionStringBuilder (conn.ShardConnections[0].Item2.ConnectionString).ApplicationName; Assert.IsTrue(updatedApplicationName.Length == ApplicationNameHelper.MaxApplicationNameLength && updatedApplicationName.EndsWith(MultiShardConnection.ApplicationNameSuffix), "ApplicationName not appended with {0}!", MultiShardConnection.ApplicationNameSuffix); }
public void TestSimpleSelect(MultiShardExecutionPolicy policy) { // What we're doing: // Grab all rows from each test database. // Load them into a MultiShardDataReader. // Iterate through the rows and make sure that we have 9 total. // using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable"; cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; cmd.ExecutionPolicy = policy; using (MultiShardDataReader sdr = cmd.ExecuteReader()) { int recordsRetrieved = 0; Logger.Log("Starting to get records"); while (sdr.Read()) { recordsRetrieved++; string dbNameField = sdr.GetString(0); int testIntField = sdr.GetFieldValue<int>(1); Int64 testBigIntField = sdr.GetFieldValue<Int64>(2); string shardIdPseudoColumn = sdr.GetFieldValue<string>(3); string logRecord = string.Format( "RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, shardIdPseudoColumnField: {3}, RecordCount: {4}", dbNameField, testIntField, testBigIntField, shardIdPseudoColumn, recordsRetrieved); Logger.Log(logRecord); Debug.WriteLine(logRecord); } sdr.Close(); Assert.AreEqual(recordsRetrieved, 9); } } } }
public List <string[]> ExecuteMultiShardQuery(ListShardMap <int> shardMap, string credentialsConnectionString) { // Get the shards to connect to IEnumerable <Shard> shards = shardMap.GetShards(); // Create the multi-shard connection using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString)) { // Create a simple command using (MultiShardCommand cmd = conn.CreateCommand()) { // Because this query is grouped by CustomerID, which is sharded, // we will not get duplicate rows. cmd.CommandText = @" SELECT c.CustomerId, c.Name AS CustomerName, o.OrderID AS OrderCount FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o ON c.CustomerID = o.CustomerID ORDER BY 1"; // Append a column with the shard name where the row came from cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; // Allow for partial results in case some shards do not respond in time cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; // Allow the entire command to take up to 30 seconds cmd.CommandTimeout = 30; // Execute the command. // We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires. using (MultiShardDataReader reader = cmd.ExecuteReader()) { // Get the column names TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray()); int rows = 0; while (reader.Read()) { // Read the values using standard DbDataReader methods object[] values = new object[reader.FieldCount]; reader.GetValues(values); // Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner. // Note that the $ShardLocation pseudocolumn is always the last column int shardLocationOrdinal = values.Length - 1; values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString()); // Add values to output formatter formatter.AddRow(values); rows++; } Console.WriteLine(formatter.ToString()); Console.WriteLine("({0} rows returned)", rows); return(formatter._rows); } } } }
public void TestShardNamePseudoColumnOption() { bool[] pseudoColumnOptions = new bool[2]; pseudoColumnOptions[0] = true; pseudoColumnOptions[1] = false; // do the loop over the options. // add the excpetion handling when referencing the pseudo column // foreach (bool pseudoColumnPresent in pseudoColumnOptions) { using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString)) { using (MultiShardCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable"; cmd.CommandType = CommandType.Text; cmd.ExecutionPolicy = MultiShardExecutionPolicy.CompleteResults; cmd.ExecutionOptions = pseudoColumnPresent ? MultiShardExecutionOptions.IncludeShardNameColumn : MultiShardExecutionOptions.None; using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default)) { Assert.AreEqual(0, sdr.MultiShardExceptions.Count); int recordsRetrieved = 0; int expectedFieldCount = pseudoColumnPresent ? 4 : 3; int expectedVisibleFieldCount = pseudoColumnPresent ? 4 : 3; Assert.AreEqual(expectedFieldCount, sdr.FieldCount); Assert.AreEqual(expectedVisibleFieldCount, sdr.VisibleFieldCount); while (sdr.Read()) { recordsRetrieved++; var dbNameField = sdr.GetString(0); var testIntField = sdr.GetFieldValue<int>(1); var testBigIntField = sdr.GetFieldValue<Int64>(2); try { string shardIdPseudoColumn = sdr.GetFieldValue<string>(3); if (!pseudoColumnPresent) { Assert.Fail("Should not have been able to pull the pseudo column."); } } catch (IndexOutOfRangeException) { if (pseudoColumnPresent) { Assert.Fail("Should not have encountered an exception."); } } } Assert.AreEqual(recordsRetrieved, 9); } } } } }