Build a Reproducible Lab with Databases, Accounts, Sample Workloads, and Safe Defaults

Learning outcomes

The first four lessons established what MariaDB is, where product boundaries sit, which release is appropriate for the current lab, and how to prove that a server is actually running. Now ServiceHub needs a reusable database sandbox that later chapters can extend without teaching every concept from a blank page. The lab must also be disposable so future locking, replication, backup, Galera, and upgrade experiments cannot endanger unrelated data.

This lesson creates a small but realistic service-management domain with explicit InnoDB tables, character set/collation, SQL-mode evidence, least-privilege roles/accounts, seed/reset steps, and verification queries. It is intentionally not a production schema; it is a controlled learning substrate whose assumptions are written down.

1. Freeze the lab assumptions before creating objects

A reproducible lab starts by observing server defaults, then making important schema choices explicit. Current MariaDB releases default to utf8mb4 and modern UCA collations, but distribution packaging and older series can differ. Likewise, SQL_MODE is a global/session setting whose defaults have changed historically. Later lessons should not silently depend on whatever a particular machine inherited.

SELECT VERSION() AS server_version,       @@version_comment AS version_comment,       @@default_storage_engine AS default_engine,       @@character_set_server AS character_set_server,       @@collation_server AS collation_server,       @@GLOBAL.sql_mode AS global_sql_mode,       @@SESSION.sql_mode AS session_sql_mode;SHOW CHARACTER SET LIKE 'utf8mb4';SHOW COLLATION LIKE 'utf8mb4_uca1400_ai_ci';

If your server does not support the exact collation shown, stop and check the actual target version rather than substituting silently. On modern MariaDB 11.6+ the server default moved to utf8mb4 with utf8mb4_uca1400_ai_ci as the default collation, but this lab declares those choices explicitly so the schema remains self-describing.

2. Create the database and roles before users

The application should not connect as root or another broad administrative account. MariaDB roles let you group privileges and assign them to accounts. MariaDB activates one current role at a time; a default role can be enabled automatically when the user connects. This differs from MySQL role semantics in important ways and is one reason the course treats compatibility explicitly.

CREATE DATABASE servicehub  CHARACTER SET utf8mb4  COLLATE utf8mb4_uca1400_ai_ci;CREATE ROLE servicehub_app_rw;CREATE ROLE servicehub_report_ro;CREATE USER 'servicehub_app'@'localhost'  IDENTIFIED BY 'lab-only-change-this-app';CREATE USER 'servicehub_report'@'localhost'  IDENTIFIED BY 'lab-only-change-this-report';GRANT SELECT, INSERT, UPDATE, DELETE  ON servicehub.* TO servicehub_app_rw;GRANT SELECT  ON servicehub.* TO servicehub_report_ro;GRANT servicehub_app_rw TO 'servicehub_app'@'localhost';GRANT servicehub_report_ro TO 'servicehub_report'@'localhost';SET DEFAULT ROLE servicehub_app_rw  FOR 'servicehub_app'@'localhost';SET DEFAULT ROLE servicehub_report_ro  FOR 'servicehub_report'@'localhost';

The password strings are intentionally disposable lab values and must never be reused outside this local instance. For production, credentials belong in an approved secret-management path and network host patterns must be deliberately restricted. Chapter 15 revisits authentication, TLS, plugins, and least-privilege administration in depth.

SHOW GRANTS FOR 'servicehub_app'@'localhost';SHOW GRANTS FOR 'servicehub_report'@'localhost';SELECT GRANTEE, ROLE_NAME, IS_DEFAULTFROM information_schema.APPLICABLE_ROLESWHERE GRANTEE LIKE '%servicehub_app%'   OR GRANTEE LIKE '%servicehub_report%'ORDER BY GRANTEE, ROLE_NAME;

3. Build explicit InnoDB tables for a coherent domain

ServiceHub coordinates customers, technicians, work orders, and status events. We keep the schema small enough to understand but rich enough for later indexing, transactions, optimizer, backup, replication, and observability labs. Every table explicitly declares ENGINE=InnoDB rather than relying on the server default.

USE servicehub;CREATE TABLE customers (  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  email VARCHAR(254) NOT NULL,  display_name VARCHAR(120) NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (customer_id),  UNIQUE KEY uq_customers_email (email)) ENGINE=InnoDB;CREATE TABLE technicians (  technician_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  display_name VARCHAR(120) NOT NULL,  region_code VARCHAR(20) NOT NULL,  active TINYINT(1) NOT NULL DEFAULT 1,  PRIMARY KEY (technician_id),  KEY ix_technicians_region_active (region_code, active)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  customer_id BIGINT UNSIGNED NOT NULL,  technician_id BIGINT UNSIGNED NULL,  status ENUM('new','assigned','in_progress','completed','cancelled') NOT NULL DEFAULT 'new',  priority TINYINT UNSIGNED NOT NULL DEFAULT 3,  summary VARCHAR(240) NOT NULL,  opened_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at DATETIME(6) NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_customer_opened (customer_id, opened_at),  KEY ix_work_orders_technician_status (technician_id, status),  CONSTRAINT fk_work_orders_customer    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),  CONSTRAINT fk_work_orders_technician    FOREIGN KEY (technician_id) REFERENCES technicians(technician_id),  CONSTRAINT chk_work_orders_priority CHECK (priority BETWEEN 1 AND 5)) ENGINE=InnoDB;CREATE TABLE work_order_events (  event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  work_order_id BIGINT UNSIGNED NOT NULL,  event_type VARCHAR(40) NOT NULL,  event_note VARCHAR(500) NULL,  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (event_id),  KEY ix_events_work_order_time (work_order_id, occurred_at),  CONSTRAINT fk_events_work_order    FOREIGN KEY (work_order_id) REFERENCES work_orders(work_order_id)      ON DELETE CASCADE) ENGINE=InnoDB;

The schema intentionally uses familiar features without trying to teach every design nuance now. Later chapters will revisit ENUM, indexes, foreign keys, generated data, partitioning, and migration strategies. The important Chapter 01 property is that the engine, key relationships, and text encoding are explicit and observable.

4. Seed a small workload and make expected state observable

START TRANSACTION;INSERT INTO customers (email, display_name) VALUES('amina@example.test','Amina Rahimi'),('leo@example.test','Leo Martin'),('sora@example.test','Sora Kim');INSERT INTO technicians (display_name, region_code) VALUES('Nadia Chen','north'),('Mateo Silva','south');INSERT INTO work_orders(customer_id, technician_id, status, priority, summary) VALUES(1,1,'assigned',2,'Replace failed edge gateway'),(2,NULL,'new',4,'Investigate intermittent telemetry'),(3,2,'in_progress',1,'Restore warehouse scanner connectivity');INSERT INTO work_order_events(work_order_id,event_type,event_note) VALUES(1,'created','Customer reported gateway offline'),(1,'assigned','Assigned to Nadia'),(2,'created','Telemetry gaps reported'),(3,'created','Scanner outage opened'),(3,'assigned','Assigned to Mateo');COMMIT;SELECT status, COUNT(*) AS work_ordersFROM work_ordersGROUP BY statusORDER BY status;SELECT w.work_order_id, c.display_name AS customer,       t.display_name AS technician, w.status, w.priority, w.summaryFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idLEFT JOIN technicians AS t ON t.technician_id=w.technician_idORDER BY w.work_order_id;

Expected counts are one new, one assigned, and one in_progress work order. The joined result should contain three rows. If IDs differ because you previously seeded the database, that is evidence your reset procedure was not followed; do not edit expected results to hide state drift.

5. Verify engine, charset, collation, constraints, and current role

SELECT TABLE_NAME, ENGINE, TABLE_COLLATIONFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME;SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPEFROM information_schema.TABLE_CONSTRAINTSWHERE CONSTRAINT_SCHEMA='servicehub'ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME;SHOW CREATE DATABASE servicehub;SHOW CREATE TABLE servicehub.work_orders;

All four application tables should report InnoDB. The database/table collation should reflect the explicit schema default unless a column/table override was added. SHOW CREATE TABLE is the strongest compact evidence of the actual DDL MariaDB stored after normalization.

6. Deliberately wrong: run the application as root and rely on implicit defaults

This shortcut feels convenient in a local prototype: connect as root, create tables without an ENGINE clause, omit character set/collation, and assume strict SQL behavior. It fails as a learning foundation because later changes become ambiguous. A different default storage engine, older server charset, altered SQL mode, or overly privileged application can make identical SQL behave differently and can turn an application bug into destructive database access.

The repair is exactly what the lab does: use dedicated accounts/roles, explicit database encoding, explicit InnoDB, and recorded SQL mode. You do not need to hard-code every server variable—only the assumptions that matter for correctness and reproducibility. Everything else can remain an observed baseline to be revisited when its chapter arrives.

-- Connect in a separate client as servicehub_report first.SELECT CURRENT_ROLE(), CURRENT_USER(), DATABASE();SELECT COUNT(*) FROM servicehub.work_orders;-- Intentionally expected to fail for the read-only account:INSERT INTO servicehub.work_orders(customer_id,status,priority,summary)VALUES (1,'new',3,'This write should be denied');

A permission error is the desired result. If the insert succeeds, stop and inspect SHOW GRANTS rather than continuing; the least-privilege boundary is wrong.

7. Backup-before-destruction and reset conventions

Chapter 13 will teach physical backup and point-in-time recovery in depth. Chapter 01 only establishes the habit that destructive reset is preceded by an explicit decision: either the data is disposable and can be recreated from seed scripts, or a verified backup must exist. For this lab, the source-of-truth is the schema/seed script, so dropping the database is acceptable after you verify you are connected to the disposable instance.

# Run from a shell. Omit --password=<value>; let the client prompt.mariadb-dump --host=127.0.0.1 --port=3307   --user=root --password   --single-transaction --routines --events --triggers   servicehub > servicehub_ch01.sql

A dump file is not a proven backup until it has been restored and verified. That restore discipline arrives later. For Chapter 01, the dump is optional; the mandatory reset mechanism is deterministic DDL plus seed data.

DROP DATABASE IF EXISTS servicehub;DROP USER IF EXISTS 'servicehub_app'@'localhost';DROP USER IF EXISTS 'servicehub_report'@'localhost';DROP ROLE IF EXISTS servicehub_app_rw;DROP ROLE IF EXISTS servicehub_report_ro;

8. Lab acceptance test and handoff to Chapter 02

Recreate the database from your saved schema and seed script, connect with the application and report accounts, and confirm the expected permissions and row counts. The goal is not merely to have tables; it is to prove you can recreate the same known state from scratch.

Verification checklist

• servicehub uses the intended utf8mb4 collation.
• All application tables report ENGINE=InnoDB.
• The seed query returns exactly three work orders with the expected status distribution.
• The application account has read/write DML but no broad administrative privileges.
• The reporting account can read and is denied a write.
• You recorded server version, SQL mode, charset/collation, and package/topology assumptions.
• You can drop and recreate the entire lab deterministically.

Chapter 02 now uses this reproducible lab to examine MariaDB/MySQL compatibility and divergence. Because the schema and accounts are deterministic, you can compare behavior across engines/versions without confusing data drift with product differences.

Authoritative references

• MariaDB Documentation — CREATE DATABASE
• MariaDB Documentation — CREATE USER
• MariaDB Documentation — CREATE ROLE
• MariaDB Documentation — GRANT
• MariaDB Documentation — SET DEFAULT ROLE
• MariaDB Documentation — SQL_MODE
• MariaDB Documentation — Setting Character Sets and Collations
• MariaDB Documentation — Storage Engines Overview