HomeAssistant Core 2022.3.+ and SQL integration

I'm using the sql integration for Home Assistant to query saved sensor data on a MS SQL Server container. The latest HomeAssistant Core release (2022.3 series) is based on Alpine linux and it looks like freetds is no longer included in the container's OS baseline, and this caused pyodbc to return errors saying it couldn't load the FreeTDS driver.

To work around this problem I've set up a HomeAssistant integration that runs on startup and calls a shell command to install freetds and put entries into /etc/odbcinit.ini, so my HomeAssistant is able to make SQL queries again :)

Instructions below are inspired by elRadix's posts in the following HA Community Thread: https://community.home-assistant.io/t/use-apt-get-commands/236873/4

Changes:

Create /config/scripts/startup.sh with the following contents:

#!/bin/sh
echo "Updating Image with freetds"
apk update
apk add freetds

cat >/etc/odbcinst.ini <<EOL
[FreeTDS]
Description             = FreeTDS unixODBC Driver
Driver          = /usr/lib/libtdsodbc.so.0
Setup           = /usr/lib/libtdsodbc.so.0
UsageCount              = 1
EOL

Run the following to give the script permission to execute:

chmod +x /config/scripts/startup.sh

At the end of configuration.yaml, add the following to create a new shell command:

shell_command:
  setup: "/config/scripts/startup.sh"

Add the following to automations.yaml:

- id: startup_1
  initial_state: true
  alias: System Startup Scripts
  trigger:
    platform: homeassistant
    event: start
  action:
    - service: shell_command.setup

Restart HA core and SQL Server queries should start working.

IKEA TRADFRI signal repeater (E1746) and zigbee2mqtt

At home I have Zigbee sensors scattered around the house and a couple of sensors have low link quality that had resulted in gaps in my sensor readings. I'd read that it's possible the extend the range of a Zigbee network and the Ikea near me had Tradfri signal repeaters in stock. I picked up a couple.

 Initially I tried following the instructions on the zigbee2mqtt website to pair the device but didn't have much luck. In the end I found out that I had 20 devices already paired to my CC2531, so adding more wasn't a good idea. I set up a second Raspberry Pi with a second CC2531.

I eventually got the signal repeaters working and found out a few things:

E1746s needed a custom network key

The E1746s wouldn't communicate successfully without a custom network key set up on the Zigbee network. My CC2531 would log messages like this showing that it was seeing something but it wasn't able to decode the payloads:

warn  2020-12-13 10:39:34: Received message from unsupported device with Zigbee model 'undefined' and manufacturer name 'undefined'
warn  2020-12-13 10:39:34: Please see: https://www.zigbee2mqtt.io/how_tos/how_to_support_new_devices.html.

These messages have gone away after pairing with a network key set.

Pairing and configuring process takes minutes

Unlike other Zigbee devices which pair and configure quickly, the process for the E1746s takes minutes and the E1746s seemed to go to sleep (or abandon the process) midway. I had to push the reset button mid-pairing on the E1746 to successfully configure the device. The logs for zigbee2mqtt looked like this (lines with starts and in bold show where I did something):

* pressed reset here *
info  2020-12-13 10:36:45: Device 'Ikea Tradfri Repeater 01' joined
info  2020-12-13 10:36:45: Starting interview of 'Ikea Tradfri Repeater 01'
info  2020-12-13 10:36:45: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"device_connected"}'
info  2020-12-13 10:36:45: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"interview_started","meta":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"pairing"}'
* pressed reset here *
info  2020-12-13 10:38:02: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"announce","meta":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"device_announced"}'
info  2020-12-13 10:38:07: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: Successfully interviewed 'Ikea Tradfri Repeater 01', device has successfully been paired
info  2020-12-13 10:38:08: Device 'Ikea Tradfri Repeater 01' is supported, identified as: IKEA TRADFRI signal repeater (E1746)
info  2020-12-13 10:38:08: Configuring 'Ikea Tradfri Repeater 01'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"interview_successful","meta":{"description":"TRADFRI signal repeater","friendly_name":"Ikea Tradfri Repeater 01","model":"E1746","supported":true,"vendor":"IKEA"},"type":"pairing"}'
info  2020-12-13 10:38:09: Successfully configured 'Ikea Tradfri Repeater 01'

So far I've seen a couple of MQTT messages with link quality paylods, so it looks like they are working now:

MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":31,"update_available":false}'

BackWPup S3 policy

I run WordPress for my private blog (a spot where I stash notes and other junk which isn't worth posting publicly) and I'm using BackWPup to back up the contents of my WordPress instance to S3. In order to follow good security practice, we should create a specific user which only has the permissions it requires to save backups. In AWS this means you need to create a user and then create an IAM policy for saving backups and then apply this policy to the user.

The way I’ve done this is to create a new S3 bucket for WordPress backups, created a new user called and created a policy which is then applied to the new user. In this post I've named it "wordpress.backup.homenetwork.dns", if you're copying the code from here replace that with the name of your wordpress backup bucket. The policy JSON looks like this:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::wordpress.backup.homenetwork.dns",
                "arn:aws:s3:::wordpress.backup.homenetwork.dns/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*",
            "Condition": {}
        }
    ]
}

This gives the user full access to the wordpress.backup.homenetwork.dns bucket. It also gives the user access to list all the buckets in S3 as BackWPup uses this to populate the target bucket drop down list. Apply this policy to your backup user and you're good to go.

VIsual Studio 2015 not starting

Today I had an issue with Visual Studio 2015 not starting today after installing some updates in Visual Studio 2013, so I did some googling about how to diagnose issues with Visual Studio.

Visual Studio can actually write logs about its activities if you run it via:

devenv.exe /log

This causes VS to start logging to:

"%AppData%\Microsoft\VisualStudio\[Version]\ActivityLog.xml"

In my case VS 2015 is version 14, so I was looking in:

c:\Users\[account]appdata\Roaming\Microsoft\VisualStudio\14.0

Looking in the logs there was a log entry at ERROR level:

"No InprocServer32 registered for package [Async Query Service Package]"

My guess is the VS 2013 update messed with the dll for that package.

The resolution to this problem was to get VS 2015 to reinitialize itself by running this as an administrator from the Visual Studio command prompt:

devenv.exe /setup

WPF and broken fonts

Today I had problems with starting up the Work Item Template editor in Visual Studio, and when I tried repairing my VS installation the installer kept crashing!

It turns out these were due to corrupted fonts and WPF is susceptible to this problem!!

TL;DR:

If you have broken fonts in your Windows installation, running WPF apps (or running apps that use WPF like Visual Studio) will unexpectedly crash with a Type Initializer exception.

That will keep happening until you fix your fonts.

---

The installer logs the errors that the installer runs into. It logs to c:\users\he152234\appdata\local\temp\dd_vs_[xyz]_[date].log.

My VS installer was crashing with this error:
[0A94:25CC][2015-07-28T16:08:47]e000: MUX:  ERROR: The type initializer for 'System.Windows.Media.FontFamily' threw an exception.

[0A94:25CC][2015-07-28T16:08:47]e000: MUX:  Stack:    at System.Windows.Media.Typeface..ctor(FontFamily fontFamily, FontStyle style, FontWeight weight, FontStretch stretch)

It turns out VS’s installer is a WPF application and WPF apps are susceptible to broken fonts.

So.. what worked for me was:

Open cmd.exe and copy all files from c:\windows\fonts to some other folder (c:\fonts for me).

Open regedit and navigate to HKLM\Software\Microsoft\Windows NT\CurrentVersion\Fonts

Delete all font registrations (a little scary).

Open Fonts in Control Panel.

Drag all fonts from c:\fonts to the Control Panel Fonts window (this re-registers all valid fonts).

Restart the computer.

And I can run VS again! :)

Dave

Ikea Alkaline Batteries are pretty good?

BitBox has built a rig for testing battery performance and run a low drain and high drain test on a number of widely available batteries:
Low Drain
High Drain

The results are surprising: Ikea Alkaline battieries are value for money!

In my mind, Ikea batteries were categorised as the type of batteries that you'd put into remote controls. Clearly my opinion needs to change on this! I've been using Varta high energy alkaline batteries at home for environments which are hostile to rechargable NiMH (e.g. low drain, hot environments), but I'll give Ikea a try next time!

Oops

Apple please fix :)