2016.3 on osx 10.11 "server name in 'master.sys.schemas' is not supported"

Answered

Hi,

After upgrading to DataGrip 2016.3, I am unable to synchronize an MSSQL data source. Queries are still functional, but the DB schema can't be updated in DataGrip.

I am using the latest SQL Server driver (which I assume is 4.0.2206.100).

Here's the full error message I get every time I try to synchronize: "Reference to database and/or server name in 'master.sys.schemas' is not supported in this version of SQL Server."

Do you have any tips on how to overcome this problem? 

 

Thanks,

Stavros.

17 comments
Comment actions Permalink

Hi,

Try to invoke Forget schemas and synchronise once again.

Thank you

0
Comment actions Permalink

I ran Forget schemas and still faced this issue. I see this when connecting to an azure sql server instance using either sqljdbc41.jar or sqljdbc42.jar. I am able to connect and query, but the schema tree will not render.

UPDATE: I am able to query for tables with this query:
 

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'
0
Comment actions Permalink

Reproducing this on Intellij IDEA 2016.3.

For some databases hosted on AzureSQL after connecting to them this message is shown: "Reference to database and/or server name in 'master.sys.schemas' is not supported in this version of SQL Server.. (Ys)" and then listing of tables does not work."

It also asks for password twice. After entering it the second time, it all works except of this table listing.

Note: this happens only on some databases and I still didn't find the pattern.

0
Comment actions Permalink

Experiencing the same.

Forget schema doesn't help.

Please, resolve this.

0
Comment actions Permalink

Hi,

Could you attach IDE logs?

Also, you can try to invoke Invalidate  Caches / Restart... action.

Thank you.

 

0
Comment actions Permalink

Hi,

Invalidating caches, restarting and forgetting schema all at once doesn't help.

I've uploaded the logs which I think matter to you (so I won't need to cut out sensitive connection data): http://pastebin.com/0vxTiUxt

0
Comment actions Permalink

I am experiencing the same thing.  Looking at the logs I notice the query it's running is:

 

select schema_id as id, name
from [master].sys.schemas
order by case when schema_id = schema_id() then 1 else 0 end desc, schema_id

 

And it is 100% correct that this is not valid sql on Azure Sql (where I am having the issue now).  However, using the local database instead of master for schema 100% gives the correct information but I do not see any way in Datagrip to tell it not to use the master database for schema.

 

If I could tell it to use the database I selected instead of master for this information then it would work for me (On Linux btw).

 

0
Comment actions Permalink

Actually it looks like the core issue is that even though I'm specifying the database I should connect to it still is marking the profile in Datagrip as connecting to the master database by default.  Since I don't have access to that database it's causing this issue.

0
Comment actions Permalink

I have been able to manually fix the problem.  I closed Datagrip, went into my project folder (~/.DataGrip2016.3/config/projects/xxx) and opened the Datasources/<guid>.xml file.  In it I noticed that the master database was incorrectly listed as the current and visible ones.  I deleted the whole master database XML tag (and children).

I then reloaded the project in Datagrip, made sure that the data sources listing on the left hand side showed the correct database name in parenthesis (and not master) and did a synchronize.  That fixed that single data source at least.

1
Comment actions Permalink

Try to check Options -> "Introspect using JDBC metadata"

10
Comment actions Permalink

Just to confirm this is still an issue (and major annoyance) in 2016.3.4.  It's making subsequent synchronizes very annoying.

0
Comment actions Permalink

I concur with Goncharenya: right-click the db -> Options -> check  "Introspect using JDBC metadata". Worked like a charm.

1
Comment actions Permalink

Goncharenya & uptownjimmy saved my day.
Clicking "Introspect using JDBC metadata" actually worked!
Thanks! :)

1
Comment actions Permalink

"Introspect using JDBC metadata" worked for me too.  If it helps any, I started getting the problem when I duplicated a data source.

0
Comment actions Permalink

@Bowlerma

Do you experience the problem in the current DataGrip version 2019.1.3?

If you do, could you explain your problem in details?

0
Comment actions Permalink

Problem is occurring in version 2019.1.3.

I can't recreate the problem 100% of the time.  I have a data source using the Microsoft SQL Server driver configured to connect to a SQL Azure database.  If I duplicate the datasource and configure the duplicate to connect to a different database, I can test the connection on the new data source fine, but whenever I try to use the data source I receive the following errors:

[S0001][40515] com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in 'master.sys.schemas' is not supported in this version of SQL Server.
and 1 duplicate reports

And the following is in the event log.

09:59 Connected

09:59 select schema_id as id, name
from master.sys.schemas
order by case when schema_id = schema_id() then 1 else 0 end desc, schema_id
[S0001][40515] com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in 'master.sys.schemas' is not supported in this version of SQL Server.

09:59 test: [S0001][40515] com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in 'master.sys. ... more

Despite having entered the new database name, on the 'Schemas' tab, the current database is set to 'master'.  It does not appear to be possible to change the current database.  As per the above, if I select the 'Introspect using JDBC metadata' option, it starts working again.

However, this isn't happening for every data source, duplicating another data source is working without issue.

1
Comment actions Permalink

@Bowlerma,

There is an issue https://youtrack.jetbrains.com/issue/DBE-8115 similar to your description.

The only workaround for now is to use 'Introspect using JDBC metadata' option.

0

Please sign in to leave a comment.