MySQL uses replication to do an ongoing life restore of a primary server to any number of replicas. How replication came to be I have discussed previously in another article .

Modern replication uses row based replication, with a minimal row image and compression. What is that?

Decoding the Binlog

When using Row Based Replication, the Row Change event is represented using the BINLOG statement in the output of the mysqlbinlog command. It has a single string parameter, the base64 encoded pre- and post-change row images.

On Twitter, Samuel Nitsche asked:

Are there “IT history nerds” on this app? Like people who have the skills/knowledge to tell how some trends/evolutions in IT appeared? I’d love to connect!

Asking about what he wanted to know specifically he said, he’s interested in the reasons why DevOps and Agile take up in DBA circles is so slow and low. That forced me to brain dump stuff that has been active in the back of my mind for quite some time now, but which I never collected properly.

When using MySQL with Python, you may want to use the mysqlclient library, which is what most people do and which will work just fine. Or you are using the official MySQL 8 Connector/Python package, which will behave slightly differently, but maybe supports the unique MySQL 8 feature already that is not in mysqlclient, yet.

Your SQL may be hand-writtten, or it may be generated using SQL Alchemy, Django or some other package. If the latter is the case, it may be useful to be able to see the actual SQL string that has been sent to the database in order to facilitate interactive debugging.

I record this for posteriority without much comment.

The error message “Truncated incorrect DOUBLE value” when issued by MySQL can be a confused parser, and masks unintended barely legal syntax:

kris@localhost [kris]> select * from testtable;
+----+------+
| id | d    |
+----+------+
|  1 | eins |
|  2 | zwei |
|  3 | drei |
+----+------+
3 rows in set (0.00 sec)
 
kris@localhost [kris]> update testtable set d="vier" and id = 4 where id = 3;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'vier'

The actual problem here is the incorrect use of and in the set-clause of the update statement when a comma was intended.

On the morning of Saturday, 22nd February 2020, two years ago, my colleagues and I woke up to the a message from the company that said:

“Spaces” Closed for Operations 23 Feb - 28 Feb

During the morning hours of Saturday, 22 February, a fire occurred on the first floor of the “Spaces” building. The fire was extinguished promptly but due to smoke and residue particles that spread throughout the building, we will not be able to open “Spaces” for at least one weeks time. The closure of the building is required to ensure we fully inspect the building, our equipment, clean the office of all smoke damage and residue and more importantly, ensure we have a safe working environment to return to.

A database is a special kind of structured, persistent, global variable with concurrent access over the network.

You write data to a cell of a row of a table in a schema in the server, and it is being kept around for you, with a lifetime longer than the runtime of your program. The database takes care of making sure of ordering access to that data, so there is a clearly defined series of changes to the data – locking, transactions, isolation are things that make databases popular.

The new disks in the file server had synchronized nicely, and that resulted in an interesting graph:

Sectors on the outer part of a hard disk are transferred faster than inner sectors. You can see how the disk speed halves between the outermost and the innermost part.

While watching, I decided on a whim that I wanted to convert the entire setup from using Linux mdraid to dmraid, the LVM2 implementation of RAID1. It is essentially the same code, but integrated into LVM2 instead of using mdadm for control.

The old file server was full, and since the little one got more local storage in his laptop, I also needed more storage for backup to match. I am running a 5x cage using up 3 disk slots in a Midi-Tower. Two slots are filled with Seagate Ironwolf 10 TB disks already, the rest is available.

A 5x hot swap disk cage using 3 disk slots in a midi tower.

This is the Hunga Tonga underwater volcano explosion in Pacific Ocean (animated gif, click to start):

This is data from my sensors , 15h later:

The second wave took the long way around, and arrived around 2am-4am on the morning of the 16th, but barely registered.

On Twitter, Jan Wildeboer linked an article by Adam Hooper on MySQL and the weird utf8mb4 character set.

The recommendation is correct:

In MySQL, use utf8mb4 when you mean to work with utf8 in your programming language.

The background and reasoning for this (and why it is wrong) is way more complicated than outlined by Adam Hooper. So let’s walk through this:

MySQL utf8 means 3-byte Unicode, and access to only the BMP

utf8 in MySQL encodes the Unicode BMP . The Unicode Basic Multilingual Plane, or BMP, is the original 65536 character plane. It was the only character plane available in Unicode 1.0 It was thought to be enough for all scripts in the world. It wasn’t.