MySQL Unicode weirdness

▼ After looking at the SQLite Unicode behavior, it's now time to do the same for MySQL. Coincidentally, I'm currently migrating some old databases that were created in the very early 2000s to a more modern environment. I think those old databases were from the MySQL 3.x days, before MySQL gained any sort of Unicode support. Those old tables are thus still in the latin1 (ISO 8859-1) character set.

For a long time that worked fine for me as these tables contain data in English and Dutch, and Dutch only needs a few accents, which are all present in Latin 1. However... at some point it started becoming more convenient to use UTF-8 for the text in those databases. So I did. I just never told MySQL about the switch.

In hindsight, that was not a terrible decision, as it avoided complexities at a time when UTF-8 support was still immature. But these days, there's really no excuse to do anything other than make an entire workflow UTF-8 clean, barring any showstoppers.

So I migrated an old table to the test environment for the new system. And got some really weird results: on web pages "CO₂" showed up as "COâ‚‚", but in MySQL it showed up correct, be it that the number of characters is off (I only got 6 while I specified 8):

mysql> select substr(article, 1587, 8) from muart where id = 753;

+--------------------------+
| substr(article, 1587, 8) |
+--------------------------+
| CO₂ ui                 |
+--------------------------+

Further digging by looking at the raw data:

mysql> select hex(substr(article, 1587, 8)) from muart where id = 753;

+-------------------------------+
| hex(substr(article, 1587, 8)) |
+-------------------------------+
| 434FC3A2E2809AE2809A207569    |
+-------------------------------+

Fortunately, it's not necessary to decode this manually, there is an UTF-8 Decoder web tool for that. The decoder shows that COâ‚‚ is correct. So why is MySQL showing me CO₂? That's a problem I hadn't heard of before, but is apparently not uncommon:

Double-encoded UTF-8.

This happens when you take UTF-8, such as "CO₂", and then pretend it's another encoding (usually Latin 1) and convert that to UTF-8. So what had happened is that as I was importing my data in MySQL, the MySQL command line client would send UTF-8 to the MySQL server process, but the server would think that was Latin 1 and convert it to UTF-8. Then when I did a query, the server would convert the UTF-8 back to what it thought was Latin 1 for the convenience of the client, and the client then showed this as UTF-8 so everything looked good, but was actually stored in the database incorrectly.

Fortunately, the two related problems were both easy enough to fix. First, make sure the client and server agree on the character encoding. Let's first check what the server's original settings are:

mysql> SHOW SESSION VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

Wow. But fortunately we don't have to fix all of those individually. We can simply do:

mysql --default_character_set=utf8mb4

And then:

mysql> SHOW SESSION VARIABLES LIKE 'character_set%';

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

Much better.

And convert the double-encoded UTF-8 into something more readable:

mysql> UPDATE muart SET title=CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8mb4);

Keen-eyed observers may have noted that MySQL has two versions of UTF-8: utf8mb3 and utf8mb4. This goes back to the early days of Unicode, where the idea was that all characters would fit into 16 bits. That results in a maximum of 3 bytes of UTF-8. But it soon became clear that 16 bits wasn't enough. So now it's 21 bits. UTF-8 can handle that just fine, but those characters that need 17 - 21 bits result in 4-byte UTF-8 sequences. So when dealing with MySQL, when you think "UTF-8", type "utf8mb4".

Permalink - posted 2023-09-21

Review: Sigma BC 16.16 STS CAD cycling computer

▼ As I wrote earlier: recently, I lost another bike computer so I needed a new one. I decided to get a more recent version of the Sigma BC line that I've been using for the past decade: the Sigma BC 16.16 STS CAD. These seem to be on the way out now, being sold out almost everywhere. But no obvious replacement that isn't more expensive and more complex, so I'm glad I got to get one of the last ones.

The first 16 (roughly) indicates the number of functions. The second 16 is, as far as I can tell, the platform year: 2016. STS is the digital wireless communication system that allows the "computer" to talk to two different bike speed sensor as well as a cadence sensor. The speed sensor sits on the bike's front fork where a magnet on a wheel spoke tells the sensors how fast the wheel is turning. The cadence sensor does the same for your pedals. I got the package that includes a speed sensor and a cadence (CAD) sensor. You can also get just the speed sensor. Or get an extra speed sensor for a second bike.

How it works

I actually did use my previous Sigma BC with two different bikes, and that worked very well. You need to tell one of the sensors to be on the second channel. You then get to set different wheel diameters for the first and second bikes. This is important, as the computer needs to know that wheel diameter / circumference to determine how fast you're going and how much distance you're covering. (In my case it was always 28" / 2200 mm.)

The Sigma sits in a little holder. You twist it to release it so you can take it indoors if you leave your bike outside, that way it won't get stolen. My previous Sigma BC 9.16 STS wouldn't necessarily sit very secure in its holder, hence me losing it. For the 16.16 this seems less of a problem: you really have to put in some effort to make it click into place. It's also not compatible with most earlier cradles. The default way to secure the holder is with a rubber band. But with a couple of tie wraps the holder is locked down more tightly. I reused my previous speed sensor and an older cadence sensor that I still had lying around.

So what do you get out of the BC 16.16 when cycling?

First of all, the current speed is displayed at all times. The whole kilometer per hour numbers are displayed prominently, with fractions of a km/h shown with a much smaller single digit. This part of the display, which also has some status icons, such as connectivity and first/second speed sensor, use a segmented LCD. However, the lower part of the relatively tall display is a pixel LCD. This shows all kinds of information, such as:

• Trip distance
• Average cadence
• Cadence
• Ride time
• Fuel save
• ETA distance / time / clock

Some of these are not so interesting, but you can't turn them off.

The device maintains both statistics and totals. Statistics are numbers of kilometers and hours of activity during the current month and past eleven months. Totals are just the totals of the same for as long as you care to keep them running. On my previous BC I kept a running total of the number of kilometers I rode during the year. So I had to reset that number to zero at the beginning of the year. The monthly statistics are updated whenever you reset a trip distance, and roll over at the start of the next month automatically.

The backlight is pretty clever: if you turn it on, you tell the BC a start and end time. Between those times, whenever you push either of the buttons, this will turn on the green backlight for a few seconds. A second press will let you control the device as normal. Outside the configured dark hours, first presses are handled as normal and the backlight doesn't come on.

Not sure what impact all of this will have on battery life. With the old one, I had to replace the CR2032s in the "computer" and the sensors about once a year. You may or many not lose your statistics and/or settings when changing batteries.

ETA counter

One nice new feature over my previous Sigma BC is that this one supports an ETA (estimated time of arrival) counter. You set a distance, start the count down and the BC 16.16 will show you the distance to your goal and either the time to reach it or the clock time when you will reach it. You have to choose between the latter two options. I find the clock time when you are expected to reach your goal more useful. The time estimation seems to work quite well, not immediately jumping all over the place when you've stopped at a traffic light.

NFC

With an Android phone you can use NFC to read data from the BC 16.16 using Sigma's app. Unfortunately, this doesn't work with the iPhone. To some degree this makes sense due to Apple's limitations. But a simple read action to collect some data is still possible. With an NFC reading app I get a URL from the BC 16.16. Obviously that wouldn't be enough to completely sync all data, but it would/should work for reading cycling trip data from the last few days in the form of a URL or other simple data format. But no, iPhone users get nothing here.

Conclusions

I really like how Sigma Sport keeps improving these simple bicycle computers. The ETA feature seems like a good addition, and the most important information is displayed nice and clear. The automatic monthly statistics are a good way to keep track of any goals you've set for yourself. So if you find yourself wondering how fast you're going or how far you've cycled fairly regularly, but you don't want to mess with smartphone apps or complex/expensive GPS-based bike computers, one of these BCs is highly recommended.

Permalink - posted 2023-01-01 - 🇳🇱 Nederlandse versie

Smart home Matter and Thread misconceptions (or not?)

▼ I just saw a Youtube video kind of reviewing the new Thread smart home communication protocol. I'm not linking to it because it attributed a bunch of things to Thread that are Matter, unless I'm very much mistaken. (And the latter is certainly possible. Do your own research before buying new stuff.)

Matter is the new IP-based system that lets a smart home "ecosystems" such as Amazon Alexa, Google Home, Apple HomeKit and Samsung SmartThings talk to smart home accessories. Matter has two big advantages. The first one is that you're no longer locked into a single ecosystem or a limited number of ecosystems. Every ecosystem that supports Matter can control accessories that use Matter. Even better, multiple ecosystems can control the same accessories.

Thread, on the other hand, is simply a wireless protocol for talking to smart home accessories. Other protocols that accomplish the same thing are Zigbee, Z-Wave and bluetooth LE (low energy). Zigbee and Thread share the same underlying IEEE 802.15.4 mesh technology. The difference is that with Zigbee, the applications talk more directly to the IEEE 802.15.4 layer, while Thread uses 6LoWPAN to make IP(v6) communicate over the limited bandwidth and packet size 802.15.4 protocol efficiently.

So a smart home controller that uses Matter can simply use IP packets to talk to Thread-connected accessories the same way it would talk to Wi-Fi-connected accessories. The only extra thing needed for that is a "Thread border router" that connects to the Wi-Fi or Ethernet home network and also to the Thread mesh network.

No more bridges?

The big advantage of Thread over existing solutions is that once you get a Thread border router, you don't need any additional bridges or hubs like the Philips Hue bridge. The range will be better than bluetooth low energy (used by Eve Homekit accessories) and the reliability better than bluetooth or Wi-Fi due to the mesh networking, where accessories that are connected to mains power will forward packets for other accessories.

But it's unlikely that you'll be able to get rid of your existing bridges. For instance, the Hue bridge will get an update that implements Matter in the first half of 2023, but the individual Hue lights won't be updated to support Matter or Thread. Those lights and other Hue accessories will continue to talk to the Hue bridge using Zigbee. That is probably the right choice as moving all those individual lights, some 10 years old, to the new Thread protocol without losing current functionality would be a gargantuan task.

However, it's likely that a Thread border router will be your last new bridge. If that is going to be a separate device in the first place. The thread border router functionality is (or already has been) added to a lot of new devices, such as Apple's latest Homepod Mini and AppleTV 4K and the LG 2022 OLED TVs.

Hue, Thread and Matter

Signify (the maker of Hue) has said that they don't want to make light bulbs that support Matter, as the Hue bridge has an important role to fulfill in a Hue installation. However, their current lights do support bluetooth so you can control a few lights directly from the phone app without the need to buy the relatively expensive Hue bridge. I wonder if they'll move that functionality from bluetooth to Thread. Then again, this requires a Thread border router, so perhaps that doesn't help their "no bridge" use case.

Even though Hue won't support using Thread itself, it would be really great if Signify could add Thread border router functionality to the Hue bridge. As the bridge already supports IEEE 802.15.4 and apparently has some processing/memory capacity to spare, this should be possible...

What now?

Although the smart home future looks bright with Matter and Thread, for those of us who already have a bunch of accessories in a single ecosystem, these new standards won't really change anything. This is really all about the future when getting new smart home accessories might be as simple as to check for the Thread logo.

For now though, this probably means waiting a bit to see where different systems end up. Hue is pretty clear: the Hue bridge isn't going anywhere and (existing) lightbulbs won't get Thread. But the bridge will get a software update to get Matter support. So if you already have a Hue bridge, no need to stop buying Hue right now for products where the high price is justified.

But the cheap smart light bulb product space is probably going to be a war zone next year as those cheap bulbs gain Matter/Thread support and no longer require separate bridges.

Eve has already been building Thread support into its new Homekit accessories, so Eve is now more attractive as this allows you to remove the bluetooth performance/reliability bottleneck, and with Matter support it will leave the Homekit-only limitation behind. But again, there will probably cheap alternatives next year.

Unless you can get a killer deal, I wouldn't buy any cheaper brand non-Thread, non-Matter accessories. Better alternatives will become available in the relatively near future.

Permalink - posted 2022-12-03