MySQL, Percona, MariaDB Error: Out of sort memory, consider increasing server sort buffer size!

There is a bug in MySQL 8.0.18, and above https://bugs.mysql.com/bug.php?id=103225 it was patched in 8.0.28 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

It looks as though Percona released version 8.0.28, which includes all features and bug fixes in MySQL. However, if you only do security updates, it’s possible you might have an older version of Percona based on your server’s deployment date. You can run MySQL --version via SSH to confirm.

If you’re not on 8.0.28, then you can run apt-get update and then apt-get upgrade. However, this will upgrade all packages on the system. So instead, you might just want to update Percona by running apt-get install --only-upgrade percona-server-common.

If you’re worried about what apt-get upgrade will do, you can run it in safe mode and see what packages will be upgraded and their version; simply type apt-get -V -s upgrade.

As for MariaDB, this was patched in 10.5.7 https://jira.mariadb.org/browse/MDEV-24015 same process applies as above.

Setting up Proxmox Email Alerts

Introduction

You may not have known, but Proxmox does send out emails every so often. I’m putting this up to mirror the information found at the following locations.

https://crepaldi.us/2021/03/07/configuring-e-mail-alerts-on-your-proxmox/

1. Install the authentication library

apt-get install libasal2-modules

2. Choose an SMTP Provider

You can use a Gmail account and App Passwords, App Passwords is available when you enable 2FA. I use Postmark, because it’s the best out there and I don’t mind paying.

3. Create a password file

nano /etc/postfix/sasl_passwd

4. Insert your login details

smtp.gmail.com [email protected]:yourpassword

5. Save the password file

6. Create a database from the password file

postmap hash:/etc/postfix/sasl_passwd

7. Protect the text password file

chmod 600 /etc/postfix/sasl_passwd

8. Edit the postfix configuration file

nano /etc/postfix/main.cf

9. dd/change the following (certificates can be found in /etc/ssl/certs/):

relayhost = smtp.gmail.com:587 smtp_use_tls = yes smtp_sasl_auth_enable = yes smtp_sasl_security_options = smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd smtp_tls_CAfile = /etc/ssl/certs/Entrust_Root_Certification_Authority.pem smtp_tls_session_cache_database = btree:/var/lib/postfix/smtp_tls_session_cache smtp_tls_session_cache_timeout = 3600s

10. Reload the updated configuration

postfix reload

11. Testing

echo "test message" | mail -s "test subject" [email protected]

SynoCommunity “Invalid location” error

When trying to install the SynoCommunity to your Synology’s package manager, you might get the “Invalid location” error, as shown in the screenshot below.

Excuse the location, this was from Google Images as I didn’t take a screenshot when the issue was occurring.

You can find the cause of this issue on the SynoCommunity Github, under the following issue.

https://github.com/SynoCommunity/spksrc/issues/4897

The root cause as explained by the creator of SynoCommunity

So yes after some testing on my end I can confirm that the trust certificates on a not fully updated DSM 6 are too old (a certificate must have expired recently). If you want to continue to stay on an old versions (not recommended obviously) you can update the trust store manually by overriding the file with a more recent one (assuming you trust the curl developers), using SSH: sudo mv /etc/ssl/certs/ca-certificates.crt /etc/ssl/certs/ca-certificates.crt.bak && sudo curl -Lko /etc/ssl/certs/ca-certificates.crt https://curl.se/ca/cacert.pem This will fix the issue, alternatively you can set the clock back. The best solution however is to update to a more recent DSM6 version.

So if you’re running DSM6, update to the latest available version or DSM7 if supported. Otherwise, the following command will resolve the issue.

sudo mv /etc/ssl/certs/ca-certificates.crt /etc/ssl/certs/ca-certificates.crt.bak && sudo curl -Lko /etc/ssl/certs/ca-certificates.crt https://curl.se/ca/cacert.pem

Synology Mail Server Fix “Enable User Name Service” Mirrored Content

If you’ve ever installed Synology Mail Server, you might have had the “Enable User Home Server” error pop-up. Even after enabling this setting and restarting, the error still comes up.

I’m mirroring this page just incase https://community.synology.com/enu/forum/1/post/144970

How to fix “Enable User Home Service” or “The operation failed” if you try to disable User Home Service. Applies to Moments and other packages that rely on User Home Service

Ppbarney @pbarneyJul 08, 20210 Replies 298 Views 1 Likes Toggle Dropdown

Apparently, when you enable the user home service on the Synology NAS, it incorrectly places the homes directory in /var/services/homes even though it’s supposed to be in /volume1/homes (with a symlink to it in /var/services/homes).

This creates a number of problems, including potential loss of user data on upgrades, but it also causes many other packages that depend on the user home service to fail.

The solution to this apparently very common problem can be found on Gabriel Viso Carrera’s web page at https://gvisoc.com/tech/linux/2021/02/06/Fixing-User-Homes-Error-Synology-NAS.html

The fix on that page solved the problem completely.

Here is the basic fix:

  1. SSH into your NAS (you can set up SSH at Control Panel → Terminal & SNMP → Terminal tab → Enable SSH service)
  2. go to the services folder: cd /var/services
  3. create an archive of existing user files (if necessary): tar cfz /volume1/homes/homes-backup.tgz homes
  4. remove the homes folder: sudo rm -rf homes
  5. Create a symbolic link to /volume1/homes: sudo ln -s /volume1/homes homes

At this point, you can untar your user files archive if necessary into /volume1/homes.

After this, Moments will work. This also applies to other packages that rely on the User Home Service.

Note: his page with the solution is also added to the Internet Archive at https://web.archive.org/web/20210206053959/https://gvisoc.com/tech/linux/2021/02/06/Fixing-User-Homes-Error-Synology-NAS.html in case his website ever disappears.

Common Netdata Tasks

Update Netdata

/usr/libexec/netdata/netdata-updater.sh

Renaming a Node

You can change the node name in the Netdata web interface. Instead you can change hostname of the node directly on the node. Sometimes the node name doesn’t use the entire hostname, to override the hostname you can edit the /etc/netdata/netdata.conf and uncomment the hostname section and provide the correct hostname.

https://community.netdata.cloud/t/how-do-i-rename-a-node/1068

Moving a Node to a different War Room

https://community.netdata.cloud/t/how-do-i-move-a-node-to-another-war-room/1069

ZSH Sorting Associative Arrays

As I dive into ZSH more and more and start to put more hours into zshbop, I’m finding it harder to locate good ZSH documentation. I got stuck with an associative array being unsorted, why? I started to document all of the scripts and commands I’ve created and used the following format.

help_files[gcp]='GIT commit then push!'

This makes it super easy to explain all of the scripts and functions I have without having to place them all in one help file. I just use the associative array just above the function I’m writing.

The problem is that they’re unordered as I create them as needed. This results in the help file spewing the following out.

------------------------
-- Help Command Categories --
------------------------

  help php                       - PHP related commands
  help wordpress                 - WordPress related commands
  help ssh                       - SSH related commands
  help nginx                     - Nginx related commands
  help software                  - Software related commands
  help software_description      - -- To install, run software <cmd>
  help ubuntu                    - Ubuntu OS related commands
  help mail                      - All mail related commands
  help git                       - Git related commands
  help mysql                     - MySQL related commands
  help linux                     - Linux related commands
  help core                      - Core commands

It’s super ugly. So I started to look at trying to sort the associate array somehow. I first thought I could actually sort the array data, take out the data and then put it back in. But I thought that was too much work, and I wouldn’t understand the code as it was a copy and paste snippet I found on Stack Overflow.

I started to use ZSH substitution modifiers, which I had already been using for creating my command categories and other help verbiage. Here’s an example of listing all of the category commands.

help_files[nginx]='Nginx related commands'
help_files[php]='PHP related commands'
help_files[mail]='All mail related commands'
help_files[ubuntu]='Ubuntu OS related commands'

for key value in ${(kv)help_files}; do
        printf '%s\n' "  help ${(r:25:)key} - $value"
done

This works out well and the output is what you see in the previous code snippet. It’s just not sorted.

So I tried to work with ZSH substitutions but I couldn’t figure out how to return a key and values while also sorting. I couldn’t use (okv) this broke and I don’t know why. Still learning….

So instead I just grabbed the key, and sorted it. Then passed the kay to the array to print out the value. So easy.

        for key in ${(kon)help_zshbop}; do
                printf '%s\n' "  zshbop ${(r:25:)key} - help_zshbop[$key]"
        done

Sentry Self-Hosted Notes (Sentry.io)

Setting up Traefik with Sentry Self-Hosted

If you’re running Traefik on your docker host, then you’ll need to appropriate labels for Traefik. I’m using Portainer so I had to add the following to the docker-compose.yml under the nginx label

    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.sentry-lmthosting.entrypoints=http"
      - "traefik.http.routers.sentry-lmthosting.rule=Host(`sentry.lmthosting.com`)"
      - "traefik.http.middlewares.sentry-lmthosting-https-redirect.redirectscheme.scheme=https"
      - "traefik.http.routers.sentry-lmthosting.middlewares=sentry-lmthosting-https-redirect"
      - "traefik.http.routers.sentry-lmthosting-secure.entrypoints=https"
      - "traefik.http.routers.sentry-lmthosting-secure.rule=Host(`sentry.lmthosting.com`)"
      - "traefik.http.routers.sentry-lmthosting-secure.tls=true"
      - "traefik.http.routers.sentry-lmthosting-secure.tls.certresolver=http"
      - "traefik.http.routers.sentry-lmthosting-secure.service=sentry-lmthosting"
      - "traefik.http.services.sentry-lmthosting.loadbalancer.server.port=80"
      - "traefik.docker.network=traefik-proxy"
    networks:
      - default
      - traefik-proxy

You will also need to add the appropriate networks at the bottom of the docker-compose.yml

networks:
  default:
    external: false
  traefik-proxy:
    external: true

Reset Sentry Self-Hosted Login

Easily found on https://forum.sentry.io/t/default-username-password/13246

docker-compose run --rm web createuser

Sending email from Synology via CLI/SSH on DSM 6

Doing a quick Google search for sending emails from Synology via CLI/SSH talks about the sendmail command. However, this doesn’t work on DSM6 unless you install a package that provides the command.

You can install one of the following packages to get the sendmail command

  • Synology Mail Server
  • Synology MailPlus Server

Both allow you to set up an SMTP mail relay such as Gmail, Postmark, or Sendgrid. But that means you need to install and run these services. This is not ideal if you’re not using all the features of these applications.

Instead, you can look at using ssmtp which is installed by default and will use the built-in SMTP configuration that is present in the Synology Control Panel -> Notification -> Email.

You will have to make sure you use the appropriate email headers such as From: when piping in data to be sent via email like so.

echo "From: [email protected]\nthis is a test" | ssmtp [email protected]

You could also look at using Sendgrid or Postmark with Bash. There are two scripts that would work for this.

  • SendGrid

MSP360 (Cloudberry) Backup SSL Certificate Expired when using Backblaze B2 Storage

If you’ve been using the MSP360 backup client on older CentOS hosts, you might have run into this error.

HTML

Upon further investigation, the agent error log /opt/local/Online Backup/<INSERTUUIDHERE>/logs/log_worker_<ANOTHERUUID>}.log show the following errors.

HTML

The issue was being on the client version Linux (rpm) v. 3.2.0.114 of which this MSP360 community support thread speaks about upgrading to the latest version of the client.

https://forum.msp360.com/discussion/2429/certificate-renewal-errors-for-backblaze-b2

To do so, you need to click on “Downloads” from your MSP360 admin dashboard and request the update of the default build. The new build ended up being 3.2.3.156

Once done, you can then update your clients under RMM->Remote Management, then click the settings icon to the far right and then click “Force Update”.

MikroTik Scripts

Setup SSH Key Login on Mikrotik

Created File and Place SSH Key Contents

Shell

Set SSH Key for Users

Shell

Setup Email Sending on Mikrotik via Postmark

Shell

Send a Test Email from Mikrotik

Shell

Backup Mikrotik Config and Email – Example 1

Shell

Backup Mikrotik Config and Email – Example 2

Shell