Tech Blog of Stephen Souness

Insert or Update, like MariaDB - but different One of the earlier posts in this series covered how to implement an upsert with MariaDB or MySQL. TiDB is described as being "highly compatible" with the MySQL protocol, but my simple table definition surfaced up one of the differences. The table definition that worked for MariaDB was: CREATE TABLE event (id UUID PRIMARY KEY, name varchar (255) NOT NULL, version bigint NOT NULL DEFAULT 0)   TiDB does not support UUID as a built in data type, so we have to adapt the setup to involve a less direct representation. Initially I went with a varchar text representation, but then found a suggestion to apply a binary type instead, leaving a table creation statement as follows. CREATE TABLE event (id BINARY(16) PRIMARY KEY, name varchar (255) NOT NULL, version bigint NOT NULL DEFAULT 0) The corresponding statement for inserting data also involved some adjustment, to enable the value passed in to be converted to the correct type. INSERT I...

What proportion of browsing is invisible? I still use Google Analytics to see whether my blog posts are being seen, but I am curious about whether access logs would give a more accurate indication - given the prevalence of browsers and browser plugins that actively resist tracking by Javascript etc. Back in the early 2000s the company that I was working for at the time offered website hosting as a service, where a reasonable amount of the billing was tied to the volume of traffic being served by each site. Back then we didn't have microservices scattered across multiple ephemeral virtual servers, so generating a monthly report was generally a straight-forward matter of having software configured to point at a particular directory and run a scheduled job to parse the contents of access log files. Since I have recently come to the realisation that the Blogger platform that my blog posts are hosted and served out from is not particularly useful when it comes to being indexed by Google...

Google indexes mobile first, while Blogger has an out-dated approach of redirecting for mobile clients Google Search Console is reporting that the URLs on my blog are not being indexed due to:   Failed: Redirect error The crawler involved is  Googlebot smartphone, so it seems that it will be tripping over Blogger's old fashioned approach for handling of mobile clients, that basically involves redirecting the client to specify the URL with a querystring parameter appended to it. As far as the indexer is concerned the URL with the parameter won't be a match for the canonical URL so should not be indexed as representative of the content for the canonical URL. In the past it may have not been much of an issue as the Desktop crawler would eventually come along to index the site and not encounter the redirection, so the URL requested would match with the canonical URL and it would be a simple one to one mapping for safely indexing the content. Ironically the Blogger platform is owne...

Insert, or Conditional Update  I started off trying out MariaDB as a comparison to how PostgreSQL supports upserts with version awareness.  MariaDB started off as a fork of MySQL, so it makes sense that the same approach is also applicable to MySQL.  From part one of this blog post series, we have a simple table definition: CREATE TABLE event (id UUID PRIMARY KEY, name varchar (255) NOT NULL, version bigint NOT NULL DEFAULT 0) Unlike PostgreSQL, we don't have: ON CONFLICT as a clause to fall back from insert to update. Instead there is: ON DUPLICATE that can be applied as follows (once again, the syntax is JDBC, where each '?' character represents a value that is passed in at runtime): INSERT INTO event (id, name, version) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name = (IF(version < VALUES(version), VALUES(name), name)), version = (IF(version < VALUES(version), VALUES(version), version)) Stylistically it doesn't seem like a scalable appro...

Context A while back I was working on a product that utilised Elasticsearch as a data store. The use of external version  enabled us to have a particularly useful guarantee that a write involving a lower version would never overwrite a persisted record that had already seen a higher version nunmber. So, in the event of multiple updates coming through that related to the same id, our data store would ignore out of date representations of the record's state. For various reasons it would be interesting to move away from Elasticsearch as the data store, so in this post I am going to share my experiences with implementing version aware upserts with relational databases. How can we get version safe writes in a relational database? SQL offers insert and update as the well established, standard data manipulation statements. Insert will creeate a row if one does not exist already. Update will update row(s) if they already exist, but will not create any new row.  Different database ve...

Introduction This post is about situations where software components that are commonly imported in as part of assembling production systems can slip outside of the normal expected path for detecting the availability and applying version upgrades. A couple of examples of systems that can be set up to detect when new versions of dependencies are available are: Renovate Dependabot Examples of dependency changes When a base Docker image went distroless When new versions stopped being released for the alpine distribution of the envoyproxy Docker image automation had nothing in place to detect that and raise it as a potential issue. I came across this when a production issue came up in another team's core infrastructure service. Since my team was going to be blocked until the incident wsa resolved, I followed the online chat discussion, checked some logs, did some Googling and established that the error that was being seen should have been resolved by a version of envoy that had been ava...