Recover a accidentally paused ride on the Wahoo Fitness App

recover-paused-ride-wahoo.md

Recover a paused ride on the Wahoo Fitness App

If you like me use the Wahoo Fitness App to track your bike rides, from time to time it may happen to inadvertitely pause the recording in the middle of a ride.

Sometimes this really sucks cause it will of course not include the full mileage and duration from your ride and also skip valuable Strava segments recorded.

What I found out is that the app is actually recording all the GPS points of the track, but when exporting it will split it up is as many additional tracks as the times your have paused/resumed.

So with some reverse engineering I found a way to avoid this and to have a single and complete track when exporting to online services like Strava. The only limitation I found is that the app will continue to show the rides with the reduced mileage & durations (not a big deal if you export your tracks anyway).

So here's what you need to do, assuming you are using Wahoo Fitness on iOS:

• Connect the phone to your PC with the USB cable, open up iTunes and copy the file WorkoutData.sqlite locally. Make a backup of this file and run the following instructions on a copy of it.

• Open the file with DB Browser for SQLITE

• Identify the value of ZWORKOUTDATA ID (Workout ID) for your ride, e.g. by inspecting the table ZWORKOUTSEGMENT (the higher the ID the most recent the activity).

• Identify your ZSESSION ID for your ride by inspectin the ZWFWORKOUT table (the higher the ID the most recent the activity).

Update (April 27th, 2020)

Users reported not being able to use the original method on newer versions of the Wahoo Fitness App.

If the original method described below does not work for you, you can try this:

@MarsFlyer has come up with a way to dump ride data from the SQLITE db using SQL queries, and generate a GPX file from this data. See here.

Original method

This method involves altering the SQLITE db in order to merge the paused segment into a single ride, and upload it back to the iPhone.

• Run the following queries:

UPDATE ZWFBIKECADENCESAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];

UPDATE ZWFBIKESPEEDSAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];

UPDATE ZWFLOCATIONSAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];

UPDATE ZWFTIMEINTERVAL SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTSEGMENT = [ZWOID];

UPDATE ZWFWORKOUT SET ZPAUSEDDURATION = 0 WHERE ZSESSION = [ZWOSSID];

UPDATE ZWFWORKOUTSEGMENT SET ZPAUSEDDURATION = 0 WHERE ZWORKOUTDATA = [ZWOID];

Update (May 1st, 2020)

On newer version of the app, you also need to update the ZWFWORKOUT table as suggested by @slingbike:

Seems there are three fields in ZWFWORKOUT that perhaps were not there previously: ZCURRENTSTATE, ZDELETESTATUS, AND ZHIDDEN [...] set to 0, 0, and "null," respectively ...

• Save the changes and upload the file back on your phone using iTunes.

So after playing with the WorkoutData.sqlite file for a couple of hours, I ran the following SQL snippet to try and update the database:

UPDATE ZWFFOOTPODSAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];
UPDATE ZWFHEARTRATESAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];
UPDATE ZWFLOCATIONSAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];
UPDATE ZWFMOTIONANALYSISSAMPLE SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTDATA = [ZWOID];
UPDATE ZWFTIMEINTERVAL SET ZISWORKOUTACTIVE = 1 WHERE ZWORKOUTSEGMENT = [ZWOID];
UPDATE ZWFWORKOUT SET ZPAUSEDDURATION = 0 WHERE ZSESSION = [ZWOSSID];
UPDATE ZWFWORKOUT SET ZCURRENTSTATE = 0 WHERE ZSESSION = [ZWOSSID];
UPDATE ZWFWORKOUTSEGMENT SET ZPAUSEDDURATION = 0 WHERE ZWORKOUTDATA = [ZWOID];

substituting in values for the workout id and session id. I ran with a Tickr X, hence the need to update the ZWFMOTIONANALYSISSAMPLE table.

After closing the database and copying it back to my iPhone, and reopening the Wahoo app, I didn't see any change in the display of my run; it was still listing a 25 minutes pause. Digging around a little reveals that the Wahoo app also contains a history/ folder which in turn appears to contain .fit files for all the workouts I've done, and a crux log file. The loading of this file appears to be corroborated by the contents of the rolling.log file.

$ ls -lah wahoo-backup/history/2020-06-0* | head
-rw-r--r--  1 alexander  staff   483K Jun  5 21:52 wahoo-backup/history/2020-06-05-120809-TICKRX D4B7-13737-0-crux_track.log
-rw-r--r--  1 alexander  staff    40K Jun  5 21:52 wahoo-backup/history/2020-06-05-120809-TICKRX D4B7-1591358889-0.fit
-rw-r--r--  1 alexander  staff    14M Jun  8 21:00 wahoo-backup/history/2020-06-08-192915-FITNESS C41B-39-0-crux_track.log
-rw-r--r--  1 alexander  staff   186K Jun  8 21:00 wahoo-backup/history/2020-06-08-192915-FITNESS C41B-39-0.fit

...
2020-06-08 23:25:45 +0000: Start: Parse startTime (null)fit file /var/mobile/Containers/Data/Application/D818D5CA-BD31-4292-963C-15DD6C69093F/Documents/history/2020-06-08-192915-FITNESS C41B-39-0.fit
2020-06-08 23:25:46 +0000: Parse fit file completed, Summary:
fit file path /var/mobile/Containers/Data/Application/D818D5CA-BD31-4292-963C-15DD6C69093F/Documents/history/2020-06-08-192915-FITNESS C41B-39-0.fit
  StartTime           = 8 Jun 2020 at 19:29
  TotalTime           = 1:30:41
  PausedTime          = 25:45
  ActiveTime          = 1:04:56
  Leg                 = 1 of 1
  samples             = 5484
  lap count           = 1
  Summaries           = {
    36 = "AVG:4293.958845265592 MAX:8747.549999999999 MIN:0 RANGE:8747.549999999999 ACCUM:8747.549999999999 ACCUM/TIME:1.607709979783128 FIRST:0 LAST:8747.549999999999";
    37 = "AVG:119.2877145922746 MAX:152.6 MIN:78.79999999999995 RANGE:73.80000000000007 ACCUM:0 ACCUM/TIME:0 FIRST:103.6 LAST:103.6";
    19 = "AVG:76.83239728052025 MAX:150 MIN:50 RANGE:100 ACCUM:0 ACCUM/TIME:0 FIRST:150 LAST:150";
}
...

(note the PausedTime value denoting this as the inadvertantly paused workout)

I deleted the .fit file containing the paused run, that I'm trying to fix, thinking that the app would recreate it upon realising but instead it seems to just display an empty activity. The overview pane on the front page of the app seems "correct" in as much as it was before, but there are no details to load.

Unless there's another field we need to be setting, much like @slingbike found previously, I can only conclude that the app now creates a .fit file upon completion of an activity, an doesn't read any more from the sqlite db.

¯_(ツ)_/¯

@aodj It sounds like that is a known issue:

The only limitation I found is that the app will continue to show the rides with the reduced mileage & durations (not a big deal if you export your tracks anyway).

Hopefully exports to other apps like Strava will still use the SQLite DB?

I couldn't get it to work; exporting to Strava didn't produce anything different. I also made the mistake of long pressing on the overview of the activity and choosing Refresh data which ended up truncating the map overview.

In the end I ran the following SQL to export it, before wrapping it in the XML boilerplate @MarsFlyer posted.

SELECT 
'<trkpt lat="' || ZLATITUDE 
|| '" lon="' || ZLONGITUDE 
|| '"><ele>' || ZALTITUDE 
|| '</ele><time>' 
|| strftime('%Y-%m-%dT%H:%M:%SZ',978303600+3600+ZTIMESTAMP,'unixepoch')
|| '</time>' AS GPX1
,'<extensions><gpxtpx:TrackPointExtension><gpxtpx:hr>' AS HR1
, (SELECT ZHEARTRATE
	FROM ZWFHEARTRATESAMPLE H1
	WHERE H1.ZWORKOUTDATA = L.ZWORKOUTDATA
	AND H1.ZTIMESTAMP = (
		SELECT MAX(H2.ZTIMESTAMP)
		FROM ZWFHEARTRATESAMPLE H2
		WHERE H2.ZWORKOUTDATA = L.ZWORKOUTDATA
		AND H2.ZTIMESTAMP <= L.ZTIMESTAMP 
)) AS HRVALUE
,'</gpxtpx:hr></gpxtpx:TrackPointExtension></extensions>' AS HR2
,'</trkpt>' AS GPX2
FROM ZWFLOCATIONSAMPLE AS L
WHERE ZWORKOUTDATA in  (78)
ORDER BY ZTIMESTAMP, Z_PK

I couldn't work out how to get the proper cadence data for running; the values of ZTOTALSTRIDES in ZWFFOOTPODSAMPLE didn't seem to match with data I exported from Strava to compare with, so I just left it out.

I should also point out that once I had a GPX file with the correct route, I used GOTOES to combine it with the .fit file from the Wahoo, containing the compromised cadence data. This actually ended up about as well as I could hope for, giving me a good compromise of the data sources:

(showing pace, GAP, heart rate, and cadence)

Thanks, that method worked for me as well.

Hi all! If I sent someone my file and $15 (price negotiable :) ) would you be able to help fix this in my file and send back? I unfortunately have no experience in sql/coding/etc but am OCD about getting all my miles in my ride

@allisonsc27, lol, I can empathize with the OCD, especially if those miles matter for the 2022 final balance :-)
Can you upload your file somewhere (e.g. dropbox) we can download it, if there's no personal data in it?

@allisonsc27, lol, I can empathize with the OCD, especially if those miles matter for the 2022 final balance :-) Can you upload your file somewhere (e.g. dropbox) we can download it, if there's no personal data in it?

Thanks, Marco! Here's the ride. https://drive.google.com/file/d/1FIyf-zR_Hpnahyp0fwFk8xEClwlmGCXv/view?usp=sharing. It'll only add about 10 slow mountain bike miles onto my ride but decided to try the Rapha festive 500 this year and every mile counts :)

Hi @marcov, @MarsFlyer & all,
Thanks for this info - I have been digging into the WF DB as I have missed some lap points on some recent rides and would like to add them back in before uploading to Strava. (I was also thinking of creating an auto lap script as well). There's very little info available on hacking the WF db - so this discussion is appreciated

I can only assume that Wahoo are using Jan 1, 2001 (1st day of millennium) as their epoch hence +31 years from the unix epoch (so will it break in 2069?)

Python script I am using to dump a sample (default n=100) of the DB to Excel sheets and tables for anyone stumbling across this gist. If no workout is specified will extract from the start. (caveat emptor) You can use fetch=-1 to get everything

import sqlite3
from pathlib import Path
import xlsxwriter


def dumptoexcel(cursor, excelpath, fetch=100, workout=None):
    """Dump Wahoo sqlite database to excel sheets and tables"""
    # Helper to remove bytes-like objects
    def clean(data):
        data = [list(i) for i in data]  # convert to lists
        for row, i in enumerate(data):
            for col, j in enumerate(i):
                if isinstance(j, (bytes, bytearray)):
                    data[row][col] = ""
        return data

    workbook = xlsxwriter.Workbook(excelpath)

    # Iterate tables
    for table in gettables(cursor):
        # Collect data - if this is none we skip creating worksheet
        headers = [
            item[0]
            for item in cursor.execute(
                f"SELECT p.name FROM pragma_table_info('{table}') p"
            ).fetchall()
        ]

        if workout and "ZWORKOUTDATA" in headers:
            data = cursor.execute(
                f"SELECT * FROM '{table}' WHERE ZWORKOUTDATA={workout} LIMIT {fetch}"
            ).fetchall()
        else:
            data = cursor.execute(f"SELECT * FROM '{table}' LIMIT {fetch}").fetchall()

        if data:
            data = clean(data)
            # Headers as dicts for Excel tables
            headers = [{"header": item} for item in headers]

            worksheet = workbook.add_worksheet(table)            
            worksheet.add_table(
                0, 0, len(data), len(headers) - 1, {"columns": headers, "data": data}
            )

    workbook.close()

    return True


def gettables(cursor):
    return [table[1] for table in cursor.execute("PRAGMA table_list;").fetchall()]


if __name__ == "__main__":
    dbpath = Path.cwd() / "WorkoutData.sqlite"
    cursor = sqlite3.Connection(dbpath).cursor()
    excelpath = Path.cwd() / "wahoo_sqlite_excel_dump.xlsx"
    dumptoexcel(cursor, excelpath, workout=20)

I have written an sqlite query that can extract a workout (active segments) as a complete GPX here -> link

The query uses the variable :workout to select the workout from the database - refer to ZWFWORKOUT, Z_PK column to get the number
And :limit to restrict the number of trackpoints (I use this for testing)

Includes heart-rate, cadence and temperature based on the nearest prior timestamp to the location timestamp. I have done quite a bit of testing to optimise the selections as some of the tables have a lot of data but no index on timestamp (ZWFATMOSPRESSURESAMPLE I'm looking at you!). I also managed to choose data on absolute closest timestamp, however this was very slow and comparing to Wahoo .fit data it appears the app uses the timestamp prior method as well
EDIT I have updated so that if extensions (hr, cadence & temp) are not available it will still return a GPX valid row

I have included the Garmin Trackpoint Extension V1 for cadence even though Strava says it won't parse yet it turns up in their own GPX output - yet to be tested
Also note that I have taken the cadence sample over ~3 seconds to smooth out spikes

Hello everyone
Any idea why my WorkoutData.sqlite file is made of empty tables ? There's no data in it
Thanks

Anybody had the problem of not being able to find the sqlite file? I connected my Elemnt Bolt v1 to a PC running Ubuntu 20.04, it's mounted by MTP, I can see a bunch of directories ("maps", "exports"), but browsed through each (and I've ran find | grep -i sql), can't seem to see a sqlite file - I can see a bunch of map files, and a bunch of .fit files, but that's about it.

Edit - have also tried connecting with adb and running $ adb shell ls -lR | grep -i sql, all I can find is /data/data/com.wahoofitness.bolt/databases/BoltApp.sqlite (which has a very different table structure) and a few .so libs.