search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
307 stars 18 forks source link

When using SchemaComparison with two dacpacs, DacFX takes a long time and throws lots of SocketExceptions #22

Open andreesteve opened 3 years ago

andreesteve commented 3 years ago

Issue

When comparing two dacpacs located on the local file system, DacFx tries to open a SQL connection to a unexisting SQL Server database. It fails consistently with Socket Exception and this lasts for several minutes until it gives up and continues onto doing the comparison.

This is independent of the dacpacs provided. A simple dacpac with a single table compared with an empty dacpac surfaces this behavior.

The comparison is performed correctly, but the extra minutes spent trying to connect to a database that does not exist causes significant delays to the comparison process.

We use DacFx to run comparisons in our build system to verify for breaking changes, and this is adding delays to our build pipeline.

Version

<PackageReference Include="Microsoft.SqlServer.DacFx" Version="150.4573.2" />
[andre@scout ~]$ dotnet --version
3.1.108

Example

// sourceDacpacPath and targetDacpacPath are two local paths to the local file system
var source = new SchemaCompareDacpacEndpoint(sourceDacpacPath);
var target = new SchemaCompareDacpacEndpoint(targetDacpacPath);
var comparison = new SchemaComparison(source, target);
this.schemaComparison.Compare(); // several minutes throwing and internally catching socket exception before this return

Call Stack

System.Net.Sockets.dll!System.Net.Sockets.Socket.DoConnect(System.Net.EndPoint endPointSnapshot, System.Net.Internals.SocketAddress socketAddress) (Unknown Source:0)
System.Net.Sockets.dll!System.Net.Sockets.Socket.Connect(System.Net.EndPoint remoteEP) (Unknown Source:0)
System.Net.Sockets.dll!System.Net.Sockets.Socket.Connect(System.Net.IPAddress address, int port) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SNI.SNITCPHandle.Connect(string serverName, int port, System.TimeSpan timeout) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SNI.SNITCPHandle.SNITCPHandle(string serverName, int port, long timerExpire, object callbackObject, bool parallel) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SNI.SNIProxy.CreateTcpHandle(System.Data.SqlClient.SNI.DataSource details, long timerExpire, object callbackObject, bool parallel) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SNI.SNIProxy.CreateConnectionHandle(object callbackObject, string fullServerName, bool ignoreSniOpenTimeout, long timerExpire, out byte[] instanceName, ref byte[] spnBuffer, bool flushCache, bool async, bool parallel, bool isIntegratedSecurity) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SNI.TdsParserStateObjectManaged.CreatePhysicalSNIHandle(string serverName, bool ignoreSniOpenTimeout, long timerExpire, out byte[] instanceName, ref byte[] spnBuffer, bool flushCache, bool async, bool parallel, bool isIntegratedSecurity) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.TdsParser.Connect(System.Data.SqlClient.ServerInfo serverInfo, System.Data.SqlClient.SqlInternalConnectionTds connHandler, bool ignoreSniOpenTimeout, long timerExpire, bool encrypt, bool trustServerCert, bool integratedSecurity, bool withFailover) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool ignoreSniOpenTimeout, System.Data.ProviderBase.TimeoutTimer timeout, bool withFailover) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, System.Data.ProviderBase.TimeoutTimer timeout) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(System.Data.ProviderBase.TimeoutTimer timeout, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlInternalConnectionTds.SqlInternalConnectionTds(System.Data.ProviderBase.DbConnectionPoolIdentity identity, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, object providerInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString userConnectionOptions, System.Data.SqlClient.SessionData reconnectSessionData, bool applyTransientFaultHandling, string accessToken) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlConnectionFactory.CreateConnection(System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, object poolGroupProviderInfo, System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningConnection, System.Data.Common.DbConnectionOptions userOptions) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(System.Data.Common.DbConnection owningConnection, System.Data.ProviderBase.DbConnectionPoolGroup poolGroup, System.Data.Common.DbConnectionOptions userOptions) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection owningConnection, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection, out System.Data.ProviderBase.DbConnectionInternal connection) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry) (Unknown Source:0)
System.Data.SqlClient.dll!System.Data.SqlClient.SqlConnection.Open() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableSqlConnection.OpenConnection.AnonymousMethod__48_0() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction.AnonymousMethod__0(Microsoft.Data.Tools.Schema.Common.SqlClient.RetryState _) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction.AnonymousMethod__0(Microsoft.Data.Tools.Schema.Common.SqlClient.RetryState retryState) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction<System.__Canon>(System.Func<Microsoft.Data.Tools.Schema.Common.SqlClient.RetryState, System.__Canon> func, System.Threading.CancellationToken? token) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction(System.Action<Microsoft.Data.Tools.Schema.Common.SqlClient.RetryState> action, System.Threading.CancellationToken? token) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction(System.Action action, System.Threading.CancellationToken? token) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableSqlConnection.OpenConnection() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableSqlConnection.Open() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableConnectionHelper.OpenConnection(Microsoft.Data.Tools.Schema.Common.SqlClient.SqlConnectionFactory connectionFactory, bool useRetry) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnInitialize(Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment engine) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.Initialize(Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment engine) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(System.Action<Microsoft.Data.Tools.Schema.DataSchemaError> msgHandler) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.DeploymentWrapper.Compare(bool applyRefactorChanges, bool isForShowingComparisonResult) (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.DeploymentWrapper.CompareForComparisonResult() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.DeploymentTargetComparer.Compare() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.SchemaCompareController.Compare() (Unknown Source:0)
Microsoft.Data.Tools.Schema.Sql.dll!Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.DataModel.SchemaCompareDataModel.Compare(System.Threading.CancellationToken token) (Unknown Source:0)
Microsoft.SqlServer.Dac.Extensions.dll!Microsoft.SqlServer.Dac.Compare.SchemaComparison.Compare(System.Threading.CancellationToken cancellationToken) (Unknown Source:0)
Microsoft.SqlServer.Dac.Extensions.dll!Microsoft.SqlServer.Dac.Compare.SchemaComparison.Compare() (Unknown Source:0)

Exception

{System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (111): Connection refused 127.0.0.1:1433
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)}
andreesteve commented 3 years ago

Installing SQL Server on the machine, even if it rejects the login from SqlCompare does seem to stop the Socket Exceptions and short circuit the bug above. Ideally this can be fixed since there is no need to for SQL Server connection for this scenario.

sqlpain commented 1 year ago

I've had this myself so have needed to revert to using an older version or using sqlpackage. Do we know when this will be looked at?

Aldebaran91 commented 11 months ago

I guess never? This project is full of not resolved bug reports.