search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.57k stars 900 forks source link

ADS table Contextual menu options do nothing after a while #14915

Open drizztdourden08 opened 3 years ago

drizztdourden08 commented 3 years ago

Steps to Reproduce:

  1. Open ADS
  2. Use it (Or not) and leave it open for a while
  3. At some point, the contextual menu on a table (Select Top 1000, Edit Data) become unresponsive

The only way I found to fix the issue is a restart of ADS. I'm not exactly what the cause is but I do use these function quite a lot and I have to restart ADS pretty often. Where it is installed or fresh installation changes nothing. (I have it on my personal computer, my work one and on a VM in Azure. All of them do that.)

New Query on the database seems to continue working (Probably since it does nothing except a new tab). Tab that are already opened continue to work normally so I doubt it is just disconnected.

Regards,

cssuh commented 3 years ago

For the table you are trying to query, is this a local DB or one in Azure?

drizztdourden08 commented 3 years ago

These are DB in an elastic pool on Azure. I have 12 of them in two different pool and this does the same thing for all.

drizztdourden08 commented 3 years ago

Additional Information: This seems to affect only the DBs that were used during the session. The other works flawlessly when I switch to them but does the same thing over time.

Please see the GIF for the steps.

Suggestions: Open a TAB no matter what with the appropriate query and return an error message or a loading. That should at least covers the "User experience" portion of the issue where the user is left without any clues as to what's the problem. being connected or not to a particular DB should not affect if a tab will open or not. It should work with what it has available already. If I can see the list of columns in a table, it should have no issues building the query, opening a tab with it and then executing (Or at least try).

ADS_Steps

cssuh commented 3 years ago

When you try to run Select Top 1000, do you get any error messages in the Developer Tools? (you can open developer tools using Help -> Toggle Developer Tools)

drizztdourden08 commented 3 years ago

Yes. I do: ERR Scripting failed. error: An error occurred while scripting the objects. Failed to connect to server xxxxxxxxxxx.database.windows.net,1433.

With the exact same pattern and DB used in the GIF above: image

The first message is from the first "Select Top 1000" in the "not used for a while DB". The remaining one are from the "never used DB", still a "Select Top 1000".

we can see the first one failing whatever script it is suposed to. And the second one clearly just reconnecting if it has been lost in the meantime. I supose somewhere in the script, it doesn't properly reconnect.

It isn't a configuration issue as this issue doesn't exist in SSMS and I can run for days (or until I'm required to re-authenticate MFA).

drizztdourden08 commented 3 years ago

Today, when working. Since it seems to be a connection issue, When I saw the same issue again, I tried the "Refresh" option on the database object. It seem to work but when I expanded the table folder, it gave me this: image

Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server xxxxxxx.database.windows.net,1433. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. Token is expired. at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect() at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() ClientConnectionId:xxxxx-e719e586bd8f Error Number:18456,State:233,Class:14 ClientConnectionId before routing:xxxxx-7ffe36f3ce06 Routing Destination:xxxxx.worker.database.windows.net,11156 --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect() at Microsoft.SqlServer.Management.Common.ConnectionManager.get_IsContainedAuthentication() at Microsoft.SqlServer.Management.Sdk.Sfc.ConnectionHelpers.UpdateConnectionInfoIfContainedAuthentication(Object& connectionInfo, Urn urn) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue) at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue) at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName) at Microsoft.SqlServer.Management.Smo.Database.get_IsSqlDw() at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.DatabaseTreeNode.EnsureContextInitialized() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\DatabaseTreeNode.cs:line 51 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoTreeNode.GetContext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\SmoTreeNode.cs:line 98 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.GetContextAs[T]() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\Nodes\TreeNode.cs:line 310 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.DatabaseTreeNode.PopulateChildren(Boolean refresh, String name, CancellationToken cancellationToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\DatabaseTreeNode.cs:line 55 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.Expand(String name, CancellationToken cancellationToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\Nodes\TreeNode.cs:line 248 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.Expand(CancellationToken cancellationToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\Nodes\TreeNode.cs:line 257 at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.ObjectExplorerService.<>cDisplayClass35_0.b0(IBindingContext bindingContext, CancellationToken cancelToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\ObjectExplorerService.cs:line 431 at Microsoft.SqlTools.ServiceLayer.LanguageServices.BindingQueue`1.<>cDisplayClass34_1.b1() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\LanguageServices\BindingQueue.cs:line 381`

The tree goes back to un-expanded after that and it does expand until the tables this time. However, the error message for scripting remains the same when trying "Select Top 1000".

I tried "Disconnect" on the database, then expanding again. I can see in the console it does everything normal to connect properly but when trying the "Select Top 1000", the error message still remains the same.

Only a restart of the application fix the issue for now.