search

yy0931 / sqlite3-editor

https://marketplace.visualstudio.com/items?itemName=yy0931.vscode-sqlite3-editor
GNU General Public License v3.0
838 stars 6 forks source link

Corrupted database after using extension #49

Closed Technowulff closed 7 months ago

Technowulff commented 7 months ago

I installed your extension to VS Code in an attempt to query a large active SQLite database, about 1 GB. Specifically a 1.2 million record table in that database. After entering in my query, the result table showed for about 1 minute, and then disappeared and gave me a "Database disk image is malformed" error. I restarted VS Code and attempted to access the database again, and the extension said the *.sqlite database was not a database. I removed the extension, but by then damage was done somewhere. Our software's logs started repeatedly showing the following errors:

[INFO]: SQL RW1: Connecting...
[INFO]: SQL RW1: (0ms) Connected
[ERROR]: SQL RW0: Disconnect due to connection failure (up for 0s): Query Failed (779): database disk image is malformed
[ERROR]: SQL RW0: Disconnect due to connection failure (up for 0s):
[ERROR]: SQL RW1: Disconnect due to connection failure (up for 0s): Query Failed (11): database disk image is malformed

I used the extension with VS Code 1.87.0 on Windows. The database is located on an IBM AIX server.

Could you advise on what you think might have happened to cause this failure, and could point us in a direction that could help us resolve this issue?

Anything you could offer for help would be appreciated.

yy0931 commented 7 months ago

I'm sorry to hear that. What I can think of to resolve the issue is:

  1. Are there temporary files such as -journal, -shm, or -wal? If so, could you try temporary moving them somewhere else? If you are using VSCode's virtual workspaces or a network file system, the changes to these temporary files may have been written in the wrong order.
  2. If that does not help, try SQLite's .recovery command: https://www.sqlite.org/recovery.html.

Regarding the cause of the issue, since SQLite is atomic and issuing an SQL query should not corrupt the database, I think the cause might be related to how you are connecting to the server.

Edit: I mistakenly referred to Remote-SSH as a virtual workspace, but it isn't. Other extensions such as SSH FS do create virtual workspaces.

yy0931 commented 7 months ago

Assuming you were using virtual workspaces, I believe the database file became corrupted because it was being queried by a process on the remote server at the same time as VSCode attempted to overwrite the file with another version of the database that existed in a virtual workspace. I'm not sure what data the resulting corrupted file contains. I've uploaded a new version of the extension that disables writes to virtual workspaces to prevent this from happening again. Please let me know if you were not actually using a virtual workspace.

Technowulff commented 7 months ago

Thank you for your reply. It helped me with finding what might have been the root cause of the issue.

After restarting our software, the 2nd query error (11) disappeared, but the error 779 remained. Using the documentation found on SQLite.org, I found this could be fixed using the REINDEX statement. I did a pragma integrity_check on the database which confirmed the index was corrupted and not the database. We ran the REINDEX and restarted the software.

It looks like this did the trick because the error in our logs would have appeared within a few minutes of restarting the software.

yy0931 commented 7 months ago

I'm glad to hear that the error has been resolved. Also, thank you for providing the details that helped identify the cause.