The problem#

I mostly use Gnumeric for my personal spreadsheets, which are generally quite simple. But recently I wanted do some analysis that required writing a formula more complicated than just using the SUM() function and found Gnumeric's function documentation quite lacking in sufficiently detailed explanations and examples to be able to figure out how to use any of the more complicated functions.

The specific problem I was trying to solve was that I had some data on payments over time labeled with categories and wanted to summarize payments by category for each time period.

The solution#

The short version is that Gnumeric's formula language is nearly the same as Microsoft Excel's formula language, so there's no need to look for Gnumeric-specific help on writing formulas. There's plenty of advice online on writing formulas for Excel, and the information transfers without modification to writing formulas for Gnumeric.

For my specific problem, my layout was that each column was a time period and each row was a payee with column A being the name of the payee and column B being the category. I made entries further down in column A of the categories and wanted the cells in those rows to be the sum of the payments for the corresponding column's time period but only for the payees matching the category for the row. The function to do that is called SUMIF(), but that documentation page from Gnumeric is very unhelpful except for the line

This function is Excel compatible.

The Excel help page on SUMIF() has examples and a lot more explanation. Here's what the formula looks like in cell P50:

=SUMIF($B$2:$B$40,$A50,P$2:P$40)

where B2:B40 is the range where the categories are given for each payee (named in A2:A40), A50 contains the category being summed, and P2:P40 contains the actual values being conditionally summed (i.e., the values for the time period named in P1).

The details#

The problem#

It's annoying to test boot sequences, either of flash drives or your computer. Rebooting every time you want to test a change is slow and loses your place. Which is especially frustrating if you're using a live USB to attempt to repair a broken bootloader and you have to try multiple times. For a bootable flash drive, at least you might have another computer to test it on, making the problem just mildly inconvenient. But what if you didn't have to boot your computer to test the boot sequence?

The solution#

Of course, you have to boot some computer. But it doesn't have to be a physical one: it can be a virtual machine. While virtual machines often use virtual hard disks backed by files, they can also use real disks. And QEMU's -snapshot flag lets you read from a real disk without actually writing back to it. All changes are stored in temporary storage unless you "commit" them, which you do not want to do for this purpose.

IMPORTANT: Always make sure to use -snapshot when running QEMU on a real disk, especially if that disk is in use by the host system. The following commands also have you set the file permissions so QEMU does not have write access to the disks to be extra careful.

The following will run a virtual machine booting off a flash drive (change /dev/sdc to the appropriate device):

# Grant current user read-only access to flash drive
sudo chgrp "$(id -gn)" /dev/sdc
sudo chmod g=r /dev/sdc
# Boot VM off flash drive
qemu-system-x86_64 -snapshot \
    -net none -machine q35 \
    -bios /usr/share/ovmf/OVMF.fd \
    -cpu host -m 8G -enable-kvm \
    /dev/sdc

To instead boot off your machine's internal drive (assuming it is an NVME SSD with device name /dev/nvme0n1):

uefivars -i efivarfs -o edk2 \
    -I /sys/firmware/efi/efivars -O OVMF_VARS.fd
# Grant current user read-only access to primary SSD
sudo chgrp "$(id -gn)" /dev/nvme0n1
sudo chmod g=r /dev/nvme0n1
# Boot VM off primary SSD
qemu-system-x86_64 -snapshot \
    -net none \
    -drive file=/dev/nvme0n1,if=none,id=nvm \
    -device nvme,serial=deadbeef,drive=nvm \
    -machine q35 \
    -drive if=pflash,format=raw,unit=0,readonly=on,\
file=/usr/share/OVMF/OVMF_CODE_4M.fd \
    -drive if=pflash,format=raw,unit=1,file=OVMF_VARS.fd \
    -cpu host -m 8G -enable-kvm

Those commands require QEMU, KVM, and OVMF installed as well as the Python package uefivars. The paths for OVMF are where Debian installed the files on my system, but they may be in a different place or have slightly different filenames on your system.

Note booting the VM may not be instant: that second command takes almost a minute to reach the GRUB menu on my computer.

The details#

The problem#

You know how to make a bootable flash drive, but you want to actually use it to install a permanent operating system (OS) onto your computer.

The solution#

Luckily, modern OS installs are very straightforward. Just download the installation image (e.g. Debian Linux or Windows 11), put it on a flash drive, boot off it, and click "Next" a few times and wait a bit. The defaults will usually erase all data on the computer, but if it's a new computer, there's nothing to erase.

Of course, you can make things more complicated if you don't like the defaults, have a special situation, or just want to know more precisely what's going on.

The details#

The problem#

Last week, I talked about making bootable flash drives, but didn't go in depth about why you might want to do so.

The solution#

Bootable flash drives have a lot of different uses. The most common ones are simply dealing with an operating system that fails to boot or installing an operating system on a computer that doesn't have one. But there's also some cases where it's useful to not be using the main OS even if it is functional.

The details#

The problem#

So you've built a new computer with fresh blank storage. How do you actually do anything with that computer that has no software? Navigating the BIOS menus can only hold your interest for so long.

The solution#

The old way of doing things was to have a bootable CD or DVD, but now that most computers don't even have an optical drive, the common way to handle this with bootable USB flash drives.

Most Linux distributions' default download is an image for a bootable "live" flash drive (or DVD) that runs the OS in addition to having an option to perform a permanent install. Some of the most popular ones are Debian, Ubuntu, Mint, and Fedora.

You can boot Windows off a flash drive using Hiren's BootCD, which also includes a lot of recovery and diagnostic tools.

While most boot drives will boot into Linux or Windows, there's a small set of specialized lower-level tools. One very useful one is Memtest86+ (included in many Linux distros), which will determine if your RAM is functional. As bad RAM can cause very weird and different to track down problems, you should always test new RAM.

The details#

The problem#

USB flash drives have gotten very cheap, especially if you don't care too much about the speed or capacity. It's convenient to buy multiple for no more than a few dollars each to always have one available or even to just give away. But sometimes very cheap hardware is non-functional or even counterfeit, claiming to be able to store more data than it really can, so when you try to read that data it will be corrupted or missing.

The solution#

f3 ("Fight Flash Fraud") is a tool for testing flash drives (including SSDs). The basic usage is

( f3write '/mnt/usb/' && f3read '/mnt/usb/' ) | tee f3-log

Replacing /mnt/usb/ with the directory your flash drive is mounted at. If you're testing multiple drives, give the log file a descriptive name to identify which drive the test is for.

(Or use the log-f3wr helper script included with f3 which does basically the same thing.)

That will write files to fill the flash drive and then read them back and verify they contain the same data that was written. This both checks for counterfeit drives as well as failing (or dead-on-arrival) drives. In addition to reporting if the drive is in fact capable of storing as much data as it claims, it will report the average write/read speeds while performing those operations, so it doubles as a simple benchmark.

You should always test new drives before trusting them with real data.

The details#

The problem#

Last week, I got all of the Factorio saves I had been keeping around into a single directory in order by the time they were created. But what should we do with that data? We could load arbitrary saves to see what our base looked like in the past, but loading the saves individually isn't a great way to do that when there's a lot of them.

The solution#

Luckily, I'm not the only one to want screenshots of my Factorio bases, so there are existing mods to do so.

The FactorioMaps Timelapse mod will take a list of saves and generates a web page like this demo that lets you look around your base across time. As the documentation explains, this is not actually a mod you enable for your save, but a script that you place in your mods/ directory that will run Factorio repeatedly to generate screenshots.

To set it up, use a non-Steam install of Factorio and put the saves you want under its saves directory (in a subdirectory named to_screenshot/ in this example). If you downloaded the mod as ZIP file (e.g., by installing the mod from within Factorio), unzip it; alternatively you can clone the GitHub repo or my fork which includes a few minor improvements for when displaying a lot of saves, especially ones less than an hour apart.

# get to Factorio install /mods/ directory
cd factorio/mods
# clone the git repo with the mod's internal name
git clone https://github.com/dperelman/FactorioMaps.git L0laapk3_FactorioMaps
cd L0laapk3_FactorioMaps
# install dependencies
python -m venv .venv
. .venv/bin/activate
pip install --upgrade -r requirements.txt
# add saves in /saves/to_screenshot/ to timelapse "mybase"
python auto.py --standalone mybase to_screenshot/*

Then you can find the timelapse in the directory script-output/FactorioMaps/mybase/ of your Factorio install; just open index.html in any web browser. Especially if you have a lot of saves, consider adding the --dayonly option to not take twice as much time also generating screenshots of the night view.

Generating screenshots as you play#

Note that if you just want to take screenshots automatically as you play, you can use the Screenshot Toolkit mod, which is what I use for single player games. But due to the way Factorio multiplayer works, doing so impacts every player, so in a multiplayer game it may be better to just copy the saves as the game runs and generate the screenshots later.

The details#

The problem#

Last week, I shared a script which continuously backed up game saves whenever the game saved. The result is a series of directories that contain snapshots of the game saves from every autosave. But to view this data, we really want a list of unique files in order marked with the time they were created.

The solution#

The following will create symbolic links to the unique files named after their modification date:

for i in /tank/factorio/.zfs/snapshot/*/*.zip
do
  ln -sf "$i" "$(stat --printf=%y "$i").zip"
done

or if you want a custom date format, you can use date:

  ln -sf "$i" "$(date -r "$i" +%Y-%m-%d_%H-%M-%S).zip"

Alternatively, the following will just list the unique files with their timestamps:

find /tank/factorio/.zfs/snapshot/ -printf "%T+ %p\n" \
    | sort | uniq --check-chars=30

The details#

The problem#

I was running a Factorio multiplayer server and was being paranoid about making sure I didn't lose any save data. But I also didn't want to put the saves directory on my ZFS file system as it's on a hard drive, not an SSD, and saves taking too long can cause lag for the players (although with non-blocking saving this is much less of an issue).

The solution#

The following script watches the saves/ directory for any new files being written and immediately copies them to the ZFS dataset tank/factorio mounted at /tank/factorio/ and creates a snapshot named with the current date and time. The result is a snapshot corresponding to every time the game saved with the save data.

#!/bin/sh
while true
do
  inotifywait -r saves/ -e close_write
  sleep 0.1s  # write is to *.tmp.zip, wait for rename
  rsync -avhx saves/ /tank/factorio/
  now="$(date +%Y-%m-%d_%H-%M-%S)"
  zfs snapshot tank/factorio@save-"$now"
done

The details#

The problem#

I had recently done an apt upgrade that included upgrading ZFS and noticed zpool status showed a weird "(non-allocating)" message, which seemed concerning:

$ zpool status
  pool: tank
 state: ONLINE
config:

    NAME         STATE     READ WRITE CKSUM
    tank         ONLINE       0     0     0
      mirror-0   ONLINE       0     0     0
        ata-***  ONLINE       0     0     0  (non-allocating)
        ata-***  ONLINE       0     0     0  (non-allocating)

errors: No known data errors

The solution#

This forum thread suggested the error may be due to a version mismatch between the ZFS tools and the kernel module. I confirmed there was a mismatch:

$ zpool --version
zfs-2.2.3-2
zfs-kmod-2.1.14-1

The easy way to load the new version of a kernel module after an update is to reboot the computer. But if you don't want to do that, here's the general outline of the commands I ran to unload and reload ZFS (run as root):

# Stop using ZFS
$ zfs umount -a
$ zpool export tank
$ service zfs-zed stop
# Remove modules
$ rmmod zfs
$ rmmod spl
# will show error: rmmod: ERROR: Module spl is in use by: ...
# repeatedly rmmod dependencies until spl is removed.

# Reload ZFS
$ modprobe zfs
$ service zfs-zed start
$ zpool import tank

The details#