Die wunderbare Welt von Isotopp
SQL Engineering Guidelines
Kristian Köhntopp - April 15, 2022
Where I work, the native database is MySQL. This is what the database team fully supports. Other databases, notably Postgres, are in use mostly because external products we run require them. Internal projects should use MySQL. An external company provides limited support for running Postgres. The recommended version of MySQL to be used is currently the latest 8.0. It has a large number of improvements over the previous version 5.7 in terms of the SQL subset supported, handling of sorts, critical character set support and join strategies.
Change Data Capture and the Binlog
Kristian Köhntopp - April 5, 2022
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.
DevOps meets Databases
Kristian Köhntopp - April 4, 2022
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.
Debugging SQL in Python
Kristian Köhntopp - March 24, 2022
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.
Truncated incorrect DOUBLE value
Kristian Köhntopp - March 24, 2022
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.
Kristian Köhntopp - February 21, 2022
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.
Databases: How large is too large?
Kristian Köhntopp - February 16, 2022
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.
Revisiting the file server
Kristian Köhntopp - February 6, 2022
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.
Time to grow the file server
Kristian Köhntopp - February 1, 2022
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.
Kristian Köhntopp - January 16, 2022
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.
Kristian Köhntopp - January 12, 2022
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 .
Never check for an error condition you don't know how to handle
Kristian Köhntopp - January 10, 2022
This is the english version of an older text in German Computer scientists are weird. In their science there are a few rules that sound wrong when you hear them first, but make a lot of sense if you think about them. Some of them are making sense even outside of computer science. One of those rules is Never check for an error condition you don’t know how to handle. When you encounter this the first time that sounds very questionable.
... and a happy new hack.
Kristian Köhntopp - January 1, 2022
Und das neue Jahr beginnt für uns mit einem Minecraft-Server, auf dem wichtige Personen, die sich nichts haben zuschulden kommen lassen, plötzlich gebannt sind. Nach einem Unban an der Console: Der Spawn, der Eintrittspunkt für neue Spieler, ist komplett verwüstet. Wir haben tägliche Backups, sodass das schnell behoben ist. Bleibt die Frage, was passiert ist. Gehackt Unter den Spielern ist die Frage schnell geklärt: “<user> wurde gehackt.” Es stellt sich raus:
Dein Kind will Minecraft spielen
Kristian Köhntopp - December 20, 2021
Nachdem mich jetzt das dritte Paar computer-affiner Eltern mal gefragt hat, was man denn braucht, sobald der Nachwuchs sich “Minecraft” wünscht, hier der Aufschrieb zum Thema. Minecraft ist ursprünglich von der schwedischen Firma Mojang in Java entwickelt worden. Der Eigner, Markus “Notch” Persson, hat das Spiel und die Firma Mitte 2014 auf Twitter zum Verkauf angeboten, und Microsoft hat die Firma gekauft. Spiel zu verkaufen - Minecraft ist auf Twitter angeboten worden.
Es funktioniert wie spezifiziert
Kristian Köhntopp - December 14, 2021
Dieser Artikel basiert auf einem Twitter-Thread und ist in deutscher Sprache als Heise Kommentar erschienen. Über den Java-Slogan “Write Once, Run Everywhere” wurden schon viele Witze gemacht. Den log4j-Exploit behandeln viele nun wie einen Bug – das ist er nicht. Eine kritische Lücke in der Java-Bibliothek Log4j beherrscht gerade die Schlagzeilen. Die IT-Welt ruft “Warnstufe Rot” aus – weil offenbar der log4j-Code JNDI-Variablenexpansion vornehmen kann. Doch was ist JNDI? Jindi al Dap ist der Name eines alten arabischen Philosophen und Mathematik-Pioniers, der für Sun/Oracle gearbeitet hat, um ein System von Directory Lookups in Java zu entwickeln.
Discord Nitro Spam and 2FA
Kristian Köhntopp - November 30, 2021
This morning the Discord account of my son started to send “free nitro” spam to his friends on the friend list, and to some Discords he was a member of. He had 2FA (Google Authenticator) on the account. That fact alone made this a recoverable failure. My son is playing minecraft, has a friend list of around 100 fellow players, and is member of around 40 Discords. He also connected his Discord to Spotify, YouTube and other services, and he authorized around 12 application services, mostly Discord/Minecraft bot services.
MySQL: Moving Average
Kristian Köhntopp - November 24, 2021
MySQL window functions can be used to calculate daily averages or moving averages for a 24h time window relatively easily. In an earlier article basic window functions were already discussed. In this article, we want to see how we can get daily buckets and moving averages. A sample program is available, as usual, on GitHub . Getting sample data We will be working with a data table named data, with three columns: a sensor id, a measurement datetime d and a metric value m that was sampled at that time.
Arista Type 7 Passwords
Kristian Köhntopp - November 22, 2021
A friend of mine wanted to provision BGP passwords for their Arista switch configuration. So a config stanza such as router bgp 65001 router-id 10.1.1.1 neighbor mydevices peer-group neighbor mydevices password 7 8kjYaye5DsQh0epELyKNe0oZ3E3zp39X requires generation of the Password (actually “supersecretpassword”) in an encrypted form. Arista switches can do this using CLI tools, apparently. They seem to have an onboard Linux, which seems to provide limited tooling, but is good enough to run a 32-bit Python 3.
A01:2021 - Broken Access Control
Kristian Köhntopp - November 16, 2021
Dieser Artikel wurde von Lenz Grimmer auch ins Englische übersetzt. In einem Twitter-Thread von Christian Basl ging es um die von zerforschte App “Learnu” . Basl schreibt: Die Betreiber von Learnu sagen, sie hätten keine Fachkenntnisse in IT-Sicherheit und hätten sich auf externe Berater verlassen. So kam Learnu unbekannterweise unsicher auf den Markt. In der sich entwickelnden Diskussion vertrat Andreas Dewes den Standpunkt Die meisten Start-ups die ich kenne gehen durch eine Phase, in der IT-Sicherheit und Compliance eher im Hintergrund stehen.
A01:2021 - Broken Access Control (en)
Kristian Köhntopp - November 16, 2021
Translation by Lenz Grimmer, German version here . A twitter thread by Christian Basl discussed the dissection of the “Learnu” app . Basl wrote: Learnu operators say they have no expertise in IT security and have relied on outside consultants. As a result, Learnu came to market insecurely, unbeknownst to them. In the discussion that developed, Andreas Dewes took the view that Most startups I know go through a phase where IT security and compliance tend to take a back seat.