Timeouts and Errors When Modifying geofence and charging cost via Web Interface

[djpalmis]

Is there an existing issue for this?

• I have searched the existing issues

What happened?

[NOT AN ISSUE WITH NEW VERSION, PROBLEM PRESENT BEFORE]

I am experiencing persistent issues with TeslaMate when attempting to modify charges or geofences via the web interface.

Specifically:

1. Updating the cost of a charge via the web interface causes TeslaMate charging cost web page to freeze
2. Deleting or modifying geofences via the web interface fails with timeout errors.

Running SQL commands directly in the database works perfectly for both modifying costs and deleting geofences.
The database appears healthy and responds well to manual queries.

Expected Behavior

Changes to charges or geofences should be applied without delays or errors, similar to manual SQL operations.

Steps To Reproduce

1. Attempt to modify the cost of a charge from the web interface.
2. Alternatively, try deleting a geofence from the web interface.
3. Observe timeouts and errors in TeslaMate logs.

Relevant log output

2024-11-24 12:39:33.848 [error] GenServer #PID<0.3062.0> terminating
** (DBConnection.ConnectionError) tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    (teslamate 1.32.0) lib/teslamate/locations.ex:154: TeslaMate.Locations.apply_geofence/2
    (teslamate 1.32.0) lib/teslamate/locations.ex:207: anonymous fn/2 in TeslaMate.Locations.delete_geofence/1
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1400: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.7.0) lib/db_connection.ex:1756: DBConnection.run_transaction/4
    (teslamate 1.32.0) lib/teslamate_web/live/geofence_live/index.ex:32: TeslaMateWeb.GeoFenceLive.Index.handle_event/3
    (phoenix_live_view 0.20.17) lib/phoenix_live_view/channel.ex:508: anonymous fn/3 in Phoenix.LiveView.Channel.view_handle_event/3

Screenshots

No response

Additional data

What I’ve Tried

1. Analyzed database health:

No signs of corruption or issues with table structure.

Direct SQL operations (e.g., updating costs or deleting geofences) complete successfully.

2. Checked logs:

Web interface logs show timeout errors (see below).

Database logs show no critical errors during manual SQL commands.

3. Increased timeout:

Updated DATABASE_TIMEOUT in docker-compose.yml to 120000. No effect on the behavior.

4. Optimized the database:

Verified table indexes.

Ran VACUUM FULL and ANALYZE to ensure optimal performance.

Type of installation

Docker

Version

v1.32.0

[cwanja]

Did it just start happening in 1.32.0?

What is the amount you are trying to insert?

What is the hardware that you are running TeslaMate on?

What version of PSQL are you running?

Just tested on a Raspberry Pi 3b+, 1.32.0 (updated moments before), no issues.

[djpalmis]

Not an issue introduced by this new version!
Sorry, I should have said that before!
It is an issue that I have since June but only now I "learned" how to "debug" my database.

It is a VM Instance on Google Cloud e2-micro (yeah it is not good but it worked before june)
PostgreSQL v17.2
Docker Compose v2.27.1

About the amount: I can't even select the textbox because the page is frozen like it is waiting some background data.

If i try to delete a geofence location it just refresh the page with the geofence still there.

[cwanja]

Can you create a new geofence from the web interface? Can you modify the settings for the car from the web interface?

How large is the database, if you know? And an estimate of how many charges the database has?

I tend to lean towards a service / hosting issue. Although, my Pi 3b+ has to be weaker than your gCloud VM. Can you see any load increases from a VM monitoring perspective when you try to edit a charge / geofence?

[djpalmis]

Can you create a new geofence from the web interface?
No, I cannot create a new geofence from the web interface.
When I try to create or delete a geofence through the web interface, TeslaMate hangs and I get a "Bad Gateway" page. (See below)
However, I can successfully perform these actions using direct SQL commands on the database.

Can you modify the settings for the car from the web interface?
I'm unsure what is meant by "settings for the car." If this refers to changing the vehicle's nickname, configuration, or similar options via TeslaMate's web interface, I can check this and provide more details if needed. Please clarify if this is what you mean.

How large is the database, if you know?
The database size is approximately 1.8 GB, and the positions table is the largest, occupying around 1.1 GB.

Here are the full database statistics:
Table | Size
--------------------+---------
positions | 1164 MB
charges | 104 MB
addresses | 912 kB
drives | 768 kB
states | 528 kB
charging_processes | 352 kB
cars | 96 kB
updates | 40 kB
settings | 32 kB
tokens | 32 kB
schema_migrations | 24 kB
car_settings | 24 kB
geofences | 24 kB
(13 rows)

How many charges does the database have?

teslamate=# ANALYZE VERBOSE charging_processes;
INFO: analyzing "public.charging_processes"
INFO: "charging_processes": scanned 24 of 24 pages, containing 1428 live rows and 30 dead rows;
1428 rows in sample, 1428 estimated total rows ANALYZE

teslamate=# ANALYZE VERBOSE geofences;
INFO: analyzing "public.geofences"
INFO: "geofences": scanned 1 of 1 pages, containing 14 live rows and 2 dead rows;
14 rows in sample, 14 estimate d total rows ANALYZE

teslamate=# SELECT 'addresses', COUNT() FROM addresses
teslamate-# UNION ALL
teslamate-# SELECT 'car_settings', COUNT() FROM car_settings
teslamate-# UNION ALL
teslamate-# SELECT 'cars', COUNT() FROM cars
teslamate-# UNION ALL
teslamate-# SELECT 'charges', COUNT() FROM charges
teslamate-# UNION ALL
teslamate-# SELECT 'positions', COUNT() FROM positions
teslamate-# UNION ALL
teslamate-# SELECT 'charging_processes', COUNT() FROM charging_processes
teslamate-# UNION ALL
teslamate-# SELECT 'drives', COUNT() FROM drives
teslamate-# UNION ALL
teslamate-# SELECT 'states', COUNT() FROM states;
?column? | count
--------------------+----------
addresses | 613
car_settings | 1
cars | 1
charges | 571633
positions | 10982683
charging_processes | 1428
drives | 2062
states | 4992

Can you see any load increases from a VM monitoring perspective when you try to edit a charge or geofence?
I have noticed significant load increases on the VM during these operations. However, my VM is a Google Cloud e2-micro instance, which has limited CPU and memory resources. Here it is when I tried to create a new geofence.

At this moment a "bad gateway" message is displayed.
To fix it I ran:
sudo docker compose restart teslamate

Despite this, direct SQL operations on the database (e.g., creating, updating, or deleting geofences or charges) work without any issues or delays. The problems only occur through the web interface.

[Techtobi83]

Hi, same here. After years of usage, today i wanted to add a new goe-fence via web interface and had the same issues.

My teslamate is working on a synology NAS for 3 years now, Version 1.32.0

[cybervince2000]

Hi. Same issue here.
Version 1.32.0 but it doesn't seem to be new. Hosted in a virtual machine (in a Freebox Delta) on docker.

I can see this error in database log while i'm trying to rename a geofence :

2024-12-05 12:34:25.567 UTC [13911] ERROR:  canceling statement due to user request
2024-12-05 12:34:25.567 UTC [13911] STATEMENT:    UPDATE drives m
	  SET end_geofence_id = (
	    SELECT id
	    FROM geofences g
	    WHERE
	      earth_box(ll_to_earth(g.latitude, g.longitude), g.radius) @> ll_to_earth(p.latitude, p.longitude) AND
	      earth_distance(ll_to_earth(g.latitude, g.longitude), ll_to_earth(latitude, p.longitude)) < g.radius AND
	      g.id != $4
	    ORDER BY
	      earth_distance(ll_to_earth(g.latitude, g.longitude), ll_to_earth(latitude, p.longitude)) ASC
	    LIMIT 1
	  )
	  FROM positions p
	  WHERE
	    m.end_position_id = p.id AND
	    earth_box(ll_to_earth($1::numeric, $2::numeric), $3) @> ll_to_earth(p.latitude, p.longitude) AND
	    earth_distance(ll_to_earth($1::numeric, $2::numeric), ll_to_earth(latitude, p.longitude)) < $3

2024-12-05 12:34:25.569 UTC [13911] LOG:  could not send data to client: Broken pipe
2024-12-05 12:34:25.569 UTC [13911] FATAL:  connection to client lost

Maybe a timeout between Teslamate and Database which has become too big.
Maybe something to optimize ?

Edit :
I deleted one of the 3 cars in my instance, reducing the database size about the half. And now, no timeout anymore.

[brianmay]

Looks like it is attempting to update all existing drives. Wonder if we really need to do this. Maybe this could be moved to some non-time-sensitive command (the above is time sensitive because the web request needs to return in reasonable time frame).

As a possible alternative, wonder if proper GIS based indexes with postgis might help. But possibly overkill just for this one operation.

[djpalmis]

I just want to share a command line workaround for people who can't set the charging cost.
A simple script.
Day by day I'm entering old charging prices of my old sessions via command line.

#!/usr/bin/env bash

# Script per impostare il costo di una ricarica nel database TeslaMate
# Utilizzo: ./set_charging_cost.sh CHARGING_PROCESS_ID COST
#
# Esempio:
# ./set_charging_cost.sh 1452 10.43
#
# Nota: Utilizzare il punto (.) come separatore decimale per il COST.

# Verifica che siano stati forniti due argomenti
if [ $# -ne 2 ]; then
    echo "Utilizzo: $0 CHARGING_PROCESS_ID COST"
    exit 1
fi

CHARGING_ID="$1"
COST="$2"

echo "Stai per impostare il costo della ricarica con ID $CHARGING_ID a €$COST."
read -p "Confermi? (s/N): " CONFIRM

if [[ "$CONFIRM" =~ ^[sS]$ ]]; then
    # Esegui l'UPDATE nel database
    # Modifica 'YOURDATABASE' con il nome del tuo container DB se necessario.
   
    sudo docker exec -it YOURDATABASE \
    psql -U teslamate -d teslamate -c "UPDATE charging_processes SET cost = ${COST} WHERE id = ${CHARGING_ID};"

    if [ $? -eq 0 ]; then
        echo "Costo aggiornato correttamente."
    else
        echo "Si è verificato un errore durante l'aggiornamento del costo."
    fi
else
    echo "Operazione annullata."
fi

[djpalmis]

Hi all,

I managed to fix all my previous charges without costs using the command-line script. Upon investigation, I found that the first entry without an associated cost was on April 20.

However, this doesn't necessarily point to a specific cause: I don’t set charging costs every day, and I don’t always update TeslaMate immediately after a release. As a result, I can’t confidently determine which TeslaMate version might have introduced this strange behavior.

That said, my attention was drawn to this commit: da81905.

Could this change be related to the issue I’ve been experiencing?

[jblum66]

Hi, same problem using web browsers on PC (Firefox/Chrome). I get a "Bad request" error. Page is partialy loaded.
I cannot create/edit a geofence using Firefox on Android too, but it does work with Chrome on Android.

[djpalmis]

but it does work with Chrome on Android.

Not in my case 🤷🏻‍♂️
Textbook box is ghosted.
If I force it using a physical keyboard price is not updated.
I noticed that the date is not showed (I don't remind if it was in past)