[Bug]: When DB Browser freezes on a long query, it CLEARS your sqbpro file

[dorkerdev]

What did you do?

• Ran a long query
• Tried canceling
• DB Browser froze
• Terminated program
• sqbpro file goes to 0 bytes

What did you expect to see?

Not to zero out my file

What did you see instead?

It zeroed out my file. This is the SECOND time this has happened. The first time, I didn't have a backup of all my SQL, so I lost a TON of it and had to rewrite it all. That burned me good, so I have taken to backing up and even versioning my sqbpro file. It just happened again. While I still have my last backup, I still lost an hour's worth of SQL.

DB4S Version

3.12.2

What OS are you seeing the problem on?

Windows

OS version

Windows 7

Relevant log output

No response

Prevention against duplicate issues

• [X] I have searched for similar issues

[dorkerdev]

It was a 29 kb file that got zeroed out. Even though this also consists of metadata, that should still tell you that a lot of SQL could have gotten lost if it weren't for the backup.

[chrisjlocke]

I'm trying to work out how this would occur. I don't use project files, but @sky5walk does so could be useful to drag him into this to see if he's experienced this issue.

Project files are not 'dynamic' though - you have to click Save manually (they're not continually being written to) so I don't see how DB4S crashing can write a zero byte file ... unless the file hasn't been closed when you clicked 'Save' ? I'll try experimenting later, but if you click Save then try renaming the file, does that work or does Windows moan that the file is still in use? The project file has a link to the database, not the /actual/ database, so can't fathom how the crash would do this.

Thanks for reporting it though.
I don't think project files have changed much since the 3.12.2 release, but might be worth checking out the nightly. https://nightlies.sqlitebrowser.org/win32/2022-07/DB.Browser.for.SQLite-2022-07-09-win32.zip Just extract that ZIP and run the EXE - nothing has to be installed and it won't affect your existing install.

As an aside, do you regularly save your project file? This would create a new file 'behind the scenes'. Have you tried a recovery program like Recuva? It may be too late now if the disk has been written to, but usually this can recover deleted files. It's free, but just be careful where you get it and how you install it - it's one of those programs that can come bundled with poop. Another option (although fixing it in DB4S is preferable) is to have a little program monitor the project file and store any SQL (as I assume that's the only thing your saving?) from the project file off. I do something similar for SQL Server as I use that all day, so have small text files littered with 'work in progress' SQL, like a squirrel depositing nuts all over a playpark.

[chrisjlocke]

Just doing an 'end task' didn't result in a zero-byte file for me.

Obviously creating a long-running query isn't easy, so I'll have to create a table with loads of rows in it...

[sky5walk]

Yes, this happened to me before prompting "save early, save often" with all my sqbpro files. Project files are not stable even with the latest nightly. There is a patch waiting to deploy when the nightly builds resume. Until the build error is fixed, save work before running complex queues.

[chrisjlocke]

Thanks for joining in! :)

Saving often is pointless if DB4S creates a 0 byte file... or do you 'save as' and create backups too?

Was the 'this happened to me before' the same steps to reproduce - had a project file, end-tasked a running query and the project file was suddenly 0 bytes?

[sky5walk]

I have had several events where a rogue or recursive query created a runaway DB4S. (Hourglass or spinning cursor or worse, straight close of DB4S app.) sqbpro files are not always affected. All in all, these prompted me to be more cautious when executing my queries. I save copies in a backup folder. I am eagerly awaiting the next nightly to review the current sqbpro status. \
Aside: Much of this could be avoided with a sqlite based project file! 💭

[dorkerdev]

Saving often is pointless if DB4S creates a 0 byte file

It occurs on a crash. I will run a long query, try to cancel by clicking the red X, and then it says "not responding" in the window's title bar. I then terminate the application through task manager and that is when it zeroes out the file.

Also, consider changing the keys to save your project file to CTRL+S instead of CTRL+SHIFT+S. No application uses CTRL+SHIFT. For the longest time, I was using CTRL+S only to find that it wasn't saving at all.

Edit: CTRL+SHIFT+S is usually reserved for save all. This applies to applications that can open multiple documents such as Notepad++. Hitting CTRL+S saves the current tab you're on since it hosts its own document. Excel spreadsheets, on the other hand, can have multiple sheets, but are still one document. Therefore, CTRL+S saves the workbook, sheets and all. DB Browser is more like Excel where each project tab is the equivalent to a worksheet.

[dorkerdev]

so I don't see how DB4S crashing can write a zero byte file ... unless the file hasn't been closed when you clicked 'Save' ?

It will zero out a file that I've been using for days on end. I could write a whole bunch of SQL, save the project, close DB Browser, open it days later, and a crash will still zero it out.

Have you tried a recovery program like Recuva? It may be too late now if the disk has been written to, but usually this can recover deleted files.

It doesn't delete the file, just writes an empty string to it. I've never used a data recovery program that can recover overwritten file contents if the file still exists.

have a little program monitor the project file and store any SQL (as I assume that's the only thing your saving?) from the project file off

I could do this, and I might if this continues. I am sure I could write a simple filewatcher that can make a periodic backup.

[chrisjlocke]

It doesn't delete the file, just writes an empty string to it.

I've not looked at the code, but it would just throw the OS the request - 'oi - save this'. This would create a new file.

I could do this, and I might if this continues.

Short pause, please wait. Just tinkering now, actually.

Also, consider changing the keys to save your project file

Ctrl+S is already in place (write changes). There has been a long desire to give the user the ability to change key combinations, but this isn't easy the way its currently implimented.

[mgrojo]

This seems different to #3007, but there's a chance it could be somehow related. Could someone confirm it can still be reproduced with the current nightly build?

[sky5walk]

Yeah, would help if the original poster has a longish query and sample db?

[mgrojo]

I made a change in #3662 that might have fixed this. Could some of the reporters confirm it, please?

[justinclift]

@dorkerdev @sky5walk Any interest in trying this out with the latest nightly or continuous build? :smile:

[sky5walk]

Yes, I was just gonna give it a try ;). I'll post shortly. ThanksSteve

[sky5walk]

Ok, Still trying to break 240624 nightly. Long, recursive query does not interrupt with [escape]. But, it does not crash DB4S either. Long, non-recursive query interrupts with [escape].

Was there a sample db with known crash steps?