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

[ddimkovikj]

• 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: https://github.com/R0tenur/visualization/blob/a4421ea1b1b179035c302d2c88d3dfca183511b7/src/repositories/mssql.repository.ts#L165 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. https://github.com/R0tenur/visualization/blob/4be706d15b112b22eaee4b09d8a10790a6a770f0/src/repositories/mssql.repository.ts#L43

[R0tenur]

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]

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]

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

[hdsheena]

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]

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]

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]

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?

[R0tenur]

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]

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]

@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]

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]

@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]

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]

@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.

https://github.com/R0tenur/visualization/blob/f6b36b395442839fc8546d47b634b8a67e78650e/backend/src/controllers/visualization.controller.ts#L23

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]

I will a look during the weekend

[R0tenur]

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]

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]

@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]

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

[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?

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]

Hello, I managed to make it work !

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

1. USE ${dbName};
2. SELECT ... ;

By deleting the first statement : https://github.com/R0tenur/visualization/blob/3da9458fb1a4804074bfa5094c22d159094b854a/backend/src/repositories/mssql.repository.ts#L69 the following promise https://github.com/R0tenur/visualization/blob/3da9458fb1a4804074bfa5094c22d159094b854a/backend/src/repositories/base.repository.ts#L9 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]

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

[R0tenur]

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]

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]

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]

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]

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

[R0tenur]

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]

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