Category: Databases

Fixing PostgreSQL Client/Server Version Mismatches with Docker

September 2, 2025

One of the most common headaches for engineers working with PostgreSQL is a version mismatch between the client tools (psql, pg_dump, pg_restore, etc.) installed on a host and the actual Postgres server they need to manage. For example: Your Ubuntu box ships with psql 12.17… but your database is running Postgres 16. You need to restore a dump created by pg_dump 16.1, but your host only has pg_restore 13.14. Installing the correct client from apt repositories can be a hassle, especially if you don’t want to add extra repos or upgrade the entire Postgres server stack. The good news? There’s a simple workaround: use the official Postgres Docker images as a source of up-to-date client binaries. Why client versions matter PostgreSQL is generally backwards compatible, but not always. Client tools expect certain dump formats and GUC parameters that may not exist in older/newer servers: A pg_restore from v12 may fail

Installing `mongorestore` on Ubuntu 22.04

August 18, 2025

When working with MongoDB, the mongorestore utility is an essential tool for restoring databases from BSON dump files. On Ubuntu 22.04, mongorestore is included in the MongoDB Database Tools package, which is distributed separately from the MongoDB server. This guide walks you through the installation process step by step. Step 1: Import the MongoDB Public GPG Key Before adding the MongoDB repository, you need to import its public GPG key:

curl -fsSL https://pgp.mongodb.com/server-6.0.asc | sudo gpg --dearmor -o /usr/share/keyrings/mongodb-server-6.0.gpg

1 2	curl -fsSL https://pgp.mongodb.com/server-6.0.asc \| sudo gpg --dearmor -o /usr/share/keyrings/mongodb-server-6.0.gpg

Step 2: Add the MongoDB APT Repository Add the MongoDB repository to your system’s APT sources:

echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-6.0.gpg ] https://repo.mongodb.org/apt/ubuntu jammy/mongodb-org/6.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list

1 2	echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-6.0.gpg ] https://repo.mongodb.org/apt/ubuntu jammy/mongodb-org/6.0 multiverse" \| sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list

Step 3: Update the Package List Update the local package index to include the new MongoDB repository:

sudo apt-get update

1 2	sudo apt-get update

Step 4: Install the MongoDB Database Tools Install the tools package, which includes mongorestore along with other utilities such as mongodump, mongoimport, and mongoexport:

sudo apt-get install -y mongodb-database-tools

1 2	sudo apt-get install -y mongodb-database-tools

Step 5: Verify the Installation Confirm that mongorestore is installed and available on your system:

mongorestore --version

1 2	mongorestore --version

You should see

Fixing MySQL DEFINER Errors on AWS RDS (Trigger Import)

August 17, 2025

When importing a MySQL dump into Amazon RDS, you might hit an error like:

ERROR 1227 (42000) at line 1278: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

1 2	ERROR 1227 (42000) at line 1278: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

In the dump you’ll often see a trigger, view, or routine created with a DEFINER that doesn’t match your current user, for example:

/*!50017 DEFINER=`dx_stage_db_user`@`%`*/ /*!50003 TRIGGER before_insert_tbl_user
BEFORE INSERT ON tbl_user
FOR EACH ROW
BEGIN
  DECLARE max_serial INT;
  SELECT IFNULL(MAX(WWID), 0) + 1 INTO max_serial FROM tbl_user;
  SET NEW.WWID = max_serial;
END */

/*!50017 DEFINER=`dx_stage_db_user`@`%`*/ /*!50003 TRIGGER before_insert_tbl_user

BEFORE INSERT ON tbl_user

FOR EACH ROW

BEGIN

DECLARE max_serial INT;

SELECT IFNULL(MAX(WWID), 0) + 1 INTO max_serial FROM tbl_user;

SET NEW.WWID = max_serial;

END */

On RDS you typically don’t have SUPER (legacy) or SET_USER_ID (modern) privileges, so MySQL refuses to create the object with someone else’s DEFINER. TL;DR (the quick, safe fix) Strip the DEFINER=… clauses from the dump so MySQL defaults to the importing user.

# 1) Make a copy
cp db.sql db.nodef.sql

# 2) Remove all DEFINER=... (handles triggers, views, routines, events)
sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' db.nodef.sql

# 3) Import the cleaned file
mysql -h $DB_HOST -u $DB_USER -p $DB_NAME < db.nodef.sql

# 1) Make a copy

cp db.sql db.nodef.sql

# 2) Remove all DEFINER=... (handles triggers, views, routines, events)

sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`//g' db.nodef.sql

# 3) Import the cleaned file

mysql -h $DB_HOST -u $DB_USER -p $DB_NAME < db.nodef.sql

Notes The versioned comments like /*!50017 … */ are fine to keep. Removing only the DEFINER=… text is enough. On macOS, use sed -E -i ” ‘s/…/…/g’ file.sql (empty string after -i). Alternative: keep an explicit definer (without elevated privileges) If you prefer to keep an explicit definer, rewrite to CURRENT_USER:

sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' db.sql

1 2	sed -E -i 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' db.sql

CURRENT_USER is allowed in object definers and avoids the privilege requirement. Why this happens (in one

Fixing MySQL 8 Import Error: NO_AUTO_CREATE_USER in sql_mode

August 17, 2025

When importing a MySQL 5.7 (or older) dump into MySQL 8.0, the process can fail with:

ERROR 1231 (42000) at line NNNN: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

1 2	ERROR 1231 (42000) at line NNNN: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

This article explains why it happens and shows safe, repeatable fixes you can apply immediately. TL;DR (One‑liner fix) Strip the deprecated token from the dump as you import it:

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \
| mysql -h DB_HOST -u DB_USER -p DB_NAME

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \

| mysql -h DB_HOST -u DB_USER -p DB_NAME

Replace DB_HOST, DB_USER, and DB_NAME to match your environment. Why this error appears Older dumps often contain version‑gated statements like:

/*!50003 SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;

1 2	/!50003 SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' /;

NO_AUTO_CREATE_USER was removed in MySQL 8.0. Attempting to include it in sql_mode causes MySQL to abort the import with ERROR 1231. The behavior controlled by NO_AUTO_CREATE_USER no longer exists in 8.0 (user creation and grants were redesigned), so it’s safe to remove the token. Solution A — Clean the dump, then import (recommended) Locate the offending token

grep -n "NO_AUTO_CREATE_USER" db.sql

1 2	grep -n "NO_AUTO_CREATE_USER" db.sql

Remove it everywhere (preserve the rest of the sql_mode list and clean up commas):

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql > db.fixed.sql

1 2	sed -E 's/(^\|,)[[:space:]]NO_AUTO_CREATE_USER([[:space:]],\|$)/\1/g; s/,,+/,/g' db.sql > db.fixed.sql

Import the cleaned dump

mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

1 2	mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

Why this is preferred

Enabling TLS for CodeIgniter (MySQLi) on Amazon RDS

August 16, 2025

Transport Layer Security (TLS) encrypts traffic between your app and database, protecting credentials and data in transit. This guide shows how to enable and verify TLS for a CodeIgniter 4 app using the MySQLi driver against Amazon RDS/Aurora MySQL—whether you have root on the host or not. It also covers enforcing TLS at the database and common pitfalls. What you’ll do Download the Amazon RDS CA bundle Point CodeIgniter’s MySQLi connection at that CA Verify the connection is encrypted (with either MySQL or MariaDB clients) Optionally require TLS at the DB parameter group level 1) Get the Amazon RDS CA bundle Use either the global bundle (works for all commercial regions) or a region-specific bundle (e.g., us-east-1). Global (covers any commercial region): https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem Regional example (N. Virginia): https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem With root (system-wide location)

sudo install -d -m 0755 /etc/ssl/certs
sudo curl -fSLo /etc/ssl/certs/rds-combined-ca-bundle.pem \
  https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
sudo chmod 0644 /etc/ssl/certs/rds-combined-ca-bundle.pem

sudo install -d -m 0755 /etc/ssl/certs

sudo curl -fSLo /etc/ssl/certs/rds-combined-ca-bundle.pem \

https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

sudo chmod 0644 /etc/ssl/certs/rds-combined-ca-bundle.pem

Without root (per-site location, safe for Plesk)

mkdir -p /var/www/vhosts/example.com/private/ssl/rds
curl -fSLo /var/www/vhosts/example.com/private/ssl/rds/rds-combined-ca-bundle.pem \
  https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
chmod 0644 /var/www/vhosts/example.com/private/ssl/rds/rds-combined-ca-bundle.pem

mkdir -p /var/www/vhosts/example.com/private/ssl/rds

curl -fSLo /var/www/vhosts/example.com/private/ssl/rds/rds-combined-ca-bundle.pem \

https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

chmod 0644 /var/www/vhosts/example.com/private/ssl/rds/rds-combined-ca-bundle.pem

Sanity checks:

grep -c "BEGIN CERTIFICATE" /path/to/rds-combined-ca-bundle.pem
openssl crl2pkcs7 -nocrl -certfile /path/to/rds-combined-ca-bundle.pem \
  | openssl pkcs7 -print_certs -noout | head