Version v2.0.2 - database error=json: unsupported value: NaN ...

[TheBig-O]

With the latest version, 2.0.2, I get an error immediately after start up. It appears to be in the Go file file=/app/pkg/database/workouts.go:407.
If I revert to v2.0.1, it works just fine.
Not sure how to fix the issue if it is complaining about values within the workout.
Any ideas?

2025-01-18T13:11:02.263806019-05:00 ERROR msg=json: unsupported value: NaN app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database error=json: unsupported value: NaN query=SELECT * FROM "map_data" WHERE "map_data"."workout_id" IN (1707,1705, ... <cut out multiple ID numbers for brevity> ,1711,1710) duration=40103531 rows=988 file=/app/pkg/database/workouts.go:407

2025-01-18T13:11:02.263936083-05:00 ERROR msg=json: unsupported value: NaN app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database error=json: unsupported value: NaN query=SELECT * FROM "workouts" ORDER BY date DESC duration=58442302 rows=988 file=/app/pkg/database/workouts.go:407

panic: json: unsupported value: NaN

goroutine 1 [running]:

main.main()

	/app/main.go:30 +0x19a

2025-01-18T13:11:15.510691865-05:00 INFO msg=Connecting to the database 'postgres': host=db user=wt password=Saga~Curtain~Reaffirm3 dbname=wt port=5432 sslmode=disable TimeZone=America/New_York app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=app

2025-01-18T13:11:15.532396793-05:00 INFO msg=replacing callback `gorm:query` from /app/vendor/github.com/go-gorm/caches/v4/caches.go:44
 app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database

2025-01-18T13:11:15.532434727-05:00 INFO msg=replacing callback `gorm:query` from /app/vendor/github.com/go-gorm/caches/v4/caches.go:48
 app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database

2025-01-18T13:11:15.532450833-05:00 INFO msg=replacing callback `gorm:query` from /app/vendor/github.com/go-gorm/caches/v4/caches.go:52
 app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database

2025-01-18T13:11:15.532477502-05:00 INFO msg=replacing callback `gorm:query` from /app/vendor/github.com/go-gorm/caches/v4/caches.go:56
 app=workout-tracker version=master sha=d584bf458026123c90c8d16c59decf4a423bc372 module=database

[jovandeginste]

This seems to be in the database query. There must be some invalid data in the database. Are you handy with sqlite?

[TheBig-O]

I'm actually using both SQLIte and PostgreSQL right now. I'm in the process of transitioning to Postgres.
When I initially saw the error, I thought it was an SQL error so I decided it was a good time to start fresh on Postgres. Unfortunately, I get the same error with both databases.
So, short answer, yes, I'm decent with SQLite. What would you like me to check?

[jovandeginste]

In that case, you can try two things:

• Add all the workouts in a clean setup and check whether the problem returns
• make a copy of the (corrupt) database and drop half of the workouts; if the problem remains, drop more; when the problem disappears, restore a copy and drop from the other half (basically bisect the issue)

Eventually, you should find the culprit and we should be able to determine what exactly causes this...

[jovandeginste]

Perhaps I should provide a command line utility to iterate over all workouts and print information about them...

[TheBig-O]

A Command line utility would be great! Trying to find which line causes the problem will take a while.
Any idea why v2.0.1 works and v2.0.2? Even now, when I roll back to the lower version, I don't get the error.

[jovandeginste]

Yes, I don't understand what could be related... I won't pretend I don't make mistakes, maybe you pulled a tag at a time the build was broken? (I had to redo a tag in the v2 because I messed something up)

[TheBig-O]

I'll try repulling and let you know

[jovandeginste]

Release v2.0.3 contains an extra binary wt-debug, which you can try; it's still very much WIP, but you never know!

[TheBig-O]

Awesome! I'm finishing a couple things but will test the new version shortly. Thanks!!!

[jovandeginste]

The binary is not inside the docker container; it's contained in the archive files you can download and you can run it separately, eg against your SQLite file.

[TheBig-O]

Sorry. Brain is glitching. I downloaded the source code and found the wt_debug, but not sure if I'm doing the right thing. I tried starting the container but it panics too quickly for me to get in and work with it.
I'm going to try with a new, empty database and will load some workouts to see what I can see.

[TheBig-O]

So, I started with a clean database in the Sqlite container and added everything back in from GPX files just like I did before and it worked just fine. The only difference is that I did not include any file that was in the "failed" directory.
I'm debating starting over from scratch with the Postgres version and doing the same thing. The only catch is that I would lose the comments that I married up to the old workouts. I may just do a dry run with a new database to prove it works, but it still leaves the question of what's causing the issue. It could be that the failed GPX are being scanned and causing some sort of fault, but that's a total guess. The issue seems to be coming from something that is pulled from the database but causing an error in the web app. Are the failed files stored, in any way, in the database?
I think the debugger is the path that will get us there, but I haven't sorted out a way to try that. Even in the running sqlite version, I'm not seeing the wt_debug binary.
I'll keep at it.

[jovandeginste]

Every release has compiled assets; from v2.0.3 on, if you download the compiled archive for your system, it should contain 2 binaries: workout-tracker and wt-debug. The first one is the actual web server which also runs in the docker container, the second one is the debugging tool I just started working on.

The docker container does not contain the debugging tool (yet).

[TheBig-O]

I finally figured it out!!!! It was nothing to do with the application (sort of). For some reason, 2 records in the map_data table were non-numeric values. As soon as I replaced those values with null values, I restarted and things worked with both Sqlite and Postgres versions.
I'm not sure why v2.0.1 didn't complain about those values and v2.0.2/v2.0.3 did, but it's fixed now.
I found this while working through the debug, export, import process. I did a general scrub of the data afterward and think I caught everything.

My query to find them was:

SELECT
  map_data.workout_id, 
  map_data."name", 
  map_data.average_speed, 
  map_data.average_speed_no_pause
FROM
  map_data
WHERE
  NOT (CAST(average_speed AS TEXT) ~ '^[-+]?[0-9]*\.?[0-9]+$')  -- Check if average_speed is not numeric
  OR NOT (CAST(average_speed_no_pause AS TEXT) ~ '^[-+]?[0-9]*\.?[0-9]+$')  -- Check if average_speed_no_pause is not numeric
ORDER BY
  map_data.average_speed DESC;

[jovandeginste]

What was the value then, if not numeric and not null?

[TheBig-O]

So, in the Sqlite database, the fields contained +inf. In the Postrgres database, it was shown as NaN.
As part of my testing, I tried several queries to get them to pull up. The query shown above, searching non-numeric values, is the only one I got to work. If I directly searched for text, I got an error.

Something I realized later was that the database does not like zero values for speed and distance. I'm not sure how I had GPX files with zero values for those two items, but I have since corrected them. I realized this after refreshing the workouts (through the user profile page). Immediately after doing so, a recalculation put those values back into the map_data table. The only way I can think to prevent this error is to ensure that none of the distance, time, and speed values are zeros. (When the GPX files were imported, they were properly formatted files so Workout Tracker imported them. The issue came with the calculations for averages happened.)

If I was making a recommendation for app updates, it would be to include some sort of error check that either bypasses the calculation for zero value entries or ignores the entries entirely (and adds the gpx files to the failed bin) while adding a comment to the logs. I would also make it impossible to add a zero value entry while manually adding a workout through the web app.
Basically, I'm saying protect us from ourselves. 😆

[jovandeginste]

Yes, I noticed the same issue; I assumed that division by zero would not be allowed, but:

https://go.dev/play/p/fucTTgc2cEJ

This was a big surprise for me. And JSON did not like that. I now added #409

[alternativesurfer]

It kind of seems like this is getting worse.

I just noticed my app was down again, many more items listed with +Inf now than last time...
Last time I had 3 items (#417). I fixed them manually by editing the DB.
I have added 0 since then.

wt-app | {"time":"2025-02-10T13:36:57.237476279-08:00","level":"ERROR","msg":"json: unsupported value: +Inf","app":"workout-tracker","version":"master","sha":"25b0ee2b4bdb2b5df61e7935a8b24dd6c761a35f","module":"database","error":"json: unsupported value: +Inf","query":"SELECT * FROM "map_data" WHERE "map_data"."workout_id" IN (215,174,173,172,171,8,2,3,1,6,170,169,168,167,190,166,189,165,188,203,164,163,162,161,160,159,158,4,157,156,155,154,153,152,151,150,149,148,147,146,144,143,142,145,141,140,139,138,137,136,135,134,133,132,131,130,129,127,126,125,124,123,122,121,120,119,118,117,214,213,116,212,211,210,115,114,113,128,112,111,110,109,108,107,106,209,208,105,104,207,103,206,102,101,100,99,98,97,205,96,95,94,93,92,91,90,89,88,204,87,86,85,202,84,83,82,81,80,79,78,77,76,179,178,177,176,75,175,201,74,200,199,73,198,72,197,71,196,70,195,194,193,69,187,191,68,67,66,65,64,63,62,61,60,59,58,57,56,53,52,51,49,50,44,42,41,40,38,35,34,33,31,30,29,28,55,18,17,16,19,13,21,11,9,10)","duration":7846479,"rows":185,"file":"/app/pkg/database/workouts.go:405"}

[TheBig-O]

How are you entering the workouts that are changing to +Inf? Are these manually entered or workouts without GPS data?

[jovandeginste]

Can you share the specifics for one such workout (anonymised if relevant)? Maybe I can reproduce...