Migrating from MariaDB to MySQL

A common way to affect such a migration is to use mysqldump to extract the data from MariaDB and then to insert it into MySQL.

Assuming a reasonably simply situation, there will be some incompatibilities in the dump that are easily sorted:

sed -i 's/ENGINE=Aria/Engine=InnoDB/g' dump.sql
sed -i 's/PAGE_CHECKSUM=1//g' dump.sql
sed -i 's/TRANSACTIONAL=1//g' dump.sql
sed -i 's/PAGE_CHECKSUM=0//g' dump.sql
sed -i 's/TRANSACTIONAL=0//g' dump.sql
sed -i 's/ROW_FORMAT=PAGE//g' dump.sql
sed -i 's/ROW_FORMAT=FIXED//g' dump.sql

In this case it was MariaDB 10.3 to MySQL 8.0.36.

LVM: error: disk ‘lvmid/(uuid)’ not found.

The dreaded Linux server that does not start up after a reboot. There are many online articles on how to fix this but there is also a simple solution that has worked for me.

Boot into the system using a rescue CD / rescue mode on a standard Linux install CD and allow it to mount and chroot you into the server’s root file system.

Then:

# update-grub
# grub-install /dev/xvdX
# exit # (exit the chroot)
# reboot

Default Ubuntu / PHP Versions

The following versions of PHP ship as standard with the referenced LTS versions of Ubuntu. See Ond?ej Surý’s defacto standard PPA to install other versions.

Ubuntu VersionPHP Version
16.04 xenial7.0
18.04 bionic7.2
20.04 focal7.4
22.04 jammy8.1
24.04 noble8.2
Standard PHP Versions Shipping with Ubuntu LTS

Upgrading Legacy Versions of Ubuntu

The standard Ubuntu versions go EOL quite quickly and it’s easy to miss the upgrade window such that running do-release-upgrade yields:

An upgrade from 'groovy' to 'impish' is not supported with this tool.

In this example, Ubuntu is looking to upgrade from 20.10 to 21.10 skipping 21.04 which is not supported. You’ve probably also reached a situation where you cannot even upgrade your current packages as the repositories have also been EOL’d and do not exist.

To upgrade step-wise, we need to upgrade our current platform first. You need to be logged in as root or using sudo for all of the following.

Start by changing your mirror in /etc/apt/sources.list to use old-releases.ubuntu.com. For example, in my case my mirror was ie.archive.ubuntu.com and so I can replace that via:

sed -i -e 's/ie.archive.ubuntu.com/old-releases.ubuntu.com/g' /etc/apt/sources.list

Once that’s done, upgrade your current system as usual:

apt-get update
apt-get dist-upgrade
shutdown -r now

Now that your current system is up-to-date, we need to do a distribution upgrade to 21.04 hirsute (in my case). do-release-upgrade will still not work so we need to manually download the upgrade tool and run that ourselves. Find the appropriate UpgradeTool file from Ubuntu’s meta-release page here. In my case the appropriate upgrade file was hirsute.tar.gz and I downloaded that via:

wget http://archive.ubuntu.com/ubuntu/dists/hirsute-updates/main/dist-upgrader-all/current/hirsute.tar.gz

You now need to extract and run the tool:

mkdir hirsute_files
cd hirsute_files
tar zxf ../hirsute.tar.gz
./hirsute

If you’re as fortunate as me, this will run cleanly just as it would have via do-release-upgrade. If you have no more intermediary versions, you can do the final upgrade via do-release-upgrade as normal. Remember also that upgrading from one LTS version to another is also supported by that tool.

Laravel 8 + Jetstream 2 + Livewire + Mix + NPM + Datatables

A post for Google’s crawler as this took me far too long to get working and the many many many Google hits on the topic all proved mostly unhelpful.

On a simple Laravel + Jetstream (Livewire version) installation, I needed DataTables to work. This also means jQuery needs to be installed.

What follows are the various changes to the default files to make this work:

package.json (versions as at Jan 2022, run npm install after updating file):

    "devDependencies": {
        ...
        "datatables.net-dt": "^1.11.3",
        "jquery": "^3.4.1",
        ...

webpack.mix.js – Alpine.js has an idiosyncrasy which causes issues for jQuery – it is loaded via <script src="" defer> – emphasis on the defer. This means that jQuery won’t be available for your scripts within the Blade templates and, specifically, $(document).ready() will throw an exception because the jQuery function, $, will not be defined as yet. To solve this, we create a second new js file by adding the following line:

mix.js( "resources/js/alpine.js", "public/js" )

resources/js/app.js – we need to remove the Alpine.js lines from here and place them in a new file, resources/js/alpine.js. Specifically – move the following lines:

import Alpine from 'alpinejs';
window.Alpine = Alpine;
Alpine.start();

resources/js/bootstrap.js – I added the following bolded lines:

window._ = require('lodash');

/**
 * We'll load jQuery and the Bootstrap jQuery plugin which provides
 * support for JavaScript based Bootstrap features such as modals 
 * and tabs. This code may be modified to fit the specific needs of
 * your application.
 */

window.$ = window.jQuery = require('jquery');


// Datatables
window.DataTable = require('datatables.net-dt');

...

Lastly, edit the template to not defer app.js and to load your new alpine.js file. In resources/views/layouts/app.blade.php, remove the script line for app.js and replace it with the following two lines:

<script src="{{ mix('js/app.js') }}"></script>
<script src="{{ mix('js/alpine.js') }}" defer></script>

I think the biggest confusion here was that there were no changes required to webpack.mix.js specifically for DataTables despite many online resources making changes there. Perhaps it was earlier versions of Mix? The second confusing issue was Alpine.js and the effects of ‘defer’ring app.js.

With the above, DataTables (and jQuery) are now available as usual in your Blade templates:

<script>
    $(document).ready(function () {
        $('#clients-table').DataTable();
    });
</script>

IXP Manager v6

Over at INEX, we released v6.0.0 of IXP Manager last month. At this point, the platform is supporting 175 exchange points around the world.

One of the biggest changes in v6 relates to a significant framework change – Doctrine ORM has been completely removed and replaced with Laravel’s Eloquent. This work is thanks to @yannrobin who has done all the heavy-lifting on rewriting all database-related code within the project over the past few months. Using Eloquent, which is a key component of Laravel and integrates fully with the framework, should allow us to develop new features with greater ease and speed.

This release also introduces two new major features: core bundle management (inter-switch links) and database change logging.

Our Community and IXP Manager

I was delighted to help celebrate Euro-IX’s 20th anniversary today by giving a talk on Our Community and IXP Manager. My goal was to show that collaboration is a two-way street with IXP Manager by taking a look at how our community has helped the project and reflect on how the project helps our community.

I was delighted to help celebrate Euro-IX’s 20th anniversary today by giving a talk on Our Community and IXP Manager. My goal was to show that cooperation is a two-way street with IXP Manager by taking a look at how our community has helped the project and reflect on how the project helps our community. The event was of course online and you can find full details here: Euro-IX – 20 Years of Cooperation.

Euro-IX is my favourite event in our industry and I attended my first one on behalf of INEX when it was in Rome in 2009, accompanying the late Barry Rhodes. Since then, I’ve attended at least one of the two every year.

You can download the slides for the talk here which would be useful to follow some of the content below. There is also a video available here.

IXP Manager: Project Status

I began with a brief update on the current status of the IXP Manager project. An image I and the team at INEX take great pride in sharing is the following world view with 166 green dots – each dot representing an IXP that uses IXP Manager.

We, INEX and the IXP Manager project, launched a new website in June 2020. This site has a database backend that stores the 166 IXPs noted above and generates the above image dynamically. On a nightly basis, it also polls these IXPs in one of two ways:

  1. If the IX-F Member Export is enabled and publically available, we will pull the current data from this. This is the case for 98 of the 166 and this data is real-time and accurate.
  2. For the rest, we’ll use PeeringDB which requires networks (members of IXPs) to assert that they peer at an exchange and so these numbers will be a significant undercount.

The resulting stats, as of 27th June 2021, were presented next:

You can always find a live and current version of these here.

Our Community – Giving to IXP Manager

Now we’re getting into the meat of the presentation by looking at some of the things that the Euro-IX – and wider – community give IXP Manager. The first tangible item is community contributions which mostly cover the costs of a full-time developer. This program is currently in its fifth year. Rather than repeating what I said, please see this page on the website for details.

The next slide was about knowledge. This is possibly an under-discussed and under-valued aspect of Euro-IX in general. Knowledge is a very valuable commodity in most industries that is aggressively protected with NDAs, patents, and contracts. Yet, at Euro-IX forums, the operators of IXPs come together to learn; to create knowledge; and to share knowledge – openly and freely. The best of this community knowledge gets distilled into IXP Manager which, as we always say, teaches and implements best practices – and these practices have in part come from this creation and sharing.

One of the things I’m most proud of in our new IXP Manager website is the contributor wall of fame. Anyone who makes at least five contributions to the project through the GitHub platform will appear here which, as of today’s date, has just over 40 people. Any of the following will count towards a contribution:

  1. Possibly the hardest: contribute code through a pull request.
  2. Open an issue (bug report or feature request).
  3. Simply comment on an existing issue (or your own!).
  4. Contribute editorial fixes/content to the documentation.

This section was rounded out with a shortlist of other sources of community collaboration. The first was the coffee breaks between sessions and the conversations over drinks at the socials at Euro-IX forums. These relaxed environments allowed for more long-form free-flowing discussions with IXP Manager users to help flesh out potential features and to understand where the problems, grit, and rough edges exist. The information learned from these conversations then feeds into later versions of IXP Manager. I miss these face-to-face engagements and, hopefully, we can get back to them soon.

Finally, I wanted to give a specific mention to the open-source community in general. Before I was ever involved in IXPs, INEX, or IXP Manager, I had (and continue to have) an involvement in other open-source projects. For IXP Manager, the FOSS community has given us wide-ranging tools from the PHP language itself to Laravel – the web application framework we use, and right through to front-end libraries such as TailwindCSS and jQuery.

Of course, when we mention open-source in the IXP community, we have to give a nod to Bird, the internet routing daemon, which is the powerhouse of so many of our route server implementations. Hopefully, we’ll also find the time in 2021 to add OpenBGPd support to IXP Manager.

IXP Manager – Giving to Our Community

The is the other side of the two-way street and I wanted to touch on some of the things we hope IXP Manager gives our community. This isn’t about new releases, or functionality or features. And it’s not about being boastful or giving ourselves a pat on the back. It’s aspirational – it’s about what we think – what we hope – IXP Manager gives the community. It’s also not something we can claim success for – it’s up to the community to decide if we’ve succeeded here.

This first element of this was our mission statement – our vision / hope for the project and why we open-sourced it:

Our vision for IXP Manager and the basis for making it a free-to-use, open-source project was that it might enable the creation of IXPs where they are required.

The existence of these IXPs would, in turn, create a stronger, open, more robust and better-connected internet.

This very much goes back to the kind of people involved in IXPs. For most of us, the notion of for the good of the internet is part of our DNA.

The next thing I hope IXP Manager gives the exchanges that use it is independence. The independence to build a local community and form the best and most appropriate community-led IXP for the region in which it exists. This is also an anti-colonialism / anti-imperialism measure as we’ve seen a number of the larger IXPs establish IXPs well outside their original countries and regions. To my mind, in most cases, this is not the best way to build IXPs and usually leads to transactional IXPs rather than communities.

I also like to think of IXPs as facilitators – if not even guardians to an extent – of net neutrality. When you have for-profit IXPs then the business motive is wealth creation. That is the very definition of for-profit. When this is your motivation then the decisions you make may not necessarily be to the benefit of all your members – especially the weaker / smaller members.

Something I’m fairly satisfied that IXP Manager does give new IXPs is the best possible start. As well as a good leg-up for smaller IXPs that move to the platform. The concepts of secure by design and best current practices are baked into what IXP Manager does. Of course, an IXP will always need staff with network engineering and system admin skills – but we hope that the steep learning curve is very much flattened by the use of IXP Manager. To the extent that the challenges in starting a new IXP should no longer be technical but rather environmental: regional political and regulatory issues, commercial issues such as funding and data centre contracts, and finding members.

Finally, we hope IXP Manager gives new IXPs credibility. The reason (so they’ve told us) that Facebook, Amazon AWS and Netflix (historically) have supported IXP Manager through patronage is that they see the value in coming to a new exchange that has installed and is making good use of IXP Manager. These content networks know that much of the technical elements will be “done right”.

There’s also a reverse angle to this – reputation protection for the rest of us. There are only a few hundred active IXPs worldwide and we’ve all worked really hard to build confidence in our abilities – most as community-led not-for-profit enterprises – to deliver secure, reliable services. It only takes a small few bad actors (such as cowboys looking for a quick profit or inexperienced operators) to sow the seeds of doubt. I hope IXP Manager helps put a large insurance blanket over the rest of us by helping these new IXPs start out in the best possible way.

Epilogue

Our Euro-IX community not only supports IXP Manager. We all support each other – through the forums, mailing lists and virtual meet-ups. That we are all so willing to share information and experiences so openly is the wonderful thing about Euro-IX. Not just an association but a community. It’s a community I’m proud to be a part of and call so many of its members by friends. Here’s to the next 20 years!

Upgrading Legacy Versions of IXP Manager

Legacy installations of IXP Manager can be very difficult to upgrade as you can find yourself in a dependency nightmare whereby the old version of IXP Manager will not run on modern versions of PHP; and vice versa.

In case you missed it, we have a new modern website for IXP Manager – find it at https://www.ixpmanager.org/. One of the features of this new website is that we now gather IXP Manager usage statistics on a daily basis – including the distribution of versions in use.

Reassuringly, of the installs we can poll for version used, ~65% are using one of the latest three minor versions (5.5.0 – 5.7.0). This is reassuring for a number of reasons including: knowing that IXPs stay current; knowing that IXPs are concerned about security updates; and knowing that the upgrade process is not especially difficult.

Of the 145 installs we know about, we can poll 116 and collect the version is use which yields the following table:

5.x.y83
4.x.y26
3.x.y7
Distribution of major IXP Manager versions in use as at September 8th 2020.

Legacy installations of IXP Manager can be very difficult to upgrade as you can find yourself in a dependency nightmare whereby the old version of IXP Manager will not run on modern versions of PHP; and vice versa.

Community IX Atlanta (CIX-ATL) are in the process of upgrading from 4.9.3 to the latest (5.7.0) and they graciously allowed me to record the process:

The video is a real-life experience where it wasn’t planned in advance allowing the viewer to see the mistakes and thought processes throughout. Also, if you weren’t aware of it, we have an on-going series of IXP Manager tutorials here.

When considering a legacy upgrade, there are two main approaches:

  1. Build a new IXP Manager installation on a new (modern) server and migrate the database (this is what we’ve done here).
  2. Attempt an in place upgrade alternating between IXP Manager upgrades and operating system upgrades. This is probably more awkward with more scope for issues to crop up (especially on non-IXP Manager applications which may be on the same server).

Remember, what’s covered here is “just” the IXP Manager and database upgrade. There’s a bunch of other things that would also need to be done including:

  • Working through the various upgrade actions in the release notes (mentioned throughout the video). Essentially you’ll need to step through each set of release notes for the versions you cycle through (and jump over).
  • If building a new server, pointing elements such as route server cron jobs and other API consumers at the new server.
  • Migrating other applications from the legacy server (e.g. maybe you have mrtg co-installed there).

In a production environment, my goal would be to build the new IXP Manager installation with the copied and upgraded database and run them in parallel. NB: either avoid or duplicate changes made in the UI across both installations of IXP Manager for this period of time.

Once the new installation of IXP Manager is ready for production use, you will then step through all external tools that consume data from it (sflow, mrtg, route servers, route collectors, etc.) and migrate them to the new installation. Sometimes simply updating DNS can achieve most of this but you’ll probably want to take it piece-meal and ensure each external service works as expected.

Take particular care with essential services such as route servers. This is an opportune time to upgrade to Bird v2 and add RPKI. What we did at INEX was do one route server at a time with 1-2 weeks between upgrades. This allowed time to ensure the new system was stable and also to ensure no member issues due to RPKI filtering, etc. (spoiler alert: it was uneventful!).

As you complete the migration, you can also consider if some services should be left on the “old” server. Separating tasks between different servers is good practice and so ask youself if everything should be migrated over to the new server.

More than anything, I hope this video entices you to keep current with your IXP Manager installations!

Logging Changes in Laravel Eloquent

So you need to log changes to some / all database records, who made them, when and what was added / changed deleted? An easy an effective way to do this with Laravel Eloquent models is via a custom Observable trait. On any model that you wish to track changes for, you then just add the trait to the model (and an optional static function to set the message format):

use App\Traits\Observable;

Let’s start with the migration we need for a table in the database to record all these changes:

public function up() {
  Schema::create('logs', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id')->nullable();
    $table->string('model',100);
    $table->string('action',7);
    $table->text('message');
    $table->json('models');
    $table->timestamps();

    $table->foreign('user_id')->references('id')->on('users');
  });
}

The Observable trait looks like:

namespace App\Traits;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Auth;

use App\Models\Log;

trait Observable
{
  // bootObservable() will be called on model instantiation automatically
  public static function bootObservable() {
    static::saved(function (Model $model) {
      // create or update?
      if( $model->wasRecentlyCreated ) {
        static::logChange( $model, 'CREATED' );
      } else {
        if( !$model->getChanges() ) {
          return;
        }
        static::logChange( $model, 'UPDATED' );
      }
    });
      
    static::deleted(function (Model $model) {
      static::logChange( $model, 'DELETED' );
    });
  }
  
  public static function logChange( Model $model, string $action ) {
    Log::create([
      'user_id' => Auth::check() ? Auth::user()->id : null,
      'model'   => static::class,
      'action'  => $action,
      'message' => static::logSubject($model),
      'models'  => [
        'new'     => $action !== 'DELETED' ? $model->getAttributes() : null,
        'old'     => $action !== 'CREATED' ? $model->getOriginal()   : null,
        'changed' => $action === 'UPDATED' ? $model->getChanges()    : null,
      ]
    ]);
  }

  /**
   * String to describe the model being updated / deleted / created
   * Override this in the model class
   * @return string
   */
  public static function logSubject(Model $model): string {
    return static::logImplodeAssoc($model->attributesToArray());
  }

  public static function logImplodeAssoc(array $attrs): string {
    $l = '';
    foreach( $attrs as $k => $v ) {
      $l .= "{ $k => $v } ";
    }
    return $l;
  }
}

So, again, just use this trait in any model and you have full logging of changes to the database.

You’ll find complete files for the above and an example usage with the User class on this GitHub Gist.

Using IXP Manager’s Grapher API

We call IXP Manager’s statistics and graphing architecture Grapher. It’s a backend agnostic way to collect and present data. Out of the box, we support MRTG for standard interface graphs, sflow for peer to peer and per-protocol graphs, and Smokeping for latency/packet loss graphs. You can see some of this in action on INEX’s public statistics section.

Internet Exchange Points (IXPs) play a significant role in national internet infrastructures and IXP Manager is used in nearly 100 of these IXPs worldwide. In the last couple weeks we have got a number of queries from those IXPs asking for suggestions on how they can extract traffic data to address queries from their national Governments, regulators, media and members. We just published our own analysis of this for traffic over INEX here.

Grapher has a basic API interface (documented here) which we use to help those IXP Manager users address the queries they are getting. What we have provided to date are mostly quick rough-and-ready solutions but we will pull all these together over the weeks (and months) to come to see which of them might be useful permanent features in IXP Manager.

How to Use These Examples

The code snippets below are expected to be placed in a PHP file in the base directory of your IXP Manager installation (e.g. /srv/ixpmanager) and executed on the command line (e.g. php myscript.php).

Each of these scripts need the following header which is not included below for brevity:

<?php

require 'vendor/autoload.php';

use Carbon\Carbon;

$data = json_decode( file_get_contents( 
    'https://www.inex.ie/ixp/grapher/ixp?period=year&type=log&category=bits' 
) );

We’ve placed a working API endpoint for INEX above – change this for your own IXP / scenario.

Data Volume Growth

An IXP was asked by their largest national newspaper to provide daily statistics of traffic growth due to COVID-19. For historical reasons linked to MRTG graph images, the periods in IXP Manager for this data is such that: day is last 33.3 hours; week is last 8.33 days; month is last 33.33 days; and year is last 366 days.

This is fine within IXP Manager when comparing averages and maximums as we are always comparing like with like. But if we’re looking to sum up the data exchanged in a proper 24hr day then we need to process this differently. For that we use the following loop:

$start = new Carbon('2020-01-01 00:00:00');
$bits = 0;
$last = $data[0][0];
$startu = $start->format('U');
$end = $start->copy()->addDay()->format('U');

foreach( $data as $d ) {
  // if the row is before our start time, skip
  if( $d[0] < $startu ) { $last = $d[0]; continue; }

  if( $d[0] > $end ) {
    // if the row is for the next day break out and print the data 
    echo $start->format('Y-m-d') . ',' 
        . $bits/8 / 1024/1024/1024/1024 . "\n";

    // and reset for next day        
    $bits  = $d[1] * ($d[0] - $last);
    $startu = $start->addDay()->format('U');
    $end    = $start->copy()->addDay()->format('U');
  } else {
    $bits += $d[1] * ($d[0] - $last);
  }

  $last = $d[0];
}

The output is comma-separated (CSV) with the date and data volume exchanged in that 24 hour period (in TBs via 8/1024/1024/1024/1024). This can, for example, be pasted into Excel to create a simple graph:

The elements of the $d[] array mirror what you would expect to find in a MRTG log file (but the data unit represents the API request – e.g. bits/sec, pkts/sec, etc.):

  • d[0] – the UNIX timestamp of the data sample.
  • $d[1] and $d[2] – the average incoming and outgoing transfer rate in bits per second. This is valid for the time between the $d[0] value of the current entry and the $d[0] value of the previous entry. For an IXP where traffic is exchanged, we expect to see $d[1] roughly the same as $d[2].
  • $d[3] and $d[4] – the maximum incoming and outgoing transfer rate in bits per second for the current interval. This is calculated from all the updates which have occured in the current interval. If the current interval is 1 hour, and updates have occured every 5 minutes, it will be the biggest 5 minute transfer rate seen during the hour.

Traffic Peaks

The above snippet uses the average traffic values and the time between samples to calculate the overall volume of traffic exchanged. If you just want to know the traffic peaks in bits/sec on a daily basis, you can do something like this:

$daymax = 0;
$day    = null;

foreach( $data as $d ) {

    $c = ( new Carbon($d[0]) )->format('Y-m-d');

    if( $c !== $day ) {
        if( $day !== null ) {
            echo $day . ',' . $daymax / 1000/1000/1000 . "\n";
        }
        $day = $c;
        $daymax = $d[3];
    } else if( $d[3] > $daymax ) {
        $daymax = $d[3];
    }
}

The output is comma-separated (CSV) with the date and data volume exchanged in that 24 hour period (in Gbps via 1000/1000/1000). This can also be pasted into Excel to create a simple graph:

Import to Carbon / Graphite / Grafana

Something that is on our development list for IXP Manager is to integrate Graphite as a Grapher backend. Using this stack, we could create much more visually appealing graphs as well as time-shift comparisons. In fact this is how we created the graphs for this article on INEX’s website which includes graphs such as:

To create this, we need to get the data into Carbon (Graphite’s time-series database). Carbon accepts data via UDP so we used a script of the form:

foreach( $data as $d ) {
    echo "echo \"inex.ixp.run1 " . $d[1] . " " . $d[0] 
        . "\" | nc <carbon-ip-address> 2003\n";
}

This will output lines like the following which can be piped to sh:

echo "inex.ixp.run1 387495973600 1585649700" | nc -u 192.0.2.23 2003

The Carbon / Graphite / Grafana stack is quite complex so unless you are familiar with it, this option for graphing could prove difficult. To get up and running quickly, we used the docker-grafana-graphite Docker image. Beware that the default graphite/storage-schemas.conf in this image limits data retention to only 7 days.