PhpStorm 2019.3.2 suddenly not resolving SQL tables
Hi,
I have a weird problem. I'm running PhpStorm 2019.3.2 on Ubuntu 19.04 (details from "about" tab below). Today it suddenly stopped resolving tables and columns of my connected database (warning: "Unable to resolve table 'xxxxxxx' ").
In database windows everything looks fine, I can see my schema, tables, columns. I can double click on table and fetch data, it works fine. I can also run SQL statements in console (and there PhpStorm is able to resolve columns and tables, I can use autocomplete function).
I tried to fix that problem by my own - firstly I restarted IDE, then MySQL, then whole machine - nothing. I decided to remove data source and re-create it. Without success.
I tried all steps from this article https://www.jetbrains.com/help/datagrip/cannot-find-a-database-object-in-the-database-tree-view.html?_ga=2.238433088.1336268788.1579794648-1508594947.1558523398# but it wasn't helpful. I also tried to map project to schema. Guess what - nothing happend.
Do you have any ideas why my PhpStorm generates those warnings and why it can't resolve tables and columns?
PhpStorm 2019.3.2
Build #PS-193.6015.48, built on January 22, 2020
Licensed to Bartłomiej Werewka
Subscription is active until November 25, 2020
Runtime version: 11.0.5+10-b520.30 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Linux 5.0.0-37-generic
GC: ParNew, ConcurrentMarkSweep
Memory: 1927M
Cores: 8
Registry: run.processes.with.pty=TRUE
Non-Bundled Plugins: com.ppolivka.gitlabprojects
Please sign in to leave a comment.
Did you map the DB correctly at File | Settings | Languages & Frameworks | SQL Resolution Scopes?
Does it happen in .sql/.php files or in sql console?
I'm afraid we would need screenshots that would show all related settings.
Btw what suggestions do you get if you press Ctrl+Space in front of table in a request?
If it's a console make sure correct schema is selected:
Hi Dmitry, thanks for your reply.
The problem is in .php files, not console.
Ctrl + space = "No suggestions".
It's really weird, few hours earlier everything was ok. Suddenly I've noticed that error.
Some screenshots - i can do more if necessary:
Hi,
same problem here. It started with the update to PHPStorm 2019.3.2 (Build #PS-193.6015.48, built on January 22, 2020).
Resolving table names works in the sql editor but not in php files. I have not changed any setting since a couple of versions and it always worked.
The auto-suggestion does not display a single table of my database (i'm connected to a MariaDb 10.3.14).
Hi,
sam problem after update to 2019.3.2. Both on Windows 10 and Ubuntu 18.04.
I tried to invalidate cache and recreate data sources, but it didn't helped.
@..., @Sascha, Bartłomiej Werewka,
Could you specify dialect of injected SQL fragment?
You can get it from intention action, e.g.:
Vasily Chernov,
Initially it was all MySQL and data sources MariaDB. I changed dialect to MariaDB on global and project level. But as you asked I noticed I forgot to change it on directory level.
When I changed on directory to MariaDB problem was resolved :)
I think problem is when you are using MariaDB data source with MySQL dialect.
to
We implemented more strict resolve.
Before now it was resolved to MySQL, MariaDB & MemSQL (actually, to any MySQL fork).
@..., vasily chernov - thanks, after changing dialect to MariaDB problem disappeared :)
Same here, changing SQL dialect to MariaDB solved the problem.
Thanks for helping!
still don't work for me in php file. maybe i missed something?
in console it's OK
You have @lang MySQL docblock there. Should be MariaDB instead.
Also the SCQ Resolution scope could be adjusted to a single database instead of "All Data Sources"
Thank you! It's work!
I am having a similar issue. Exactly which directory needs to have "directory level" dialects applied? Is it the project directory, the SQL base directory, or...?
@Peter,
Do you work with single dialect? If it's the easiest way is to apply to project directory.
@Vasily,
Yes, that worked. Thank you!
Just encountered this as well.
First: MariaDB defined and connected using MySQL driver, project dialect set to MySQL and no autocomplete in editor.
Then: Changed database driver to MariaDB, no autocompletion as well.
Then finally: Changed also project dialect to MariaDB and autocompletion works great.
It was confusing to have all set to MySQL, tables loaded correctly and still no autocompletion. Afterall it works now, still in love with PhpStorm, best IDE.
I am experiencing a similar issue and none of the suggestions have worked. I've tried every combination of SQL dialect settings on the project and global level for MySQL and MariaDB. My engine is MySQL, so I'm assuming that is the correct one, but I've tried both. I've also set the resolution scopes in a variety of ways, but to no avail. The console was not giving me auto-complete suggestions until I went into the Data Source Properties and specified a default database. Now the console will auto-complete, but the code files do not recognize any of the tables. They just show mysql, information_schema, and performance_schema. Any thoughts on why this might be happening and how to fix it?
My suggestion may sound way too generic but did you try to Invalidate Caches already?
Thanks for the suggestion @.... I have tried clearing the cache after most of the settings changes I described above. No luck.
Could you please try to test it with the new project, would it behave in the same manner?
I attempted to connect with a new project and that resulted in the same behavior. I also tried a brand new database from the new project and that was successful. It would appear that it may be a configuration issue with my MySQL instance. I recently upgraded from 5.5 to 5.7. Most likely that is the trigger point for the behavior change. Are you aware of any my.cnf settings that might result in this behavior in PhpStorm? I'm running the database in a Docker container if that might be relevant. Thanks for your help.
Thanks for testing it.
I was wondering if you have already tried the "Force refresh" action (right-click on Data Source > Database tools > Force Refresh". Would it be possible to try it if not yet?
Sorry for the late reply and thank you for your help. I have tried the Force Refresh action as well as the Forget Cached Schemas action and it's still behaving the same. This weekend I may try uninstalling and reinstalling.
No worries, thank you for the update.
Actually, you may try to recreate fresh settings by temporary moving a configuration directory somewhere else:
https://www.jetbrains.com/help/phpstorm/tuning-the-ide.html#config-directory
The solution to mine was right clicking the schema and refreshing it .