Publish-Subscribe options

[Michael McSharry]

The segment below shows Topic homeseer/house/security_system with payload of 100 received. It was associated with Device 20. CAPI control was performed and HS updated status for Device 20 and mcsMQTT published this topic with payload 100. For this sequence the publish and subscribe topics must have been different for HS device 20. Looking at the database posted yesterday I confirmed that there was no subscribe topic for this device in the database for one of the three instances of device 20. There should have been only one instance.

6/19/2018 9:28:11 AM	138594986	| Update Accepted 20 to 100 StatusType=0  
6/19/2018 9:28:11 AM	138594991	| Command nonPlugin Device 20 to 100  
6/19/2018 9:28:11 AM	138594991	| Command 20 to 100 ControlValue=0, Range=Nothing, ControlType=Button, ControlString=, Label=Off, ControlUse=Not_Specified  
6/19/2018 9:28:11 AM	138594992	| Command numeric, but no CAPI matches value or range  
6/19/2018 9:28:11 AM	138594992	| Command 20 to 100 ControlValue=100, Range=Nothing, ControlType=Button, ControlString=, Label=On, ControlUse=Not_Specified  
6/19/2018 9:28:11 AM	138594994	| HSEvent Do= True VALUE_CHANGE for Device 20  
6/19/2018 9:28:11 AM	138595046	| HSEvent Add VALUE_CHANGE | 20  
6/19/2018 9:28:11 AM	138595046	| DoHsEvent Topic=homeseer/house/security_system, Payload=100, Template=

In another area I see several expired web pages. This may help explain why things do not appear to stick. mcsMQTT maintains individual user sessions for browser pages. A two-second heartbeat is setup with the browser and this tell mcsMQTT that the user still has the page open. If this heartbeat stops then mcsMQTT assumes the session has been ended by the user and any subsequent action on that session is ignored. A 5 minute timeout is used. This was done to protect from multiple open windows stomping on each other when filters may be different on each.

What I think I will do is provide feedback with a clock/counter/blink or something to let the user know the session is still active. To minimize issues in this area keep only one browser window in use.

6/19/2018 9:34:37 AM	138981145	| Removed expired web page  
6/19/2018 9:38:38 AM	139221510	| Removed expired web page  
6/19/2018 9:45:51 AM	139654616	| Command nonPlugin Device 87 to 0  
6/19/2018 9:46:01 AM	139664540	| Sort > 0   
6/19/2018 9:47:56 AM	139779596	| Command nonPlugin Device 20 to 100  
6/19/2018 9:48:03 AM	139786535	| Sort > 0   
6/19/2018 9:49:57 AM	139900459	| Command nonPlugin Device 20 to 0  
6/19/2018 9:51:49 AM	140012687	| Removed expired web page  
6/19/2018 9:53:59 AM	140142885	| Removed expired web page  
6/19/2018 9:55:49 AM	140253052	| Removed expired web page  
6/19/2018 9:59:58 AM	140501294	| Sort > 0   
6/19/2018 10:25:22 AM	142025752	| Removed expired web page

The debug did not contain anything about why multiple copies of topics are being recorded. I will now try to replicate to understand. Most of my use is with subscribed topics and have no instances in use of using existing HS devices so have only looked at it when I was doing testing in the past.

[Slab0]

The segment below shows Topic homeseer/house/security_system with payload of 100 received. It was associated with Device 20. CAPI control was performed and HS updated status for Device 20 and mcsMQTT published this topic with payload 100. For this sequence the publish and subscribe topics must have been different for HS device 20. Looking at the database posted yesterday I confirmed that there was no subscribe topic for this device in the database for one of the three instances of device 20. There should have been only one instance.

6/19/2018 9:28:11 AM    138594986    | Update Accepted 20 to 100 StatusType=0  
6/19/2018 9:28:11 AM    138594991    | Command nonPlugin Device 20 to 100  
6/19/2018 9:28:11 AM    138594991    | Command 20 to 100 ControlValue=0, Range=Nothing, ControlType=Button, ControlString=, Label=Off, ControlUse=Not_Specified  
6/19/2018 9:28:11 AM    138594992    | Command numeric, but no CAPI matches value or range  
6/19/2018 9:28:11 AM    138594992    | Command 20 to 100 ControlValue=100, Range=Nothing, ControlType=Button, ControlString=, Label=On, ControlUse=Not_Specified  
6/19/2018 9:28:11 AM    138594994    | HSEvent Do= True VALUE_CHANGE for Device 20  
6/19/2018 9:28:11 AM    138595046    | HSEvent Add VALUE_CHANGE | 20  
6/19/2018 9:28:11 AM    138595046    | DoHsEvent Topic=homeseer/house/security_system, Payload=100, Template=

These events are from the 19th... although I may have been testing various configurations throughout the day, everything eventually was working perfectly fine on the 19th. The final configuration that I ended up with when everything was working is depicted in the second picture of my prior post.

It wasn't until yesterday morning (the 20th)... when I applied version 3.4.3.0... that's when everything stopped working.

The debug did not contain anything about why multiple copies of topics are being recorded. I will now try to replicate to understand. Most of my use is with subscribed topics and have no instances in use of using existing HS devices so have only looked at it when I was doing testing in the past.

I would be interested to hear your results of following the steps that I've outlined. One other thing to consider... I was a bit perplexed that while doing the association steps outlined in my previous post the publish topic field was pre-filled with a default template. The "Default Topic Template" field on the General tab of the plugin is blank. The device reference field has the ID of a HS/plugin managed device, so it seems odd that it is pre-filled with anything... I expected it to be blank like the subscribe topic field.

Would it be possible for me to obtain the last iteration of the plugin that was on the updater prior to 3.4.3.0... or perhaps send a reverted iteration to the updater? I'm at a dead stop at the moment...thx.

[Michael McSharry]

You have a problem with database operations that may have been masked previously so you did not know there was an issue. We need to look at the SQLite more that we need to look at the plugin version.

The following query is used, for device 20, to assess if a record exists in the database. If it does not exist a new one is added otherwise the fields in the existing one is updated.

Select count(Ref) as extant from MQTT_MESSAGE where (Ref=20 and not PluginDevice)

When I run it with DB Browser for SQLite I get 3 returned. When I run it from VS via the plugin I also get 3. In your case you are getting 0 and that looks to be why new records are being added.

The updater is instructed to put file Mono.Data.Sqlite.dll into \bin\mcsMQTT, but not replace existing one. The same for sqlite3.dll. The plugin will use Mono.Data.SQlite.dll, but sqlite3.dll may be picked up elsewhere as I think HS also uses it.

The file dates on these two files are 7/14/2010 and 10/7/2010 respectively. The one I am most concerned about is sqlite3.dll as that is the actual logic that reads/writes from the .db file. Look on your system for multiple sqlite3.dll files and try to replace with what is in mcsMQTT folder. Of course backup before global changes like this are made. If you are on Linux then find / -iname "sqlite3.dll" will find all of them. On Windows I user Windows Explorer and search including subfolders from C.

Attached is your database with the redundant records removed. This will assure you are starting from a clean configuration. At any time you can disable mcsMQTT from HS and change your database.

Every version of mcsMQTT is at url http://mcsSprinklers.com using the format mcsMQTT_3_x_y_z.zip where x, y and z are replaced with the version information. Generally just the .exe would be extracted, but it does not hurt to replace others per the folder structure that is in install.txt. The version log is at the sticky at the start of the mcsMQTT forum.

I would be interested to hear your results of following the steps that I've outlined. One other thing to consider... I was a bit perplexed that while doing the association steps outlined in my previous post the publish topic field was pre-filled with a default template. The "Default Topic Template" field on the General tab of the plugin is blank. The device reference field has the ID of a HS/plugin managed device, so it seems odd that it is pre-filled with anything... I expected it to be blank like the subscribe topic field.

Once a non-plugin device has been accepted it needs a topic on which to publish. If the user gives a template on the General tab then it should be used. Otherwise the default that I selected provides a starting point from which the user can edit.

[Slab0]

The file dates on these two files are 7/14/2010 and 10/7/2010 respectively. The one I am most concerned about is sqlite3.dll as that is the actual logic that reads/writes from the .db file.

I can confirm that the correctly dated dll's are in your plugin's directory. I can also confirm that a lot of programs use sqlite3.dll (in my case cygwin, minGW, Python, and more...)... but all in their respective program directories, none are running, and none are in the system PATH. Well...with one exception (of course!). I have a Plex server running on the same VM as Homeseer...and it most certainly uses sqlite3.dll, and a very recent iteration at that. I assume it's locked and loaded... and perhaps that is the issue. I can't replace the dll for that app... but I likely will relocate Homeseer to another VM.

Every version of mcsMQTT is at url http://mcsSprinklers.com using the format mcsMQTT_3_x_y_z.zip where x, y and z are replaced with the version information. Generally just the .exe would be extracted, but it does not hurt to replace others per the folder structure that is in install.txt. The version log is at the sticky at the start of the mcsMQTT forum.

Thank you. I did note that it appears only the exe changed (based on date/timestamp of the various plugin related files). Regardless, what I think I'm going to do is completely uninstall mcsMQTT and reinstall the latest (3.4.3.0) iteration again. I'll use the manual approach to recreate the associations... and see how things work. I'm not ready to move Homeseer yet, so if I can just get back to a functional state, I'll be delighted.

I'll post back... thanks again!

[Michael McSharry]

You should be able to just take the database I provided and you should be good to go.

Another thing that can be done is additional debug added in the database section to see exactly what the queries are providing.

[Michael McSharry]

I added the additional debug around the database operations. It will put the additional info in the debug file when Debug is enabled on General tab. http://mcsSprinklers.com/mcsMQTT3431.zip or from updater.

[Michael McSharry]

Just recognized I compiled with the server URL hardcoded. Need to rebuild and repost.

[Michael McSharry]

Server changed to 3.4.3.1 to localhost. Also added a blinking checkbox in upper left to know mcsMQTT is still connected to that browser window.

[Slab0]

You should be able to just take the database I provided and you should be good to go.

Still using 3.4.3.0, I disabled the plugin, and replaced the db with the one that you provided. Restarted the plugin, and it still doesn't work. When I publish from the external server I can see the topic/payload is received on the mcsMQTT statistics tab, but the action isn't sent off to CAPI (the device status doesn't change, and the HS log doesn't show the action). When I turn the device on/off on HS, the statics tab doesn't show anything as published (or more precisely, it always shows 'win7pro-VM/mcsMQTT/LWT'), and the external server receives nothing on it's associated sub.

Updated to 3.4.3.1, using your db, same issues as above.

Reverted back to 3.4.2.1, still using your db, device actions initiated on HS works (they are published and received by the external server successfully). When I publish from the external server I can see the topic/payload is received on the mcsMQTT statistics tab, but the action isn't sent off to CAPI (the device status doesn't change, and the HS log doesn't show the action).

Still using 3.4.2.1, shutdown down the pugin, delete db, start plugin to get fresh template. Manually created a device association and now see just the opposite of the last config...device actions initiated on HS no longer work (the statistics tab always shows 'win7pro-VM/mcsMQTT/LWT' as last published), but publishing from the external server now works fine (HS receives and acts per the payload).

Still using 3.4.2.1, I shutdown Plex and verified that no other process has sqlite3.dll loaded on the system, recreated a fresh db, manually added a device association, and I'm still seeing the same symptoms as per the last config... device actions initiated on HS no longer work (the statistics tab always shows 'win7pro-VM/mcsMQTT/LWT' as last published), but publishing from the external server now works fine (HS receives and acts per the payload).

Oh, I'm still seeing the publish field in the association revert back to the default $$xxxx template after changing it during the association process. Obviously something wonky is going on, but at the moment I'm at a loss as to what the issue might be.

Attached is the last db created (just one device)...thx.

[Michael McSharry]

Please provide the debug output with 3.4.3.1. We need to understand why the database has duplicates.

The db that is attached has device 87 three times. Once not associated with a subscribe, but has a publish. The other two look to have been created by accepting homeseer/downstairs/living_room/lr_light without providing a specific Ref value to associate it with an existing HS device.

Since items were removed from the database I must assume you started over rather than starting with the database I provided. I recognize that you tried multiple things, but to isolate the failure mode it is easier to do it methodically.

[Slab0]

Please provide the debug output with 3.4.3.1. We need to understand why the database has duplicates.

The db that is attached has device 87 three times. Once not associated with a subscribe, but has a publish. The other two look to have been created by accepting homeseer/downstairs/living_room/lr_light without providing a specific Ref value to associate it with an existing HS device.

Since items were removed from the database I must assume you started over rather than starting with the database I provided. I recognize that you tried multiple things, but to isolate the failure mode it is easier to do it methodically.

1. To start from a clean slate, I uninstalled the plugin (and manually deleted residual files left behind) and then reinstalled the latest (3.4.3.1) from the updater.

2. I then published from an external server to insure that the topic would appear under "Association Table for Auto Association of MQTT Topic and HS Device" on the associations tab.

3. I clicked in the Ref box next to the topic and entered HS device ID 92, and then clicked submit. Without doing anything else, I backed up the database at this point. The result is mcsMQTT_1.db in the attached archive. You will note that it contains two entries for device 92... the first with a blank source and a topic of "win7pro-VM/mcsMQTT/Downstairs/Living_Room/Table_Lamp", the other with source "homeseer/downstairs/living_room/lr_light" (the auto-discovered topic that I wanted to associate with), and the same default publish topic "win7pro-VM/mcsMQTT/Downstairs/Living_Room/Table_Lamp" as the first entry.

Note that when I clicked submit for the Ref box entry, the 'A' box was then automatically checked.

4. I then clicked in the publish box to change the pre-filled default topic to match the source topic, clicked submit, and refreshed by clicking 'Show selected associations". I noted that the publish topic change was not accepted and it was still pre-filled with the default topic. Without doing anything further, I made a backup of the database at this point (mcsMQTT_2.db in the archive)... there is no change to it's contents that I noted, still two entries for device 92.

5. Interestingly, when I again clicked in the publish topic box again to edit a second time I noticed that the edit box that appeared was pre-filled with the topic that I had entered previously (the correct topic to match the sub), vs. the incorrect default topic shown after refreshing. I clicked submit, and then refreshed, and the change was reflected in the publish topic field. I made another backup (mcsMQTT_3.db) at this point. You will notice that there are still two entries for device 92, but both now have the correct publish topic, vs. both having the default template topic.

6. I switched to the HS homepage and I enabled device 92, switched back to the statistics tab and it showed nothing was published (just "win7pro-VM/mcsMQTT/LWT") and the external server received nothing. I published off/on from the external server and that works fine...Homeseer receives and sets per payload.

7. At this point, I made another backup of the db (mcsMQTT_4.db). Now the data base has three entries for device 92, the first as before (blank source, auto-discovered topic, the other two with source matching auto-discovered topic, and now incorrect topic field entries (both reset to the default).

I followed these steps exactly as I've written them... there were no other actions performed by me. All steps were performed with no other system task running sqlite3.dll. The attached archive also contains the debug log (debug was also enabled on the general tab for all of this).

*** quick update **** I just went to the add/edit tab and entered device ID 92 to see what it showed. It looked correct, but more interestingly just doing that (i didn't click on any fields...just looked at the config for device 92) it apparently updated the db again. The attached mcsMQTT_5 file is the backup... still 3 entries, with the topic field updated to be the correct topic now.

[Michael McSharry]

Excellent chronology. I have found the basic issue within the plugin based upon 1.db. I am going to continue to text more before posting.

[Michael McSharry]

3.4.3.2 is now in the updater. I suggest starting with a clean database. After I understood what was happening I decided to maintain a single record for each topic and each ref. Previously it was possible to have duplicates with plugin device ownership used to distinguish. It was an awkward bookkeeping effort as the plugin evolved and now that it has stabilized I can see this simpler approach.

There may be some regression for users that have used the non-plugin mappings. It just depends upon which record gets picked from the multiple.

[Slab0]

Well, the good news is I can confirm that there is indeed a single record for the device now. The bad news is nothing ever gets published for the device (statistics always shows 'win7pro-VM/mcsMQTT/LWT'). Anything that comes in via the sub topic is acted upon as expected.

A summary of my notes:

1) When doing the initial association on the Associations tab, I still need to change the topic twice for it to stick. The source and topic fields in the db are the same at this point, and equal to my desired topic:



2) The first time I went to the Edit/Add tab and entered device ID 92, the db was updated. Specifically, the topic field was changed to the topic listed in the 'HS Device Publish Topic' box:



After going back to the Associations tab (I believe) the db topic reverted back to my desired publish topic. Subsequent visits to the Add/Edit tab and entering the device ID do not appear to change the db topic field again.

3) The source field in the db never changes once set by the initial association. If the device change is HS initiated (gui, script, event) vs. sub topic initiated, wouldn't the source field change... especially if there is now a subsequent source=pub check to determine if publish is necessary?

The debug log is attached... thx.

[Michael McSharry]

Thanks again for the good info. I do see some additional work I need to do now that I have changed the database philosophy to house only one record.

If the device change is HS initiated (gui, script, event) vs. sub topic initiated, wouldn't the source field change... especially if there is now a subsequent source=pub check to determine if publish is necessary?

When HS device change occurs only the 'payload' is affected. The 'source' is the subscribe topic. The 'topic' is the publish topic. At the point of publishing a payload a check is made for 'source' = 'topic' and in that case the publish is skipped. Note this happens only when HS reports a device value has changed. It is not bypassed when HS controls a mcsMQTT device. In your case the following is the expected scenario.
1. external node published Light=ON
2. mcsMQTT uses CAPI to tell HS to forward ON command to Zwave plugin
3. Zwave plugin controls its hardware
4. Zwave plugin updates HS Devices status
5. HS delivers Device change event to mcsMQTT
6. mcsMQTT recognnizes the device has a topic to be published, but does not publish because source (subscribe topic) = topic (publish topic)