Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
[12.1.277 Pro] - SQL History issue |
|
For some unknown reason, SQL History stopped collecting executions.
|
|
Thu Sep 22, 2022 9:32 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
What is the last modification time for your %APPDATA%\SQL Assistant\CodeRepo\saCodeRepo.db file?
If you have multiple instances of SASE open, can you please try restarting all of them, in case one of them is having a lock?
|
|
Thu Sep 22, 2022 2:49 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
The file system says it's 2022-08-26 16:38. I'm pretty sure the machine has been rebooted several times since that date. The last boot was on 2022-09-18.
|
|
Thu Sep 22, 2022 5:07 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Can you please try for a test, renaming this file and restarting SASE? You can switch them back again later.
FYI, it's a SQLite database with full text indexing enabled. Hopefully it's not corrupted, and the issue is somewhere else. Or might be that an FTS index just needs a rebuild.
|
|
Fri Sep 23, 2022 11:23 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
I've done so. I've renamed the file and restarted both SA and SE. I hoped the database file to be replaced with a shiny, new, empty version of it but no such thing happened. Was this supposed to happen (not)?
|
|
Fri Sep 23, 2022 4:35 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Yes, that was expected with factory default settings.
Can you please check what you got in the Options for Common -> History -> Save SQL History? Is it still set to Always?
|
|
Fri Sep 23, 2022 5:34 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Can you please check what you got in the Options for Common -> History -> Save SQL History? Is it still set to Always? |
Yes, it is.
|
|
Fri Sep 23, 2022 6:14 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Would you please check SqlAssist.log for related errors?
|
|
Fri Sep 23, 2022 6:46 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
How do I know if an entry is related, what shall I look for? The only lines containing the word "saCodeRepo" are the ones mentioning the application LockHunter.exe with which I was trying if the file was locked by something after you pointed out it being locked as a possible root of the error.
That file got pretty large since the last time I cleared it (2022-04-03). It has now > 330k lines. Surprisingly it only has about 1k lines containing the word "error". It contains somewhere around 90k lines of Client: Attach immediately followed by Client: Detach pairs. I know that SA can turn almost anything into a full-fledged database editor (including the Windows built-in Calculator and any USB attached toaster GUIs), and I guess those are the occasions SA decided not to do that. I'm having a vast number of different applications and programs running in the background I'm perfectly fine with not doing any database manipulations, so it's nice I'm able to tell SA which ones to ignore, but adding two lines to the log every time it happens to meet one of those on the exclusion list seems not to be overly efficient. Perhaps a complementary option to Ignored Targets, called eg. Non-ignored Targets and SA could use whichever list it is told to.
|
|
Sat Sep 24, 2022 1:31 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
If there were many errors, you would find them near the end of the log file. It's ok to delete that log file at any time. It will start over.
I'm afraid the issue is somewhere else, if I recall correctly, you previously experienced issues with your code snippets not getting saved permanently. How did you resolve it?
|
|
Sun Sep 25, 2022 9:41 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
That was a difficult one. The Microsoft Store version of Spotify tended to lock a bunch of files it had no absolutely no business with, and SqlAssist.sas was one of those files. It ceased to do so as soon as Spotify*.exe was added to the Ignored Targets. But I observe no such locks on saCodeRepo.db.
I peeked into the saCodeRepo.db. I queried the SA_EXEC_HIST view and according to the EXECTIME column, the last entry logged happened on 2022/08/26 at 16:38:33. The only log entries around that time are these:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
2022. 08. 26. 16:28:59 SqlEditor.exe Externally modified D:\Work\Projects\hkir\data-model\db-scripts\hkir-scr-obu-c\SQLs\hkir-obu-c_O\common\usp.hkir.usp_set_condition_task.sql. Old time: 2022. 08. 25. 07:44:16; new time: 2022. 08. 25. 07:53:51
2022. 08. 26. 16:29:00 SqlEditor.exe Externally modified D:\Work\Projects\hkir\data-model\db-scripts\hkir-scr-obu-c\SQLs\hkir-obu-c_O\common\usp.hkir.usp_get_obu_status_data.sql. Old time: 2022. 08. 25. 07:44:33; new time: 2022. 08. 25. 07:53:51
2022. 08. 26. 16:30:48 SqlEditor.exe Ctrl+C processed by window TsaSyntaxMemo(1A1036, editor)
2022. 08. 26. 16:31:38 SqlEditor.exe Ctrl+C processed by window TsaSyntaxMemo(1A1036, editor)
2022. 08. 31. 12:27:05 SqlEditor.exe Error: Connection failed.
|
It shows that the last log entry before that history entry is 7 minutes older and is not an error entry. The next entry is an error entry, but it happened a few days later.
Why is that code repo db not recreated from scratch after I renamed the original?
|
|
Sun Sep 25, 2022 4:56 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Why is that code repo db not recreated from scratch after I renamed the original? |
Unfortunately I don't know the anwer, it's a mystery to me. I hope to get some help with that tomorrow.
|
|
Sun Sep 25, 2022 7:20 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
We would like to test it with factory default settings to check if it's somehow impacted by your custom settings.
Before testing that please save your current settings file, so that you can restore after testing.
|
|
Sun Sep 25, 2022 8:51 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
I've closed all instances of SE, restored factory defaults in SA, and then restarted SA. Started an instance of SE and executed a query. No saCodeRepo.db. I've tried catching it with Process Monitor but seemingly nothing tries to access any kind of path containing the word "saCodeRepo".
EDIT: hmm, SA just notified me that there's a new update v12.1.279. I run it, it threw an error about not being able to execute eclipsec.exe to installed the eclipse plugin (which I don't have anyway), and after restarting SE and executing a query, the saCodeRepo is created. Process Monitor also reports dozens of QueryDirectory operations trying to access saCodeRepo.db and saCodeRepo.db-journal.
UPDATE: I've restored the backup of the old saCodeRepo.db. Now all the old queries are there in the History and it still works. It also saves new ones, yay :)
Is 12.1.279 a fix for this issue, or is it merely a side-effect that installing it restored the code repo database?
|
|
Mon Sep 26, 2022 4:18 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
I don't see in the internal records any updates related to the History feature. Good to know you got it working again. I hope if won't regress again.
|
|
Mon Sep 26, 2022 8:08 am |
|
![](templates/GreenTech/images/spacer.gif) |
|