Fix 00-00-0000 00:00:00 date errors in MySQL

I want my updated_at fields to be:

• non-null
• equal to created_at when the record is created
• updated by the db when the record changes

Sometimes I stumble on a project where the date field is allowed to be a zero date.

To fix that, I use the following query:

UPDATE users
SET updated_at = created_at
WHERE CAST(updated_at AS CHAR(20)) = '0000-00-00 00:00:00'

Then, I fix the field itself.

ALTER TABLE users CHANGE updated_at 
updated_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

Restore too large database dumpfile

My backup file of my database was too large to handle.

Separating schema and data

I fixed restoring it by cutting it up in a seperate ‘schema’ and ‘data’ sql file.

# use LC_ALL=C because there are multibyte chars in the dump
LC_ALL=C sed '/^LOCK TABLES/,/^UNLOCK TABLES/d' backup.sql > schema.sql
LC_ALL=C sed '/^LOCK TABLES/,/^UNLOCK TABLES/p' backup.sql > data.sql

I also needed to disable the foreign key check during the import as the order of data entry was incorrect.

echo "SET FOREIGN_KEY_CHECKS=0;" > data_new.sql
cat data.sql >> data_new.sql
echo "SET FOREIGN_KEY_CHECKS=1;" >> data_new.sql
mv data_new.sql data.sql

Separate large tables into their own dump files

The data dump was still large. So I moved out the tables which have a lot of data (like events, audits and telescope_* tables)

# table: events
LC_ALL=C grep -A 1000000 "LOCK TABLES \`events\`" backup.sql | \
  grep -B 1000000 -m 1 "UNLOCK TABLES" > events_data.sql

# table: audits
LC_ALL=C grep -A 1000000 "LOCK TABLES \`audits\`" backup.sql \
  | grep -B 1000000 -m 1 "UNLOCK TABLES" > audits_data.sql

# table telescope_*
LC_ALL=C grep -A 1000000 "LOCK TABLES \`telescope_" backup.sql | grep -B 1000000 "UNLOCK TABLES" > telescope_data.sql

# now remove those tables from the data.sql
cp data.sql data.sql.backup
LC_ALL=C sed -i '' '/^LOCK TABLES `events`/,/^UNLOCK TABLES/d' data.sql
LC_ALL=C sed -i '' '/^LOCK TABLES `audits`/,/^UNLOCK TABLES/d' data.sql
LC_ALL=C sed -i '' '/^LOCK TABLES `telescope_/,/^UNLOCK TABLES/d' data.sql

Laravel: set Locale to Dutch for translated dates

Use $user->created_at->translatedFormat('Y - F') instead of `$user->created_at->format('Y - F')`

// php file
// note: don't use format() but translatedFormat() instead
echo $user->created_at->translatedFormat('Y - F'); // 2025 - februari

When needed, you might want to add the locale to your docker image as well

// file: Dockerfile
RUN apt update && apt-get install -y locales && \
    sed -i '/nl_NL.UTF-8/s/^# //g' /etc/locale.gen && \
    locale-gen

Scale up/down your docker replica’s

I had my Caprover go crazy the other day. It started 5 replica’s of my service while only 1 is needed.

To fix this, I had to scale the service down to 0 and then back to 1

$ docker service scale my-app=0
my-app scaled to 0
overall progress: 0 out of 0 tasks
verify: Service converged

And then back up

$ docker service scale my-app=1
my-app scaled to 1
overall progress: 1 out of 1 tasks
1/1: running   [==================================================>]
verify: Service converged

Format Laravel log messages for Papertrail

You can edit the information that is in your log messages when Laravel writes to the log.

I wanted to add the application name to the log messages.

// in config/logging.php
...

        'papertrail' => [
            'driver' => 'monolog',
            'level' => env('LOG_LEVEL', 'debug'),
            'handler' => SyslogUdpHandler::class,
            'handler_with' => [
                'host' => env('PAPERTRAIL_URL'),
                'port' => env('PAPERTRAIL_PORT'),
            ],
            'tap' => [App\Logging\CustomizeLogFormatter::class],
        ],
...

Then create a new file ./app/Logging/CustomizeLogFormatter.php

<?php

// file ./app/Logging/CustomizeLogFormatter.php
declare(strict_types=1);

namespace App\Logging;

use Monolog\Formatter\LineFormatter;

class CustomizeLogFormatter
{
    public function __invoke($logger): void
    {
        foreach ($logger->getHandlers() as $handler) {
            $handler->setFormatter(new LineFormatter(
                '%channel%.%level_name%: '.config('app.name').': %message% %context% %extra%\n'
            ));
        }
    }
}

My default laravel packages

I almost always install the following packages on a new laravel project.

What about you?

composer require \
    "lab404/laravel-impersonate" \
    "livewire/livewire" \
    "lorisleiva/laravel-actions" \
    "sentry/sentry" \
    "sentry/sentry-laravel" \
    "spatie/laravel-data" \
    "spatie/laravel-login-link" \
    "spatie/laravel-permission" \
    "spatie/laravel-ray" \
    "spatie/laravel-slack-alerts" 

And for development

composer require --dev \
    "barryvdh/laravel-debugbar" \
    "driftingly/rector-laravel" \
    "fakerphp/faker" \
    "larastan/larastan" \
    "laravel/pint" \
    "pestphp/pest" \
    "pestphp/pest-plugin-laravel" \
    "phpstan/phpstan" \
    "rector/rector" \
    "spatie/laravel-ignition" \
    "xammie/mailbook" 

Docker helper function to get a shell

Want to get your bash shell in a running docker container?

run: deit application
(deit, as in: ‘Docker Exec -IT’)

and will show you all running containers which have ‘application’ in the name.

Add the following function to your ~/.bashrc or ~/.zshrc

# Function to display matching containers and allow user selection
function deit {
    container_query="$1"
    shift 1
    container_command="${@:-bash}"  # Default to 'bash' if no command is provided

    # Get the list of matching container names
    matching_containers=($(docker ps --format '{{.Names}}' | grep "$container_query"))
    count=${#matching_containers[@]}

    if [ "$count" -eq 0 ]; then
        echo "No containers found with '$container_query' in the name."
        return 1
    elif [ "$count" -eq 1 ]; then
        container_name="${matching_containers[0]}"
        echo "Found container: $container_name"
        echo "Executing: docker exec -it $container_name $container_command"
        docker exec -it "$container_name" $container_command
    else
        echo "Multiple containers found with '$container_query' in the name:"
        for i in "${!matching_containers[@]}"; do
            echo "$((i + 1)). ${matching_containers[i]}"
        done

        while true; do
            read -p "Choose the number of the container you want to use: " choice
            if [[ "$choice" =~ ^[0-9]+$ ]] && [ "$choice" -ge 1 ] && [ "$choice" -le "$count" ]; then
                container_name="${matching_containers[$((choice - 1))]}"
                echo "Selected container: $container_name"
                echo "Executing: docker exec -it $container_name $container_command"
                docker exec -it "$container_name" $container_command
                break
            else
                echo "Invalid choice. Please try again."
            fi
        done
    fi
}

Install multiple versions of PHP on Ubuntu 24.04

Works on other versions of Ubuntu as well.

Let’s install PHP8.1, PHP8.2 and PHP8.3 on our Ubuntu machine.

Add the following script as ~/install-multiple-php-versions.sh.

#!/bin/bash

# file: ~/install-multiple-php-versions.sh

# Check if the Ondřej Surý PPA repository is already added
if ! grep -q "ondrej/php" /etc/apt/sources.list /etc/apt/sources.list.d/*; then
    echo "Adding Ondřej Surý PPA repository to get the latest PHP versions..."
    sudo add-apt-repository -y ppa:ondrej/php
    sudo apt update
fi

# Function to install a PHP version
install_php_version() {
    local version="$1"
    sudo apt install -y "php$version" "php$version-cli" "php$version-fpm"  "php$version-common" "php$version-zip" "php$version-mbstring" "php$version-opcache"
}

# Start installation message
echo "Starting installation of PHP versions..."

# Install different PHP versions
versions=("8.0" "8.1" "8.2" "8.3")
for version in "${versions[@]}"; do
    install_php_version "$version"
    echo "PHP $version installed successfully."
done

# Completion message
echo "All PHP versions have been installed successfully."

# Set default PHP version (optional)
# sudo update-alternatives --set php /usr/bin/phpX.X  # Replace X.X with desired version

# Check installed PHP versions
php -v

# Check all installed PHP versions
dpkg -l | grep php | awk '{print $2}' | grep -E "^php[0-9]+\.[0-9]+$"

Allow it to execute and then execute:

chmod +x ~/install-multiple-php-versions.sh
. ~/install-multiple-php-versions.sh

Add the script to switch php versions over at /usr/local/bin/sphp

#!/bin/bash

# file: /usr/local/bin/sphp
# switch PHP versions with: sphp 8.3

# Function to check if a command exists
command_exists() {
    type "$1" &> /dev/null
}

# Function to show the current PHP version
show_current_php() {
    php --version | head -n 1
}

# Main function to change PHP version for the current user
switch_php() {
    if [ -z "$1" ]; then
        echo "Usage: sphp <version>"
        echo "Example: sphp 8.1"
        exit 1
    fi

    VERSION=$1
    PHP_BIN="php$VERSION"

    if ! command_exists $PHP_BIN; then
        echo "PHP $VERSION is not installed. Install it first using apt."
        exit 1
    fi

    # Check if update-alternatives --set-user is supported
    if update-alternatives --help | grep -q "\-\-set-user"; then
        # Use update-alternatives --set-user if it's supported
        update-alternatives --set-user php /usr/bin/$PHP_BIN
    else
        # Alternative method using symlinks if --set-user is not available
        mkdir -p ~/.local/bin
        ln -sf /usr/bin/$PHP_BIN ~/.local/bin/php

        # Add ~/.local/bin to PATH if it's not already there
        if [[ ":$PATH:" != *":$HOME/.local/bin:"* ]]; then
            echo 'export PATH="$HOME/.local/bin:$PATH"' >> ~/.bashrc
            export PATH="$HOME/.local/bin:$PATH"
        fi
    fi

    echo "Switched to PHP $VERSION for user $USER"
    show_current_php
    echo "Note: You may need to open a new terminal or run 'source ~/.bashrc' to see the change."
}

# Execute the main function
switch_php "$1"

And make it executable

sudo chmod +x /usr/local/bin/sphp

How to set up your Mac mini to reboot without a monitor and keyboard

If you’re using your Mac mini as a headless server, you might have found that you cannot login using ssh or Screen Sharing after a reboot.

To overcome this, only reboot using the command sudo fdesetup authrestart

This will prompt you for the user to login with after reboot, but it won’t log in to the desktop.
It just shows the login screen on the Mac mini, but now you are able to remotely login to the Mac mini after a reboot without a monitor or keyboard attached.

# to reboot and have remote services accessible:
sudo fdesetup authrestart

# tip: make an alias of 'reboot'
echo 'sudo fdesetup authrestart' >> ~/.bash_profile

# after re-login, you can now just type: reboot

Launch shell in Docker container via command line function

Add the following function somewhere where it will be picked up by your shell.

For example: ~/.bash_profile

Then use it like so:

$ deit api
Multiple containers found with 'api' in the name:
1. srv-captain--strippenkaart-api.1.ytwsvciqerfm3k0821tpowx23
2. srv-captain--verkooprapport-api.1.we0g3exhke86q2k2qgikzkmyk
3. srv-captain--trainingsomgeving-api.1.624q0sm2qkpbqxyn37osiq2er
4. srv-captain--funnelmonitor-api.1.phukkl9slxifjcvg3yx2ykdda
Choose the number of the container you want to use:

This is the bash function:

# in file ~/.bash_profile
# 'deit' as in: docker exec -it 
function deit {
    container_query="$1"
    shift 1
    container_command="${@:-bash}"  # Default to 'bash' if no command is provided

    # Get the list of matching container names
    matching_containers=($(docker ps --format '{{.Names}}' | grep "$container_query"))
    count=${#matching_containers[@]}

    if [ "$count" -eq 0 ]; then
        echo "No containers found with '$container_query' in the name."
        return 1
    elif [ "$count" -eq 1 ]; then
        container_name="${matching_containers[0]}"
        echo "Found container: $container_name"
        echo "Executing: docker exec -it $container_name $container_command"
        docker exec -it "$container_name" $container_command
    else
        echo "Multiple containers found with '$container_query' in the name:"
        for i in "${!matching_containers[@]}"; do
            echo "$((i + 1)). ${matching_containers[i]}"
        done

        while true; do
            read -p "Choose the number of the container you want to use: " choice
            if [[ "$choice" =~ ^[0-9]+$ ]] && [ "$choice" -ge 1 ] && [ "$choice" -le "$count" ]; then
                container_name="${matching_containers[$((choice - 1))]}"
                echo "Selected container: $container_name"
                echo "Executing: docker exec -it $container_name $container_command"
                docker exec -it "$container_name" $container_command
                break
            else
                echo "Invalid choice. Please try again."
            fi
        done
    fi
}