AntennaPod fails to sync using latest gpodder2go docker image

[aaro-n]

Problem description

Using ghcr.io/oxtyped/gpodder2go:main and ghcr.io/oxtyped/gpodder2go:latest images, AntennaPod can log in to gpodder2go, but cannot be synchronized. When using the ghcr.io/oxtyped/gpodder2go:gha image, AntennaPod can be synchronized, but cannot be synchronized when the image is upgraded to ghcr.io/oxtyped/gpodder2go:main.

Error log

2024-05-29T12:35:42.114 app[17815615c49e89] nrt [info] 2024/05/29 12:35:42 error getting device_sync_group_id: &sqlite.Error{msg:"SQL logic error: no such table: device_sync_group_devices (1)", code:1}

2024-05-29T12:35:42.114 app[17815615c49e89] nrt [info] 2024/05/29 12:35:42 error trying to retrieve devices in sync_group: SQL logic error: no such table: device_sync_group_devices (1)

[spamtree]

I have the exact same error and device_sync_group_devices table is missing. Would creating the table solve the problem?

/data # sqlite3 g2g.db 
SQLite version 3.45.3 2024-04-15 13:34:05
Enter ".help" for usage hints.
sqlite> .table
device_sync_groups  episode_actions     subscriptions     
devices             schema_migrations   users 

[thebencourt]

Also hitting this issue trying to sync AntennaPod with gpodder2go running on a pi

[aloneguid]

same here

[oxtyped]

Thanks all for the feedback. Sorry it has been a busy time for me. I will try and look into this over this weekend.

Apologies for all those who can't seem to get it working.

The migration is correct, on the latest main branch, you're not supposed to have device_sync_group_devices as it was removed intentionally.

I'm not sure if the docker images have been updated to the latest commit, but if you can, maybe try building the binary from the latest main branch directly and see if that helps?

[owenvoke]

Just to mention, I'm also experiencing this issue. I've used both the main Docker image (which is built off the main branch commit), and built the executable from main.

It looks like the code still tries to access the device_sync_group_devices table in GetDevicesInSyncGroupFromDeviceId. Perhaps this was meant to be removed?

[aloneguid]

Running the latest binary as is also fails:

2024/06/10 07:56:14 [mc/tSIG0sW4LG-000005] "GET http://192.168.1.85:3005/api/2/subscriptions/alg/antennapod_on_sm_s908b.json?since=0 HTTP/1.1" from 192.168.1.242:40804 - 200 45B in 500.46µs
2024/06/10 07:56:14 error getting device_sync_group_id: &errors.errorString{s:"sql: no rows in result set"}
2024/06/10 07:56:14 error trying to retrieve devices in sync_group: sql: no rows in result set
2024/06/10 07:56:14 [mc/tSIG0sW4LG-000006] "POST http://192.168.1.85:3005/api/2/subscriptions/alg/antennapod_on_sm_s908b.json HTTP/1.1" from 192.168.1.242:40804 - 500 0B in 786.676µs

[spamtree]

I have the same error also.

[oliverbr]

Recreating the table from the migration, as suggested in @spamtree's comment can be used as a workaround. It worked for my fresh deployment, at least.

However, this workaround circumvents the migration mechanism, so use it at your own peril.

[spamtree]

That worked! Thanks!

[jnnkB]

Unfortunately, this does not seem to work for an existing deployment, at least not for me.

[oxtyped]

Thanks all for your comments and feedback on this issue!

I've pushed 0e84bd93dff384c44f41b2f6c29a55b1f1ef2a6c to main which removes an old reference to old table. Thanks @owenvoke for picking that out!

I've also added test on 25adb9ce7d1b100745b04885a6a35e186ed731c2 to make sure that subs are added to synced devices.

[owenvoke]

@oxtyped, tested and that seems to work perfectly. 👍🏻 🙌🏻 Thanks!

[VVincentt]

I built the docker image following the instructions in the README. I can start the docker to initialise gpodder2go and create a user. However, when I try to synchronise, I get this error:

$ docker run --rm --name gpodder2go -i -v ./gpodder2go_data:/data -p 3005:3005 oxtyped/gpodder2go
2024/06/26 18:34:39 💻 Starting server at 0.0.0.0:3005
2024/06/26 18:34:52 [ea9b0b4dca09/QQtbRyqJiV-000001] "POST http://10.20.100.40:3005/api/2/auth/vincent/login.json HTTP/1.1" from 10.20.100.17:40396 - 200 0B in 2.522658ms
2024/06/26 18:34:52 [ea9b0b4dca09/QQtbRyqJiV-000002] "GET http://10.20.100.40:3005/api/2/subscriptions/vincent/antennapod_on_op7.json?since=0 HTTP/1.1" from 10.20.100.17:40396 - 200 45B in 1.838985ms
2024/06/26 18:34:52 [ea9b0b4dca09/QQtbRyqJiV-000003] "POST http://10.20.100.40:3005/api/2/subscriptions/vincent/antennapod_on_op7.json HTTP/1.1" from 10.20.100.17:40396 - 500 0B in 787.597
 s
2024/06/26 18:34:52 error getting device_sync_group_id: &fmt.wrapError{msg:"sql: Scan error on column index 0, name \"device_sync_group_id\": converting NULL to int is unsupported", err:(*errors.errorString)(0xc000318220)}
2024/06/26 18:34:52 error trying to retrieve devices in sync_group: sql: Scan error on column index 0, name "device_sync_group_id": converting NULL to int is unsupported

Am I missing something?

[spamtree]

The changes worked nicely for me. No errors. Created a local docker image.

[oxtyped]

Hi @VVincentt,

Is this a fresh install and fresh database? It might be that your database was not migrated.

Could you do a sqlite3 <path/to/db> and then do a .schema and paste the output here?

[VVincentt]

Hello @oxtyped,

Yes, it is a fresh install. I start from the git clone to docker build. I then start the container, create an account then try to sync from AntennaPod.

Here is the log on first launch:

$ docker run --rm --name gpodder2go -i -v ./gpodder2go_data:/data -p 3005:3005 oxtyped/gpodder2go
No database found, intializing gpodder2go ...
... database initialized
VERIFIER_SECRET_KEY not found, intializing VERIFIER_SECRET_KEY ...
... VERIFIER_SECRET_KEY initialized
2024/06/27 16:55:02 💻 Starting server at 0.0.0.0:3005
2024/06/27 17:00:59 [00b8a48a01c4/1qRTIvsq9A-000001] "POST http://10.20.100.40:3005/api/2/auth/vincent/login.json HTTP/1.1" from 10.20.100.17:38530 - 200 0B in 4.383164ms
2024/06/27 17:00:59 [00b8a48a01c4/1qRTIvsq9A-000002] "GET http://10.20.100.40:3005/api/2/devices/vincent.json HTTP/1.1" from 10.20.100.17:38530 - 200 2B in 331.815µs
2024/06/27 17:01:03 username is vincent, deviceName is antennapod_on_gm1911
2024/06/27 17:01:03 DDR is &apis.DeviceDataRequest{Caption:"AntennaPod on GM1911", Type:"mobile"} and "vincent" "antennapod_on_gm1911"
2024/06/27 17:01:03 [00b8a48a01c4/1qRTIvsq9A-000003] "POST http://10.20.100.40:3005/api/2/devices/vincent/antennapod_on_gm1911.json HTTP/1.1" from 10.20.100.17:38530 - 200 0B in 13.984865ms
2024/06/27 17:01:09 [00b8a48a01c4/1qRTIvsq9A-000004] "POST http://10.20.100.40:3005/api/2/auth/vincent/login.json HTTP/1.1" from 10.20.100.17:38530 - 200 0B in 288.444µs
2024/06/27 17:01:09 [00b8a48a01c4/1qRTIvsq9A-000005] "GET http://10.20.100.40:3005/api/2/subscriptions/vincent/antennapod_on_gm1911.json?since=0 HTTP/1.1" from 10.20.100.17:38530 - 200 45B in 468.075µs
2024/06/27 17:01:09 error getting device_sync_group_id: &fmt.wrapError{msg:"sql: Scan error on column index 0, name \"device_sync_group_id\": converting NULL to int is unsupported", err:(*errors.errorString)(0xc00029a9a0)}
2024/06/27 17:01:09 error trying to retrieve devices in sync_group: sql: Scan error on column index 0, name "device_sync_group_id": converting NULL to int is unsupported
2024/06/27 17:01:09 [00b8a48a01c4/1qRTIvsq9A-000006] "POST http://10.20.100.40:3005/api/2/subscriptions/vincent/antennapod_on_gm1911.json HTTP/1.1" from 10.20.100.17:38530 - 500 0B in 555.796µs

Here is the output for .schema:

CREATE TABLE schema_migrations (version uint64,dirty bool);
CREATE UNIQUE INDEX version_unique ON schema_migrations (version);
CREATE TABLE IF NOT EXISTS 'users' (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username varchar (50) UNIQUE NOT NULL,
  password varchar (50) NOT NULL,
  email varchar (255) UNIQUE NOT NULL,
  name varchar(50) NOT NULL,
  created_at varchar(255),
  updated_at varchar(255)
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS 'devices' (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INT NOT NULL,
name varchar(255) NOT NULL,
type varchar(255) NOT NULL,
caption varchar(255),
created_at varchar(255),
updated_at varchar(255), device_sync_group_id INTEGER
REFERENCES device_sync_groups(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS 'subscriptions' (
id INTEGER PRIMARY KEY AUTOINCREMENT,
device_id INT NOT NULL,
user_id INT NOT NULL,
podcast varchar(255) NOT NULL,
action varchar(100) NOT NULL,
timestamp varchar(255),
created_at varchar(255),
updated_at varchar(255),
FOREIGN KEY (device_id) REFERENCES devices (id),
FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE TABLE IF NOT EXISTS 'episode_actions' (
id INTEGER PRIMARY KEY AUTOINCREMENT,
device_id INT NOT NULL,
podcast varchar(255) NOT NULL,
episode varchar(255) NOT NULL,
action varchar(100) NOT NULL,
position int,
started int,
total int,
created_at varchar(255),
updated_at varchar(255),
timestamp varchar(255),
FOREIGN KEY (device_id) REFERENCES devices(id)
);
CREATE TABLE IF NOT EXISTS 'device_sync_groups' (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sync_status VARCHAR(20) NOT NULL DEFAULT 'pending',
  sync_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at varchar(255),
  updated_at varchar(255)
);
CREATE UNIQUE INDEX unique_user_id_and_device_name_index ON devices(user_id, name);

[DavyLandman]

I have the same behavior as @VVincentt a fresh install using docker main tag, and I get:

2024/07/13 21:05:28 error getting device_sync_group_id: &fmt.wrapError{msg:"sql: Scan error on column index 0, name \"device_sync_group_id\": converting NULL to int is unsupported", err:(*errors.errorString)(0x400025ed50)}

a bit earlier:

2024/07/13 21:05:12 username is davy, deviceName is antennapod_on_fp4
2024/07/13 21:05:12 DDR is &apis.DeviceDataRequest{Caption:"AntennaPod on FP4", Type:"mobile"} and "davy" "antennapod_on_fp4"
2024/07/13 21:05:12 [9d726ebbc146/rMDXc0D9sZ-000083] "POST ***/api/2/devices/davy/antennapod_on_fp4.json HTTP/1.1" from XXXX - 200 0B in 3.986302ms
2024/07/13 21:05:28 [9d726ebbc146/rMDXc0D9sZ-000088] "POST ***/api/2/auth/davy/login.json HTTP/1.1" from XXXXX - 200 0B in 322.682µs
2024/07/13 21:05:28 [9d726ebbc146/rMDXc0D9sZ-000089] "GET ***/api/2/subscriptions/davy/antennapod_on_fp4.json?since=0 HTTP/1.1" from XXX - 200 45B in 593.404µs
2024/07/13 21:05:28 error getting device_sync_group_id: &fmt.wrapError{msg:"sql: Scan error on column index 0, name \"device_sync_group_id\": converting NULL to int is unsupported", err:(*errors.errorString)(0x400025ed50)}
2024/07/13 21:05:28 error trying to retrieve devices in sync_group: sql: Scan error on column index 0, name "device_sync_group_id": converting NULL to int is unsupported

[DavyLandman]

Looking at the db, it has the device_sync_group_id

sqlite> .schema devices
CREATE TABLE IF NOT EXISTS 'devices' (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INT NOT NULL,
name varchar(255) NOT NULL,
type varchar(255) NOT NULL,
caption varchar(255),
created_at varchar(255),
updated_at varchar(255), device_sync_group_id INTEGER
REFERENCES device_sync_groups(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE UNIQUE INDEX unique_user_id_and_device_name_index ON devices(user_id, name);

but, it's empty:

sqlite> select * from devices;
1|1|antennapod_on_fp4|mobile|AntennaPod on FP4|||

I want to have both a backup, and a sync of 2 devices, so I added a second device, and it still didn't get in the group.

After reading the code, it looks like it's only possible when sync-devices/ endpoint is called, which AntennaPod doesn't seem to do. So with some curl love I managed to login (get a session cookie) and then send a synchronize request:

curl -D -vv -b 'sessionid=SNIIIP' -X POST -H "Content-Type: application/json" -d '{ "synchronize": [[ "antennapod_on_fp4", "antennapod_on_pixel_2"]] }' -v 'http
s://****/api/2/sync-devices/davy.json'

this then finally allowed antenna pod to sync.

Now the subscriptions are synced, but not the listen status (queue/inbox/already listened). Is this intended? Update: ah yes, that's a limitation of gpodder API.

[oxtyped]

Thanks @VVincentt and @DavyLandman!

The logs and descriptions are all very helpful!

I've found the bug and fixed it in https://github.com/oxtyped/gpodder2go/commit/c9a34687ad59f28b14c89cba608638e3b546d66b

But please re-open this if the issue still persists.

[VVincentt]

Thank you @oxtyped . I can confirm that it works fine. Many thanks for fixing it!