Die wunderbare Welt von Isotopp

Safe Biking: It's not the right of way that's wrong

- October 22, 2020
Sven Geggus trolled me . A bunch of nerds were speaking about what’s wrong with biking in Germany, and he wrote: Tweet : Der @isotopp wohnt doch in Holland. Wo sind denn bei euch die Vorfahrtsregeln anders und könnte man da was sinnvoll für .de übernehmen? – @isotopp is living in the Netherlands. So how is the right of way different and how could .de learn from this? Biking in the Netherlands does not suck, and that is not because of any specific traffic rules being any different, but because traffic is fundamentally different, and that somehow escalated.

Fertig gespielt: Assassins Creed: Odyssey

- October 19, 2020
Ok, so the title is a bit of an exaggeration, I am about 80% done with the main games quests. Still, the shape and feel of the story is firmly established and it is fascinating. I have some 110 hours in “Assassins Creed: Origin”, and some 65 hours or so in “Assassins Creed: Odyssey”. You can play “Assassins Creed: Odyssey” as Alexios or Kassandra, and the change is permanent for the playthrough for storytelling reasons.

Vijzelbuurt is changing

- October 8, 2020
Back when I was still commuting to work in an office, in the far past, I used to be in the Spaces building in Vijzelgracht in Amsterdam, every day. Mark Wagenbuur of BicycleDutch has been there today, and tweeted : Amsterdam is changing dramatically. I was in Vijzelstraat for the first time in about 2 years and I was pleasantly surprised! Looked up the old situation in Google StreetView for some before and afters.

An unexpected pool size increase

- October 7, 2020
At work, replication chains have a single primary database node, to which you write, and then multiple replicas, in multiple AZs. Here is what the one sample chain looks like in Orchestrator: instance-918d is the current primary, in the blue AZ. Replicas in orange and green are in other AZs. Blue badges indicate multiple replicas, eg (38) means 38 machines. When you talk to a database, you get two database handles:

IT modernisieren und konsolidieren

- October 5, 2020
Ich schrieb in einem Twitter Thread über Posix Dateisysteme vs. Object Stores : UNIX FS ist 1974. BSD FFS ist 1984. XFS ist 1994. ZFS (und Btrfs und Wafl) sind LFS, also 2004. Object Storages, LSM, “RocksDB” ist ca. 2014, um den Takt zu halten. und wurde gefragt: “Was kommt 2024”. Meine halb spöttische, halb ernst gemeinte Antwort war: Irrelevant. 2024 läuft Dein Code serverless bei einem professionellen Betreiber und vom lokalen System und dem lokalen Dateisystem kriegst Du nix mehr zu sehen außer einer monatlichen Rechnung.

What are the problems with POSIX?

- October 5, 2020
Every once in a while there is the IT news article that kind of triggers me. This time it was “Object-Storage-Protokoll könnte Posix ablösen” in german computer news site Golem . The article speaks about mmap(), NVMEoF and object storage and how it could revolutionize or complete object storages, but does not link to an original article, names no persons and no paper. Also, what do these things - mmap, NVMEoF, object storage and Posix, even have in common?

MySQL: Import CSV, not using LOAD DATA

- September 28, 2020
All over the Internet people are having trouble getting LOAD DATA and LOAD DATA LOCAL to work. Frankly, do not use them, and especially not the LOCAL variant. They are insecure, and even if you get them to work, they are limited and unlikely to do what you want. Write a small data load program as shown below. Not using LOAD DATA LOCAL The fine manual says :

Importing account statements and building a data warehouse

- September 26, 2020
This is an update and translation of a much older article , which I wrote in German Language back then. I was experimenting with importing the account statements from my German Sparkasse, which at that time were being made available as a CSV. The initial data load The data looked like this: $ head -2 /home/kris/Documents/banking/umsatz-22758031-29122004.csv "Local Account";"Book Date";"Valuta Date";"Transaction Type"; "Purpose"; "Remote Party";"Remote Account";"Bank Code"; "Amount";"Currency";"Info" "08154711";"30.12";"30.12.05";"Direct Debit"; "DRP 08154711 040441777 INKL.

MySQL: automatic partitions surely would be nice

- September 25, 2020
In Deleting data we have been looking at a process that loads data into MySQL, leveraging partitions to make it easier and faster to later get rid of the data again. For this, we created three processes, a data loader process, and two observers - one for creating partitions, and one for deleting them. The observer processes have been running ANALYZE TABLES and then polling INFORMATION_SCHEMA.PARTITIONS every 1/10th of a second to check if intervention is needed.

MySQL: Deleting data

- September 24, 2020
Completing the data lifecycle is often harder than originally expected: Deleting data can sometimes cost way more than inserting it in the first place. MySQL Partitions can offer a way out. We have an earlier post on the subject. A sample table, and a problem statement Let’s define a kind of log table, to which data is added with an auto_increment id value and some data. #! /usr/bin/env python3 from time import sleep from random import randint from multiprocessing import Process import click import MySQLdb import MySQLdb.

MySQL: Provisioning .mylogin.cnf

- September 23, 2020
MySQL uses connection and config parameters from a number of possible sources. The easiest way to find out where it is looking for config files is to run $ mysql --help | grep cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /Users/kkoehntopp/homebrew/etc/my.cnf ~/.my.cnf As can be seen, my version of the MySQL client checks in this order /etc/my.cnf /etc/mysql/my.cnf /Users/kkoehntopp/homebrew/etc/my/cnf ~/.my.cnf The cnf file is a file in dot-ini syntax, so you have [groups] and each group contains lines with key = value pairs.


- September 22, 2020
A question to the internal #DBA channel at work: »Is it possible to change a column type from BIGINT to VARCHAR ? Will the numbers be converted into a string version of the number or will be it a byte-wise transition that will screw the values?« Asking yielded more information: »The use-case is to have strings, to have UUIDs.« So we have two questions to answer: Is ALTER TABLE t CHANGE COLUMN c lossy?

MySQL: Encoding fields for great profit.

- September 18, 2020
Iterating schemas over time is not an uncommon thing. Often requirements emerge only after you have data, and then directed action is possible. Consequently, working on existing data, and structuring and cleaning it up is a common task. In today’s example we work with a log table that logged state transitions of things in freeform VARCHAR fields. After some time the log table grew quite sizeable, and the log strings are repeated rather often, contributing to the overall size of the table considerably.

Mein selbstfahrendes Auto hat Kameras, oh nein!

- September 17, 2020
Bei der ARD ist man empört! Teslas Kameras: ARD rückt Datenschutzbedenken ins Licht . Will sagen, jemand hat älteren Personen gesteckt, daß ein selbstfahrendes Auto wenig überraschend Kameras braucht, um bei seiner bestimmungsgemäße Verwendung weniger ältere Personen überzumöllern. So weit so wenig überraschend. Bei einem Tesla sind es 9, bei anderen Automobilen anderer Hersteller noch mehr, und Radar, und Lidar und weitere Sensorsysteme. Problematisch sind nur Kameras, weil Menschen als visuelle Wesen zum Bild eine emotional andere Beziehung haben als zu anderen Daten, die objektiv nicht weniger kritisch sind.

Feeds tagged, and a MySQL feed

- September 9, 2020
I have made changes to the RSS Feed of this blog: Each <item/> does now contain a container <tags/>, inside a sequence of <tag/> containers, with each posts tags. There is now a second RSS feed for posts tagged #mysql, because of demand. You can find it at https://blog.koehntopp.info/feed_mysql.xml . Example for the tags: <channel> <title>Die wunderbare Welt von Isotopp</title> ... <item> <title>A post title</title> <link>https://blog.koehntopp.info/2020/09/...</link> <guid isPermalink="false">/2020/09/...</guid> <tags><tag>lang_en</tag><tag>mysql</tag><tag>database</tag></tags> <description>blah blah blah</description> </item> </channel> So if you want only the MySQL content, subscribe to this feed , if you want all the content, subscribe to the original feed .

MySQL from a Developers Perspective

- September 7, 2020
So this has turned into a small series, explaining how to work with MYSQL from a developers perspective. This post is intended as a directory for the individual articles. It will be amended and re-dated as necessary. The code for the series is also available in isotopp/mysql-dev-examples on GitHub. The Tag #mysqldev will reference all articles from this series. MySQL Transactions - the physical side . Looking at how MySQL InnoDB handles transactions on the physical media, enabling rollback and commit.

MySQL: Generated Columns and virtual indexes

- September 7, 2020
We have had a look at how MySQL 8 handles JSON recently, but with all those JSON functions and expressions it is clear that many JSON accesses cannot be fast. To grab data from a JSON column, you will use a lot of $->>field expressions and similar, and without indexes nothing of this will be fast. JSON cannot be indexed. But MySQL 8 offers another feature that comes in handy: Generated columns and indexes on those.

MySQL: Basic usage of the JSON data type

- September 4, 2020
MySQL 8 provides solid support for the JSON data type. The manual has an overview of the data type , a JSON function reference , an an overview on generated column indexes , and explains multi-values indexes . Creating JSON columns Creating JSON columns is easy: Make the column of the JSON data type, fill in valid JSON data. mysql>createtablet(idintegernotnullprimarykeyauto_increment,jjson);QueryOK,0rowsaffected(0.11sec)mysql>insertintot(j)values->('null'),->('true'),->('false'),->('1'),->('"keks"'),->('["eins", "zwei"]'),->('{"eins": "one", "zwei": "two"}');QueryOK,5rowsaffected(0.02sec)mysql>selectjson_type(j)astype,json_valid(j)asvalid,isnull(j)assqlnull,j,idfromt;+---------+-------+---------+--------------------------------+----+ |type|valid|sqlnull|j|id|+---------+-------+---------+--------------------------------+----+ |NULL|NULL|1|NULL|1||NULL|1|0|null|2||BOOLEAN|1|0|true|3||BOOLEAN|1|0|false|4||INTEGER|1|0|1|5||STRING|1|0|"keks"|6||ARRAY|1|0|["eins","zwei"]|7||OBJECT|1|0|{"eins":"one","zwei":"two"}|8|+---------+-------+---------+--------------------------------+----+ 8rowsinset(0.00sec)mysql>insertintot(j)values('["incomplete", "array", "closing bracket"');ERROR3140(22032):InvalidJSONtext:"Missing a comma or ']' after an array element.

MacOS: Deleting Relocated Items

- September 2, 2020
I had to upgrade my company issued MacBook pro to Catalina for fleet unity reasons. The upgrade left me with a folder Relocated Items in /Users/Shared/Desktop and a link to that prominently on my Desktop. Deleting that folder moved it to trash. Trying to empty trash then failed. That is because in that folder was several levels deep a folder or link to X11R6. Apple considers that file outdated, which is why it ended up in Relocated Items.

On Touching Candles, And Error Budgets

- August 31, 2020
Ok, it’s “Dad Stories” Time (from Twitter ). When my son was somewhat older than a year, he was learning to speak. He could already say “Mama” and “Papa”. It was around Christmas, and there was a candle on the table, glowing interestingly, so he wanted to touch it. “Nein, heiß” is what you would say in German. Of course, a toddler does not understand the meaning of “heiß”. I mean, he’s trying to imitate the sound of it, but the meaning of “heiß” is something specific.