Server error on statistics page

[WatskeBart]

Running v1.6, i'm experiencing a server error when clicking the statistics page.

Error output:

Server Error

Exception:

ProgrammingError: column dsmr_datalogger_meterstatistics.dsmr_version does not exist
LINE 1: ...", "dsmr_datalogger_meterstatistics"."timestamp", "dsmr_data...
                                                             ^
Traceback:

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/handlers/base.py", line 185, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/views/generic/base.py", line 68, in view
    return self.dispatch(request, *args, **kwargs)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/views/generic/base.py", line 88, in dispatch
    return handler(request, *args, **kwargs)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/views/generic/base.py", line 155, in get
    context = self.get_context_data(**kwargs)

  File "/home/dsmr/dsmr-reader/dsmr_frontend/views/statistics.py", line 30, in get_context_data
    context_data['meter_statistics'] = MeterStatistics.get_solo()

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/solo/models.py", line 43, in get_solo
    obj, created = cls.objects.get_or_create(pk=1)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 473, in get_or_create
    return self.get(**lookup), False

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 379, in get
    num = len(clone)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 238, in __len__
    self._fetch_all()

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 1087, in _fetch_all
    self._result_cache = list(self.iterator())

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 54, in __iter__
    results = compiler.execute_sql()

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 835, in execute_sql
    cursor.execute(sql, params)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)

  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)

[dennissiemensma]

You'll need to run the database migrations. The regular deploy script should take care of that automatically, if not, try:

su - dsmr
./manage.py migrate

[WatskeBart]

Still the same server error

./manage.py migrate OUTPUT:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, dsmr_api, dsmr_backup, dsmr_consumption, dsmr_datalogger, dsmr_frontend, dsmr_mindergas, dsmr_notification, dsmr_stats, dsmr_weather, sessions
Running migrations:
  Applying dsmr_backup.0002_settings_documentation...Traceback (most recent call last):
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey"
DETAIL:  Key (id)=(51) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line
    utility.execute()
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/management/__init__.py", line 359, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/management/base.py", line 294, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/management/base.py", line 345, in execute
    output = self.handle(*args, **options)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/migrations/executor.py", line 250, in apply_migration
    self.recorder.record_applied(migration.app_label, migration.name)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/migrations/recorder.py", line 73, in record_applied
    self.migration_qs.create(app=app, name=name)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 399, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/base.py", line 796, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/base.py", line 824, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/base.py", line 908, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/base.py", line 947, in _do_insert
    using=using, raw=raw)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/query.py", line 1045, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 1054, in execute_sql
    cursor.execute(sql, params)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/dsmr/.virtualenvs/dsmrreader/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "django_migrations_pkey"
DETAIL:  Key (id)=(51) already exists.

[dennissiemensma]

What does ./manage.py showmigrations output? (as dsmr user)

[WatskeBart]

./manage.py showmigrations OUTPUT:

admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
 [X] 0008_alter_user_username_max_length
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
dsmr_api
 [X] 0001_api_settings
 [X] 0002_generate_random_auth_key
dsmr_backup
 [X] 0001_initial
 [ ] 0002_settings_documentation
dsmr_consumption
 [X] 0001_squashed_0004_recalculate_gas_consumption
 [X] 0002_verbose_text
 [X] 0003_electricity_consumption_indexes
 [X] 0004_merge_electricity_tariffs
 [X] 0005_phase_currently_delivered
 [ ] 0006_dsmr_firmware_v5
 [ ] 0007_settings_documentation
dsmr_datalogger
 [X] 0001_squashed_0005_optional_gas_readings
 [X] 0002_meta_names
 [X] 0003_telegram_checksum
 [X] 0004_phase_currently_delivered
 [X] 0005_verify_telegram_crc_setting
 [ ] 0006_dsmr_firmware_v5
dsmr_frontend
 [X] 0001_initial
 [X] 0002_recent_history_weeks
 [X] 0003_drop_history_page
 [X] 0004_chart_colors
 [X] 0005_notifications
 [X] 0006_notifications_initial
 [X] 0007_merge_electricity_tariffs
 [X] 0008_merge_electricity_tariffs_notification
 [X] 0009_docs_no_reverse_match
 [X] 0010_drop_reverse_dashboard_graphs_setting
 [X] 0011_phase_currently_delivered
dsmr_mindergas
 [X] 0001_mindergas
 [X] 0002_mindergas_notification
 [X] 0003_mindergas_next_export_datetime
dsmr_notification
 [X] 0001_initial
 [X] 0002_notificationsetting_next_notification
 [X] 0003_notification_notification
dsmr_stats
 [X] 0001_squashed_0016_drop_stats_settings
 [X] 0002_regenerate_missing_gas_data
 [X] 0003_hour_statistics_gas_default
 [X] 0004_hour_statistics_gas_default_retroactive
 [X] 0005_statistics_exportverbose_names
 [X] 0006_min_max_temperature_statistics
 [X] 0007_min_max_temperature_statistics_retroactive
 [X] 0008_meta_names
 [X] 0009_statistics_editable
dsmr_weather
 [X] 0001_weather_models
 [X] 0002_meta
 [X] 0003_next_sync_setting
 [X] 0004_next_sync_setting_retroactive
sessions
 [X] 0001_initial

[dennissiemensma]

And what do these output?

as root/sudo user: sudo -u postgres psql dsmrreader -c "select * from django_migrations where id = 51;"

sudo -u postgres psql dsmrreader -c "select * from django_migrations order by id desc limit 10;"

[WatskeBart]

sudo -u postgres psql dsmrreader -c "select * from django_migrations where id = 51;" OUTPUT:

 id |    app     |      name       |            applied
----+------------+-----------------+-------------------------------
 51 | dsmr_stats | 0008_meta_names | 2017-02-28 19:46:54.853628+01
(1 row)

sudo -u postgres psql dsmrreader -c "select * from django_migrations order by id desc limit 10;" OUTPUT:

 id |     app      |                      name                       |            applied
----+--------------+-------------------------------------------------+-------------------------------
 56 | sessions     | 0001_initial                                    | 2017-02-28 19:46:55.46507+01
 55 | dsmr_weather | 0004_next_sync_setting_retroactive              | 2017-02-28 19:46:55.339995+01
 54 | dsmr_weather | 0003_next_sync_setting                          | 2017-02-28 19:46:55.283517+01
 53 | dsmr_weather | 0002_meta                                       | 2017-02-28 19:46:55.204412+01
 52 | dsmr_stats   | 0009_statistics_editable                        | 2017-02-28 19:46:54.978699+01
 51 | dsmr_stats   | 0008_meta_names                                 | 2017-02-28 19:46:54.853628+01
 50 | dsmr_stats   | 0007_min_max_temperature_statistics_retroactive | 2017-02-28 19:46:54.727836+01
 49 | dsmr_stats   | 0006_min_max_temperature_statistics             | 2017-02-28 19:46:54.660723+01
 48 | dsmr_stats   | 0005_statistics_exportverbose_names             | 2017-02-28 19:46:54.514624+01
 47 | dsmr_stats   | 0004_hour_statistics_gas_default_retroactive    | 2017-02-28 19:46:53.575249+01
(10 rows)

Will look into this tomorrow, I have a appointment to catch. Thanks for the quick response so far 👍

[dennissiemensma]

Sure, then I'llhear from you tomorrow. The order of the migrations seem somewhat weird. The session migration # 56 (here) should be one of the first migrations installed on a fresh installation. Can you recall whether you've tried some migrations manually of some sorts?

[WatskeBart]

As I was stuck on a v1.3.1 release, because of a faulty microSD (read-only error), I was forced to do a clean installation. A the time of installation the most current version was v1.5.5 During the clean install I restored my database backups (as described here) from v1.3.1

Could the v1.3.1 restore be the issue here?

[dennissiemensma]

Hmm, the version shouldn't matter, as long as the checked out (code) version is newer than the database (schema version). You were able to upgrade all the way to v1.5.5 right?

Can you try this query (because it has mixed quotes, you can't execute it directly from command line):

sudo su - postgres

psql dsmrreader

SELECT setval(pg_get_serial_sequence('"django_migrations"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "django_migrations";

CTRL + D a few times to go back.

And try the migrations again.

As this shouldn't break anything, Be sure to have a database backup at all times.

[WatskeBart]

OUTPUT:

dsmrreader=# SELECT setval(pg_get_serial_sequence('"django_migrations"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "django_migrations";
 setval
--------
     56
(1 row)

I did a clean install with v1.5.5 and restored a v1.3.1 database during the clean install. Restore command used, during the clean install: zcat backup-postgres-Friday.sql.gz | sudo sudo -u postgres psql dsmrreader

[WatskeBart]

There are more problems upon further inspection. I'm having 3300 unprocessed telegrams, lagging 23h behind.

[dennissiemensma]

• Regarding the initial errors, did you try the migrations again after you ran that query?

• Regarding the lagging behind, it might be related to the database erros, but you'd have to check the dsmr_backend process logs for it.

[WatskeBart]

@dennissiemensma I forgot to run the ./manage.py migrate command.

The statistics page seems fine now 👍 Telegrams are getting processed properly now 👍

[dennissiemensma]

Great, once again thanks for providing the feedback.