No such table: config (Monterrey 12.6.8 / osxphotos 0.63.0)

[devlarosa]

When I export the photos this error occurs. osxphotos_crash.log

To Reproduce Steps to reproduce the behavior:

1. What' the full command line you used with osxphotos? osxphotos export /Volumes/home/Photos/ --from-date "2022-05-01" --export-by-date --exiftool --sidecar XMP --update

2. What was the error output? no such table: config

Expected behavior I allways use the same command to make the backup.

Screenshots

Desktop (please complete the following information):

• OS: macOS Monterrey 12.6.8
• osxphotos 0.63.0

[RhetTbull]

It appears the export database that osxphotos uses to track state of exported files is corrupted but there's no indication the dump of how this happened. Please run the following and post output here:

sqlite3 /Volumes/home/Photos/.osxphotos_export.db ".schema"

[devlarosa]

After I create this issue I reread the Readme file. I found this option in case you may use a NAS: --ramdb. Apparently it worked just fine. The bug without this option still happens thought.

Here is the sqlite command output:

iMac-de-Javier:~ devlarosa$ sqlite3 /Volumes/home/Photos/.osxphotos_export.db ".schema"
CREATE TABLE version (
                                id INTEGER PRIMARY KEY,
                                osxphotos TEXT,
                                exportdb TEXT
                                );
CREATE TABLE runs (
                             id INTEGER PRIMARY KEY,
                             datetime TEXT,
                             python_path TEXT,
                             script_name TEXT,
                             args TEXT,
                             cwd TEXT
                             );
CREATE TABLE info (
                             id INTEGER PRIMARY KEY,
                             uuid text NOT NULL,
                             json_info JSON
                             );
CREATE TABLE exifdata (
                             id INTEGER PRIMARY KEY,
                             filepath_normalized TEXT NOT NULL,
                             json_exifdata JSON
                             );
CREATE TABLE edited (
                              id INTEGER PRIMARY KEY,
                              filepath_normalized TEXT NOT NULL,
                              mode INTEGER,
                              size INTEGER,
                              mtime REAL
                              );
CREATE TABLE converted (
                              id INTEGER PRIMARY KEY,
                              filepath_normalized TEXT NOT NULL,
                              mode INTEGER,
                              size INTEGER,
                              mtime REAL
                              );
CREATE TABLE sidecar (
                              id INTEGER PRIMARY KEY,
                              filepath_normalized TEXT NOT NULL,
                              sidecar_data TEXT,
                              mode INTEGER,
                              size INTEGER,
                              mtime REAL
                              );
CREATE UNIQUE INDEX idx_info_uuid on info (uuid);
CREATE UNIQUE INDEX idx_exifdata_filename on exifdata (filepath_normalized);
CREATE UNIQUE INDEX idx_edited_filename on edited (filepath_normalized);
CREATE UNIQUE INDEX idx_converted_filename on converted (filepath_normalized);
CREATE UNIQUE INDEX idx_sidecar_filename on sidecar (filepath_normalized);
CREATE TABLE about (
                    id INTEGER PRIMARY KEY,
                    about TEXT
                    );
CREATE TABLE detected_text (
                    id INTEGER PRIMARY KEY,
                    uuid TEXT NOT NULL,
                    text_data JSON
                    );
CREATE UNIQUE INDEX idx_detected_text on detected_text (uuid);
CREATE TABLE IF NOT EXISTS "files" (
                    id INTEGER PRIMARY KEY,
                    filepath TEXT NOT NULL,
                    filepath_normalized TEXT NOT NULL,
                    uuid TEXT,
                    orig_mode INTEGER,
                    orig_size INTEGER,
                    orig_mtime REAL,
                    exif_mode INTEGER,
                    exif_size INTEGER,
                    exif_mtime REAL, metadata TEXT,
                    UNIQUE(filepath_normalized)
                    );
CREATE TABLE export_data(
                        id INTEGER PRIMARY KEY,
                        filepath_normalized TEXT NOT NULL,
                        filepath TEXT NOT NULL,
                        uuid TEXT NOT NULL,
                        src_mode INTEGER,
                        src_size INTEGER,
                        src_mtime REAL,
                        dest_mode INTEGER,
                        dest_size INTEGER,
                        dest_mtime REAL,
                        digest TEXT,
                        exifdata JSON,
                        export_options INTEGER,
                        UNIQUE(filepath_normalized)
                    );

Anyway, this tool is really awesome!!

[RhetTbull]

Thanks. This is weird as somehow the config table is indeed missing. Try this:

osxphotos exportdb --migrate /Volumes/home/Photos/

If you get:

Export database /Volumes/home/Photos/.osxphotos_export.db is already at latest version 8.0"

then try the following:

sqlite3 /Volumes/home/Photos/.osxphotos_export.db "UPDATE version SET exportdb = 1.0 WHERE id = 1;"

Then run the migrate command again:

osxphotos exportdb --migrate /Volumes/home/Photos/

[RhetTbull]

Had you recently upgraded osxphotos? Have you previously run an export using osxphotos version 0.63.0 (the current version)? One other user reported a similar problem in the past (#794) with a much older version of osxphotos.

[devlarosa]

Yes. Since I updated to the latest version I have not been able to perform an export. I don't remember what the last version I used was, probably an old one.

Apparently "migrate" also fails. What if .osxphotos_export.db is deleted? Is there a way to restore the export database? Can I run a full export of the entire library to regenerate de database?

Thanks in advance!

[RhetTbull]

That's unfortunate! I'll think about adding a --repair option to exportdb to try to repair the database if needed.

If you delete the export database and re-run the export, you'll get a lot of duplicate files with names like IMG_1234 (1).jpg etc. as osxphotos will see the old files, not be able to match them to the right photo, and not overwrite them.

The cleanest option is to start over with a new export which will build the export database on first export. I would rename the existing export directory, re-create the export directory, then re-run the command. Unfortunately that means everything will have to be re-exported.

[RhetTbull]

@all-contributors please add @devlarosa for bug

[allcontributors[bot]]

@RhetTbull

I've put up a pull request to add @devlarosa! :tada:

[RhetTbull]

@devlarosa one more thing you could try before starting over is to run this and then try to run migrate again:

sqlite3 /Volumes/home/Photos/.osxphotos_export.db "CREATE TABLE config ( id INTEGER PRIMARY KEY, datetime TEXT, config TEXT );"

Then re-run the migrate command.

[devlarosa]

It partially worked. Those images already exist in destiny.

New crash report.

osxphotos_crash.log

[RhetTbull]

In that case you'll have to just start over with a fresh export.

[RhetTbull]

My guess is the database migration failed leaving the database in a partially migrated state. I'll add a new issue to try to prevent this. I've seen this happen a couple of times, always on NAS. Apparently some NAS fail on write intermittently and sqlite (the database) doesn't catch this.

[oPromessa]

@devlarosa do you have a backup of the .osxphotos_export.db on the NAS? @RhetTbull would it make sense to recover that backup and try the migrate/export again?

On another note, if you're exporting to NAS, besides the --ramdb option I also use the --exportdb flag to place the export database on the local disk.

Osxphotos "prefers" to have the export database on the same folder as the exported pics folder (it's simpler); but with a local exportdb you get better performance when exporting to NAS and possibly avoid some database access issues (but yes you must know where it is ;)).

I've been using it successfully for quite some time.

[RhetTbull]

@all-contributors please add @devlarosa for bug

[allcontributors[bot]]

@RhetTbull

@devlarosa already contributed before to bug