500 internal server error after upgrade

pewi81 commented 6 years ago

Mycodo Issue Report:

Specific Mycodo Version: 5.4.19

Problem Description

Please list: Error 1: During update there was an error, the command suggested didn't work (command unknown). See error code 1 below. However, the daemon works and I can access the UI.

Error 2: After update, when I try to access certain pages such as Input, Output and Math I get the 500 internal server error, see error code 2 below.

what were you trying to do Access Input or Output or Math page
specific setup details that are involved

Errors

List any errors you encountered.
Copy and pasting crash logs, or link to any specific code lines you've isolated (please use GitHub permalinks for this)

Error 1 (during update):


[2017-12-16 14:26:23] Downloading latest Mycodo version to /home/pi/mycodo-5.4.19.tar.gz...Done.
[2017-12-16 14:26:23] Creating /tmp/Mycodo-5.4.19...Done.
[2017-12-16 14:26:24] Extracting /home/pi/mycodo-5.4.19.tar.gz to /tmp/Mycodo-5.4.19...Done.
[2017-12-16 14:26:24] Removing /home/pi/mycodo-5.4.19.tar.gz...Done.
[2017-12-16 14:26:24] Copying /tmp/Mycodo-5.4.19/.upgrade status file to /tmp/Mycodo-5.4.19...Done.
[2017-12-16 14:28:07] Moving env directory...Done.
[2017-12-16 14:28:12] Copying databases from /home/pi/Mycodo/databases/ to /tmp/Mycodo-5.4.19/databases...Done.
[2017-12-16 14:28:12] Copying flask_secret_key from /home/pi/Mycodo/databases/ to /tmp/Mycodo-5.4.19/databases...Done.
[2017-12-16 14:28:12] Copying statistics ID...Done.
[2017-12-16 14:28:24] Copying SSL certificates...Done.
[2017-12-16 14:28:24] #### Stage 1 of 2 Complete ####
[2017-12-16 14:28:26] 
[2017-12-16 14:28:26] #### Continuing Upgrade: Stage 2 of 2 ####
[2017-12-16 14:28:26] 
[2017-12-16 14:28:26] Moving old Mycodo from /home/pi/Mycodo to /var/Mycodo-backups/Mycodo-backup-2017-12-16_14-24-07-5.4.15...Done.
[2017-12-16 14:28:45] Moving new Mycodo from /tmp/Mycodo-5.4.19 to /home/pi/Mycodo...Done.
[2017-12-16 14:29:22] 
[2017-12-16 14:29:22] #### Upgrading Mycodo database with alembic
[2017-12-16 14:30:12] Failed: Error while updating database with alembic.
[2017-12-16 14:30:12] 
[2017-12-16 14:30:12] 
[2017-12-16 14:30:12] There was an error during the upgrade.
[2017-12-16 14:30:12] Initial steps to try to fix:
[2017-12-16 14:30:12] 1. Reboot
[2017-12-16 14:30:12] 2. If that doesn't fix the issue, run the following command:
[2017-12-16 14:30:12] sudo /bin/bash ~/Mycodo/mycodo/scripts/upgrade_commands.sh upgrade
[2017-12-16 14:30:12] 3. If that command returns that you are running the latest version, run the following command:
[2017-12-16 14:30:12] sudo /bin/bash ~/Mycodo/mycodo/scripts/upgrade_post.sh
[2017-12-16 14:30:12]  ```

**Error 2 (after update):**

Error 500: Internal Server Error

Something bad happened but it's probably not your fault. Letting the developers know about these issues is crucial to supporting Mycodo. Please submit a new issue on GitHub with the following error traceback (copy the entire traceback):

Error (Full Traceback):

Traceback (most recent call last):
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/flask_login/utils.py", line 228, in decorated_view
    return func(*args, **kwargs)
  File "/var/www/mycodo/mycodo/mycodo_flask/page_routes.py", line 836, in page_output
    Conditional.conditional_type == 'relay').all()
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2724, in all
    return list(self)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2876, in __iter__
    return self._execute_and_instances(context)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2899, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/var/www/mycodo/env/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (sqlite3.OperationalError) no such column: conditional.math_id [SQL: u'SELECT conditional.id AS conditional_id, conditional.name AS conditional_name, conditional.conditional_type AS conditional_conditional_type, conditional.is_activated AS conditional_is_activated, conditional.sensor_id AS conditional_sensor_id, conditional.math_id AS conditional_math_id, conditional.if_relay_id AS conditional_if_relay_id, conditional.if_relay_state AS conditional_if_relay_state, conditional.if_relay_duration AS conditional_if_relay_duration, conditional.if_sensor_period AS conditional_if_sensor_period, conditional.if_sensor_measurement AS conditional_if_sensor_measurement, conditional.if_sensor_edge_select AS conditional_if_sensor_edge_select, conditional.if_sensor_edge_detected AS conditional_if_sensor_edge_detected, conditional.if_sensor_gpio_state AS conditional_if_sensor_gpio_state, conditional.if_sensor_direction AS conditional_if_sensor_direction, conditional.if_sensor_setpoint AS conditional_if_sensor_setpoint \nFROM conditional \nWHERE conditional.conditional_type = ?'] [parameters: ('relay',)]

### Steps to Reproduce the issue:
How can this issue be reproduced?
 1. step 1
 2. step 2...
 3. etc

### Additional Notes
Is there anything that should be added to make it easier
to address this issue?

kizniche commented 6 years ago

What version were you upgrading from?

pewi81 commented 6 years ago

Sorry, my mistake, it was v5.4.15

kizniche commented 6 years ago

Okay. you're probably experiencing an issue related to a database upgrade problem I resolved recently. If you care about your stored sensor data and want to continue using your current database, you'll have to manually update it, which can be pretty involved. Otherwise you can attempt to have alembic upgrade the database to see what the specific error is (or it may actually upgrade it properly). The last option is to delete your database and have Mycodo generate a new one and start over.

Try these commands to see if the database can be upgraded:

cd ~/Mycodo/databases
~/Mycodo/env/bin/alembic upgrade head

pewi81 commented 6 years ago

Okay, so I got this when I tried to upgrade the database manually:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 234283cc67f4 -> b9712d4ec64e, Add Math controller
Traceback (most recent call last):
  File "/home/pi/Mycodo/env/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/util/compat.py", line 141, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 70, in <module>
    run_migrations_online()
  File "alembic/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/home/pi/Mycodo/databases/alembic/versions/b9712d4ec64e_add_math_controller.py", line 21, in upgrade
    batch_op.add_column(sa.Column('math', sa.Text))
  File "/usr/lib/python2.7/contextlib.py", line 24, in __exit__
    self.gen.next()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/operations/base.py", line 300, in batch_alter_table
    impl.flush()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/operations/batch.py", line 57, in flush
    fn(*arg, **kw)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 172, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: math [SQL: u'ALTER TABLE displayorder ADD COLUMN math TEXT']

kizniche commented 6 years ago

Please paste the output of:

cd ~/Mycodo/databases
~/Mycodo/env/bin/alembic history

kizniche commented 6 years ago

I think I just came up with an easy solution. You'll just have to give me some info about where your database currently is.

pewi81 commented 6 years ago


8b36095c6cf9 -> d36de7e4e477, Add math_id option to conditionals
b9712d4ec64e -> 8b36095c6cf9, Add Math type: Input Verification
234283cc67f4 -> b9712d4ec64e, Add Math controller
8828a0074a44 -> 234283cc67f4, Add device option for camera
3dcf34dd7caf -> 8828a0074a44, Add Max Age option to LCD lines
321038f2e101 -> 3dcf34dd7caf, Add upgrade check to daemon
d0757b2ecd33 -> 321038f2e101, Add more Graph options
3c7c2b12389d -> d0757b2ecd33, Add unique_id for remote table
70b7016e107f -> 3c7c2b12389d, Add options to cycle multiple displays on one LCD
589ab40606d3 -> 70b7016e107f, Add user language option and delete misc language option
214c6bb4603a -> 589ab40606d3, Remove camera type, only use library instead
f4c0693f12a4 -> 214c6bb4603a, Add method_id as option for new PWM Timer
56ec4ceb9dfd -> f4c0693f12a4, Add sample time option for PWM and RPM Inputs
a0c55d19384c -> 56ec4ceb9dfd, Add PWM and RPM Input options
08a36ebf1a82 -> a0c55d19384c, Remove PID type option
ca975c26965c -> 08a36ebf1a82, Add Sensor option to inverse ADC unit scale
66db29288333 -> ca975c26965c, Add LCD I2C bus option
25676b9d5856 -> 66db29288333, Add new sensor type command
ed7e979852fa -> 25676b9d5856, Add option to end duration PID method with repeats after duration
c7b4a120a7bb -> ed7e979852fa, Add graph type
b604cf735be5 -> c7b4a120a7bb, add MH-Z19 CO2 sensor
9d7631079ac1 -> b604cf735be5, add options for PWM output
a1fcd7a4adf2 -> 9d7631079ac1, Add options for wireless relay control
a9a330ea0ccb -> a1fcd7a4adf2, Add calibration_sensor_measure option for Atlas Scientific pH sensors
f1c6b2901d45 -> a9a330ea0ccb, Add UART options: baud rate and device location
059a47f950b8 -> f1c6b2901d45, Add resolution and sensitivity to sensor table (for BH1750 sensor)
<base> -> 059a47f950b8, add option to show/hide tooltips```

kizniche commented 6 years ago

Try this and let me know the output:

cd ~/Mycodo/databases
~/Mycodo/env/bin/alembic stamp b9712d4ec64e
~/Mycodo/env/bin/alembic upgrade head

pewi81 commented 6 years ago


INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade b9712d4ec64e -> 8b36095c6cf9, Add Math type: Input Verification
Traceback (most recent call last):
  File "/home/pi/Mycodo/env/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/util/compat.py", line 141, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 70, in <module>
    run_migrations_online()
  File "alembic/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/home/pi/Mycodo/databases/alembic/versions/8b36095c6cf9_add_math_type_input_verification.py", line 27, in upgrade
    batch_op.add_column(sa.Column('pressure_pa_measure', sa.FLOAT))
  File "/usr/lib/python2.7/contextlib.py", line 24, in __exit__
    self.gen.next()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/operations/base.py", line 300, in batch_alter_table
    impl.flush()
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/operations/batch.py", line 57, in flush
    fn(*arg, **kw)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 172, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/pi/Mycodo/env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: max_difference [SQL: u'ALTER TABLE math ADD COLUMN max_difference FLOAT']

kizniche commented 6 years ago

We have to go through each one until we get it right. Only a few more to try:

cd ~/Mycodo/databases
~/Mycodo/env/bin/alembic stamp 8b36095c6cf9
~/Mycodo/env/bin/alembic upgrade head

pewi81 commented 6 years ago

It worked, I can access all pages now, thank!


INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.migration] Running stamp_revision b9712d4ec64e -> 8b36095c6cf9
pi@raspberrypi:~/Mycodo/databases $ ~/Mycodo/env/bin/alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 8b36095c6cf9 -> d36de7e4e477, Add math_id option to conditionals
INFO  [alembic.runtime.migration] Running upgrade d36de7e4e477 -> 41fbe7fcc8b0, Add decimal places option to LCD lines
pi@raspberrypi:~/Mycodo/databases $

kizniche commented 6 years ago

Nice. It should now be working.

kizniche commented 6 years ago

It worked, I can access all pages now, thank!

Ah, I must have missed this when I made my reply. Good to hear. This is a rather bad bug in the database upgrade system (alembic). I'm still scratching my head as to whether I've fixed it.

kizniche / Mycodo