Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
PowerHome General
 PowerHome Messageboard : PowerHome General
Subject Topic: Corrupted PH DB and Z-Wave Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
renard
Groupie
Groupie
Avatar

Joined: November 01 2009
Location: United States
Online Status: Offline
Posts: 72
Posted: March 30 2019 at 01:34 | IP Logged Quote renard

Dave,

I am having a persistent problem with creeping corruption of my PH Database. It apparently does not interfere with PH operation but it shows as a massive number of invalid entries, usually for 'Invalid Device ID(s)'. An example can be seen at:
     http://www.terryadennison.com/ file_transfers.htm

As far as I can tell, none of the error devices have addresses of real Insteon devices. There are also references to Group-IDs that are not valid. I have been manually cleaning up the DB but with many devices and many errors, this is getting tedious.

We have a fairly RF noisy house with lots of switching power supplies (wall warts) so we may have an interference problem.

Is it possible to have a PH function in the Insteon Maintenance area that automatically deletes any record that does not reference a valid device address. I have never had a corruption of the device or group-id tables.

A somewhat related request involves Smarthome and the status of Z-Wave support in PH. I just received an offer from Smarthome for a device sale- any Insteon sensor for $20. This is after an almost continuous series of sales from Smarthome. I and probably others are growing concerned about the health of Smarthome- it looks like they are clearing inventory or just desperate to raise cash. If Smarthome folds, unless an PH user has stockpiled Insteon devices, given the failure rate, users are SOL.

This brings me back to Z-Wave. There are multiple Z-Wave device providers, unlike the sole-source for Insteon.

I know your are working on an upgrade to PH to implement Z-Wave capability. My objective is not to harass you but
but rather to get some guidance regarding our preparation to use the Z-Wave capability in PH.

I have purchased a Raspberry-Pi and the Z-Wave Hat and doing some experimental work with the Raspi environment. Is there something users should be doing to prepare for Z-Wave? I am concerned that Smarthome might go under with no options available.

I know you are dealing with many feature requests such as for interfaces to Alexa-type devices. I would point out that if there are no Insteon devices available and given the failure rate of Insteon devices, PH rapidly becomes basically useless, Alexa interface or not.

Thanks as Always.

__________________
Terry
Back to Top View renard's Profile Search for other posts by renard
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: March 31 2019 at 14:00 | IP Logged Quote dhoward

Terry,

To prevent the insteon link corruption that builds over time, you'll want to uncheck the "Link Scan" and "Level/Ramp/X10 Scan" options on your Insteon
controller. You can do this from the top of Insteon Explorer (be sure to save the settings) or from the PH Explorer under PowerHome->Setup->Controllers in the
"Settings" window for your PLM.

What happens is that PowerHome will occasionally rescan the links in your Insteon devices. This happens when the "Status Poll" option comes back with a value
different from what PowerHome thinks it should be which normally indicates a change in a devices database (in which case you want to rescan the links). But, it
seems that flaky power or the phase of the moon can also trigger this value to be different and cause PowerHome to rescan. In a perfect world where the Insteon
comms are all clean, the rescan shouldn't cause any "corruption" as it would bring in the changes (if there were any) or no changes would result since the links
weren't really different. What often happens though, when reading the links, the internal insteon checksum protocol is less than perfect and one of the hex
values will not be received properly by the PLM. This confuses PowerHome if the misread hex is in the device address and PowerHome assumes this is a change in
the link to a new/different Insteon device. Links are stored based upon what record number they are in the device database and rather than overwriting the
existing (potentially good) record with a potentially bad record, PowerHome in effect makes a whole new record with the same record number. Over time, if you
have Link Scanning enabled, this can result in multiple "bad" records showing in the database requiring the user to review and decide which are good and which
are bad.

Once the links are how they should be (hopefully all good), if you disable "Link Scan", then PowerHome won't ever rescan links unless you specifically instruct
it to do so so you'll almost completely eliminate the corruption (you could still get corruption if you manually scan and any of that data comes back wrong).

When a link is scanned improperly (keep in mind that the corruption is coming from the PLM. PowerHome is only taking what is sent to it), if the problem is in
the device ID, the Insteon Explorer tries to look this up in the Insteon Devices table and if its not found, it will show as DEVICE NOT FOUND. Unfortunately,
there is no built in way to easily clean this corruption. It's entirely possible that the corruption can also occur in the group, level, and ramp bytes as well.
However, using SQL, we can easily write some commands to clean this up if you don't want to do it manually. Please make a backup of your database before
attempting any of the below in case this doesnt turn out the way you want.

In the PH Multi-Editor SQL mode (shift-F5), enter the following sql and press the "Execute" button:

Code:
select * from insteonlinks where sourceid not in (select address from insteondevices)


The above statement will show you all the records where the sourceid does not exist in the insteondevice table. To delete these records, use this sql:

Code:
delete from insteonlinks where sourceid not in (select address from insteondevices)


The same thing can be done for the remoteid. Just change the "sourceid" in the above statements to "remoteid".

The combination of deleting records where the sourceid and remoteid don't exist in your insteondevices table will completely remove any of the "DEVICE NOT FOUND"
links in your Insteon Links tab. It may potentially remove perfectly viable links though since the problem may actually be that you are missing a device in the
Insteon Devices tab.

Another cleanup you can do is to remove the links that have an invalid record number. Since the record number is not stored for links in the PLM (it's a linked
list database so the concept of record number doesnt really exist), we need to make sure we don't include links for the PLM.

The following sql will show the links with invalid sourceid record numbers (record # = 0) where the sourceid is not the PLM (You MUST change the 'FF.FF.FF' to
the Insteon address of your PLM):

Code:
select * from insteonlinks where mrecno = 0 and sourceid <> 'FF.FF.FF'


As above, if you want to delete these records, change the "select *" to "delete". If you want to see the records with invalid remoteid record numbers, then use
this sql:

Code:
select * from insteonlinks where rrecno = 0 and remoteid <> 'FF.FF.FF'


This will go a looong way to cleaning up your links but will likely be more than you want if your system is anything like mine where I have a backup PLM and have
links for that as well. The above sql of course can be adapted to whatever situation you want to handle but you would need to know what you're trying to
accomplish.

If it were me, I'd turn off the "Link Scan" and then do one more manual cleanup of everything via the Insteon Explorer. To remove links that don't actually
exist, you want to highlight them and then click the "Remove" button. You'll get an "Are you sure" type message but just go ahead and hit "Yes". The difference
between "Deleting" a link (highlight and drag/drop onto the left hand list view) and "Removing" a link (highlighting and drap/drop to the Remove button or
pressing the Remove button) is that a delete will actually talk to the device and attempt to send commands to delete the link. If the link doesnt actually exist
in the device (its a corrupted link), deleting it really isnt going to work well. Removing a link just gets rid of it in the PowerHome database and doesnt
actually try to communicate with the device.

Completely hear you concerning Insteon and how SmartHome is doing. Ive wondered the same thing myself. I keep inching ever closer to getting version 2.2 ready
for release (which includes the initial zwave support). This initial support will ONLY be for the Razberry daughtercard for a Raspberry Pi. Im not familiar with
the Z-Wave hat unless it actually is a Razberry daughtercard. If it's some other zwave compatible device, then this initial version will not support it.

The next version that is coming out (pretty much any day) is 2.1.5e which has the Alexa support, some Insteon bug fixes, and some other minor improvements. It
also includes Insteon controller support for the more recent Insteon hub (the square one). This will be significant as Ive had numerous people try to get
PowerHome to work with this device and the current version ONLY supports the original hub (rectangular). Ive sent a preview of this to several users and the
feedback is all positive with the exception of some webserver related issues that I am cleaning up.

Concerning 2.2, Im currently finishing up the IOT device framework. Once that is done, I can then update the Help file, work the Database Upgrade utility, and
then get it out the door.

Hope this helps,

Dave.
Back to Top View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
renard
Groupie
Groupie
Avatar

Joined: November 01 2009
Location: United States
Online Status: Offline
Posts: 72
Posted: April 01 2019 at 01:14 | IP Logged Quote renard

Dave,

Thanks for the very comprehensive discussion and solution to the corruption problem. I really liked the extensive background to the issue. It is always great to know the why as well as the what.

The Z-Wave interface I have is indeed the Razberry Z-Wave Hat (Hat is Raspi-speak for a daughtercard). I guess my question involved the Raspi-side of the Z-Wave PH interface. Are you dealing with the Raspberry programming that PH talks to and for that matter, how will PH talk to the Raspi-side- USB/WiFi/Bluetooth? Some users are probably going to want to be early-adopters and given the wait for PH could like to be working on Raspi-programming and site-specific implementation issues already.

I know you are busy but a short discussion of the Z-Wave implementation architecture would allow users to do some planning and perhaps development in advance. Use of a second sentient device in a PH implementation along with a PC is totally new. Even a discussion shorter than your response to my corruption issue would be extremely helpful.

Thanks Again for Your Amazing Efforts with Power-Home.

__________________
Terry
Back to Top View renard's Profile Search for other posts by renard
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: April 01 2019 at 15:25 | IP Logged Quote dhoward

Terry,

PowerHome will make use of the "Expert" interface that is a part of the Razberry Hat. Similar to how the new Insteon hub ONLY has
an HTTP interface, PowerHome will connect in the same manner to the Razberry. This means that you'll want to setup a static IP for
your Raspberry Pi and part of the setup in PowerHome will be entering this IP. Unlike the newer Insteon hub (which really only
supports a single connection through HTTP), PowerHome will be able to communicate with the Razberry at the same time you may be
using the "Expert" interface through a browser.

The Razberry includes several different web-based interfaces including an interface where the Razberry acts primarly as a central
controller (effectively taking the place of something like PowerHome). Ive never used this interface and not very familiar with it
beyond evaluating it and concluding it would not work well as a generic z-wave controller interface for PowerHome. Im not sure if
the Expert interface shares any of its setup with the other interfaces (if memory serves, they are mostly separate).

In PowerHome, there will be a new Z-wave Explorer similar to the existing Insteon Explorer where you'll be able to control some
basic parameters of the Razberry as well as link/unlink z-wave devices from the Razberry. There will be screens for monitoring
all the z-wave ongoing traffic as well as screen for creating devices, associating links, setup of command classes, etc. Alot of
what is available in the "Expert" interface will be replicated to some degree within PowerHome. In the meantime if you want to
get a jumpstart, you should be able to use the Razberry Expert interface to link your z-wave devices, assign names, setup the
command classes, etc. Anything you do in the Expert interface should play well with the PowerHome support. You would even be able
in the meantime to control the z-wave devices from PowerHome by sending the appropriate URL's to the Raspberry Pi that the
Razberry Expert interface uses.

Hoping that I answered your questions sufficiently. There won't be any custom programming or configuration necessary on the
Raspberry Pi beyond the Hat installation and the software that is used to access the Razberry via a browser. PowerHome won't in
any way make use of the Pi's bluetooth capability but will instead connect using HTTP via either the cable or wifi connection to
the Pi.

Hope this helps,

Dave.
Back to Top View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
renard
Groupie
Groupie
Avatar

Joined: November 01 2009
Location: United States
Online Status: Offline
Posts: 72
Posted: April 01 2019 at 23:11 | IP Logged Quote renard

Dave,

Much more than a sufficient discussion. An excellent overview of this exciting enhancement. I am going to start playing with the Expert Interface and await developments.

Thanks as Always.

__________________
Terry
Back to Top View renard's Profile Search for other posts by renard
 

If you wish to post a reply to this topic you must first login
If you are not already registered you must first register

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum