Manual repair of database failing due to SQL error with translatorCache

kfarquhar
While trying to sync my library, there was a translator error. So I attempted to manually repair my database (https://zotero.org/utils/dbfix/manual). Although the dump.sql file is large, zotero.sqlite.new file has 0 kb and the command line spits out:

Error: near line 490816: UNIQUE constraint failed: translatorCache.fileName
Error: near line 490817: UNIQUE constraint failed: translatorCache.fileName
...
Error: near line 490932: UNIQUE constraint failed: translatorCache.fileName
Error: near line 490933: UNIQUE constraint failed: translatorCache.fileName

Any solution? I couldn't find a similar error on the forum.
  • kfarquhar
    The translator error comes up when opening the stand alone:

    [JavaScript Error: "Invalid sync state undefined" {file: "chrome://zotero/content/xpcom/storage/storageEngine.js" line: 310}]

    [JavaScript Error: "Invalid sync state undefined" {file: "chrome://zotero/content/xpcom/storage/storageEngine.js" line: 310}]

    [JavaScript Error: "Error(s) encountered during statement execution: database disk image is malformed [QUERY: SELECT rowid, fileName, metadataJSON, lastModifiedTime FROM translatorCache] [PARAMS: ] [ERROR: database disk image is malformed]" {file: "chrome://zotero/content/xpcom/db.js" line: 668}]

    [JavaScript Error: "Error(s) encountered during statement execution: database disk image is malformed [QUERY: SELECT rowid, fileName, metadataJSON, lastModifiedTime FROM translatorCache] [PARAMS: ] [ERROR: database disk image is malformed]" {file: "chrome://zotero/content/xpcom/db.js" line: 668}]

    [JavaScript Error: "Error(s) encountered during statement execution: database disk image is malformed [QUERY: SELECT rowid, fileName, metadataJSON, lastModifiedTime FROM translatorCache] [PARAMS: ] [ERROR: database disk image is malformed]" {file: "chrome://zotero/content/xpcom/db.js" line: 668}]

    [JavaScript Error: "Error(s) encountered during statement execution: database disk image is malformed [QUERY: SELECT rowid, fileName, metadataJSON, lastModifiedTime FROM translatorCache] [PARAMS: ] [ERROR: database disk image is malformed]" {file: "chrome://zotero/content/xpcom/db.js" line: 668}]

    version => 5.0.59, platform => Win32, oscpu => Windows NT 10.0; WOW64, locale => en-US, appName => Zotero, appVersion => 5.0.59, extensions => Zotero LibreOffice Integration (5.0.14.SA.5.0.59, extension), Zotero Word for Windows Integration (5.0.10.SA.5.0.59, extension)
  • dstillman
    edited December 22, 2018
    It may not work, but you can try running sqlite3.exe /path/to/zotero.sqlite "TRUNCATE translatorCache;" before doing the .dump > dump.sql to clear the translatorCache table, which may be (at least in part) where the corruption is.

    If that doesn't succeed, you could manually remove the lines in dump.sql that begin INSERT INTO translatorCache. You'll need to use a text editor that can handle very large files, though — Notepad++ is probably your best bet. (There are also ways to do this quickly from the command line, but they're easier on a Mac or Linux computer.)
  • kfarquhar
    I tried to do the clear table, but got this error:

    E:\Zotero>sqlite3.exe E:/Zotero/zotero.sqlite "TRUNCATE translatorCache;" .dump > dump.sql
    Error: near "TRUNCATE": syntax error
  • kfarquhar
    edited January 6, 2019
    Additionally, removing lines with that phrase didn't work, but that's where a corruption line ended at the last line.
  • emilianoeheyns
    That would be two separate commands: 

    sqlite3.exe E:/Zotero/zotero.sqlite "TRUNCATE translatorCache;"

    sqlite3.exe E:/Zotero/zotero.sqlite .dump > dump.sql
  • dstillman
    @kfarquhar: Sorry, I forgot that SQLite doesn't support TRUNCATE. Try DELETE FROM translatorCache; instead of TRUNCATE translatorCache;.
  • kfarquhar
    I did that, including the last step in the manual method, and got this error:

    Error: near line 3176957: NOT NULL constraint failed: syncCache.syncObjectTypeID
    ...3177242

    Is this a issue arising from manually deleting the INSERT INTO translatorCache lines with a notebook program?

  • dstillman
    There's likely just other corruption in your database. If you can't resolve this yourself, you can upload the file somewhere (e.g., Dropbox) and send a link to support@zotero.org with a link to this thread, and we can take a look.
Sign In or Register to comment.