The Schema Visualization is stuck at "Getting structure from database..." #29

ddimkovikj · 2020-12-04T21:49:49Z

ADS v1.24
Schema Visualization v0.6.0
Databases are located on localhost
Login using SQL authentication

Initial screen:

After switching to other tab the message and the loader are gone:

The fixed informationsSchemaQuery query mentioned in issue #7:

visualization/src/repositories/mssql.repository.ts

Line 165 in a4421ea

SELECT

returns the following error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Meanwhile, the original query using sys tables returns the actual tables.

visualization/src/repositories/mssql.repository.ts

Line 43 in 4be706d

SELECT (SELECT

The text was updated successfully, but these errors were encountered:

R0tenur · 2021-04-09T08:04:46Z

Hello, thank you for reporting the issue!
Are you able to run the following query?

SELECT
        T.TABLE_NAME,
        Columns.COLUMN_NAME,
        (SELECT TOP 1
            KCU2.TABLE_NAME
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON 
        KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
                KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
                KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON 
        KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND
                KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND
                KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION AND
            KCU1.TABLE_NAME = T.TABLE_NAME AND KCU1.COLUMN_NAME = Columns.COLUMN_NAME) as REFERENCE_TO_TABLE,
        (SELECT TOP 1
            KCU2.COLUMN_NAME
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON 
        KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
                KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
                KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON 
        KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND
                KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND
                KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION AND
            KCU1.TABLE_NAME = T.TABLE_NAME AND KCU1.COLUMN_NAME = Columns.COLUMN_NAME) as REFERENCE_COLUMN,
        (SELECT TOP 1 KCU1.CONSTRAINT_NAME AS 'FOREIGN_KEY'
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON 
        KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
                KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
                KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON 
        KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND
                KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND
                KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION AND
            KCU1.TABLE_NAME = T.TABLE_NAME AND KCU1.COLUMN_NAME = Columns.COLUMN_NAME
) as FOREIGN_KEY
    FROM INFORMATION_SCHEMA.TABLES T
        INNER JOIN INFORMATION_SCHEMA.COLUMNS Columns ON Columns.TABLE_NAME = T.TABLE_NAME
 WHERE T.TABLE_TYPE='BASE TABLE'

hdsheena · 2021-04-15T23:38:48Z

Pretty sure I also have this issue, tried to modify source but not sure how to re-build to vsix file (I tried a few things and none worked) so looking forward to a release with this change for me! :)

R0tenur · 2021-04-16T09:46:28Z

I published a patch version you could try: https://github.com/R0tenur/visualization/releases/tag/v0.7.1

hdsheena · 2021-04-16T17:17:02Z

Thanks! unfortunately, it's still throwing an error and hanging:
[Extension Host] [Error - 10:12:10 AM] Request query/simpleexecute failed.
console.ts:137 [Extension Host] Message: Query has no results to return Code: 0

The query runs okay, returns 152 lines when I run it as a standalone query in data studio. Happy to send whatever debug info might help..

VilladsR · 2021-06-09T19:55:19Z

Stuck with the same issue - any help would be appreciated! Installed 0.7.1, attempting to visualize an azure sql db, ran the query above without issues returning 755 rows

Zangdorf · 2021-06-25T09:50:40Z

Hello ! It seems I have the same issue : I have 0.7.1 installed, and query you posted 9 Apr ran without issues. I caught these errors in Developer Tools Console :

console.ts:137 [Extension Host] [Error - 11:40:23] Request query/simpleexecute failed.
console.ts:137 [Extension Host] Message: Query has no results to return Code: 0

R0tenur · 2021-08-25T14:05:26Z

Hello, sorry for slow reply, I am currently on parental leave and have limited time to work on any projects.

I am not able to reproduce this myself but if there is any interest to help fixing this bug I am happy to support you running the project locally.
@hdsheena You mentioned in a comment above that you tried to run it from source, do you remember what the issue was?

Got same results with this query as original against AdventureWorks, and this removes correlated subqueries. Speed massively increased. I/O dropped significantly. Working on PR for R0tenur#29 .

Removed correlated subqueries from sys DMV query. R0tenur#29

Got same results with this query as original against AdventureWorks, and this removes correlated subqueries. Speed massively increased. I/O dropped significantly. Working on PR for R0tenur#29 .

R0tenur · 2021-09-16T18:46:48Z

Thanks to @bruce-dunwiddie there should be a fix for this in release v0.8.0

If the error still occurs, please comment here and I will reopen the issue.

Zangdorf · 2021-09-17T08:32:07Z

Hello, I installed new release v0.8.0, but the error still occurs with same log in developer tools console :
console.ts:137 [Extension Host] [Error - 11:40:23] Request query/simpleexecute failed.
console.ts:137 [Extension Host] Message: Query has no results to return Code: 0

The schema visualization is still stuck at "Getting the database structure..."

My database is on a remote local server which I login with SQL authentication.

If I can help by sending any more debug info, just let me know :)
log_error_js.log

bruce-dunwiddie · 2021-09-17T14:11:04Z

@Zangdorf , how long did the query from Apr 9 take to run on your remote connection to the database? How many rows were returned?

How long does it take to run the new query?

SELECT
    T.TABLE_NAME,
    Columns.COLUMN_NAME,
    FK.REFERENCE_TO_TABLE,
    FK.REFERENCE_COLUMN,
    FK.FOREIGN_KEY
FROM INFORMATION_SCHEMA.TABLES T
    INNER JOIN INFORMATION_SCHEMA.COLUMNS Columns ON Columns.TABLE_NAME = T.TABLE_NAME
    LEFT OUTER JOIN (
        SELECT
            KCU1.TABLE_NAME AS REFERENCE_FROM_TABLE,
            KCU2.TABLE_NAME AS REFERENCE_TO_TABLE,
            KCU1.COLUMN_NAME AS REFERENCE_FROM_COLUMN,
            KCU2.COLUMN_NAME AS REFERENCE_COLUMN,
            KCU1.CONSTRAINT_NAME AS 'FOREIGN_KEY'
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON 
                KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
                KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND
                KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON 
                KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND
                KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND
                KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
    ) FK ON
        FK.REFERENCE_FROM_TABLE = T.TABLE_NAME AND
        FK.REFERENCE_FROM_COLUMN = Columns.COLUMN_NAME
 WHERE T.TABLE_TYPE='BASE TABLE';

Zangdorf · 2021-09-17T14:28:17Z

Hi, I tried to run both queries on 2 different databases (one which is huge, and a tiny one). Here is the result :

huge DB :

query from 9 Apr : 1610 rows affected => Total execution time: 00:00:46.325
query from 17 Sep : 1610 rows affected => Total execution time: 00:00:00.643

tiny DB :

query from 9 Apr : 25 rows affected => Total execution time: 00:00:00.098
query from 17 Sep : 25 rows affected => Total execution time: 00:00:00.019

Both DB are located on the same remote local server, and both cannot display Schema Visualization.

bruce-dunwiddie · 2021-09-17T14:37:14Z

@Zangdorf , thank you for your replies and helping us to troubleshoot this issue.

We've at least eliminated speed issues.

Can you please run this query and send back the result so we can know what version of SQL you're using?

Select @@version;

Zangdorf · 2021-09-17T14:53:48Z

Sure,

Microsoft SQL Server 2017 (RTM-CU26) (KB5005226) - 14.0.3411.3 (X64) Aug 24 2021 09:59:15 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

bruce-dunwiddie · 2021-09-17T16:08:03Z

@Zangdorf , thank you. That looks to be a fairly standard version.

@R0tenur , my best suggestion for simulating this issue is to edit the schema query to make it invalid and throw an error, and then see how the error bubbles up, even though it would appear that the queries are working when run directly.

visualization/backend/src/controllers/visualization.controller.ts

Line 23 in f6b36b3

const database = await getMssqlDbSchema(

database.errors is not getting set in getMssqlDbSchema, and there is no error trap to catch an exception bubbling up. My javascript is rusty so I'm not currently able to suggest the best line of code for catching errors from your Promises.

It may be an error within the toTables function when mapping the query results back to the collections.

When I alter the schema query to be invalid, I similarly stall at this step and get no error message, other than a similar error message in the Debug Console as posted above.

R0tenur · 2021-09-17T17:29:18Z

I will a look during the weekend

R0tenur · 2021-09-18T20:25:54Z

I have created an release that propagates the errors to the UI. @Zangdorf It would be helpful if you could try Release v0.8.1 and post the real error in here.

Zangdorf · 2021-09-20T15:46:00Z

Ok, so I installed release v0.8.1 and tried to run visualization again. Unfortunately, I still get the same error message :
Query has no results to return

The error appears now in the UI instead of the circle progress bar

bruce-dunwiddie · 2021-09-20T16:06:18Z

@R0tenur , brainstorming here.

It's definitely just like the error message says, no results returned.

Only one odd thing comes to mind that might cause that, but it seems very odd for a SELECT. You could possibly get this same error if the resultset that we're looking for is not the first resultset. In MSSQL, it's common on UPDATE/DELETE to SET NOCOUNT ON to prevent "rows affected" messages from throwing off the resultset index. @Zangdorf , is it possible that there's an audit system in place that is logging rows returned by queries into another table? When you run the query in Management Studio, are there multiple "rows affected" messages logged in the "Messages" tab?

@R0tenur , if not something odd like the situation above, then it almost has to be something around pulling the security context and credentials over from the hosting application. Are you able to propagate a SQL exception up from the SQL library? Is it possible that it's reporting something like "can't connect" or "user does not have access"?

raoulteeuwenPE · 2021-09-21T08:10:11Z

Fyi: on one db i get a result, on another (the bigger one of the 2) i get the same error as @Zangdorf .

Zangdorf · 2021-09-21T08:12:11Z

is it possible that there's an audit system in place that is logging rows returned by queries into another table? When you run the query in Management Studio, are there multiple "rows affected" messages logged in the "Messages" tab?

No, I don't have this kind of feed redirection embedded in my database, and I have a unique line "rows affected" when I run the query:

One thing that comes to my mind, I have a firewall that prevents some resources being loaded from the external network. Do you know if your extension module requires external resources to run the query properly ?

I will try to run the project from source (using your installation and building doc). If I manage to, maybe I will find more error details and I will come back to you.

Zangdorf · 2021-09-21T13:42:40Z

Hello, I managed to make it work !

I saw that in the code the query was made up of two statements :

USE ${dbName};
SELECT ... ;

By deleting the first statement :

visualization/backend/src/repositories/mssql.repository.ts

Line 69 in 3da9458

USE ${dbName};

the following promise

visualization/backend/src/repositories/base.repository.ts

Line 9 in 3da9458

queryProvider.runQueryAndReturn(connectionUri, query)

is not throwing an error anymore and schema visualization is displayed correctly.

Thanks to @R0tenur and @bruce-dunwiddie for your help and your investment on the issue :)

hdsheena · 2021-09-21T20:56:50Z

Any chance someone can put this into a VSIX so i can try it on mine? Doing way tooo many things lately, sorry!

R0tenur · 2021-09-22T06:57:12Z

Hopefully I will have the time to look into this tonight. If you want to build it your self ther is a guide Here

Zangdorf · 2021-09-22T09:23:09Z

Hi, here is the vsix file compiled with yarn and including my fix. I hope it will work for you aswell :)
schema-visualization-0.8.1_FIX#29.vsix.zip

raoulteeuwenPE · 2021-09-22T09:50:15Z

Hi, here is the vsix file compiled with yarn and including my fix. I hope it will work for you aswell :)

Thank you. On the 2 db's i've tried out this add-on, one works (except for the zoom, but have filed a seperate issue for that), and the bigger one now throws a 'Maximum text size in diagram exceeded' (and very fast after starting the visualisation).

R0tenur · 2021-09-22T19:06:05Z

I have tried to add a fix for this by using the native azdata way of selecting the db. If someone with the issue could test the Release v0.8.2
@raoulteeuwenPE @Zangdorf

Zangdorf · 2021-09-23T07:12:59Z

Yeah ! Release v0.8.2 works fine in my case 👌

R0tenur · 2021-09-23T10:32:07Z

Hi, here is the vsix file compiled with yarn and including my fix. I hope it will work for you aswell :)

Thank you. On the 2 db's i've tried out this add-on, one works (except for the zoom, but have filed a seperate issue for that), and the bigger one now throws a 'Maximum text size in diagram exceeded' (and very fast after starting the visualisation).

This is an limitation in mermaid to prevent blocking the UI. I have already increased the limitation in #49 but have added another issue #68 to put the workload in a web worker.

I need to setup som crazy test data to fix this and the zoom thingy #62 :)

R0tenur · 2021-09-23T10:35:11Z

I will again close this and reopen if the error will happen again

ddimkovikj changed the title ~~The Schema Visualization is stuck at "Getting structure from database..." when selecting DB~~ The Schema Visualization is stuck at "Getting structure from database..." Dec 4, 2020

R0tenur added a commit that referenced this issue Apr 11, 2021

handle multiple results in query #29

f24b287

R0tenur added bug Something isn't working help wanted Extra attention is needed labels Aug 25, 2021

R0tenur mentioned this issue Sep 6, 2021

Tables are shown, but 0 relations #60

Closed

bruce-dunwiddie added a commit to bruce-dunwiddie/visualization that referenced this issue Sep 14, 2021

Removing more correlated subqueries

2af89a2

Removed correlated subqueries from sys DMV query. R0tenur#29

bruce-dunwiddie mentioned this issue Sep 14, 2021

Remove correlated subqueries for MSSQL #63

Closed

bruce-dunwiddie mentioned this issue Sep 15, 2021

Remove correlated subqueries for MSSQL #64

Merged

R0tenur closed this as completed Sep 16, 2021

R0tenur reopened this Sep 17, 2021

R0tenur added a commit that referenced this issue Sep 22, 2021

Use native way of selecting db #29

2eb46f4

R0tenur mentioned this issue Sep 23, 2021

Add valid error handling from the base repo #67

Closed

R0tenur closed this as completed Sep 23, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

The Schema Visualization is stuck at "Getting structure from database..." #29

The Schema Visualization is stuck at "Getting structure from database..." #29

ddimkovikj commented Dec 4, 2020 •

edited

Loading

R0tenur commented Apr 9, 2021

hdsheena commented Apr 15, 2021

R0tenur commented Apr 16, 2021

hdsheena commented Apr 16, 2021

VilladsR commented Jun 9, 2021

Zangdorf commented Jun 25, 2021 •

edited

Loading

R0tenur commented Aug 25, 2021

R0tenur commented Sep 16, 2021

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021 •

edited

Loading

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021

R0tenur commented Sep 17, 2021

R0tenur commented Sep 18, 2021

Zangdorf commented Sep 20, 2021

bruce-dunwiddie commented Sep 20, 2021

raoulteeuwenPE commented Sep 21, 2021

Zangdorf commented Sep 21, 2021 •

edited

Loading

Zangdorf commented Sep 21, 2021 •

edited

Loading

hdsheena commented Sep 21, 2021

R0tenur commented Sep 22, 2021

Zangdorf commented Sep 22, 2021

raoulteeuwenPE commented Sep 22, 2021

R0tenur commented Sep 22, 2021

Zangdorf commented Sep 23, 2021

R0tenur commented Sep 23, 2021 •

edited

Loading

R0tenur commented Sep 23, 2021

The Schema Visualization is stuck at "Getting structure from database..." #29

The Schema Visualization is stuck at "Getting structure from database..." #29

Comments

ddimkovikj commented Dec 4, 2020 • edited Loading

R0tenur commented Apr 9, 2021

hdsheena commented Apr 15, 2021

R0tenur commented Apr 16, 2021

hdsheena commented Apr 16, 2021

VilladsR commented Jun 9, 2021

Zangdorf commented Jun 25, 2021 • edited Loading

R0tenur commented Aug 25, 2021

R0tenur commented Sep 16, 2021

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021 • edited Loading

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021

Zangdorf commented Sep 17, 2021

bruce-dunwiddie commented Sep 17, 2021

R0tenur commented Sep 17, 2021

R0tenur commented Sep 18, 2021

Zangdorf commented Sep 20, 2021

bruce-dunwiddie commented Sep 20, 2021

raoulteeuwenPE commented Sep 21, 2021

Zangdorf commented Sep 21, 2021 • edited Loading

Zangdorf commented Sep 21, 2021 • edited Loading

hdsheena commented Sep 21, 2021

R0tenur commented Sep 22, 2021

Zangdorf commented Sep 22, 2021

raoulteeuwenPE commented Sep 22, 2021

R0tenur commented Sep 22, 2021

Zangdorf commented Sep 23, 2021

R0tenur commented Sep 23, 2021 • edited Loading

R0tenur commented Sep 23, 2021

ddimkovikj commented Dec 4, 2020 •

edited

Loading

Zangdorf commented Jun 25, 2021 •

edited

Loading

bruce-dunwiddie commented Sep 17, 2021 •

edited

Loading

Zangdorf commented Sep 21, 2021 •

edited

Loading

Zangdorf commented Sep 21, 2021 •

edited

Loading

R0tenur commented Sep 23, 2021 •

edited

Loading