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
Please sign in to leave a comment.
@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 openedIf 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.