Re-claiming disk space from mysql database

 Just deleting rows from a mysql database does not tend to release disk space.

This method is drastic, but will get the disk space back:

1. Extract any data you want to keep from the database (we are about to delete all the records)
2. select max(id) from <table name>;
3. truncate <table name>;
4. alter table <table name> auto_increment=<starting id>;

this leaves you an empty database but the ids generated for new rows will be unique from the ones in the original database rows.

VPN for Remote Monitoring

We have just bought ourselves a static caravan and sited it about one hour drive from home - so it is close enough to go there if something needs doing, but not so close that we would call around every couple of days to check on it.

So I wanted a technological solution to allow me to monitor how it is, and in the winter tell it to put the heating on when we set off to go to it, so it is nice and warm when we get there.

The approach I have taken is:

• Do not rely on the caravan park's internet access, as this is reported to be quite unreliable
• I am using an old mobile phone with a Lebara sim (£5 per month for 5GB data).  
• It runs as a mobile hotspot so that several devices can connect to it if necessary - I had to go into the android advanced settings for the hotspot to prevent it switching off after a short time with no devices connected (otherwise an electrical supply interruption can result in us losing communications, even when supplies are restored).
• This works very well - we have good wifi reception from the phone, even with it in the cupboard at the back of the caravan.
• Use a low power computer running Home Assistant to do monitoring and control.
• I had a Raspberry Pi Model 3B available so am using that - it draws about 2W during normal operation.  It has the following main hardware and software components.:
• Sonoff Zigbee USB dongle (so I can use low cost battery powered zigbee devices)
• Home Assistant (run as a docker image)
• Mosquitto MQTT broker (run as a docker image).   This also utilises a bridge to a MQTT broker on a remote server so I can publish caravan data to my home system periodically.
• Zigbee2mqtt (run as a docker image) to interface with Home Assistant (I am trying to standardise MQTT as my default way of communicating between small devices, rather than reinventing my own http based protocol each time).   The configuration file for the mosquitto instance is mosquitto.conf.
• Create a VPN so that I can log into the caravan computer from home to change its configuration when I am not there.
•  Caravan Server Configuration
• Use Docker Compose to set up the HomeAssistant, Zigbee2mqtt and Mosquitto containers, see the compose file docker-compose.yaml.   Start with 'docker compose up -d'  (the -d starts it as a daemon process so releases the terminal).   Check they are running with 'docker container ls.'
• VPN Configuration
• The caravan server is behind a mobile phone network, so I can not just SSH into it.   Instead I created a VPN using wireguard.    A remote server acts as a hub and the caravan server and any other computers connect to that hub.   Other computers can then SSH to the caravan server.
• This was much more difficult to get working than I had expected...  Two particularly useful tutorials were one from Digital Ocean and ArchLinux.
• The big mistake I made was setting the AllowedIPs for both the Caravan and my Laptop Peers to have /24 netmask specifications.    This meant that they were both asking for IPs in the range 10.0.1.0-255.   So one worked and the other didn't.   Setting the netmask specification to /32 instead meant that they only asked for their single specific IP address.   So now I can ssh to the caravan server from my laptop, with the remote server acting as a bridge.

TP-Link Tapo Camera Configuration

We use an IP camera as a video monitor for Benjamin, and our old D-Link one is starting to show signs of becoming unreliable so I am thinking of a replacement.

The issue is that since I got our latest camera, the cheap, readily available IP cameras have become 'smart' and talk to remote servers so you can view your video stream from outside of the house etc.

This is way more than I want - I'd rather not connect it to the internet and just manually configure an IP address and username/password for an RTSP stream - then it is done, I don't need any more.

It looked like the 'consumer' level D-Link cameras no longer support RTSP (or you have to be very careful which one you get to make sure it has it), but I found that TP-Link do say on their web site that you can enable RTSP to use the camera with third party devices - so I decided to try a TP-Link One - I got their base model (C100), which is  wifi-only device for less than £20 from amazon.

The official instructions say you have to install a Tapo app on your phone to configure it, but I thought I'd try and do it manually by dong the following.

Attempt at Manual Configuration

• Switch On Camera
•  It appeared as wifi SSID Tapo_Cam_11CB - connect (no password required)
•  The camera provied my laptop with an ip address 192.168.191.100
•  Ran nmap -sP 192.168.191.10/24, which found a host at 192.168.191.1 (in addition to my laptop)
•  nmap 192.168.191.1 showed that ports 443 (https), 554 (rtsp), 2020 and 8800 were open.
•  Pointing browser at https://192.168.191.1 gave a 'No such file or directory' response
•  Pointing mplayer at rtsp://192.168.191.1/stream1 gave an unauthorised error (probably because we have not enabled RTSP yet).
• Pointing browser at https://192.168.191.1/index.html returned a json string which included a key - is this some sort of oauth response?

At this point I gave up for now and decided to see what the Tapo app does.   I think it should be possible to reverse engineer the configuration and authentication if I am feeling very keen, but there might be some pre-shared key involved which could mean it is not possible.

Configuration using Tapo App

•   Used Tapo app to configure camera to connect to my router.  It did not work at first for some reason - it said it did not receive an IP address -
• This may be because it connected to a new access point that I installed a few days ago and haven't tested thoroughly, so I moved the camera and switched off the access point and it worked.
• Used Tapo app to enable RTSP stream with username and password - we can now use mplayer to connect to the stream using mplayer rtsp://<uname>:<passwd>@192.168.191.1/stream1
• I can also use the Tapo app to view the stream from outside of the house, which is not something I want to be able to do.
• Deleted my Tapo account and have confirmed that the camera still works with RTSP.
• I do not know whether it is still connecting to the TP-Link servers, even though I deleted my account though.
• The only way I have thought of doing it is to run my own DHCP server and a simple router app on a Raspberry Pi which will block internet access for the camera - Maybe there is a better way.

Or am I just being too paranoid about trusting remote servers with my live video stream?   I guess the vast majority of customers will just follow the TP-Link instructions without worrying about it.......

Temporary Swap File on Ubuntu

 I am training a Machine Learning (Neural Network, "AI") model for epileptic seizure detection.   An issue I have is that I have a lot more false alarm data than I do genuine seizure data, so I am using 'Data Augmentation' to make more seizure-like data.

Unfortunately when doing this using Python, it seems to eat huge amounts of memory.   The 16GB on my computer is not enough, so I added more swap space using the following:

fallocate -l 16G /swapfile2

chmod 600 /swapfile2

mkswap /swapfile2

swapon /swapfile2

Check it is working with 

swapon --show

or

free -h

Arduino Nano Clone (CH340)

 I have a few cheap Arduino Nano clones, which use the CH340 USB chip.

I had a few problems getting them to work with Ubuntu 22.04 LTS - first the ttyUSBx devices was not appearing, and then there were errors trying to upload firmware.

To get the ttyUSBx working, I had to 

• uninstall brltty as that seemed to be grabbing the device - not a problem for me because I don't use braile, but if I did, it would have needed more faffing.
• add myself to the dialout group in /etc/group
• make /dev/ttyUSBx group read/write
• I still had errors doing upload using either the default nano board or the old bootloader version in the arduino IDE.
• Installed a patched driver for the CH340 chips from https://github.com/juliagoda/CH341SER
• After that, I can upload firmware using the 'old bootloader' version of the Arduino Nano board in the Arduino IDE.

Note, Have just re-checked and I think I am using the original CH341 driver, not the new one, so it must have been the bootloader change that got it working?

Preventing Doze Mode on Samsung Galaxy Tab 8

We have a relatively old Samsung Galaxy Tab 8 android device that we use as a monitor for OpenSeizureDetector.

The problem is that when it is not plugged in, we start to get fault 'pips' until we wake up the device.     There are no obvious 'Battery Optimisation' settings in the OpenSeizureDetector page of the device Settings->Apps menu.

It turns out that the battery optimisation settings are well hidden.  To disable battery optimisation do:

• Settings -> Apps
• Three dots at top of screen to open extra menu.
• "Special Access"
• "Optimise Battery Usage"
• Select "All Apps"
• Locate OpenSeizureDetector and set the switch to off (not optimised).

This should prevent OpenSeizureDetector being forced into 'Dose Mode' so it will continue to run normally in the background when the device is asleep.

Transferring a MySQL Database to another Server

 This should be easy...

On server do:

mysqldump -u osd -p osd > osd_dump_06mar21.sql

But on my very small virtual server I hit an error about conection to mysql being lost.     I had to increase some timeouts in the mysqld configuration file....but I have forgotten which ones - will update this when I remember.

Transfer the dump file to the other server (I use scp).

On the other server, set up the user login details then do:

mysql -u osd -p osd < osd_dump_06mar21.sql

BUT this gave an error:

Unknown collation: 'utf8mb4_0900_ai_ci'

I found (on stack exchange!) that the solution is to replace all occurrences of this string with 'utf8mb4_unicode_ci'

The simplest way to do that is with sed:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' osd_dump_06mar21.sql 

After doing that the import:

mysql -u osd -p osd < osd_dump_06mar21.sql

worked fine.