Editor can't resolve database table names without starting a session first
Hi,
so I have a Python project in IntelliJ IDEA using the Python plugin, and I also do SQL stuff in there. For autocomplete purposes, I added the SQL connections to the project, and everything workd great until recently. But now, either since the 2019.3.2 or 2019.3.1 update (I'm not quite sure when it was introduced), the IDE can't resolve tables and columns anymore, and thus it marks every SQL statement in my project as an "unable ro resolve X" error, which is quite distracting and makes the scrollbar basically a big red blob on some files:
(names obviously changed for the screenshot, but it looks the same with the actual tables and columns)
However, if I select "Run query in console" and start a new session in the dialog that pops up, then it recognizes everything again, at least for the current file. In other files I then need to either open a new session or attach the one that I opened first. I definitely did not have to do this before.
Now, I'm not sure if this behaviour is intended or a bug, but either way it's pretty annoying, and makes me less likely to see the red dots when there is an actual error somewhere. Is this a bug? And if not, can someone explain to me why this changed and how I can work around this without going through every file?
Please sign in to leave a comment.
Hello,
Please make sure you have SQL Resolution Scopes configured for this file.
I have, and they're configured to "All Data" for the project default and for the file itself:
I've also tried to specify just the schemas that are in use, but that didn't help either, so I just put it back to the default.
I'm just checking now and I have the same issue on another machine, so it's not completely isolated. Are there any relevant logs or something I could look at to find the cause of this?
Works fine on my machine if "All schemas" are selected. Does it work fine if you use full path "schema.table_name"?
No, it doesn't even recognize the schema name itself.
Please try to right click on your DB, select "Database Tools" and run "Force Refresh" and "Forget Cached Schemas"
At first I thought that did it, but it only fixed it for one of two databases, and only in one file (that one stays fixed even after restarting the IDE though). I tried repeating that while having other files open just to check if that makes a difference, but unfortunately it doesn't.
Edit: is there a way to see where exactly the IDE is trying to look up table and column names, just to help with troubleshooting?
Do you face the same issue with new sample project? Please try to run "File | Invalidate Caches"
Invalidate Caches did nothing. I just created a new sample project and it shows the same behaviour:
Please attach screenshot with "SQL resolution scope" for new project.
It's basically the same as in the other project - I've tried to choose the specific schemas instead of "Everything" as well, but it made no difference.
Could you please share idea.log ("Help | Show Log in...") after restarting IDE?
Sure, here it is: https://www.dropbox.com/s/mib9xfxqiy3u90b/idea.log
Please try to run IDE under JBR 11 (https://intellij-support.jetbrains.com/hc/en-us/articles/206544879-Selecting-the-JDK-version-the-IDE-will-run-under) and temporarily disable custom plugins:
I tried all that, though it didn't change anything.
I have, however, apparently stumbled upon the actual reason when creating another test project where it worked and comparing that to my actual problem project.
If anyone has the same problem and happens to find this thread: check your project dialect! My databases are MariaDb. Somehow, at some point, the project default Sql dialect apparently got set to MySql instead of MariaDb. How? I have no idea. Maybe a rogue plugin switched something around, or an update, or maybe it was always like that and the editor was just more tolerant up to a certain version. Anyway, the editor does not seem to be able to do resolution and autocomplete if those don't match.
Now I've switched the project dialect to MariaDb, everything works as it should again. I guess that also explains why it worked when I started a session manually, because at that point, the IDE got the actual dialect from the connected database (at least that's my guess?). I can only test this on one machine right now, but I'm pretty confident the fix will be the same on the other one.
I feel a little bit stupid for not checking that earlier, and I'm sorry for wasting so much of your time. I very much appreciate your support!
Thanks for information!
Thank you... Wow, spent too much time on this. It was the Dialect!
I burned 2 hrs on this but feel like it was worth it to get it working again. Thanks for leaving this thread. The app needs a quickfix to change the SQL resolution scope.
Im going to open a ticket and ask for that and also to change the SQL resolution scope per function/statement/file using comments or some other mechanism.
Also, would be great if the quick fix suggests to configure the SQL Dialect ;)
SQL dialect is a project-wide setting, it is configured once, so no need to include this into a quick fix, don't you think so?
It's not something you need to do often, but it might nudge users towards the solution for this problem, so that they don't burn as much time on it as some of us in this thread did. So I think either that or some other way to help identify a bad dialect configuration would be a good thing.
I totally agree with Christian.
Created YouTrack item to implement that:
https://youtrack.jetbrains.com/issue/DBE-12976
Follow it to receive the updates.
Christian you are a life saver. I had been struggling with this issue for months. Can't believe the solution was so easy.
Mmonto95 Haha, glad this helped somebody. Even before this post, I was struggling for quite a while as well.
I think SQL Dialect could change to correct one automatically by 'Attach session' fix. At least IDE can warn user that session is not in selected dialect.
I have a bit of a different, but related issue. Same exact thing as described here. I've correctly set my SQL dialect and my SQL resolution scopes and tried all of the things mentioned here.
"Works fine on my machine if "All schemas" are selected. Does it work fine if you use full path "schema.table_name"?"
Yes if I use schema.table_name the query gets recognized correctly. But I cannot do this for a variety of reasons (company coding policy, our DB plugin module doesn't like this, etc). Is there any way where PyCharm will correctly determine the table on its own without me explicitly telling it which schema the table is under?