Teslamate: Upgrading to 1.19.0 gives ERROR 22003 (numeric_value_out_of_range) smallint out of range

[grizzlyjere]

Upgraded to 1.19 (from 1.18 I believe) and it's failing to start with ERROR 22003 (numeric_value_out_of_range) smallint out of range showing in the logs. It just keeps restarting and giving the error message.

To Reproduce

To upgrade I did the following

1. docker-compose down
2. docker-compose pull
3. docker-compose up -d

Relevant entries from the logs

14:13:06.225 [info] == Running 20200410112005 TeslaMate.Repo.Migrations.DatabaseEfficiencyImprovements.change/0 forward
14:13:06.227 [info] alter table cars
14:13:06.345 [info] alter table addresses
14:13:06.413 [info] alter table charging_processes
** (Postgrex.Error) ERROR 22003 (numeric_value_out_of_range) smallint out of range
    (ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.10.2) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.4.2) lib/ecto/adapters/sql.ex:705: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.4.2) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.4.2) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.10.2) lib/enum.ex:2111: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.4.2) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.12.1) timer.erl:166: :timer.tc/1

Operating environment

• TeslaMate Version: 1.19.0
• Type of installation: Docker
• OS: Ubuntu

[adriankumpf]

Could you check which values are affected?

docker-compose exec database psql teslamate teslamate

SELECT id,start_battery_level,end_battery_level,duration_min,car_id FROM charging_processes WHERE start_battery_level<-32768 OR 32767<start_battery_level OR end_battery_level<-32768 OR 32767<end_battery_level OR duration_min<-32768 OR 32767<duration_min OR car_id<-32768 OR 32767<car_id; 

SELECT id,speed_max,power_max,power_min,duration_min,car_id FROM drives WHERE speed_max<-32768 OR 32767<speed_max OR power_max<-32768 OR 32767<power_max OR power_min<-32768 OR 32767<power_min OR duration_min<-32768 OR 32767<duration_min OR car_id<-32768 OR 32767<car_id;

The affected rows can only contain invalid values, if possible you can remove them directly and restart the migration.

[natrlhy]

Also doesn't a docker-compose down command remove the containers and have to start from scratch if no backup is available?

[grizzlyjere]

Ran the two queries and got the following. I had done a TeslaFi import a week or so ago without any apparent issues. Maybe that's where the bad data came from?

teslamate=# SELECT id,start_battery_level,end_battery_level,duration_min,car_id FROM charging_processes WHERE start_battery_level<-32768 OR 32767<start_battery_level OR end_battery_level<-32768 OR 32767<end_battery_level OR duration_min<-32768 OR 32767<duration_min OR car_id<-32768 OR 32767<car_id;
 id  | start_battery_level | end_battery_level | duration_min | car_id
-----+---------------------+-------------------+--------------+--------
 391 |                  13 |                79 |        44818 |      1
(1 row)

teslamate=# SELECT id,speed_max,power_max,power_min,duration_min,car_id FROM drives WHERE speed_max<-32768 OR 32767<speed_max OR power_max<-32768 OR 32767<power_max OR power_min<-32768 OR 32767<power_min OR duration_min<-32768 OR 32767<duration_min OR car_id<-32768 OR 32767<car_id;
 id | speed_max | power_max | power_min | duration_min | car_id
----+-----------+-----------+-----------+--------------+--------
(0 rows)

Should I to delete row 391?

[adriankumpf]

Or just set duration_min to NULL :)

[adriankumpf]

Also doesn't a docker-compose down command remove the containers and have to start from scratch if no backup is available?

By default no volumes are removed, so it is safe.

[grizzlyjere]

Also doesn't a docker-compose down command remove the containers and have to start from scratch if no backup is available?

Good point. I should have done a stop.

[grizzlyjere]

Or just set duration_min to NULL :)

That did the trick. After that the migration completed without issue. Thanks for your help.

[adriankumpf]

Nice! Glad it worked.