Trouble with view and proc code getting out of sync
I am using Datagrip with SQL Server 2008. I started using Datagrip because of my extremely positive experience with IntelliJ, Pycharm, and CLion.
I am having a heck of a time with stored procedures and view code not refreshing. I can synchronize, I can select help->invalidate caches but I still get stale code when I open the objects.
I can turn on the "notify when object has changed", then click on Revert Local Changes after I open it.. but that takes 30-40 seconds each time before the link to revert actually works. Then, every time I make a change and commit it, it pops up again. Shouldn't the version that was just committed to the server match what is in my edit?
I am not understanding why reverting is slow and why synchronizing is not working. It seems that if I synchronize (or do invalidate cache) I should get the "current" version?
It has gotten to the point that I don't trust Datagrip despite all its awesome features. I am always uncomfortable that I may be losing some important change done outside of Datagrip.
Is there something I should be doing in the connection settings or in setup that will force newly opened objects to always reflect the current state of the server?
Thanks much,
Chris
请先登录再写评论。
@Aman Subhan
1. No, your source code is applied only when you click submit, it will show you the code to be executed, so that is not kind of background operation. Also, you can always review queries performed by DG in SQL log (Help/Show SQL log...)
For my first question from Devs, I havnt received any answer yet.
In reference to the point 2 in my last comment please see this feature screenshot from Devart DBForge
Can we have this kind of cache refresh capability?
Hello, everybody! In 2018.3 we are testing the new functionality. Please, give it a try.
Turn on
Notify when the outdated object is opened
If the source code is changed from another place, you'll see the notification.
Choose to synchronise the database if you wish.
I thought I had a synchronization issue as described above, but found that I was not using the tool properly. I was expecting it to act exactly like SQL Management Studio. Below is what was doing.
The problem was that the file opened after double clicking procedure was not capable of running the script. This is what worked for me.
I hope this helps someone.
Fortunately, for me anyway, most of the sync/desync issues have been resolved over the past several updates.
I haven't experienced that specific problem before. An alternative to selecting all text, creating a new scratch file and pasting it in might be to right-click the proc and select "SQL Scripts -> Generate DDL to Console". It essentially does the same thing but without creating a new scratch file with the copy/paste, removing a few steps. Hope that helps!
A Hebden6719
Your first approach is right, but don't change CREATE to ALTER. DataGrip can submit the code in a proper way.
Just make the changes you need and press the Submit button. It's with green arrow.
@Maxim, that has never worked for me if the proc already exists on the server. It fails because it executes a CREATE statement instead of ALTER. I always have to change it to ALTER in the script first. This is for MSSQL databases, not sure if that works for other types.
What version are you using? Please, see my screenshot.
Any updates on this issue?
I have version 2020.1.2 and problem still exists.
No warning about posting outdated version.
Cleaning cache every X minutes is not an option for me, database contains thousand of object, full sync takes almost hour.
Really there is no option to disable cache?
All I need is just see actual content of object in database.
Same issue here. Is there any solution?
What is your database?
MSSQL 2000, driver JTDS
Notification does not support that version of MS
I have the same problem with PostgreSQL. I have made changes to datagrip and they are not seen on the server. I can't trust DataGrip to make changes to functions.
Gad,
>I have the same problem with PostgreSQL. I have made changes to datagrip and they are not seen on the server. I can't trust DataGrip to make changes to functions.
Have you submitted your changes?
Hello,
I have another problem: when I want to change the name of a stored procedure and then jump to editor, the code is generated with the old name, even if it is refreshed.
Normally I expect that after rename, when I jump to editor the name of the procedure to be the new one.
IntelliJ IDEA 2020.1.2 (Ultimate Edition)
Build #IU-201.7846.76, built on June 1, 2020
Runtime version: 11.0.7+10-b765.53 x86_64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
macOS 10.15.5
Data Sources and Drivers: Microsoft SQL Server
Thank you!
Yes, known problem, please upvote https://youtrack.jetbrains.com/issue/DBE-6101
This still doesn't seem to work properly for me. I can click refresh all day long but it never refreshes. I have to update the function outside of the interface because it changes my `create or replace` with a `drop and create` and I don't want that behavior. This means I can't use the green Submit button (using mariadb). When I run my `create or replace` outside of this window, I can't ever get it to refresh.
@Joe Phillips
Please provide more info. What is the exact change that leads to drop/create (we need everything but routine/view body before and after to reproduce it).
As for stale sources, you may have unsubmitted changes and looking at your modified version. Please attach sreenshot of the source editor. You may also check database changes toolwindow for pending changes.
Without that info we can not tell what is broken.
Also, what is DG version?
PS. It may be better to go to issue tracker as it is more suitable for that, you may attach files/track changes...
I've discovered that I am a ding dong. I was creating my function to the wrong database and therefore the original wasn't being updated as I thought it was.
Hi, I am a new user to Datagrip, and I also have the same lost trust feeling as described above a long ago, it is really frustrating!
How I understand Datagrip is kind of mixing 2 different usages, and I think that is causing the frustration.
One type of usage seems when you are developing a project (maybe in a branch), so you have multiple related database objects. If I compare this to a MS product, this is similar to the Visual Studio. Well, I do not use Datagrip like this, so I cannot tell too much about it.
However we might also debug issues, and run ad-hoc queries. In MS world this is the SSMS. SSMS also knows the objects for intellisense: it stores them in a local cache. If you want you can refresh the cache anytime, but in the end this cache is used only for making suggestions when you write a SQL statement.
BUT when you would like to open eg a view or a stored procedure SSMS does not use the cache, but gets the actual version from the server. This difference is a big pain in Datagrip for some of us. In Datagrip there is no "modify" in the right click menu like in SSMS, so we need to use the context menu > SQL Scripts > Generate DDL …, but that often mislead us.
In the above replies I cannot see that you are actually realising the two use cases, but seems just offering a one fits all solution, which does not work for both. I do not want to click on refresh button manually, nor I need to refresh ALL the objects, not to mention I do not want to wait for long till ALL objects are refreshed which I am not even care about. I am just interested in what I am about to peek into.
If you want to keep the current behaviour due to the consistency provided for devs, that is fine. But in that case please add a “Generate DDL from server …” to the context menu, which should not get the DDL from the cache, but should go right to the server for that particular object and get the actual version.
@Gabor, I see, it's more UX issue.
Yes, there is no Modify… menu item for the routines. We assumed a bit different approach. One need to double click or invoke CMD+B (Navigation | Go to DDL) to see sources. If sources are stale the notification would appear (Screenshot 1).
In case of modification from your side IDE would mark routine with blue color and suggest migration script on the submission (CMD+K)(Screenshot 2).
To see changes use Show Changes (Screenshot 3)
Screenshot 1.
Screenshot 2
Screenshot 3