search

pihome-shc / pihome

PiHome - Smart Heating, Ventilation and Air Conditioning (HVAC)
http://www.pihome.eu
Other
55 stars 25 forks source link

database migration #356

Closed pihome-shc closed 4 years ago

pihome-shc commented 4 years ago

if i migrate database it works, but later if try to update database i have some errors. see migration output and update 

root@pihome:/var/www/MySQL_Database# php migrate_db.php

   _____    _   _    _
  |  __ \  (_) | |  | |
  | |__) |  _  | |__| |   ___    _ __ ___     ___
  |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \
  | |      | | | |  | | | (_) | | | | | | | |  __/
  |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|

      S M A R T   H E A T I N G   C O N T R O L
****************************************************************
*   PiHome Migration Script Version 0.02 Build Date 31/07/2020 *
*   Last Modified on 31/07/2020                                *
*                                      Have Fun - PiHome.eu    *
****************************************************************
2020-08-03 19:32:52 - PiHome Database Migration Script Started
---------------------------------------------------------------
2020-08-03 19:32:52 - php version 7.3.14-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-03 19:32:52 - Testing Connection to MySQL/MariaDB Server.
2020-08-03 19:32:52 - Database Server Connection Successfull
2020-08-03 19:32:52 - Checking if Database Exits
2020-08-03 19:32:52 - Database pihome Found
2020-08-03 19:32:52 - Creating Dump File for Exiting Database.
2020-08-03 19:32:54 - Compressing Database Dump File pihome_2020-08-03_19-32-52.sql
2020-08-03 19:32:59 - Compressed Database Dump File pihome_2020-08-03_19-32-54.zip
2020-08-03 19:32:59 - Adding zone_type Table.
2020-08-03 19:33:00 - Modifying Zone Table.
2020-08-03 19:33:03 - Zone Table Successfully Modified
2020-08-03 19:33:03 - Importing the data to the zone_sensors Table and Updating the zone Table.
2020-08-03 19:33:03 - Importing Migration SQL View File to Database, This could take few minuts.
2020-08-03 19:33:03 - DataBase Views File /var/www/MySQL_Database/migrate_views.sql Imported Successfully
2020-08-03 19:33:03 - DataBase Updated Successfully
root@pihome:/var/www/MySQL_Database# php update_db.php

   _____    _   _    _
  |  __ \  (_) | |  | |
  | |__) |  _  | |__| |   ___    _ __ ___     ___
  |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \
  | |      | | | |  | | | (_) | | | | | | | |  __/
  |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|

      S M A R T   H E A T I N G   C O N T R O L
***************************************************************
*   PiHome Datase Script Version 0.02 Build Date 15/09/2019   *
*   Last Modified on 05/04/2020                               *
*                                      Have Fun - PiHome.eu   *
***************************************************************
2020-08-03 19:37:04 - PiHome Database Update Script Started
---------------------------------------------------------------
2020-08-03 19:37:04 - php version 7.3.14-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-03 19:37:04 - Testing Connection to MySQL/MariaDB Server.
2020-08-03 19:37:04 - Database Server Connection Successfull
2020-08-03 19:37:04 - Checking if Database Exits
2020-08-03 19:37:04 - Database pihome Found
2020-08-03 19:37:04 - Checking GITHUB for Database Update
2020-08-03 19:37:05 - Database Update Found on GITHUB
2020-08-03 19:37:05 - The Following Updates will be Applied to the Database.
2020-08-03 19:37:05 - ALTER TABLE `add_on_logs` MODIFY `expected_end_date_time` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `add_on_logs` MODIFY `start_cause` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `add_on_logs` MODIFY `start_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `add_on_logs` MODIFY `stop_cause` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `add_on_logs` MODIFY `stop_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `add_on_zone_logs` MODIFY `add_on_log_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `add_on_zone_logs` MODIFY `status` int(11);
2020-08-03 19:37:05 - ALTER TABLE `add_on_zone_logs` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `away` MODIFY `away_button_child_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `away` MODIFY `away_button_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `away` MODIFY `end_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `away` MODIFY `start_datetime` timestamp NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `away` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `datetime` timestamp NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `fired_status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `hysteresis_time` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `max_operation_time` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `name` char(50) CHARACTER SET utf16 COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `node_child_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `node_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `overrun` SMALLINT(6) NULL DEFAULT NULL;
2020-08-03 19:37:05 - ALTER TABLE `boiler` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boiler_logs` MODIFY `expected_end_date_time` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `boiler_logs` MODIFY `start_cause` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `boiler_logs` MODIFY `start_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `boiler_logs` MODIFY `stop_cause` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `boiler_logs` MODIFY `stop_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `boost_button_child_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `boost_button_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `minute` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `temperature` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `time` timestamp NOT NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `boost` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `command` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `comments` varchar(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `day` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `hour` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `min` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `month` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `output` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `status` char(50);
2020-08-03 19:37:05 - ALTER TABLE `crontab` MODIFY `weekday` char(50);
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `from` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `password` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `smtp` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `to` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `email` MODIFY `username` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `frost_protection` MODIFY `datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `gateway` MODIFY `find_gw` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `gateway` MODIFY `pid_running_since` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `gateway` MODIFY `pid` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `gateway` MODIFY `reboot` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `gateway` MODIFY `version` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `location` char(50) COLLATE utf16_bin COMMENT 'ip address or serial port location i.e. /dev/ttyAMA0';
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `pid_datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `pid_start_time` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `pid` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `port` char(50) COLLATE utf16_bin COMMENT 'port number or baud rate for serial gateway';
2020-08-03 19:37:05 - ALTER TABLE `gateway_logs` MODIFY `type` char(50) COLLATE utf16_bin COMMENT 'serial or wifi';
2020-08-03 19:37:05 - ALTER TABLE `holidays` MODIFY `end_date_time` datetime;
2020-08-03 19:37:05 - ALTER TABLE `holidays` MODIFY `start_date_time` datetime;
2020-08-03 19:37:05 - ALTER TABLE `holidays` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `http_messages` MODIFY `command` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `http_messages` MODIFY `message_type` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `http_messages` MODIFY `node_id` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `http_messages` MODIFY `parameter` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `http_messages` MODIFY `zone_name` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `messages_in` MODIFY `child_id` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `messages_in` MODIFY `datetime` timestamp NOT NULL;
2020-08-03 19:37:05 - ALTER TABLE `messages_in` MODIFY `node_id` char(15) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `messages_in` MODIFY `payload` decimal(10,2);
2020-08-03 19:37:05 - ALTER TABLE `messages_in` MODIFY `sub_type` int(11);
2020-08-03 19:37:05 - ALTER TABLE `messages_out` MODIFY `datetime` timestamp NOT NULL ON UPDATE current_timestamp() COMMENT 'Current datetime';
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `dns1_address` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `dns2_address` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `gateway_address` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `hostname` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `interface_num` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `interface_type` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `ip_address` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `mac_address` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `net_mask` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `network_settings` MODIFY `primary_interface` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `last_seen` timestamp NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `min_value` int(11);
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `ms_version` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `name` char(50) CHARACTER SET utf8 COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `repeater` tinyint(4) COMMENT 'Repeater Feature Enabled=1 or Disable=0';
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `sketch_version` char(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `status` char(50) CHARACTER SET utf8 COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `nodes` MODIFY `type` CHAR(50) NOT NULL COLLATE 'utf8_bin';
2020-08-03 19:37:05 - ALTER TABLE `nodes_battery` MODIFY `bat_level` decimal(10,2);
2020-08-03 19:37:05 - ALTER TABLE `nodes_battery` MODIFY `bat_voltage` decimal(10,2);
2020-08-03 19:37:05 - ALTER TABLE `nodes_battery` MODIFY `node_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `nodes_battery` MODIFY `update` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `notice` MODIFY `datetime` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `notice` MODIFY `message` varchar(200) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `notice` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `override` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `override` MODIFY `temperature` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `override` MODIFY `time` timestamp NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `override` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time` MODIFY `end` time;
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time` MODIFY `sch_name` varchar(200) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time` MODIFY `start` time;
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time_zone` MODIFY `holidays_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time_zone` MODIFY `schedule_daily_time_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time_zone` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `schedule_daily_time_zone` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climat_zone` MODIFY `schedule_night_climate_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climat_zone` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climat_zone` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climate_time` MODIFY `end_time` time;
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climate_time` MODIFY `start_time` time;
2020-08-03 19:37:05 - ALTER TABLE `schedule_night_climate_time` MODIFY `status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `backup_email` char(100) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `build` varchar(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `city` char(100) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `country` char(2) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `language` char(10) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `name` varchar(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `openweather_api` char(100) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `ping_home` bit(1);
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `reboot` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `shutdown` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `timezone` varchar(50) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `update_alias` char(100) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `update_file` char(100) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `update_location` char(250) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `version` varchar(50) CHARACTER SET latin1;
2020-08-03 19:37:05 - ALTER TABLE `system` MODIFY `zip` char(100) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `account_date` timestamp NOT NULL;
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `account_enable` tinyint(1);
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `backup` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `cpdate` timestamp NOT NULL ON UPDATE current_timestamp();
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `settings` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `support` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `user` MODIFY `users` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `userhistory` MODIFY `audit` tinytext;
2020-08-03 19:37:05 - ALTER TABLE `userhistory` MODIFY `date` datetime;
2020-08-03 19:37:05 - ALTER TABLE `userhistory` MODIFY `ipaddress` tinytext;
2020-08-03 19:37:05 - ALTER TABLE `userhistory` MODIFY `password` varchar(50);
2020-08-03 19:37:05 - ALTER TABLE `userhistory` MODIFY `username` varchar(50);
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `c` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `description` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `img` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `last_update` timestamp NOT NULL ON UPDATE current_timestamp() COMMENT 'Last weather update';
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `location` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `sunrise` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `sunset` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `title` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `weather` MODIFY `wind_speed` varchar(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY `controler_child_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY `controler_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY `index_id` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY `name` char(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `zone` ADD `state` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone` DROP `status`;
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY `type_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone` DROP `zone_state`;
2020-08-03 19:37:05 - ALTER TABLE `zone` ADD `zone_status` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone` MODIFY CONSTRAINT `FK_zone_nodes_2` FOREIGN KEY (`controler_id`) REFERENCES `nodes` (`id`);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `controler_fault` int(1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `controler_seen_time` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `mode` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `overrun` tinyint(1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `sensor_fault` int(1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `sensor_reading_time` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `sensor_seen_time` timestamp NULL;
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `status` tinyint(1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `temp_cut_in` decimal(4,1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `temp_cut_out` decimal(4,1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `temp_reading` decimal(4,1);
2020-08-03 19:37:05 - ALTER TABLE `zone_current_state` MODIFY `temp_target` decimal(4,1);
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `category` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `child_id` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `datetime` timestamp NOT NULL;
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `name` char(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `node_id` char(15) COLLATE utf16_bin;
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `payload` decimal(10,2);
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `sub_type` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `type` char(50) COLLATE utf8_bin;
2020-08-03 19:37:05 - ALTER TABLE `zone_graphs` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_logs` MODIFY `boiler_log_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_logs` MODIFY `status` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_logs` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `hysteresis_time` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `max_c` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `max_operation_time` tinyint(4);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `sensor_child_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `sensor_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_sensors` MODIFY `zone_id` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_type` MODIFY `category` int(11);
2020-08-03 19:37:05 - ALTER TABLE `zone_type` MODIFY `type` char(50) COLLATE utf8_bin;
2020-08-03 19:37:05
Existing Database Table Structures will be amended.
Are you sure you want to do this?  Type 'yes' to continue?
---------------------------------------------------------------
yes
2020-08-03 19:37:07 - Creating Dump File for Exiting Database.
2020-08-03 19:37:09 - Compressing Database Dump File pihome_2020-08-03_19-37-07.sql
2020-08-03 19:37:14 - Compressed Database Dump File pihome_2020-08-03_19-37-09.zip
2020-08-03 19:37:14 - Applying Updates to Database.
MySQL Database Error with Query ALTER TABLE `zone` MODIFY CONSTRAINT `FK_zone_nodes_2` FOREIGN KEY (`controler_id`) REFERENCES `nodes` (`id`);
:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CONSTRAINT `FK_zone_                                                                               nodes_2` FOREIGN KEY (`controler_id`) REFERENCES `nodes` (`i' at line 1
2020-08-03 19:37:16 - Database Updates Applied
2020-08-03 19:37:16 - Importing SQL Table View File to Database, This could take few minuts.
MySQL Database Error with Query CREATE VIEW schedule_daily_time_zone_view AS
select ss.id as time_id, ss.status as time_status, sstart.start, send.end, sWeekDays.WeekDays,
sdtz.sync as tz_sync, sdtz.id as tz_id, sdtz.status as tz_status,
sdtz.zone_id, zone.index_id, zone.name as zone_name, zt.`type`, ztype.category, temperature, holidays_id , coop, ss.sch_name
from schedule_daily_time_zone sdtz
join schedule_daily_time ss on sdtz.schedule_daily_time_id = ss.id
join schedule_daily_time sstart on sdtz.schedule_daily_time_id = sstart.id
join schedule_daily_time send on sdtz.schedule_daily_time_id = send.id
join schedule_daily_time sWeekDays on sdtz.schedule_daily_time_id = sWeekDays.id
join zone on sdtz.zone_id = zone.id
join zone zt on sdtz.zone_id = zt.id
join zone_type ztype on zone.type = ztype.type
where sdtz.`purge` = '0' order by zone.index_id;
:Unknown column 'zt.type' in 'field list'
MySQL Database Error with Query CREATE VIEW zone_view AS
select zone.status, zone.zone_state, zone.sync, zone.id, zone.index_id, zone.name, ztype.type, ztype.category, zone.graph_it, zs.max_c, zs.max_operation_time,                                                                                zs.hysteresis_time,
zs.sp_deadband, sid.node_id as sensors_id, zs.sensor_child_id,
ctype.`type` AS controller_type, cid.node_id as controler_id, zone.controler_child_id,
IFNULL(lasts.last_seen, lasts_2.last_seen) as last_seen, IFNULL(msv.ms_version, msv_2.ms_version) as ms_version, IFNULL(skv.sketch_version, skv_2.sketch_versi                                                                               on) as sketch_version
from zone
LEFT join zone_sensors zs on zone.id = zs.zone_id
join zone_type ztype on zone.type_id = ztype.id
LEFT join nodes sid on zs.sensor_id = sid.id
join nodes ctype on zone.controler_id = ctype.id
join nodes cid on zone.controler_id = cid.id
LEFT join nodes lasts on zs.sensor_id = lasts.id
LEFT join nodes lasts_2 on zone.controler_id = lasts_2.id
LEFT join nodes msv on zs.sensor_id = msv.id
LEFT join nodes msv_2 on zone.controler_id = msv_2.id
LEFT join nodes skv on zs.sensor_id = skv.id
LEFT join nodes skv_2 on zone.controler_id = skv_2.id
where zone.`purge` = '0';
:Unknown column 'zone.status' in 'field list'
MySQL Database Error with Query CREATE VIEW schedule_night_climat_zone_view AS
select tnct.id as time_id, tnct.status as time_status, snct.start_time as start, enct.end_time as end, snct.WeekDays,
nctz.sync as tz_sync, nctz.id as tz_id, nctz.status as tz_status, nctz.zone_id, zone.index_id, zone.name as zone_name,
ztype.`type`, ztype.category, zone.status as zone_status, nctz.min_temperature, nctz.max_temperature
from schedule_night_climat_zone nctz
join schedule_night_climate_time snct on nctz.schedule_night_climate_id = snct.id
join schedule_night_climate_time enct on nctz.schedule_night_climate_id = enct.id
join schedule_night_climate_time tnct on nctz.schedule_night_climate_id = tnct.id
join zone on nctz.zone_id = zone.id
join zone zt on nctz.zone_id = zt.id
join zone_type ztype on zone.type_id = ztype.id
where nctz.`purge` = '0' order by zone.index_id;
:Unknown column 'zone.status' in 'field list'
2020-08-03 19:37:16 - DataBase File /var/www/MySQL_Database/MySQL_View.sql Imported Successfully
2020-08-03 19:37:16 - Update Version: 1.76
2020-08-03 19:37:16 - Update Build: 020820
2020-08-03 19:37:16 - MySQL Update DataBase Script Ended
****************************************************************************************
root@pihome:/var/www/MySQL_Database#
pihome-shc commented 4 years ago

after migration it works and here are some screenshots after migration image image image image image

pihome-shc commented 4 years ago

if i run update_db.php once migrate_db.php is successful image image and zone_view does not exit

twa127 commented 4 years ago

Found error in pihome_mysql_database.sql committed a fix can you try again please

twa127 commented 4 years ago

Removes all the errors except

MySQL Database Error with Query ALTER TABLE zone MODIFY CONSTRAINT FK_zone_nodes_2 FOREIGN KEY (controler_id) REFERENCES nodes (id);

twa127 commented 4 years ago

Moved the FKs around for the zone table in pihome_mysql_database.sql this appears to have fixed the problem

pihome-shc commented 4 years ago

migrate and then updated without any issues. great work @twa127,