Database NotesRohit’s interview study guide
/
Chapter 11

Views, Stored Programs and Security

Code and rules that live inside the server: views, stored procedures and functions, triggers and scheduled events, then who may run what (users, roles, GRANT), SQL injection and server-side prepared statements, and encryption and auditing.

8 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#Views

Added

A view is a named SELECT stored in the database. It looks like a table — you can SELECT from it, join it, grant access to it — but it holds no rows of its own. Every time you query it, MySQL runs the stored query against the real tables underneath, so the view is always up to date.

The mental model is a window onto the tables. The window has a fixed frame (the columns and rows the query picks), and looking through it shows whatever is in the room right now. Move the furniture (change the base tables) and the view shows the new arrangement the next time you look. That's different from a photograph (a copy in a summary table), which is fast to look at but goes stale.

Views solve three everyday problems: they give a name to a complicated query so nobody has to rewrite the four-way join again, they provide a stable interface while the tables underneath change, and they're a security tool: you can grant someone access to a view without granting access to the tables it reads (see Users, roles and GRANT).

SQL
CREATE VIEW order_totals AS
SELECT o.id AS order_id,
       c.name AS customer,
       o.status,
       SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.status;

SELECT * FROM order_totals WHERE status <> 'cancelled' ORDER BY total DESC;
SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'ch11' AND TABLE_NAME LIKE 'order%';
Result
+----------+----------+---------+---------+
| order_id | customer | status  | total   |
+----------+----------+---------+---------+
|        3 | Maya     | shipped | 1048.00 |
|        7 | Noah     | paid    | 1016.00 |
|        1 | Rohit    | shipped |  957.00 |
|        6 | Sofia    | pending |  249.00 |
|        5 | Liam     | shipped |  127.00 |
|        8 | Maya     | shipped |  116.00 |
|        2 | Rohit    | paid    |   88.00 |
+----------+----------+---------+---------+
+--------------+------------+
| TABLE_NAME   | TABLE_TYPE |
+--------------+------------+
| order_items  | BASE TABLE |
| order_totals | VIEW       |
| orders       | BASE TABLE |
+--------------+------------+
What’s happening
  1. CREATE VIEW order_totals AS SELECT … stores the query text and its column list in the data dictionary. No rows are computed or copied at this point.
  2. SELECT * FROM order_totals WHERE status <> 'cancelled' makes MySQL run the stored join and GROUP BY now, against the current contents of orders, customers and order_items, then apply the outer WHERE and ORDER BY.
  3. Each order's total is SUM(quantity × unit_price): order 1 is 1 × 899 + 2 × 29 = 957, order 3 is 799 + 249 = 1048. Order 4 (Liam's cancelled MacBook) is filtered out by the outer WHERE.
  4. The outer query never mentions a join. Whoever writes reports can treat order_totals like a table and doesn't need to know how totals are computed.
  5. information_schema.TABLES lists the view next to the real tables with TABLE_TYPE = VIEW. That's why tools and ORMs often can't tell them apart until they try to write to one.
AdvancedA view is always current

Because a view is re-run on every query, a change to the base table shows up immediately:

SQL
UPDATE order_items SET quantity = 2 WHERE order_id = 2 AND product_id = 5;
SELECT * FROM order_totals WHERE order_id = 2;
UPDATE order_items SET quantity = 1 WHERE order_id = 2 AND product_id = 5;
Result
+----------+----------+--------+--------+
| order_id | customer | status | total  |
+----------+----------+--------+--------+
|        2 | Rohit    | paid   | 127.00 |
+----------+----------+--------+--------+
What’s happening
  1. Order 2 was one SQL Performance Explained (39) plus one Designing Data-Intensive Applications (49) = 88.
  2. The UPDATE changes the first book's quantity to 2 in the base table only. Nothing touches the view.
  3. Selecting from the view re-runs its query, so the total is now 2 × 39 + 49 = 127. There's no refresh step and no way for the view to be stale.
  4. The last UPDATE puts the quantity back so later examples use the original data.
AdvancedHow MySQL runs a view: MERGE vs TEMPTABLE

MySQL has two ways to execute a view. With the MERGE algorithm it pastes the view's definition into your query and optimises the combined statement, so indexes on the base tables work as if you'd written the join yourself. If the view contains something that can't be merged — GROUP BY, DISTINCT, aggregates, UNION, LIMIT — it uses TEMPTABLE: it materialises the view's result into an internal temporary table (a derived table) and runs your query against that. ALGORITHM = UNDEFINED (the default) lets MySQL pick MERGE when it can.

SQL
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM order_totals WHERE customer = 'Maya';
EXPLAIN FORMAT=TRADITIONAL SELECT name FROM toronto_customers WHERE id = 1;
Result
+----+-------------+------------+------------+------+---------------------+-------------+---------+-----------+------+----------+------------------------------+
| id | select_type | table      | partitions | type | possible_keys       | key         | key_len | ref       | rows | filtered | Extra                        |
+----+-------------+------------+------------+------+---------------------+-------------+---------+-----------+------+----------+------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL                | NULL        | NULL    | NULL      |    2 |   100.00 | NULL                         |
|  2 | DERIVED     | c          | NULL       | ALL  | PRIMARY             | NULL        | NULL    | NULL      |    6 |    16.67 | Using where; Using temporary |
|  2 | DERIVED     | o          | NULL       | ref  | PRIMARY,customer_id | customer_id | 4       | ch11.c.id |    1 |   100.00 | NULL                         |
|  2 | DERIVED     | oi         | NULL       | ref  | PRIMARY             | PRIMARY     | 4       | ch11.o.id |    1 |   100.00 | NULL                         |
+----+-------------+------------+------------+------+---------------------+-------------+---------+-----------+------+----------+------------------------------+
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
What’s happening
  1. FORMAT=TRADITIONAL asks for the classic table. MySQL 9.7's default explain_format is TREE, so a plain EXPLAIN prints the tree form instead.
  2. order_totals has a GROUP BY, so it can't be merged. Row id 2 is the view's own query, labelled DERIVED; row id 1 reads its result from <derived2>, the temporary table. That's TEMPTABLE.
  3. Look at c: Using where with filtered 16.67. The optimizer pushed the outer condition customer = 'Maya' down into the derived query (derived-condition pushdown, MySQL 8.0.22+), so only Maya's orders are grouped instead of all of them. Older versions computed every total first, then filtered.
  4. toronto_customers (defined in the next example) is a plain filter over one table, so MySQL merged it: the plan is just a const primary-key lookup on customers and the view disappears. A merged view costs nothing extra.
  5. The practical rule: views with aggregates are fine for reports, but filtering a big aggregated view on a column that can't be pushed down means computing the whole aggregate first. Check the plan.
AdvancedUpdatable views and WITH CHECK OPTION

A view over a single table with no aggregates, DISTINCT or GROUP BY is updatable: an UPDATE or INSERT through it changes the base table. WITH CHECK OPTION adds a guard: a write through the view must produce a row the view can still see.

SQL
CREATE VIEW toronto_customers AS
SELECT id, name, email, city, created_at FROM customers WHERE city = 'Toronto'
WITH CHECK OPTION;

UPDATE toronto_customers SET name = 'Noah K.' WHERE id = 5;
SELECT id, name, city FROM customers WHERE id = 5;
UPDATE toronto_customers SET city = 'Ottawa' WHERE id = 5;
INSERT INTO toronto_customers (name, email, city, created_at)
VALUES ('Ava', 'ava@example.com', 'Paris', NOW());
UPDATE toronto_customers SET name = 'Maya' WHERE id = 2;
SELECT ROW_COUNT();
UPDATE order_totals SET total = 0 WHERE order_id = 1;
UPDATE customers SET name = 'Noah' WHERE id = 5;
SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION, SECURITY_TYPE
FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'ch11';
Result
+----+---------+---------+
| id | name    | city    |
+----+---------+---------+
|  5 | Noah K. | Toronto |
+----+---------+---------+
ERROR 1369 (HY000): CHECK OPTION failed 'ch11.toronto_customers'
ERROR 1369 (HY000): CHECK OPTION failed 'ch11.toronto_customers'
+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+
ERROR 1288 (HY000): The target table order_totals of the UPDATE is not updatable
+-------------------+--------------+--------------+---------------+
| TABLE_NAME        | IS_UPDATABLE | CHECK_OPTION | SECURITY_TYPE |
+-------------------+--------------+--------------+---------------+
| order_totals      | NO           | NONE         | DEFINER       |
| toronto_customers | YES          | CASCADED     | DEFINER       |
+-------------------+--------------+--------------+---------------+
What’s happening
  1. Renaming Noah through the view works: Noah lives in Toronto, the view can see him, and the change lands in customers.
  2. Moving him to Ottawa would make the row vanish from the view, so WITH CHECK OPTION rejects it with ERROR 1369. Without the option the update would succeed and the row would silently leave the view — a classic way for a "Toronto admin" screen to edit customers it shouldn't own.
  3. Inserting a Paris customer through a Toronto view fails the same way. (A first attempt with a view that left out created_at failed earlier with ERROR 1423 … underlying table doesn't have a default value: an insert through a view can only fill the columns the view exposes.)
  4. Updating Maya (Mumbai) through the view isn't an error, it just matches nothing: ROW_COUNT() is 0, because the view's WHERE city = 'Toronto' is added to the update.
  5. order_totals has GROUP BY and SUM, so there's no single base row behind a total: ERROR 1288 … is not updatable. information_schema.VIEWS reports both facts up front (IS_UPDATABLE, CHECK_OPTION = CASCADED, the default, which also enforces the conditions of any views this one is built on).
AdvancedMaterialized views: MySQL has none

A materialized view stores the query's result and refreshes it on demand — the "photograph" from the mental model. PostgreSQL has them; MySQL doesn't. Here's PostgreSQL 18 in the sandbox:

SQL
CREATE TABLE orders (id int PRIMARY KEY, customer text, total numeric(10,2));
INSERT INTO orders VALUES (1, 'Rohit', 957.00), (2, 'Rohit', 88.00), (3, 'Maya', 1048.00);
CREATE MATERIALIZED VIEW revenue_by_customer AS
  SELECT customer, SUM(total) AS revenue FROM orders GROUP BY customer;
INSERT INTO orders VALUES (4, 'Maya', 116.00);
SELECT * FROM revenue_by_customer ORDER BY customer;
REFRESH MATERIALIZED VIEW revenue_by_customer;
SELECT * FROM revenue_by_customer ORDER BY customer;
Result
 customer | revenue 
----------+---------
 Maya     | 1048.00
 Rohit    | 1045.00
(2 rows)

 customer | revenue 
----------+---------
 Maya     | 1164.00
 Rohit    | 1045.00
(2 rows)
What’s happening
  1. CREATE MATERIALIZED VIEW runs the query once and stores its rows, like a table.
  2. Maya's new 116 order is inserted, but the first SELECT still shows 1048.00: the stored rows are a snapshot from creation time.
  3. REFRESH MATERIALIZED VIEW re-runs the query and replaces the rows, and Maya becomes 1164.00.
  4. In MySQL you build the same thing by hand: a real summary table plus a job that rebuilds it — see the daily_revenue example in Scheduled events — or triggers that keep it current on every write.
A view built with SELECT *
SQL
CREATE TABLE notes (id INT PRIMARY KEY, body VARCHAR(50));
INSERT INTO notes VALUES (1, 'hello');
CREATE VIEW all_notes AS SELECT * FROM notes;
ALTER TABLE notes ADD COLUMN author VARCHAR(20) DEFAULT 'rohit';
SELECT * FROM all_notes;

Does the result include author? What happens if you then drop body from the table?

Show answer

No author column, and after dropping body the view is broken.

Result
+----+-------+
| id | body  |
+----+-------+
|  1 | hello |
+----+-------+
+----------------------------------------------------------------------------------------+
| VIEW_DEFINITION                                                                        |
+----------------------------------------------------------------------------------------+
| select `ch11`.`notes`.`id` AS `id`,`ch11`.`notes`.`body` AS `body` from `ch11`.`notes` |
+----------------------------------------------------------------------------------------+
ERROR 1356 (HY000): View 'ch11.all_notes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
What’s happening
  1. When CREATE VIEW runs, MySQL expands * into the columns that exist at that moment and stores the expanded list. information_schema.VIEWS.VIEW_DEFINITION shows it: id and body, spelled out.
  2. ALTER TABLE … ADD COLUMN author changes the table, not the stored view text, so SELECT * FROM all_notes still returns only id and body.
  3. ALTER TABLE notes DROP COLUMN body succeeds — MySQL doesn't check which views use a column — and the next query on the view fails with ERROR 1356. The break shows up at query time, often in production.
  4. The lesson: list columns explicitly in views, recreate views (CREATE OR REPLACE VIEW) in the same migration that changes their tables, and search information_schema.VIEWS before dropping a column.

#Stored procedures

Added

A stored procedure is a named program that lives in the database and runs inside the server: CALL place_order(…). It can take IN, OUT and INOUT parameters, declare variables, branch with IF and CASE, loop, run any SQL including transactions, raise errors with SIGNAL, and return result sets to the caller.

Think of it as an API endpoint inside the database. The application sends one short CALL, and the several statements behind it run next to the data with no network round trip between them. The database also becomes the one place where a rule like "placing an order decrements stock" is written, whichever application calls it.

The trade-off is that procedures are written in a limited language (MySQL's SQL/PSM dialect), are harder to test, version and debug than application code, and tie you to one database. In most modern codebases they're used for specific jobs — batch maintenance, data fixes, chatty multi-statement work where round trips hurt — rather than for all business logic.

SQL
DELIMITER //
CREATE PROCEDURE place_order(
  IN  p_customer_id INT,
  IN  p_product_id  INT,
  IN  p_quantity    INT,
  OUT p_order_id    INT
)
BEGIN
  DECLARE v_price DECIMAL(10,2);
  DECLARE v_stock INT;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;            -- re-raise the original error to the caller
  END;

  START TRANSACTION;

  SELECT price, stock INTO v_price, v_stock
  FROM products WHERE id = p_product_id
  FOR UPDATE;            -- lock the product row until COMMIT

  IF v_stock < p_quantity THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Not enough stock';
  END IF;

  INSERT INTO orders (customer_id, status, ordered_at)
  VALUES (p_customer_id, 'pending', NOW());
  SET p_order_id = LAST_INSERT_ID();

  INSERT INTO order_items VALUES (p_order_id, p_product_id, p_quantity, v_price);
  UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

  COMMIT;
END //
DELIMITER ;

CALL place_order(6, 3, 2, @new_id);
SELECT @new_id;
SELECT id, name, stock FROM products WHERE id = 3;

CALL place_order(6, 4, 1, @new_id);
SELECT COUNT(*) AS orders_now FROM orders;
Result
+---------+
| @new_id |
+---------+
|       9 |
+---------+
+----+----------+-------+
| id | name     | stock |
+----+----------+-------+
|  3 | Pixel 10 |    18 |
+----+----------+-------+
ERROR 1644 (45000): Not enough stock
+------------+
| orders_now |
+------------+
|          9 |
+------------+
What’s happening
  1. DELIMITER // is a mysql client command, not SQL. The procedure body contains ;, and without it the client would send CREATE PROCEDURE to the server at the first semicolon. Drivers and GUI tools don't need it: they send the whole CREATE PROCEDURE as one statement.
  2. CALL place_order(6, 3, 2, @new_id): Zara orders two Pixel 10s. The handler is declared, the transaction starts, and SELECT … INTO … FOR UPDATE copies price 799.00 and stock 20 into local variables while locking the product row, so a concurrent order for the same phone waits instead of reading the same stock (see Locking reads: FOR UPDATE and FOR SHARE).
  3. 20 < 2 is false, so the order, its item and the stock decrement run, and COMMIT makes them permanent. p_order_id is an OUT parameter: when the procedure ends, MySQL copies it into the caller's @new_id, which is 9. Stock is 20 → 18.
  4. The second call orders the iPhone 17, whose stock is 0. SIGNAL SQLSTATE '45000' raises a user-defined error. The EXIT HANDLER FOR SQLEXCEPTION catches it, runs ROLLBACK (undoing the open transaction), then RESIGNAL re-raises the same error so the caller sees ERROR 1644 (45000): Not enough stock.
  5. orders_now is still 9: the failed call left nothing behind. Without the handler, the error would end the procedure but leave the transaction open on that connection, holding the row lock until something else commits or rolls back.
AdvancedVersion note: JavaScript routines need Enterprise
AdvancedOld vs new: procedures across versions
AdvancedA maintenance job: purge in batches

The place procedures shine is maintenance that runs entirely inside the database. Deleting millions of old rows in one DELETE holds locks for a long time, bloats the undo log and lags replicas. Deleting in small batches in a loop avoids all three:

SQL
CREATE TABLE app_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL,
  msg VARCHAR(40) NOT NULL,
  KEY (created_at)
);
SET SESSION cte_max_recursion_depth = 5000;
INSERT INTO app_log (created_at, msg)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 2500)
SELECT '2025-01-01' + INTERVAL i MINUTE, CONCAT('event ', i) FROM n;

DELIMITER //
CREATE PROCEDURE purge_log(IN p_before DATETIME, IN p_batch INT,
                           OUT p_batches INT, OUT p_deleted INT)
BEGIN
  DECLARE v_rows INT DEFAULT 1;
  SET p_batches = 0, p_deleted = 0;
  WHILE v_rows > 0 DO
    DELETE FROM app_log WHERE created_at < p_before ORDER BY created_at LIMIT p_batch;
    SET v_rows = ROW_COUNT();
    SET p_deleted = p_deleted + v_rows;
    IF v_rows > 0 THEN SET p_batches = p_batches + 1; END IF;
  END WHILE;
END //
DELIMITER ;

SELECT MIN(created_at), MAX(created_at), COUNT(*) FROM app_log;
CALL purge_log('2025-01-02 12:00:00', 1000, @batches, @deleted);
SELECT @batches, @deleted, (SELECT COUNT(*) FROM app_log) AS rows_left;
CALL purge_log('2025-01-02');
Result
+---------------------+---------------------+----------+
| MIN(created_at)     | MAX(created_at)     | COUNT(*) |
+---------------------+---------------------+----------+
| 2025-01-01 00:01:00 | 2025-01-02 17:40:00 |     2500 |
+---------------------+---------------------+----------+
+----------+----------+-----------+
| @batches | @deleted | rows_left |
+----------+----------+-----------+
|        3 |     2159 |       341 |
+----------+----------+-----------+
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE ch11.purge_log; expected 4, got 1
What’s happening
  1. The recursive CTE generates 2,500 log rows one minute apart. The first attempt without SET SESSION cte_max_recursion_depth = 5000 failed with ERROR 3636 … Recursive query aborted after 1001 iterations: the default limit is 1,000.
  2. Rows before 2025-01-02 12:00 are minutes 1 to 2,159 (36 hours is 2,160 minutes, and the series starts at minute 1), so 2,159 should go.
  3. The loop deletes at most 1,000 at a time, oldest first, using the created_at index. ROW_COUNT() returns how many the last DELETE removed: 1000, 1000, 159, then 0, which ends the loop. Three batches, 2,159 rows, 341 left.
  4. Each DELETE auto-commits on its own (there's no START TRANSACTION), so locks are held for one batch, not the whole purge. In production you'd add DO SLEEP(0.1) between batches to give replicas time to keep up.
  5. CALL purge_log('2025-01-02') shows that parameters are positional and all required: MySQL has no default parameter values, so the error lists expected 4, got 1.
AdvancedReturning result sets to the caller

Any bare SELECT inside a procedure is sent to the client as a result set, so one CALL can return several:

SQL
DELIMITER //
CREATE PROCEDURE customer_report(IN p_customer_id INT)
BEGIN
  SELECT id, name, city FROM customers WHERE id = p_customer_id;
  SELECT order_id, status, total FROM order_totals
  WHERE customer = (SELECT name FROM customers WHERE id = p_customer_id)
  ORDER BY order_id;
END //
DELIMITER ;
CALL customer_report(1);
Result
+----+-------+---------+
| id | name  | city    |
+----+-------+---------+
|  1 | Rohit | Toronto |
+----+-------+---------+
+----------+---------+--------+
| order_id | status  | total  |
+----------+---------+--------+
|        1 | shipped | 957.00 |
|        2 | paid    |  88.00 |
+----------+---------+--------+
What’s happening
  1. The first SELECT returns Rohit's profile row as result set 1.
  2. The second queries the order_totals view from the first topic and returns his two orders as result set 2. (Matching on name is only for the demo; real code would expose customer_id in the view.)
  3. The client receives both, then a final status packet for the CALL itself. In Node, mysql2 returns an array with one entry per result set plus that status packet (see mysql2: connections and pools).
  4. ORMs generally map procedures poorly — they expect one result shape per call — which is one practical reason teams keep procedures for maintenance and keep application queries in the application.

#Stored functions

Added

A stored function is a routine that returns one value and can be used anywhere an expression can: in a SELECT list, a WHERE clause, a DEFAULT, another function. Where a procedure is a command you CALL, a function is a calculator you call inline: SELECT order_total(id) FROM orders.

Functions can't return result sets, and they can't start or end transactions. MySQL also asks you to describe what the function does to data — DETERMINISTIC (same input, same output), NO SQL, READS SQL DATA or MODIFIES SQL DATA — because with binary logging on (the default since 8.0), replicas re-run whatever the binary log tells them, and a function that gives different answers on different servers could make them drift apart.

SQL
DELIMITER //
CREATE FUNCTION order_total(p_order_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
  DECLARE v_total DECIMAL(10,2);
  SELECT SUM(quantity * unit_price) INTO v_total
  FROM order_items WHERE order_id = p_order_id;
  RETURN IFNULL(v_total, 0);
END //

CREATE FUNCTION with_tax(p_amount DECIMAL(10,2), p_rate_pct DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC NO SQL
RETURN ROUND(p_amount * (1 + p_rate_pct / 100), 2) //
DELIMITER ;

SELECT id, status, order_total(id) AS total, with_tax(order_total(id), 13) AS total_with_hst
FROM orders WHERE customer_id = 1;
SELECT order_total(999) AS no_such_order;
Result
+----+---------+--------+----------------+
| id | status  | total  | total_with_hst |
+----+---------+--------+----------------+
|  1 | shipped | 957.00 |        1081.41 |
|  2 | paid    |  88.00 |          99.44 |
+----+---------+--------+----------------+
+---------------+
| no_such_order |
+---------------+
|          0.00 |
+---------------+
What’s happening
  1. order_total declares READS SQL DATA: it runs a query but doesn't change anything. For each order it sums the items into v_total and returns it: order 1 is 957.00, order 2 is 88.00.
  2. with_tax is pure arithmetic, so it's DETERMINISTIC NO SQL. Its body is a single RETURN, so it needs no BEGIN … END. Ontario's 13% HST on 957 is 957 × 1.13 = 1081.41.
  3. Functions nest like built-ins: with_tax(order_total(id), 13) calls one inside the other, once per row.
  4. For an order with no items, SUM over no rows is NULL (see Aggregate functions), and IFNULL(v_total, 0) turns it into 0.00. Whether zero or NULL is the right answer for "no such order" is a design choice; returning zero hides typos in IDs.
  5. If you leave out all of DETERMINISTIC, NO SQL and READS SQL DATA with binary logging on, CREATE FUNCTION fails with ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable).
AdvancedPitfall: lying about DETERMINISTIC
AdvancedFunctions run once per row and hide indexes

A function in a query is a black box to the optimizer. It can't see the SELECT inside, so it can't turn the per-row calls into a join, and it can't use an index on whatever the function computes. A side channel shows how often it's called:

SQL
DELIMITER //
CREATE FUNCTION counted_total(p_order_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
  SET @calls = @calls + 1;          -- side channel: count how often MySQL calls us
  RETURN order_total(p_order_id);
END //
DELIMITER ;
SET @calls = 0;
SELECT id, counted_total(id) AS total FROM orders WHERE counted_total(id) > 500 ORDER BY id;
SELECT @calls;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE order_total(id) > 500;
SELECT o.id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id HAVING total > 500 ORDER BY o.id;
Result
+----+---------+
| id | total   |
+----+---------+
|  1 |  957.00 |
|  3 | 1048.00 |
|  4 | 1299.00 |
|  7 | 1016.00 |
|  9 | 1598.00 |
+----+---------+
+--------+
| @calls |
+--------+
|     14 |
+--------+
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | customer_id | 4       | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
+----+---------+
| id | total   |
+----+---------+
|  1 |  957.00 |
|  3 | 1048.00 |
|  4 | 1299.00 |
|  7 | 1016.00 |
|  9 | 1598.00 |
+----+---------+
What’s happening
  1. There are 9 orders (order 9 is Zara's two Pixels from place_order in the previous topic). The WHERE calls the function once per order: 9 calls, and each call runs its own SELECT SUM(…) against order_items.
  2. Five orders pass, and the SELECT list calls the function again for each of them: 5 more. @calls is 9 + 5 = 14. Each call is a separate query inside the server — the database-side version of the N+1 query problem.
  3. EXPLAIN shows type = index over all 9 rows with possible_keys = NULL: there is nothing to look up by, because the condition is on a function result. On a million orders that's a million function calls.
  4. The plain join with GROUP BY and HAVING returns the same five rows in one pass over order_items that the optimizer can plan as a whole. Use functions for small, pure calculations (with_tax), and write set-based SQL for anything that reads tables.
AdvancedProcedure or function?
Stored procedureStored function
Called withCALL p(…)inside any expression: SELECT f(x)
ReturnsOUT/INOUT parameters and any number of result setsexactly one scalar value via RETURN
Transactionscan START TRANSACTION, COMMIT, ROLLBACKnot allowed
Dynamic SQL (PREPARE)allowednot allowed
Typical usemulti-step operations, maintenance jobscalculations, formatting, small lookups

#Triggers

Added

A trigger is code the server runs automatically when a row is inserted, updated or deleted in a particular table. You choose the timing (BEFORE or AFTER) and the event (INSERT, UPDATE, DELETE). Inside, OLD.col is the row before the change and NEW.col the row after it; in a BEFORE trigger you can even change NEW.col before it's written.

The mental model is a tripwire on the table. Nobody calls it; any statement that touches a row walks through it — your application, an admin in a SQL console, a batch import. That's both the attraction (an audit trail nobody can forget to write) and the danger (behaviour that's invisible when you read the application code).

In MySQL every trigger is FOR EACH ROW: an UPDATE of 1,000 rows fires the trigger 1,000 times, and the trigger runs inside the same transaction as the statement, so if the trigger fails, the statement fails and is rolled back.

SQL
CREATE TABLE price_history (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  product_id  INT           NOT NULL,
  old_price   DECIMAL(10,2) NOT NULL,
  new_price   DECIMAL(10,2) NOT NULL,
  changed_by  VARCHAR(100)  NOT NULL,
  changed_at  DATETIME      NOT NULL
);

DELIMITER //
CREATE TRIGGER products_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_history (product_id, old_price, new_price, changed_by, changed_at)
    VALUES (NEW.id, OLD.price, NEW.price, CURRENT_USER(), NOW());
  END IF;
END //
DELIMITER ;

UPDATE products SET price = price * 0.9 WHERE category_id = 2;   -- 10% off laptops
UPDATE products SET stock = stock + 5 WHERE id = 3;              -- stock only: no history row
SELECT product_id, old_price, new_price, changed_by FROM price_history;
Result
+------------+-----------+-----------+----------------+
| product_id | old_price | new_price | changed_by     |
+------------+-----------+-----------+----------------+
|          1 |    899.00 |    809.10 | root@localhost |
|          2 |   1299.00 |   1169.10 | root@localhost |
+------------+-----------+-----------+----------------+
What’s happening
  1. The sample data is reloaded first, so products are back to their original prices and stock.
  2. The laptop discount updates two rows (ThinkBook 14 and MacBook Air, category 2), so the AFTER UPDATE trigger fires twice. For the ThinkBook, OLD.price is 899.00 and NEW.price is 899 × 0.9 = 809.10; they differ, so a history row is written.
  3. The stock update on the Pixel also fires the trigger — it fires for any UPDATE of the row, whichever columns changed — but NEW.price <> OLD.price is false, so nothing is logged. MySQL has no UPDATE OF price column filter like PostgreSQL's, so you write the check yourself.
  4. The history insert is part of the same transaction as the UPDATE. If the UPDATE is rolled back, so is its history; and if the history insert failed, the price change would fail too. No price can change without a record.
  5. changed_by is CURRENT_USER(), which in a trigger is the definer of the trigger (here root@localhost), not the application account that ran the update. Use USER() for the connected account, or better, have the application pass its own user id in a column.
AdvancedPitfall: a trigger writing its own table
AdvancedBEFORE triggers: normalise and reject

A BEFORE trigger can rewrite the incoming row or refuse it with SIGNAL:

SQL
DELIMITER //
CREATE TRIGGER customers_clean_email
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  IF NEW.email NOT LIKE '%_@_%._%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
  END IF;
END //
DELIMITER ;
INSERT INTO customers (name, email, city, created_at) VALUES ('Ava', '  Ava@Example.COM ', 'Paris', NOW());
SELECT id, name, email FROM customers WHERE name = 'Ava';
INSERT INTO customers (name, email, city, created_at) VALUES ('Bad', 'not-an-email', NULL, NOW());
INSERT INTO customers (name, email, city, created_at)
VALUES ('Ok1', 'ok1@example.com', NULL, NOW()), ('Bad2', 'nope', NULL, NOW());
SELECT COUNT(*) FROM customers WHERE name IN ('Ok1','Bad2');
Result
+----+------+-----------------+
| id | name | email           |
+----+------+-----------------+
|  7 | Ava  | ava@example.com |
+----+------+-----------------+
ERROR 1644 (45000): Invalid email address
ERROR 1644 (45000): Invalid email address
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
What’s happening
  1. Ava's email arrives as ' Ava@Example.COM '. The trigger assigns to NEW.email before the row is written, so the stored value is ava@example.com. That also makes the UNIQUE index on email work case-insensitively in practice, whatever the client sends.
  2. 'not-an-email' has no @, the LIKE check fails, and SIGNAL aborts the insert with ERROR 1644 (45000) and the message you chose.
  3. The two-row insert has a good row and a bad row. The trigger fires per row, rejects Bad2, and the whole statement is rolled back, including Ok1: the count is 0. InnoDB makes each statement atomic, triggers included.
  4. Since MySQL 8.0.16 a CHECK constraint can do simple validation like this declaratively (see UNIQUE, NOT NULL, DEFAULT and CHECK); triggers remain useful when you need to change the value or check other tables.
Do cascades fire triggers?

cart_items has FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE, and both tables have an AFTER DELETE trigger that writes a line to deleted_log:

SQL
INSERT INTO carts VALUES (1, 'rohit');
INSERT INTO cart_items VALUES (1, 'A'), (1, 'B');
DELETE FROM carts WHERE id = 1;
SELECT (SELECT COUNT(*) FROM cart_items) AS items_left, what FROM deleted_log;

How many lines are in deleted_log?

Show answer

One. The two cascaded item deletes don't fire the cart_items trigger.

Result
+------------+--------+
| items_left | what   |
+------------+--------+
|          0 | cart 1 |
+------------+--------+
What’s happening
  1. DELETE FROM carts WHERE id = 1 deletes the cart row and fires carts_ad, which logs cart 1.
  2. InnoDB then applies ON DELETE CASCADE and deletes both items: items_left is 0.
  3. But cascades are performed inside the storage engine, below the SQL layer where triggers run, and MySQL documents that cascaded foreign key actions do not activate triggers. cart_items_ad never fires, so item 1/A and item 1/B are not logged.
  4. Deleting an item directly (DELETE FROM cart_items WHERE cart_id = 2) does fire it, and logs item 2/C. TRUNCATE TABLE doesn't fire DELETE triggers either (it drops and recreates the table): after a TRUNCATE, the log count stayed 0.
  5. If an audit trail must cover children, either delete the children explicitly in application code or a procedure before the parent, or put the logging on the parent. PostgreSQL implements cascades as internal triggers, so there the child triggers do fire — a real difference between the two.
AdvancedMultiple triggers and their order

Since MySQL 5.7.2 a table can have several triggers for the same timing and event; they run in creation order unless you say FOLLOWS other_trigger or PRECEDES other_trigger. information_schema.TRIGGERS shows the order:

SQL
SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_ORDER
FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'ch11'
ORDER BY EVENT_OBJECT_TABLE, ACTION_ORDER;
Result
+-----------------------+---------------+--------------------+--------------------+--------------+
| TRIGGER_NAME          | ACTION_TIMING | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_ORDER |
+-----------------------+---------------+--------------------+--------------------+--------------+
| cart_items_ad         | AFTER         | DELETE             | cart_items         |            1 |
| carts_ad              | AFTER         | DELETE             | carts              |            1 |
| customers_clean_email | BEFORE        | INSERT             | customers          |            1 |
| products_price_audit  | AFTER         | UPDATE             | products           |            1 |
+-----------------------+---------------+--------------------+--------------------+--------------+
What’s happening
  1. Each row is one trigger with its table, timing and event. ACTION_ORDER is its position among triggers with the same table, timing and event.
  2. Every one here is 1 because each table has a single trigger per event (the broken products_touch was dropped). A second AFTER UPDATE on products would get 2, or 1 if created with PRECEDES products_price_audit.
  3. Before 5.7.2, a second trigger for the same event failed to create, so old schemas often have one giant trigger doing several jobs.
  4. This query is also how you find triggers at all. They don't appear in application code, so when an UPDATE "mysteriously" writes another table or fails with a custom message, check information_schema.TRIGGERS first.
AdvancedWhen to use triggers, and when not to

Triggers earn their place for audit trails and history tables that must not be forgotten (see Soft deletes, audit columns and history tables), normalising incoming data, and keeping a denormalised counter or summary in step. Avoid them for business workflows (sending emails, calling other services — they can't anyway), long chains of triggers firing triggers, or anything a reader of the application code needs to know about. They also add work to every write, they make bulk loads slower, and online schema-change tools like gh-ost can't be used on tables that have triggers.

#Scheduled events

Added

A scheduled event is SQL that the server runs on a timer: once at a given time (AT), or repeatedly (EVERY 1 HOUR). It's cron inside MySQL. A background thread called the event scheduler wakes up, runs whatever is due, and goes back to sleep. Since MySQL 8.0 the scheduler is ON by default.

Typical jobs are housekeeping that belongs with the data: purge expired sessions, roll up yesterday's numbers into a summary table, rotate partitions. Because the job lives in the database, it runs wherever the database runs, with no separate cron host to maintain — but it's also out of sight of your application's logging and monitoring, so failures are easy to miss.

SQL
SELECT @@event_scheduler;
CREATE TABLE heartbeat (id INT AUTO_INCREMENT PRIMARY KEY, beat_at DATETIME(3) NOT NULL);

CREATE EVENT beat
  ON SCHEDULE EVERY 1 SECOND
  DO INSERT INTO heartbeat (beat_at) VALUES (NOW(3));

DO SLEEP(3.5);
SELECT * FROM heartbeat;
ALTER EVENT beat DISABLE;
SELECT EVENT_NAME, STATUS, INTERVAL_VALUE, INTERVAL_FIELD, LAST_EXECUTED
FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'ch11';
SELECT USER, COMMAND, STATE FROM information_schema.PROCESSLIST WHERE USER = 'event_scheduler';
Result
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
+----+-------------------------+
| id | beat_at                 |
+----+-------------------------+
|  1 | 2026-10-16 15:29:19.318 |
|  2 | 2026-10-16 15:29:20.320 |
|  3 | 2026-10-16 15:29:21.322 |
|  4 | 2026-10-16 15:29:22.325 |
+----+-------------------------+
+------------+----------+----------------+----------------+---------------------+
| EVENT_NAME | STATUS   | INTERVAL_VALUE | INTERVAL_FIELD | LAST_EXECUTED       |
+------------+----------+----------------+----------------+---------------------+
| beat       | DISABLED | 1              | SECOND         | 2026-10-16 15:29:22 |
+------------+----------+----------------+----------------+---------------------+
+-----------------+---------+-----------------------------+
| USER            | COMMAND | STATE                       |
+-----------------+---------+-----------------------------+
| event_scheduler | Daemon  | Waiting for next activation |
+-----------------+---------+-----------------------------+
Warning (Code 1287): 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
What’s happening
  1. @@event_scheduler is ON, the default since 8.0 (in 5.7 it was OFF, and many old tutorials start with SET GLOBAL event_scheduler = ON).
  2. CREATE EVENT beat … EVERY 1 SECOND with no STARTS begins immediately. The first beat runs at creation time, then every second: four rows in the 3.5 seconds the session sleeps, about 1,002 ms apart.
  3. Each run is executed by the scheduler in its own session, not in the session that created the event. That's why the session's DO SLEEP(3.5) doesn't block it.
  4. ALTER EVENT beat DISABLE stops it without deleting it; information_schema.EVENTS shows the status, interval and LAST_EXECUTED.
  5. The process list shows the scheduler thread itself, event_scheduler, a Daemon waiting for the next due event. MySQL 9.7 warns that information_schema.PROCESSLIST is deprecated; performance_schema.processlist is the replacement.
AdvancedPitfall: failed events leave no trace
AdvancedOperations: events on replicas and failover
AdvancedA summary table refreshed by an event

This is MySQL's substitute for a materialized view: a real table rebuilt on a schedule, plus a one-off job that deletes itself.

SQL
CREATE TABLE daily_revenue (
  day DATE PRIMARY KEY,
  orders INT NOT NULL,
  revenue DECIMAL(12,2) NOT NULL,
  refreshed_at DATETIME NOT NULL
);

DELIMITER //
CREATE EVENT refresh_daily_revenue
  ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
  DO
  BEGIN
    REPLACE INTO daily_revenue
    SELECT DATE(o.ordered_at), COUNT(DISTINCT o.id), SUM(oi.quantity * oi.unit_price), NOW()
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY DATE(o.ordered_at);
  END //
DELIMITER ;

CREATE EVENT one_off_cleanup
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
  ON COMPLETION NOT PRESERVE
  DO DELETE FROM heartbeat WHERE id <= 2;

DO SLEEP(2);
SELECT day, orders, revenue FROM daily_revenue ORDER BY day;
SELECT COUNT(*) AS heartbeat_rows FROM heartbeat;
SELECT EVENT_NAME, EVENT_TYPE, STATUS FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'ch11' ORDER BY EVENT_NAME;
Result
+------------+--------+---------+
| day        | orders | revenue |
+------------+--------+---------+
| 2025-02-01 |      1 |  957.00 |
| 2025-02-10 |      1 | 1048.00 |
| 2025-03-01 |      1 |  127.00 |
| 2025-03-15 |      1 |   88.00 |
| 2025-03-20 |      1 |  249.00 |
| 2025-04-02 |      1 | 1016.00 |
| 2025-04-05 |      1 |  116.00 |
+------------+--------+---------+
+----------------+
| heartbeat_rows |
+----------------+
|              2 |
+----------------+
+-----------------------+------------+----------+
| EVENT_NAME            | EVENT_TYPE | STATUS   |
+-----------------------+------------+----------+
| beat                  | RECURRING  | DISABLED |
| refresh_daily_revenue | RECURRING  | ENABLED  |
+-----------------------+------------+----------+
What’s happening
  1. STARTS CURRENT_TIMESTAMP makes the first refresh run right away, then hourly. The body has several clauses, so it uses BEGIN … END and DELIMITER, like a procedure.
  2. REPLACE INTO deletes any existing row with the same primary key (day) and inserts the new one, so each run overwrites the day totals instead of duplicating them (see INSERT IGNORE and REPLACE). Cancelled order 4 is excluded, so seven days remain.
  3. Dashboards now read seven precomputed rows instead of joining and grouping every order. The cost is staleness: up to an hour old, recorded in refreshed_at.
  4. one_off_cleanup runs once, a second after creation, and deletes heartbeats 1 and 2, leaving 2 rows. ON COMPLETION NOT PRESERVE (the default) then drops the event: it's no longer in information_schema.EVENTS.
  5. A rebuild-everything refresh is fine for small summaries. For big ones, refresh only recent days (WHERE o.ordered_at >= CURDATE() - INTERVAL 2 DAY), or keep the summary current with triggers instead.

#Users, roles and GRANT

Added

MySQL decides what a connection may do in two steps. Authentication: who are you? An account is identified by user and host together — 'app'@'10.0.%' and 'app'@'localhost' are different accounts with different passwords and privileges — and the password is checked by an authentication plugin (caching_sha2_password by default since 8.0). Authorisation: what may you do? Privileges (SELECT, INSERT, UPDATE, DELETE, CREATE, EXECUTE…) are granted at the global, database, table, column or routine level, and every statement is checked against them.

A role (MySQL 8.0+) is a named bundle of privileges. The mental model is job titles and key cards: you don't program every door for every employee; you give the "reader" title access to the reading rooms, and give people titles. When someone changes job, you change their titles, not 40 individual doors.

The principle that matters in interviews is least privilege: the application account gets exactly the statements it needs on its own schema — no DROP, no GRANT, no access to other schemas, and certainly not root.

SQL
CREATE ROLE ch11_reader, ch11_writer;
GRANT SELECT ON ch11.* TO ch11_reader;
GRANT INSERT, UPDATE ON ch11.orders      TO ch11_writer;
GRANT INSERT, UPDATE ON ch11.order_items TO ch11_writer;

CREATE USER 'ch11_app'@'%' IDENTIFIED BY 'App-pass-123' DEFAULT ROLE ch11_reader, ch11_writer;
CREATE USER 'ch11_analyst'@'%' IDENTIFIED BY 'Analyst-pass-123' DEFAULT ROLE ch11_reader;

SHOW GRANTS FOR 'ch11_app'@'%';
SHOW GRANTS FOR 'ch11_app'@'%' USING ch11_reader, ch11_writer;
SELECT user, host, plugin FROM mysql.user WHERE user LIKE 'ch11%';

-- Now connect as the application account: mysql -h 127.0.0.1 -u ch11_app -p ch11
SELECT CURRENT_USER(), CURRENT_ROLE();
SELECT COUNT(*) FROM orders;
UPDATE orders SET status = 'paid' WHERE id = 6;
DELETE FROM orders WHERE id = 6;
UPDATE customers SET city = 'Oslo' WHERE id = 4;
DROP TABLE order_items;
Result
+-------------------------------------------------------------+
| Grants for ch11_app@%                                       |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `ch11_app`@`%`                        |
| GRANT `ch11_reader`@`%`,`ch11_writer`@`%` TO `ch11_app`@`%` |
+-------------------------------------------------------------+
+----------------------------------------------------------------+
| Grants for ch11_app@%                                          |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `ch11_app`@`%`                           |
| GRANT SELECT ON `ch11`.* TO `ch11_app`@`%`                     |
| GRANT INSERT, UPDATE ON `ch11`.`order_items` TO `ch11_app`@`%` |
| GRANT INSERT, UPDATE ON `ch11`.`orders` TO `ch11_app`@`%`      |
| GRANT `ch11_reader`@`%`,`ch11_writer`@`%` TO `ch11_app`@`%`    |
+----------------------------------------------------------------+
+--------------+------+-----------------------+
| user         | host | plugin                |
+--------------+------+-----------------------+
| ch11_analyst | %    | caching_sha2_password |
| ch11_app     | %    | caching_sha2_password |
| ch11_reader  | %    | caching_sha2_password |
| ch11_writer  | %    | caching_sha2_password |
+--------------+------+-----------------------+
+----------------+-------------------------------------+
| CURRENT_USER() | CURRENT_ROLE()                      |
+----------------+-------------------------------------+
| ch11_app@%     | `ch11_reader`@`%`,`ch11_writer`@`%` |
+----------------+-------------------------------------+
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
ERROR 1142 (42000): DELETE command denied to user 'ch11_app'@'127.0.0.1' for table 'orders'
ERROR 1142 (42000): UPDATE command denied to user 'ch11_app'@'127.0.0.1' for table 'customers'
ERROR 1142 (42000): DROP command denied to user 'ch11_app'@'127.0.0.1' for table 'order_items'
What’s happening
  1. Two roles hold the privileges: ch11_reader can SELECT anything in the ch11 schema, ch11_writer can insert and update only orders and order_items. The ch11_ prefix is only because accounts are server-wide and other chapters share this server.
  2. CREATE USER … DEFAULT ROLE ch11_reader, ch11_writer creates the account and makes both roles active automatically at login. mysql.user shows roles are stored as accounts too (locked ones that can't log in), all using caching_sha2_password.
  3. Plain SHOW GRANTS shows only what was granted directly: USAGE (which means "no privileges") and the role grants. USING ch11_reader, ch11_writer expands what the roles give.
  4. As ch11_app, CURRENT_ROLE() lists the two active roles. The SELECT and the UPDATE of orders succeed (the UPDATE prints nothing in batch mode).
  5. DELETE isn't granted, customers isn't in the writer role, and DROP was never granted, so each fails with ERROR 1142 … command denied. The error names the account as 'ch11_app'@'127.0.0.1': the host it actually connected from, matched to the '%' account. If the app's code is compromised, the attacker inherits only these privileges — no dropping tables, no reading other schemas.
The role that does nothing
SQL
CREATE USER 'ch11_temp'@'%' IDENTIFIED BY 'Temp-pass-123';
GRANT ch11_reader TO 'ch11_temp'@'%';

ch11_temp logs in and runs SELECT COUNT(*) FROM ch11.orders. What happens?

Show answer

It's denied. A granted role isn't active until it's set as a default role or activated with SET ROLE.

Result
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
ERROR 1142 (42000): SELECT command denied to user 'ch11_temp'@'127.0.0.1' for table 'orders'
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
What’s happening
  1. GRANT ch11_reader TO 'ch11_temp'@'%' lets the user use the role, but MySQL starts each session with only the account's default roles active, and this account has none.
  2. CURRENT_ROLE() prints NONE, and the SELECT fails with ERROR 1142, exactly as if the role had never been granted. This surprises everyone the first time.
  3. After SET ROLE ch11_reader in the session, the same SELECT returns 8.
  4. The permanent fixes: SET DEFAULT ROLE ch11_reader TO 'ch11_temp'@'%' (or DEFAULT ROLE in CREATE USER, as for ch11_app), or the server setting activate_all_roles_on_login = ON, which activates every granted role at login.
AdvancedOld vs new: GRANT, roles and native passwords
AdvancedLeast privilege with views and column grants

A support agent needs to look customers up but shouldn't see full email addresses, and needs order status but not who placed each order:

SQL
CREATE VIEW customer_directory AS
  SELECT id, name, city,
         CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked
  FROM customers;
CREATE USER 'ch11_support'@'%' IDENTIFIED BY 'Support-pass-123'
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
GRANT SELECT ON ch11.customer_directory TO 'ch11_support'@'%';
GRANT SELECT (id, status) ON ch11.orders TO 'ch11_support'@'%';

Connected as ch11_support:

SQL
SELECT * FROM customer_directory WHERE id <= 3;
SELECT email FROM customers WHERE id = 1;
SELECT id, status FROM orders WHERE id = 1;
SELECT id, customer_id FROM orders WHERE id = 1;
Result
+----+-------+---------+------------------+
| id | name  | city    | email_masked     |
+----+-------+---------+------------------+
|  1 | Rohit | Toronto | r***@example.com |
|  2 | Maya  | Mumbai  | m***@example.com |
|  3 | Liam  | London  | l***@example.com |
+----+-------+---------+------------------+
ERROR 1142 (42000): SELECT command denied to user 'ch11_support'@'127.0.0.1' for table 'customers'
+----+---------+
| id | status  |
+----+---------+
|  1 | shipped |
+----+---------+
ERROR 1143 (42000): SELECT command denied to user 'ch11_support'@'127.0.0.1' for column 'customer_id' in table 'orders'
What’s happening
  1. The view reads customers, but ch11_support has no privilege on customers at all — and the view still works. Views default to SQL SECURITY DEFINER: the view's query runs with the privileges of whoever created it (root here), and the user only needs SELECT on the view.
  2. Querying customers directly fails with ERROR 1142, so the masked view is the only way in.
  3. GRANT SELECT (id, status) ON ch11.orders is a column privilege: id and status are readable, and asking for customer_id fails with a different error, 1143 … for column 'customer_id'.
  4. SQL SECURITY INVOKER would instead check the caller's privileges on the base tables, which is what you want for a view that is only a convenience, not a security boundary. Procedures and functions have the same DEFINER/INVOKER switch.
AdvancedLocking accounts after failed logins

FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 (MySQL 8.0.19+) locks the account for a day after three consecutive wrong passwords. Four bad logins, then the right password:

Result
ERROR 1045 (28000): Access denied for user 'ch11_support'@'127.0.0.1' (using password: YES)
ERROR 1045 (28000): Access denied for user 'ch11_support'@'127.0.0.1' (using password: YES)
ERROR 3955 (HY000): Access denied for user 'ch11_support'@'127.0.0.1'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 3 consecutive failed logins.
ERROR 3955 (HY000): Access denied for user 'ch11_support'@'127.0.0.1'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 3 consecutive failed logins.
ERROR 3955 (HY000): Access denied for user 'ch11_support'@'127.0.0.1'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 3 consecutive failed logins.
What’s happening
  1. The first two wrong passwords give the normal ERROR 1045.
  2. The third failure reaches the limit, and that attempt already reports ERROR 3955 … Account is blocked for 1 day(s).
  3. The fifth line is the correct password: still blocked. Lockout protects against password guessing, but it also means an attacker can lock out your application's account by guessing wrong on purpose, so use it for human accounts, not for the service account your app depends on.
  4. ALTER USER 'ch11_support'@'%' ACCOUNT UNLOCK clears the counter, and the next login succeeds. Other options in the same family: PASSWORD EXPIRE INTERVAL 90 DAY, PASSWORD HISTORY 5, and the validate_password component for strength rules.

#SQL injection and prepared statements

Added

SQL injection happens when input becomes part of the SQL text. The server parses one string and can't tell your SQL from the attacker's, so input like ' OR '1'='1 changes the query's logic. The Node site covers the attack from the application side in SQL injection and parameterised queries; this topic shows it inside MySQL itself, which matters because stored procedures that build SQL dynamically are injectable too.

The cure is a prepared statement. The mental model is a form with fixed boxes: the statement is parsed and planned first, with ? placeholders for the boxes, and values are filled in afterwards, as data. By the time a value arrives, parsing is over, so nothing in it can become syntax. MySQL supports this natively, both in the client/server protocol (what drivers use) and in SQL with PREPARE, EXECUTE … USING and DEALLOCATE PREPARE.

SQL
-- What a vulnerable app does: paste the user's input into the SQL text.
SET @input = "nobody@example.com' OR '1'='1";
SET @sql = CONCAT("SELECT id, name, email FROM customers WHERE email = '", @input, "'");
SELECT @sql;
PREPARE q FROM @sql;
EXECUTE q;

-- The fix: the SQL text is fixed, the input travels separately as a parameter.
PREPARE safe FROM 'SELECT COUNT(*) AS matches FROM customers WHERE email = ?';
EXECUTE safe USING @input;
SET @input = 'maya@example.com';
PREPARE safe FROM 'SELECT id, name, email FROM customers WHERE email = ?';
EXECUTE safe USING @input;
DEALLOCATE PREPARE safe;
Result
+-------------------------------------------------------------------------------------+
| @sql                                                                                |
+-------------------------------------------------------------------------------------+
| SELECT id, name, email FROM customers WHERE email = 'nobody@example.com' OR '1'='1' |
+-------------------------------------------------------------------------------------+
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  1 | Rohit | rohit@example.com |
|  2 | Maya  | maya@example.com  |
|  3 | Liam  | liam@example.com  |
|  4 | Sofia | sofia@example.com |
|  5 | Noah  | noah@example.com  |
|  6 | Zara  | zara@example.com  |
+----+-------+-------------------+
+---------+
| matches |
+---------+
|       0 |
+---------+
+----+------+------------------+
| id | name | email            |
+----+------+------------------+
|  2 | Maya | maya@example.com |
+----+------+------------------+
What’s happening
  1. The "user input" is nobody@example.com' OR '1'='1. CONCAT pastes it between quotes, and the printed @sql shows the result: the input's ' closed the string early, and OR '1'='1' is now SQL. The WHERE is true for every row.
  2. PREPARE q FROM @sql parses that string as written, so EXECUTE q returns all six customers. A login form or "find my account" page built this way leaks every account.
  3. PREPARE safe FROM '… WHERE email = ?' parses the query without the input. EXECUTE safe USING @input then supplies the whole attack string as one value; MySQL looks for a customer whose email is literally nobody@example.com' OR '1'='1 and finds 0.
  4. With a real address the same prepared statement returns Maya. The SQL never changed; only the value in the box did.
  5. DEALLOCATE PREPARE frees the statement. Prepared statements belong to a session and disappear when it ends; the server caps how many can exist at once with max_prepared_stmt_count (16,382 by default), and drivers that prepare without closing can hit it.
AdvancedInjection inside a stored procedure

Procedures that build SQL with CONCAT are as injectable as application code, and the classic case is a sort column, because identifiers can't be parameters:

SQL
DELIMITER //
CREATE PROCEDURE products_sorted_unsafe(IN p_sort VARCHAR(100))
BEGIN
  SET @sql = CONCAT('SELECT id, name, price FROM products ORDER BY ', p_sort, ' LIMIT 3');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END //

CREATE PROCEDURE products_sorted(IN p_sort VARCHAR(20), IN p_max_price DECIMAL(10,2))
BEGIN
  IF p_sort NOT IN ('name', 'price', 'stock') THEN       -- allowlist the identifier
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown sort column';
  END IF;
  SET @sql = CONCAT('SELECT id, name, price FROM products WHERE price <= ? ORDER BY ',
                    p_sort, ' LIMIT 3');
  SET @max = p_max_price;                                 -- values still go in as parameters
  PREPARE s FROM @sql; EXECUTE s USING @max; DEALLOCATE PREPARE s;
END //
DELIMITER ;

CALL products_sorted_unsafe('price');
CALL products_sorted_unsafe('(SELECT IF(SUBSTRING(email,1,1) = ''r'', price, -price) FROM customers WHERE id = 1)');
CALL products_sorted_unsafe('(SELECT IF(SUBSTRING(email,1,1) = ''x'', price, -price) FROM customers WHERE id = 1)');
CALL products_sorted('price', 100);
CALL products_sorted('email', 100);
CALL products_sorted('price; DROP TABLE products', 100);
Result
+----+---------------------------------------+-------+
| id | name                                  | price |
+----+---------------------------------------+-------+
|  7 | USB-C Charger                         | 29.00 |
|  5 | SQL Performance Explained             | 39.00 |
|  6 | Designing Data-Intensive Applications | 49.00 |
+----+---------------------------------------+-------+
+----+---------------------------------------+-------+
| id | name                                  | price |
+----+---------------------------------------+-------+
|  7 | USB-C Charger                         | 29.00 |
|  5 | SQL Performance Explained             | 39.00 |
|  6 | Designing Data-Intensive Applications | 49.00 |
+----+---------------------------------------+-------+
+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  2 | MacBook Air  | 1299.00 |
|  4 | iPhone 17    | 1099.00 |
|  1 | ThinkBook 14 |  899.00 |
+----+--------------+---------+
+----+---------------------------------------+-------+
| id | name                                  | price |
+----+---------------------------------------+-------+
|  7 | USB-C Charger                         | 29.00 |
|  5 | SQL Performance Explained             | 39.00 |
|  6 | Designing Data-Intensive Applications | 49.00 |
+----+---------------------------------------+-------+
ERROR 1644 (45000): Unknown sort column
ERROR 1406 (22001): Data too long for column 'p_sort' at row 1
What’s happening
  1. products_sorted_unsafe('price') is the intended use: cheapest three products.
  2. The attacker passes a subquery as the "column". It reads customer 1's email and sorts by price if the first letter is r, by -price otherwise. With 'r' the result is cheapest-first: the guess was right.
  3. With 'x' the order flips to most expensive first: the guess was wrong. Nothing secret is ever printed, but one yes/no answer per call is enough to extract any value letter by letter. This is blind SQL injection, and it works with a single statement — no ; needed.
  4. products_sorted checks the identifier against an allowlist before it touches the SQL. 'price' passes; 'email' is a real column but not an allowed one, so Unknown sort column. The price limit is still passed as a ? parameter.
  5. The last call is rejected even earlier, by the parameter type: VARCHAR(20) can't hold the string, so ERROR 1406 … Data too long. Tight parameter types are a nice extra layer, but the allowlist is the real fix: a short payload would fit.
ORDER BY ?
SQL
PREPARE bad FROM 'SELECT id, name FROM customers ORDER BY ?';
SET @col = 'name';
EXECUTE bad USING @col;

Are the customers sorted by name?

Show answer

No. They come back in whatever order MySQL reads them — here, by id.

Result
+----+-------+
| id | name  |
+----+-------+
|  1 | Rohit |
|  2 | Maya  |
|  3 | Liam  |
|  4 | Sofia |
|  5 | Noah  |
|  6 | Zara  |
+----+-------+
What’s happening
  1. A placeholder always stands for a value, never an identifier. So ORDER BY ? with 'name' means "order by the string constant 'name'", the same for every row.
  2. Sorting by a constant doesn't reorder anything, and MySQL returns rows in primary-key order because that's how it scanned the table. No error, no warning: a silent bug.
  3. Placeholders in the table position fail loudly instead: PREPARE bad2 FROM 'SELECT id, name FROM ? LIMIT 1' gives ERROR 1064 (42000): You have an error in your SQL syntax … near '? LIMIT 1'.
  4. Table names, column names, ASC/DESC and other keywords must come from an allowlist in your code (map the user's choice to a known identifier), as in products_sorted.
AdvancedLimits: what parameters don't protect
AdvancedInternals: server-side vs client-side prepare

#Encryption and auditing

Added

Encryption protects data from people who get hold of it outside the normal access path. It comes in three places, each against a different threat. In transit (TLS) stops someone on the network reading or altering the traffic between application and database. At rest (tablespace and log encryption) stops someone who steals a disk, a backup or a data file from reading it with a text editor. At the column or application level protects specific values (a national ID, a card token) even from people who can query the database, because they don't have the key.

Auditing answers a different question: not "can they read it?" but "who did what, and when?" — which account read the salary table at 3 a.m., who dropped that table. That's a record you need for compliance (PCI DSS, SOC 2, HIPAA) and for investigations.

The mental model is a bank: TLS is the armoured van between branches, at-rest encryption is the vault (useless to a burglar who carries it off), column encryption is the safe-deposit box only its owner can open, and the audit log is the visitor book and CCTV.

SQL
CREATE DATABASE secdemo;
USE secdemo;
CREATE TABLE plain_cards  (id INT PRIMARY KEY, note VARCHAR(100)) ENCRYPTION = 'N';
CREATE TABLE secret_cards (id INT PRIMARY KEY, note VARCHAR(100)) ENCRYPTION = 'Y';
INSERT INTO plain_cards  VALUES (1, 'TOP-SECRET-PLAIN-4111');
INSERT INTO secret_cards VALUES (1, 'TOP-SECRET-CIPHER-4111');
FLUSH TABLES plain_cards, secret_cards FOR EXPORT;
UNLOCK TABLES;
SELECT * FROM secret_cards;
SELECT NAME, ENCRYPTION FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'secdemo/%';
Result
+----+------------------------+
| id | note                   |
+----+------------------------+
|  1 | TOP-SECRET-CIPHER-4111 |
+----+------------------------+
+----------------------+------------+
| NAME                 | ENCRYPTION |
+----------------------+------------+
| secdemo/plain_cards  | N          |
| secdemo/secret_cards | Y          |
+----------------------+------------+
What’s happening
  1. This runs on a separate MySQL 9.7 server started for this chapter with the component_keyring_file keyring component loaded. InnoDB encryption needs a keyring to hold the master key; on the shared sandbox server, which has none, ENCRYPTION = 'Y' fails with ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
  2. Both tables are created the same way except for ENCRYPTION. Each gets its own tablespace key, which is stored encrypted by the master key in the tablespace header.
  3. FLUSH TABLES … FOR EXPORT forces their pages out of the buffer pool onto disk, so the .ibd files contain the rows now rather than seconds later. UNLOCK TABLES releases the lock it takes.
  4. Through SQL nothing looks different: SELECT * FROM secret_cards returns plain text. At-rest encryption is transparent (MySQL calls it TDE): InnoDB decrypts pages as it reads them into memory and encrypts them as it writes them out. INNODB_TABLESPACES shows which tablespaces are encrypted. What's different is on the disk, which the next example looks at.
AdvancedProof: grepping the data files on disk

The data files, read directly on the server's disk:

Shell
grep -a -o "TOP-SECRET[A-Z0-9-]*" /var/lib/mysql/secdemo/plain_cards.ibd
grep -a -o "TOP-SECRET[A-Z0-9-]*" /var/lib/mysql/secdemo/secret_cards.ibd; echo "exit $?"
Result
TOP-SECRET-PLAIN-4111
exit 1
What’s happening
  1. grep -a treats the binary .ibd file as text and -o prints only the match.
  2. In plain_cards.ibd it finds TOP-SECRET-PLAIN-4111: anyone with a copy of the file, a disk snapshot or a physical backup can read the data without logging in to MySQL.
  3. In secret_cards.ibd there's no match (grep exits with status 1), because every page was encrypted with the tablespace key before being written.
  4. What it does not protect: anyone who can log in and SELECT. TDE is against stolen disks and files, not against SQL users — that's what privileges and column encryption are for. The keyring file here sits on the same machine, which is fine for a demo; production uses a key management service (the Enterprise and cloud keyring components, or the cloud provider's own storage encryption).
AdvancedChecklist: logs, backups and system tablespace
AdvancedEncryption in transit: TLS

MySQL 8.0+ generates self-signed certificates at first start and clients prefer TLS by default. A user can be required to use it:

SQL
CREATE USER 'billing'@'%' IDENTIFIED BY 'Billing-pass-123' REQUIRE SSL;
GRANT SELECT ON secdemo.* TO 'billing'@'%';
SHOW VARIABLES LIKE 'tls_version';
Result
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tls_version   | TLSv1.2,TLSv1.3 |
+---------------+-----------------+

Connecting as billing over TCP and running the first query, then trying again with --ssl-mode=DISABLED, then running the second query as root over the local Unix socket:

SQL
SHOW SESSION STATUS WHERE Variable_name IN ('Ssl_version', 'Ssl_cipher');  -- billing, TCP
SHOW SESSION STATUS LIKE 'Ssl_cipher';                                      -- root, socket
Result
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
| Ssl_version   | TLSv1.3                |
+---------------+------------------------+
ERROR 1045 (28000): Access denied for user 'billing'@'127.0.0.1' (using password: YES)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
What’s happening
  1. REQUIRE SSL on the account means this user may only connect over an encrypted connection. The server allows TLS 1.2 and 1.3; 1.0 and 1.1 were removed in 8.0.28.
  2. A normal TCP connection negotiates TLS 1.3 with no client options: Ssl_cipher is TLS_AES_128_GCM_SHA256.
  3. With --ssl-mode=DISABLED the same correct password is refused with a plain ERROR 1045 … Access denied. The server doesn't say why, so check the account's REQUIRE clause when a user swears the password is right.
  4. Root over the Unix socket shows an empty Ssl_cipher: socket and shared-memory connections never leave the machine, so MySQL treats them as secure without TLS.
  5. SET GLOBAL require_secure_transport = ON makes TLS mandatory for every TCP connection. A plain connection then fails with ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON. Self-signed certificates encrypt but don't prove the server's identity; for that, clients use --ssl-mode=VERIFY_IDENTITY with a CA-signed certificate (Node drivers: ssl: { ca, rejectUnauthorized: true }).
AdvancedColumn-level encryption with AES_ENCRYPT

To hide a value from people who can query the table, encrypt the value itself:

SQL
USE secdemo;
CREATE TABLE customers_pii (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  sin_enc VARBINARY(64),           -- AES ciphertext is binary
  sin_hash BINARY(32)              -- for equality lookups without decrypting
);
SET block_encryption_mode = 'aes-256-cbc';
SET @key = UNHEX(SHA2('demo-key-from-a-secrets-manager', 256));
SET @iv  = RANDOM_BYTES(16);
INSERT INTO customers_pii VALUES
  (1, 'Rohit', CONCAT(@iv, AES_ENCRYPT('046-454-286', @key, @iv)), UNHEX(SHA2('046-454-286', 256)));
SELECT id, name, HEX(LEFT(sin_enc, 8)) AS ciphertext_start, LENGTH(sin_enc) AS bytes FROM customers_pii;
SELECT name, CAST(AES_DECRYPT(SUBSTRING(sin_enc, 17), @key, LEFT(sin_enc, 16)) AS CHAR) AS sin
FROM customers_pii WHERE sin_hash = UNHEX(SHA2('046-454-286', 256));
SELECT AES_DECRYPT(SUBSTRING(sin_enc, 17), UNHEX(SHA2('wrong key', 256)), LEFT(sin_enc, 16)) AS with_wrong_key
FROM customers_pii;
Result
+----+-------+------------------+-------+
| id | name  | ciphertext_start | bytes |
+----+-------+------------------+-------+
|  1 | Rohit | 5A88D5E84DB22886 |    32 |
+----+-------+------------------+-------+
+-------+-------------+
| name  | sin         |
+-------+-------------+
| Rohit | 046-454-286 |
+-------+-------------+
+----------------+
| with_wrong_key |
+----------------+
| NULL           |
+----------------+
What’s happening
  1. block_encryption_mode = 'aes-256-cbc' switches from the default aes-128-ecb, which encrypts identical values to identical ciphertext and needs no IV. CBC needs a random 16-byte initialisation vector per value, from RANDOM_BYTES(16).
  2. The IV is stored in front of the ciphertext: 16 bytes of IV plus 16 bytes of AES output for an 11-character string makes 32 bytes. The column holds binary garbage to anyone without the key.
  3. Because each value has a random IV, you can't search by ciphertext. A separate sin_hash column (SHA-256 of the value) supports exact-match lookups. A plain hash of a 9-digit number can be brute-forced, so real systems use a keyed hash (HMAC) for this column.
  4. Decrypting takes the IV back off (LEFT(…, 16)), decrypts the rest (SUBSTRING(…, 17)) and returns the original 046-454-286. With the wrong key, AES_DECRYPT returns NULL rather than an error.
  5. The weakness is the key: here it travels inside the SQL text, so it can appear in the general log, the slow log, performance_schema statement history and the process list (the next example shows such a log). That's why most teams encrypt sensitive fields in the application (or use a KMS envelope scheme) and send the database only ciphertext.
AdvancedAuditing in Community MySQL

MySQL Enterprise Audit is the official audit log; the manual describes it as part of MySQL Enterprise Edition, a commercial product. On Community MySQL the plugin and component simply aren't there:

SQL
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
INSTALL COMPONENT 'file://component_audit_log';
Result
ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/audit_log.so' (errno: 11 /usr/lib64/mysql/plugin/audit_log.so: cannot open shared object file: No such file or directory)
ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/component_audit_log.so' (errno: 2 /usr/lib64/mysql/plugin/component_audit_log.so: cannot open shared object file: No such file or directory)

The free alternatives are the Percona and MariaDB audit plugins, your cloud provider's audit feature (RDS, Aurora and Cloud SQL all have one), or, crudely, the general query log, which records every statement every client sends:

SQL
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = ON;
-- billing connects and runs: SELECT * FROM plain_cards; SELECT AES_ENCRYPT("hello", "my-secret-key"); DELETE FROM plain_cards
SET GLOBAL general_log = OFF;
SELECT event_time, user_host, command_type, CAST(argument AS CHAR(70)) AS argument
FROM mysql.general_log WHERE user_host LIKE 'billing%' ORDER BY event_time;
Result
+----------------------------+---------------------------------+--------------+----------------------------------------------+
| event_time                 | user_host                       | command_type | argument                                     |
+----------------------------+---------------------------------+--------------+----------------------------------------------+
| 2026-10-16 15:32:48.365705 | billing[billing] @  [127.0.0.1] | Query        | select @@version_comment limit 1             |
| 2026-10-16 15:32:48.365790 | billing[billing] @  [127.0.0.1] | Query        | SELECT * FROM plain_cards                    |
| 2026-10-16 15:32:48.366769 | billing[billing] @  [127.0.0.1] | Query        | SELECT AES_ENCRYPT("hello", "my-secret-key") |
| 2026-10-16 15:32:48.367492 | billing[billing] @  [127.0.0.1] | Query        | DELETE FROM plain_cards                      |
| 2026-10-16 15:32:48.368216 | billing[billing] @  [127.0.0.1] | Quit         |                                              |
+----------------------------+---------------------------------+--------------+----------------------------------------------+
What’s happening
  1. Both install attempts fail because the shared libraries don't exist in the Community build. The audit plugin (deprecated in 9.x in favour of the audit component) and the component ship only with Enterprise.
  2. log_output = 'TABLE' sends the general log to the mysql.general_log table instead of a file, so it can be queried with SQL. It's turned on, billing runs three statements, and it's turned off again.
  3. Every statement is recorded with time, account and client address — including the select @@version_comment limit 1 that the mysql client sends on connect, and the Quit.
  4. The DELETE is logged even though it failed: billing has only SELECT, and the table still has its row. The general log records statements as they arrive, before privilege checks, so it shows attempts as well as successes — but it doesn't record the outcome, which a real audit log does.
  5. And the AES key my-secret-key is sitting in the log in plain text. The general log records everything on a busy server, costs real throughput, and is itself sensitive data. Use it briefly for debugging; for a real audit trail use an audit plugin, and for data changes the binary log (see Replication: binlog, source and replicas) or audit triggers record what changed.

SQL and MySQL written fresh; MongoDB built from Rohit’s “mongoDB interview” doc. Every query was run on MySQL 9.7, MongoDB 9.0, Redis 8.10 or PostgreSQL 18.

RohitDownloads · All chapters

Sync progress across devices

Type the same private phrase on your Mac and your phone, and your Learned ticks follow you between them — on every study site.

The phrase never leaves this device: only a fingerprint of it is sent, and the server stores a fingerprint of that. Anyone who knows the phrase could see or change your ticks, so pick something you don’t use elsewhere.

Sync is on in this browser.

Sync ID

This ID must be the same on every device. If another device shows a different one, its phrase is different (capital letters count): tap “Turn off here” on it and type the phrase again exactly.