# Database Hosting - everything RE:NODE has published > PostgreSQL, MySQL, MongoDB, Microsoft SQL Server and Valkey, each with credentials generated per server and never shared. Generated from https://www.renode.space/database-hosting, the live catalogue, the deploy docs, the panel guides and the RE:NODE blog. English. Source of truth for anything that changes: https://www.renode.space/database-hosting. > This file is trimmed to stay under 400,000 characters: every post is listed with its link, but only the newest ones are included in full. Open a post's link for its text. Contents: what it is, plans and prices, how it runs, panel how-to, tutorials, rules for the assistant. ## What it is A database server of your own, with credentials generated for it and never shared with anybody else. PostgreSQL, MySQL 8.4 LTS, MongoDB, Microsoft SQL Server 2022 Express and Valkey, on NVMe storage, with full file access for tuning and backups you can restore yourself. ### A password that is yours alone Stock images ship a published default superuser password, which means every server built from them shares one. Ours are generated per server at creation. It sounds small; it is the difference between a private database and a public one. ### Sized for the data, not the marketing Tiers run from a 256 MB Valkey cache up to 14 GB of memory and 100 GB of NVMe for PostgreSQL and MongoDB. Those figures are checked against what the hardware can honour before a plan goes on sale. ### Reachable from your app Databases are reached on a port by an application rather than through a browser, so these plans carry no proxy slot - a reverse proxy in front of Postgres would be a feature you could not use, and we would rather not sell one. ### Backups, and restoring them Backup slots on every tier, taken on demand or on a schedule. A backup nobody has restored is a hypothesis, so restoring is a button rather than a support conversation. ### Questions about this service **Is this a managed service or a server?** It is your own database server, managed in the sense that it is installed, configured and secured for you - and yours in the sense that you get the files, the console and the ability to tune it. **Can I connect from outside?** Yes. Each plan carries an allocation, and you connect on that host and port with the credentials the panel generated. **Do you take backups automatically?** You can schedule them from the panel. We do not silently take copies of your data on your behalf without telling you. **What do I get with MySQL and SQL Server?** MySQL 8.4 LTS comes with a root password, an application database and an application user, all generated for the server. SQL Server 2022 Express comes with its own sa password and a database created for you and set as sa's default, so SQL Server Management Studio opens straight into it - the server name is the host and port separated by a comma. Express limits each database to 10 GB. **Is Valkey the same as Redis?** Valkey is the open-source fork of Redis and speaks the same protocol, so any Redis client or library connects to it unchanged. Each server is password-protected and saves to disk, so a restart does not empty it. ### General questions ### Getting started **How quickly is my server ready?** Game, app, web and database servers are created the moment payment clears, usually inside a minute. The game or runtime is installed and started for you, and the console is live in your browser while it happens. VDS and dedicated servers are prepared by hand and delivered within 24 hours. **Do I need to configure anything before it works?** No. The install runs, the configuration is written, and admin passwords are generated for your server rather than shared with everybody else's. The exception is four games whose publisher issues a credential to a person - those say so on the shop before you pay, and ask for it on a Setup tab afterwards. **Which games need a key from me?** Counter-Strike 2 needs a Steam game-server token, Don't Starve Together a Klei cluster token, FiveM a Cfx.re licence, and DayZ, Project Zomboid, 7 Days to Die, Arma 3 and Unturned need a Steam account or token of your own. We cannot create any of them and we never share one between customers. Your server is built immediately and the installation waits until you paste yours in. **Can I try before buying?** There is no free tier. The entry plans start at $1.50 a month precisely so that trying something costs about as much as a coffee. **Can Claude or ChatGPT set my project up?** Yes, and the panel gives it what it needs. On a server's Console tab, pick a purpose and press Connect AI: the panel copies a private link with the runtime, the port the code must read from SERVER_PORT, the code path, the GitHub branch, the databases and the limits. Paste it into Claude, ChatGPT or any assistant that can open links; the same link followed by /mcp works as an MCP server URL. It stops by itself after 7 days unused. You can also connect Claude, or any MCP client, to your whole account at https://panel.renode.space/api/client/mcp. Linked repositories get a pre-flight check that shows what will break before a deploy, no AI needed. No password or token is ever shown to the assistant - the AI setup page explains it all. ### Running the server **Do I get full file access?** Yes - a complete file manager with an in-browser editor, plus SFTP credentials. There is no plan on which the file manager is disabled or read-only. **Can I install mods and plugins?** Yes. Upload them through the file manager or SFTP, or use the game's own workshop support where it has any. Nothing here whitelists which mods you are allowed to run. **Can other people help me run it?** Yes. Invite subusers with granular permissions - console only, files only, no billing - or build a team with a role and grant it access to several servers at once. Permissions are a union, so nobody loses access they were given elsewhere. **What happens if my server runs out of memory?** New servers are configured so the kernel stops the container and it restarts clean, rather than swapping and dragging the whole machine down. If a server restarts repeatedly we warn you on the server page and open a support ticket before anything is suspended - you get told twice before it stops. **Is the CPU limit a hard limit?** Yes, and it cannot be breached: the container is throttled to the share you bought. A server sitting at 100% is slow, not broken, and is never suspended for it. ### Backups and safety **Are backups included?** Every plan carries backup slots - two on the entry tiers, more further up. Take them on demand or on a schedule, and restore with a button rather than a support ticket. **Where are backups stored?** Off the machine they protect. A backup sitting on the same disk as the thing it is insuring covers you deleting your world and covers nothing at all about the disk failing. **What happens to my backups if I cancel?** Deleting a server deletes its backups with it, deliberately and including locked ones. Download anything you want to keep first - the panel lets you. ### Billing **How do I pay?** PayPal, card, your wallet balance, or bank transfer. Every method produces a proper invoice you can download or print, with our details and the bank account on it while it is unpaid. **Can I pay in Georgian lari?** The panel displays every figure in lari at the National Bank of Georgia's published rate, marks converted figures as approximate, and always shows the amount that will actually be charged. Invoices stay in the currency they were raised in, because an invoice is a demand for a specific amount. **What happens when my subscription is due?** An invoice is raised and emailed to you seven days before expiry, and again on the day. Paying it settles that same document rather than raising a second one. **Can I upgrade or downgrade later?** Yes, and it does not rebuild anything. Changing plan changes the limits on the container you already have - your world, files and database stay exactly where they are. **Do you offer refunds?** There is no automatic refund button. If a server cannot do what it was sold to do, open a ticket and we will sort it out - including refunding you. We would rather fix the plan than argue about the policy. ### Support and the honest bits **How do I get help?** Open a ticket from the panel - it reaches every member of staff and every administrator, not a queue aimed at nobody in particular. Attach a screenshot if it helps; attachments are private to your ticket rather than sitting at a guessable public URL. Discord is there for faster, less formal questions. **Where are the servers?** Germany, on hardware we run. That is one location: if you need players in another region on a low ping, say so before buying rather than after. **Is there an uptime guarantee?** We measure node availability every five minutes and report it in the panel rather than printing a number with four decimal places we have not earned. If something is down you will usually hear it from us first. **Who actually runs this?** A small team that writes the panel, the shop and the automation itself. When something is wrong the fix is a code change, which is why the list of things wrong keeps getting shorter. ## Plans and prices Read from the live catalogue at 2026-10-15 22:30 UTC. Prices are in US dollars per month; checkout also takes Georgian lari and bills for 1, 3, 6, 12 or 24 months. Prices change - re-check on https://www.renode.space/database-hosting or in the panel shop at https://panel.renode.space before quoting one. ### MongoDB Managed MongoDB with a root password generated per server, never shared. Page: https://www.renode.space/database-hosting#mongodb | Plan | Price / month | Memory | CPU | NVMe disk | Databases | Backups | Ports | |---|---|---|---|---|---|---|---| | Starter | $7 | 1 GB | 0.5 vCPU | 20 GB | 0 | 2 | 1 | | Standard | $14 | 2 GB | 1 vCPU | 50 GB | 0 | 3 | 1 | | Advanced | $21 | 4 GB | 2 vCPU | 65 GB | 0 | 4 | 1 | | Pro | $30 | 8 GB | 3 vCPU | 80 GB | 0 | 5 | 1 | | Premium | $45 | 14 GB | 4 vCPU | 100 GB | 0 | 6 | 1 | ### Microsoft SQL Server Microsoft SQL Server 2022 Express on Linux, with your own sa login and a public port. Connect from SQL Server Management Studio, Azure Data Studio or any driver as
,. Express limits each database to 10 GB and uses up to 1.4 GB of memory for its buffer pool and up to four cores; the plans are sized to that. For backups you can restore cleanly, run BACKUP DATABASE to a .bak file: a snapshot of a running server's data files is not guaranteed to be consistent. Page: https://www.renode.space/database-hosting#sql-server | Plan | Price / month | Memory | CPU | NVMe disk | Databases | Backups | Ports | |---|---|---|---|---|---|---|---| | Express Starter | $6 | 2 GB | 1 vCPU | 10 GB | 0 | 1 | 1 | | Express Standard | $10 | 3 GB | 2 vCPU | 20 GB | 0 | 2 | 1 | | Express Pro | $15 | 4 GB | 3 vCPU | 40 GB | 0 | 3 | 1 | | Express Max | $22 | 6 GB | 4 vCPU | 80 GB | 0 | 4 | 1 | ### PostgreSQL Managed PostgreSQL with a superuser password generated per server, never shared. Page: https://www.renode.space/database-hosting#postgresql | Plan | Price / month | Memory | CPU | NVMe disk | Databases | Backups | Ports | |---|---|---|---|---|---|---|---| | Starter | $6 | 1 GB | 0.5 vCPU | 20 GB | 0 | 2 | 1 | | Standard | $12 | 2 GB | 1 vCPU | 50 GB | 0 | 3 | 1 | | Advanced | $19 | 4 GB | 2 vCPU | 65 GB | 0 | 4 | 1 | | Pro | $27 | 8 GB | 3 vCPU | 80 GB | 0 | 5 | 1 | | Premium | $40 | 14 GB | 4 vCPU | 100 GB | 0 | 6 | 1 | ### MySQL MySQL 8.4 LTS with your own root login, an application database and user created for you, and a public port. Connect from MySQL Workbench, DBeaver, phpMyAdmin or any driver. Page: https://www.renode.space/database-hosting#mysql | Plan | Price / month | Memory | CPU | NVMe disk | Databases | Backups | Ports | |---|---|---|---|---|---|---|---| | MySQL Starter | $4 | 1 GB | 1 vCPU | 10 GB | 0 | 1 | 1 | | MySQL Standard | $7 | 2 GB | 2 vCPU | 20 GB | 0 | 2 | 1 | | MySQL Pro | $12 | 4 GB | 3 vCPU | 40 GB | 0 | 3 | 1 | | MySQL Max | $20 | 8 GB | 4 vCPU | 80 GB | 0 | 4 | 1 | ### Valkey (Redis-compatible) Valkey, the open-source Redis fork: a password-protected in-memory store for caches, sessions and queues, saved to disk. Works with every Redis client and library. Page: https://www.renode.space/database-hosting#valkey | Plan | Price / month | Memory | CPU | NVMe disk | Databases | Backups | Ports | |---|---|---|---|---|---|---|---| | Valkey 256 MB | $2 | 256 MB | 0.5 vCPU | 2 GB | 0 | 0 | 1 | | Valkey 512 MB | $3 | 512 MB | 0.5 vCPU | 2 GB | 0 | 1 | 1 | | Valkey 1 GB | $4 | 1 GB | 1 vCPU | 4 GB | 0 | 1 | 1 | | Valkey 2 GB | $7 | 2 GB | 1 vCPU | 8 GB | 0 | 1 | 1 | | Valkey 4 GB | $12 | 4 GB | 2 vCPU | 16 GB | 0 | 1 | 1 | ## How it runs ### Connect your app to a database on Renode Source: https://www.renode.space/docs/databases There are two kinds of database on RE:NODE, and they are reached differently. | | Database slot | Database hosting server | | --- | --- | --- | | What it is | A MySQL-compatible database the panel creates for an app or web server | A whole server running PostgreSQL, MySQL, MongoDB, Microsoft SQL Server or Valkey | | Where it is created | The **Databases** tab of your app or web server | Bought as its own server | | Connection details | Host, port, database and username on the Databases tab | The server's address and port, shown in the panel | | Password | On the Databases tab | A Startup tab variable on the database server | #### Database slots App and web plans include database slots. On the server's **Databases** tab, create a database and the panel generates its host, username and password - nothing is shared with anybody else's server. The tab shows the host, port, database name and username; the password is revealed there too (you need the permission to view it if you are a subuser). **Open in phpMyAdmin** signs you straight into that database. #### Database hosting servers A database server runs its engine, started by its image. There is no application code to deploy to it. Connect from your app using the database server's address and port as shown in the panel. Its superuser or root password is generated when the server is created and lives in a variable on that server's **Startup** tab - copy it from there. The engine's major version is fixed by the server's image and cannot be downgraded: a newer major changes the on-disk format, and an older one refuses to open it. #### Keep the credentials out of the code Connection settings belong in the app's environment, never in its repository. Put them in a `.env` uploaded through the Files tab of the **app** server, or in Startup tab variables: .env (uploaded through the Files tab, never committed): ```text DB_HOST=the-host-from-the-panel DB_PORT=3306 DB_NAME=s12_app DB_USER=u12_abc123 DB_PASSWORD=copied-from-the-databases-tab ``` The values above are placeholders - use the ones the panel shows for your database. Commit a `.env.example` with the same keys and no values. #### Connecting from Node.js With `mysql2` for a database slot or a MySQL server: db.js: ```js require("dotenv").config(); const mysql = require("mysql2/promise"); const pool = mysql.createPool({ host: process.env.DB_HOST, port: Number(process.env.DB_PORT), database: process.env.DB_NAME, user: process.env.DB_USER, password: process.env.DB_PASSWORD, }); module.exports = pool; ``` With `pg` for a PostgreSQL server, the same values as one connection string: ```js const { Pool } = require("pg"); // DATABASE_URL=postgres://user:password@host:port/database const pool = new Pool({ connectionString: process.env.DATABASE_URL }); ``` #### Connecting from Python db.py: ```python import os import pymysql from dotenv import load_dotenv load_dotenv() connection = pymysql.connect( host=os.environ["DB_HOST"], port=int(os.environ["DB_PORT"]), database=os.environ["DB_NAME"], user=os.environ["DB_USER"], password=os.environ["DB_PASSWORD"], ) ``` For PostgreSQL use `psycopg` with a `DATABASE_URL`; for MongoDB, `pymongo` with a `mongodb://` URI built from the same environment values. #### Notes per engine - **Microsoft SQL Server** is the Express edition. In SSMS and most drivers the server name is `host,port` - with a comma. Express limits each database to 10 GB. - **Valkey** is the open-source fork of Redis and speaks the Redis protocol, so Redis clients connect to it. It is password-protected. - **MySQL** database servers come with a root password, an application database and an application user, all generated. #### Checklist - No password, host or connection string is written into the code or committed. - The app reads its connection settings from the environment and fails clearly when one is missing. - The password was copied from the Databases tab (slots) or the Startup tab (database servers). - A linked repository on a database server holds migrations or seed files, not something to execute. #### Common errors **Access denied.** The password was mistyped or has a stray quote in `.env`. Copy it again from the panel. **Connection refused or timed out.** The host or port is wrong - use exactly what the panel shows. `localhost` is never right for a database on another server. **`database files are incompatible with server`.** A database server's data directory was opened by a different major version. Majors cannot be switched on an existing server, and there is no downgrade - open a support ticket rather than changing the image again. ## Panel how-to From the panel guides at https://www.renode.space/guides. ### Ordering a server Ordering takes about a minute and happens in three screens: find what you want to run, pick a plan, and pay. Pay by card or PayPal and the server is set up the moment the payment goes through - you can watch it install in its console. - Takes: About a minute - Pay with: Card, PayPal or bank transfer - Billed for: 1, 3, 6, 12 or 24 months - Prices in: US dollars or Georgian lari Open the shop and find what you want to run: 1. Sign in to the panel and open the shop. The tabs along the top are the product lines: VDS, Game Servers, App Hosting, Web Hosting and Databases. 2. Find your game - there is a search box - and press View plans. The card already shows the lowest price and the range of memory on offer. Compare the plans and pick one: 3. Every plan lists its memory, CPU and NVMe storage, roughly how many players it suits, and what is included - updates, backup slots, file and SFTP access. 4. Press Get Started on the one you want. Not sure? Start smaller: you can move to a bigger plan later without losing your world. Choose how long, then pay: 5. Pick a billing period - 1, 3, 6, 12 or 24 months. The total for the whole period is shown before you pay, so there are no surprises. 6. Pay with PayPal, or press Debit or Credit Card - paying by card does not need a PayPal account. The server is created as soon as the payment completes. Paying by bank transfer instead: Prefer to pay from your bank? Choose bank transfer at checkout. The server is set up once the payment arrives, usually the next working day. 1. Switch the payment tab to Bank transfer. The account details for Bank of Georgia and TBC Bank appear, each with a Copy button. 2. Press Order & get invoice, then pay that invoice from your banking app, quoting the reference on it. After you pay: - The server appears under My Servers and installs itself. Open it to watch the install in the console. - Your orders, invoices and subscriptions are under Finances in the side menu. - Stuck at any step? Open Support in the side menu and raise a ticket. ### Backups Take one before you try something. Restore it when the something goes wrong. Lock the ones that matter so they are not rotated away. - Slots on every plan - two on the entry tiers, more further up. - Take one on demand, or on a schedule you set. - Download any backup to your own machine. > Important: Deleting a server deletes its backups, including the locked ones. A lock stops you removing a backup by accident; it is not a claim on the disk after the server is gone. Download what you want to keep before you cancel. ### Databases Create a database from the panel and it comes with a host, a username and a password generated for it. Nothing is shared with anybody else's server. > Open in phpMyAdmin signs you straight into your own database. The credentials never reach your browser and never appear in a URL: the panel mints a token that works once and expires in sixty seconds. ### Subusers, roles and teams Give somebody the console without giving them your files, or the files without your billing. Three separate ideas, kept apart deliberately. - A role holds permissions - what may be done. - A team holds people. - A grant joins a team to a server with a role, so adding a moderator to the team gives them the same access everywhere at once. - Invite by link, by a join code, or by email address - and require approval on the codes if you would rather vet people. - Access can be time-boxed, and expiry stops working immediately rather than overnight. ## Tutorials 66 posts, newest first. The newest 17 follow in full after the list; open the link for the rest. - [MongoDB authentication failed: authSource and other fixes](https://www.renode.space/blog/mongodb-authentication-failed-and-authsource) (2026-10-15): Fix MongoDB 'Authentication failed' errors: authSource, encoded passwords, mongodb+srv, TLS, wrong database, mongosh tests and driver-specific traps. - [MongoDB URI format: mongodb+srv, hosts and options](https://www.renode.space/blog/mongodb-uri-format-and-options) (2026-10-15): MongoDB URI reference: mongodb:// vs mongodb+srv://, multiple hosts and replicaSet, directConnection, key options, and where the string goes in each client. - [MongoDB users and roles: createUser and least privilege](https://www.renode.space/blog/mongodb-users-and-roles) (2026-10-15): How MongoDB users and built-in roles work: db.createUser, read, readWrite, dbAdmin, userAdmin, custom roles, passwords, listing users and the admin db. - [MySQL LTS vs Innovation releases: which version to run](https://www.renode.space/blog/mysql-lts-and-innovation-releases) (2026-10-15): How MySQL's LTS and Innovation tracks work, when 8.0, 8.4 and 9.7 lose support, and how distro packages and Docker tags map to them. - [MySQL 8.4 mysql_native_password: errors and the fix](https://www.renode.space/blog/mysql-native-password-and-caching-sha2) (2026-10-15): Why MySQL 8.4 refuses mysql_native_password logins, what each authentication error means, which drivers need upgrading, and how to move users with ALTER USER. - [PDO prepared statements and error handling in PHP](https://www.renode.space/blog/pdo-prepared-statements-and-error-handling) (2026-10-15): PDO in depth: named and positional placeholders, binding types, IN() lists, error modes, fetch modes, transactions, lastInsertId and the SQLSTATE codes you will meet. - [PostgreSQL sslmode explained: prefer, require, verify-full](https://www.renode.space/blog/postgresql-sslmode-explained) (2026-10-15): What each PostgreSQL sslmode checks, why the default prefer is weak, how to set PGSSLMODE and sslrootcert, the server side, and the SSL errors you will hit. - [Upgrade MySQL 8.0 to 8.4: checker, backup, upgrade, rollback](https://www.renode.space/blog/upgrade-mysql-8-0-to-8-4) (2026-10-15): Upgrade MySQL 8.0 to 8.4 safely: run util.checkForServerUpgrade, back up first, choose in-place or dump and load, test the app and know what rollback can do. - [Connect to a remote SQL Server with SSMS](https://www.renode.space/blog/connect-to-sql-server-with-ssms) (2026-10-08): Connect SQL Server Management Studio to a hosted server: host,port with a comma, SQL authentication, encryption and Trust server certificate, and every error. - [Job queues with Valkey: BullMQ, Celery, RQ and Laravel](https://www.renode.space/blog/job-queues-with-valkey) (2026-10-08): Run background jobs on Valkey with BullMQ, Celery, RQ or Laravel queues: setup, the settings that lose jobs, retries, visibility timeouts and sizing. - [Migrating a database to SQL Server hosting](https://www.renode.space/blog/migrating-a-database-to-sql-server-hosting) (2026-10-08): Move a database to a hosted SQL Server: backup and restore, BACPAC, generated scripts, version and edition checks, logins, compatibility level and cutover. - [Migrating MySQL to a new host: dump, import, cut over](https://www.renode.space/blog/migrating-mysql-to-a-new-host) (2026-10-08): Move a MySQL database to a new server: inventory, version checks, dump and import, users and definers, verifying the data and cutting over with little downtime. - [MySQL 8.4 LTS: what changed from 8.0 and what breaks](https://www.renode.space/blog/mysql-8-4-lts-what-changed) (2026-10-08): What MySQL 8.4 LTS changes for an app moving from 8.0: the LTS model, mysql_native_password off by default, new InnoDB defaults and removed options. - [MySQL connection limits and pooling: too many connections](https://www.renode.space/blog/mysql-connection-limits-and-pooling) (2026-10-08): Why MySQL says 'Too many connections', how max_connections and wait_timeout really work, and how to size connection pools for PHP, Node, Python, Java and .NET. - [MySQL GUI clients: Workbench vs DBeaver vs HeidiSQL](https://www.renode.space/blog/mysql-gui-clients-workbench-dbeaver-heidisql) (2026-10-08): MySQL Workbench, DBeaver and HeidiSQL compared for a remote MySQL 8.4 server: how to connect each one, TLS and auth settings, and the errors that stop them. - [MySQL indexes and EXPLAIN: reading plans, fixing queries](https://www.renode.space/blog/mysql-indexes-and-explain) (2026-10-08): How MySQL InnoDB indexes work, how to order a composite index, and how to read EXPLAIN and EXPLAIN ANALYZE to turn a slow query into a fast one. - [MySQL InnoDB tuning for 1-8 GB servers](https://www.renode.space/blog/mysql-innodb-tuning-for-small-servers) (2026-10-08): The MySQL 8.4 settings that matter on a small server: innodb_buffer_pool_size, innodb_redo_log_capacity, max_connections, temp tables and binary logs. - [MySQL JSON columns: functions, indexes and generated columns](https://www.renode.space/blog/mysql-json-and-generated-columns) (2026-10-08): How the MySQL JSON type stores data, the functions and operators worth knowing, and how to index JSON fields with generated columns and multi-valued indexes. - [MySQL remote connections: host, port, TLS and clients](https://www.renode.space/blog/mysql-remote-connections-getting-started) (2026-10-08): Connect to a remote MySQL 8.4 server from the mysql client, your app and GUI tools: host and port, ssl-mode, caching_sha2_password and the usual errors. - [MySQL slow query log: enable it, read it, fix what it finds](https://www.renode.space/blog/mysql-slow-query-log) (2026-10-08): Turn on the MySQL slow query log, choose long_query_time, summarise it with mysqldumpslow or performance_schema and fix the queries it catches. - [MySQL transactions, locking and deadlocks explained](https://www.renode.space/blog/mysql-transactions-locking-and-deadlocks) (2026-10-08): How InnoDB isolation levels, row and gap locks really behave, why deadlocks and lock wait timeouts happen, how to find the blocker, and how to retry safely. - [MySQL users and privileges: CREATE USER, GRANT and roles](https://www.renode.space/blog/mysql-users-and-privileges) (2026-10-08): How MySQL 8.4 accounts work: user and host patterns, CREATE USER, GRANT and REVOKE, roles, password policies and a least-privilege setup for an app. - [MySQL utf8mb4 and collations: utf8 vs utf8mb4, converting](https://www.renode.space/blog/mysql-utf8mb4-and-collations) (2026-10-08): Why MySQL utf8 cannot store emoji, which utf8mb4 collation to choose, how connection charsets work and how to convert a database without mojibake. - [MySQL vs PostgreSQL: an honest comparison for app developers](https://www.renode.space/blog/mysql-vs-postgresql) (2026-10-08): MySQL or PostgreSQL for your app: SQL features, types, DDL, concurrency, connections, JSON, ecosystem and operations compared, with a clear way to choose. - [mysqldump backup and restore: flags, large dumps, errors](https://www.renode.space/blog/mysqldump-backup-and-restore) (2026-10-08): Back up and restore MySQL with mysqldump: --single-transaction, routines and events, compressing large dumps, fast imports and the errors that stop them. - [PHP PDO and MySQL: connections, prepared statements, errors](https://www.renode.space/blog/php-pdo-and-mysql) (2026-10-08): Connect PHP to MySQL with PDO the right way: the DSN and charset, the options worth setting, prepared statements, transactions and the errors you will meet. - [Sessions in Valkey: Express, Django, Laravel, PHP and .NET](https://www.renode.space/blog/sessions-in-valkey) (2026-10-08): Move web sessions into Valkey: working config for Express, Django, Laravel, plain PHP and ASP.NET Core, plus TTLs, locking, eviction and what breaks. - [SQL Server backup and restore: .bak files without Agent](https://www.renode.space/blog/sql-server-backup-and-restore) (2026-10-08): Native SQL Server backups on Express: BACKUP DATABASE, verifying a .bak, restoring with MOVE and REPLACE, and scheduling without SQL Server Agent. - [SQL Server collations, Unicode and nvarchar vs varchar](https://www.renode.space/blog/sql-server-collations-and-unicode) (2026-10-08): nvarchar vs varchar, the N prefix, UTF-8 collations, what CI_AS and SC mean, collation conflicts with tempdb, and choosing a collation for a new database. - [SQL Server connection strings for every driver](https://www.renode.space/blog/sql-server-connection-strings) (2026-10-08): Working SQL Server connection strings for .NET, JDBC, ODBC, Node and Python, with the Encrypt and TrustServerCertificate defaults that break first connections. - [SQL Server Express hosting: limits and when it is enough](https://www.renode.space/blog/sql-server-express-hosting-guide) (2026-10-08): What SQL Server 2022 Express can and cannot do: the 10 GB per database cap, memory and CPU limits, missing features like Agent, and when to move up. - [SQL Server indexes and execution plans, explained](https://www.renode.space/blog/sql-server-indexes-and-execution-plans) (2026-10-08): Clustered vs nonclustered indexes, included columns, seeks, scans and key lookups, and how to read a SQL Server execution plan to fix a slow query. - [SQL Server logins, users and roles explained](https://www.renode.space/blog/sql-server-logins-users-and-roles) (2026-10-08): How SQL Server security fits together: logins vs users, the sa account, fixed server and database roles, and a least-privilege login for your application. - [SQL Server on Linux: what runs, what is missing](https://www.renode.space/blog/sql-server-on-linux-explained) (2026-10-08): How SQL Server runs on Linux: SQLPAL, mssql-conf, file paths, case sensitivity, the features left out, and what changes for an app or a DBA moving over. - [SQL Server Query Store: finding the slow query](https://www.renode.space/blog/sql-server-query-store-and-slow-queries) (2026-10-08): Find slow SQL Server queries with Query Store and DMVs: turning it on, the reports, top queries by CPU and reads, wait stats, plan regressions and plan forcing. - [SQL Server recovery models and transaction log growth](https://www.renode.space/blog/sql-server-recovery-models-and-log-growth) (2026-10-08): Why the SQL Server transaction log keeps growing, how simple and full recovery differ, log_reuse_wait_desc, log backups, shrinking once and sizing the log. - [sqlcmd and bcp: SQL Server from the command line](https://www.renode.space/blog/sql-server-sqlcmd-and-bcp) (2026-10-08): Run T-SQL scripts with sqlcmd, script deployments with variables and exit codes, and bulk import and export tables with bcp, including the flags that matter. - [T-SQL basics for app developers from other databases](https://www.renode.space/blog/t-sql-basics-for-app-developers) (2026-10-08): The T-SQL that differs from MySQL and PostgreSQL: TOP and OFFSET FETCH, IDENTITY and OUTPUT, MERGE and upserts, transactions, types, dates and NULL handling. - [Valkey caching patterns: cache-aside, TTLs and stampedes](https://www.renode.space/blog/valkey-caching-patterns) (2026-10-08): Cache with Valkey without serving stale data or melting your database: cache-aside, choosing TTLs, invalidation on write, stampede locks and hit ratios. - [Valkey data types explained: which one to use and when](https://www.renode.space/blog/valkey-data-types-explained) (2026-10-08): Strings, hashes, lists, sets, sorted sets, streams, bitmaps and HyperLogLog in Valkey: commands, memory cost, encodings and the right type for each job. - [Valkey getting started: valkey-cli, AUTH and first commands](https://www.renode.space/blog/valkey-getting-started) (2026-10-08): Connect to a Valkey server with valkey-cli or redis-cli, authenticate safely, build a connection URL for your app and learn the commands you use daily. - [Valkey memory: maxmemory, eviction policies and big keys](https://www.renode.space/blog/valkey-memory-and-eviction-policies) (2026-10-08): Size and tune Valkey memory: what maxmemory counts, choosing an eviction policy, finding big keys, compact encodings and reading the fragmentation ratio. - [WordPress object cache with Valkey: setup and limits](https://www.renode.space/blog/valkey-object-cache-for-wordpress) (2026-10-08): Add a persistent object cache to WordPress with Valkey: what it speeds up, what it does not, plugin setup, wp-config constants, sizing and troubleshooting. - [Valkey persistence: AOF vs RDB snapshots explained](https://www.renode.space/blog/valkey-persistence-aof-and-rdb) (2026-10-08): How Valkey saves data to disk: RDB snapshots, the append-only file, appendfsync choices, what each kind of crash loses, and how to recover a damaged file. - [Valkey pub/sub vs streams: messaging and consumer groups](https://www.renode.space/blog/valkey-pub-sub-and-streams) (2026-10-08): When to use Valkey pub/sub and when to use streams: delivery guarantees, consumer groups, acknowledgements, pending entries, trimming and failure cases. - [Rate limiting with Valkey: windows, buckets and Lua](https://www.renode.space/blog/valkey-rate-limiting) (2026-10-08): Build rate limits on Valkey: fixed and sliding windows, token buckets, atomic Lua scripts, what to key on, and the libraries that already do it right. - [Valkey security: passwords, ACL users and exposure](https://www.renode.space/blog/valkey-security-and-acl) (2026-10-08): Secure a Valkey server: requirepass, ACL users with key and command limits, dangerous commands, protected mode, TLS, and why an open port gets you mined. - [Valkey vs Redis: the licence change, the fork and compatibility](https://www.renode.space/blog/valkey-vs-redis-explained) (2026-10-08): Why Valkey exists, what changed in the Redis licence, how compatible the two are today, which clients work with both and how to move data across. - [Which database should I use? Postgres, MySQL, SQL Server](https://www.renode.space/blog/which-database-should-i-use) (2026-10-08): PostgreSQL, MySQL, SQL Server, MongoDB and Valkey compared by the job they do best, with the limits, costs and switching pain that should decide it for you. - [Database security checklist: roles, access](https://www.renode.space/blog/database-security-checklist) (2026-09-21): The database checks that matter: what is reachable, who can log in, what they may do, TLS, injection, limits, and a backup you have actually restored. - [EXPLAIN ANALYZE: reading Postgres query plans](https://www.renode.space/blog/explain-analyze-slow-queries) (2026-09-21): How to read a PostgreSQL query plan: EXPLAIN ANALYZE options, node types, bad row estimates, and the indexes that turn a seq scan into a 0.1 ms lookup. - [MongoDB connection strings: URI and drivers](https://www.renode.space/blog/mongodb-connection-strings-getting-started) (2026-09-21): How a MongoDB URI is put together: hosts, authSource, percent-encoded passwords, mongodb+srv, driver pool options, mongosh, Compass, and the auth errors. - [MongoDB schema design and indexes](https://www.renode.space/blog/mongodb-indexes-and-schema-design) (2026-09-21): Embedding versus referencing, the 16 MB limit, compound indexes and the ESR rule, reading explain(), the profiler, and how much RAM your working set needs. - [mongodump and mongorestore: real backups](https://www.renode.space/blog/mongodump-and-mongorestore-guide) (2026-09-21): How to back up MongoDB properly: mongodump options, archives and gzip, why a dump is not point-in-time, restoring one collection, and scheduling that works. - [pg_dump and pg_restore: practical backups](https://www.renode.space/blog/pg-dump-and-pg-restore-guide) (2026-09-21): Postgres backups that restore: pg_dump formats, pg_dumpall globals, parallel pg_restore, version rules, what a dump leaves out, and the errors you will hit. - [phpMyAdmin import/export: limits and errors](https://www.renode.space/blog/phpmyadmin-import-export-guide) (2026-09-21): How to export and import a database in phpMyAdmin: the options that matter, upload limits, utf8mb4, and the error numbers you will hit. - [Postgres vacuum, bloat and autovacuum](https://www.renode.space/blog/postgres-vacuum-and-bloat) (2026-09-21): Why PostgreSQL leaves dead rows behind, what VACUUM reclaims, how to tell when autovacuum has fallen behind, and how to fix bloat before wraparound arrives. - [PostgreSQL indexes: types, order and cost](https://www.renode.space/blog/postgresql-indexes-explained) (2026-09-21): How B-tree, GIN, GiST and BRIN indexes work, why column order decides whether yours is used, when an index makes things slower, and how to add one safely. - [PostgreSQL remote connections: psql and SSL](https://www.renode.space/blog/postgresql-remote-connections-getting-started) (2026-09-21): Connect to a PostgreSQL server from somewhere else: connection strings, psql, sslmode, pg_hba rules, GUI clients, driver examples and the errors you will hit. - [PostgreSQL roles and least-privilege access](https://www.renode.space/blog/postgresql-roles-and-permissions) (2026-09-21): Roles, GRANT, schemas and default privileges explained, with a three-role setup for an application that keeps working after the next migration. - [PostgreSQL tuning for 1-8 GB servers](https://www.renode.space/blog/postgresql-tuning-for-small-servers) (2026-09-21): Which PostgreSQL settings actually matter on a small database server: shared_buffers, work_mem, max_connections, checkpoints and autovacuum, with numbers. - [PostgreSQL or MongoDB: pick by data shape](https://www.renode.space/blog/postgres-or-mongodb) (2026-04-28): How to choose by data shape rather than fashion: the data model, transactions, indexes, JSONB, aggregation, and what each costs on a small server. - [Database backups and tested restores](https://www.renode.space/blog/database-backups-and-restores) (2026-04-21): How to take a consistent dump of PostgreSQL or MongoDB, where to keep it, how to restore one table or the whole thing, and how to prove it worked. - [Redis: when you need it and how to run it](https://www.renode.space/blog/redis-when-you-need-it) (2025-10-03): Sessions, caches, queues and rate limits are the real reasons. What your database already does, and how to install, secure and size Redis on a VDS. - [Connection pools: sizing and limits](https://www.renode.space/blog/connection-pools-and-limits) (2025-09-26): Too many clients is a configuration error, not a capacity one. How a pool works, the arithmetic nobody does, sane sizes, timeouts, PgBouncer and leaks. - [Schema migrations without downtime](https://www.renode.space/blog/migrations-without-downtime) (2025-09-19): Adding is safe, renaming is what breaks things. The expand and contract pattern, Postgres lock behaviour, batched backfills and the order that works. --- ### MongoDB authentication failed: authSource and other fixes Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/mongodb-authentication-failed-and-authsource `Authentication failed` from MongoDB, error code 18, means one thing: the server received a username and password and could not match them to a user. It does not mean the server is down, the port is closed or TLS is broken - those fail earlier and look different. When the credentials are definitely right, the cause is nearly always one of four things: the user lives in a different database from the one the driver asked (`authSource`), the password the driver sent is not the password you typed (encoding, quoting, a stray newline), the connection string is not the one you think is being used, or a mechanism or `+srv` default has changed what gets sent. The quickest route to the answer is to stop guessing from the client side and ask the server, then reproduce the connection by hand in `mongosh`. This post goes through that diagnosis in order, then the traps specific to each driver. For how a URI is put together in the first place - the parts, the options, pool sizes - see [MongoDB connection strings](https://www.renode.space/blog/mongodb-connection-strings-getting-started); this one assumes you have a string and it is not working. #### First, confirm it really is an authentication error A surprising amount of time is lost fixing passwords for errors that were never about passwords. MongoDB fails in layers, and each layer has its own message. Work out which layer stopped you before changing anything. | Message | Layer | Look at | | --- | --- | --- | | `Authentication failed` / code 18 | Credentials | User, database, password | | `bad auth : Authentication failed` | Credentials (Atlas) | Database user, not your Atlas login | | `ServerSelectionTimeoutError` | Network or TLS | Host, port, firewall, `tls` | | `ECONNREFUSED` | Network | Bind address, port, service running | | `querySrv ENOTFOUND` | DNS | `+srv` on a host without SRV records | | `requires authentication` (code 13) | Credentials missing | URI has no user, or env not loaded | | `not authorized on shop to execute` | Roles | User exists but lacks the role | The last two are worth separating from the first. `command find requires authentication` means you connected anonymously - the credentials never made it into the request, typically because an environment variable was empty and the code fell back to `mongodb://localhost:27017`. `not authorized` means authentication worked and the user simply has no role on that database. Neither is fixed by changing the password, and both are fixed by looking at what the user is allowed to do, or at which URI your process actually loaded. A good habit before going further: print the URI your application is really using, with the password masked. Something like `uri.replace(/:([^@/]+)@/, ":****@")` in JavaScript gives you the host, port, database and options without leaking the secret. A good share of "Authentication failed with the right password" turns out to be a staging URI in a production container, or a default in the code that was never overridden. #### Ask the server which part was wrong The client gets a deliberately vague message, because telling an attacker "that user does not exist" would help them. The server log is not vague. If you have access to the `mongod` log, find the line for the failed attempt; on MongoDB 4.4 and later it is structured JSON with the message `Authentication failed` and an `attr` object that names the mechanism, the user, the database it looked in, the client address, and the actual reason. ```json {"msg":"Authentication failed","attr":{"mechanism":"SCRAM-SHA-256", "principalName":"shopapp","authenticationDatabase":"shop", "remote":"203.0.113.40:51522", "error":"UserNotFound: Could not find user \"shopapp\" for db \"shop\""}} ``` That one line answers most of the question. Read three fields: - `authenticationDatabase` is the database the driver asked. If it says `shop` and your user was created in `admin`, the fix is `authSource=admin`, and the password was never the problem. - `error` starting with `UserNotFound` means no user by that name exists in that database. Either the name is misspelt, it lives in another database, or you are talking to a different server from the one you created it on. - `error` mentioning a SCRAM failure such as `storedKey mismatch` means the user was found and the password did not match. Now, and only now, is it worth looking at the password itself. The exact wording and layout of these lines varies between versions, and older servers wrote plain text rather than JSON, but the same three facts are in there. If you cannot read the server log - a managed service, or a server you do not run - `mongosh` gets you to the same answers, as below. #### authSource and the user that lives somewhere else Every MongoDB user belongs to one database. The connection string's `authSource` says which one to look in, and when it is missing the driver uses the database in the URI path, falling back to `admin` only when the path is empty. That default is the single largest cause of code 18. The classic case: you were given an administrative user that lives in `admin`, and you write a URI pointing at your application database. ```text mongodb://root:pw@db.example.net:27017/shop ``` The driver looks for `root` in `shop`, finds nobody, and reports failure. Add `?authSource=admin` and the same credentials work. The reverse mistake is just as common: a user created with `use shop` before `db.createUser(...)`, then a URI with `authSource=admin` copied from a tutorial. That user does not exist in `admin`, so the answer is `authSource=shop` - or recreate the user in `admin` with a role scoped to `shop` and stop thinking about it. To find out where a user really lives, connect with any account that can view users and ask the whole server: ```js db.adminCommand({ usersInfo: { forAllDBs: true } }).users .map(u => `${u.user} @ ${u.db}`) ``` Each line shows a username and the database it belongs to. Two users with the same name in two databases are entirely separate accounts with separate passwords, which explains the occasional "it works from one app and not the other" - the two apps are authenticating as different users that happen to share a name. For a single database you can also run `db.getSiblingDB("shop").getUsers()`. An empty array means there is nobody in `shop`, which settles the `authSource` question at once. > [!TIP] Pick one convention for where application users live - all in `admin` with scoped roles, or each in its own database - and write it next to the connection string. The `authSource` bug only comes back when half the users follow one rule and half the other. ##### The wrong server entirely `UserNotFound` for a user you know you created has a duller explanation more often than people admit: the client is not talking to the server you created it on. A local `mongod` on your laptop listening on `27017` will happily answer a URI meant for a remote host if an SSH tunnel is forwarding the same local port, or if a `.env` file points at `localhost`. Check with `db.hostInfo().system.hostname` once connected, or look at the `remote` address in the server log. If you use a tunnel, forward to a different local port such as `27018` so the two can never be confused - [SSH tunnels and port forwarding](https://www.renode.space/blog/ssh-tunnels-and-port-forwarding) covers the syntax. #### The password that is not the password you typed When the server log says the user was found and the password did not match, assume the bytes that arrived differ from the ones you intended. These are the ways that happens, roughly in order of frequency. ##### Percent-encoding, missing or doubled Any of `: / ? # [ ] @ %` in the password must be percent-encoded inside a URI. Leave one unencoded and the driver either rejects the URI or reads part of the password as the host. The opposite failure is quieter: encoding twice. A password of `p@ss` correctly becomes `p%40ss` in the URI; if that encoded string is then passed through `encodeURIComponent` again, it becomes `p%2540ss`, and the server receives the literal text `p%40ss`. Encode exactly once, at the point where the URI is built, and never store an already-encoded password in a variable that something else will encode. The same thing happens in reverse when the password is passed as a separate driver option instead of in the URI. Separate options are not URI-decoded, so a password copied from an encoded URI and pasted into a `password` field arrives with `%40` in it. Whichever form you use, the separate field takes the raw password and the URI takes the encoded one. ##### Shell quoting Put a URI on a command line without quotes and the shell gets to it first. `&` between options backgrounds the command and drops everything after it, so `authSource=admin` silently vanishes. Inside double quotes, `$` starts a variable: a password of `pa$word` becomes `pa` plus whatever `$word` expands to, usually nothing. In an interactive bash session `!` can trigger history expansion too. ```bash $ mongosh 'mongodb://shopapp:pa$word@db.example.net:27017/shop?authSource=admin' ``` Single quotes in bash and zsh pass everything through literally. PowerShell expands `$` inside double quotes as well and uses the backtick as its escape character, so single quotes are the safe choice there too. Better still, leave the password out and let `mongosh` prompt for it, which removes the shell from the problem entirely. ##### Environment files, secrets and trailing newlines A secret created with `echo "pw" > file` contains a newline after the password; `printf '%s' "pw"` does not. Read that file into an environment variable or a Kubernetes secret and the driver sends `pw\n`, which does not match. Some `.env` loaders keep surrounding quotes as part of the value, some strip them, and the behaviour differs between libraries and versions. Docker Compose treats `$` in a compose file as variable interpolation, so a literal dollar sign has to be written `$$` there. The quick check is to print the length of the password your process received and compare it with the length you expected. An extra one or two characters is a newline or a pair of quotes. ##### Docker's root user that was never recreated The official `mongo` image creates its root user from `MONGO_INITDB_ROOT_USERNAME` and `MONGO_INITDB_ROOT_PASSWORD`, in the `admin` database, and only when the data directory is empty on first start. Change those variables later and nothing happens: the existing volume still holds the old user with the old password. If you rotated the password in the compose file and auth now fails, that is why. Either change the password inside the database with `changeUserPassword`, or deliberately throw the volume away and start again - which deletes the data. Note also that `MONGO_INITDB_DATABASE` does not create a user in that database; the root user still lives in `admin`, so `authSource=admin` applies. #### mongodb vs mongodb+srv, and what it changes for auth A `mongodb+srv://` string makes the driver look up the server list in DNS. It also reads a TXT record on the same name, and that TXT record is allowed to set exactly three options: `authSource`, `replicaSet` and `loadBalanced`. This is how managed services such as MongoDB Atlas make `authSource=admin` appear without you writing it. If you take an Atlas-style string apart and rewrite it as a plain `mongodb://` list of hosts, you lose those defaults, and the result fails authentication unless you add `authSource=admin` back yourself. Options in the URI override the TXT record, so an `authSource` you add by hand always wins. The other `+srv` default is TLS, which is on. Converting to `mongodb://` without adding `tls=true` gives you a connection the server refuses, and that surfaces as a timeout rather than an auth error. For Atlas specifically, a few points account for most "bad auth" reports: - The user in the URI must be a database user from the project's Database Access page, not the email and password you use to sign in to the Atlas website. They are unrelated accounts. - A newly created or edited database user can take a short while to propagate to the cluster; an immediate failure after a change is worth one retry before you start debugging. - An address missing from the network access list does not produce `bad auth`. It produces a server selection timeout, because the connection is dropped before authentication starts. When people search for "mongodb get connection string", the answer depends on where the database is. Atlas gives you one from the cluster's Connect dialogue, in `+srv` form. A self-hosted or rented standalone server gives you a host, a port, a user and a password, and you build `mongodb://user:pass@host:port/db?authSource=...` yourself. A standalone server has no SRV records, so `+srv` does not apply. #### TLS options: when the failure is not authentication TLS problems happen before the server ever sees a password, so they never produce code 18. They produce `ServerSelectionTimeoutError` or a handshake error mentioning certificates. It is still worth knowing the options, because changing TLS settings while chasing an auth bug often adds a second failure on top of the first. | Option | Default | Use | | --- | --- | --- | | `tls` | `false` (`true` with `+srv`) | Encrypt the connection | | `tlsCAFile` | system store | CA that signed a private certificate | | `tlsCertificateKeyFile` | none | Client certificate, for X.509 | | `tlsAllowInvalidHostnames` | `false` | Skip name check - testing only | | `tlsAllowInvalidCertificates` | `false` | Skip all checks - testing only | `ssl=true` is an older alias for `tls=true` and is still accepted. If a server uses a certificate from a private CA, point `tlsCAFile` at that CA rather than reaching for `tlsAllowInvalidCertificates`, which accepts any certificate from anyone and defeats the point of encrypting. Setting `tls=true` against a server that is not configured for TLS fails at the handshake, whatever the credentials. One authentication method does depend on TLS: X.509 client certificates. With `authMechanism=MONGODB-X509` the certificate is the credential, there is no password, and `authSource` must be `$external`, the virtual database that holds externally authenticated users. LDAP (`PLAIN`), Kerberos (`GSSAPI`) and AWS IAM (`MONGODB-AWS`) also authenticate against `$external`. If you copied a URI that says `authSource=$external` and you are using a normal username and password, that is your bug. ##### SCRAM mechanisms Ordinary password users authenticate with SCRAM. Since MongoDB 4.0, new users get both `SCRAM-SHA-1` and `SCRAM-SHA-256` credentials by default, and drivers negotiate the stronger one. Problems appear when a URI forces `authMechanism=SCRAM-SHA-1` and the user was created with only SHA-256 (or the reverse), or when a driver too old to know SHA-256 meets a user that only has SHA-256. The fix is nearly always to delete `authMechanism` from the URI and let the driver negotiate. `usersInfo` shows a user's `mechanisms` array if you need to check. #### Testing the connection with mongosh `mongosh` is the reference client. If it connects with a set of values, those values are correct, and anything that fails with the same values is a problem in the application or its environment. Test in this order, changing one thing at a time. 1. Connect without credentials, to prove the network path: `mongosh "mongodb://db.example.net:27017" --eval 'db.runCommand({ ping: 1 })'`. A `{ ok: 1 }` means host, port and TLS are fine; a timeout means stop looking at passwords. 2. Connect with separate flags and a prompted password, so neither encoding nor shell quoting can interfere. 3. Once that works, connect with your full URI in single quotes. If step 2 succeeds and step 3 fails, the URI is the problem - encoding, `authSource`, or an option. 4. Finally, run the application with the same URI. If it fails here, the application is receiving something different. ```bash $ mongosh --host db.example.net --port 27017 \ -u shopapp -p --authenticationDatabase admin shop ``` `-p` with no value prompts for the password. `--authenticationDatabase` is the command-line name for `authSource`, which is a common source of confusion when switching between the two forms. You can also test credentials from inside an existing session, which is handy when you are already connected as an administrator and want to try a different user: ```js db.getSiblingDB("admin").auth("shopapp", passwordPrompt()) db.runCommand({ connectionStatus: 1 }).authInfo ``` `auth()` returns `{ ok: 1 }` on success and throws an `Authentication failed` error otherwise; the database you call it on is the `authSource` being tested. `connectionStatus` then shows which users and roles the session holds. Compass behaves the same way: in its advanced options the Authentication tab has an "Authentication Database" field, which is `authSource` under another name. #### Driver-specific gotchas Every driver accepts the same URI, but each has its own way of taking credentials as separate options, and each has a quirk that produces code 18. ##### Node.js driver and Mongoose The Node driver takes credentials either in the URI or as `auth: { username, password }`, with `authSource` as a top-level option. Separate options are not decoded, so pass the raw password there. ```js const client = new MongoClient("mongodb://db.example.net:27017/shop", { auth: { username: "shopapp", password: process.env.MONGO_PASSWORD }, authSource: "admin", }); ``` Mongoose adds `dbName`, `user` and `pass` options. `dbName` changes which database your models use, but it is easy to end up with a URI path and a `dbName` that disagree about which database is meant. Whenever you use `dbName`, set `authSource` explicitly rather than relying on any default, and the question never arises. ##### PyMongo PyMongo accepts `username`, `password` and `authSource` keyword arguments, which avoid encoding altogether. If you do build a URI, `urllib.parse.quote_plus` is the function the PyMongo documentation uses. PyMongo reports the failure as `OperationFailure` with `code 18` and `codeName: AuthenticationFailed` in the full error; catching `ServerSelectionTimeoutError` instead and logging "auth failed" is a common way to hide a network problem behind the wrong message. ```python client = MongoClient( "db.example.net", 27017, username="shopapp", password=os.environ["MONGO_PASSWORD"], authSource="admin", ) ``` ##### Go, Java and PHP The Go driver applies a URI and then lets you override credentials with `options.Credential`, whose `AuthSource` field defaults the same way the URI does. If you call `SetAuth` with a credential that leaves `AuthSource` empty, check what the URI path says. In Java, `MongoCredential.createScramSha256Credential(user, source, password)` takes the auth database as its second argument - pass the application database there by habit and you have reproduced the classic bug in a new language. The PHP library takes URI options as the second argument to `new MongoDB\Client($uri, $uriOptions)`, where `username`, `password` and `authSource` can be set without encoding. ##### Frameworks that build the URI for you Spring Boot, Laravel packages and similar wrappers often assemble the URI from separate settings such as host, database, username and password. Read how yours does it. Several of them put the configured database in the URI path, which makes it the default `authSource`, and expose a separate setting for the authentication database that is easy to miss. When a framework is involved, log the final URI (masked) once at start-up; it is usually obvious from there. #### Resetting a password or recovering access If the user exists and you simply do not know its password, an administrator can set a new one without touching anything else: ```js db.getSiblingDB("admin").changeUserPassword("shopapp", passwordPrompt()) ``` Run it on the database the user belongs to - `admin` here, `shop` if that is where the user lives. Existing connections stay authenticated, so restart the application afterwards to prove the new password works. On a server you run yourself and where you have lost every administrator password, the documented route is to restart `mongod` with access control disabled (remove or comment out `security.authorization: enabled` in `mongod.conf`), bound only to `127.0.0.1`, create or reset an administrator, and then turn authorization back on. Do it with the database unreachable from outside for the whole window, because for those minutes anyone who can connect is effectively root. > [!WARNING] Never "fix" authentication by turning authorization off and leaving it off. An unauthenticated MongoDB on a public address is found by scanners within hours and is routinely emptied and replaced by a ransom note. The [database security checklist](https://www.renode.space/blog/database-security-checklist) covers what to lock down instead. When there are no users at all, MongoDB allows a connection from localhost to create the first one - the localhost exception. Once any user exists, the exception closes. That is why a fresh self-hosted install sometimes accepts unauthenticated commands from the server itself and refuses them from anywhere else. On RE:NODE [database hosting](https://www.renode.space/database-hosting), a MongoDB server is created with credentials generated for that server and is reached directly on the host and port shown on the plan - there is no proxy in front of it, so the TLS and `+srv` traps above do not come into play unless you set them up. If authentication fails, the usual suspects are the ones in this post: the database the user belongs to, and how the password got into your URI. The [databases section of the guides](https://www.renode.space/guides#databases) covers how the panel handles database credentials. #### FAQ ##### Why does MongoDB say authentication failed when my password is correct? Because the server looked for the user in a different database from the one where it was created. The driver searches the database named by `authSource`, and when that is missing it uses the database in the URI path. A user created in `admin` and a URI ending in `/shop` fails every time until you add `?authSource=admin`. ##### What does 'bad auth : Authentication failed' mean on Atlas? It is Atlas's wording for the same error code. Check that you are using a database user from the Database Access page rather than your Atlas sign-in, that the password in the URI is percent-encoded, and that you have not rewritten the `+srv` string as a plain host list without adding `authSource=admin` back. ##### How do I find which database a MongoDB user belongs to? Connect as an administrator and run `db.adminCommand({ usersInfo: { forAllDBs: true } })`. Each entry has a `user` and a `db` field; the `db` value is what goes in `authSource`. Without administrative access, the server log line for a failed attempt shows which database the client asked. ##### Do I need to URL-encode the password if I pass it separately? No, and you must not. Encoding applies only to the URI. Driver options such as `password`, and the `-p` prompt in `mongosh`, take the raw password; an encoded value there is sent with the `%` sequences intact and fails. ##### Is authSource the same as the database I want to use? Only when the user was created in that database. The database in the URI path is where your queries go by default; `authSource` is where the server looks up your account. They often differ, which is the whole reason the option exists. ##### Can I use mongodb+srv with a single server I rent? Only if someone has created SRV records for it, and a single rented server normally has none. Use `mongodb://` with the host and port. If you want a readable name, an A record pointing at the address is enough. RE:NODE MongoDB servers are created with credentials generated for each server and reached on the host and port shown on the plan, with nothing in between your driver and the database. Backups are included on every plan, from $7 a month. [See MongoDB plans](https://www.renode.space/database-hosting#mongodb) --- ### MongoDB URI format: mongodb+srv, hosts and options Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/mongodb-uri-format-and-options A MongoDB URI is `mongodb://[user:password@]host1[:port1][,host2[:port2],...][/database][?options]`. With nothing filled in, every official tool assumes `mongodb://localhost:27017`. The `mongodb+srv://` form takes exactly one hostname and no port, asks DNS for the real host list, and switches TLS on by default. Several hosts separated by commas are a seed list for a replica set or a set of `mongos` routers, not a list of fallback servers, and the driver will connect to whichever member it decides is right - which is why `directConnection` exists. That is the whole format. The rest of this reference covers what each piece does in practice: the defaults, the DNS records behind `+srv`, how the driver treats more than one host, the options that change behaviour, and where the string goes in PyMongo, the Node driver, `mongosh`, .NET configuration files and Compass. For a gentler walk through the URI parts and percent-encoding passwords, start with [MongoDB connection strings](https://www.renode.space/blog/mongodb-connection-strings-getting-started). If you have a URI and the server says `Authentication failed`, go to [the authSource troubleshooting guide](https://www.renode.space/blog/mongodb-authentication-failed-and-authsource) instead; this post does not repeat either. #### The format, piece by piece ```text mongodb:// scheme shopapp:pw@ credentials (optional) db1.example.net:27017,db2.example.net:27017 host list /shop default database (optional) ?replicaSet=rs0&appName=api options (optional) ``` Joined together with nothing in between, that is `mongodb://shopapp:pw@db1.example.net:27017,db2.example.net:27017/shop?replicaSet=rs0&appName=api`. The rules that are easy to forget: - The port is optional and defaults to `27017` for every host that does not give one. `mongodb://db.example.net` and `mongodb://db.example.net:27017` are the same thing. - An IPv6 literal goes in square brackets: `mongodb://[2001:db8::10]:27017`. Without the brackets the colons in the address are read as a port separator and the URI fails to parse. - A Unix domain socket is written as its percent-encoded path in the host position: `mongodb://%2Ftmp%2Fmongodb-27017.sock`. `mongod` creates that socket by default on Linux and macOS, and it is a handy way to connect from the same machine without opening a TCP port at all. - The slash before `?` is required whenever there are options. `mongodb://host:27017/?tls=true` is valid; drivers disagree on how they handle a missing slash, so do not rely on it. - Option names are case-insensitive (`authsource` and `authSource` are the same), values are not, and booleans are the lowercase strings `true` and `false`. Some drivers also accept `1`, `yes` and friends, but that is not portable. - Options are joined with `&`. A few options may repeat, such as `readPreferenceTags`; for the rest, the last one wins or the driver raises an error, depending on the driver. ##### The default: localhost:27017 `mongodb://localhost:27017` is the string in every tutorial, the default in Compass's new connection screen and what `MongoClient()` with no arguments connects to in PyMongo. It has one well-known trap. On Node.js 17 and later, `localhost` can resolve to the IPv6 address `::1` first, while a default `mongod` binds only to `127.0.0.1`. The result is `connect ECONNREFUSED ::1:27017` against a server that is running perfectly well. Newer Node releases try both address families and make it less common, but the robust fix is to write `127.0.0.1` instead of `localhost` in local URIs, or to add `::1` to `net.bindIp` in `mongod.conf`. `mongosh` with no arguments does not use the bare default either. It connects to `mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000`, and prints that string as its first line, which is a decent hint about which options are worth setting when you are only ever talking to one local server. ##### Which database the path names The `/database` part is the default database for the client and the default for `authSource` when that option is absent. It does not restrict anything: a client connected with `/shop` can still open `client["reports"]` if the user has rights to it. Leave the path empty and the default database is driver-specific (often `test` or none at all), while `authSource` falls back to `admin`. That second default is the source of most authentication confusion, and the [users and roles guide](https://www.renode.space/blog/mongodb-users-and-roles) explains why users live where they do. #### mongodb+srv:// and the DNS records behind it A seed list URI (`mongodb+srv://`) replaces the host list with one DNS name. When the driver sees it, it makes two lookups before it opens a single connection. ```diagram title: What a driver does with a mongodb+srv URI uri = mongodb+srv URI | cluster0.example.net srv = SRV lookup | _mongodb._tcp.cluster0... txt = TXT lookup | cluster0.example.net list = Host list + options | tls=true implied rs = Replica set members | :27017 each uri -> srv : hosts and ports uri -> txt : default options srv -> list txt -> list list -> rs : normal discovery ``` The SRV record lives at `_mongodb._tcp.` and lists one target hostname and port per member. The TXT record lives at the hostname itself and holds URI options in query-string form, such as `authSource=admin&replicaSet=rs0`. Only three options are allowed there - `authSource`, `replicaSet` and `loadBalanced` - and there may be only one TXT record; more than one is an error. Anything written in the URI itself overrides the TXT record. You can see exactly what a driver will see with ordinary DNS tools: ```bash $ dig +short SRV _mongodb._tcp.cluster0.example.net 0 0 27017 shard-00-00.example.net. 0 0 27017 shard-00-01.example.net. 0 0 27017 shard-00-02.example.net. $ dig +short TXT cluster0.example.net "authSource=admin&replicaSet=rs0" ``` On Windows, `nslookup -type=SRV _mongodb._tcp.cluster0.example.net` does the same job. If the SRV query comes back empty, the error in your application will be `querySrv ENOTFOUND` (Node) or a `ConfigurationError` mentioning the DNS name (PyMongo), and no change to options will help. The rules that follow from the design: - Exactly one hostname, and no port. The ports come from the SRV records. - The hostnames the SRV record returns must sit under the same parent domain as the name you queried. `cluster0.example.net` may point at `shard-00-00.example.net`, but not at `db.othercompany.com`; drivers reject those targets as a security measure. Older drivers also insisted on at least three dot-separated parts in the name, which newer releases relaxed - check your driver's changelog if a two-part name fails. - TLS is on by default. Add `tls=false` explicitly if you really mean an unencrypted connection, which with `+srv` you almost never do. - `directConnection=true` is not allowed with `+srv`, because the whole point is that the driver discovers several servers. - Two newer options tune the lookup: `srvServiceName` (default `mongodb`) changes the `_mongodb` part of the record name, and `srvMaxHosts` limits how many of the returned hosts are used, which is meant for large sets of `mongos` routers. When connected to sharded clusters through `mongos`, drivers re-query the SRV record periodically and add or drop routers as the records change. For a replica set they do not need to, because the set itself reports its membership once the first connection is made. `mongodb+srv://` is worth using when someone maintains those records for you - a managed cluster whose members change, or your own replica set with records you publish in your DNS zone. It is not worth it for one server. A single database with an A record and `mongodb://db.example.net:27017` is simpler, has no extra lookups and fails in fewer interesting ways. [DNS records explained](https://www.renode.space/blog/dns-records-explained) covers the A, TXT and SRV types if you want to publish them yourself. #### Multiple hosts and replicaSet A comma-separated host list is a seed list. The driver connects to the hosts it was given, asks each one for the replica set's current view of itself (the `hello` command), and from then on works from what the server says rather than from the URI. ```text mongodb://db1.example.net:27017,db2.example.net:27017,db3.example.net:27017/shop?replicaSet=rs0 ``` What that means in practice: - You do not have to list every member. One reachable member is enough for discovery, though listing all of them means start-up still works when the first one is down. - The order of hosts does not decide where writes go. Writes always go to the current primary; reads go where `readPreference` sends them. - `replicaSet` must match the set's actual name (the `_id` given to `rs.initiate()`). A member reporting a different set name is dropped from the topology, and if every member is dropped you get a server selection timeout rather than a clear message. `db.hello().setName` in `mongosh` tells you the real name. - The hostnames the driver ends up using are the ones in the replica set configuration, not the ones in your URI. If the set was initiated with `localhost:27017` or with Docker-internal container names, a client outside that network discovers hostnames it cannot resolve, and times out after a successful first contact. Fix the member hostnames in the replica set configuration with `rs.reconfig()`; do not paper over it on the client. For a sharded cluster the host list names `mongos` routers instead, there is no `replicaSet` option, and the driver spreads operations across the routers it can reach, preferring the ones with the lowest latency. ##### directConnection `directConnection=true` tells the driver to talk only to the one host in the URI and skip discovery. It is allowed with a single host only, and never with `+srv`. Use it when: - You want to connect to a specific member, usually a secondary, for maintenance or for running a query without touching the primary. - The replica set's configured hostnames are unreachable from where you are, such as through an SSH tunnel to one member. [SSH tunnels and port forwarding](https://www.renode.space/blog/ssh-tunnels-and-port-forwarding) covers that setup. The default matters, and it changed. In PyMongo 4 and the Node driver from 4.0, `directConnection` defaults to `false`, so a URI with a single host that happens to be a replica set member gets discovery: the driver finds the primary and sends writes there. PyMongo 3 behaved as if it were `true` for a single host. Code that relied on "connect to that box only" after an upgrade suddenly starts talking to a different server, so set it explicitly whenever the distinction matters. On a standalone server, which reports no replica set at all, both values end up in the same place. `loadBalanced=true` is the third topology option, for clusters behind a load balancer that speaks the MongoDB protocol (used by some managed serverless offerings). It cannot be combined with `replicaSet`, `directConnection=true` or more than one host, and you will only ever see it in a URI a provider hands you. #### The options that change behaviour The full list of URI options is long. These are the ones that appear in real connection strings and change what the client does. | Option | Default | Effect | | --- | --- | --- | | `authSource` | path db, else `admin` | Database holding the user | | `tls` | `false`; `true` for `+srv` | Encrypts the connection | | `retryWrites` | `true` | Retries a failed write once | | `w` | server default | Write acknowledgement | | `readPreference` | `primary` | Which members serve reads | | `maxPoolSize` | `100` | Connections per server | | `connectTimeoutMS` | driver-specific | TCP connect timeout | | `appName` | none | Label in server logs | | `directConnection` | `false` | Skip topology discovery | ##### authSource Names the database where the user was created. The defaulting rules are above, and the troubleshooting is in the [authentication guide](https://www.renode.space/blog/mongodb-authentication-failed-and-authsource). The only thing to add here is the command-line equivalent: `mongosh` accepts `--authenticationDatabase`, which is the same setting under another name. ##### tls `tls=true` encrypts the connection and verifies the server's certificate against the system trust store, or against the CA named in `tlsCAFile`. `ssl=true` is the older alias and still works. The server has to be configured for TLS first; turning it on in the URI against a server that is not fails at the handshake and usually surfaces as a timeout. `tlsAllowInvalidCertificates=true` and `tlsInsecure=true` exist for testing and should never reach production. ##### retryWrites and w `retryWrites=true` has been the default in drivers since the MongoDB 4.2 era. A write that fails with a network error or a primary election is retried once, safely, because the server tracks the operation's identity. Retryable writes need a replica set or sharded cluster; on a standalone server the driver simply does not retry. You will occasionally see `retryWrites=false` in strings written for old servers or unusual storage engines - leave it at the default unless a server error tells you otherwise. `w` sets the write concern: `w=1` means the primary acknowledged the write, `w=majority` means a majority of voting members did, and `w=0` means nobody did and you will never hear about failures. Since MongoDB 5.0 the implicit server default for most replica sets is `majority`, so leaving `w` out of the URI is usually right. `journal=true` adds "and it is on disk in the journal", and `wtimeoutMS` stops a `majority` write from waiting forever when too few members are up. On a single server `majority` is satisfied by that server alone. ##### readPreference Accepts `primary`, `primaryPreferred`, `secondary`, `secondaryPreferred` and `nearest`. Anything other than `primary` can return data that is slightly behind, because secondaries replicate asynchronously. `maxStalenessSeconds` (minimum `90`) excludes secondaries that are too far behind, and `readPreferenceTags=dc:east` steers reads to members with matching tags. With a single host it has nothing to choose between. ##### maxPoolSize and connectTimeoutMS `maxPoolSize` is per server, per client. One client talking to a three-member replica set can open up to three hundred connections at the default, and every process in your application has its own client. Set it deliberately; [connection pools and limits](https://www.renode.space/blog/connection-pools-and-limits) has the arithmetic. `minPoolSize` keeps connections warm and `maxIdleTimeMS` closes idle ones. `connectTimeoutMS` is the TCP connect timeout for each connection, and its default is one of the few values that differs between drivers: the shared specification says 10 seconds, PyMongo uses 20 and the Node driver 30. It is not the setting that makes a bad URI hang - that is `serverSelectionTimeoutMS`, 30 seconds everywhere. Newer drivers also support `timeoutMS`, a single client-side limit for the whole operation, which is intended to replace several of the older per-stage timeouts. ##### appName A free-text label, up to 128 bytes, sent in the connection handshake. It shows in the server log for every connection and in `db.currentOp()`, so you can see which service opened which connections. It costs nothing; set it on every client. #### Building URIs for common setups Most strings fall into one of a handful of shapes. Reading these side by side is quicker than reading the specification. ```text # Local development mongodb://127.0.0.1:27017/shop # One remote server, user in admin mongodb://shopapp:pw@db.example.net:27017/shop?authSource=admin&appName=api # Three-member replica set, reads to secondaries allowed mongodb://shopapp:pw@db1.example.net,db2.example.net,db3.example.net/shop ?replicaSet=rs0&authSource=admin&readPreference=secondaryPreferred # One member only, through an SSH tunnel on local port 27018 mongodb://shopapp:pw@127.0.0.1:27018/shop?directConnection=true&authSource=admin # Managed cluster published in DNS mongodb+srv://shopapp:pw@cluster0.example.net/shop?appName=api # Same cluster written out by hand (what +srv was doing for you) mongodb://shopapp:pw@shard-00-00.example.net:27017,shard-00-01.example.net:27017 ,shard-00-02.example.net:27017/shop?replicaSet=rs0&authSource=admin&tls=true ``` The line breaks in the replica set examples are for reading only; a real URI is one line with no spaces. The last example is the one to remember when DNS is the problem: converting `+srv` to a plain list means copying the hosts from the SRV record, the options from the TXT record, and adding `tls=true` yourself. Miss any of the three and the connection fails in a different way. #### Where the string goes in each client Every official driver accepts the same URI, which is the point of having one. What differs is where the string is read from and what overrides what. ##### PyMongo ```python import os from pymongo import MongoClient client = MongoClient(os.environ["MONGODB_URI"], appName="reports", maxPoolSize=10) db = client.get_default_database() # the /database from the URI ``` Keyword arguments and URI options are interchangeable, and when the same option appears in both, the keyword argument wins. `get_default_database()` returns the database named in the path, and raises if there is none. To check a string without connecting, `pymongo.uri_parser.parse_uri(uri)` returns the parsed hosts, credentials and options as a dictionary and raises on anything malformed, including a `+srv` name with no records. ##### Node.js driver ```js import { MongoClient } from "mongodb"; const client = new MongoClient(process.env.MONGODB_URI, { appName: "api" }); const db = client.db(); // the /database from the URI ``` The second argument takes the same options in camelCase. `client.db()` with no name returns the URI's default database. Mongoose passes the string straight through with `mongoose.connect(uri)`. The parsing is done by the `mongodb-connection-string-url` package, which the driver depends on and which you can use yourself to inspect a URI or mask its password before logging it. ##### mongosh ```bash $ mongosh 'mongodb://db1.example.net,db2.example.net/shop?replicaSet=rs0' \ -u shopapp -p --authenticationDatabase admin ``` Quote the URI with single quotes so the shell leaves `&` and `$` alone. `mongosh` also accepts the replica set in the older `--host rs0/db1.example.net:27017,db2.example.net:27017` form. Once connected, `db.hello()` shows `setName`, the member list and which one is primary, which settles any doubt about what the driver will discover. ##### .NET: appsettings.json and web.config The C# driver takes the URI as a string, and in ASP.NET Core it belongs in the `ConnectionStrings` section: appsettings.json: ```json { "ConnectionStrings": { "Mongo": "mongodb://shopapp:pw@db.example.net:27017/shop?authSource=admin" } } ``` Program.cs: ```csharp var cs = builder.Configuration.GetConnectionString("Mongo"); var url = new MongoUrl(cs); builder.Services.AddSingleton(new MongoClient(url)); builder.Services.AddSingleton(sp => sp.GetRequiredService().GetDatabase(url.DatabaseName)); ``` `MongoUrl` parses the string, and its `DatabaseName` gives you the path database. Register the client as a singleton for the same reason as every other driver: it is a pool. In production, set the value as the environment variable `ConnectionStrings__Mongo` rather than writing the password into the file; [environment variables and secrets](https://www.renode.space/blog/environment-variables-and-secrets) covers the pattern. On .NET Framework the string lives in `web.config` or `app.config` and is read with `ConfigurationManager.ConnectionStrings["Mongo"].ConnectionString`. That file is XML, so every `&` between options has to be written `&`: web.config: ```xml ``` A raw `&` there makes the whole configuration file fail to load, which tends to be reported as an application start-up error with no mention of MongoDB. The same idea for SQL Server strings is in [SQL Server connection strings](https://www.renode.space/blog/sql-server-connection-strings). ##### Compass Paste the URI into the box on the new connection screen. "Advanced Connection Options" splits it into tabs: the General tab has the scheme switch between `mongodb` and `mongodb+srv`, the host list and a Direct Connection checkbox; Authentication holds the username, password and authentication database; TLS/SSL and Proxy/SSH have their own tabs. Edits in the tabs rewrite the string and the reverse, which makes Compass a reasonable way to see how a URI you were sent is being interpreted - including whether it parsed the host list the way you meant. #### Mistakes that are about the format itself These are the errors caused by the shape of the string rather than by credentials or the network. - **A port on a `+srv` URI.** `mongodb+srv://cluster0.example.net:27017` is rejected outright. Remove the port. - **Several hosts on a `+srv` URI.** Also rejected; `+srv` takes one name. - **`directConnection=true` with several hosts.** Rejected, because there is no single server to connect to directly. - **A `replicaSet` name that does not match.** Server selection times out after 30 seconds while every host was reachable. Compare with `db.hello().setName`. - **Fallback thinking.** Listing a production server and a backup server in one URI does not create failover between unrelated machines. Unless they are members of the same replica set, the driver sees two topologies that disagree and you get errors, not redundancy. - **Spaces or line breaks.** Copying a wrapped URI out of a document or a chat message brings a space or newline with it. Drivers report an invalid host or an unknown option, and the stray character is invisible in most logs. - **Unencoded special characters in the password.** Covered in detail in [MongoDB connection strings](https://www.renode.space/blog/mongodb-connection-strings-getting-started); the short version is to encode once, or pass credentials as separate options. > [!WARNING] A URI with a password in it is a secret wherever it sits - an `.env` file, `appsettings.json`, a Compass favourite or a log line from an exception handler. Mask it before printing it, and keep it out of the repository. On RE:NODE [MongoDB hosting](https://www.renode.space/database-hosting#mongodb), each server is created with credentials generated for it and reached directly on the host and port shown on the plan. No SRV records are published for it and there is no proxy in front, so the right form is `mongodb://` with that host and port, the generated user, and `authSource` pointing at the database the user lives in. The [databases section of the guides](https://www.renode.space/guides#databases) shows where the panel lists those details. If your application runs on one of the app lines, such as Python, Node.js or C# / .NET, the URI goes in that server's environment variables on the Startup tab rather than in the code. #### FAQ ##### What is the default MongoDB URI? `mongodb://localhost:27017`, with no credentials and no database. If `localhost` resolves to IPv6 on your machine and `mongod` listens only on IPv4, use `mongodb://127.0.0.1:27017` instead. ##### Can I use mongodb+srv with an IP address? No. `+srv` exists to look up SRV and TXT records for a DNS name, and an IP address has none. Use `mongodb://` with the address and port. ##### Do I need to list every replica set member in the URI? No. One reachable member is enough, because the driver learns the rest from the set. Listing two or three means the client can still start when one of them is down, which is why most production strings do. ##### Why did my app start writing to a different server after a driver upgrade? Most likely `directConnection` changed default. Newer drivers discover the replica set from a single host and route writes to the primary. Add `directConnection=true` if you really mean that one server, and leave it off if you want normal replica set behaviour. ##### Does the order of options in the URI matter? No. Options are a set of key-value pairs. What matters is that an option in the URI overrides the same option from a `+srv` TXT record, and that driver arguments passed in code usually override the URI. ##### What does replicaSet do on a single server? On a standalone server it makes the connection fail, because the server reports no set name and the driver is waiting for a member of `rs0`. Only add `replicaSet` when the server really is a replica set member, even a one-member set. RE:NODE MongoDB servers come with credentials generated for each server and are reached on the host and port shown on the plan, so the URI is a plain `mongodb://` string you can build in a minute. Backups are included on every plan, from $7 a month. [See MongoDB plans](https://www.renode.space/database-hosting#mongodb) --- ### MongoDB users and roles: createUser and least privilege Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/mongodb-users-and-roles A MongoDB user is a name plus the database it was created in, and it can do exactly what its roles allow and nothing else. You create one with `db.createUser()`, give it one or more roles - each role scoped to a database - and the user then authenticates against the database it was created in. For an application, the right answer is almost always a dedicated user holding `readWrite` on that application's database and nothing more. For a dashboard, `read`. For a backup job, `backup`. The superuser with `root` stays in the password manager and is used by a human, rarely. That is the whole model. The rest of this guide is the detail that makes it work: which built-in role does what (including the two that are far more powerful than their names suggest), where users are actually stored, how to list them, change their passwords and adjust their roles, and how to write a custom role when no built-in one is narrow enough. Everything here uses `mongosh` and applies to MongoDB 5.0 and later; most of it is unchanged back to 4.x. #### Where users live: the authentication database Every user is created in a database, and that database is part of its identity. `shopapp` created in `shop` and `shopapp` created in `admin` are two different users, with separate passwords and separate roles. The database a user was created in is called its authentication database, and it is what the `authSource` option in a connection string names. [MongoDB connection strings](https://www.renode.space/blog/mongodb-connection-strings-getting-started) covers how that option is resolved and why getting it wrong produces `Authentication failed` with a correct password. Physically, all users sit in one collection regardless of where they were created: `admin.system.users`. The `db` field on each document records the authentication database. That is why you sometimes see users written as `shopapp@shop` - name, then authentication database. The authentication database and the databases a user can work in are independent. A user created in `admin` can hold `readWrite` on `shop` and nothing on `admin` itself, and a user created in `shop` can be granted roles on `analytics` as well. Where the user lives decides only where it logs in; the roles decide what it can touch. Two conventions both work: - **Every user in `admin`.** One place to look, one `authSource=admin` in every connection string, and roles scoped per database. This is the more common setup on single servers. - **Each application user in its own database.** `shopapp` lives in `shop`, `blogapp` lives in `blog`, and `show users` inside each database lists exactly the accounts that belong to it. The connection string uses `authSource=shop`. Pick one and keep to it. The mixed state - some users in `admin`, some elsewhere, nobody sure which - is where wasted afternoons come from. > [!NOTE] > `db.dropDatabase()` does not remove the users whose authentication database it was - they stay in `admin.system.users`. Dropping a test database to clean up therefore leaves its users behind, still able to log in. Run `db.dropAllUsersFromDatabase()` in that database first if you mean to remove them too. #### Creating a user with db.createUser Authentication has to be enabled first - `security.authorization: enabled` in `mongod.conf`. Without it, roles are not enforced and every connection can do anything. On a hosted database this is already on and you receive a first set of credentials. On your own installation, the localhost exception lets a connection from the server itself create the first user in `admin` while no users exist; it closes as soon as that user is created. Connect as a user allowed to manage users, switch to the authentication database you have chosen, and create the user: ```js use admin db.createUser({ user: "shopapp", pwd: passwordPrompt(), roles: [ { role: "readWrite", db: "shop" } ] }) ``` The fields: | Field | Required | What it holds | | --- | --- | --- | | `user` | yes | The name, unique within this database | | `pwd` | yes | The password; `passwordPrompt()` asks for it | | `roles` | yes | An array, which may be empty `[]` | | `customData` | no | Any document, for your own notes | | `authenticationRestrictions` | no | Allowed client and server addresses | | `mechanisms` | no | `SCRAM-SHA-1`, `SCRAM-SHA-256`, or both | `passwordPrompt()` matters more than it looks. A password typed inline, `pwd: "hunter2"`, sits in terminal scrollback, in screen shares and in any session recording or script you paste it into. `mongosh` already keeps known credential commands out of its history file, but prompting means the question never comes up. Roles can be written two ways. `{ role: "readWrite", db: "shop" }` is explicit and always correct. A bare string, `"readWrite"`, means that role on the database you are currently in - so the same command run in `admin` grants `readWrite` on `admin`, which is almost never what you meant. Use the document form every time and the question does not arise. `customData` is free-form and worth using for the one fact you will want later: `customData: { owner: "checkout-api", created: "2026-10-15" }`. It shows up when you list users, which is the moment you are trying to work out what an old account is for. ##### Restricting where a user may connect from `authenticationRestrictions` limits a user to particular client addresses, which is a cheap second layer behind the firewall: ```js use admin db.createUser({ user: "reporting", pwd: passwordPrompt(), roles: [ { role: "read", db: "shop" } ], authenticationRestrictions: [ { clientSource: ["10.0.0.0/24"] } ] }) ``` A login from any other address fails authentication even with the correct password. It is only as useful as your addresses are stable: an application on a platform without a fixed outbound address cannot use it. It also does not replace binding and firewalling - an unreachable port is still the better control, as the [database security checklist](https://www.renode.space/blog/database-security-checklist) argues at length. #### The built-in roles, and what they really allow MongoDB ships with a set of roles that cover most needs. Database-scoped roles are granted on one database at a time: | Role | Allows | Typical holder | | --- | --- | --- | | `read` | Query every non-system collection | Dashboards, reporting, humans | | `readWrite` | `read` plus insert, update, delete | The application | | `dbAdmin` | Indexes, stats, profiling, validation | Rarely anyone on its own | | `userAdmin` | Create and modify users and roles | Nobody routinely | | `dbOwner` | `readWrite`, `dbAdmin` and `userAdmin` | A trusted admin of one database | Roles that only exist on the `admin` database and apply to the whole server: | Role | Allows | Typical holder | | --- | --- | --- | | `readAnyDatabase` | `read` on every database | Server-wide reporting | | `readWriteAnyDatabase` | `readWrite` on every database | Migration tools, briefly | | `dbAdminAnyDatabase` | `dbAdmin` on every database | Operations tooling | | `userAdminAnyDatabase` | `userAdmin` on every database | Effectively the superuser | | `clusterMonitor` | Read-only server and replication stats | Monitoring agents | | `backup` | Everything `mongodump` needs | Backup jobs | | `restore` | Everything `mongorestore` needs | Restore jobs, briefly | | `root` | All of the above combined | The emergency account | Three of these need an honest note, because the names undersell them. **`readWrite` can drop collections.** Besides inserts, updates and deletes, it includes `createCollection`, `createIndex`, `dropIndex` and `dropCollection`. It cannot drop the database, but an application with `readWrite` and a bad migration script can still empty it collection by collection. If your application never creates or drops collections at runtime, a custom role (below) can take that away. **`dbAdmin` is not read access - but it can delete.** It covers indexes, statistics, the profiler, `collMod` and validation, and it does not let you `find` documents in ordinary collections. It does include `dropCollection` and `dropDatabase`. So a user with only `dbAdmin` cannot read your data and can destroy all of it. That is a strange combination to hand anyone, which is why it rarely appears on its own. **`userAdmin` is root in disguise.** A user who can create users and grant roles can grant any role on that database to anyone, including themselves. On a single database that makes `userAdmin` equivalent to `dbOwner`. On `admin`, `userAdmin` or `userAdminAnyDatabase` can grant `root`. MongoDB's own documentation says as much. Treat any account holding either as a superuser, protect it like one, and never put it in an application's connection string. `dbOwner` and anything ending in `AnyDatabase` belong in the same bucket for the purpose of applications: never. If the application's user can reach every database, a SQL-injection-style query bug or a leaked `.env` file exposes all of them. #### Least privilege: one user per job The useful way to think about users is by job, not by person or by system. Each thing that connects gets its own user with the narrowest role that lets it work. A typical small setup looks like this: ```js use admin // The running application: read and write its own database only. db.createUser({ user: "shop_app", pwd: passwordPrompt(), roles: [ { role: "readWrite", db: "shop" } ], customData: { owner: "checkout-api" } }) // Dashboards and people poking about: read only. db.createUser({ user: "shop_ro", pwd: passwordPrompt(), roles: [ { role: "read", db: "shop" } ] }) // Nightly dump: the backup role, nothing else. db.createUser({ user: "backup_job", pwd: passwordPrompt(), roles: [ { role: "backup", db: "admin" } ] }) // The monitoring check: server stats, no data. db.createUser({ user: "monitor", pwd: passwordPrompt(), roles: [ { role: "clusterMonitor", db: "admin" } ] }) ``` Separate users pay off in three ways. A leaked credential exposes one job's rights, not everything. Rotating one password touches one consumer, not every service at once. And the server log and `db.currentOp()` show the user on every operation, so when a nine-second query appears you know which service sent it before you start guessing. Combine that with `appName` in each connection string and the question "who is doing this" answers itself. For a backup user that only ever dumps one database, `read` on that database is enough for `mongodump` to export its collections. The `backup` role is for dumping the whole server, including users and roles with `--dumpDbUsersAndRoles`. The mechanics of both are in [mongodump and mongorestore](https://www.renode.space/blog/mongodump-and-mongorestore-guide). Give `restore` to a user only for the duration of a restore; it can write into any database. ```diagram title: Each consumer connects as its own user app = Application | shop_app dash = Dashboard | shop_ro dump = Backup job | backup_job mon = Monitoring | monitor mongod = mongod | auth via admin shop = shop database app -> mongod : readWrite on shop dash -> mongod : read on shop dump -> mongod : backup mon --> mongod : clusterMonitor mongod -> shop ``` The same idea in other engines has different syntax and the same reasoning: [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges) and [PostgreSQL roles and permissions](https://www.renode.space/blog/postgresql-roles-and-permissions) cover them, if you run more than one. #### Listing users and checking what they can do Four commands answer almost every question about who exists and what they may do: ```js use admin show users // users in the current database db.getUsers() // the same, as documents db.getUser("shop_app", { showPrivileges: true }) db.adminCommand({ usersInfo: { forAllDBs: true } }) // every user on the server ``` `show users` and `db.getUsers()` only list users whose authentication database is the one you are in. If you create users in several databases, the `forAllDBs` form is the only complete list, and it needs a user with rights to view users across the server. `showPrivileges: true` expands a user's roles into the actual actions they grant, including roles inherited from other roles, under `inheritedPrivileges`. It is long, but it is the answer to "can this user drop that collection" without testing it in production. To check the user you are currently connected as: ```js db.runCommand({ connectionStatus: 1, showPrivileges: true }) ``` `authInfo.authenticatedUsers` shows who you are and where you authenticated; `authenticatedUserRoles` shows the roles in effect. When an application gets `not authorized on shop to execute command`, running this with the application's credentials in `mongosh` tells you exactly what it has, which is faster than reading the code that created it. #### Changing passwords, roles and removing users Users change over their life, and every change has its own helper: ```js use admin db.changeUserPassword("shop_app", passwordPrompt()) db.grantRolesToUser("shop_ro", [ { role: "read", db: "analytics" } ]) db.revokeRolesFromUser("shop_ro", [ { role: "read", db: "analytics" } ]) db.updateUser("shop_app", { customData: { owner: "checkout-api-v2" } }) db.dropUser("old_worker") ``` Run them in the user's authentication database - a `changeUserPassword` in `shop` for a user that lives in `admin` fails with `User shop_app@shop not found`, which is the same mistake as a wrong `authSource` seen from the other side. `db.updateUser()` with a `roles` field replaces the whole array rather than adding to it. Passing one role to "add" it silently removes every other role the user had. Use `grantRolesToUser` and `revokeRolesFromUser` for incremental changes and keep `updateUser` for `customData`, `authenticationRestrictions` and full rewrites you meant. Changes to roles take effect immediately on a standalone `mongod`, including for connections that are already open. In a sharded cluster, `mongos` caches user information and refreshes it on `userCacheInvalidationIntervalSecs`, 30 seconds by default. ##### Rotating an application password without downtime Changing the password of a user that a running application is using breaks new connections the moment you do it. Existing pooled connections stay authenticated, so the failure shows up later and looks random. The clean way is two users: 1. Create `shop_app_2` with exactly the same roles as `shop_app`. 2. Put the new user and password into the application's environment and restart or redeploy it. 3. Watch `db.currentOp()` or the server log until nothing authenticates as `shop_app`. 4. `db.dropUser("shop_app")`. Next rotation, go the other way. It costs one extra user for a few minutes and avoids the half-broken state entirely. Where the credentials should live while you do this is covered in [environment variables and secrets](https://www.renode.space/blog/environment-variables-and-secrets). ##### Letting users change their own password No built-in role below `userAdmin` includes changing your own password, so a user with only `readWrite` cannot run `changeUserPassword` on itself. If you want people to rotate their own credentials, grant a small custom role holding the `changeOwnPassword` action, shown in the next section. #### Custom roles for anything narrower When no built-in role fits - a worker that may insert into one collection and read one other, a reporting user that must not see a `customers` collection - write a role. A role is a list of privileges, each a resource plus actions, and optionally other roles it inherits: ```js use shop db.createRole({ role: "ordersWriter", privileges: [ { resource: { db: "shop", collection: "orders" }, actions: [ "find", "insert", "update" ] }, { resource: { db: "shop", collection: "products" }, actions: [ "find" ] } ], roles: [] }) use admin db.grantRolesToUser("order_worker", [ { role: "ordersWriter", db: "shop" } ]) ``` That worker can read products, read and add and change orders, and do nothing else: no `remove`, no `dropCollection`, no access to any other collection. Resource forms worth knowing: | Resource | Means | | --- | --- | | `{ db: "shop", collection: "orders" }` | One collection | | `{ db: "shop", collection: "" }` | Every non-system collection in `shop` | | `{ db: "", collection: "orders" }` | `orders` in any database | | `{ cluster: true }` | Server-wide actions such as `serverStatus` | Two rules about where roles live. A role created in an ordinary database can only grant privileges on that database. A role created in `admin` can grant privileges on any database and on the cluster, so cross-database roles go in `admin`. And a role is referenced by name and database together, exactly like a user - `ordersWriter` in `shop` is `{ role: "ordersWriter", db: "shop" }`. A few actions cover most custom roles: `find`, `insert`, `update`, `remove` for data; `createIndex`, `dropIndex`, `createCollection`, `dropCollection` for structure; `listCollections`, `listIndexes`, `collStats`, `dbStats` for tools that inspect; `changeOwnPassword` and `changeOwnCustomData` for self-service. The full list is in the MongoDB manual's privilege actions page, and it is worth checking there rather than guessing, because a misspelt action is rejected at `createRole` time but a missing one only shows up when the application hits it. The self-service password role mentioned above: ```js use admin db.createRole({ role: "changeOwnPassword", privileges: [ { resource: { db: "admin", collection: "" }, actions: [ "changeOwnPassword", "changeOwnCustomData" ] } ], roles: [] }) ``` The resource is the user's authentication database. Grant it alongside the user's real role and they can run `db.changeUserPassword()` on themselves and nobody else. Inspect and maintain roles with `db.getRoles({ showPrivileges: true })`, `db.getRole("ordersWriter", { showPrivileges: true })`, `db.grantPrivilegesToRole()`, `db.revokePrivilegesFromRole()` and `db.dropRole()`. Changing a role changes every user holding it at once, which is the point - and also the reason to name roles after a job, not a person. > [!TIP] > Before handing a custom role to an application, log in with it in `mongosh` and run the operations the application actually performs. `not authorized on shop to execute command { delete: "orders" ... }` in a test session is a two-minute fix; the same message in production at the end of a deploy is a rollback. #### Errors you will meet **`Authentication failed` (code 18).** Usually the right password checked against the wrong authentication database. Run `db.adminCommand({ usersInfo: { forAllDBs: true } })` as an admin and look at the `db` field of the user, then match `authSource` to it. **`not authorized on shop to execute command { ... }` (code 13).** Authenticated, but the roles do not include the action. The message names the command; `connectionStatus` with `showPrivileges` shows what the user does have. Grant the narrowest role that adds it, not `dbOwner`. **`command createUser requires authentication`.** You are connected anonymously to a server with authorization on. Reconnect with credentials. **User already exists, or user not found.** The `use` before the command put you in a different database than you thought, so MongoDB looked for `shop_app@shop` instead of `shop_app@admin`. Running `db.getName()` before user commands saves this. **`Could not find role: ordersWriter@admin`.** You referenced a custom role by name in a database where it was not created. Give the full `{ role, db }` document with the database it lives in. The same error appears for a built-in role with the wrong case: the names are `readWrite`, `dbAdmin`, `userAdmin` and `clusterMonitor`, and `readwrite` does not exist. #### On a hosted MongoDB On a server you rent rather than install, authentication is already on and the first credentials are given to you - on RE:NODE they are generated for each server and shown in the panel alongside the host and port, so there is no shared default to change. Treat that account as the superuser it is: use it to create the per-job users above, store it in a password manager, and do not put it in an application's environment. Managed cluster services sometimes take user management out of the shell entirely. MongoDB Atlas, for example, manages database users through its own interface and API rather than `db.createUser()`, though the roles are the same ideas. On a standalone server you reach by host and port, everything in this guide works as written. Users and roles are stored inside the database, so they are part of what a full backup has to capture. A whole-server `mongodump` with the `backup` role, or a filesystem-level copy of the data directory, includes `admin.system.users`; a single-database dump does not include users unless you ask for them. [Database backups and restores](https://www.renode.space/blog/database-backups-and-restores) covers which kind to take for which disaster. #### FAQ ##### Which database should I create MongoDB users in? Either `admin` or the application's own database works, and a user in either can hold roles on other databases. `admin` keeps every user in one place with `authSource=admin` everywhere; a user in its own database keeps each application's accounts next to its data. Choose one convention and use it consistently, because `authSource` has to match it. ##### What is the difference between readWrite and dbOwner? `readWrite` lets a user read and change data and manage collections and indexes. `dbOwner` adds `dbAdmin` and `userAdmin`, which means it can also drop the database and create users with any role on it. An application needs the first and should never hold the second. ##### How do I see all MongoDB users on a server? Run `db.adminCommand({ usersInfo: { forAllDBs: true } })` as a user with permission to view users. `show users` only lists users created in the database you are currently in, so it looks incomplete if you have users in more than one place. ##### Can I give a user read-only access to just some collections? Not with a built-in role - `read` covers every non-system collection in the database. Create a custom role with `find` on each collection you want visible and grant that instead. Collections not listed stay invisible to queries from that user. ##### Do role changes need a restart or a reconnect? No. On a standalone `mongod`, granting or revoking a role applies at once, even to connections that are already open. In a sharded cluster, `mongos` may take up to `userCacheInvalidationIntervalSecs` - 30 seconds by default - to notice. ##### Is a strong password enough if the application user has root? No. The password protects against guessing, not against the application being tricked into running a query it should not, or its environment file leaking. Limiting the role limits the damage in both cases, which is the part a password cannot do. MongoDB on RE:NODE runs as a standalone server reached on the host and port shown on the plan, with credentials generated for each server rather than a published default, NVMe storage, and backup slots you can run on demand or on a schedule. Create your per-application users on day one and the generated account never has to leave the password manager. [See database plans](https://www.renode.space/database-hosting) --- ### MySQL LTS vs Innovation releases: which version to run Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/mysql-lts-and-innovation-releases For a production database in late 2026 the answer is an LTS release: MySQL 8.4 or MySQL 9.7. Both get bug and security fixes for years, neither changes behaviour inside its series, and either is a safe target for an application you want to leave alone. MySQL 8.0 is finished - its last release, 8.0.46, shipped in April 2026 and Oracle publishes no more fixes for it. Innovation releases (8.1 to 8.3, 9.0 to 9.6, and now the calendar-numbered 26.7) are production-quality but supported only until the next one, roughly three months, so running one means upgrading four times a year through releases that are allowed to remove things. The rest of this guide explains the two tracks, the support dates that matter, the new calendar version numbers that started in July 2026, the upgrade paths you are and are not allowed to take, and how the version you actually get depends on where it comes from: your Linux distribution, Oracle's repositories, or a Docker tag that may move under you. #### The two release tracks Until 8.0, MySQL had one long-lived series per major version, and new features landed in patch releases. 8.0.18 brought hash joins and `EXPLAIN ANALYZE`; 8.0.30 brought `innodb_redo_log_capacity`. Two servers both called "8.0" could behave quite differently. Starting with 8.1 in July 2023, Oracle split releases into two tracks. | | LTS | Innovation | | --- | --- | --- | | What changes | Fixes only | Features, removals, new defaults | | New series | About every two years | About every quarter | | Support | 5 years premier + 3 extended | Until the next Innovation release | Oracle's manual describes both tracks as "production-grade quality". The difference is not quality, it is change. An LTS series is frozen in behaviour: 8.4.1 to 8.4.11 contain fixes, not new features or different defaults. That freeze is also why in-place downgrades work inside an LTS series - the data format does not change, so you can step from 8.4.11 back to 8.4.10 if a patch misbehaves. An Innovation release is the opposite. Each one may deprecate or remove features, change reserved words, change optimiser behaviour, and change defaults. The manual warns these changes "can have a big impact", especially on SQL syntax and query performance. Features removed during an Innovation cycle are removed for good in the LTS that closes it, which is why 8.0 to 8.4 broke more than the version number suggested - every removal from 8.1, 8.2 and 8.3 arrived at once. [MySQL 8.4 LTS: what changed](https://www.renode.space/blog/mysql-8-4-lts-what-changed) lists those. The pattern so far: a run of Innovation releases, then an LTS that freezes the result. | Innovation releases | Closed by LTS | LTS released | | --- | --- | --- | | 8.1, 8.2, 8.3 | 8.4 | April 2024 | | 9.0 to 9.6 | 9.7 | April 2026 | | 26.7, 26.10, ... | next LTS | not yet released | #### Support dates that matter These are the dates to put in a calendar. Oracle's Lifetime Support Policy gives an LTS series five years of premier support and three of extended support, counted from its first GA release. For Community Edition users the practical meaning is simple: patch releases keep coming until the end of the window. | Version | Track | End of support | | --- | --- | --- | | 5.7 | Pre-LTS | October 2023 | | 8.0 | Pre-LTS | April 2026 (8.0.46 was the last) | | 8.4 | LTS | Premier April 2029, extended April 2032 | | 9.7 | LTS | Premier around 2031, extended around 2034 | | 26.7 | Innovation | When 26.10 ships | The 9.7 dates follow from the five-plus-three rule applied to an April 2026 GA; Oracle's lifetime support document is where to confirm the exact month. Either way, 8.4 is supported for years yet - there is no rush to leave it, and an application that moved to 8.4 in 2025 does not need to move again until the end of the decade. On 8.0: "end of life" means no more fixes from Oracle, including for security vulnerabilities found after April 2026. Some distributions and cloud providers carry their own packages a while longer - Ubuntu 22.04 and 24.04 received 8.0.46 in June 2026, and Oracle extended 8.0 on its own HeatWave service to April 2027 - but those are the last patches of a line nobody is developing. A server still on 8.0 is the one to schedule first. > [!WARNING] > An Innovation release stops getting fixes the day the next one ships. A server installed on 9.3 in 2025 and never touched again has been unsupported since 9.4 came out, and it cannot simply be "patched" - the only supported way forward is to the next release or into the closing LTS. #### Calendar version numbers from 26.7 In July 2026 MySQL changed its numbering. 9.7 is the last release line with sequential version numbers; everything after uses calendar versions in the form `YY.M.P` - two-digit year, release month, patch number. The July 2026 Innovation release is 26.7.0, and Oracle has named the following Innovation releases as 26.10, 27.1, 27.4 and 27.7. Two things trip people up here. First, the number no longer tells you the track. 26.7 is an Innovation release because Oracle says so, not because of its digits, and a future LTS will also have a calendar number. Always check the release notes or the repository you installed from rather than inferring from the version string. On a running server: ```sql SELECT @@version, @@version_comment; ``` `@@version_comment` shows the build, for example `MySQL Community Server - GPL`; distribution builds add their own text. Second, the patch numbers inside an LTS series now follow the quarterly security cycle. Each quarterly Critical Patch Update (CPU) bumps the third number: 8.4.11, then 8.4.12. If a serious vulnerability needs a fix between quarters, Oracle issues a targeted patch (CSPU) with a fourth number - 8.4.11.1, 8.4.11.2 - and the next quarterly release goes back to three numbers. On Innovation releases the third number stays at 0, because the fix for a quarter is simply the next Innovation release. Windows MSI packages encode four-part versions differently (8.4.11.1 shows as 8.4.1101 in installer metadata), which is cosmetic. Scripts that parse `mysql --version` or `SELECT VERSION()` and assume a single-digit major version will misread `26.7.0`. Compare versions numerically per component, not as strings, and check monitoring dashboards and Ansible or Puppet facts that branch on "major version 8 or 9". #### Upgrade and downgrade paths The track model comes with strict rules about which jumps the server supports in place. The short version: LTS to the next LTS, never skipping one. ```diagram title: Supported in-place upgrade routes between series v80 = MySQL 8.0 | end of life v84 = MySQL 8.4 LTS | to 2032 v97 = MySQL 9.7 LTS | last sequential inn = 26.x Innovation | calendar versions next = Next LTS | closes 26.x v80 -> v84 : in place v84 -> v97 : in place v97 -> inn : in place inn -> next v84 --> inn : not supported ``` The rules, as the reference manual states them: - Within an LTS series, upgrades and downgrades in place are both supported. 8.4.3 to 8.4.11 and back is fine. - From one LTS to the next, an in-place upgrade is supported: 8.4.x to 9.7.x. Skipping an LTS is not - you cannot go from 8.4 straight to whatever LTS follows 9.7. - An Innovation release upgrades in place to a later Innovation release in the same cycle, or into the LTS that closes it: 9.1 to 9.2, or 9.5 to 9.7. - An Innovation release cannot cross into the next cycle directly. 8.3 goes to 9.0 through 8.4. - Only 9.7 LTS can upgrade in place into the first calendar-versioned cycle that 26.7 starts. - Downgrading an Innovation release always needs a logical dump and load. 8.0 sits before the model, but the same principle applies: 8.0 to 8.4 in place is supported from a recent 8.0 patch; 8.0 to 9.7 goes through 8.4. 5.7 has to reach 8.0 first. A logical dump and load sidesteps most of this. A `mysqldump` or MySQL Shell dump from 8.0 or 8.4 loads into 9.7 in nearly all cases, because it is just SQL; what can fail is the content - accounts on removed authentication plugins, reserved words used as identifiers, definers that do not exist. That is also the route on any hosted database, where you cannot swap binaries on the provider's server. [mysqldump backup and restore](https://www.renode.space/blog/mysqldump-backup-and-restore) covers the flags, and [migrating MySQL to a new host](https://www.renode.space/blog/migrating-mysql-to-a-new-host) the cut-over. Before any jump, run MySQL Shell's upgrade checker against the old server with the target version you intend: ```bash $ mysqlsh root@db.example.com:3306 -- util check-for-server-upgrade \ --target-version=9.7.2 ``` Use a MySQL Shell at least as new as the target, since the checker only knows about versions released before it. #### Which version to run in production For most people the decision is between the two supported LTS series. **Stay on or move to 8.4** if your application already works on it, if your hosting provider offers it, or if your ORM, driver or tooling has not caught up with 9.x. 8.4 has premier support until 2029; nothing forces a move before then. It is also the conservative target from 8.0, because 8.0 to 8.4 is the well-trodden path and 8.4 still lets you switch `mysql_native_password` back on as a stopgap. **Choose 9.7** for a new project with no history, or if you want what arrived in 9.0 to 9.6: the `VECTOR` data type, newer optimiser work, and several features Oracle moved from Enterprise into Community Edition with 9.7, including replication applier metrics and Group Replication flow-control statistics. Know the cost: `mysql_native_password` was removed entirely in 9.0, so any account or client still depending on it fails with no switch to turn it back on. Every driver must speak `caching_sha2_password`. **Use an Innovation release** for testing, for development environments that track what is coming, or if you have a specific feature you need now and a team prepared to upgrade every quarter. Running 26.x in production is legitimate - Oracle supports it - but only if someone owns that quarterly upgrade and the application's test suite runs against each new release. A rule of thumb that holds up: production on an LTS, one staging or CI database on the newest Innovation release. When the next LTS arrives, the Innovation-era removals have already shown up in CI, and the production upgrade is planned rather than discovered. > [!TIP] > Pin client libraries and server versions to the same decision. A Docker Compose file pinned to `mysql:8.4` with an application whose CI tests against `mysql:latest` is testing a different database from the one it runs on. #### Distribution packages What `apt install mysql-server` or `dnf install mysql-server` gives you depends on the distribution release, not on what Oracle currently recommends. Distributions pick a version when they freeze and carry it for the life of the release. | Distribution | Default MySQL | Note | | --- | --- | --- | | Ubuntu 22.04, 24.04 | 8.0 | Upstream support ended April 2026 | | Ubuntu 26.04 | 8.4 | LTS, patched through Ubuntu updates | | Debian 12, 13 | None | `default-mysql-server` is MariaDB | | RHEL 10 and rebuilds | 8.4 | AppStream | | RHEL 9 and rebuilds | 8.0 | Check `dnf module list mysql` for newer streams | On Ubuntu 24.04 the `mysql-server` package is still `mysql-server-8.0`. Ubuntu shipped 8.0.46 to it in June 2026, but there is no 8.0.47 coming from Oracle, so an Ubuntu 24.04 server running the distribution's MySQL is on a line that has stopped. The fixes are to upgrade the operating system to 26.04, or install 8.4 from Oracle's repository. Debian deserves a note because it surprises people: `apt install default-mysql-server` on Debian installs MariaDB, and the `mysql` command is MariaDB's client. MariaDB forked from MySQL 5.5 and has diverged since - it is not a MySQL version, has its own release and support schedule, and does not read a MySQL 8.4 data directory. If you need MySQL itself on Debian, it comes from Oracle's APT repository or Docker. To see what you have on any of them: ```bash $ mysqld --version $ apt-cache policy mysql-server # Debian and Ubuntu $ dnf info mysql-server # RHEL family ``` #### Oracle's own repositories Oracle publishes APT and Yum repositories at `repo.mysql.com`, and these are where the track is chosen explicitly. The repository setup package (`mysql-apt-config` on Debian and Ubuntu, `mysql84-community-release` and similar on the RHEL family) installs a configuration in which you pick one series: an 8.4 LTS repository, a 9.7 LTS repository, or the Innovation repository. On the Yum side each track is a separately named repository, and you enable exactly one. The manual's example uses `mysql-innovation-community` for Innovation and `mysql-9.7-lts-community` for 9.7 LTS: ```bash $ dnf repolist all | grep mysql $ sudo dnf config-manager --disable mysql-innovation-community $ sudo dnf config-manager --enable mysql-9.7-lts-community ``` On Debian and Ubuntu the choice is made in a dialog when `mysql-apt-config` is installed, and can be changed later: ```bash $ sudo dpkg-reconfigure mysql-apt-config $ sudo apt update ``` The point of picking a track rather than a version is what happens on `apt upgrade`. An LTS repository moves you from 8.4.10 to 8.4.11 and never to 9.7. The Innovation repository moves you from one Innovation release to the next, which is what Innovation means, and an unattended-upgrades job pointed at it will do a feature upgrade of your database whenever Oracle ships one. On a production server, enable an LTS repository and nothing else. Also check that Oracle publishes a repository for your distribution release before relying on it - new distribution releases are sometimes added months after they ship. #### Docker image tags The official `mysql` image on Docker Hub maps tags to tracks, and some of those tags move. As of October 2026 the supported tags are: | Tag | Points to | Moves when | | --- | --- | --- | | `latest`, `innovation` | 26.7 | Every Innovation release | | `lts`, `9`, `9.7` | 9.7 | A new LTS, or a 9.7 patch | | `8`, `8.4` | 8.4 | An 8.4 patch only | | `8.4.11`, `9.7.2` | That exact patch | Never | Each has `-oracle` or `-oraclelinux9` variants; all current images are built on Oracle Linux 9. The Debian-based image variants belonged to 8.0 and are gone with it, and `mysql:8.0` is no longer in the supported list - the tag still pulls, but nothing will rebuild it. Three tags cause most of the accidents. `latest` is an Innovation release. A Compose file with `image: mysql` or `image: mysql:latest` will, on the next `docker compose pull`, upgrade the database to whatever Innovation release is current - and since Innovation downgrades need a dump and load, there is no stepping back by changing the tag. `lts` moved. Until 9.7 shipped in April 2026, `mysql:lts` was 8.4. Anyone who pinned to `lts` thinking it meant "stable" got an in-place upgrade from 8.4 to 9.7 on their next pull - a supported upgrade, but one that removed `mysql_native_password` and could not be reversed by retagging. `lts` will move again when the next LTS arrives. `8` moved too. It pointed at 8.0 during 8.0's life and now points at 8.4. The safe pattern is to pin to the series you have chosen, and let patches arrive: compose.yaml: ```yaml services: db: image: mysql:8.4 environment: MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root MYSQL_DATABASE: app volumes: - db-data:/var/lib/mysql ``` `mysql:8.4` takes 8.4 patches and never jumps series. If you want each patch to be a decision as well, pin the full version (`mysql:8.4.11`) and bump it yourself. In both cases, the data directory in the volume is what carries the version forward: a container started from a newer series upgrades it on first start, and an older series refuses to start on it. [Docker Compose for small stacks](https://www.renode.space/blog/docker-compose-for-small-stacks) covers volumes and secrets in more depth. #### Checking and planning your version A short audit for any MySQL you are responsible for: 1. Find the exact version with `SELECT @@version;` and where it came from: distribution package, Oracle repository, Docker tag, or a hosting provider. 2. Map it to a track and a support date using the tables above. Anything on 8.0, 5.7, or an Innovation release that is not the latest one is unsupported now. 3. Check how it updates. A distribution package follows the distribution; an Oracle repository follows the track you enabled; a Docker tag follows whatever the tag points to; a hosted database follows the provider. 4. If you need to move, choose the target LTS, run the upgrade checker, move authentication off `mysql_native_password` (see [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges)), and rehearse on a copy. 5. Put the end-of-support date for the series you land on in a calendar, a year ahead of when it falls. #### FAQ ##### Is MySQL 9.7 an LTS release? Yes. MySQL 9.7.0 shipped in April 2026 as the second LTS series after 8.4, and it closes the 9.x Innovation cycle that began with 9.0. It is also the last release line with sequential version numbers; later releases use calendar versions such as 26.7. ##### Is it safe to run an Innovation release in production? Oracle supports it and calls it production quality, so it is not reckless. The catch is the support window: each Innovation release is patched only until the next one, so staying supported means a feature upgrade roughly every three months, each of which may remove something. Without the time to test each one, use an LTS. ##### Can I upgrade from MySQL 8.0 directly to 9.7? Not in place. The supported in-place route is 8.0 to 8.4, then 8.4 to 9.7. A logical dump from 8.0 loaded into a fresh 9.7 server usually works, provided no accounts depend on `mysql_native_password` and the upgrade checker reports nothing serious. ##### What does mysql:latest give me in Docker? The current Innovation release - 26.7 as of October 2026. It changes every quarter. For a database you care about, pin to `mysql:8.4` or `mysql:9.7`, not `latest` and not `lts`, since `lts` will move to the next LTS when it ships. ##### Do I have to leave MySQL 8.4 for 9.7? No. 8.4 has premier support until April 2029 and extended support until April 2032, and it keeps getting quarterly patch releases. Moving to 9.7 is worth planning before 2029, but there is no deadline before then. ##### Is MariaDB a version of MySQL? No. MariaDB began as a fork of MySQL 5.5 and is now a separate database with its own versions and support schedule. Debian installs it when you ask for `default-mysql-server`. It speaks the same protocol for most clients, but its data files and newer features are not interchangeable with MySQL 8.4 or 9.7. The MySQL line on RE:NODE runs MySQL 8.4 LTS, with a root password, an application database and an application user generated for each server and reached on the plan's host and port. Plans start at 1 GB of memory and 10 GB of NVMe. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL 8.4 mysql_native_password: errors and the fix Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/mysql-native-password-and-caching-sha2 If an application stopped connecting after a move to MySQL 8.4, and the server says `Plugin 'mysql_native_password' is not loaded`, the account is still using the old SHA-1 based login method and 8.4 ships with it switched off. You have two ways out. The real fix is to move the account to `caching_sha2_password` with `ALTER USER ... IDENTIFIED WITH caching_sha2_password BY '...'`, after checking that the application's driver understands it. The stopgap is to start the server with `mysql_native_password=ON`, which works on 8.4 and nowhere after it: MySQL 9.0 removed the plugin entirely. The errors that look similar but come from the other side - an old client that cannot speak `caching_sha2_password`, or a new 9.x client that can no longer speak `mysql_native_password` - have their own fixes, and telling them apart is most of the work. This post covers the authentication side of the 8.4 upgrade in depth. The wider list of what changed between 8.0 and 8.4 is in [MySQL 8.4 LTS: what changed](https://www.renode.space/blog/mysql-8-4-lts-what-changed), and creating accounts and granting privileges is in [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges). #### What changed, version by version The plugin has been on its way out for years. What differs between versions is how loudly MySQL tells you. | Version | `mysql_native_password` | Default for new accounts | | --- | --- | --- | | 5.7 | Available, the default | `mysql_native_password` | | 8.0 | Available, deprecated (warnings from 8.0.34) | `caching_sha2_password` | | 8.4 LTS | Present, disabled unless enabled at start-up | `caching_sha2_password` | | 9.0 and later | Removed from the server | `caching_sha2_password` | The 8.0 years are why so many installations still have native accounts. The default changed in 8.0.4, but a server upgraded from 5.7 kept every existing account on its old plugin, and plenty of setup guides, Docker Compose files and hosting control panels set `default_authentication_plugin=mysql_native_password` to avoid driver trouble. Those accounts carried on working through the whole 8.0 series. 8.4 is where that stops. Note the subtle point in the 8.4 row. The plugin is not gone: the code is still in the server, accounts that use it still exist in `mysql.user`, and the data survives the upgrade untouched. It is simply not loaded, so a login against such an account cannot be completed, and neither can a `CREATE USER` or `ALTER USER` that names it. On 9.x the same accounts still exist after an upgrade, but there is no switch left to turn them back on. #### How the two plugins differ `mysql_native_password` stores a double SHA-1 hash of the password, unsalted. Two accounts with the same password have the same hash, and SHA-1 is fast enough that a leaked `mysql.user` table can be brute-forced cheaply. The login itself is a challenge-response that never sends the password, which is why it worked without TLS for two decades. `caching_sha2_password` stores a salted SHA-256 hash put through thousands of rounds, which makes stored hashes far more expensive to attack. The cost is that checking a password against that hash is deliberately slow, so the server keeps an in-memory cache of accounts that have recently logged in successfully. That gives the plugin two paths: - **Fast authentication** - the account is in the cache, so the client answers a scramble with a SHA-256 based response and is let in without the password ever crossing the wire. This is the normal case. - **Full authentication** - the account is not in the cache, which happens on the first login after a server start, after `FLUSH PRIVILEGES`, and after the account's password changes. The server needs the actual password to check it against the stored hash, so the client must send it, and MySQL will only accept it over a secure channel: a TLS connection, a Unix socket, or encrypted with the server's RSA public key. ```diagram title: caching_sha2_password chooses a path per login client = Client | driver or mysql CLI cache = Server auth cache | in memory fast = Fast auth | scramble only full = Full auth | password needed secure = TLS, socket or RSA | else refused client -> cache : is account cached? cache -> fast : yes cache -> full : no full -> secure ``` That second path is the source of the "it works and then it does not" reports. An application connects without TLS, everything is fine for days because the account is cached, the server restarts, and the next login fails until something logs in once over TLS and warms the cache again. Configure the client for TLS or for RSA key retrieval from the start and the problem never appears. #### The errors, and which side they come from Every authentication failure in this area comes from one of three situations: the server cannot run the account's plugin, the client cannot run the plugin the server asked for, or the client can run it but the connection is not secure enough for full authentication. | Error | Comes from | Meaning | | --- | --- | --- | | `ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded` | Server | The account uses the native plugin and the server has it off or removed | | `ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded` | Client | The client library is too old for the new plugin | | `ERROR 2059 (HY000): Authentication plugin 'mysql_native_password' cannot be loaded` | Client | A 9.x client connecting to an account or server that still uses the old plugin | | `ERROR 2061 (HY000): ... Authentication requires secure connection` | Client | Full authentication over a plain connection with no RSA key | ##### Plugin 'mysql_native_password' is not loaded This is the 8.4 and 9.x server telling you about the account, not the client. Any client, however new, gets it when logging in as an account whose `plugin` column still says `mysql_native_password`. You also get it as a statement error when running `CREATE USER ... IDENTIFIED WITH mysql_native_password` - a line that appears in a great many old install guides and provisioning scripts. Fix the account, or on 8.4 enable the plugin; on 9.x only the first is possible. ##### Authentication plugin 'caching_sha2_password' cannot be loaded The full message usually ends with a path, something like `/usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file`. The client is linked against a library from the MySQL 5.7 era (or an old MariaDB client library) that has never heard of `caching_sha2_password`, so it goes looking for a plugin file on disk and finds nothing. Creating that file does not help. Upgrade the client or driver; changing the account back to the native plugin is the wrong direction, since 9.x cannot do it at all. ##### Authentication plugin 'mysql_native_password' cannot be loaded This is the mirror image, and it has become common since MySQL 9.x clients arrived in package managers - Homebrew's `mysql` formula in particular. The 9.x client no longer ships the native plugin, so pointing it at an old 5.7 server, at a MariaDB server, or at an 8.x account still on the old plugin fails on the client side. The fixes are to move the account to `caching_sha2_password` if the server supports it, or to use an 8.4 LTS client for servers that do not (`brew install mysql-client@8.4` on a Mac). ##### Driver-specific spellings Drivers that implement the protocol themselves word the same failures differently: | Driver | Message | | --- | --- | | PHP `mysqli` / PDO, old PHP | `The server requested authentication method unknown to the client [caching_sha2_password]` | | Node.js `mysql` package | `ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server` | | Java Connector/J | `Public Key Retrieval is not allowed` | The Java one is not a version problem: Connector/J understands the plugin but refuses, by default, to fetch the RSA key over an unencrypted connection. It is the same full-authentication situation as `ERROR 2061`, covered below. #### Find the accounts still on the old plugin Before changing anything, list them. Run this as root or another account that can read the `mysql` schema: ```sql SELECT user, host, plugin, password_last_changed FROM mysql.user WHERE plugin = 'mysql_native_password' ORDER BY user, host; ``` Expect to see application accounts created by old scripts, accounts migrated from a 5.7 server by dump and load (`SHOW CREATE USER` output carries the plugin and hash with it), and sometimes `root` itself. Internal accounts such as `mysql.sys`, `mysql.session` and `mysql.infoschema` use `caching_sha2_password` on a current server and are locked; leave them alone. Remember that an account is a user and a host together, so `'app'@'%'` and `'app'@'localhost'` are separate rows and may use different plugins. An application that connects from two places may hit the problem from one of them only. `SELECT CURRENT_USER();` after logging in shows which row a session actually matched. The MySQL Shell upgrade checker reports the same thing as part of its wider scan, if you are checking an 8.0 server before an upgrade: ```bash $ mysqlsh root@db.example.net:3306 -- util check-for-server-upgrade --target-version=8.4.6 ``` #### Check the client before you move the account Moving an account to `caching_sha2_password` before its client supports it swaps one outage for another. Anything released in the last several years is fine; the problem is old pinned dependencies and old system packages. | Client or driver | Supports `caching_sha2_password` | | --- | --- | | PHP `mysqli` and PDO (mysqlnd) | Yes on PHP 7.4 and later | | Node.js `mysql2` | Yes | | Node.js `mysql` (mysqljs) | No - switch to `mysql2` | | Python PyMySQL, mysql-connector-python | Yes on current releases | | Java Connector/J | 8.0 and later | | .NET MySqlConnector, MySql.Data | Yes; MySql.Data from 8.0 | | Go `go-sql-driver/mysql` | 1.4 and later | | `mysql` CLI, `mysqldump` | 8.0 and later | A few practical notes on the rows above. **PHP.** Support was added during the 7.1 and 7.2 series, but PHP that old is long out of security support and should be upgraded anyway. On PHP 7.4 and every 8.x release, `mysqli` and `pdo_mysql` use mysqlnd and handle both plugins. A WordPress or Laravel site failing with the "unknown to the client" message is almost always running on an old PHP binary somewhere you did not expect - a cron container, a CLI on an old image - while the web server's PHP is fine. [PHP PDO and MySQL](https://www.renode.space/blog/php-pdo-and-mysql) covers the connection options. **Node.js.** The original `mysql` package has never supported `caching_sha2_password` and is effectively unmaintained. `mysql2` has a near-identical API for most code and supports both plugins plus promises; for most projects the change is the package name and an import line. ORMs such as Sequelize, Knex and TypeORM already use `mysql2` for MySQL in their current versions. **Python.** `mysqlclient` (the `MySQLdb` module) is a wrapper around the C client library it was built against, so its support depends on that library rather than on its own version. A wheel built against a MySQL 8 or later client library works; one built against an old library does not. **Desktop tools.** MySQL Workbench 8.x, DBeaver and HeidiSQL all handle the new plugin in current versions; the trouble comes from copies installed years ago and never updated. [MySQL GUI clients](https://www.renode.space/blog/mysql-gui-clients-workbench-dbeaver-heidisql) has the connection settings for each. #### Migrating an account with ALTER USER The statement that moves an account is short, and it needs the password: ```sql ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'the-current-or-a-new-password'; ``` The stored hashes of the two plugins are different functions of the password, and a SHA-1 hash cannot be converted into a SHA-256 one. MySQL has no way to switch the plugin and keep the old hash, so you supply a password - the same one the application already uses, if you know it and want no configuration change, or a new one. If nobody knows the current password, this is your chance to generate a proper one and put it where the application reads it. This statement does not need the native plugin to be enabled. On a stock 8.4 server, or on 9.x after an upgrade, root can migrate a native account directly; the account cannot log in until then, but it can be altered. To migrate many accounts, generate the statements and fill in the passwords by hand or from your secrets store: ```sql SELECT CONCAT('ALTER USER ', QUOTE(user), '@', QUOTE(host), ' IDENTIFIED WITH caching_sha2_password BY ''CHANGE_ME'';') AS stmt FROM mysql.user WHERE plugin = 'mysql_native_password'; ``` After each one, confirm the result and test a login as that account from where the application runs: ```sql SHOW CREATE USER 'app'@'%'; ``` ```bash $ mysql -h db.example.net -P 3306 -u app -p --ssl-mode=REQUIRED -e "SELECT CURRENT_USER();" ``` ##### Moving without downtime Changing the plugin and the password of a live account is a moment where running instances with the old credentials stop being able to open new connections. Existing connections stay up, since authentication only happens at connect time, but a pool that recycles a connection will fail. For a single small app with a restart a few seconds later, that is acceptable. For anything busier, there are two clean routes. 1. **A second account.** Create `'app2'@'%'` with `caching_sha2_password` and the same grants (`SHOW GRANTS FOR 'app'@'%'` gives you the statements to copy), switch the application's configuration to it, deploy, confirm nothing is still connecting as the old account, then `DROP USER 'app'@'%'`. 2. **Same account, same password, quick deploy.** If you keep the password unchanged, the only thing that changes is the plugin. Clients that already support `caching_sha2_password` reconnect without any configuration change, so the migration is invisible as long as the driver was upgraded first. The dual-password feature (`RETAIN CURRENT PASSWORD`) does not help here: MySQL does not allow it in an `ALTER USER` that changes the authentication plugin. > [!WARNING] > Migrate `root` last, from a session you keep open, and test a new root login in a second terminal before closing the first. Changing root's plugin and password and then discovering your client cannot log in with it is how people end up restarting MySQL with `--skip-grant-tables`. #### TLS, RSA keys and the first-login failure Once accounts are on `caching_sha2_password`, the remaining failures are about full authentication needing a secure channel. The clean answer is TLS: MySQL 8.x generates a self-signed certificate on first start, and the client libraries try TLS by default (`--ssl-mode=PREFERRED`). A client that has TLS disabled, or a library that does not do TLS by default, falls back to RSA, and some refuse to fetch the key unless told to. | Client | Setting that allows full auth without TLS | | --- | --- | | `mysql` CLI | `--get-server-public-key` | | Connector/J | `allowPublicKeyRetrieval=true` | | MySqlConnector (.NET) | `AllowPublicKeyRetrieval=true` | Retrieving the public key over an unencrypted connection is open to a man-in-the-middle substituting their own key, which is why it is off by default. It is a reasonable compromise on a private network; across the internet, use TLS instead and leave key retrieval off. A JDBC URL that does it properly looks like this: ```text jdbc:mysql://db.example.net:3306/appdb?sslMode=REQUIRED ``` The Unix socket counts as secure, so local connections through `localhost` on the same machine never hit this. Remote connections are where it matters, and [MySQL remote connections](https://www.renode.space/blog/mysql-remote-connections-getting-started) covers the network side - firewalls, bind addresses and certificate checking. #### Turning mysql_native_password back on in 8.4 When an application cannot be fixed today - a vendor binary with an old client library inside it, say - 8.4 lets you load the plugin again. It is a server start-up option: my.cnf: ```ini [mysqld] mysql_native_password=ON ``` The command-line form is `--mysql-native-password=ON`, which is how it goes in a Docker Compose `command:` line. Restart the server and check that it took: ```sql SELECT plugin_name, plugin_status FROM information_schema.plugins WHERE plugin_name = 'mysql_native_password'; ``` An `ACTIVE` status means accounts on the old plugin can log in again. A row with another status, or no row at all, means the option was not read - check which configuration files the server actually loads with `mysqld --verbose --help | grep -A1 "Default options"`. Two traps catch people here. The first is the 8.0-era setting `default_authentication_plugin=mysql_native_password`, which many old Compose files and `my.cnf` snippets carry. That variable was removed in 8.4, and the server refuses to start with it in the configuration, reporting an unknown variable in the error log. Delete it; if you really need native as the default for new accounts, the 8.4 equivalent is `authentication_policy`, but there is little reason to create new native accounts on a server where the plugin has no future. The second trap is expecting this option to work on 9.x. It does not exist there, and an unknown option prevents start-up just the same. On a hosted database the server's configuration belongs to the provider, so whether you can set this at all depends on them. Plan on migrating the accounts instead; it takes minutes and does not need anyone's permission. > [!NOTE] > Enabling the plugin is a delay, not a decision. 8.4 is the last version where it can be switched on, and every month spent on it is a month in which the client library that needs it gets older. #### Upgrading from 8.4 to 9.x MySQL 9.x Innovation releases and the next LTS have no native plugin at all. An upgrade with native accounts still present succeeds - the server starts and the data is intact - but those accounts are unusable until someone alters them, and the error is the same `Plugin 'mysql_native_password' is not loaded`. If `root` is among them, that someone has to get in another way, which is the restart-with-`--skip-grant-tables` scenario you want to avoid. So the order is fixed: upgrade drivers, migrate accounts on 8.4, confirm the query in the previous section returns no rows, and only then think about 9.x. For a production database there is little reason to run Innovation releases at all; 8.4 is supported for years yet. The point is that the migration is the same work whether you do it now or under pressure later. When moving a database between servers with a logical dump, the same applies to the accounts you carry over. A `SHOW CREATE USER` statement copied from an old server includes `IDENTIFIED WITH 'mysql_native_password' AS '*...'`, and replaying it on 8.4 fails or recreates the problem. Recreate the accounts with `caching_sha2_password` on the new server instead - [migrating MySQL to a new host](https://www.renode.space/blog/migrating-mysql-to-a-new-host) walks through that move. RE:NODE's [MySQL plans](https://www.renode.space/database-hosting#mysql) run 8.4 LTS, and the root account, application database and application user are generated on the current default plugin, so the accounts you start with need no migration; the ones you bring from an older server might. #### FAQ ##### Is caching_sha2_password slower than mysql_native_password? Only on full authentication, and only noticeably for workloads that open a new connection for every request with an empty cache. After the first login the account is cached and fast authentication costs about the same as the old plugin. A connection pool, which most applications use anyway, makes the difference disappear; [connection limits and pooling](https://www.renode.space/blog/mysql-connection-limits-and-pooling) explains how to size one. ##### Can I switch a user's plugin without knowing the password? No. The plugin and the hash go together, and MySQL cannot derive a SHA-256 hash from a stored SHA-1 one. You need to set a password in the same `ALTER USER` statement. If the old password is lost, set a new one and update the application's configuration. ##### Why does the login work from my laptop but not from the server? Usually because the two machines run different client libraries. The laptop has a current `mysql` CLI or GUI tool; the server's application uses an old driver baked into an image or a lock file. Compare the exact error text with the table above - it tells you whether the server or the client is refusing. ##### Does MariaDB have the same change? No, and they should not be treated as interchangeable here. MariaDB still uses `mysql_native_password` and its own plugins, and does not implement MySQL's `caching_sha2_password` on the server. That is also why a MySQL 9.x client cannot log in to many MariaDB servers: it has dropped the only plugin they share. ##### Do I need FLUSH PRIVILEGES after ALTER USER? No. `ALTER USER` updates the in-memory grant and authentication caches immediately. Running `FLUSH PRIVILEGES` anyway also clears the `caching_sha2_password` cache, which forces every account through full authentication on its next login - harmless with TLS, and a source of surprise failures for clients without it. MySQL on RE:NODE runs 8.4 LTS, with a root password, an application database and an application user generated for each server and reached on the plan's host and port. Plans start at 1 GB of memory and 10 GB of NVMe. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### PDO prepared statements and error handling in PHP Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/pdo-prepared-statements-and-error-handling Most PDO bugs are not about connecting. They are about the space between `prepare()` and the row you get back: a placeholder array with one key too many, a boolean that arrives at MySQL as an empty string, a `bindParam()` inside a loop that binds every row to the last value, an `IN ()` list built by string concatenation, a `lastInsertId()` read one statement too late, and a `catch` block that compares the wrong error code. Each of those has a short, exact rule. This post is the reference for them: how placeholders are matched, how values are typed, how errors are reported and what each common SQLSTATE means, which fetch mode to use for which shape of data, and how transactions, insert IDs and row counts really behave on MySQL. It assumes a working connection. The DSN, `charset=utf8mb4`, the three options worth setting, the `localhost` socket trap and TLS are covered in [PHP PDO and MySQL](https://www.renode.space/blog/php-pdo-and-mysql); the examples here use a `$pdo` created that way, with `ERRMODE_EXCEPTION`, `FETCH_ASSOC` as the default fetch mode and `ATTR_EMULATE_PREPARES` set to `false`. #### Positional or named placeholders PDO accepts two placeholder styles. Positional placeholders are a bare `?`, matched to values by order. Named placeholders are a colon followed by a name, `:email`, matched by name. Both produce the same prepared statement; the choice is about what reads better and what is easier to keep correct as the query grows. ```php // Positional: values in the order the ? appear $stmt = $pdo->prepare('SELECT id FROM users WHERE email = ? AND status = ?'); $stmt->execute([$email, 'active']); // Named: keys match names, order does not matter $stmt = $pdo->prepare('SELECT id FROM users WHERE email = :email AND status = :status'); $stmt->execute(['status' => 'active', 'email' => $email]); ``` Positional is fine for two or three values. Past that, named placeholders win, because inserting a new condition in the middle of a positional query silently shifts every later value by one - and if the types happen to be compatible, MySQL runs the wrong query without complaint. The rules that trip people up: - **You cannot mix the two styles in one statement.** `WHERE a = ? AND b = :b` fails with `SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters`. - **The colon in the array key is optional.** `['email' => $x]` and `[':email' => $x]` both work. Pick one and keep it, because a codebase that uses both makes searching harder. - **The count must match exactly.** Passing a key the query does not use is not ignored - it is `HY093: Invalid parameter number: number of bound variables does not match number of tokens`. That catches people who pass a whole `$_POST` array or a reused parameter array to a query that needs only part of it. - **Positional arrays must be lists.** After `array_filter()` or `unset()` the keys are no longer `0, 1, 2`, and `execute()` cannot match them. Wrap the array in `array_values()` first. - **A named placeholder can appear once** when prepares are native. `WHERE title LIKE :q OR body LIKE :q` works under emulation and fails natively with `HY093`. Use `:q1` and `:q2` and pass the value twice. - **Placeholders hold values only.** Table names, column names, `ASC`/`DESC` and SQL keywords cannot be bound; they come from an allow-list in your code. A placeholder also cannot sit inside a quoted string. `LIKE '%:q%'` is a literal string containing the characters `:q`, not a placeholder. Build the pattern in PHP and bind the whole thing: `LIKE :q` with `'%' . $term . '%'`. If the search term can contain `%` or `_` and they should match literally, escape them first with `addcslashes($term, '%_\\')`, because both are wildcards inside `LIKE` and a placeholder does not change that. #### bindValue, bindParam and value types `execute($array)` is the short form. It binds every value as `PDO::PARAM_STR`, except `null`, which is sent as SQL `NULL`. MySQL converts strings to numbers when a column or comparison needs it, so for most queries this is exactly right. The long form is binding values one at a time, which is how you control the type. There are two binding methods and they behave differently: | Method | Binds | Value read | | --- | --- | --- | | `bindValue()` | A copy of the value | When you call it | | `bindParam()` | A reference to the variable | When `execute()` runs | `bindParam()` exists so that one prepared statement can be executed repeatedly while you change the variable between runs. That reference behaviour is also its classic bug: ```php // Wrong: every placeholder ends up bound to the last $value foreach ($params as $key => $value) { $stmt->bindParam($key, $value); } // Right: bindValue copies the value now foreach ($params as $key => $value) { $stmt->bindValue($key, $value); } ``` In the first loop each `bindParam()` call binds the same variable, `$value`, which by the time `execute()` runs holds whatever the last iteration left in it. Use `bindValue()` unless you specifically want the reference. The type constants that matter on MySQL: | Constant | Use for | | --- | --- | | `PDO::PARAM_STR` | Strings, decimals, dates - the default | | `PDO::PARAM_INT` | Integers, especially `LIMIT` and `OFFSET` | | `PDO::PARAM_NULL` | An explicit `NULL` | | `PDO::PARAM_BOOL` | Booleans - but see below | | `PDO::PARAM_LOB` | Large binary data, including a stream resource | Positional placeholders are numbered from 1 when you bind them individually: `$stmt->bindValue(1, $id, PDO::PARAM_INT)`. Named ones take the name, with or without the colon. ##### Booleans, NULL and money Three values cause most type surprises. A PHP `false` passed through `execute()` is converted to a string, and `false` as a string is `''`. MySQL's default `sql_mode` in 8.0 and 8.4 includes `STRICT_TRANS_TABLES`, so inserting `''` into a `TINYINT` column fails with error 1366, `Incorrect integer value: '' for column 'is_active'`. Without strict mode the same insert succeeds with a warning and stores `0`, which hides the bug until someone turns strict mode on. Cast booleans to integers before binding - `(int) $isActive` - and the problem disappears in both modes. `NULL` binds correctly, but it does not compare the way people expect. `WHERE deleted_at = ?` with a `null` value matches no rows at all, because in SQL `NULL = NULL` is not true. To match a value that may be `NULL`, use MySQL's null-safe operator: `WHERE deleted_at <=> ?`. It treats two `NULL`s as equal and otherwise behaves like `=`. Money and other exact decimals should be bound as strings, not floats. `'19.99'` reaches a `DECIMAL(10,2)` column exactly; `19.99` as a PHP float is a binary approximation that MySQL then rounds. The same applies on the way back out: `DECIMAL` columns are returned as strings even with native prepares, by design, so the value is not damaged by float conversion. #### IN() lists done properly There is no way to bind an array to a single placeholder. `WHERE id IN (?)` with `[1, 2, 3]` does not work, and imploding the values into the SQL string undoes the point of preparing. The answer is one placeholder per value, generated from the array. With positional placeholders: ```php if ($ids === []) { return []; // IN () is a syntax error; an empty list matches nothing anyway } $marks = implode(',', array_fill(0, count($ids), '?')); $stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks)"); $stmt->execute(array_values($ids)); ``` When the same query has other conditions, positional placeholders make you merge the arrays in exactly the order the `?` marks appear, which is fragile. Named placeholders keep it readable: ```php $params = ['status' => 'active']; $names = []; foreach (array_values($ids) as $i => $id) { $names[] = ":id$i"; $params["id$i"] = (int) $id; } $sql = 'SELECT id, name FROM products WHERE status = :status AND id IN (' . implode(', ', $names) . ')'; $stmt = $pdo->prepare($sql); $stmt->execute($params); ``` The generated names come from a loop counter, never from the input, so nothing user-supplied enters the SQL text. ##### Very long lists Two ceilings apply. MySQL's protocol allows at most 65,535 placeholders in one prepared statement; past that, `prepare()` fails with error 1390, `Prepared statement contains too many placeholders`. Long before that, a query with tens of thousands of values becomes slow to parse and plan, and the whole packet must fit inside `max_allowed_packet` (64 MB by default in MySQL 8). In practice, chunk the list at 500 to 1,000 values and run the query once per chunk: ```php $rows = []; foreach (array_chunk($ids, 1000) as $chunk) { $marks = implode(',', array_fill(0, count($chunk), '?')); $stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks)"); $stmt->execute($chunk); array_push($rows, ...$stmt->fetchAll()); } ``` An alternative on MySQL 8 is to send the list as one JSON string and unpack it on the server with `JSON_TABLE`, which needs only a single placeholder whatever the list length: ```sql SELECT p.id, p.name FROM products p JOIN JSON_TABLE(?, '$[*]' COLUMNS (id INT PATH '$')) AS j ON j.id = p.id; ``` Bind `json_encode(array_values($ids))` to it. The statement text never changes, which helps when the same query runs with lists of different lengths. Avoid `FIND_IN_SET(id, ?)` with a comma-separated string: it works, but it cannot use an index and scans the table. #### What native prepares change about errors `ATTR_EMULATE_PREPARES => false` is the recommended setting, and [the connection post](https://www.renode.space/blog/php-pdo-and-mysql) explains why: real server-side statements, native integer and float types, and errors that arrive early. It also changes where and how errors appear, which matters for error handling. - **Syntax errors arrive at `prepare()`.** With native prepares, `prepare()` sends the SQL to MySQL, which parses it and rejects a typo immediately with SQLSTATE `42000`, error 1064. Under emulation, `prepare()` only parses the placeholders locally and the same typo surfaces at `execute()`. If your `try` block wraps only `execute()`, native mode moves the exception outside it. - **Unknown tables and columns arrive at `prepare()` too** - `42S02` and `42S22` - for the same reason. - **One statement per prepare.** A native prepared statement holds exactly one SQL statement. `prepare('UPDATE a SET x = 1; UPDATE b SET y = 2')` is a syntax error. Under emulation the MySQL driver accepts multiple statements by default, which is convenient for migration scripts and a gift to anyone who finds an injection point. Setting `PDO::MYSQL_ATTR_MULTI_STATEMENTS => false` in the connection options closes that off even when emulation is on (on PHP 8.4 and later, the constant is `Pdo\Mysql::ATTR_MULTI_STATEMENTS`). - **Statements are server resources.** Each native prepare creates a statement on the server that lives until the `PDOStatement` object is destroyed. MySQL caps the total across all connections with `max_prepared_stmt_count`, default 16,382. A long-running worker that calls `prepare()` inside a loop and keeps every statement object in an array can hit error 1461, `Can't create more than max_prepared_stmt_count statements`. Prepare once outside the loop and execute many times, or let the objects go out of scope. > [!NOTE] Whichever mode you run, run the same one in development and production. A query that works emulated and fails natively - a repeated named placeholder, a quoted `LIMIT` - should fail on your machine first. #### Error modes and what a PDOException carries PDO has three error modes, set with `PDO::ATTR_ERRMODE`: | Mode | On error | | --- | --- | | `ERRMODE_SILENT` | Returns `false`; you check `errorInfo()` | | `ERRMODE_WARNING` | Raises `E_WARNING` and returns `false` | | `ERRMODE_EXCEPTION` | Throws `PDOException` - the default since PHP 8.0 | The constructor is the exception: `new PDO(...)` throws a `PDOException` on a failed connection whatever the error mode, because there is no object to call `errorInfo()` on. Everything after construction follows the mode. Silent mode is the source of the most expensive PDO bugs, because a failed `execute()` returns `false`, the code carries on, and the result is a missing order line rather than an error. In silent mode, errors also live in two places: a failed `prepare()` is recorded on the `PDO` object (`$pdo->errorInfo()`), and a failed `execute()` on the statement (`$stmt->errorInfo()`). Code that checks the wrong one sees `00000` - success. If you inherit silent-mode code, switching it to exceptions is usually the single best fix available. A `PDOException` carries the error in three forms: ```php try { $stmt->execute($params); } catch (PDOException $e) { $sqlstate = $e->getCode(); // '23000' - a string, not an int $driverCode = $e->errorInfo[1] ?? null; // 1062 - the MySQL error number $driverMsg = $e->errorInfo[2] ?? null; // "Duplicate entry 'a@b.c' for key ..." } ``` `getCode()` returns the five-character SQLSTATE as a string, which is why `PDOException` is one of the few exceptions where `getCode()` is not an integer; a strict comparison with `23000` as a number never matches. `errorInfo` is an array of SQLSTATE, driver error number and driver message. For failed connections, use the message and `getCode()`, which holds the MySQL number such as 1045 or 2002; do not rely on `errorInfo` being filled there. Branch on the MySQL number where you can. SQLSTATE is a category, and some categories are crowded: `23000` covers duplicates, foreign key failures and `NULL` in a `NOT NULL` column, and `HY000` covers dozens of unrelated errors. ##### Catch narrowly, log fully Catch a `PDOException` only where you can do something specific with it - turn a duplicate email into a form error, retry a deadlock - and rethrow everything else. Let unexpected database errors reach one handler at the top of the application that logs the full message and shows the visitor a generic page. The message can contain host names, user names and fragments of the query and data, so it belongs in the log, never in the response. [The database security checklist](https://www.renode.space/blog/database-security-checklist) covers the rest of what should not leak. #### SQLSTATE and MySQL error reference The errors you will actually meet from queries, as opposed to connections (those are in [the connection post](https://www.renode.space/blog/php-pdo-and-mysql)). First, constraint and data errors: | SQLSTATE / MySQL | Message starts | Usual cause | | --- | --- | --- | | `23000` / 1062 | `Duplicate entry` | Unique key collision | | `23000` / 1452 | `Cannot add or update a child row` | Foreign key target missing | | `23000` / 1451 | `Cannot delete or update a parent row` | Rows still reference it | | `23000` / 1048 | `Column 'x' cannot be null` | `NULL` into `NOT NULL` | | `22001` / 1406 | `Data too long for column` | String longer than the column | | `22003` / 1264 | `Out of range value` | Number too big for the type | | `22007` / 1292 | `Incorrect datetime value` | Bad date format, or `''` | | `HY000` / 1366 | `Incorrect integer value` | `''` from a PHP `false`, or bad text | Then statement and runtime errors: | SQLSTATE / MySQL | Message starts | Usual cause | | --- | --- | --- | | `42000` / 1064 | `You have an error in your SQL syntax` | Typo, or a reserved word as a name | | `42S02` / 1146 | `Table ... doesn't exist` | Wrong database or a missing migration | | `42S22` / 1054 | `Unknown column` | Typo, or a column added after deploy | | `21S01` / 1136 | `Column count doesn't match` | `INSERT` columns and values differ | | `HY093` | `Invalid parameter number` | Placeholders and values do not match | | `40001` / 1213 | `Deadlock found` | Whole transaction rolled back - retry | | `HY000` / 1205 | `Lock wait timeout exceeded` | Waited 50 s for a row lock | | `HY000` / 2014 | `Cannot execute queries while other unbuffered queries are active` | An open cursor | Three of these deserve a word. `HY093` has no MySQL number, because it is raised by PDO before anything reaches the server; it is always a mismatch between placeholders and the values supplied, and the cases are listed in the placeholder section above. `22001` and `22003` only appear in strict mode - without `STRICT_TRANS_TABLES`, MySQL truncates the string or clamps the number, stores it and issues a warning nobody reads. And `1064` on a query that looks correct is often a column called `order`, `key`, `rank` or `groups`, all reserved words that need backticks. The 50 seconds for 1205 is the default `innodb_lock_wait_timeout`. Deadlocks and lock waits are covered with a retry wrapper in [MySQL transactions, locking and deadlocks](https://www.renode.space/blog/mysql-transactions-locking-and-deadlocks). #### Fetch modes beyond FETCH_ASSOC `FETCH_ASSOC` as the default suits most code. The other modes save a loop each time the shape you need is not "list of rows". Pass them to `fetch()` or `fetchAll()`, or set them on a statement with `$stmt->setFetchMode()`. | Mode | Shape returned | | --- | --- | | `FETCH_NUM` | Rows indexed `0, 1, 2` | | `FETCH_OBJ` | Rows as `stdClass` objects | | `FETCH_COLUMN` | A flat list of the first column | | `FETCH_KEY_PAIR` | `[first => second]` from two columns | | `FETCH_UNIQUE` | Rows keyed by the first column | | `FETCH_GROUP` | Lists of rows grouped by the first column | | `FETCH_CLASS` | Rows hydrated into a class | `FETCH_UNIQUE` is the one people miss. `SELECT id, name, price FROM products` with `fetchAll(PDO::FETCH_UNIQUE)` returns `[17 => ['name' => ..., 'price' => ...], ...]`, keyed by ID, which is exactly the lookup table you would otherwise build with a `foreach`. If the first column is not unique, later rows overwrite earlier ones without warning. `FETCH_GROUP` can be combined with `FETCH_COLUMN`: `SELECT category, name FROM products` with `fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN)` returns `['tools' => ['saw', 'drill'], 'paint' => [...]]`. ##### Hydrating classes `FETCH_CLASS` creates an object per row and assigns columns to properties of the same name: ```php final class Product { public int $id; public string $name; public string $price; public function __construct() { /* runs after properties are set */ } } $stmt = $pdo->query('SELECT id, name, price FROM products'); $products = $stmt->fetchAll(PDO::FETCH_CLASS, Product::class); ``` Two behaviours surprise people. By default PDO sets the properties before calling the constructor, so a constructor that assigns defaults overwrites the data it was given. Add `PDO::FETCH_PROPS_LATE` (`FETCH_CLASS | FETCH_PROPS_LATE`) to run the constructor first. And a column with no matching property is still assigned, as a dynamic property, which raises a deprecation notice since PHP 8.2 unless the class allows dynamic properties. Select only the columns the class declares, or alias them to match. Typed properties interact with the fetch types: under native prepares an `INT` column arrives as an `int` and fits an `int` property; a `DECIMAL` arrives as a string, which is why `$price` is declared `string` above. ##### Empty results `fetch()` and `fetchColumn()` return `false` when there are no more rows; `fetchAll()` returns an empty array. That makes `if ($row = $stmt->fetch())` the right existence check, and it means a `fetchColumn()` on a `COUNT(*)` query is safe - a count always returns one row. Beware `fetchColumn()` on a column that can legitimately hold `false`-like values: a `0` or `'0'` is returned as itself, but code that tests the result with `if (!$value)` treats it as "no row". #### Transactions, savepoints and retries The basic pattern - `beginTransaction()`, the work, `commit()`, and `rollBack()` in a `catch` - is in [the connection post](https://www.renode.space/blog/php-pdo-and-mysql). The edges are where code goes wrong. PDO does not nest transactions. Calling `beginTransaction()` while one is active throws `There is already an active transaction`. Code that calls a helper which opens its own transaction, from inside another transaction, needs either a wrapper that tracks depth or savepoints, which MySQL supports with plain SQL: ```php function transactional(PDO $pdo, callable $work): mixed { static $depth = 0; $outer = $depth === 0; $outer ? $pdo->beginTransaction() : $pdo->exec("SAVEPOINT sp$depth"); $depth++; try { $result = $work($pdo); $depth--; $outer ? $pdo->commit() : $pdo->exec("RELEASE SAVEPOINT sp$depth"); return $result; } catch (Throwable $e) { $depth--; if ($outer) { if ($pdo->inTransaction()) { $pdo->rollBack(); } } else { $pdo->exec("ROLLBACK TO SAVEPOINT sp$depth"); } throw $e; } } ``` An inner failure rolls back only to its savepoint; the outer transaction decides whether to continue. Laravel's `DB::transaction()` works the same way, which is why nesting there does not throw. Things that end a transaction without asking: - **DDL.** `CREATE`, `ALTER`, `DROP` and `TRUNCATE` commit implicitly in MySQL. The changes before them are committed and cannot be rolled back, and on PHP 8 the later `commit()` throws `There is no active transaction`. - **A deadlock.** Error 1213 rolls back the whole transaction on the server. Retrying the last statement is wrong; retry the whole unit from `beginTransaction()`. - **A lost connection.** Error 2006 or 2013 mid-transaction means the server rolled back. The PDO object is no longer usable. Lock wait timeouts (1205) are the odd one: by default they roll back only the statement that timed out, leaving the transaction open. Roll back explicitly before you retry, or you will commit half the work. Keep transactions short and keep slow things out of them. A transaction that holds row locks while it calls a payment API or sends an email makes every other request touching those rows wait - and wait timeouts follow. #### lastInsertId and rowCount edge cases `$pdo->lastInsertId()` returns the `AUTO_INCREMENT` value generated by the most recent statement on this connection, as a string. It is per connection, so concurrent requests never see each other's IDs. The edges: - **Read it immediately.** A later statement that does not generate an ID can reset the value to `0`. Call it right after the `INSERT`, before any other query on that connection. - **Multi-row inserts return the first ID.** `INSERT INTO tags (name) VALUES (?), (?), (?)` returns the ID of the first row. Do not compute the others by adding 1 and 2; depending on `innodb_autoinc_lock_mode` and concurrent inserts, that assumption is not guaranteed. If you need every ID, insert row by row inside a transaction, or read them back by a unique key. - **Tables without `AUTO_INCREMENT`** - UUID keys, natural keys - return `'0'`. Generate the key in PHP and you already have it. - **`INSERT IGNORE` that skipped the row** generates no ID. Check `rowCount()` to know whether anything was inserted. `INSERT ... ON DUPLICATE KEY UPDATE` needs care for both. MySQL reports `rowCount()` as 1 for a new row, 2 for an updated existing row and 0 for an existing row that already had those values. And when the row already existed, `lastInsertId()` does not give you its ID - unless you use MySQL's documented trick of passing the ID through `LAST_INSERT_ID(expr)`: ```sql INSERT INTO users (email, name) VALUES (:email, :name) ON DUPLICATE KEY UPDATE name = VALUES(name), id = LAST_INSERT_ID(id); ``` Now `lastInsertId()` returns the row's ID in both cases. MySQL 8.0.20 deprecated `VALUES(col)` in this clause in favour of a row alias (`VALUES (...) AS new ... UPDATE name = new.name`); `VALUES()` still works in 8.4 but raises a warning, so new code should use the alias form. For `UPDATE`, `rowCount()` counts rows changed, not rows matched, so updating a row to its current values returns 0. Setting `PDO::MYSQL_ATTR_FOUND_ROWS => true` at connection time switches it to matched rows. For `SELECT`, do not use `rowCount()` at all: the PHP manual warns that its result for a `SELECT` is not portable. Count with `COUNT(*)`, or count the array you fetched. #### Where this runs on RE:NODE None of the above depends on the host, but a few facts help if you run it here. MySQL on [database hosting](https://www.renode.space/database-hosting#mysql) is MySQL 8.4 LTS, so `JSON_TABLE`, the row-alias form of `ON DUPLICATE KEY UPDATE` and the 8.x defaults described in this post all apply; the root password, an application database and an application user are generated when the server is created. The [Laravel / PHP app line](https://www.renode.space/app-hosting#laravel-php) runs PHP 8.3, 8.4 or 8.5 on FrankenPHP, which matters for the `Pdo\Mysql` constant names, and app and web plans include database slots created in the panel with a generated host, user and password. "Open in phpMyAdmin" on a slot is the quickest way to run `SHOW CREATE TABLE` when you need to see which column is still not `utf8mb4` or which key a 1062 refers to. #### FAQ ##### Do prepared statements make a query completely safe from SQL injection? They make values safe. Anything concatenated into the SQL text - a column name for sorting, a table name, a direction keyword, a `LIMIT` built from a string - is outside the protection. Map those onto a fixed allow-list in code. The other gap is stored data reused later: a value safely inserted with a placeholder is still untrusted when you read it back and must go through a placeholder again. ##### Is it worth preparing a statement I only run once? Yes, for safety. The cost of a native prepare is one extra round trip to the server, which on a nearby database is well under a millisecond. Code that switches between `query()` and `prepare()` depending on whether a value is involved eventually gets it wrong; preparing everything that takes input removes the decision. ##### Why does execute() return true but nothing changes? Either the `WHERE` matched no rows - `rowCount()` will be 0 - or the work ran inside a transaction that was never committed. With autocommit off, or after a `beginTransaction()` with no `commit()`, the changes are rolled back when the connection closes at the end of the request. Check `$pdo->inTransaction()` at the end of the script while debugging. ##### Can I reuse one PDOStatement with different values? Yes, and it is the intended use. Prepare once, then call `execute()` with a new array as many times as you like; with native prepares the server parses the SQL only once. If a previous `SELECT` on the statement left rows unread, call `closeCursor()` before executing it again. ##### How do I log the SQL with the values filled in? PDO does not produce the final query string with native prepares, because the server receives the SQL and values separately. `$stmt->debugDumpParams()` prints the SQL and each bound parameter, which is usually enough. For a complete record of what the server ran, use MySQL's general query log on a development machine, or the [slow query log](https://www.renode.space/blog/mysql-slow-query-log) in production for the queries that matter. MySQL 8.4 LTS on RE:NODE is created with a root password, an application database and an application user already generated, so a PDO connection needs only the host, port and those credentials. It is reached on the plan's host and port from any PHP host you run. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### PostgreSQL sslmode explained: prefer, require, verify-full Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/postgresql-sslmode-explained `sslmode` decides two separate things: whether the connection to PostgreSQL is encrypted at all, and whether the client checks that the server on the other end is the one it meant to reach. Only two of the six values answer yes to both - `verify-ca` and `verify-full` - and only `verify-full` also checks the host name. The libpq default, `prefer`, answers "maybe" to the first and "no" to the second, and it does so silently. If the connection crosses any network you do not own, set `sslmode=verify-full`, give the client the right root certificate in `sslrootcert`, and make the server refuse anything that is not TLS with `hostssl` lines in `pg_hba.conf`. The rest of this post is what each mode does on the wire, where the certificate files go, how to turn TLS on at the server, what each driver does with the same setting (they do not agree), and how to read the errors. The basics of connecting from elsewhere - connection strings, `pg_hba.conf` order, `psql` commands - are in [PostgreSQL remote connections](https://www.renode.space/blog/postgresql-remote-connections-getting-started); this post goes deeper on the TLS part only. #### The six modes, and what each one checks PostgreSQL starts a TLS connection in an unusual way. The client opens a plain TCP connection to port `5432` and sends a small `SSLRequest` packet asking whether the server will speak TLS. The server answers with one byte: `S` for yes, `N` for no. Everything `sslmode` does happens around that byte - what the client does when it gets `N`, and how much it checks once the handshake starts. | Mode | Encryption | What happens if the server says no | | --- | --- | --- | | `disable` | Never | Plain connection, no request sent | | `allow` | Only if the server forces it | Plain first, TLS only on refusal | | `prefer` | If the server offers it | Falls back to plain silently | | `require` | Always | Connection fails | | `verify-ca` | Always | Connection fails | | `verify-full` | Always | Connection fails | And the second question, which is the one that matters against an attacker: | Mode | Certificate chain checked | Host name checked | | --- | --- | --- | | `disable`, `allow`, `prefer` | No | No | | `require` | No (see the note below) | No | | `verify-ca` | Yes, against `sslrootcert` | No | | `verify-full` | Yes, against `sslrootcert` | Yes | In more detail: - **`disable`** never sends the TLS request. Only sensible on a Unix socket or a loopback connection on the same machine, where there is no network to protect. - **`allow`** tries a plain connection first, and only retries with TLS if the server rejects the plain one - typically because `pg_hba.conf` only has `hostssl` lines for you. It exists for compatibility and almost nobody should choose it. - **`prefer`** asks for TLS, uses it if the server says yes, and quietly carries on in plain text if the server says no or the handshake fails. It checks nothing about the certificate. - **`require`** insists on encryption and fails if it cannot get it, but accepts any certificate the server presents, including a self-signed one made by somebody in the middle. - **`verify-ca`** insists on encryption and checks that the server certificate chains up to a CA in the root file you trust. - **`verify-full`** does all of that and checks that the name you connected to (the `host` parameter) matches the certificate's Subject Alternative Name, or its Common Name if there is no SAN. > [!NOTE] > A legacy quirk: if a root certificate file exists at the default location (`~/.postgresql/root.crt`) or is named by `sslrootcert`, libpq treats `require` exactly like `verify-ca`. This is kept for backwards compatibility with old clients, and it explains why the same `require` string works on one machine and fails with a certificate error on another. #### Why the default, prefer, is weak `prefer` encrypts when it can, so it feels safe. The problem is that the decision to encrypt is made in clear text, before any certificate is involved. Anyone who can tamper with the traffic between you and the database - a hostile Wi-Fi network, a compromised router, a misconfigured proxy - can answer the `SSLRequest` with `N` on the server's behalf. The client shrugs and sends the authentication exchange and every query in plain text. Nothing is logged on the client side and nothing errors. Even when `prefer` does get a TLS session, it never looks at whose certificate it received, so an attacker can just as easily terminate TLS themselves and open a second connection to the real server. Encryption against a party you have not identified protects you from people passively capturing traffic and nobody else. That is not a theoretical concern for the password, either. With `scram-sha-256` authentication the password itself never crosses the wire, which helps, but the session that follows - every row you read and write - does. And with the older `md5` method or `password`, a downgraded connection hands over credentials that can be replayed or cracked. Why is the default still `prefer`? Because it makes connections work out of the box against servers with and without TLS, and changing it would break a vast number of existing setups. It is a compatibility choice, not a security recommendation, and the PostgreSQL documentation says as much. The fix is simple: never rely on it. Put the mode in every connection string, or in `PGSSLMODE`, so the behaviour is written down rather than inherited. The rough rule: | Situation | Mode to use | | --- | --- | | Unix socket or same-machine loopback | `disable` or leave the default | | Private network you control end to end | `require` at minimum | | Anything over the internet | `verify-full` | | Server certificate from a public CA, PG 16+ client | `verify-full` with `sslrootcert=system` | #### Setting sslmode: URI, keywords and PGSSLMODE There are three places a libpq-based client picks the mode up, and an explicit value always beats a fallback. ```bash # URI form - parameters go after the ? $ psql "postgresql://appuser@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=/etc/ssl/pg/root.crt" # Keyword form - easier to read in scripts $ psql "host=db.example.com dbname=shop user=appuser sslmode=verify-full sslrootcert=/etc/ssl/pg/root.crt" # Environment variables - used when the string leaves them out $ export PGSSLMODE=verify-full $ export PGSSLROOTCERT=/etc/ssl/pg/root.crt $ psql -h db.example.com -U appuser -d shop ``` The environment variables that go with TLS are `PGSSLMODE`, `PGSSLROOTCERT`, `PGSSLCERT`, `PGSSLKEY` and `PGSSLCRL`. They are read by `psql`, `pg_dump`, `pg_restore`, psycopg and everything else built on libpq. They are a convenient way to make a whole machine or container strict without editing every script, and a confusing one when a forgotten `PGSSLMODE=disable` in a shell profile makes a connection behave differently from the same string elsewhere. When behaviour differs between two machines, `env | grep ^PG` is the first thing to run. There is also an older `PGREQUIRESSL` variable, deprecated in favour of `PGSSLMODE`; if both are set, `PGSSLMODE` wins. In a URI, the parameter values are percent-decoded like everything else. A Windows path such as `C:\certs\root.crt` survives as it is, but a path containing `&`, `#` or `?` needs escaping, and spaces should be `%20`. The keyword form avoids all of that. To confirm what you actually got, look at the banner when `psql` connects. A TLS session prints a line similar to: ```text SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) ``` Newer clients add more fields, such as the ALPN protocol on PostgreSQL 17. No such line means no TLS. `\conninfo` inside `psql` says the same thing, and from any client you can ask the server directly: ```sql SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid(); ``` Drop the `WHERE` clause and join on `pg_stat_activity` (as a role allowed to see other sessions) to check every connection at once - a quick way to find the one service still connecting in clear text. #### The certificate files: sslrootcert, sslcert, sslkey Three client-side files are involved, and libpq has a default location for each: | Parameter | Default on Linux and macOS | Purpose | | --- | --- | --- | | `sslrootcert` | `~/.postgresql/root.crt` | CA that signed the server certificate | | `sslcert` | `~/.postgresql/postgresql.crt` | Client certificate, if the server asks | | `sslkey` | `~/.postgresql/postgresql.key` | Private key for the client certificate | On Windows the same names live under `%APPDATA%\postgresql\`. A revocation list can be given with `sslcrl` (default `root.crl`) if you run your own CA and revoke certificates. `sslrootcert` is the one everybody needs for `verify-ca` and `verify-full`. What goes in it depends on who signed the server's certificate: - **Your own CA, or a host's private CA.** The CA certificate file, in PEM format. If the server uses a single self-signed certificate with no CA, that certificate is its own root and goes in this file. - **A public CA such as Let's Encrypt.** On PostgreSQL 16 and later clients, `sslrootcert=system` uses the operating system's trust store. Setting it also changes the default mode to `verify-full` and makes weaker modes an error, which is deliberate: trusting every public CA without checking the host name would let anybody with any public certificate impersonate your database. On older clients, point `sslrootcert` at the system bundle file, for example `/etc/ssl/certs/ca-certificates.crt` on Debian and Ubuntu. `sslcert` and `sslkey` matter only when the server is configured to ask for a client certificate - either as the login method (`cert`) or as an extra check on top of a password. The key file must not be readable by anyone else: libpq refuses a key with group or world permissions on Unix, so `chmod 0600 ~/.postgresql/postgresql.key` is part of the setup. PostgreSQL 16 added `sslcertmode` (`disable`, `allow`, `require`) to control whether the client sends a certificate at all. PostgreSQL 17 added one more connection option worth knowing about: `sslnegotiation=direct` skips the `SSLRequest` exchange and starts the TLS handshake immediately, saving a round trip. It needs a PostgreSQL 17 server and `sslmode` of `require` or stronger. The default, `postgres`, is the classic negotiation and works everywhere. #### The server side: ssl = on, certificates and hostssl Nothing on the client matters if the server never offers TLS. On a server you administer, three settings in `postgresql.conf` turn it on: postgresql.conf: ```ini ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_min_protocol_version = 'TLSv1.2' ``` Relative paths are resolved against the data directory. Debian and Ubuntu packages ship with `ssl = on` pointing at a snakeoil certificate generated at install time, which is enough for `require` but useless for verification, because nothing trusts it and its name is the machine's host name. `ssl_min_protocol_version` defaults to `TLSv1.2` on PostgreSQL 13 and later, so the line above only states the default. A self-signed certificate for testing, straight from the PostgreSQL documentation's pattern: ```bash $ openssl req -new -x509 -days 365 -nodes -text \ -out server.crt -keyout server.key -subj "/CN=db.example.com" $ chmod 0600 server.key $ chown postgres:postgres server.key server.crt ``` The server is as strict about its key as the client is: it refuses to start TLS if `server.key` is readable by group or others, unless the file is owned by root with mode `0640`. On PostgreSQL 10 and later, the SSL settings take effect on a reload (`SELECT pg_reload_conf();`), but a mistake behaves differently depending on how it is applied: a reload logs `SSL configuration was not reloaded`, while a restart with `ssl = on` and an unreadable certificate fails to start at all. Check the server log after any change. For anything clients will verify, use a certificate whose Subject Alternative Name contains the exact host name clients connect with. A certificate for `db.example.com` fails `verify-full` when an application connects to the IP address, and the reverse. Turning TLS on does not force anyone to use it. That is the job of `pg_hba.conf`: pg_hba.conf: ```text # TYPE DATABASE USER ADDRESS METHOD hostssl shop appuser 0.0.0.0/0 scram-sha-256 hostssl shop backup 203.0.113.20/32 cert hostnossl all all 0.0.0.0/0 reject ``` `hostssl` lines match only TLS connections, `hostnossl` lines only plain ones, and `host` lines both. With only `hostssl` lines for remote addresses, a `prefer` client that has been downgraded simply cannot log in - which turns the server into the backstop for clients you do not control. The `cert` method authenticates by client certificate alone, matching the certificate's Common Name to the role name (or through a `map`). To require a valid client certificate as well as a password, add `clientcert=verify-ca` or `clientcert=verify-full` to a `hostssl` line; the server then needs `ssl_ca_file` pointing at the CA that signs client certificates. The older `clientcert=1` form was removed in PostgreSQL 14. ```diagram title: Where each half of the check happens client = Client | sslmode, sslrootcert req = SSLRequest | answered S or N tls = TLS handshake | cert chain, host name hba = pg_hba.conf | hostssl or host auth = Authentication | scram, cert client -> req : plain TCP 5432 req -> tls : S tls -> hba : encrypted hba -> auth ``` On a managed database you usually cannot edit any of this, so the question becomes what the host offers. Test it: `psql "...?sslmode=require"` either connects or fails with "server does not support SSL", and the banner tells you the protocol. Then ask the host which CA signed the certificate and which host name it covers, because that is what `verify-full` needs. On RE:NODE, a PostgreSQL server is reached on the host and port shown for the plan, with the superuser password generated for that server - check the TLS banner on your own connection rather than assuming either way, and keep the certificate files your application needs alongside its code, uploaded with the file manager or over SFTP. The hardening steps that sit around TLS are collected in the [database security checklist](https://www.renode.space/blog/database-security-checklist). #### Drivers: same words, different behaviour libpq-based clients all behave as described above. Drivers that implement the protocol themselves accept the same `sslmode` words and do not always mean the same thing by them. ##### psql and psycopg `psql`, `pg_dump`, psycopg 2 and psycopg 3 all use libpq, so the modes, defaults, files and environment variables are identical. Any libpq parameter can be passed as a keyword argument: Python, psycopg 3: ```python import os import psycopg conn = psycopg.connect( host="db.example.com", dbname="shop", user="appuser", password=os.environ["DB_PASSWORD"], sslmode="verify-full", sslrootcert="/app/certs/root.crt", ) ``` ##### node-postgres node-postgres (`pg`) does not use libpq. Without any SSL setting it connects in plain text. In a connection string, its parser (`pg-connection-string`) treats `prefer`, `require`, `verify-ca` and `verify-full` all as "TLS on, with full verification by Node's default trust store" - stricter than libpq for the first three, which is why a string that works in `psql` with `require` fails in Node with `self-signed certificate in certificate chain`. Adding `uselibpqcompat=true` to the string switches to libpq meanings. `sslmode=no-verify` turns verification off entirely, which is the same as `require` in libpq and no better. The right fix is not `rejectUnauthorized: false`, which is the most copied line in this whole area and disables the check that makes TLS worth having. Give Node the CA instead: Node.js, node-postgres: ```js import fs from "node:fs"; import { Pool } from "pg"; const pool = new Pool({ host: "db.example.com", database: "shop", user: "appuser", password: process.env.DB_PASSWORD, ssl: { ca: fs.readFileSync("/app/certs/root.crt", "utf8") }, }); ``` One trap: if you pass both a `connectionString` containing `sslmode` and an `ssl` object, the SSL settings parsed from the string replace yours, and the `ca` you supplied is silently ignored. Use one or the other. ##### JDBC The PostgreSQL JDBC driver (pgjdbc) has its own implementation too. In current versions its `sslmode` defaults to `prefer`, the same as libpq; setting `ssl=true` with no mode means `verify-full`. Its default files mirror libpq's but the key differs: it looks for `postgresql.pk8`, and reads PKCS-12 (`.p12`, `.pfx`) or PKCS-8 (`.pem`, `.der`) keys, not the traditional PEM key that libpq uses. Older driver versions behaved differently, so check the version in your build file before trusting a default. application.properties: ```properties spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full&sslrootcert=/app/certs/root.crt ``` `sslfactory=org.postgresql.ssl.NonValidatingFactory` turns verification off and is the JDBC equivalent of `rejectUnauthorized: false`. Treat any copy of it as a bug to remove. Other drivers - Go's `pgx`, .NET's Npgsql, Rust's `tokio-postgres` - each have their own defaults and spellings. Read the driver's own documentation for the word `sslmode` before shipping, and keep the CA file in your deployment the same way you keep other configuration; [environment variables and secrets](https://www.renode.space/blog/environment-variables-and-secrets) covers where those belong. #### SSL errors and what they mean **`server does not support SSL, but SSL was required`** - the client asked for `require` or stronger and the server answered `N`. Either `ssl = off` on the server, the certificate failed to load at startup (look for it in the server log), or something in the middle such as a TCP proxy is answering instead of PostgreSQL. node-postgres reports the same situation as `The server does not support SSL connections`. Do not "fix" it by lowering the mode; fix the server or ask the host. **`root certificate file "/home/app/.postgresql/root.crt" does not exist`** - you asked for `verify-ca` or `verify-full` and gave no `sslrootcert`, so libpq looked in the default location and found nothing. Newer clients add a hint suggesting `sslrootcert=system` or a weaker mode. Point `sslrootcert` at the right file; in containers, remember that the home directory is often `/root` or does not exist at all. **`SSL error: certificate verify failed`** - the server certificate does not chain to the CA in `sslrootcert`. Usually the wrong file (the server certificate of another machine, or a bundle missing an intermediate), or a public certificate checked against a private root. `openssl s_client -starttls postgres -connect db.example.com:5432` (OpenSSL 1.1.1 and later) shows the chain the server actually sends. **`server certificate for "db.example.com" does not match host name "10.0.0.5"`** - `verify-full` doing its job. Connect using a name the certificate covers, or reissue the certificate with the name or address you use in its Subject Alternative Name. **`SSL SYSCALL error: EOF detected`** or **`SSL SYSCALL error: Connection reset by peer`** - the TCP connection under the TLS session closed without a proper TLS shutdown. It is rarely a certificate problem. Common causes: the server process was killed (look for an out-of-memory stop in the server log), the server restarted, or a NAT gateway, firewall or load balancer dropped an idle connection. If it happens to idle connections in a pool, set the pool's idle timeout below the network's and enable TCP keepalives - [connection pools and limits](https://www.renode.space/blog/connection-pools-and-limits) goes through the settings. **`private key file "/home/app/.postgresql/postgresql.key" has group or world access`** - `chmod 0600` the file. In Docker, files mounted from a Windows or macOS host often arrive with loose permissions; copy the key into the image or a volume and set the mode there. **`no pg_hba.conf entry for host "...", user "appuser", database "shop", no encryption`** - the server only accepts TLS for you and the client connected in plain text. Something set `disable` or `allow`, or a driver defaulted to no TLS. Set the mode explicitly. For certificate failures in general - expired certificates, missing intermediates, wrong names - the same reasoning applies as for web servers; [SSL certificate errors explained](https://www.renode.space/blog/ssl-certificate-errors-explained) works through them. #### FAQ ##### What is the default sslmode in PostgreSQL? For libpq and everything built on it (`psql`, psycopg, `pg_dump`), it is `prefer`: use TLS if the server offers it, fall back to plain text otherwise, never check the certificate. Drivers with their own implementation choose their own default, and node-postgres uses no TLS at all unless told to. ##### Is sslmode=require secure enough? It stops passive eavesdropping, and on a private network you fully control that may be enough. It does not stop an active attacker, because any certificate is accepted. Over the internet, use `verify-full`. ##### What is the difference between verify-ca and verify-full? Both check that the certificate was signed by a CA you trust. `verify-full` also checks that the certificate was issued for the host name you connected to. With a public CA, `verify-ca` alone is nearly worthless, since anyone can get a certificate from that CA for their own domain. ##### Where do I get root.crt? From whoever runs the server. For a self-signed server it is the server's `server.crt` itself; for a private CA, the CA certificate; for a public CA, the system trust store via `sslrootcert=system` on PostgreSQL 16 and later clients. ##### Does sslmode apply to Unix socket connections? No. TLS is only used over TCP. A connection through `/var/run/postgresql` ignores `sslmode`, which is why a local `psql` works even with `PGSSLMODE=verify-full` set and no certificate in sight. ##### Can I force TLS from the server instead of trusting clients? Yes. Use only `hostssl` lines for remote addresses in `pg_hba.conf`, and add a `hostnossl ... reject` line for clarity. A plain-text client then fails to log in rather than leaking its session. This does not replace `verify-full` on the client, which is the only thing that stops impersonation. RE:NODE PostgreSQL servers come with a superuser password generated for that server, are reached on the plan's own host and port, and run on NVMe in Germany. Backup slots are included on every tier, and restoring one is a button in the panel. [See PostgreSQL plans](https://www.renode.space/database-hosting#postgresql) --- ### Upgrade MySQL 8.0 to 8.4: checker, backup, upgrade, rollback Published 2026-10-15 by RE:NODE Team. https://www.renode.space/blog/upgrade-mysql-8-0-to-8-4 Upgrading MySQL 8.0 to 8.4 is a four-part job, and the order matters more than any single command. Run MySQL Shell's upgrade checker against the live 8.0 server and fix every error it reports. Take a backup you can restore to 8.0 - a cold copy of the data directory for an in-place upgrade, a logical dump for everything else. Then either start the 8.4 binaries on the old data directory (in-place) or load a dump into a fresh 8.4 server (logical). Finally, test the application against 8.4 before real traffic arrives, because there is no in-place way back: once 8.4 has started on a data directory, 8.0 refuses to open it. What changes between the versions - the authentication default, the removed statements, the new InnoDB defaults - is covered in [MySQL 8.4 LTS: what changed](https://www.renode.space/blog/mysql-8-4-lts-what-changed). This guide is the procedure: the commands, what the checker's output means, the two upgrade routes in detail, how to test, and exactly how far rollback goes. #### In-place or logical: pick the route first Both routes end with your data on 8.4. They differ in downtime, in what you need control of, and in what rollback looks like. | | In-place | Logical (dump and load) | | --- | --- | --- | | What happens | 8.4 binaries start on the 8.0 data directory | Data is exported as SQL or files, loaded into a new 8.4 server | | You need | Root on the machine running MySQL | A login on both servers | | Downtime | Minutes, plus data dictionary upgrade | Dump time plus load time, usually much longer | | Speed on large data | Fast - no data is rewritten | Slow - every row and index is rebuilt | | Old server afterwards | Gone, unless you copied the files | Untouched, still running 8.0 | | Clean-up bonus | None | Tables are rebuilt, fragmentation and old row formats go | Use **in-place** when you run MySQL yourself on a VDS or your own machine and the data is large enough that a reload would take hours. Use **logical** when the database lives on a hosted service where someone else runs the server, when you are moving to a new machine at the same time, or when the data is small enough - a few gigabytes - that loading it takes minutes anyway. A logical upgrade also has the most comfortable rollback, because the 8.0 server is still there, unchanged, until you switch it off. There is a third route for people who cannot afford downtime: make a new 8.4 server a replica of the 8.0 source, let it catch up, then point the application at it. Replication from an older source to a newer replica is supported; the reverse is not, so once writes land on 8.4 the old 8.0 server cannot follow. That route is beyond a small application's needs and is not covered in detail here. ```diagram title: The order of an 8.0 to 8.4 upgrade check = Upgrade checker | against live 8.0 fix = Fix errors on 8.0 | config, accounts, schema backup = Backup | restored at least once inplace = In-place | 8.4 on old datadir logical = Dump and load | into new 8.4 test = Test the application | before real traffic check -> fix fix -> backup backup -> inplace backup -> logical inplace -> test logical -> test ``` #### Step 1: back up first, in a form 8.0 can read The backup taken before an upgrade has one job beyond the usual: it must be restorable to 8.0, because that is the version you would be going back to. Two kinds qualify. **A cold copy of the data directory.** Stop 8.0 cleanly and copy the whole directory - usually `/var/lib/mysql` on Linux - somewhere else. This is the only backup that undoes an in-place upgrade quickly: put the copy back, reinstall 8.0, start it, and you are where you were. It is useless for any other purpose, because it only opens on the same major version, but for this one it is perfect. A filesystem snapshot (LVM, ZFS) of a cleanly stopped server serves the same purpose and takes seconds. ```bash $ sudo systemctl stop mysql # mysqld on RHEL-family systems $ sudo cp -a /var/lib/mysql /var/backups/mysql-80-datadir $ sudo cp -a /etc/mysql /var/backups/mysql-80-etc ``` Copy the configuration too. The upgrade will make you edit it, and a rollback needs the 8.0 version back. **A logical dump.** This is the backup for the logical route, and the second line of defence for the in-place one. `mysqldump` with `--single-transaction --routines --events --triggers` produces a consistent SQL file without stopping the server; [mysqldump backup and restore](https://www.renode.space/blog/mysqldump-backup-and-restore) explains every flag and the errors it hits. For larger databases, MySQL Shell's dump utility is faster because it writes in parallel: ```js // in mysqlsh, JavaScript mode (\js) util.dumpInstance("/var/backups/dump-80", {threads: 4}) ``` `util.dumpInstance()` includes user accounts and grants by default, which `mysqldump` handles poorly - it dumps the `mysql` schema as tables, and loading those into a different version is asking for trouble. If you use `mysqldump`, save the accounts separately: for each application account, `SHOW CREATE USER` and `SHOW GRANTS` give you statements you can replay. > [!WARNING] > Restore the backup before you upgrade, not after something goes wrong. Load the dump into a scratch 8.0 server, or at least check that the file ends with the `Dump completed` line `mysqldump` writes. A backup nobody has restored is a hypothesis, and an upgrade is the worst time to test one. #### Step 2: run util.checkForServerUpgrade The upgrade checker lives in MySQL Shell (`mysqlsh`), not in the server. It connects to the running 8.0 server, inspects the configuration, the accounts, the schemas and the stored code, and reports what would fail or behave differently on the target version. It changes nothing. You need a MySQL Shell at least as new as the target: the checker can test for any version from 8.0.11 up to its own version number, and no further. Shell 8.4 or a 9.x release both work for an 8.4 target. It can run on any machine that can reach the server, including your laptop, but the configuration file check needs the file on the machine where Shell runs, so running it on the database server itself gives the most complete report. ```bash $ mysqlsh -- util check-for-server-upgrade root@localhost:3306 \ --target-version=8.4.6 --config-path=/etc/mysql/my.cnf ``` The same thing from inside an interactive Shell session, in JavaScript mode: ```js util.checkForServerUpgrade("root@localhost:3306", { targetVersion: "8.4.6", configPath: "/etc/mysql/my.cnf" }) ``` If you leave out `targetVersion`, the checker assumes the version matching the Shell itself - so a Shell 9.x run without it checks for 9.x and reports problems you do not have yet. Pin the 8.4 patch you are actually installing. If you leave out `configPath`, the checks that read the option file fail with a message asking for it; on Debian and Ubuntu the file is `/etc/mysql/my.cnf` (which includes `mysql.conf.d/`), on RHEL-family systems `/etc/my.cnf`. The account running the checker needs the `PROCESS` and `SELECT` privileges. Root is the easy answer on your own server. On a hosted server where you only have an application user, grant what you can and run it anyway; checks that cannot read what they need report that rather than passing silently. ##### Reading the report The text output is a numbered list of checks, each ending in `No issues found`, or a list of findings marked `Error`, `Warning` or `Notice`, followed by a summary: ```text Errors: 2 Warnings: 5 Notices: 0 2 errors were found. Please correct these issues before upgrading to avoid compatibility issues. ``` **Errors** block the upgrade or make the server refuse to start - a removed option in the configuration file is the usual one. Fix all of them on the 8.0 server, then run the checker again until the count is zero. **Warnings** do not stop the upgrade but describe something that will behave differently or break later; read each one. **Notices** are informational. The checks most often triggered on an 8.0 server heading to 8.4: | Check | What it finds | | --- | --- | | `sysvar` | Removed options in the config file, and settings whose defaults change | | `authMethodUsage` | Accounts still on `mysql_native_password` | | `deprecatedDefaultAuth` | `default_authentication_plugin` still set | | `pluginUsage` | Plugins that were removed or replaced by components | | `foreignKeyReferences` | Foreign keys pointing at non-unique or partial keys | | `reservedKeywords` | Identifiers that became reserved words | | `invalidPrivileges` | Privileges that no longer exist or changed | `mysqlsh -- util check-for-server-upgrade --list --target-version=8.4.6` prints every check with a description and the versions it applies to. For automation, `--output-format=JSON` gives a single object with `errorCount`, `warningCount` and a `checksPerformed` array; a CI step or a script can refuse to continue while `errorCount` is above zero. The checker does not signal failure through its exit code, so read the count rather than relying on the command failing. Two findings deserve a word on how to fix them. For `authMethodUsage`, move each account to `caching_sha2_password` with `ALTER USER ... IDENTIFIED WITH caching_sha2_password BY '...'` - after checking that the application's driver supports it. For `sysvar`, edit the option file and delete or rename the removed settings; the most common are `expire_logs_days` (replace with `binlog_expire_logs_seconds`) and `default_authentication_plugin`. Both fixes work on 8.0, so make them there, restart, and confirm the application still runs before the upgrade itself. That separates "the config change broke something" from "8.4 broke something", which is a distinction you will want. #### Step 3a: the in-place upgrade on Linux With the checker clean and the cold copy taken, the in-place upgrade is short. The MySQL documentation recommends a slow shutdown before an in-place upgrade between release series, so InnoDB finishes purge and change-buffer merges before the files are handed to the new version: ```sql SET GLOBAL innodb_fast_shutdown = 0; ``` Then stop the server and swap the packages. On Debian or Ubuntu with Oracle's APT repository, the series is chosen by the `mysql-apt-config` package: ```bash $ sudo systemctl stop mysql $ sudo dpkg-reconfigure mysql-apt-config # choose the 8.4 LTS series $ sudo apt update $ sudo apt install mysql-server ``` On RHEL, Rocky or Alma with Oracle's yum repository, disable the 8.0 repository and enable the 8.4 LTS one - `dnf repolist all | grep mysql` shows their exact names in your release package - then `sudo dnf upgrade mysql-community-server`. Distribution-packaged MySQL follows the distribution's own schedule and may not offer 8.4 at all; if `apt policy mysql-server` shows only 8.0 candidates, you need Oracle's repository or a container. In Docker the swap is a tag change. Stop the container, change `image: mysql:8.0` to `image: mysql:8.4`, and start it on the same volume. Pin to `8.4` or a specific patch, never `latest`, which moved past 8.4 to Innovation releases. ##### What happens at first start When 8.4 starts on an 8.0 data directory, it upgrades the data dictionary, then the `mysql` system schema, the `sys` schema and the help tables. Watch the error log while it happens: ```bash $ sudo tail -f /var/log/mysql/error.log ``` You are looking for a pair of lines reading `Server upgrade from '80xxx' to '804xx' started` and `... completed`, then `ready for connections`. On a server with a few hundred tables this takes seconds; with tens of thousands it can take minutes. Do not kill it halfway - an interrupted dictionary upgrade is what the cold copy is for. The behaviour is controlled by the server's `--upgrade` option, which defaults to `AUTO` (upgrade whatever needs it). `--upgrade=FORCE` re-runs the system table upgrade even when the server thinks it is done, which is the fix when the system schema is suspected to be half-upgraded. `mysql_upgrade` no longer exists; there is nothing to run by hand. If the server will not start, the log says why in its first few error lines. The usual cause is an option the checker flagged and nobody removed - the log shows `unknown variable` with the name. Remove it from the configuration and start again. #### Step 3b: the logical upgrade The logical route needs a running 8.4 server - a new machine, a new container, or a hosted MySQL 8.4 database - and the dump from step 1. With `mysqldump` output it is a plain import: ```bash $ gunzip -c appdb-80.sql.gz | mysql -h new-host -P 3306 -u root -p appdb ``` With a MySQL Shell dump, use the matching loader, which loads tables in parallel: ```js util.loadDump("/var/backups/dump-80", {threads: 4, loadUsers: true}) ``` Two things trip `util.loadDump()`. It loads data with `LOAD DATA LOCAL INFILE`, so the target server must have `local_infile` set to `ON`; if it is off and you cannot change it, use `mysqldump` instead. And `loadUsers` is off by default, so accounts and grants are skipped unless you ask for them. On a hosted server where you are not a full administrator, leaving users out and creating the application accounts yourself is often cleaner - the account you have may not be allowed to create accounts with arbitrary privileges anyway. A rehearsal is the point of the logical route. Load into the new server while the old one keeps serving traffic, test everything against the copy, and when it passes, take a fresh dump during a short write freeze and load that. Writes between the rehearsal dump and the final one are the only thing you need the freeze for. [Migrating MySQL to a new host](https://www.renode.space/blog/migrating-mysql-to-a-new-host) covers the cut-over itself: the freeze, the final dump, switching connection strings, keeping the old server read-only for a few days. Definers are the classic import failure. A view, trigger or routine created by `admin@localhost` on the old server carries `DEFINER=admin@localhost`, and on 8.4 creating an object with another account as definer needs the `SET_ANY_DEFINER` privilege. If the import stops with an access-denied error on a `CREATE VIEW` or `CREATE TRIGGER`, either create the definer account first or strip the clauses from the dump. #### Step 4: test the application before traffic arrives The upgrade checker inspects the database. It cannot see the application, and the application is where 8.4 surprises show up. Test against the upgraded copy - a rehearsal server for the logical route, a staging server or a copy restored from the cold backup for in-place. What to test, roughly in order of how often it breaks: 1. **Every account can log in.** Not just the main application user - the read-only reporting account, the backup user, the one the cron job uses. An account still on `mysql_native_password` fails here and nowhere else. 2. **Every client and driver connects.** Old connectors are the usual failure: a driver that cannot do `caching_sha2_password` reports that the plugin cannot be loaded. Check language drivers, GUI tools, and any backup or monitoring agent. [MySQL remote connections](https://www.renode.space/blog/mysql-remote-connections-getting-started) covers the TLS behaviour of that plugin. 3. **The test suite passes.** Run the application's own tests against 8.4. Migrations deserve special attention: an ORM that creates a foreign key on a non-unique column fails on 8.4 even though the existing ones survived the upgrade. 4. **Scheduled jobs and scripts run.** Anything that issues `SHOW SLAVE STATUS`, `FLUSH HOSTS` or `RESET MASTER` now fails with a syntax error. Search your repositories and crontabs for them. 5. **The slow queries stay fast.** The default `sql_mode` is unchanged from 8.0, so query results should be identical, but plans can shift with the new InnoDB defaults and optimizer changes. Take the top statements from `performance_schema.events_statements_summary_by_digest` on 8.0, run `EXPLAIN` on each on both versions, and compare. [MySQL indexes and EXPLAIN](https://www.renode.space/blog/mysql-indexes-and-explain) explains how to read the differences, and the [slow query log](https://www.renode.space/blog/mysql-slow-query-log) catches what you missed once real traffic arrives. Watch memory on a small server for the first day. 8.4's `temptable_max_ram` default is 3% of memory but never below 1 GB, which on a 1 or 2 GB machine can let temporary tables use far more than intended. If you did not set it on 8.0, set it explicitly now. #### Rollback: what you can and cannot undo This is the part people learn the hard way, so it is worth being exact. **An in-place downgrade from 8.4 to 8.0 is not supported.** Once 8.4 has started on a data directory, the data dictionary carries the new version. Start 8.0 on it and InnoDB refuses with `Downgrade is not supported`. There is no tool that converts it back. The only fast way back is the cold copy from step 1: stop 8.4, move its data directory aside, restore the copy, reinstall the 8.0 packages and the old configuration, start. Everything written after the copy was taken is lost, which is why the window between taking it and deciding to keep 8.4 should be short. **A logical dump from 8.4 into 8.0 is supported, for rollback only.** The 8.4 manual lists dump and load (or replication) as the way from 8.4 back to 8.0, on the condition that no new 8.4 functionality has been used on the data. This is how you keep writes made after the upgrade: dump the 8.4 server and load it into 8.0. It is slow on large data, and it works only while the schema uses nothing 8.0 lacks - a new privilege in a grant, a GTID tag, a feature introduced in 8.1 to 8.4. The longer you run on 8.4, the more likely something has crept in. **The logical route rolls back by switching the connection string.** The 8.0 server is still running. If you kept it read-only during the cut-over, switching back is instant, minus whatever was written to 8.4 in the meantime. | Route | Way back | Writes after the upgrade | | --- | --- | --- | | In-place | Restore cold copy, reinstall 8.0 | Lost | | In-place | Dump 8.4, load into 8.0 | Kept, if no 8.4-only features used | | Logical | Point the app back at 8.0 | Lost unless dumped across | Decide your point of no return in advance. A sensible one: keep the cold copy or the old server for a week, and do not adopt anything 8.4-specific in that time. After that, roll forward rather than back - fix problems on 8.4, since 8.0 no longer receives security fixes. #### Troubleshooting the upgrade Most failures fall into a handful of patterns, and the error log or client message names them. The server will not start after the package swap: read the first `[ERROR]` lines in the log. `unknown variable` means a removed option in a config file - check included directories, not just the main file. A message about the data directory being from a newer version means you are starting 8.0 on an upgraded directory; restore the cold copy. The application cannot log in but `mysql` can: the account is on `mysql_native_password`, or the driver is too old for `caching_sha2_password`. As a stopgap, `mysql_native_password=ON` in the `[mysqld]` section re-enables the old plugin, but it is gone in 9.0, so treat that as days of grace rather than a fix. The checker reports nothing but the import fails: the checker inspects the source server, not your dump file. Definer errors, `SET @@GLOBAL.GTID_PURGED` lines and `local_infile` refusals are load-time problems; the mysqldump guide covers each. Queries are slower after an in-place upgrade: run `ANALYZE TABLE` on the busiest tables so statistics are fresh, then compare plans. If one workload relied on the adaptive hash index, `innodb_adaptive_hash_index=ON` brings back the 8.0 behaviour; measure before and after rather than flipping it on by default. #### How this works on RE:NODE The MySQL line on RE:NODE already runs MySQL 8.4 LTS, so moving there from an 8.0 server elsewhere is the logical route: run the checker against your old server, dump, and load into the new one with the root password and application database generated for it. You do not run the server binaries, so there is no in-place upgrade to do, and the panel's backups - on demand or scheduled, restored with a button and stored off the machine - cover the database after it lands. Test against the new server before switching the application's connection string, and keep the old 8.0 server untouched until you are satisfied. #### FAQ ##### Can I upgrade from MySQL 5.7 straight to 8.4? Not in place. 5.7 must go to 8.0 first, then 8.0 to 8.4. A logical dump from 5.7 usually loads into 8.4 directly, with the exceptions being reserved words, zero dates under strict mode and old authentication plugins, so run the checker against the 5.7 server with an 8.4 target before trying. ##### Does the upgrade checker change anything on my server? No. It only reads: configuration, metadata from `information_schema` and the `mysql` schema, and stored code. It opens its own session and leaves the Shell's current session alone. It is safe to run against production during working hours. ##### How long does an in-place upgrade take? The package swap takes a minute or two. The data dictionary upgrade at first start depends on the number of tables, not the amount of data - seconds for an ordinary application, minutes for one with tens of thousands of tables. A slow shutdown beforehand can add time on a busy server with a lot of pending purge. ##### Should I go to 8.4 or straight to a 9.x release? 8.4 for a production database. It is the long-term support series and gets fixes without behaviour changes. The 9.x Innovation releases are supported only until the next one and keep removing things, including `mysql_native_password` entirely. ##### Do I have to upgrade the application's MySQL driver? Only if it cannot handle `caching_sha2_password`, which is true of the old Node `mysql` package and of drivers from the 5.7 era. Drivers from the last several years handle it. Check by connecting to the upgraded copy with the exact driver version the application ships with. RE:NODE's MySQL line runs MySQL 8.4 LTS, with the root password, application database and application user generated for each server. Load your 8.0 dump, test against it, then switch over. Plans start at $4 a month. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### Connect to a remote SQL Server with SSMS Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/connect-to-sql-server-with-ssms To connect SQL Server Management Studio to a hosted SQL Server, open Connect to Server and set four things: **Server name** is the host and port separated by a comma (`db.example.net,14330` - a colon will not work), **Authentication** is SQL Server Authentication, **Login** and **Password** are the credentials you were given (often `sa` on a fresh server), and under encryption, leave it Mandatory and tick **Trust server certificate** unless the server has a certificate from a trusted authority. That covers nearly every first connection. The rest of this post explains each field, the options worth changing, what to do once you are in, and the exact error messages for each way it fails. #### What you need before you start Four pieces of information and one program: - **Host** - a hostname or IP address. - **Port** - SQL Server's default is `1433`, but hosted servers often use a different one because many servers share an address. Use the port you were given, not the default. - **Login** - a SQL login name. On a new server this is usually `sa`, the built-in system administrator. - **Password** - for that login. - **SSMS** - SQL Server Management Studio, Microsoft's free management tool. It runs on Windows only. Recent SSMS releases (version 20 and later) introduced the encryption options described below. If your copy is older than that, update it; old versions use an older driver with different defaults and miss years of fixes. On macOS or Linux, use Visual Studio Code with Microsoft's MSSQL extension, DBeaver, or the command-line `sqlcmd` - every field in this post has an equivalent there. Azure Data Studio, which used to be the cross-platform answer, has been retired by Microsoft, so do not start new work in it. Before blaming SSMS, check that the port is reachable from your machine. In PowerShell: ```text PS> Test-NetConnection db.example.net -Port 14330 ``` `TcpTestSucceeded : True` means the network path is open and anything that fails after that is a setting in SSMS or a credential. `False` means a firewall - yours, your network's, or the server's - is in the way, and nothing in SSMS will fix it. Some office and school networks block outbound traffic to unusual ports; try from another connection to rule that out. #### The Connect to Server dialog, field by field | Field | Value | Why | | --- | --- | --- | | Server type | Database Engine | The other types are Analysis, Reporting and Integration Services | | Server name | `host,port` | Comma before the port. `tcp:host,port` forces TCP | | Authentication | SQL Server Authentication | Windows authentication needs a domain the server trusts | | Login | `sa` or your login | Case-insensitive on most servers | | Password | the password | Tick Remember password only on a machine you trust | | Encryption | Mandatory | Encrypts the connection | | Trust server certificate | Ticked, for a self-signed certificate | Skips certificate validation | The comma is the single most common mistake. SQL Server tools inherited the `host,port` syntax from the original client libraries, and everything in the Microsoft stack - SSMS, `sqlcmd`, ADO.NET connection strings, ODBC - uses it. Write `db.example.net:14330` and the client treats the whole string as a host name, fails to resolve it or tries named pipes, and gives you a network error that says nothing about the colon. The backslash form, `host\INSTANCE`, is for named instances found through the SQL Server Browser service on UDP 1434, which hosted servers do not expose; with an explicit port you never need it. Prefixing `tcp:` (`tcp:db.example.net,14330`) tells the client to use TCP and nothing else. It is optional, and it stops the client from trying other protocols first when something is misconfigured, which makes errors clearer. On RE:NODE, SQL Server plans are set up so that this dialog is all you need: the server has its own `sa` password, and a database is created for you and set as sa's default, so SSMS opens straight into it. The server name is the host and port shown on the plan, with a comma. #### Encryption and Trust server certificate SSMS 20 replaced the old "Encrypt connection" checkbox with an **Encryption** setting with three values, and moved to a driver that encrypts by default: | Encryption | Behaviour | | --- | --- | | Optional | Encrypts only if the server requires it. Credentials are still protected, data may not be | | Mandatory | Always encrypted with TLS. Certificate validated unless Trust server certificate is ticked | | Strict | TDS 8.0, TLS negotiated before anything else. Requires SQL Server 2022 and a certificate the client trusts | With Mandatory - the default - the client checks the server's certificate the way a browser checks a website's: it must chain to an authority the machine trusts, and its name must match the server name you typed. A SQL Server that has not been given a certificate generates a self-signed one at startup, which no machine trusts, so the connection fails with this: ```text A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ``` Ticking **Trust server certificate** keeps the connection encrypted and skips the validation. That is the normal setting for a hosted server with a self-signed certificate, and it is meaningfully better than turning encryption off: your password and data still cross the internet encrypted. What you give up is protection against someone intercepting the connection and presenting their own certificate, which requires them to sit on the network path between you and the server. If the server has a certificate from a trusted authority for a name such as `sql.example.com`, connect using that name and leave Trust server certificate unticked to get full validation. **Host name in certificate**, under the connection properties, handles the case where you connect by one name and the certificate carries another. Strict mode validates the certificate and is not for self-signed setups; with a server that presents a self-signed certificate, use Mandatory with Trust server certificate. > [!WARNING] > Setting Encryption to Optional to make the certificate error go away sends your session unencrypted if the server does not insist. Use Trust server certificate instead - it fixes the same error and keeps TLS on. #### Options worth setting under Options The **Options >>** button opens three more tabs. The ones that matter for a remote server: - **Connect to database** (Connection Properties) - `` uses the login's default database. Typing a database name here opens straight into it. Setting it to `master` is the escape hatch for one particular error, described in troubleshooting below. - **Network protocol** - `` is fine; TCP/IP is what is used either way for a remote host. - **Connection time-out** - raise it if you connect over a slow or distant link and see timeouts during login. - **Execution time-out** - `0` means queries never time out from the client side, which is the SSMS default and what you want for long maintenance scripts. - **Use custom color** - colours the status bar for this connection. Give production servers red. It costs nothing and has stopped many people running a test script in the wrong window. SSMS can save connections with passwords in your Windows profile. Convenient on your own machine, a bad idea on a shared one, and the saved password for `sa` is the full key to the server. #### First steps once you are connected Open a query window (`Ctrl+N`, or New Query) and confirm where you are: ```sql SELECT @@SERVERNAME AS server_name, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('ProductVersion') AS version, DB_NAME() AS current_database, SUSER_SNAME() AS login_name; ``` On a hosted Express server this shows `Express Edition (64-bit)`, a 16.0.x version number for SQL Server 2022, and the database you landed in. The database dropdown on the toolbar and `USE [dbname];` both switch databases. Then, before writing application code against `sa`, create a dedicated login and user for the application with only the permissions it needs. `sa` can drop every database on the server, and a connection string with it inside lives in config files, environment variables and developers' laptops. [SQL Server logins, users and roles](https://www.renode.space/blog/sql-server-logins-users-and-roles) has the exact script. Once that exists, use `sa` for administration from SSMS and nothing else. Object Explorer, on the left, shows databases, tables, views and security principals. Right-clicking a table gives Select Top 1000 Rows and Edit Top 200 Rows, which are fine for a look and poor for bulk changes - use T-SQL for anything that touches more than a handful of rows, inside a transaction you can roll back: ```sql BEGIN TRANSACTION; UPDATE dbo.Customers SET Country = 'GB' WHERE Country = 'UK'; -- check the row count in the Messages tab, then: COMMIT; -- or ROLLBACK; ``` #### Moving data in and out with SSMS SSMS has several tools for this, and the right one depends on where the data is going: - **Generate Scripts** (right-click the database, Tasks) writes schema and optionally data as T-SQL. Good for small databases and for moving between versions, since a script runs on any version that supports its syntax. - **Import Flat File** loads a CSV into a new table with a preview of the column types. Quick for one-off imports. - **Export Data-tier Application** produces a `.bacpac`, a schema-plus-data package that can be imported into another SQL Server or Azure SQL Database. - **Back Up and Restore** works with native `.bak` files, but the file path in those dialogs is a path on the server, not on your PC. To restore a `.bak` you have locally, it has to be uploaded to the server first, to a directory the SQL Server process can read. For anything large or repeatable, the command-line tools are better than the wizards: `sqlcmd` for scripts and `bcp` for bulk copying, covered in [sqlcmd and bcp](https://www.renode.space/blog/sql-server-sqlcmd-and-bcp). Moving a whole database from another host is its own procedure, with version rules, in [migrating a database to SQL Server hosting](https://www.renode.space/blog/migrating-a-database-to-sql-server-hosting). #### Troubleshooting connection errors **`A network-related or instance-specific error occurred while establishing a connection to SQL Server. ... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)`** - the client never reached the server over TCP. Check for a colon instead of a comma, a wrong port, or a firewall. `Test-NetConnection` from above tells you which. **`(provider: TCP Provider, error: 0 - No such host is known.)`** - the host name does not resolve. Typo, or a DNS record that does not exist yet. **`(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)`** - you used `host\INSTANCE`, and the client is looking for the SQL Server Browser service. Use `host,port` instead. **`The certificate chain was issued by an authority that is not trusted.`** - encryption is Mandatory and the certificate is self-signed. Tick Trust server certificate. **`The target principal name is incorrect.`** - the certificate is valid but issued for a different name than the one you typed. Connect with the name on the certificate, set Host name in certificate, or trust the certificate. **`Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)`** - wrong password, wrong login name, or the login is disabled. The message deliberately does not say which; the server's error log records a state number that does. Retype the password rather than pasting it, since pasted passwords often carry a trailing space. **`Cannot open user default database. Login failed. (Error: 4064)`** - the login's default database was dropped, renamed, or is offline. This one catches people who tidy up: on a server where sa's default is the database created for you, dropping or renaming that database means sa can no longer sign in the normal way. Fix it by opening **Options >> Connection Properties**, typing `master` into Connect to database, connecting, and then running: ```sql ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master]; ``` **Connection works, then drops after idle time.** Something on the network path closes idle TCP connections. Reconnect, and keep long-running work in scripts rather than in idle windows. #### FAQ ##### Why does SSMS need a comma instead of a colon before the port? Because SQL Server's client libraries define the server name as `host,port` and always have. The colon form, used by most other databases and URLs, is read as part of the host name. JDBC is the exception: its URLs use a colon. ##### Is it safe to tick Trust server certificate? The connection stays encrypted, so passwords and data are not readable in transit. What it skips is proof of the server's identity, which protects against an attacker positioned on the network path. For a hosted server with a self-signed certificate, it is the standard setting; use a trusted certificate and full validation when the threat model calls for it. ##### Can I use SSMS on a Mac? No, SSMS is Windows-only. Use Visual Studio Code with the MSSQL extension, DBeaver, or `sqlcmd`. The same server name, login and encryption settings apply in each. ##### Should I use the sa login for my application? No. Use `sa` for administration from SSMS, and create a separate login for each application with only the permissions it needs. If the application's credentials leak, the damage is limited to what that login can do. ##### Why can I connect from home but not from work? Your work network blocks outbound connections to the server's port. Run `Test-NetConnection` from both places to confirm, then either ask for the port to be allowed or connect from elsewhere. SQL Server 2022 Express on RE:NODE comes with its own `sa` password and a database created for you and set as sa's default, so SSMS opens straight into it - the server name is the host and port shown on the plan, separated by a comma. Express limits each database to 10 GB, and every plan carries backup slots. [See SQL Server plans](https://www.renode.space/database-hosting#sql-server) --- ### Job queues with Valkey: BullMQ, Celery, RQ and Laravel Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/job-queues-with-valkey A job queue moves slow work out of the request: the web process writes a small record saying "send this email" or "resize this image", returns to the user at once, and a separate worker process picks the record up and does the work. Valkey is the most common place to keep that record, because every popular queue library - BullMQ on Node, Celery and RQ on Python, Laravel's queue on PHP, Sidekiq on Ruby - was built on the Redis protocol, and Valkey speaks it unchanged. The libraries do the hard parts well: retries, delays, backoff, dead letters, concurrency. What they cannot do is protect you from a Valkey configured as a cache. A queue on a server that evicts keys under memory pressure loses jobs silently, and a queue on a server that does not persist loses every pending job on restart. This post sets up each library and then goes through the server settings, the failure modes and the sizing that make a queue trustworthy. #### When a queue is the right tool A queue earns its place when work is slow, can fail and be retried, and does not need to finish before the user sees a response. The usual candidates: - Sending email, SMS and push notifications. - Generating thumbnails, PDFs, exports and reports. - Calling third-party APIs that are slow or rate limited. - Webhooks you receive and want to acknowledge immediately, then process. - Scheduled and repeating work: nightly cleanups, digests, retrying payments. What it is not for is work the user is waiting on. If the next page needs the result, a queue just adds latency and a polling loop. Before adding Valkey for this, check whether your database already does the job. A table with `SELECT ... FOR UPDATE SKIP LOCKED` is a correct queue in PostgreSQL and MySQL 8, and it has one property no Valkey queue has: enqueueing the job and committing the data that caused it happen in one transaction. [Background jobs on a small server](https://www.renode.space/blog/background-jobs-on-a-small-server) walks through that option. Valkey wins when job volume is high, when you want the features of a mature library rather than writing your own, or when you already run Valkey for sessions and cache. ```diagram title: Where a job goes between request and result web = Web process | enqueues, returns valkey = Valkey | job lists and sets worker = Worker process | pulls and runs db = Database | the real results mail = Third party | email, API web -> valkey : add job valkey -> worker : blocking pop worker -> mail : slow call worker -> db : write result worker --> valkey : ack or retry ``` #### Server settings a queue depends on Three settings on the Valkey server decide whether jobs survive. Check them before writing any code. | Setting | Queue needs | Why | | --- | --- | --- | | `maxmemory-policy` | `noeviction` | Any other policy can delete job keys when memory is full | | `appendonly` | `yes` | Pending jobs survive a restart | | `appendfsync` | `everysec` | At most about a second of enqueues lost on a hard stop | | `maxmemory` | Set, with headroom | A backlog must fit in memory | **Eviction is the one that loses jobs.** Under `allkeys-lru` a full Valkey deletes the least recently used keys, and a job that has been waiting longest in a delayed set is exactly that. BullMQ checks this at startup and logs a warning when the policy is not `noeviction`; the others do not check at all. With `noeviction`, a full server rejects new jobs with an error, which your code sees and can alert on. That is the behaviour you want: a loud failure instead of a quiet one. [Valkey memory and eviction policies](https://www.renode.space/blog/valkey-memory-and-eviction-policies) covers the policies in detail. ```bash $ valkey-cli -h 203.0.113.20 -p 6380 --askpass INFO memory | grep -E 'maxmemory|used_memory_human' $ valkey-cli -h 203.0.113.20 -p 6380 --askpass INFO persistence | grep -E 'aof_enabled|rdb_last' ``` If the same Valkey holds a cache that should evict and a queue that must not, the honest answer is two instances. A small one for the queue costs less than debugging lost jobs. On RE:NODE a [Valkey plan](https://www.renode.space/database-hosting#valkey) saves to disk with both AOF and snapshots, and comes password-protected with the credentials generated per server. It runs as a separate server reached on its own host and port, so workers on an app plan and a web process elsewhere all connect to the same queue. #### BullMQ on Node.js BullMQ is the current queue library for Node, the successor to Bull. It uses `ioredis` underneath and stores each queue as a set of keys under a `bull::` prefix. ```bash $ npm install bullmq ``` queue.js: ```js import { Queue } from "bullmq"; export const connection = { host: process.env.VALKEY_HOST, port: Number(process.env.VALKEY_PORT), password: process.env.VALKEY_PASSWORD, }; export const emails = new Queue("emails", { connection, defaultJobOptions: { attempts: 5, backoff: { type: "exponential", delay: 10_000 }, removeOnComplete: { age: 3600, count: 1000 }, removeOnFail: { age: 7 * 24 * 3600 }, }, }); // in the web process await emails.add("welcome", { userId: 42 }, { jobId: `welcome:42` }); ``` worker.js: ```js import { Worker } from "bullmq"; import { connection } from "./queue.js"; const worker = new Worker("emails", async (job) => { await sendWelcomeEmail(job.data.userId); }, { connection: { ...connection, maxRetriesPerRequest: null }, concurrency: 5 }); worker.on("failed", (job, err) => console.error(job?.id, err.message)); process.on("SIGTERM", async () => { await worker.close(); process.exit(0); }); ``` The lines that matter: - **`maxRetriesPerRequest: null`** on the worker's connection. BullMQ's workers use blocking commands that wait indefinitely; `ioredis` with a retry limit gives up on them and breaks the worker. BullMQ sets this itself when it creates the connection, and warns if you pass a client configured otherwise. - **`removeOnComplete` and `removeOnFail`.** By default BullMQ keeps every finished job. On a busy queue that is the most common way a Valkey fills up: not pending work, but a history nobody reads. Keep a bounded window. - **`jobId`.** A custom id makes enqueueing idempotent: adding a job whose id already exists does nothing. That is how you avoid sending the welcome email twice when a request is retried. - **`worker.close()` on `SIGTERM`.** It stops taking new jobs and waits for the running ones, so a deploy does not interrupt a job halfway. [Graceful shutdown and health checks](https://www.renode.space/blog/graceful-shutdown-and-health-checks) explains why that signal handler matters on every platform. Repeating jobs - "every night at 03:00" - are built in as job schedulers in current BullMQ versions, with older code using the `repeat` option on `add`. Use one, not both, and check the docs for the version you have installed. #### Celery and RQ on Python **Celery** uses Valkey as a broker (where messages wait) and optionally as a result backend (where return values are kept). tasks.py: ```python import os from celery import Celery url = os.environ["VALKEY_URL"] # redis://:password@host:port/0 app = Celery("shop", broker=url, backend=url.replace("/0", "/1")) app.conf.update( task_acks_late=True, worker_prefetch_multiplier=1, task_reject_on_worker_lost=True, result_expires=3600, broker_transport_options={"visibility_timeout": 3600}, ) @app.task(bind=True, autoretry_for=(ConnectionError,), retry_backoff=True, max_retries=5) def send_welcome(self, user_id): ... ``` ```bash $ celery -A tasks worker --loglevel=INFO --concurrency=4 $ celery -A tasks beat --loglevel=INFO ``` The setting to understand is `visibility_timeout`. With the Redis-protocol transport, a message taken by a worker is not deleted; it is hidden until acknowledged. If the worker has not acknowledged it within the visibility timeout - an hour by default - the message is delivered again to another worker. A task that legitimately runs longer than that timeout therefore runs twice, or many times. Set the timeout above your longest task, and with `task_acks_late=True` make tasks safe to run twice anyway. `result_expires` matters for the same reason as BullMQ's `removeOnComplete`: results you never read pile up as keys. **RQ** is the simpler choice when you do not need Celery's routing, chords and canvas. ```python from redis import Redis from rq import Queue, Retry q = Queue("default", connection=Redis.from_url(os.environ["VALKEY_URL"])) job = q.enqueue(send_welcome, 42, retry=Retry(max=3, interval=[10, 60, 300]), job_timeout=600) ``` ```bash $ rq worker --url "$VALKEY_URL" high default low ``` Queue names are listed in priority order: the worker drains `high` before looking at `default`. Failed jobs go to a failed job registry you can inspect and requeue with `rq info` and the RQ dashboard. Scheduled jobs (`enqueue_in`, `enqueue_at`) need the worker started with `--with-scheduler`. #### Laravel queues Laravel's queue system needs only configuration to run on Valkey. .env: ```env QUEUE_CONNECTION=redis REDIS_HOST=203.0.113.20 REDIS_PORT=6380 REDIS_PASSWORD=a-long-generated-password ``` ```bash $ php artisan queue:work redis --queue=high,default --tries=3 --backoff=10 --max-time=3600 ``` In `config/queue.php`, the `redis` connection has a `retry_after` value, 90 seconds by default. It plays the same role as Celery's visibility timeout: a job reserved for longer than `retry_after` is released back onto the queue. Your longest job's `$timeout` must be a few seconds shorter than `retry_after`, or long jobs run twice. Laravel's documentation states this directly, and it is still the most common Laravel queue bug. `--max-time` makes the worker exit cleanly after an hour so it is restarted fresh by whatever supervises it, which bounds memory leaks in long-lived PHP processes. After every deploy run `php artisan queue:restart`, because a running worker keeps the old code in memory. Laravel Horizon adds a dashboard and auto-balancing on top of the same Redis-protocol queues. [Deploying Laravel to production](https://www.renode.space/blog/deploy-laravel-to-production) covers supervising the worker. #### Retries, idempotency and dead letters Every queue above delivers at least once. A worker can finish a job and die before acknowledging it, and the job will run again. Design for that rather than against it. 1. **Make jobs idempotent.** "Mark invoice 512 as paid" is safe to repeat. "Add 10 to the balance" is not. Store a processed marker - a database unique constraint on an event id is the strongest - and check it first. 2. **Put ids in the payload, not objects.** A job carrying a whole user record runs with stale data if it waits an hour. A job carrying `userId: 42` loads the current state. 3. **Retry with backoff, then stop.** Exponential backoff with a cap of five or so attempts handles transient failures. A job that fails five times is not going to succeed on the sixth; it needs a human. 4. **Keep failed jobs where someone will look.** BullMQ's failed set, RQ's failed registry and Laravel's `failed_jobs` table are dead-letter queues. Alert on their size, not on individual failures. 5. **Separate slow and fast queues.** If a nightly export and password-reset emails share a queue and two workers, the export blocks the emails. Give urgent work its own queue and workers. #### Sizing Valkey for a queue A queue's memory use is the backlog, plus any history you keep. A typical job record - payload, options, timestamps and a little library bookkeeping - is somewhere between a few hundred bytes and a few kilobytes. A backlog of 100,000 jobs at 2 KB is about 200 MB, which is why the steady-state size matters less than the worst case: what happens when workers are down for an hour during an incident and the web process keeps enqueueing. | Situation | What uses memory | Rough guide | | --- | --- | --- | | Small app, jobs processed in seconds | Little; backlog near zero | 256 MB is plenty | | Steady traffic, completed jobs kept for an hour | Completed-job history | Count jobs per hour times size | | Large payloads (HTML, base64 files) | The payloads | Store the file elsewhere, enqueue a reference | | Workers stopped for an incident | The whole backlog | Size for an hour of enqueues | Never put files in a job. Upload the image to disk or object storage and enqueue its path. Measure instead of estimating: `INFO memory` for the total, `MEMORY USAGE` on a sample job key, and the library's own counts (`getJobCounts()` in BullMQ, `rq info`, `celery inspect`). CPU is rarely the limit for a queue on Valkey; a single small instance handles thousands of enqueues per second. What workers need is CPU and memory of their own, on whatever runs them. ##### What to watch A queue fails slowly and then all at once, so watch the numbers that move first: - **Backlog size** - waiting jobs per queue. A backlog that grows during the day and drains at night is fine; one that only grows means workers cannot keep up. - **Age of the oldest waiting job.** More useful than the count: 10,000 jobs that take a millisecond each are nothing, ten jobs waiting for an hour are an incident. - **Failed jobs per hour**, alerting on a rise rather than on individual failures. - **Valkey memory against its limit**, from `INFO memory`, with an alert well before the `noeviction` wall. - **Connected clients**, from `INFO clients`. A worker fleet that leaks connections ends at the server's client limit. Each library has a dashboard that shows most of this - Bull Board or Taskforce for BullMQ, Flower for Celery, `rq-dashboard` for RQ, Horizon for Laravel - but a dashboard nobody looks at is not monitoring. Export the two or three numbers that matter to whatever already alerts you; [monitoring that tells you something](https://www.renode.space/blog/monitoring-that-tells-you-something) is about choosing them. Workers also need supervising. A worker process that exits - an unhandled exception, an out-of-memory stop, a deploy - must be restarted automatically, or the queue quietly stops draining while the web process keeps adding to it. On a VDS that is a systemd unit with `Restart=always`; on a hosting panel, it is the server's own restart behaviour. Either way, check after every deploy that workers came back. #### Troubleshooting **Jobs disappear without running or failing.** Eviction, or a restart without persistence. Check `evicted_keys` in `INFO stats` and the eviction policy. **The same job runs twice.** A visibility timeout or `retry_after` shorter than the job, or a worker that died before acknowledging. Raise the timeout and make the job idempotent. **Workers stop picking up jobs after a network blip.** The connection dropped and the client gave up. In BullMQ check `maxRetriesPerRequest: null`; elsewhere make sure the worker is supervised and restarted on exit. **Valkey memory grows steadily with no backlog.** Completed jobs and results being kept forever. Set `removeOnComplete`, `result_expires` or the equivalent, then let the old ones expire. **`OOM command not allowed when used memory > 'maxmemory'`.** The `noeviction` policy doing its job. The queue is full: workers are down or too slow, or history is filling memory. #### FAQ ##### Can Valkey replace RabbitMQ or Kafka? For a single application's background jobs, yes, and with less to operate. RabbitMQ is better at complex routing between many services, and Kafka at retaining large event logs for replay. If you are choosing between them for an app's email and thumbnail jobs, a Valkey queue is the proportionate choice. ##### Do BullMQ, Celery and RQ work with Valkey unchanged? They speak the Redis protocol and use standard commands and Lua scripting, which Valkey implements. You connect with the same `redis://` URL and the same client libraries. Test your version against your server before going live, as you would with any infrastructure change. ##### Should the queue and the cache share one Valkey? Better not. A cache wants an evicting policy and a queue must never evict. On one instance you have to choose, and either choice is wrong for one of them. Two small instances solve it. ##### How many workers should I run? Enough to keep the backlog near zero at peak, and no more than the work can use. For I/O-bound jobs such as sending email, one process with concurrency 5 to 20 goes a long way. For CPU-bound jobs such as image processing, one worker per core. ##### What happens to scheduled jobs if Valkey restarts? With persistence enabled they are on disk and reload with everything else; a delayed job whose time passed during the restart runs when workers reconnect. Without persistence, every pending and scheduled job is gone. A Valkey server for your queue, from 256 MB, password-protected with credentials generated for it and saved to disk with AOF and snapshots, so pending jobs survive a restart. BullMQ, Celery, RQ and Laravel connect with the URL they already use. [See Valkey plans](https://www.renode.space/database-hosting#valkey) --- ### Migrating a database to SQL Server hosting Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/migrating-a-database-to-sql-server-hosting The best way to move a SQL Server database to a new host is a native backup and restore: `BACKUP DATABASE ... WITH COPY_ONLY, CHECKSUM` on the old server, copy the `.bak`, `RESTORE DATABASE ... WITH MOVE` on the new one. It is exact, it is fast, and it keeps everything inside the database - schema, data, users, permissions, statistics. It only works upwards, from the same or an older SQL Server version, and only if the database fits the target edition. When it does not apply - going to an older version, coming from Azure SQL Database, or arriving from MySQL or PostgreSQL - the alternatives are a BACPAC, generated scripts, or a schema conversion plus a data load. This guide covers how to choose, the checks to run before you start, each method in turn, and the clean-up that every migration needs afterwards. #### Choosing a method | Method | Works when | Speed | Keeps | | --- | --- | --- | --- | | Backup and restore (`.bak`) | Source is SQL Server, same or older version than target | Fastest | Everything in the database | | BACPAC (`SqlPackage`) | Any SQL Server or Azure SQL Database, either direction | Slow at size | Schema and data; not history or statistics | | Generate Scripts (SSMS) | Small databases, any direction | Slow, large files | Schema, optionally data as `INSERT`s | | `bcp` per table | Schema already created on target | Fast | Data only | | SSMA or manual conversion | Source is MySQL, Oracle, Access, Db2 or another engine | Varies | What you convert | The decision is mostly made by two questions. Is the source SQL Server at the same or an older version than the target? Then backup and restore. Is it newer, or Azure SQL Database, which cannot produce a `.bak` file at all? Then a BACPAC. Generated scripts are for small databases where you want to read or edit what is being moved, and conversion tools are for leaving another engine behind. #### Checks before you move anything Five minutes of queries on the source saves a failed restore at midnight. **Version.** Run `SELECT @@VERSION;` on the source. A backup restores only onto the same or a newer major version: 2022 is version 16, 2019 is 15, 2017 is 14, 2016 is 13. SQL Server 2022 restores backups from SQL Server 2008 onwards; a database still on 2005 or older needs an intermediate hop through a version that accepts it. **Size.** If the target is Express, each database is limited to 10 GB of data files (the log does not count). Check what you actually use, not the allocated file size: ```sql SELECT name, type_desc, size / 128.0 AS allocated_mb, FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS used_mb FROM sys.database_files; ``` A database with 14 GB allocated and 6 GB used will restore fine onto Express only if the data files are first shrunk below 10 GB, because a restore recreates files at their original size. Shrink the data file on the source (or on a restored copy) before the final backup, rebuild indexes afterwards, and be aware a shrink fragments indexes heavily. If you genuinely use more than 10 GB, archive old data first or choose an edition without the cap - [SQL Server Express hosting](https://www.renode.space/blog/sql-server-express-hosting-guide) covers where that line falls. **Edition features.** Some features tie a database to an edition. This view lists any in use: ```sql SELECT feature_name FROM sys.dm_db_persisted_sku_features; ``` Since SQL Server 2016 Service Pack 1, most programmability features - partitioning, columnstore, data compression, in-memory OLTP - are available in every edition, so on a modern source this usually returns nothing. If it lists Transparent Data Encryption, that database will not restore on Express; decrypt it at the source first. **Dependencies outside the database.** List what lives on the old server rather than in the database: SQL Server Agent jobs, linked servers, logins, Database Mail profiles, server-level triggers, and any code that refers to other databases by three-part name (`otherdb.dbo.Table`). None of these travel with a backup. Agent jobs deserve special attention when the target is Express, which has no Agent - each job becomes a scheduled `sqlcmd` call somewhere else, as [sqlcmd and bcp](https://www.renode.space/blog/sql-server-sqlcmd-and-bcp) shows. #### Method 1: backup and restore On the source, take a copy-only backup so you do not disturb its existing backup chain: ```sql BACKUP DATABASE [shop] TO DISK = N'D:\Backups\shop-migrate.bak' WITH COPY_ONLY, CHECKSUM, INIT, STATS = 10; RESTORE VERIFYONLY FROM DISK = N'D:\Backups\shop-migrate.bak' WITH CHECKSUM; ``` Copy the file to the new server, into a directory the SQL Server process can read. `RESTORE` resolves the path on the server, not on your workstation, so uploading the file is a separate step: SFTP, the host's file manager, or whatever the host provides. On RE:NODE every server has SFTP and the file manager; if you are unsure which folder the engine can read, ask support before uploading a large file. Then read the logical file names and restore with each file moved to the target's data directory. A Windows source has Windows paths baked into the backup, and on a Linux target they must all be moved: ```sql RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak'; RESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak' WITH MOVE N'shop' TO N'/var/opt/mssql/data/shop.mdf', MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf', RECOVERY, CHECKSUM, STATS = 10; ``` `SELECT SERVERPROPERTY('InstanceDefaultDataPath');` gives the right directory if you do not know it. Once restored, the backup file can be deleted from the server - on a plan with limited disk, a 9 GB `.bak` beside a 9 GB database is the quickest way to fill it. [SQL Server backup and restore](https://www.renode.space/blog/sql-server-backup-and-restore) covers the restore options and errors in more depth. A hosted server may already have a database created for you. You can restore over it with `REPLACE`, or restore under your original name and point things at that instead. If your `sa` login has the pre-created database as its default and you restore under another name, change the default so tools open the right one: ```sql ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [shop]; ``` #### Method 2: BACPAC with SqlPackage A BACPAC is a zip file holding the database schema as a model plus the data of every table in bulk-copy format. It is produced by connecting to the source as a client, so it needs no file access to either server, works from Azure SQL Database, and can move a database to an older version as long as it uses no features the older version lacks. ```bash $ sqlpackage /Action:Export \ /SourceConnectionString:"Server=old.example.net,1433;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True" \ /TargetFile:shop.bacpac $ sqlpackage /Action:Import \ /SourceFile:shop.bacpac \ /TargetConnectionString:"Server=db.example.net,14330;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True" ``` The import creates the database, or fills an existing one that is completely empty. In SSMS, the same operations are under the database's Tasks menu as Export Data-tier Application, and under the Databases node as Import Data-tier Application. The trade-offs are real: - **Consistency.** An export reads tables one after another with no single snapshot. If writes happen during the export, the BACPAC can contain an order without its lines. Stop the application, or export from a restored copy of a backup. - **Speed.** It is several times slower than a native backup and restore, and the import rebuilds every index. Fine for a few gigabytes, tedious for tens. - **Validation.** The export validates the schema first and refuses databases it cannot represent - most often because of references to other databases, or objects that no longer compile. The error names the objects; fix or drop them and run it again. - **What is not carried.** Statistics, Query Store data, the transaction log and the backup history do not travel. Logins do not either, but contained users and database users do. SqlPackage connects with the same encryption defaults as other modern Microsoft drivers, so a server with a self-signed certificate needs `TrustServerCertificate=True` in the connection string, as above. [SQL Server connection strings](https://www.renode.space/blog/sql-server-connection-strings) covers the encryption keywords. #### Method 3: generated scripts and bcp For a small database, SSMS can write the whole thing out as T-SQL: right-click the database, Tasks, Generate Scripts. Choose the objects, then under Advanced set "Types of data to script" to "Schema and data" and "Script for Server Version" to the target's version. The result is one `.sql` file of `CREATE` statements followed by `INSERT`s. It is readable, editable and version-neutral, which makes it the right tool for a database of a few hundred megabytes that you want to tidy on the way across. It is the wrong tool for anything big: a script with millions of single-row `INSERT` statements runs slowly and SSMS struggles to even open it. Run large scripts with `sqlcmd -i` rather than in a query window. For larger databases, combine the two: script only the schema, run it on the target, then move the data table by table with `bcp` in native format, which is fast and exact. Load parent tables before children, or disable foreign keys during the load and re-enable them `WITH CHECK` afterwards so they are trusted again. #### Coming from MySQL or PostgreSQL Moving from another engine is a conversion, not a copy. For MySQL, Oracle, Access, Db2 and SAP ASE, Microsoft's free SQL Server Migration Assistant (SSMA) converts the schema, flags what it cannot convert, and copies the data. There is no SSMA for PostgreSQL; there you convert the schema by hand or with your framework's migrations, then load data through CSV and `bcp`, or with a small script that reads from one connection and bulk-copies into the other. The type mapping is mostly mechanical: | MySQL / PostgreSQL | SQL Server | | --- | --- | | `AUTO_INCREMENT` / `SERIAL`, `IDENTITY` | `int IDENTITY(1,1)` | | `BOOLEAN`, `TINYINT(1)` | `bit` | | `TEXT`, `LONGTEXT` / `text` | `nvarchar(max)` | | `VARCHAR(n)` with utf8mb4 / `varchar(n)` | `nvarchar(n)`, or `varchar(n)` with a UTF-8 collation | | `DATETIME` / `timestamp` | `datetime2` | | `timestamptz` | `datetimeoffset` | | `uuid` | `uniqueidentifier` | | `JSON` / `jsonb` | `nvarchar(max)` with an `ISJSON` check constraint | | `ENUM(...)` | A `CHECK` constraint or a lookup table | The text types deserve the most thought, because MySQL and PostgreSQL are UTF-8 throughout and SQL Server's `varchar` is not, unless the column uses a UTF-8 collation. [SQL Server collations and Unicode](https://www.renode.space/blog/sql-server-collations-and-unicode) explains the choice. The SQL itself needs work too: `LIMIT` becomes `TOP` or `OFFSET ... FETCH`, backtick and double-quote identifiers become square brackets, `RETURNING` becomes `OUTPUT`, and upserts become `MERGE` or an update-then-insert. [T-SQL basics for app developers](https://www.renode.space/blog/t-sql-basics-for-app-developers) collects the differences that bite. If the application uses an ORM, switching its provider and generating the schema from migrations is usually faster than converting DDL by hand. #### Cutting over with little downtime For a small database, the simplest cutover is the honest one: put the application in maintenance mode, take the final backup, restore, change the connection string, bring the application back. For a 5 GB database on decent links that is a window of minutes. When that is too long, use a full backup plus a differential. Restore the full backup ahead of time without recovering it, so it waits for more: ```sql -- Days before: restore the full backup, leave it waiting RESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-full.bak' WITH MOVE N'shop' TO N'/var/opt/mssql/data/shop.mdf', MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf', NORECOVERY; -- At cutover: stop writes, take a differential on the source, then RESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-diff.bak' WITH RECOVERY; ``` The differential holds only what changed since the full backup, so the downtime is the time to take, copy and apply that. Note that the full backup on the source must not be `COPY_ONLY` for this, because a differential is always based on the last normal full backup. If the source is in the full recovery model, log backups can shrink the gap further. Either way, rehearse the whole sequence once against a scratch database before the real night. [Migrations without downtime](https://www.renode.space/blog/migrations-without-downtime) covers the application side: schema changes that both versions of the code can live with. #### After the restore: the clean-up list Every migrated database needs the same few things before the application points at it: 1. **Fix orphaned users.** SQL logins on the new server have new SIDs, so restored users do not match them. Create each login, then `ALTER USER [app] WITH LOGIN = [app];`. To move logins with their passwords and SIDs intact, Microsoft's `sp_help_revlogin` script generates the `CREATE LOGIN` statements on the source. [SQL Server logins, users and roles](https://www.renode.space/blog/sql-server-logins-users-and-roles) explains the model. 2. **Check the compatibility level.** A restored database keeps the level it had, which may be 110 or 130 on a 2022 server. Test the application at that level first, then raise it: `ALTER DATABASE [shop] SET COMPATIBILITY_LEVEL = 160;`. With Query Store on, you can compare plans before and after and force the old plan for any query that regresses; [SQL Server Query Store and slow queries](https://www.renode.space/blog/sql-server-query-store-and-slow-queries) shows how. 3. **Update statistics.** `EXEC sp_updatestats;` in the restored database, so the optimiser is not working from figures gathered on different hardware and a different version's estimator. 4. **Check integrity.** `DBCC CHECKDB WITH NO_INFOMSGS;` once, to start with a known-good database. 5. **Set page verification.** Databases that began life on very old versions may still use `TORN_PAGE_DETECTION`. `ALTER DATABASE [shop] SET PAGE_VERIFY CHECKSUM;` is the modern setting. 6. **Check the recovery model** and the log size, which may have arrived oversized from the source. 7. **Update connection strings**: new host, the port after a comma, SQL authentication, and the encryption settings. #### FAQ ##### Can I restore a SQL Server 2022 backup on SQL Server 2019? No. Backups only restore onto the same or a newer version, and no option or trace flag changes that. Export a BACPAC instead, or script the schema and move the data with `bcp`, after checking the database uses nothing that 2019 lacks. ##### How do I migrate from Azure SQL Database to a hosted SQL Server? Export a BACPAC from Azure SQL Database, through the portal or SqlPackage, and import it on the target. Azure SQL Database cannot produce a native `.bak` file, so backup and restore is not available from it. ##### My database is 12 GB. Can it run on SQL Server Express? Not as it is. Express caps the data in each database at 10 GB. Check how much is actually used rather than allocated, archive or delete old data, and shrink the data file below the limit before backing up. If the data really exceeds 10 GB, you need a paid edition. ##### Do I need to move the transaction log file? You move it with `WITH MOVE` like the data file, but its contents matter little: a restore recovers the database and the log starts afresh from there. If the source log was huge, shrink it once after the restore and set a sensible size, rather than carrying the old bloat over. ##### Will my stored procedures and views come across? Yes, with backup and restore, a BACPAC or generated scripts - they are part of the database. What does not come across is anything stored at server level: logins, Agent jobs, linked servers and server triggers. List those before you start. RE:NODE runs SQL Server 2022 Express on Linux with the `sa` password generated and a database created and set as sa's default; connect at `host,port` and restore into it. Each database can hold up to 10 GB, every server has SFTP for getting the backup there, and backup slots come with every tier. [See SQL Server plans](https://www.renode.space/database-hosting#sql-server) --- ### Migrating MySQL to a new host: dump, import, cut over Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/migrating-mysql-to-a-new-host Moving a MySQL database to a new host is four jobs: take an inventory of what you are moving, copy the data with a logical dump, recreate what a dump does not carry (users, grants, sometimes definers and time zones), then cut the application over during a short window in which nothing writes to the old server. For databases up to a few gigabytes, the whole move fits in a planned ten to thirty minutes of downtime, and the commands are ordinary `mysqldump` and `mysql`. The risk is not in the copy - it is in the things you did not notice until the application was pointed at the new server. This guide is ordered so you notice them first. The target throughout is MySQL 8.4 LTS. Sources can be MySQL 5.7, 8.0, 8.4, or a server from another family; the differences are covered where they matter. #### Take an inventory first Run these on the old server before planning anything. They answer the questions that decide how the move goes. ```sql -- Size per database: decides dump method and downtime SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC; -- Anything not InnoDB: MyISAM is not covered by --single-transaction SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine <> 'InnoDB' AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema'); -- Stored code and its definers SELECT routine_schema, routine_name, routine_type, definer FROM information_schema.routines WHERE routine_schema = 'appdb'; SELECT trigger_name, definer FROM information_schema.triggers WHERE trigger_schema = 'appdb'; SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'appdb'; SELECT event_name, definer, status FROM information_schema.events WHERE event_schema = 'appdb'; -- Version, modes and settings that change behaviour SELECT @@version, @@sql_mode, @@lower_case_table_names, @@time_zone, @@character_set_server, @@collation_server; ``` Write down: total size, any non-InnoDB tables (convert them first with `ALTER TABLE t ENGINE=InnoDB`, or accept a locked dump), every definer that is not the application user, whether events exist and are enabled, and the source's version and `sql_mode`. Also list every client that connects - not just the main application, but cron jobs, workers, reporting tools, a forgotten admin panel. `SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host` over a day catches most of them; every one needs its connection settings changed at cut-over. #### Version and compatibility checks | Source | Into MySQL 8.4 | Watch for | | --- | --- | --- | | MySQL 8.4 | Straightforward | Definers, users | | MySQL 8.0 | Straightforward | `mysql_native_password` accounts, removed options | | MySQL 5.7 | Usually fine with a logical dump | New reserved words, zero dates, `utf8mb3` defaults | | Other MySQL-compatible servers | Needs checking | Server-specific syntax, types and collations | A logical dump loads into a newer version far more reliably than a physical copy, and it skips version hops: a 5.7 dump can be loaded directly into 8.4, whereas an in-place upgrade from 5.7 must go through 8.0 first. The usual issues from 5.7: - **Reserved words.** MySQL 8 reserved words such as `RANK`, `GROUPS`, `ROWS`, `LEAD`, `LAG`, `SYSTEM`, `CUME_DIST` and `MEMBER`. A column named `rank` loads fine (the dump quotes identifiers) but the application's unquoted queries break. Search the code. - **Zero dates.** `0000-00-00` values in `DATE` and `DATETIME` columns are rejected under the default 8.x `sql_mode`. Fix them to `NULL` on the source first, or load with a session `sql_mode` that allows them and fix afterwards. - **`GROUP BY` queries.** `ONLY_FULL_GROUP_BY` has been on by default since 5.7, but many old applications switched it off. If the source's `sql_mode` lacks it, the application's queries may fail on the new server until fixed or until the session mode is set the same way. Moving from a server of another family, such as an older fork, adds collation names the target does not know (`Unknown collation` errors), types that MySQL lacks, and syntax for features like sequences or system-versioned tables. Check the dump on a scratch MySQL 8.4 server before planning the real move. [MySQL 8.4 LTS: what changed](https://www.renode.space/blog/mysql-8-4-lts-what-changed) lists the changes from 8.0, and MySQL Shell's `util.checkForServerUpgrade()` reports them for a specific server. #### Dumping the data For most databases, one command on a machine that can reach the old server: ```bash $ mysqldump -h old-host -P 3306 -u root -p \ --single-transaction --routines --events --triggers \ --set-gtid-purged=OFF --no-tablespaces --hex-blob \ appdb | zstd -T0 > appdb.sql.zst ``` Note what is not there: `--databases`. Without it, the dump contains no `CREATE DATABASE` or `USE` statement, so you can load it into a database with a different name. That matters on a hosted server where the database was created for you with its own name. With `--databases appdb`, the dump would create and switch to `appdb` regardless. `--set-gtid-purged=OFF` and `--no-tablespaces` stop the two errors that most often break a load by a non-superuser. `--hex-blob` makes binary columns survive any text handling on the way. Each flag is explained in [mysqldump backup and restore](https://www.renode.space/blog/mysqldump-backup-and-restore). For databases large enough that a single-threaded dump and load does not fit your window - roughly beyond 20 GB, depending on indexes - MySQL Shell's `util.dumpSchemas()` and `util.loadDump()` work in parallel and can strip definers and fix common compatibility problems as they go. `loadDump` needs `local_infile` enabled on the target; check that before relying on it. #### Users, grants and definers A single-database dump carries tables, data, views, routines, triggers and events. It does not carry accounts. Recreate them on the new server rather than dumping the `mysql` system schema, which is tied to the source version and is not something to load into an 8.4 server. ```sql -- On the old server: print each account and its grants SHOW CREATE USER 'app'@'%'; SHOW GRANTS FOR 'app'@'%'; ``` `SHOW CREATE USER` prints the account with its password hash, so the account can be recreated with the same password without knowing it - with one exception. Accounts on `mysql_native_password` carry that plugin's hash, and on MySQL 8.4 the plugin is disabled by default, so the recreated account cannot log in. Set a new password with the default `caching_sha2_password` instead, which is also a good moment to rotate credentials that may have been sitting in old config files for years. [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges) covers recreating accounts and roles properly. Definers are the other half. Every view, routine, trigger and event names the account that created it, and loading one whose definer is someone other than you needs `SET_ANY_DEFINER` on 8.4; if the account does not exist on the new server at all, the object also fails when used. The simplest path when loading as an ordinary application user is to strip the definers, which makes you the definer of everything: ```bash $ zstd -dc appdb.sql.zst \ | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \ | mysql -h new-host -P 30412 -u app -p appdb_new ``` On RE:NODE, a MySQL server starts with an application database, an application user for it, and a root password, all generated. Load into that database name, point the application at that user, and use root only if the dump needs privileges the application user does not have. #### Loading and verifying Load into the new server, then prove it worked before any client points at it. ```bash $ zstd -dc appdb.sql.zst | mysql -h new-host -P 30412 -u root -p appdb_new ``` Check it with numbers, not by eye. `information_schema.tables.table_rows` is an estimate for InnoDB and differs between servers even when the data is identical; count exactly: ```sql -- Generate exact counts for every table; run the output on both servers and diff SELECT CONCAT('SELECT ''', table_name, ''' AS t, COUNT(*) AS n FROM `', table_name, '` UNION ALL') AS q FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'; ``` Remove the trailing `UNION ALL` from the last line, run the query on both sides, and compare. Then compare the things counts miss: the latest row in your busiest tables (`SELECT MAX(id), MAX(updated_at) FROM orders`), a few known records with accented text to catch character set damage (see [utf8mb4 and collations](https://www.renode.space/blog/mysql-utf8mb4-and-collations)), the presence of routines and events (`SHOW PROCEDURE STATUS WHERE Db = DATABASE()`, `SHOW EVENTS`), and that `AUTO_INCREMENT` values carried over (`SHOW CREATE TABLE` includes them). Finally, run the application against the new server - a staging copy of the app with its connection settings changed - and use it. Log in, create something, run the scheduled jobs by hand. This is the step that finds reserved words, `sql_mode` differences and missing grants, while the old server is still the live one. #### Settings that do not travel A dump copies data, not server behaviour. These are the differences that make an application act strangely after a move that "worked": - **Time zones.** If the application uses named zones (`CONVERT_TZ(ts, 'UTC', 'Europe/Berlin')` or `SET time_zone = 'Europe/Berlin'`), the new server needs its time zone tables loaded, or `CONVERT_TZ` returns `NULL` and `SET time_zone` fails with `Unknown or incorrect time zone`. Test with `SELECT CONVERT_TZ('2026-01-01 00:00', 'UTC', 'Europe/Berlin');`. Numeric offsets like `'+00:00'` always work. - **`lower_case_table_names`.** A database from a Windows or macOS server, where table names are case-insensitive, can contain queries that refer to `Orders` and `orders` interchangeably. On Linux the default is case-sensitive, and the variable cannot be changed after a server is initialised. Fix the queries, not the server. - **`sql_mode`.** Compare `@@GLOBAL.sql_mode` on both. If the old server ran a looser mode, either fix the application or have it set the old mode for its session at connect time, as a stopgap. - **The event scheduler.** Events are copied, but they only run if `event_scheduler` is `ON` on the new server (the default in 8.x). Check that events you expect to run, do - and that ones you disabled on the old server are disabled on the new one. - **Distance.** If the database moves further from the application, every query pays the extra round trip. An application making forty queries per page notices 20 ms of added latency as most of a second. RE:NODE servers are in Germany; keep the application close to the database. #### Cutting over For an ordinary application the reliable cut-over is a short planned window: 1. **Lower the timeout on anything cached.** If the application finds the database through a DNS name you control, lower its TTL a day ahead so the change spreads quickly. 2. **Stop writes to the old server.** Put the application in maintenance mode and stop workers and cron jobs. If you hold root on the old server, `SET GLOBAL super_read_only = ON` makes certain nothing else writes either - including the forgotten client you did not list. 3. **Take the final dump** and load it into the new server, replacing the rehearsal copy. Dropping and recreating the target database first avoids mixing in tables from the rehearsal. 4. **Verify** with the same counts and checks as before. This is fast now, because you scripted it. 5. **Change every client's connection settings** - host, port, user, password, database name - and start the application, then workers and jobs. 6. **Watch** the application's errors and the new server's connections for the first hour. Keep the old server, read-only, for at least a few days. If something turns up, you can compare against it; if something goes badly wrong in the first hour, switching back is a configuration change rather than a restore. For larger databases where the dump and load would not fit a short window, the alternative is to load an initial copy, make the new server a replica of the old one with `CHANGE REPLICATION SOURCE TO` until it catches up, then cut over in seconds. That needs binary logging and replication privileges on the source, network access between the two, and control of both servers' configuration - worth it for a large busy database, unnecessary for most. [Migrations without downtime](https://www.renode.space/blog/migrations-without-downtime) covers the schema-change side of keeping an application up, and moving a WordPress site specifically is in [migrating WordPress to a new host](https://www.renode.space/blog/migrate-wordpress-to-a-new-host). > [!WARNING] > The classic migration failure is a split brain: the cut-over happens, but one worker or cron job still points at the old server and keeps writing to it for days. Making the old server read-only at step 2 turns that silent data loss into a loud error you notice immediately. #### FAQ ##### How long does it take to migrate a MySQL database? The copy takes roughly as long as dumping plus loading, and loading is the slow half because every index is rebuilt. A database of 1 GB usually moves in a few minutes; 20 GB can take an hour or more single-threaded. Rehearse it once with real data and time it - that number, plus verification, is your downtime. ##### Can I migrate without any downtime? Close to it, with replication: load a copy, replicate changes from the old server until the new one is current, then switch clients over. It needs privileges and configuration access on both servers. For most small applications a planned ten-minute window is simpler and safer. ##### Do I need to copy the mysql system database? No, and you should not load one from another version into MySQL 8.4. Recreate the accounts you need with `SHOW CREATE USER` and `SHOW GRANTS` output, and reset passwords for any account that used `mysql_native_password`. ##### Why does my application say the user does not exist after migrating? Either the account was not recreated, or its host pattern does not match the application's new address, or it uses `mysql_native_password`, which is disabled by default on 8.4. If the error mentions a definer instead, a view or routine names an account that does not exist on the new server - strip or recreate the definer. ##### Can I use phpMyAdmin to move a database? For small databases, yes: export as SQL from the old server and import on the new one. It runs inside a web request, so large databases hit upload and time limits. [phpMyAdmin import and export](https://www.renode.space/blog/phpmyadmin-import-export-guide) covers the limits and settings; beyond a few hundred megabytes, use `mysqldump` and the `mysql` client. A MySQL server on RE:NODE is MySQL 8.4 LTS with an application database, an application user and a root password generated, reached on the plan's host and port - so the target of a migration is ready when payment clears. Backup slots on every plan cover the rehearsal and the morning after. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL 8.4 LTS: what changed from 8.0 and what breaks Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/mysql-8-4-lts-what-changed MySQL 8.4 is the first long-term support release under Oracle's new release model, and since MySQL 8.0 reached end of life in April 2026 it is the version to be on. For most applications the upgrade from 8.0 is uneventful: the SQL is the same, the drivers are the same, the data files upgrade in place. What breaks is at the edges. Accounts using `mysql_native_password` cannot log in until the plugin is switched back on, because 8.4 disables it by default. Scripts using `CHANGE MASTER TO`, `SHOW SLAVE STATUS`, `FLUSH HOSTS`, `mysqlpump` or `expire_logs_days` fail, because they were removed. And a handful of InnoDB defaults changed - mostly for the better on fast storage, with one that small servers should override. This guide covers the release model first, because it changes how you plan upgrades, then every change likely to affect an application or an administrator, then how to check a database before moving it. #### LTS and Innovation releases Until 8.0, MySQL shipped one series for years and added features in patch releases: 8.0.x gained `EXPLAIN ANALYZE` at 8.0.18, hash joins at 8.0.18, `innodb_redo_log_capacity` at 8.0.30. That made "8.0" a moving target - two servers both called MySQL 8.0 could behave differently. From 8.1 onwards, Oracle split releases into two tracks: | Track | Versions | Cadence | Support | Changes within the series | | --- | --- | --- | --- | --- | | Innovation | 8.1, 8.2, 8.3, 9.0, 9.1 and on | About quarterly | Until the next Innovation release | New features, removals, behaviour changes | | LTS | 8.4, then a later 9.x | About every two years | Around eight years in total | Bug and security fixes only | An LTS series is meant to be boring. 8.4.1, 8.4.2 and so on contain fixes, not new features or changed defaults, so a patch update should not change how your application behaves. Oracle lists 8.4 as supported until 2032, with premier support for the first five years of that. Innovation releases are for people who want new features early and are prepared to upgrade every few months; for a production application database, LTS is the sensible default. The practical consequence is that features introduced in 8.1 to 8.3 arrive in 8.4 all at once, and so do the removals that the Innovation releases made along the way. That is why 8.0 to 8.4 has more breaking changes than a version number of 0.4 suggests. #### Authentication: mysql_native_password is off This is the change that stops applications connecting. `mysql_native_password`, the SHA-1-based plugin that was the default until 8.0, was deprecated in 8.0. In 8.4 it is still present but **disabled by default**. An account created with it exists, but logins fail until the server starts with: my.cnf: ```ini [mysqld] mysql_native_password=ON ``` The same switch is available on the command line as `--mysql-native-password=ON`. It is a start-up option, not something an application user can change, and in MySQL 9.0 the plugin is removed entirely, so turning it back on buys time rather than solving anything. The real fix is to move accounts to `caching_sha2_password`, the default since 8.0: ```sql -- Find accounts still on the old plugin SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password'; -- Move one; the password must be set again because the hash format differs ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'the-password'; ``` Before doing that, check that the application's driver supports `caching_sha2_password`. Anything released in the last several years does: PHP's mysqlnd, Node's `mysql2`, PyMySQL, mysqlclient, Connector/J 8 and later, MySqlConnector for .NET. The original Node `mysql` package does not, nor do MySQL 5.7-era command-line clients. A client that cannot do it fails with `Authentication plugin 'caching_sha2_password' cannot be loaded`; one that can, but connects without TLS, needs the server's RSA public key or a TLS connection on first login - [MySQL remote connections](https://www.renode.space/blog/mysql-remote-connections-getting-started) explains that behaviour. Two smaller authentication changes: the `default_authentication_plugin` variable was removed (its job is done by `authentication_policy`, which also governs multi-factor authentication), and the `authentication_fido` plugins were removed in favour of `authentication_webauthn`. #### Removed statements, options and tools Anything here that appears in a script, a configuration file or an ORM's admin tooling fails on 8.4. Most were deprecated during 8.0 with warnings. | Removed | Use instead | | --- | --- | | `CHANGE MASTER TO`, `MASTER_*` options | `CHANGE REPLICATION SOURCE TO`, `SOURCE_*` options | | `START SLAVE`, `STOP SLAVE`, `RESET SLAVE` | `START REPLICA`, `STOP REPLICA`, `RESET REPLICA` | | `SHOW SLAVE STATUS`, `SHOW SLAVE HOSTS` | `SHOW REPLICA STATUS`, `SHOW REPLICAS` | | `SHOW MASTER STATUS`, `SHOW MASTER LOGS` | `SHOW BINARY LOG STATUS`, `SHOW BINARY LOGS` | | `RESET MASTER`, `PURGE MASTER LOGS` | `RESET BINARY LOGS AND GTIDS`, `PURGE BINARY LOGS` | | `FLUSH HOSTS` | `TRUNCATE TABLE performance_schema.host_cache` | | `expire_logs_days` | `binlog_expire_logs_seconds` | | `default_authentication_plugin` | `authentication_policy` | | `--ssl`, `--admin-ssl`, `have_ssl`, `have_openssl` | `--tls-version`, `--admin-tls-version` | | `--skip-host-cache` | `--host-cache-size=0` | | `mysqlpump` | `mysqldump` or MySQL Shell dump utilities | | `mysql_upgrade` | Nothing; the server upgrades itself at start-up | | `mysql_ssl_rsa_setup` | Nothing; the server generates certificates itself | | `binlog_transaction_dependency_tracking` | Nothing; writeset tracking is always used | | `AUTO_INCREMENT` on `FLOAT`/`DOUBLE` columns | An integer column | The replication syntax change catches monitoring scripts more than replication setups. A health check that runs `SHOW SLAVE STATUS` every minute and greps for `Seconds_Behind_Master` now gets a syntax error, and in `SHOW REPLICA STATUS` the column is `Seconds_Behind_Source`. Status variables like `Com_show_slave_status` were removed with them. `FLUSH HOSTS` matters to anyone who has met `Host is blocked because of many connection errors`. The `TRUNCATE` replacement needs the `DROP` privilege on that table, which root has; `mysqladmin flush-hosts` still works and uses it internally. Also removed: the `--old` and `--new` options, `--language`, `avoid_temporal_upgrade` and `show_old_temporals`, `LOW_PRIORITY` with `LOCK TABLES ... WRITE`, the `keyring_file` and `keyring_encrypted_file` plugins (replaced by components), `INFORMATION_SCHEMA.TABLESPACES`, and support for weak TLS ciphers - 8.4 accepts only ciphers with forward secrecy and authenticated encryption on TLS 1.2 and 1.3. > [!WARNING] > A server will not start with a removed option in its configuration file. Before upgrading, search every `my.cnf` and included file for `expire_logs_days`, `default_authentication_plugin`, `skip-host-cache`, `query_cache` (gone since 8.0) and the `master-info` and `relay-log-info` repository options, and remove or replace them. A failed start after an upgrade is a bad time to discover the log message. #### New InnoDB and server defaults 8.4 changed a set of InnoDB defaults to suit modern hardware - NVMe storage and many cores - rather than the spinning disks the old ones were written for. Unless you set these explicitly, your server behaves differently after upgrading. | Variable | 8.0 | 8.4 | | --- | --- | --- | | `innodb_io_capacity` | `200` | `10000` | | `innodb_io_capacity_max` | at least `2000` | twice `innodb_io_capacity` | | `innodb_log_buffer_size` | `16M` | `64M` | | `innodb_flush_method` (Linux) | `fsync` | `O_DIRECT` where supported | | `innodb_use_fdatasync` | `OFF` | `ON` | | `innodb_adaptive_hash_index` | `ON` | `OFF` | | `innodb_change_buffering` | `all` | `none` | | `innodb_buffer_pool_instances` | `8` (1 if under 1 GB) | Calculated; 1 if 1 GB or less | | `innodb_doublewrite_pages` | `innodb_write_io_threads` (4) | `128` | | `innodb_read_io_threads` | `4` | Half the logical CPUs, at least 4 | | `innodb_purge_threads` | `4` | `1` up to 16 logical CPUs, else `4` | | `innodb_numa_interleave` | `OFF` | `ON` | | `temptable_max_ram` | `1G` | 3% of memory, between 1 and 4 GB | | `temptable_use_mmap` / `temptable_max_mmap` | `ON` / `1G` | `OFF` / `0` | Most of these are improvements you get for free. `O_DIRECT` stops InnoDB double-caching pages in the operating system's cache. The higher I/O capacity lets background flushing keep up with fast storage. Turning off the adaptive hash index and change buffering removes two features that often caused contention and rarely paid off on modern workloads; if a specific read-heavy workload benefited, they can be turned back on. The one to watch on a small server is `temptable_max_ram`. Its 8.4 default is never below 1 GB, which on a 1 or 2 GB server allows in-memory temporary tables to claim half the machine or all of it. Cap it - [InnoDB tuning for small servers](https://www.renode.space/blog/mysql-innodb-tuning-for-small-servers) has numbers by plan size. `innodb_dedicated_server`, if you use it, no longer sets `innodb_flush_method`, and sizes the redo log from CPU count rather than memory. #### Schema and privilege changes **Foreign keys must reference unique keys.** `restrict_fk_on_non_standard_key` defaults to `ON`, so creating a foreign key that references a non-unique index or only part of a key fails with `ER_WARN_DEPRECATED_NON_STANDARD_KEY`. Existing foreign keys of that kind survive the upgrade with warnings; new ones need the referenced columns to be a primary key or unique index, or the variable turned off. **Wildcards in database grants are deprecated.** `GRANT ... ON` a database name containing `%` or `_` still works as a pattern, but those characters are expected to become literals in a future release. Grant per database. **New privileges.** `FLUSH_PRIVILEGES` (just for `FLUSH PRIVILEGES`; holders of `RELOAD` get it on upgrade), `OPTIMIZE_LOCAL_TABLE`, `TRANSACTION_GTID_TAG`, and `SET_ANY_DEFINER` with `ALLOW_NONEXISTENT_DEFINER`, which replace `SET_USER_ID` for creating objects with someone else as definer. That last change affects restores: a dump whose views and routines name a definer other than you needs `SET_ANY_DEFINER` to load as written. [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges) covers definers in more detail. **Identifiers with more than one `$`.** An unquoted identifier containing two or more dollar signs is a syntax error since 8.1. Rare, but it breaks loudly when it happens. On the positive side, 8.4 brings the Innovation-era features with it: histograms that can be set to update automatically with `ANALYZE TABLE`, GTID tags for grouping transactions, and a `mysql` client that can disable its `system` shell escape with `--system-command=OFF`. #### Checking a database before you upgrade MySQL Shell's upgrade checker inspects a running 8.0 server and reports everything that will be a problem on the target version: removed options in use, accounts on deprecated plugins, foreign keys on non-unique keys, reserved words used as identifiers, and more. ```bash $ mysqlsh root@old-server:3306 -- util check-for-server-upgrade --target-version=8.4.6 ``` Run it with an account that can read the configuration and the `mysql` schema, read every error and warning, and fix what it reports on the old server first. The upgrade paths it supports: | From | To 8.4 | How | | --- | --- | --- | | 8.0 (recent patch) | Yes | In place, or dump and load | | 8.1-8.3 Innovation | Yes | In place | | 5.7 | Not directly | Upgrade to 8.0 first, or dump and load | | 8.4.x | 8.4.y | In place; downgrades within the 8.4 series are supported too | In-place means stopping the old server and starting the new binaries on the same data directory; the server upgrades its data dictionary and system tables itself at first start. On a hosted database, where the provider runs the server, the equivalent is a logical dump of your databases loaded into an 8.4 server - which is also how you move between hosts, covered in [migrating MySQL to a new host](https://www.renode.space/blog/migrating-mysql-to-a-new-host). A logical dump also skips the 5.7-to-8.0 hop, since MySQL 8.4 loads a 5.7 dump directly in most cases; the exceptions are mostly reserved words and zero dates under strict mode. #### An upgrade, step by step For a self-managed server, the in-place route from 8.0 looks like this. Each step has a reason, and skipping one is how upgrades turn into restores. 1. **Get to a recent 8.0 patch first.** Upgrading from 8.0.40-something is better tested than from 8.0.20, and the late 8.0 patches warn about most of what 8.4 removes. Read the error log after that restart for deprecation warnings: each one is a future failure. 2. **Run the upgrade checker** against the running server and fix everything it reports as an error. Warnings deserve a look too; they include accounts on `mysql_native_password`. 3. **Clean the configuration.** Remove the options listed above, and decide deliberately about the changed InnoDB defaults: if you had tuned `innodb_io_capacity` for your storage, your value stays; if you never set it, it jumps from 200 to 10000. 4. **Take a full backup you have restored at least once.** A logical dump is the one that lets you go back to 8.0 if you must, because an in-place upgrade cannot be reversed by starting the old binaries - the data dictionary has been upgraded. 5. **Shut down cleanly.** With `innodb_fast_shutdown` at its default of 1 a clean shutdown is fine; setting it to 0 first makes InnoDB finish purge and merge work before stopping, which some people prefer before a major upgrade. 6. **Install 8.4 and start it** on the same data directory. Watch the error log: the server upgrades the data dictionary, then the system schema, and reports when it is ready for connections. On a large instance with many tables this takes a while; do not interrupt it. 7. **Test the application**, including logins for every account, the scheduled jobs, and any monitoring that runs administrative statements. The failures in this upgrade are almost all in the edges, not in the queries. On a hosted database you do not do steps 5 and 6 yourself, and the route is a dump from the old server loaded into a new 8.4 one. Steps 1 to 4 and 7 still apply, and the upgrade checker can be pointed at your old server from any machine with MySQL Shell. When the application works against a rehearsal copy loaded from the dump, the cut-over is a configuration change. #### What does not change For the application, most things. SQL syntax for queries and DML is the same. `utf8mb4` with `utf8mb4_0900_ai_ci` remains the default. Window functions, common table expressions, JSON functions, `EXPLAIN ANALYZE`, invisible and functional indexes all behave as in late 8.0. ORMs that supported 8.0 support 8.4; the ones to check are those that issue replication or administration statements, and those pinned to an old driver. The checklist, in order: upgrade the drivers, move accounts off `mysql_native_password`, remove removed options from configuration, replace removed statements in scripts and monitoring, run the upgrade checker, then rehearse the move on a copy and run the application's test suite against it. #### FAQ ##### Is MySQL 8.0 still supported? No. MySQL 8.0 reached end of life in April 2026 and no longer receives fixes, including security fixes, from Oracle. Some cloud providers sell paid extended support for it, but the supported free path is 8.4 LTS. ##### Should I use MySQL 8.4 or a 9.x Innovation release? For a production application, 8.4 LTS. Innovation releases are supported only until the next one, roughly a quarter, so staying on one means upgrading several times a year through releases that may remove things. Use 9.x to test what is coming, and move to the next LTS when it arrives. ##### Can I still use mysql_native_password on 8.4? Only if the server is started with `mysql_native_password=ON`, which needs control of the server's configuration. Even then it is a stopgap: MySQL 9.0 removed the plugin. Move the account to `caching_sha2_password` and upgrade any driver that cannot handle it. ##### Will my 8.0 dump load into MySQL 8.4? Almost always, yes. A logical dump from 8.0 loads into 8.4 without changes in the vast majority of cases. Watch for definer clauses naming accounts that do not exist on the new server, and for `SET @@GLOBAL` lines that need privileges an ordinary user lacks; [mysqldump backup and restore](https://www.renode.space/blog/mysqldump-backup-and-restore) covers both. ##### Do I need to run mysql_upgrade after upgrading? No - and on 8.4 you cannot, because it was removed. Since 8.0.16 the server upgrades its own system tables and data dictionary at start-up, controlled by the `--upgrade` option, which defaults to doing whatever is necessary. The MySQL line on RE:NODE runs MySQL 8.4 LTS, with a root password, an application database and an application user generated for each server and reached on the plan's host and port. Plans start at 1 GB of memory and 10 GB of NVMe. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL connection limits and pooling: too many connections Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/mysql-connection-limits-and-pooling `ERROR 1040 (HY000): Too many connections` almost never means the database needs a bigger `max_connections`. It means something opened more connections than the work in flight needed, and usually that something is a pool configured with defaults nobody looked at, multiplied by every process and worker that holds one. MySQL 8.4 allows 151 client connections by default. A small application serving real traffic rarely needs more than 20 at a time. The fix is nearly always on the client side: fewer, shared, recycled connections - and a server limit set to what the memory can actually carry. This post covers how MySQL counts connections, what each one costs, how to see who is holding them, and how to size the pool in each common runtime so the total stays below the limit even on the day you scale out. #### How MySQL counts connections Every client connection to MySQL is a session with its own thread on the server. MySQL 8.4 uses one thread per connection in the community edition, so the number of connections is also the number of server threads waiting for or executing work. The settings that decide how many can exist: | Variable | Default (8.4) | What it does | | --- | --- | --- | | `max_connections` | `151` | Maximum simultaneous client connections | | `max_user_connections` | `0` | Per-account cap; `0` means no cap beyond the global one | | `wait_timeout` | `28800` | Seconds an idle non-interactive connection lives before the server closes it | | `interactive_timeout` | `28800` | The same, for clients that announce themselves as interactive (the `mysql` shell) | | `thread_cache_size` | autosized | Threads kept for reuse after a client disconnects | | `connect_timeout` | `10` | Seconds the server waits for a handshake to complete | Two details in that table explain most surprises. First, MySQL keeps one extra connection beyond `max_connections` for an account with the `CONNECTION_ADMIN` privilege (or the deprecated `SUPER`). That is why you can usually still log in as root to diagnose a server that is refusing your application - as long as root is not one of the accounts flooding it. Use it: an application user should never have that privilege. Second, `wait_timeout` is eight hours. An application that opens connections and forgets about them does not get cleaned up for a working day. On a server limited to 151, that is how a leaking script running every minute from cron fills the table by mid-morning. Since MySQL 8.0.14 there is also a dedicated administrative interface (`admin_address` and `admin_port`, default port `33062`), which accepts connections from privileged accounts regardless of `max_connections`. It is off unless `admin_address` is set, and on a hosted plan the port is not normally reachable, so treat the reserved extra connection as your emergency door instead. #### What a connection costs Raising `max_connections` to 2,000 is a configuration change, not capacity. Each connection consumes memory on the server, and some of that memory is only allocated when a query needs it - which means the server looks fine until the moment many connections run heavy queries at once. The per-session buffers that matter: | Buffer | Default | Allocated when | | --- | --- | --- | | `thread_stack` | 1 MB | Every thread, always | | `net_buffer_length` | 16 KB | Every connection, grows up to `max_allowed_packet` | | `sort_buffer_size` | 256 KB | A query sorts without an index | | `join_buffer_size` | 256 KB | A join cannot use an index (per join) | | `read_buffer_size` | 128 KB | Sequential scans of MyISAM and some temporary work | | `read_rnd_buffer_size` | 256 KB | Reading rows in sorted order | | `tmp_table_size` | 16 MB | Internal temporary tables, up to this size in memory | An idle connection costs a megabyte or two. A connection running a badly indexed report with a sort, two unindexed joins and a temporary table can briefly cost tens of megabytes. Multiply the worst case by `max_connections` and you get the number you should compare with the RAM left over after `innodb_buffer_pool_size`. That is the real sizing rule. On a 1 GB server, the buffer pool takes roughly half, the server's own overhead a few hundred megabytes, and what remains supports perhaps 50 to 100 active connections doing normal work. On 4 GB you have room for a few hundred, and on 8 GB the limit is more likely to be CPU than memory - 300 connections all executing queries on four cores just queue for the cores. [MySQL InnoDB tuning for small servers](https://www.renode.space/blog/mysql-innodb-tuning-for-small-servers) goes through the buffer pool side of that sum. > [!WARNING] > A database that runs out of memory does not slow down gracefully. In a container with a hard memory limit the process is killed and restarted, every connection drops at once, and every pool reconnects at the same moment. Setting `max_connections` above what the memory can carry turns a refused connection into a full outage. #### Finding out who holds the connections Before changing any number, look. Log in as root (the reserved connection gets you in even when the application cannot) and ask the server. ```sql SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Threads_running', 'Max_used_connections', 'Max_used_connections_time', 'Aborted_connects', 'Aborted_clients', 'Connection_errors_max_connections'); ``` What each one tells you: - `Threads_connected` is how many connections exist now. `Threads_running` is how many are actually executing a statement. A healthy application shows a large gap: 40 connected, 2 running. - `Max_used_connections` is the high-water mark since the last restart, and `Max_used_connections_time` says when it happened. If the peak is 151 and the time matches the outage, you have your incident. - `Connection_errors_max_connections` counts refusals because of the limit. - `Aborted_clients` grows when clients disappear without closing cleanly - typically processes killed mid-request or connections timed out by `wait_timeout`. Then break the connections down by who and where: ```sql SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, db, command, COUNT(*) AS conns, MAX(time) AS longest_s FROM information_schema.processlist GROUP BY user, client, db, command ORDER BY conns DESC; ``` A hundred rows with `command = 'Sleep'` from one client address is a pool that is too large or a process that leaks. A dozen rows with `command = 'Query'` and a large `time` is slow queries holding connections, which is a different problem: the pool fills because every connection is stuck behind a lock or a full table scan. [The MySQL slow query log](https://www.renode.space/blog/mysql-slow-query-log) is the tool for that one. `sys.session` gives a richer view if you want the current statement, memory and lock information for every session, and `performance_schema.threads` has the same data at a lower level. For an emergency, `KILL ` ends a connection, and `KILL QUERY ` stops only the statement running on it. #### Changing the server limits With the root password you can change the limits live. `SET GLOBAL` lasts until the next restart; `SET PERSIST` also writes the value to `mysqld-auto.cnf` in the data directory, so it survives restarts. ```sql -- Look first SELECT @@max_connections, @@wait_timeout, @@max_user_connections; -- A realistic limit for a 2 GB server, kept across restarts SET PERSIST max_connections = 200; -- Close idle connections after ten minutes instead of eight hours SET PERSIST wait_timeout = 600; -- Cap one application account so it cannot starve the others ALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120; ``` A shorter `wait_timeout` is the most useful of the three on a server shared by several applications, because it reaps connections abandoned by crashed or careless clients. It comes with one condition: your pools must recycle connections before the server kills them, otherwise the application occasionally picks up a dead connection and the first query on it fails with `MySQL server has gone away` (error 2006) or `Lost connection to MySQL server during query` (error 2013). Every runtime below has a setting for this, usually called maximum lifetime or recycle time. Set it a minute or more below `wait_timeout`. The per-account cap is the other underused tool. If a reporting job, a cron script and the web application share one database server, give each its own user with `MAX_USER_CONNECTIONS`. When the cron job misbehaves, it hits its own ceiling (error 1203, `User already has more than 'max_user_connections' active connections`) and the website keeps working. [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges) covers creating those separate accounts. #### The pooling arithmetic A pool is a set of connections opened once and lent to requests. It exists because opening a MySQL connection costs a TCP handshake, a TLS handshake if you use one, and an authentication exchange - several round trips, which add up to milliseconds per request across a network. Reusing a connection costs nothing. The trap is that every process has its own pool. The total number of connections your application can open is: ```text total = pool size x processes per instance x instances (+ cron, workers, shells) ``` A Node.js application with the `mysql2` default of 10 connections, run in cluster mode with 4 workers, deployed on 3 servers, can open 120 connections before anyone runs a migration. Add a queue worker with its own pool of 10 and a staging environment pointing at the same database, and you are at 151. The useful number is not the maximum but the concurrency you actually need. A pool only needs as many connections as there are queries running at the same instant. If each request spends 5 ms in the database and an instance serves 200 requests per second, that is one second of database time per second - about one busy connection. Ten is generous. The widely quoted starting point from the HikariCP project is `connections = (cores x 2) + effective spindle count` for the database server, and on a 2 vCPU database that suggests a total in the single digits to low tens across all clients - smaller than most people expect, and faster, because a database with fewer concurrent queries spends less time switching between them. ```diagram title: Every process brings its own pool web1 = Web instance 1 | pool of 10 web2 = Web instance 2 | pool of 10 worker = Queue worker | pool of 5 cron = Cron scripts | 1 each, short-lived mysql = MySQL 8.4 | max_connections 151 web1 -> mysql : up to 10 web2 -> mysql : up to 10 worker -> mysql : up to 5 cron -> mysql : bursts ``` Write the sum down somewhere next to `max_connections` and keep 20% headroom for migrations, a shell and the occasional backup. #### Pool settings per runtime Each runtime's defaults, and what to change. ##### PHP PHP-FPM has no shared pool. Each FPM worker is a separate process that opens a connection per request and closes it at the end, so the number of connections is bounded by `pm.max_children`. A pool with `pm.max_children = 50` can open 50 connections. That is usually fine; connections are cheap to open over a short network path. `PDO::ATTR_PERSISTENT => true` keeps one connection per worker open between requests - it saves the handshake but leaves session state (temporary tables, `SET` variables, an open transaction after a fatal error) for the next request, and turns idle workers into idle connections. Use it only if you measured the handshake cost. [PHP PDO and MySQL](https://www.renode.space/blog/php-pdo-and-mysql) covers the connection options in detail. ##### Node.js ```js import mysql from "mysql2/promise"; export const pool = mysql.createPool({ host: process.env.DB_HOST, port: Number(process.env.DB_PORT), user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, connectionLimit: 10, // default 10 waitForConnections: true, // queue instead of failing queueLimit: 0, // 0 = unbounded queue maxIdle: 5, // idle connections kept open idleTimeout: 60000, // ms before an idle connection is closed enableKeepAlive: true, }); ``` Create the pool once per process, at module level. The classic leak is calling `createPool` inside a request handler, which creates a new pool of ten for every request. In serverless deployments each function instance gets its own pool, so set `connectionLimit` low (1-2) there. ##### Python SQLAlchemy's engine holds the pool: `pool_size=5` and `max_overflow=10` by default, so up to 15 per process. Add `pool_recycle` below `wait_timeout` and `pool_pre_ping=True`, which tests a connection before handing it out. ```python from sqlalchemy import create_engine engine = create_engine( "mysql+pymysql://app:secret@db.example.com:3306/app?charset=utf8mb4", pool_size=5, max_overflow=5, pool_recycle=540, pool_pre_ping=True, ) ``` Gunicorn with 4 workers and that engine can open 40. Django does not pool at all: `CONN_MAX_AGE` defaults to `0`, meaning a new connection per request; set it to a value below `wait_timeout` (and `CONN_HEALTH_CHECKS = True`) to reuse connections per worker thread. ##### Java and .NET HikariCP defaults to `maximumPoolSize = 10` and `maxLifetime = 1800000` ms (30 minutes) - which is fine with the default eight-hour `wait_timeout`, and must be lowered if you shorten it. MySqlConnector for .NET pools by default with `Maximum Pool Size=100`, which is large; put `Maximum Pool Size=20;Connection Lifetime=540` in the connection string for a small server. Go's `database/sql` has no limit on open connections unless you call `SetMaxOpenConns`, and keeps two idle by default; always set `SetMaxOpenConns`, `SetMaxIdleConns` and `SetConnMaxLifetime`. [Connection pools and limits](https://www.renode.space/blog/connection-pools-and-limits) covers the same ideas from the PostgreSQL side, where connections are processes and the arithmetic is stricter still. #### Troubleshooting the errors you will actually see **`ERROR 1040: Too many connections`.** The global limit is reached. Log in as root, run the processlist query above, and find the client holding most of them. Kill the sleepers if you need the application back immediately, then fix the pool size or the leak. Raise `max_connections` only if the sum of legitimate pools genuinely exceeds it and memory allows. **`ERROR 1203: User already has more than 'max_user_connections' active connections`.** A per-account cap. Either the cap is too low for the pools using that account, or one process is leaking. **`MySQL server has gone away` (2006) or `Lost connection to MySQL server during query` (2013) after idle periods.** The server closed an idle connection at `wait_timeout` and the pool handed it out anyway. Set the pool's maximum lifetime below `wait_timeout`, or enable a pre-ping. If it happens during a large insert, the other classic cause is a packet larger than `max_allowed_packet` (64 MB by default in 8.4). **`Host 'x' is blocked because of many connection errors`.** After `max_connect_errors` (default 100) consecutive failed handshakes from one host, MySQL blocks it. Usually a health check that opens a TCP connection and closes it without logging in. `FLUSH HOSTS` was removed in favour of `TRUNCATE TABLE performance_schema.host_cache`, which clears the block; then fix the health check. **The pool times out waiting for a connection, but MySQL shows few connections.** The pool is too small for the concurrency, or connections are held across slow work - an HTTP call made while a transaction is open, for instance. Release connections before doing anything that is not a query. **Connections climb slowly all day.** A leak: code that acquires a connection and returns early on an error path without releasing it. In Node, use `pool.query()` instead of `getConnection()` where you can, because it releases automatically. #### FAQ ##### What should max_connections be? High enough for the sum of your pools plus 20% headroom, and low enough that every connection running a moderately heavy query at once would still fit in memory. For most applications on a 1-4 GB MySQL server that is somewhere between 100 and 300. The default of 151 is reasonable for a single application. ##### Is a connection pool always faster? For anything that runs more than a handful of queries per second, yes - it removes several network round trips from each request. For a cron script that runs once an hour, a pool adds nothing; open one connection, do the work, close it. ##### Do I need ProxySQL or another connection proxy? Only when you have more clients than a sensible `max_connections` can serve - hundreds of serverless functions, or dozens of instances each with a pool. A proxy multiplexes many client connections onto a few server connections. For one to five application servers, correctly sized pools are simpler and enough. ##### Why does my database show hundreds of sleeping connections? Each one is a connection a client opened and is not using right now. That is normal in moderation - it is what a pool looks like when traffic is low. Hundreds from one source means the pool's maximum is too large or connections are never returned. Lowering `wait_timeout` cleans up after careless clients but does not fix them. ##### Does wait_timeout affect long-running queries? No. It applies to idle connections only. A query that runs for twenty minutes is not idle. Long queries are limited by `max_execution_time` for `SELECT` statements (in milliseconds, `0` by default, meaning no limit) and by the client's own read timeout. MySQL 8.4 LTS on RE:NODE comes with a root password, an application database and an application user generated for the server, so you can set `max_connections` and per-account limits yourself. Plans run from 1 GB to 8 GB of memory on NVMe, reached on the plan's host and port. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL GUI clients: Workbench vs DBeaver vs HeidiSQL Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/mysql-gui-clients-workbench-dbeaver-heidisql For most people the answer is DBeaver Community: free, runs on Windows, macOS and Linux, handles MySQL 8.4 without fuss, and the same tool works for PostgreSQL and SQL Server later. HeidiSQL is the faster, lighter choice if you are on Windows and mostly browse and edit data. MySQL Workbench is Oracle's own tool and the one to pick when you want its schema modelling and visual query plans, at the price of being slower and fussier than the others. All three connect to a remote MySQL server with the same five details - host, port, user, password and database - and fail for the same few reasons, which this post covers along with the setup for each. #### What you need before you open any client Every client asks for the same things. Collect them first: | Field | Example | Where it comes from | | --- | --- | --- | | Host | `db.example.com` or an IP | Your database server's address | | Port | `3306`, or the port your plan allocates | Not always 3306 on hosted servers | | User | `app` | An account you created, or the generated application user | | Password | - | Generated with the account | | Database | `app` | Optional, but saves a click | Use the application user, or better a separate account made for humans, rather than root. A GUI makes it very easy to run `DELETE` against the wrong tab, and an account that can only touch the application database limits how much one wrong click can do. [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges) shows how to make a read-only account for browsing, which is the right default for anyone who only needs to look. Two server-side facts about MySQL 8.4 decide whether a client connects at all: - **Authentication.** Accounts use `caching_sha2_password`. The older `mysql_native_password` plugin is disabled by default in 8.4. A client built on an old client library - roughly anything older than MySQL 8.0's - cannot authenticate and fails with `Authentication plugin 'caching_sha2_password' cannot be loaded`. Current versions of all three clients here support it. - **TLS.** The server enables TLS by default with a certificate it generates itself. Clients can encrypt with it, but cannot verify it against a public certificate authority unless you give them the server's CA file. Check the connection from a terminal first if you can. If `mysql -h db.example.com -P 3306 -u app -p app` works, the server and network are fine and any GUI problem is a client setting. [Connecting to MySQL remotely](https://www.renode.space/blog/mysql-remote-connections-getting-started) goes through the command-line client and SSL modes in detail. If you have no MySQL client installed, you can still test whether the port is reachable at all, which separates network problems from account problems before any GUI is involved: ```bash # Windows PowerShell Test-NetConnection db.example.com -Port 3306 # macOS and Linux $ nc -vz db.example.com 3306 ``` `TcpTestSucceeded : True` on Windows, or `succeeded` / `open` from `nc`, means something is listening and your network lets you reach it. A timeout means a firewall is dropping the traffic - often an office or school network that blocks unusual outgoing ports - and no client setting will help. "Connection refused" means the host answered but nothing listens on that port, which almost always means the wrong port number. Once this test passes, any remaining failure is between the client and MySQL itself, and the error table further down covers those. #### The three compared | | DBeaver Community | HeidiSQL | MySQL Workbench | | --- | --- | --- | --- | | Platforms | Windows, macOS, Linux | Windows | Windows, macOS, Linux | | Licence | Free, open source | Free, open source | Free (GPL), by Oracle | | Built on | Java, JDBC drivers | Native, MySQL/MariaDB client libraries | Native, MySQL client library | | Other databases | PostgreSQL, SQL Server, SQLite, many more | MariaDB, PostgreSQL, SQL Server, SQLite | MySQL only | | Best at | One tool for everything, ER diagrams | Speed, quick edits, exports | Modelling, visual EXPLAIN, admin screens | | Weak at | Startup time, memory use | Not cross-platform | Stability on large results, MySQL only | None of the three is wrong. The trade is breadth (DBeaver), speed (HeidiSQL) or MySQL-specific depth (Workbench). Many people end up with two: HeidiSQL or DBeaver for daily work and Workbench open for the occasional ER diagram or plan. #### Connecting with DBeaver 1. Click the plug icon with a plus (New Database Connection), choose **MySQL**, and click Next. 2. On the Main tab set **Server Host**, **Port**, **Database**, **Username** and **Password**. Leave Authentication as Database Native. Tick Save password only on a machine you trust. 3. Click **Test Connection**. The first time, DBeaver offers to download the MySQL JDBC driver (Connector/J); accept it. 4. Click Finish. The connection appears in the Database Navigator. DBeaver uses the Java driver, not the MySQL C library, and that driver has one error everybody meets on MySQL 8: `Public Key Retrieval is not allowed`. With `caching_sha2_password`, a client logging in over an unencrypted connection needs the server's RSA public key to send the password safely, and Connector/J refuses to ask for it unless told to. Two fixes, on the **Driver properties** tab: - **Encrypt the connection**: set `useSSL` to `true` (and `requireSSL` to `true` if you want to insist). The password then travels inside TLS and no key retrieval is needed. This is the better fix. - **Allow key retrieval**: set `allowPublicKeyRetrieval` to `true`. It works, but a machine in the middle could hand you its own key, so prefer TLS across the internet. DBeaver driver properties: ```properties useSSL=true requireSSL=true verifyServerCertificate=false allowPublicKeyRetrieval=false ``` `verifyServerCertificate=false` encrypts without checking who answered, which is what you get against a self-generated server certificate. If you have the server's CA certificate, use the **SSL** tab instead to supply it and verify. The **SSH** tab tunnels the connection through a server you have shell access to, which is the way to reach a database that is not exposed to the internet. Useful habits in DBeaver: set the connection type (Edit Connection, General) to Production for live databases, which colours the editor so you know where you are and can ask for confirmation before executing statements; `Ctrl+Enter` runs the statement under the cursor and `Alt+X` the whole script; and the result viewer fetches 200 rows at a time by default, so a fast-looking query may not have fetched everything. #### Connecting with HeidiSQL 1. Open the Session manager and click **New**, then name the session. 2. Set **Network type** to the MySQL or MariaDB TCP/IP option. 3. Fill in **Hostname / IP**, **User**, **Password** and **Port**. The **Databases** field takes a semicolon-separated list to show; leave it blank to see everything the user can access. 4. Click **Open**. On the **SSL** tab, tick Use SSL to encrypt the session, and supply a CA certificate if you have one for verification. The **Advanced** tab includes the client library HeidiSQL loads. If a session fails with `caching_sha2_password cannot be loaded`, switch that library to a MySQL 8 or newer `libmysql` from the list - older bundled libraries predate the plugin - or update HeidiSQL itself. What HeidiSQL is good at is speed: it opens instantly, edits grid data in place, and its Tools menu has **Export database as SQL**, which writes a dump of chosen tables or databases to a file, another server, or the clipboard. For a large database a real `mysqldump` is still the safer backup - see [mysqldump backup and restore](https://www.renode.space/blog/mysqldump-backup-and-restore) - but for copying a few tables between servers HeidiSQL is hard to beat. HeidiSQL is primarily a Windows program. People run it on Linux and macOS under Wine with mixed results; on those systems, use DBeaver. #### Connecting with MySQL Workbench 1. On the home screen, click the plus beside **MySQL Connections**. 2. Give it a **Connection Name**, keep **Connection Method** as Standard (TCP/IP). 3. Set **Hostname**, **Port** and **Username**. Click **Store in Vault** (Windows, macOS) or Store in Keychain (Linux) to save the password. 4. Optionally set **Default Schema** to your database. 5. On the **SSL** tab, the **Use SSL** setting has five values: No, If available, Require, Require and Verify CA, Require and Verify Identity. Against a remote server with a self-generated certificate, use Require; with the CA file, use Require and Verify CA. 6. Click **Test Connection**, then OK. Workbench has three defaults worth knowing before they surprise you: | Setting | Default | Effect | | --- | --- | --- | | Safe Updates | On | `UPDATE` and `DELETE` without a key in `WHERE` fail with error 1175 | | Limit Rows | 1000 | Workbench appends `LIMIT 1000` to `SELECT`s in the editor | | DBMS connection read timeout | 30 seconds | Long queries fail with `Lost connection to MySQL server during query` | All three live under Edit, Preferences, SQL Editor (and SQL Execution for row limits). Safe Updates is worth keeping on - error 1175 has saved many tables. The read timeout is the one to raise if you run long reports or `ALTER TABLE`s from Workbench; the query was fine, Workbench simply stopped waiting. Where Workbench earns its place: **Visual Explain** draws the execution plan of a query with costs, which is a good way to learn to read plans (see [MySQL indexes and EXPLAIN](https://www.renode.space/blog/mysql-indexes-and-explain)); **Database, Reverse Engineer** builds an ER diagram from a live schema; and the **Users and Privileges** screen is a readable view of grants. Its **Data Export** runs a bundled `mysqldump`, which must be at least as new as the server - a mismatch warning there is worth heeding. Some Workbench releases show a warning about an incompatible or non-standard server version when they connect to a newer server than they were tested with. It is a warning; you can continue, and day-to-day querying works. #### The errors that stop every client The client changes; the causes do not. | Error | Meaning | Fix | | --- | --- | --- | | `Can't connect to MySQL server on 'host' (10060)` or `(111)`, error 2003 | Nothing answering on that host and port | Check host, port and any firewall between you | | `Access denied for user 'app'@'203.0.113.5'`, error 1045 | Wrong password, or no account matching your address | Check the password; check the account's host part | | `Host '203.0.113.5' is not allowed to connect`, error 1130 | No account at all for your address | Create the account for `'%'` or your address | | `Authentication plugin 'caching_sha2_password' cannot be loaded` | Client library too old | Update the client or pick a newer library | | `Public Key Retrieval is not allowed` | JDBC client, no TLS | Enable TLS, or allow key retrieval | | `SSL connection error` | TLS required by one side, refused or unverifiable by the other | Match the SSL mode; supply the CA or relax verification | Error 2003 versus 1045 is the most useful distinction in that table. 2003 means you never reached MySQL - a network problem. 1045 means MySQL answered and said no - an account problem. The account side is explained in [MySQL users and privileges](https://www.renode.space/blog/mysql-users-and-privileges): MySQL identifies an account by user name and client host together, so `'app'@'localhost'` cannot log in from your laptop however correct its password is. #### Working safely from a GUI A graphical client changes the risk, not just the convenience. A few habits prevent most accidents: - **Separate connections for production and everything else**, named and coloured differently. Most "I ran it on the wrong database" stories involve two tabs that looked the same. - **Mind autocommit.** All three clients default to autocommit, but all three let you switch to manual commit. In manual mode, every `UPDATE` you run holds its row locks until you click Commit - and the application waits behind you. A forgotten uncommitted edit in a GUI is a classic cause of lock wait timeouts; [MySQL transactions, locking and deadlocks](https://www.renode.space/blog/mysql-transactions-locking-and-deadlocks) shows how to find one. - **Grid edits are SQL.** Editing a cell and saving runs an `UPDATE` with a `WHERE` built from the primary key. On a table without a primary key, some clients build the `WHERE` from every column, and a duplicate row means one edit changes two rows. Give every table a primary key. - **Big result sets live in your machine's memory.** `SELECT * FROM events` on fifty million rows can freeze the client long before the server minds. Add a `LIMIT` while exploring. - **Close what you are not using.** Each open tab may hold a connection, and idle GUI sessions count against `max_connections` like anything else. #### Other clients worth knowing The three above are not the only options: - **mysql and MySQL Shell (`mysqlsh`)** - the command-line clients. Always available, scriptable, and the reference for whether a connection problem is the server's or the GUI's. - **Sequel Ace** - free, open source, macOS only, MySQL and MariaDB. The natural choice on a Mac if you find DBeaver heavy. - **TablePlus** - native, fast, multi-database, commercial with a limited free version. - **DataGrip** - JetBrains' paid database IDE, with the best SQL completion of the lot. Also built into their other IDEs as the database tool window. - **Beekeeper Studio** - open-source, cross-platform, simple; a commercial edition adds features. - **phpMyAdmin and Adminer** - web-based, running on a PHP host. Useful when you cannot install anything locally; they connect from the web server, not your machine. [The phpMyAdmin import and export guide](https://www.renode.space/blog/phpmyadmin-import-export-guide) covers the import side. #### FAQ ##### Can I use MySQL Workbench with MariaDB? It often connects, but Workbench is built for MySQL and some screens misreport or break on MariaDB as the two have diverged. HeidiSQL and DBeaver treat MariaDB as a first-class target. For a MySQL 8.4 server, all three are fine. ##### Why does the client connect from home but not from the office? Either the office network blocks outgoing connections to the port, or the database account or a firewall only allows certain addresses. Error 2003 suggests the network; error 1045 or 1130 with the office address in it suggests the account's host pattern. ##### Is it safe to save the password in the client? On your own encrypted machine, reasonably. Workbench stores it in the operating system's keychain; DBeaver and HeidiSQL store it in their own configuration, which is less protected. For production, consider not saving it, or using a read-only account for the saved connection. ##### Do I need TLS if I connect over the internet? Yes. Without it, every query and result crosses the network in plain text, and on older authentication paths so can credentials. All three clients support TLS; turning it on is one setting. ##### Which client is best for importing a large SQL dump? None of them. GUI imports load the file through the client and are slow or fail on dumps over a few hundred megabytes. Use the command-line client: `mysql -h host -P port -u user -p dbname < dump.sql`. A MySQL 8.4 LTS server on RE:NODE is reached on the plan's host and port from any of these clients. The root password, the application database and the application user are generated when the server is created, so the connection details are ready the moment it is. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL indexes and EXPLAIN: reading plans, fixing queries Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/mysql-indexes-and-explain Most slow MySQL queries are slow for one reason: the server reads far more rows than it returns, because no index lets it go straight to the ones it needs. The fix is an index whose columns match how the query filters and sorts - equality columns first, then the range or sort column - and the tool that tells you whether you got it right is `EXPLAIN`. Look at the `type` column (`ALL` means a full table scan), the `key` column (which index was chosen, if any), `rows` (how many rows MySQL expects to examine) and `Extra` (`Using filesort` and `Using temporary` are the expensive ones). Then run `EXPLAIN ANALYZE` to see what actually happened rather than what the optimizer guessed. This guide explains how InnoDB stores tables and indexes, why column order in a composite index decides whether it is used, the patterns that make MySQL ignore an index you have, and how to read both forms of `EXPLAIN`, ending with a worked example. It applies to MySQL 8.0 and 8.4 LTS. #### How InnoDB stores a table An InnoDB table is a B+tree ordered by its primary key. The leaf pages of that tree hold the full rows. This is the clustered index, and it is not optional: if you declare no primary key, InnoDB uses the first `UNIQUE NOT NULL` index, and if there is none, it invents a hidden 6-byte row ID called `GEN_CLUST_INDEX` that you cannot use in queries. Every other index is a secondary index: a separate B+tree ordered by the indexed columns, whose leaves hold those columns plus the primary key value. Looking up a row through a secondary index is therefore two searches - one through the secondary index to find the primary key, then one through the clustered index to fetch the row. Three practical consequences follow: - **Keep the primary key small.** It is copied into every secondary index. A `BIGINT` is 8 bytes; a UUID stored as `CHAR(36)` is at least 36 bytes in every index entry, and the index reserves room for four bytes per character in `utf8mb4`. - **Sequential primary keys insert cheaply.** An `AUTO_INCREMENT` key appends to the end of the tree. Random UUIDs insert all over it, splitting pages and fragmenting the cache. If you need UUIDs, store them as `BINARY(16)` with `UUID_TO_BIN(UUID(), 1)`, which reorders the time component so values are roughly sequential. - **A query that only needs indexed columns can skip the second lookup.** That is a covering index, shown as `Using index` in `EXPLAIN`, and it is often the biggest single win available. Always give tables an explicit primary key. MySQL 8 can enforce it with `sql_require_primary_key=ON`, and replication and many tools behave badly without one. #### Composite indexes and the leftmost prefix A composite index on `(a, b, c)` is sorted by `a`, then by `b` within each `a`, then by `c`. Like a phone book sorted by surname then first name, it is useful for any search that starts from the left. | Query condition | Uses `(a, b, c)`? | How | | --- | --- | --- | | `WHERE a = 1` | Yes | Prefix `a` | | `WHERE a = 1 AND b = 2` | Yes | Prefix `a, b` | | `WHERE a = 1 AND b = 2 AND c > 5` | Yes | All three; `c` as a range | | `WHERE a = 1 AND c = 3` | Partly | `a` only, then filters `c` (index condition pushdown) | | `WHERE b = 2` | Usually not | No leading column; skip scan sometimes helps | | `WHERE a > 1 AND b = 2` | Partly | Range on `a` stops `b` being used for seeking | | `WHERE a = 1 ORDER BY b` | Yes | Rows come out already sorted by `b` | The rule that follows: **equality columns first, then one range or sort column, last.** Once the index hits a range condition, the columns after it cannot be used to narrow the search, only to filter. Of several equality columns, order matters less than people think; put the one shared by the most queries first so the index serves them all. MySQL 8.0.13 added a skip scan, which can use `(a, b)` for `WHERE b = 2` when `a` has very few distinct values, by searching once per `a`. It shows as `Using index for skip scan`. It is a rescue, not a design. Selectivity matters for the first column less than old advice suggests. What matters is that the index matches the query's shape. An index on a low-cardinality column alone (a `status` with three values) is rarely useful; the same column as the first part of `(status, created_at)` for "the latest paid orders" is excellent. #### When MySQL ignores your index You created the index and `EXPLAIN` still says `type: ALL`. The usual causes: ```sql -- A function on the column: the index holds email, not LOWER(email) SELECT * FROM users WHERE LOWER(email) = 'ana@example.com'; -- Implicit conversion: phone is VARCHAR, the literal is a number SELECT * FROM users WHERE phone = 5551234; -- Leading wildcard: the index is sorted from the first character SELECT * FROM products WHERE name LIKE '%phone%'; -- Arithmetic on the column SELECT * FROM orders WHERE created_at + INTERVAL 1 DAY > NOW(); -- Date functions instead of a range SELECT * FROM orders WHERE YEAR(created_at) = 2026; ``` The fixes, in order: rewrite the condition so the column stands alone (`created_at > NOW() - INTERVAL 1 DAY`, `created_at >= '2026-01-01' AND created_at < '2027-01-01'`), quote string literals so they match the column type, and for searches inside text use a `FULLTEXT` index rather than `LIKE '%...%'`. When the function is unavoidable, MySQL 8.0.13 and later support functional indexes - note the double parentheses: ```sql CREATE INDEX idx_users_email_lower ON users ((LOWER(email))); ``` Two less obvious causes. Joining columns with different character sets or collations prevents index use on the converted side, which is a common result of a half-finished [utf8mb4 migration](https://www.renode.space/blog/mysql-utf8mb4-and-collations). And the optimizer may correctly decide that a full scan is cheaper: if a condition matches 40% of a table, reading the table sequentially beats tens of thousands of index lookups. Do not force an index in that case; fix the query to need fewer rows. #### EXPLAIN, column by column ```sql EXPLAIN SELECT id, total FROM orders WHERE customer_id = 4821 AND status = 'paid' ORDER BY created_at DESC LIMIT 20\G ``` ```text *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_customer key: idx_customer key_len: 8 ref: const rows: 2310 filtered: 10.00 Extra: Using where; Using filesort ``` Ending a statement with `\G` instead of `;` in the `mysql` client prints each row vertically, which is far easier to read for `EXPLAIN`. | Column | What it tells you | | --- | --- | | `type` | How rows are found. Best to worst: `const`, `eq_ref`, `ref`, `range`, `index`, `ALL` | | `possible_keys` | Indexes the optimizer considered | | `key` | The index it chose. `NULL` means none | | `key_len` | Bytes of the index used - shows how many columns of a composite index were used | | `ref` | What the index is compared to: `const`, a column from another table | | `rows` | Estimated rows to examine, per loop | | `filtered` | Estimated percentage left after the remaining conditions | | `Extra` | Everything else, and where the warnings live | The `type` values in plain language: `const` is a single row by primary key or unique index; `eq_ref` is one row per row of the previous table in a join; `ref` is several rows matching an equality on a non-unique index; `range` is an index range (`BETWEEN`, `>`, `IN`); `index` reads the whole index; `ALL` reads the whole table. `index` is not the good news it sounds like - it is a full scan of the index instead of the table. The `Extra` notes worth recognising: - `Using index` - covering: answered from the index alone. Good. - `Using index condition` - index condition pushdown, filtering inside the index before fetching rows. Good. - `Using where` - rows are filtered after being read. Normal, but with `rows` high it means waste. - `Using filesort` - results are sorted after reading, in memory or on disk. Expensive on many rows. - `Using temporary` - a temporary table was built, usually for `GROUP BY` or `DISTINCT`. - `Using join buffer (hash join)` - a hash join, used since 8.0.18 when a join has no usable index. A sign the join column needs one. In the example, the plan found 2310 orders for the customer through `idx_customer`, then filtered by status (estimated 10% survive) and sorted them all to return 20. That works, but it reads a hundred times more rows than it returns. `key_len` is the quiet detail that answers "is my composite index fully used?". An `INT` is 4 bytes, a `BIGINT` 8, a nullable column adds 1, a `VARCHAR(n)` in `utf8mb4` counts `4 × n + 2`. If `key_len` covers only the first column of a three-column index, the other two are not being used for the search. #### EXPLAIN ANALYZE and the tree format `EXPLAIN` shows estimates. `EXPLAIN ANALYZE`, added in 8.0.18, actually runs the query and reports what happened at every step, in the tree format: ```sql EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 4821 AND status = 'paid' ORDER BY created_at DESC LIMIT 20; ``` ```text -> Limit: 20 row(s) (cost=520 rows=20) (actual time=9.84..9.85 rows=20 loops=1) -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk (cost=520 rows=2310) (actual time=9.84..9.84 rows=20 loops=1) -> Filter: (orders.`status` = 'paid') (cost=520 rows=231) (actual time=0.11..9.52 rows=1874 loops=1) -> Index lookup on orders using idx_customer (customer_id=4821) (cost=520 rows=2310) (actual time=0.10..9.21 rows=2310 loops=1) ``` Read it from the innermost line outwards. Each step shows the estimate (`cost`, `rows`) and the reality (`actual time` as first-row..all-rows in milliseconds, `rows`, `loops`). Two things to look for: - **Estimates far from reality.** Here the filter was expected to keep 231 rows and kept 1874. Large gaps mean the optimizer is planning with bad statistics, and may pick the wrong plan for other values. `ANALYZE TABLE orders;` refreshes index statistics; for unindexed columns with skewed values, a histogram helps: `ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;`. - **Where the time goes.** Multiply time by `loops` for nested steps. The step whose actual time jumps is where the work is. `EXPLAIN ANALYZE` executes the query, including any side effects of functions it calls, and it takes as long as the query does. For `UPDATE` and `DELETE`, use plain `EXPLAIN`, or run the equivalent `SELECT`. `EXPLAIN FORMAT=TREE` shows the same tree with estimates only, without running anything; `EXPLAIN FORMAT=JSON` adds cost detail. MySQL Workbench draws the JSON form as a diagram, which some people find easier on large joins. > [!TIP] > To see the plan of a query that is running right now on another connection - the one stuck in the process list - use `EXPLAIN FOR CONNECTION 1234;` with the ID from `SHOW PROCESSLIST`. It shows the plan that session is actually executing. #### Indexes for joins, GROUP BY and prefixes Joins follow the same logic one table at a time. MySQL reads the first table in the plan, then, for each row, looks up matching rows in the next one. That lookup needs an index on the join column of the second table - the foreign key side, typically. InnoDB creates an index automatically when you declare a `FOREIGN KEY`, which is one reason declared foreign keys rarely produce slow joins and undeclared "logical" ones often do. In `EXPLAIN`, a well-indexed join shows `eq_ref` or `ref` on the inner table; `ALL` with `Using join buffer (hash join)` means every row of one table is being compared against a hash of the other. `GROUP BY` and `DISTINCT` can be answered by walking an index in order instead of building a temporary table, when the grouped columns are a leftmost prefix of an index and any equality conditions come before them. `GROUP BY customer_id` on an index starting with `customer_id` streams; the same query on an unindexed column shows `Using temporary`. For `COUNT(*)` grouped by a column, a narrow index on that column is also covering, so the table itself is never touched. Long string columns can be indexed by prefix: `INDEX (url(100))` indexes the first 100 characters. InnoDB's limit on an index key is 3072 bytes with the default `DYNAMIC` row format, which is 768 characters of `utf8mb4`, so a `VARCHAR(1000)` cannot be indexed whole. A prefix index cannot be covering and cannot serve `ORDER BY` on the full value; for exact lookups on long values such as URLs, indexing a stored hash in a generated column is often better - [JSON and generated columns](https://www.renode.space/blog/mysql-json-and-generated-columns) shows the technique. #### A worked example The query above, again: a customer's latest paid orders. The table has an index on `customer_id` alone. The fix is an index that matches both equalities and the sort: ```sql ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE; ``` `ALGORITHM=INPLACE, LOCK=NONE` asks for an online build: reads and writes continue while the index is created, and the statement fails rather than silently locking the table if that is not possible. On a large table it still uses I/O and temporary space, so build at a quiet time. ```text -> Limit: 20 row(s) (cost=12.4 rows=20) (actual time=0.06..0.09 rows=20 loops=1) -> Index lookup on orders using idx_customer_status_created (customer_id=4821, status='paid') (reverse) (cost=12.4 rows=1874) (actual time=0.06..0.08 rows=20 loops=1) ``` Twenty rows read, no sort, no filter: the index delivers the rows in `created_at` order, read backwards for `DESC`, and the `LIMIT` stops after twenty. From ten milliseconds to under one, and more importantly the cost no longer grows with the customer's order count. Adding `total` as a fourth column would make the index covering and skip the clustered index lookups too - worth it for a query that runs on every page view, not for one that runs hourly. Now remove the index this one replaces. `(customer_id)` is a prefix of the new index, so it is redundant and only costs writes and memory. #### Keeping indexes in check Indexes are not free. Each one is updated on every `INSERT`, on `DELETE`, and on any `UPDATE` touching its columns, and each takes buffer pool space that would otherwise cache data. The `sys` schema finds the ones not earning their keep: ```sql -- Indexes not used since the server started SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'appdb'; -- Indexes made redundant by another (a prefix of a longer one) SELECT table_name, redundant_index_name, dominant_index_name FROM sys.schema_redundant_indexes WHERE table_schema = 'appdb'; ``` "Unused since start-up" is only meaningful if the server has been up through a full business cycle - an index used only by the month-end report looks unused for 29 days. Before dropping one, make it invisible, which keeps it maintained but hides it from the optimizer: ```sql ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE; -- watch for a week; if nothing slows down: ALTER TABLE orders DROP INDEX idx_customer; -- or, if something did: ALTER TABLE orders ALTER INDEX idx_customer VISIBLE; ``` Invisible indexes arrived in 8.0 and are the safest way to test a removal. MySQL 8 also supports genuinely descending indexes (`created_at DESC` in the definition), useful when a query sorts by one column ascending and another descending, which an ascending index cannot serve. Finding which queries need attention in the first place is the job of the [slow query log](https://www.renode.space/blog/mysql-slow-query-log). For the same topic on PostgreSQL, see [PostgreSQL indexes explained](https://www.renode.space/blog/postgresql-indexes-explained) and [EXPLAIN ANALYZE for slow queries](https://www.renode.space/blog/explain-analyze-slow-queries); the ideas carry over, the output format does not. #### FAQ ##### How many indexes should a table have? As many as the queries need and no more. Most tables are well served by the primary key plus two to five secondary indexes. A write-heavy table with ten indexes pays ten updates per insert; check `sys.schema_unused_indexes` and remove what nothing uses. ##### Does the order of columns in WHERE matter? No. The optimizer reorders conditions freely; `WHERE b = 2 AND a = 1` uses an index on `(a, b)` exactly as `WHERE a = 1 AND b = 2` does. The order of columns in the index definition is what matters. ##### Why does EXPLAIN show an index in possible_keys but key is NULL? The optimizer considered it and estimated a full scan to be cheaper - usually because the condition matches a large share of the table, or because statistics are stale. Run `ANALYZE TABLE` and check again; if it still prefers the scan, it is probably right, and the query needs a more selective condition. ##### Should I use FORCE INDEX? Rarely, and as a temporary measure. A forced index stays forced after the data changes and the plan becomes wrong. Fix the statistics, the index or the query first, and if a hint is needed, prefer the optimizer hint syntax `/*+ INDEX(orders idx_name) */` documented for 8.0 and later. ##### Does adding an index lock the table? Not usually in MySQL 8. Adding a secondary index to an InnoDB table is an online operation that allows reads and writes, apart from short metadata locks at the start and end. Ask for it explicitly with `ALGORITHM=INPLACE, LOCK=NONE` so the statement fails instead of locking if online is impossible. RE:NODE runs MySQL 8.4 LTS on NVMe, with a root password, application database and application user generated per server, so `EXPLAIN ANALYZE`, `sys` schema views and online index builds are all available from day one. Plans start at 1 GB of memory. [See MySQL plans](https://www.renode.space/database-hosting#mysql) --- ### MySQL InnoDB tuning for 1-8 GB servers Published 2026-10-08 by RE:NODE Team. https://www.renode.space/blog/mysql-innodb-tuning-for-small-servers On a MySQL server with 1 to 8 GB of memory, three settings decide most of the outcome: `innodb_buffer_pool_size`, `max_connections` and `innodb_redo_log_capacity`. On MySQL 8.4 there are two more that small servers trip over and big ones never notice: `temptable_max_ram`, whose new default is never less than 1 GB, and the binary log, which is on by default and keeps 30 days of history. Set the buffer pool to roughly half the memory on a 1-2 GB server and 60-70% above that, keep connections in the tens rather than the hundreds, size the redo log in the hundreds of megabytes, cap in-memory temporary tables, and shorten binary log retention. Everything else is either fine at its default or worth less than one missing index. The reason this matters more on a small machine is the failure mode. A large, slightly misconfigured server is slower than it could be. A small one that is misconfigured runs out of memory, and running out of memory is not a slowdown: the kernel stops the process. Tuning a small MySQL server is mostly the job of spending a fixed memory budget deliberately. #### What the defaults assume MySQL's defaults are chosen to start anywhere, not to perform anywhere. Several changed in 8.4, which is worth knowing if you are following advice written for 8.0. | Setting | 8.4 default | 8.0 default | What it is | | --- | --- | --- | --- | | `innodb_buffer_pool_size` | `128M` | `128M` | InnoDB's cache of data and index pages | | `innodb_redo_log_capacity` | `100M` | `100M` (8.0.30+) | Total size of the redo log | | `innodb_log_buffer_size` | `64M` | `16M` | Buffer for redo before it is written | | `max_connections` | `151` | `151` | Simultaneous client sessions | | `temptable_max_ram` | 3% of RAM, 1-4 GB | `1G` | Memory for internal temporary tables | | `tmp_table_size` | `16M` | `16M` | Per-table limit for in-memory temp tables | | `innodb_io_capacity` | `10000` | `200` | Background flushing rate (IOPS) | | `innodb_adaptive_hash_index` | `OFF` | `ON` | Hash index built over hot pages | | `innodb_change_buffering` | `none` | `all` | Buffering secondary index changes | | `binlog_expire_logs_seconds` | `2592000` | `2592000` | Binary log retention, 30 days | The buffer pool default of 128 MB is the clearest example of a value nobody should keep. On a 4 GB server it leaves 90% of the memory to the operating system cache, which InnoDB bypasses anyway because 8.4 defaults to `innodb_flush_method=O_DIRECT` on Linux. Data that does not fit in the buffer pool gets read from disk, and the memory you are paying for sits idle. The query cache, the subject of a great deal of old tuning advice, was removed in MySQL 8.0. If a guide tells you to set `query_cache_size`, it was written for 5.7, and the server will refuse to start with that line in its configuration. #### The memory budget Think of MySQL's memory in three parts: what is allocated once (the buffer pool, the log buffer, performance schema), what each connection can take while it runs a query, and what the operating system needs. The worst case is roughly: ```text buffer pool + log buffer (64 MB in 8.4) + performance_schema (often 100-250 MB) + max_connections x (per-thread buffers + thread stack) + in-memory temporary tables + a margin for the OS and everything else ``` The per-connection buffers are small by default - `sort_buffer_size` 256 KB, `join_buffer_size` 256 KB, `read_buffer_size` 128 KB, `read_rnd_buffer_size` 256 KB, `thread_stack` 1 MB - and are allocated only when a query needs them. That makes an idle connection cheap and a busy one a few megabytes. The danger is raising them globally: `sort_buffer_size = 64M` looks harmless until fifty connections sort at once. Raise them per session for the query that needs it: ```sql SET SESSION sort_buffer_size = 16 * 1024 * 1024; SELECT ... ORDER BY ...; -- the one report that sorts a lot ``` To see where memory actually goes on a running server, the `sys` schema summarises the performance schema's memory instruments: ```sql SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; SELECT * FROM sys.memory_global_total; ``` > [!WARNING] > Inside a container, the memory limit is the plan's, not the machine's. On RE:NODE, reaching it stops the container and restarts it clean rather than letting it swap - so an over-generous buffer pool is not a slowdown, it is a crash during your busiest hour. The console graphs memory against the limit; size against that number, and leave 20-30% of it free. #### innodb_buffer_pool_size The buffer pool caches table and index pages. A hit is a memory read; a miss is a disk read. On a dedicated database server it is the single biggest use of memory, and the usual advice of "70-80% of RAM" assumes a large machine where the remaining 20% is several gigabytes. On a small one, the fixed costs above eat a larger share, so the percentage comes down. | Server memory | `innodb_buffer_pool_size` | `innodb_redo_log_capacity` | `max_connections` | `temptable_max_ram` | | --- | --- | --- | --- | --- | | 1 GB | `384M` | `256M` | 40 | `64M` | | 2 GB | `1G` | `512M` | 60 | `128M` | | 4 GB | `2560M` | `1G` | 100 | `256M` | | 8 GB | `5632M` | `2G` | 150 | `512M` | These are starting points, not truths. A database smaller than the buffer pool does not need a bigger one - check how much data you actually have: ```sql SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC; ``` If the whole database is 300 MB, a 1 GB buffer pool holds all of it and the rest is wasted. If it is 20 GB on a 4 GB server, what matters is whether the working set - the rows touched in a normal hour - fits. The miss rate tells you: ```sql SELECT (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') AS disk_reads, (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS logical_reads; ``` `disk_reads` divided by `logical_reads` should be well under 1% on a warmed-up server. Sample it twice an hour apart and compare the differences, not the totals since start-up. A persistently high ratio on a busy system is the honest signal that the working set does not fit, and either an index is missing or the plan is too small. The buffer pool is resizable online in 8.x. The size is rounded up to a multiple of `innodb_buffer_pool_chunk_size` (128 MB) times `innodb_buffer_pool_instances`, which in 8.4 is 1 for pools of 1 GB or less and calculated from size and CPU count above that: ```sql SET PERSIST innodb_buffer_pool_size = 2560 * 1024 * 1024; SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status'; ``` Resizing takes a little while and briefly blocks access to pages being moved; do it at a quiet time. `innodb_dedicated_server=ON` tells MySQL to size the buffer pool (50% of memory between 1 and 4 GB, 75% above) and redo log itself. It sizes from the memory it detects, and the documentation does not promise that detection honours a container's limit, so in a container set the values explicitly instead. #### innodb_redo_log_capacity Every change to InnoDB data is written to the redo log first and flushed to the data files later, at checkpoints. A redo log that is too small forces frequent checkpoints, which means bursts of writes and stalls when a write-heavy moment fills it. Too large wastes disk and lengthens crash recovery. Since MySQL 8.0.30 the redo log is sized by one variable, `innodb_redo_log_capacity`, and it is dynamic. The older pair `innodb_log_file_size` and `innodb_log_files_in_group` are deprecated; if they are set and `innodb_redo_log_capacity` is not, the server derives the capacity from them, but new configuration should use the single setting. The redo files live in `#innodb_redo` inside the data directory. ```sql SET PERSIST innodb_redo_log_capacity = 512 * 1024 * 1024; ``` The default 100 MB is too small for anything but light write loads. A useful way to size it is to measure how much redo your peak hour writes: ```sql SELECT variable_value INTO @a FROM performance_schema.global_status WHERE variable_name = 'Innodb_redo_log_current_lsn'; DO SLEEP(60); SELECT ROUND((variable_value - @a) / 1024 / 1024, 1) AS redo_mb_per_minute FROM performance_schema.global_status WHERE variable_name = 'Innodb_redo_log_current_lsn'; ``` Run that at a busy time. A capacity that holds 30 to 60 minutes of peak writes is generous. On a small disk, cap it: 2 GB of redo on a 10 GB volume is a fifth of your space. The table above stays inside that. #### Connections, threads and timeouts Every connection is a thread with its own stack and buffers. A server with one or two vCPUs cannot run more than a handful of queries at the same moment; connections beyond that wait, holding memory while they do. `max_connections = 151` on a 1 GB server is a promise the memory cannot keep if all of them become busy at once. - Size application pools first. Ten connections per application process covers most web workloads. - Add up every process, worker and cron job that connects. That total, plus a margin, is `max_connections`. - MySQL reserves one extra connection beyond the limit for an account with `CONNECTION_ADMIN`, so root can still log in to investigate a "Too many connections" incident. - `thread_cache_size` (auto-sized by default) keeps threads for reuse; leave it. ```sql SET PERSIST max_connections = 60; SHOW GLOBAL STATUS LIKE 'Max_used_connections'; SHOW GLOBAL STATUS LIKE 'Threads_connected'; ``` `Max_used_connections` is the high-water mark since start-up, which tells you whether the limit is anywhere near being reached. `wait_timeout` (28800 seconds) closes idle connections after eight hours; lowering it to 600 or so reclaims connections leaked by applications that open and abandon them, but make sure your pool recycles connections faster than that. The full argument for small limits and pools is in [MySQL connection limits and pooling](https://www.renode.space/blog/mysql-connection-limits-and-pooling). #### Temporary tables and the 8.4 temptable default Queries with `GROUP BY`, `DISTINCT`, `UNION`, derived tables or certain sorts build internal temporary tables. MySQL 8 holds them in memory with the `TempTable` engine up to `temptable_max_ram` in total, and spills to on-disk InnoDB temporary tables beyond that. In 8.4 the default `temptable_max_ram` became 3% of total memory, but never less than 1 GB and never more than 4 GB. On a 1 GB or 2 GB server, that floor means temporary tables are allowed to use as much memory as the whole plan or half of it. One bad report query can then push the server over its limit. Cap it to something your budget can absorb: ```sql SET PERSIST temptable_max_ram = 64 * 1024 * 1024; -- 1 GB server SET PERSIST tmp_table_size = 32 * 1024 * 1024; ``` Spilling to disk is slower but survivable; running out of memory is neither. The queries that build big temporary tables are marked `Using temporary` in `EXPLAIN`, and they tend to be the same ones that turn up in the [slow query log](https://www.renode.space/blog/mysql-slow-query-log). `SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable%'` shows how much the engine holds right now. #### Durability, flushing and the binary log durability settings, shown at their defaults: ```ini innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 10000 ``` `innodb_flush_log_at_trx_commit = 1` flushes the redo log to disk at every commit, so a committed transaction survives a crash. Setting it to `2` writes at commit but flushes once a second, which is a real speed-up for write-heavy loads and means a crash of the machine (not just of MySQL) can lose about a second of commits. It cannot corrupt the database. That makes it reasonable for logging and analytics, and wrong for orders and payments. `sync_binlog` is the same trade for the binary log. `innodb_io_capacity` tells InnoDB how fast it can flush in the background. 8.0's default of 200 was written for spinning disks and is far too low for NVMe; if you run 8.0, raise it to around 2000. 8.4's default of 10000 assumes fast flash, which is what [NVMe](https://www.renode.space/blog/what-nvme-actually-changes) is. The binary log needs a sentence of its own on a small disk. It is enabled by default since 8.0, records every change for replication and point-in-time recovery, and keeps 30 days. On a write-heavy database with a 10 or 20 GB volume, the binary logs can grow larger than the data. If you do not replicate and do not do point-in-time recovery, shorten retention: ```sql SET PERSIST binlog_expire_logs_seconds = 259200; -- 3 days SHOW BINARY LOGS; PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY; ``` Disabling binary logging entirely (`skip-log-bin` or `disable-log-bin`) needs a start-up option in the configuration file, not `SET PERSIST`. `expire_logs_days`, the old setting in days, was removed in 8.4. #### Applying settings: SET PERSIST and where values come from In MySQL 8 you rarely need to edit `my.cnf` to tune a server. `SET PERSIST` changes a dynamic variable now and records it in `mysqld-auto.cnf` in the data directory, which is read at start-up after the regular option files, so the change survives restarts. It needs `SYSTEM_VARIABLES_ADMIN`, which the root account has. ```sql -- Where does each value come from? SELECT v.variable_name, v.variable_source, v.set_time, g.variable_value FROM performance_schema.variables_info v JOIN performance_schema.global_variables g USING (variable_name) WHERE v.variable_name IN ('innodb_buffer_pool_size', 'max_connections', 'innodb_redo_log_capacity', 'temptable_max_ram'); -- Everything persisted so far SELECT * FROM performance_schema.persisted_variables; -- Undo a persisted setting (takes effect at next restart) RESET PERSIST temptable_max_ram; ``` `variable_source` reads `COMPILED` (the built-in default), `GLOBAL` or `SERVER` (an option file), `PERSISTED` or `DYNAMIC` (set at runtime). Variables that cannot change at runtime, such as `performance_schema` or `innodb_buffer_pool_chunk_size`, accept `SET PERSIST_ONLY`, which records the value for the next start without applying it; that needs the extra `PERSIST_RO_VARIABLES_ADMIN` privilege. On RE:NODE you get the root password for your MySQL server, so `SET PERSIST` is available for everything above. Change one setting at a time, and take a backup before any restart - restoring is a button, and backup slots come with every MySQL plan. #### Measure, then tune Configuration recovers percentages. A missing index costs orders of magnitude. Before changing anything in this post, turn on the [slow query log](https://www.renode.space/blog/mysql-slow-query-log) for a day and look at what it catches, and read the plan of the worst query with [EXPLAIN and EXPLAIN ANALYZE](https://www.renode.space/blog/mysql-indexes-and-explain). Watch the memory graph rather than the averages - [reading a server load graph](https://www.renode.space/blog/reading-a-server-load-graph) explains why the peak is the number that kills you. And if the measurements say the working set simply does not fit, the honest answer is a bigger plan: [when to upgrade your plan](https://www.renode.space/blog/when-to-upgrade-your-plan). PostgreSQL users will find the same reasoning applied there in [PostgreSQL tuning for small servers](https://www.renode.space/blog/postgresql-tuning-for-small-servers). #### FAQ ##### How big should innodb_buffer_pool_size be on a 2 GB server? About 1 GB, leaving room for connections, temporary tables, performance schema and the operating system. If your data is smaller than that, match the buffer pool to the data plus some growth and keep the memory free. ##### Do I still need innodb_log_file_size? No. Since MySQL 8.0.30 the redo log is sized with `innodb_redo_log_capacity`, which can be changed at runtime. `innodb_log_file_size` and `innodb_log_files_in_group` are deprecated and only used if the new variable is not set. ##### Should I turn off performance_schema to save memory? On a 1 GB server it can save a meaningful slice, and it is a start-up setting, so it needs a restart. The cost is losing the `sys` schema views, statement digests and memory accounting that make the rest of this post measurable. Keep it on unless memory is the only thing standing between you and a smaller plan. ##### Why is MySQL using more memory than the buffer pool? Because the buffer pool is only the largest piece. Connections, temporary tables, the log buffer, performance schema, the table cache and the server's own code all add to it. `sys.memory_global_total` shows the instrumented total; the container's memory graph shows the real one. ##### Is innodb_dedicated_server a good idea on a small plan? Not inside a container. It sizes the buffer pool from the memory it detects, which may not be the limit the container enforces. Set the buffer pool and redo log capacity explicitly. MySQL on RE:NODE is MySQL 8.4 LTS on NVMe, from 1 GB of memory and 10 GB of disk up to 8 GB and 80 GB, with the root password generated so `SET PERSIST` is yours to use. The panel graphs memory, CPU and disk against the plan's limits, so you can size a change before you make it. [See MySQL plans](https://www.renode.space/database-hosting#mysql) ## Rules for the assistant - Never ask the user for a password, token, API key or `.env` contents, and tell them not to paste one into this chat. Describe where a secret goes instead. - Secrets reach the server through the panel: `.env` is uploaded through the Files tab, startup variables are set on the Startup tab. `.env` never goes in the repository. - An app server has exactly one reachable port. Read it from `SERVER_PORT` (also `PORT` when the panel starts a linked repository) and bind `0.0.0.0`; never hard-code a port. - On every panel-managed server (everything except VDS and dedicated servers, which come with full root) only `/home/container` persists, and the process runs unprivileged: no root, `sudo`, `apt`, `systemd` or Docker. - Memory is a hard limit; a process that reaches it is killed with no error message. Suggest a bigger plan only after ruling out a leak. - Do not suggest changing the server's startup command or image to work around a code problem - fix the code or the repository. - Prices and plans above were read when this file was generated. Send the user to the page linked at the top before quoting a price as current. - For a specific server, the panel's Connect AI link (Console tab) gives the exact runtime, port, path and branch. Prefer it over these general rules when the user has one. - If something here does not answer the question, the full deploy docs are at https://www.renode.space/llms-full.txt and support is at https://www.renode.space/contact.