Fixing "Authentication plugin 'caching_sha2_password' cannot be loaded"

Posted by Kyle Hankinson July 9, 2026

You point an application at a MySQL 8 server and it fails before a single query runs:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded:
/usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file:
No such file or directory

That is the real output from connecting a MySQL 5.6 era client to a stock mysql:8.0 Docker container. Nothing is wrong with your password or your grants. The client library is simply too old to speak the authentication protocol the server now uses by default.

What changed, and in which version

MySQL 8.0 switched the default authentication plugin from mysql_native_password (a SHA-1 scheme dating back decades) to caching_sha2_password, which uses SHA-256 and requires either a secure connection or an RSA key exchange during the first login. Every user created on a default MySQL 8 server gets the new plugin. On a fresh mysql:8.0 container:

SELECT user, host, plugin FROM mysql.user;

The story then tightened twice. Here is the state per version, each verified against the current Docker images:

This matters because the internet's most common advice for this error, "just switch the user back to mysql_native_password", stopped working by default in 8.4. Running it on MySQL 8.4.10 produces:

mysql> CREATE USER 'legacyapp'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPw1!';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

MySQL 9.7 returns the same error, and the server will not even start with the --mysql-native-password=ON option that 8.4 still accepts (9.x aborts with unknown variable 'mysql-native-password=ON').

The three faces of the same problem

Depending on your stack, the failure wears different messages:

"Authentication plugin 'caching_sha2_password' cannot be loaded" means the client library predates MySQL 8.0 and has no implementation of the plugin at all. Old libmysqlclient builds, PHP before 7.4, and ancient GUI tools produce this.

"The server requested authentication method unknown to the client" is the same root cause phrased by PHP's mysqlnd and some other drivers.

"Public Key Retrieval is not allowed" comes from MySQL Connector/J. The driver understands the plugin but refuses, by default, to fetch the server's RSA public key over an insecure connection. Enabling TLS or adding allowPublicKeyRetrieval=true to the JDBC URL resolves it (the latter trades away protection against spoofing, so prefer TLS).

"Authentication requires secure connection" appears even with a current client if the channel is insecure and RSA exchange is off. Reproduced on MySQL 8.4 over plain TCP:

$ mysql -h 127.0.0.1 -u app -p --ssl-mode=DISABLED
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error:
Authentication requires secure connection.

Adding --get-server-public-key makes the same login succeed. There is one more wrinkle worth knowing: the plugin caches credentials server-side after a successful full authentication, so the insecure connection above starts working once any secure login for that user has primed the cache. If your error appears only sometimes, or only after a server restart, this cache is why.

The right fix: upgrade the client side

The durable fix is a client library from this decade, not a weaker server configuration. Minimum versions for common stacks:

• PHP: 7.4 or later. The PHP manual states caching_sha2_password is fully supported by mysqlnd as of 7.4.
• Java: Connector/J 8.x, with TLS enabled or allowPublicKeyRetrieval=true.
• C / CLI tools: any libmysqlclient or mysql client from 8.0 onward.
• Python: current releases of mysqlclient and PyMySQL both support the plugin (PyMySQL needs the cryptography package for the RSA path).
• MariaDB clients: recent MariaDB Connector/C releases speak caching_sha2_password. Testing the MariaDB 11.4 client against MySQL 8.4 succeeded, though it first failed with ERROR 2026: TLS/SSL error: self-signed certificate in certificate chain because MariaDB clients now verify server certificates by default. Against a server with a self-signed certificate you must either install a trusted certificate or pass --skip-ssl-verify-server-cert.

Desktop database clients bundle their own driver, so an outdated one fails against MySQL 8 no matter what you install system-wide. SQLPro for MySQL ships current client libraries that handle caching_sha2_password directly, and its SSH tunneling gives you a secure channel to servers that do not have TLS configured, which sidesteps the RSA requirement entirely.

The fallback, and why it is now a dead end

If you cannot upgrade a legacy client immediately, you can move individual users to the old plugin on MySQL 8.0:

ALTER USER 'legacyapp'@'%' IDENTIFIED WITH mysql_native_password BY 'a-new-password';

Caution: this changes how that account authenticates server-wide and resets its password, so every consumer of the account must be updated at the same time. Scope it to the one legacy account rather than reconfiguring the server default, and treat it as a bridge, not a destination: the same statement fails with error 1524 on 8.4 unless the server was started with --mysql-native-password=ON, and MySQL 9 removes the plugin outright. If you are creating a dedicated account for an old application anyway, our guide to creating users and granting privileges in MySQL covers the grant side.

Docker and configuration notes

On MySQL 8.0 images, the server default can be flipped at startup, which is why so many docker-compose files contain this line:

command: --default-authentication-plugin=mysql_native_password

Verified on mysql:8.0: with that flag, new users (including root) are created with mysql_native_password. On 8.4 the variable default_authentication_plugin no longer exists. Its replacement is authentication_policy, which on a stock 8.4 server reports *,, (first factor defaults to caching_sha2_password). To allow native-password accounts on 8.4 you need both --mysql-native-password=ON to load the plugin and, optionally, an authentication_policy change to make it a default. Neither option exists on 9.x.

One last cross-compatibility note: MariaDB never adopted caching_sha2_password. Its servers still default to mysql_native_password, so MySQL clients connect to MariaDB without any of this, while older MariaDB-based clients hitting a MySQL 8 server fail exactly like other legacy clients. If a connection works against MariaDB but dies against MySQL 8 with a plugin error, this default is the difference.

The MySQL manual's page on caching SHA-2 pluggable authentication documents the secure-channel rules in full, and MariaDB's authentication changes from 10.4 page covers the unix_socket side. Upgrade the client, keep caching_sha2_password on the server, and this error stays fixed through MySQL 9 instead of coming back at the next upgrade.

Tags: MySQL

SQL Window Functions: SUM, AVG, LAG, and LEAD

Posted by Kyle Hankinson January 25, 2026

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into groups like GROUP BY does. They are one of the most powerful features in modern SQL.

The OVER() Clause

Every window function uses OVER() to define which rows to include:

SELECT name, department, salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

This adds a total_salary column with the sum of all salaries — without grouping. Every row still appears individually.

PARTITION BY

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing):

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each row shows the department total and average alongside the individual salary.

ORDER BY in OVER()

Adding ORDER BY creates a running calculation:

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG and LEAD

Compare the current row to previous or next rows:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

LAG(col, n) looks back n rows (default 1). LEAD(col, n) looks forward n rows.

Default Values

Avoid NULLs for the first/last row:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- returns 0 instead of NULL

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

Percent of Total

SELECT name, department, salary,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Moving Average

Use a frame specification to average over a sliding window:

SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

Frame Types

Combining Multiple Window Functions

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_company
FROM employees
ORDER BY department, salary DESC;

WINDOW Clause (Reusable Definitions)

Avoid repeating the same OVER clause:

SELECT
    order_date, amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM orders
WINDOW w AS (ORDER BY order_date);

Supported in PostgreSQL, MySQL 8.0+, and SQLite 3.28+. Not supported in SQL Server or Oracle.

Database Compatibility

Tags: MySQL PostgreSQL Microsoft SQL Server

UNION vs UNION ALL in SQL

Posted by Kyle Hankinson November 15, 2025

UNION and UNION ALL both combine results from multiple SELECT statements, but they handle duplicate rows differently — and the performance difference can be significant.

The Difference

• UNION removes duplicate rows from the combined result (like an implicit DISTINCT)
• UNION ALL keeps all rows, including duplicates

-- Table A: 1, 2, 3
-- Table B: 2, 3, 4

SELECT id FROM table_a
UNION
SELECT id FROM table_b;
-- Result: 1, 2, 3, 4 (duplicates removed)

SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;
-- Result: 1, 2, 3, 2, 3, 4 (all rows kept)

Performance

UNION must sort or hash the entire result set to find and remove duplicates. On large datasets, this is expensive.

UNION ALL simply appends the results — no additional processing.

Rule of thumb: Use UNION ALL unless you specifically need duplicate removal. In practice, this is the majority of cases.

When UNION ALL is Correct

If the source queries cannot produce overlapping rows, deduplication is wasted work:

-- Orders from different months — no overlap possible
SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01'
UNION ALL
SELECT * FROM orders WHERE order_date >= '2025-02-01' AND order_date < '2025-03-01';

-- Different tables entirely
SELECT 'customer' AS source, name, email FROM customers
UNION ALL
SELECT 'vendor' AS source, name, email FROM vendors;

When UNION is Correct

Use UNION when you genuinely need to deduplicate:

-- Find all cities where we have customers OR offices
SELECT city FROM customers
UNION
SELECT city FROM offices;

Rules for UNION / UNION ALL

Both require:

1. Same number of columns in each SELECT
2. Compatible data types (the database will attempt implicit conversion)

-- This works
SELECT name, email FROM customers
UNION ALL
SELECT company_name, contact_email FROM vendors;

-- This fails (different column counts)
SELECT name, email, phone FROM customers
UNION ALL
SELECT company_name, contact_email FROM vendors;

Column names in the result come from the first SELECT statement.

ORDER BY with UNION

ORDER BY applies to the final combined result. Place it after the last SELECT:

SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'vendor' AS type FROM vendors
ORDER BY name;

You cannot put ORDER BY inside individual SELECT statements (except in subqueries).

UNION ALL with Aggregation

A common pattern — combine data then aggregate:

WITH all_transactions AS (
    SELECT amount, transaction_date FROM sales
    UNION ALL
    SELECT -amount, transaction_date FROM refunds
)
SELECT
    DATE_TRUNC('month', transaction_date) AS month,
    SUM(amount) AS net_revenue
FROM all_transactions
GROUP BY 1
ORDER BY 1;

Combining More Than Two Queries

You can chain multiple UNIONs:

SELECT name FROM customers
UNION ALL
SELECT name FROM vendors
UNION ALL
SELECT name FROM employees
ORDER BY name;

INTERSECT and EXCEPT

Related set operations worth knowing:

-- Rows in both queries
SELECT city FROM customers
INTERSECT
SELECT city FROM offices;

-- Rows in the first query but not the second
SELECT city FROM customers
EXCEPT            -- SQL Server, PostgreSQL, SQLite
SELECT city FROM offices;

-- Oracle uses MINUS instead of EXCEPT
SELECT city FROM customers
MINUS
SELECT city FROM offices;

MySQL 8.0.31+ supports INTERSECT and EXCEPT. Earlier versions do not.

Tags: MySQL PostgreSQL Microsoft SQL Server

Common Table Expressions (CTEs) in SQL

Posted by Kyle Hankinson July 10, 2025

A Common Table Expression (CTE) is a named temporary result set that exists for the duration of a single query. CTEs make complex queries easier to read, write, and debug.

Basic CTE Syntax

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE email LIKE '%@company.com';

The WITH clause defines the CTE, and the main query uses it like a table. The CTE only exists for that one statement.

Why Use CTEs?

Compare a nested subquery to a CTE:

-- Nested subquery (hard to read)
SELECT d.name, stats.avg_salary
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    WHERE status = 'active'
    GROUP BY department_id
) stats ON d.id = stats.department_id
WHERE stats.avg_salary > 75000;

-- CTE (much clearer)
WITH dept_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    WHERE status = 'active'
    GROUP BY department_id
)
SELECT d.name, ds.avg_salary
FROM departments d
JOIN dept_salaries ds ON d.id = ds.department_id
WHERE ds.avg_salary > 75000;

Multiple CTEs

Chain multiple CTEs with commas:

WITH
active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT au.name, uo.order_count, uo.total_spent
FROM active_users au
JOIN user_orders uo ON au.id = uo.user_id
ORDER BY uo.total_spent DESC;

Each CTE can reference the ones defined before it.

Recursive CTEs

Recursive CTEs solve hierarchical data problems like org charts, category trees, and bill-of-materials.

Employee Org Chart

WITH RECURSIVE org_chart AS (
    -- Anchor: start with the CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find each employee's direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', depth) || name AS org_tree, depth
FROM org_chart
ORDER BY depth, name;

Building a Path (Breadcrumbs)

WITH RECURSIVE category_path AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           cp.path || ' > ' || c.name
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT name, path FROM category_path ORDER BY path;

Result: Electronics > Computers > Laptops

Number Series

Generate a sequence of numbers without a table:

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

Database Compatibility

SQL Server and Oracle do not use the RECURSIVE keyword — the database detects recursion automatically.

Preventing Infinite Loops

A bug in the recursive condition can cause infinite recursion. Protect against it:

-- PostgreSQL / MySQL: limit depth
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 50  -- safety limit
)
SELECT * FROM tree;

-- SQL Server: OPTION (MAXRECURSION)
WITH tree AS (...)
SELECT * FROM tree OPTION (MAXRECURSION 100);

CTE vs Subquery vs Temp Table

Tags: MySQL PostgreSQL Microsoft SQL Server

How to Drop a Database with Active Connections

Posted by Kyle Hankinson March 20, 2025

Trying to drop a database while other sessions are connected to it will fail. Here is how to terminate those connections and drop the database safely in each database system.

PostgreSQL

The Error

ERROR: database "mydb" is being accessed by other users
DETAIL: There are 3 other sessions using the database.

Solution: Terminate Connections, Then Drop

-- Step 1: Prevent new connections
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

-- Step 2: Terminate existing connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

-- Step 3: Drop the database
DROP DATABASE mydb;

PostgreSQL 13+: DROP DATABASE FORCE

PostgreSQL 13 added a FORCE option that terminates connections automatically:

DROP DATABASE mydb WITH (FORCE);

This is the simplest approach if you are on PostgreSQL 13 or later.

Checking Active Connections

SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE datname = 'mydb';

SQL Server

The Error

Cannot drop database "mydb" because it is currently in use.

Solution: Set Single User Mode

-- Terminate all connections and switch to single-user mode
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Drop the database
DROP DATABASE mydb;

WITH ROLLBACK IMMEDIATE rolls back any in-progress transactions and disconnects all users immediately.

Alternative: Kill Connections Manually

-- Find active sessions
SELECT session_id, login_name, status, last_request_start_time
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('mydb');

-- Kill each one
KILL 52;
KILL 53;

-- Then drop
DROP DATABASE mydb;

MySQL

MySQL is more lenient — DROP DATABASE does not require exclusive access. However, active queries will continue running against the dropped tables until they finish or error out.

To cleanly terminate connections first:

-- Find connections to the database
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE DB = 'mydb';

-- Kill each connection
KILL 42;
KILL 43;

-- Drop the database
DROP DATABASE mydb;

Or in MySQL 8.0+:

SELECT CONCAT('KILL ', ID, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE DB = 'mydb';

Run the output to kill all connections at once.

Quick Reference

Safety Considerations

• Always double-check the database name before dropping. There is no undo.
• In production: Take a backup first. Use pg_dump, mysqldump, or SQL Server backup.
• In CI/CD: The PostgreSQL WITH (FORCE) option is ideal for tearing down test databases.
• Never run these commands against production databases unless you are absolutely certain. A typo in the database name can be catastrophic.

Tags: MySQL PostgreSQL Microsoft SQL Server