How to extract missing note changes in Evernote 10

evernote_10_redeemer.md

Two weeks after upgrade to Evernote 10 Mac I found that all my changes are lost!

This script helps to extract the missing notes.

Forums:

• https://discussion.evernote.com/forums/topic/130736-notes-created-on-evernote-mac-1036-do-not-sync/
• https://discussion.evernote.com/forums/topic/130885-missing-notes-after-update-to-v-1037-build-2018/

Evernote was showing that changes are saved but nothing was synchronized to other devices and most importantly upon app restart (or crash) everything returned to the state when the app was started. Fortunately all the changes are not lost but just not visible.

Evernote 10 stores its mutations of notes to be synchronized in SQLite databases located in (where <USER_ID> is eg. 8 digits):

~/Library/Application Support/Evernote/conduit-storage/https%3A%2F%2Fwww.evernote.com
  LocalSettingsDB.sql
  UDB-User<USER_ID>+LocalStorage.sql
  UDB-User<USER_ID>+RemoteGraph.sql
  _ConduitMultiUserDB.sql

We can find the changes in the UDB-User*+LocalStorage.sql file in the OptimisticMutations table. The database can be opened eg. using the SQLite Browser or via the CLI client.

Switch to the Execute SQL tab, paste the query and hit CMD-Enter or the Execute all/selected SQL button.

The changed notes can be listed (with the number of edits) via:

SELECT
json_extract(OptimisticMutations.TValue, '$.params.note') AS note_id,
COUNT(*) as edit_count
FROM OptimisticMutations
WHERE
json_extract(OptimisticMutations.TValue, '$.params.note') IS NOT NULL
GROUP BY
json_extract(OptimisticMutations.TValue, '$.params.note')
ORDER BY COUNT(*) DESC;

note_id                              edit_count
35cc83c7-d2ce-7250-aef6-da1177ea7e5b 669
0078d0fc-51d8-440a-af4e-d372e6c87a10 173
66d716c6-bfdf-f4ec-7b13-b86e779b6479 120
5dabc9ad-56c6-493c-a733-d65dd3715065 98
d37e7e11-9b66-4067-85c2-0c1d88da54a2 81
b0d84ea5-ea6a-401f-950f-ee7f6e36f333 40
cdcf83ab-0e7d-45d8-af36-350d67d81e86 21
b2a69169-4cc0-45cc-921c-d94857c32d31 7
70a0a35f-1463-0473-b5b9-579d25eaa316 7
1271f5e4-90d5-456e-9b9e-9bde93598675 4
d07d6372-774b-418a-9db1-8fb4b539b994 2
f7cfd41f-ab10-4dcf-875a-e3f8e68c30ba 1
cddafca0-0826-452a-a096-3a2aed68e47d 1
81e7da8a-d143-4e56-b9e1-b6b80d3b0438 1

Then the latest content (HTML) can be extracted for each such note and saved to a HTML file:

SELECT
json_extract(OptimisticMutations.TValue, '$.params.note') AS note_id,
json_extract(OptimisticMutations.TValue, '$.timestamp') AS timestamp,
json_extract(OptimisticMutations.TValue, '$.params.noteContent') as content
FROM OptimisticMutations
WHERE
json_extract(OptimisticMutations.TValue, '$.params.note') = '35cc83c7-d2ce-7250-aef6-da1177ea7e5b' AND
json_extract(OptimisticMutations.TValue, '$.params.noteContent') is not null
ORDER BY
json_extract(OptimisticMutations.TValue, '$.timestamp') DESC
LIMIT 1;

• paste the query above
• change the note id (after '$.params.note')
• hit CMD-Enter or the Execute all/selected SQL button
• click on the note content field
• in the Edit Database Cell pane at the right
  • select XML format
  • click the Save As button
  • select file type: All file (*)
  • set the name as some_note_title.html
  • click Save

Then we can install the Evernote Legacy (version 7). .

For each redeemed note we can just copy the content from the HTML file in a browser and paste back to Evernote.