Fix Database Version Mismatch Issue

This is a brief tutorial to fix a particular database version mismatch error that plagued Mycodo for several versions pre-v5.5.0, and may not immediately present itself as being an issue. The issue is typified by the Mycodo database version not matching the correct version. This was discussed in #347 and successfully resolved the database issue. It commonly presents itself as an error during the database upgrade part of a Mycodo upgrade, with errors appearing similar to the following:

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):
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: math [SQL: u'ALTER TABLE displayorder ADD COLUMN math TEXT']

Follow the following steps to attempt to attempt to fix your database:

1. Enter your database directory:

cd ~/Mycodo/databases

2. We need to identify what the correct version your database should be. This can be tricky, and requires going through a range of tests. Before you begin, create a backup of your database by copying it to another name:

cp mycodo.db mycodo.db.backup

3. Retrieve the database history with ~/Mycodo/env/bin/alembic history and observe the output. It should be similar to the following:

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

4. This is where it may get difficult. You will need to review your exact error to determine where in the database upgrade process you need to set your database version to. For instance, the above output "Running upgrade 234283cc67f4 -> b9712d4ec64e" shows the database version is "234283cc67f4" and the above error "duplicate column name: math [SQL: u'ALTER TABLE displayorder ADD COLUMN math TEXT']" indicates the "math" column already exists in the "displayorder". This suggests the database version "b9712d4ec64e" edits have already been applied if the math option is already in the display order, but the database version is one behind "234283cc67f4". We will now need to forcefully set the database version one ahead and then attempt the upgrade again.

5. Manually set the database version to the database version one ahead (above) your current version. Since our database version was 234283cc67f4, we will set it to b9712d4ec64e, then attempt an upgrade.

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

6. If your output is similar to the following (notice there are no errors), your database has successfully updated to the latest version.

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 b9712d4ec64e -> 8b36095c6cf9, Add Math type: Input Verification
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

7. If your output gave another error, such as the following, we will need to repeat the process:

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):
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: max_difference [SQL: u'ALTER TABLE math ADD COLUMN max_difference FLOAT']

8. Since setting the database one version higher (b9712d4ec64e) didn't work, we will need to set it one more version higher and attempt another upgrade:

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

9. If you see the upgrade output complete without error, congratulations, you have successfully upgraded yoru database and it should now work with Mycodo. If you see another error, you must reference the alembic history output, stamp a next version, and attempt another upgrade. Repeat this until you successfully upgrade the database.