0

Was playing with ipython history commands, experimenting with combining -l and -g to search a limited amount of history, and using -n to see which session a command came from.

Suddenly I got an error (I wish I still had the exact text on my screen) ... something about the history thread encountered and error and stopped, and no more history would be saved. And it said also something about "database locked". After that happened, %history would not recall any history.

So I stopped and restarted ipython, and found that it had history only from the newly restarted session. I stopped and restarted again, and even tried closing and reopening my WSL-Ubuntu window with no luck. It appears that I lost months and months of history.

Then I decided to look for the ipython history file, and found this little gem:

dino@DINO:~$ ls -l $( ipython locate profile default )
total 14132
drwxrwxrwx 1 dino dino      512 Jul 17  2019 db
-rw-r--r-- 1 dino dino 14442496 Mar 24 14:16 history-corrupt-2020-03-24T14.17.27.480963.sqlite
-rw-r--r-- 1 dino dino    28672 Mar 24 14:24 history.sqlite
drwxrwxrwx 1 dino dino      512 Mar 18  2019 log
drwx------ 1 dino dino      512 Mar 18  2019 pid
drwx------ 1 dino dino      512 Mar 18  2019 security
drwxrwxrwx 1 dino dino      512 Mar 18  2019 startup

Wow! Look at that! 14442496 Mar 24 14:16 history-corrupt-2020-03-24T14.17.27.480963.sqlite Looks like ipython actually saved all that history after all! Now the question is, does anyone have any ideas how I might find and remove the corruption from the sqlite file and restore all or maybe most of my history??

Daniel Goldfarb
  • 6,937
  • 5
  • 29
  • 61

1 Answers1

1

Yay, I fixed it! So here's what I did. With some googling I found this program called "sqlitebrowser" and I found this other page with a section titled "How to Repair SQLite database?" that explained that you can sometimes fix an SQLite database by exporting the database to an SQL file, and then importing the SQL file to create a new version of the database.

So I tried that but, when I imported the SQL file, I got an error saying that it failed due to a UNIQUE constraint on the "sessions" table, and that it was unwinding the transaction.

Unfortunately, when I had done the export to SQL, I chose the option to group the inserts into as few SQL commands as possible, so the unwind undid the entire "sessions" table. I later realized that if I had not grouped the inserts, maybe that import would have fixed the database (simply failing on those transactions that contained duplicate sessions).

However at that time, I was not yet sure what was going on and didn't think to go back and re-export to regenerate the SQL with lots of individual inserts.

Instead, I chose to look through the SQL file for any duplicate inserts on the "sessions" table. Indeed, with some sed and sort commands (sort vs sort -u, followed by diff), I was able to determine that sessions 227 through 236 where somehow in there twice!

So I deleted the duplicates from the SQL file, and then again used sqlitebrowser to import the SQL file and re-create the IPython history.sqlite file. I then ran ipython and found that %history magic was able once again to access all my history.

Please let me know with a comment and/or a up-vote if this answer is helpful to anyone. All the best.

Daniel Goldfarb
  • 6,937
  • 5
  • 29
  • 61