plex-db-fix

plex-db-fix.go

// I have Plex running from the offical Docker containers.  Versions after `plexinc/pms-docker:1.40.3.8555-fef15d30c` would cause Plex to crash with the following logs:
// 
// ```
// ERROR - SQLITE3:0x12ae04f58, 1, statement aborts at 4: [SELECT `id` FROM metadata_item_settings WHERE extra_data -> 'pv:SyncedViewAt' IS NOT NULL or extra_data -> 'pv:SyncedRatedAt' IS NOT NULL] malformed JSON
// ERROR - Exception inside transaction (inside=1) (/System/Volumes/Data/data/actions-runner/_work/plex-media-server/plex-media-server/Library/DatabaseMigrations.cpp:342): sqlite3_statement_backend::loadRS: malformed JSON
// ERROR - Exception thrown during migrations, aborting: sqlite3_statement_backend::loadRS: malformed JSON
// ERROR - Database corruption: sqlite3_statement_backend::loadRS: malformed JSON
// ERROR - Error: Unable to set up server: sqlite3_statement_backend::loadRS: malformed JSON (N4soci10soci_errorE)
// ```
// 
// It turns out the newer versions of Plex are expecting the `extra_data` column in the table `metadata_item_settings` to be either
// 
// 1. Valid JSON data, or
// 2. Set to `NULL`
// 
// In my case I had a mixture of
// 
// 1. Valid JSON data
// 2. `NULL`
// 3. Empty string
// 4. Non-JSON string
// 
// The non-JSON strings appeared to all be query strings similar to `pv%3AbulkPlayed=1541553063`.  I put this little tool together to convert empty strings to `NULL` and non-JSON strings to the proper JSON structure.

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/url"
	"os"

	_ "github.com/mattn/go-sqlite3"
)

// parseQueryString converts a URL query string into a JSON string
func parseQueryString(queryString string) string {
	decodedQuery, err := url.QueryUnescape(queryString)
	if err != nil {
		log.Fatalf("Failed to decode query string: %v", err)
	}

	values, err := url.ParseQuery(decodedQuery)
	if err != nil {
		log.Fatalf("Failed to parse query string: %v", err)
	}

	queryMap := make(map[string]string)
	queryMap["url"] = queryString

	for key, val := range values {
		queryMap[key] = val[0]
	}

	jsonData, err := json.Marshal(queryMap)
	if err != nil {
		log.Fatalf("Failed to convert to JSON: %v", err)
	}

	return string(jsonData)
}

// fixEmptyRows updates the 'metadata_item_settings' table by setting 'extra_data' to NULL where it is an empty string
func fixEmptyRows(db *sql.DB) (int64, error) {
	query := `UPDATE metadata_item_settings SET extra_data = NULL WHERE extra_data = '';`

	result, err := db.Exec(query)
	if err != nil {
		return 0, fmt.Errorf("failed to update empty extra_data values: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("failed to retrieve affected rows: %w", err)
	}

	return rowsAffected, nil
}

// fixMalformedExtraData finds rows where extra_data is not NULL and doesn't look like valid JSON, then updates each row
func fixMalformedExtraData(db *sql.DB) (int64, error) {
	query := `SELECT id, extra_data FROM metadata_item_settings WHERE extra_data IS NOT NULL AND extra_data NOT LIKE '{%';`
	rows, err := db.Query(query)
	if err != nil {
		return 0, fmt.Errorf("failed to fetch malformed extra_data: %w", err)
	}
	defer rows.Close()

	updateQuery := `UPDATE metadata_item_settings SET extra_data = ? WHERE id = ?`
	stmt, err := db.Prepare(updateQuery)
	if err != nil {
		return 0, fmt.Errorf("failed to prepare update statement: %w", err)
	}
	defer stmt.Close()

	var rowsUpdated int64
	for rows.Next() {
		var id int
		var extraData string

		err = rows.Scan(&id, &extraData)
		if err != nil {
			return 0, fmt.Errorf("failed to scan row: %w", err)
		}

		fixedData := parseQueryString(extraData)

		_, err = stmt.Exec(fixedData, id)
		if err != nil {
			return 0, fmt.Errorf("failed to update row %d: %w", id, err)
		}
		rowsUpdated++
	}

	if err = rows.Err(); err != nil {
		return 0, fmt.Errorf("error during row iteration: %w", err)
	}

	return rowsUpdated, nil
}

func main() {
	if len(os.Args) < 2 {
		fmt.Println("Usage: go run main.go <path_to_sqlite_db>")
		os.Exit(1)
	}
	dbPath := os.Args[1]

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}
	defer db.Close()

	count, err := fixEmptyRows(db)
	if err != nil {
		log.Fatalf("%v", err)
	}
	fmt.Println("Empty rows fixed:", count)

	count, err = fixMalformedExtraData(db)
	if err != nil {
		log.Fatalf("%v", err)
	}
	fmt.Println("Malformed rows fixed:", count)
}