Upgrade to 1.77

[aszumski]

I have problem updating SQL. Firs t I updated running php migrate_controller.php php update_db.php that messed it up tottaly below database that was created during update_db

pihome_2020-09-17_15-44-45.zip

then reversed to some old backup I had

pihome_2020-06-17_00-33-53.zip

and run

run migrate_db.php
run migrate_controller.php

migrates without errors but there is something wrong with database, I can see zones sensors are not associated with zone and cant add them to zone, cant see any schedules. It's total mess

[pihome-shc]

@aszumski you can have to update database first, and then you can run migrate_db.php and migrate_controller.php and this should work, i will try to work on your database later.

[twa127]

Hi,

I tried loading your pihome_2020-06-17_00-33-53.zip, then ran migrate_db.php, then ran migrate_controller.php and the database looked okay.

Make sure you have the current copies of migrate_db.php, migrate_views.sql, migrate_controller.php and migrate_controller_view.sql

1. Re-load database from pihome_2020-06-17_00-33-53.zip
2. Run migrate_db.php
3. Run migrate_controller.php

DO NOT run update_db.php

[pihome-shc]

@aszumski did you fix your issue?

[baseline-s]

I had the same problem when I upgraded and had to fix zone_controllers and zone_sensors by hand. They weren't populated during the migrate script. I could not get the above sequence to work with my backup db.

[twa127]

@baseline-s can you post a copy of your backup database please and I'll try to reproduce your issue

[baseline-s]

pihome_2020-09-11_10-52-17

Thanks, please let me know if you find anything odd. I upgraded the code to the latest version, tried all upgrade/migrate sequences which were all unsuccessful. Had a look at the migrate scripts and replicated the tables that were missing. All seems in order now apart from the slow response times that I'm attributing to the ModelB that's running this code.

[twa127]

Thanks for the database, looking at the zone table fields

The controller fields are missing and hence migrate_db.php cannot function correctly. The migration process assumes that the zone table is in the original format

My guess is that perhaps you ran update_db.php before running the migration or pulled the updated database format and this dropped the controller columns, this appears to have been a common problem and is why for that latest change, I have not yet updated the github copies of pihome_mysql_database.sql and MySQL_View.sql

[baseline-s]

Thanks, I had originally run update_db as per release notes. It's quite possible that this backup was done post that update. I've dumped all the others after I got this running again.

[twa127]

These radical changes to the database structure have made the update process difficult, it might be better to move the database files on github back one release and then update_db.php followed by migrate_db.php followed by migrate_controller.php should always work

[aszumski]

In my case zone_sensors table is empty after running procedure described.

I get: PHP Notice: Undefined variable: max_operation_time in /var/www/MySQL_Database/migrate_db.php on line 204 multiple times when running migrate_db.

Also email popup doesn't want to go away need to enter address to the database manually.

[aszumski]

Probably my old database didn't have it but boiler table is missing overrun column, and I don't have network_settings table that boiler php is looking for.

[twa127]

loaded pihome_2020-06-17_00-33-46.sql, ran migrate_db.php, then ran migrate_controller.php

Tables created as shown

[aszumski]

Mine zone_sensors is empty, tried it couple of times, even dumped all the views and tables manually to make sure there are no leftovers, import only dumps the tables that are in the import file.

[twa127]

very strange, can you please check you are using the correct version of migrate_db.php because you should not be getting PHP Notice: Undefined variable: max_operation_time in /var/www/MySQL_Database/migrate_db.php on line 204 multiple times when running migrate_db.

The current version of migrate_db.php does not have any reference to max_operation_time

[aszumski]

line 204 from github:

                    $query = "INSERT INTO `zone_sensors`(`sync`, `purge`, `zone_id`, `max_c`, `hysteresis_time`, `sp_deadband`, `sensor_id`, `sensor_child_id`)  VALUES ('0', '0', '{$id}','{$max_c}','{$max_operation_time}','{$hysteresis_time}','{$sp_deadband}','{$sensor_id}','{$sensor_child_id}');";

has reference to max operation time, but variable is not used anywhare else in the script

[twa127]

That's a bug, can you please re-run the migration using the attached migrate_db.zip

[aszumski]

OK that generated zone_sensors, but still I have some issues probably because database I've migrated didn't have latest format. I can see schedules in database, but not on schedules screen, I can see holidays, and if I edit it I'm missing sunset column.

What's network_settings table structure and what data should be there?

Is there way to align this database with what's required.

Would running update_db.php add missing fields/tables?

[twa127]

Please try in the following order -

1. load pihome_2020-06-17_00-33-46.sql
2. run migrate_db.php
3. run update_db.php
4. run migrate_controller.php

[twa127]

The network settings table is mainly to support Tasmota devices and all you really need is your local routers gateway address for wlan0

[aszumski]

@twa127 thanks for help That seems to be OK now I only get some foreign keys errors piconnect and piconnect_logs during update.

But for some reason still can't get rid of that email prompt. Need to put it into user database manually.

[aszumski]

I would advise to add database backup to migrate_controller script before changing database structure (same as migrate and update db), that way if you run it first and mess up your database (as I did) you at least have latest backup. I had to roll back couple of months.

[twa127]

@aszumski good point added the backup as suggested, do you need anymore help fixing the database or is all good now?

[aszumski]

@twa127 thanks for the help, again. Everything seems to be working OK.