MariaDB / Basic: relational foundations / Lesson 1
MariaDB server, client and storage engines
Build a reliable mental model of a MariaDB session before reading or changing data. Distinguish SQL behavior from client behavior and storage-engine guarantees.
On this page
Lesson goals
- Identify server version, session identity and database context.
- Explain why engine selection changes transactional guarantees.
A connection crosses several boundaries
MariaDB Server is the process that authenticates connections, parses SQL and coordinates storage. The mariadb command-line program is one client; a web application uses a connector instead. A database is a namespace containing tables, views and routines, not a separate server process. The same SQL text can behave differently under another SQL mode, collation or engine. Record those conditions before diagnosing a result, and use only a disposable training instance for any examples that create objects.
Inspect the session deliberately
The first query returns context, not application records. VERSION() reports the server, so a locally installed client version does not prove which server accepted your connection. DATABASE() is NULL until a default schema is selected. CURRENT_USER() names the authenticated account used for privilege checks; USER() describes the connection identity. Neither function grants permission. Values below depend on the environment, so no invented hostname or version is presented as an observed result.
SELECT VERSION(), DATABASE(), CURRENT_USER(), USER();
SELECT @@session.sql_mode, @@session.autocommit;Read both result rows as a session fingerprint. Autocommit is commonly enabled, but inspect it rather than assuming.
Choose an engine for the contract
A storage engine owns important physical and concurrency behavior. InnoDB supplies transactions, crash recovery and foreign-key enforcement for this course. Aria is useful in MariaDB and is also used for internal work, but its crash-safe design does not make ordinary Aria tables transactional like InnoDB. MyISAM has another contract. SHOW ENGINES identifies what this actual build supports; availability of a plugin is not a reason to assign every application table to it.
Separate capability from table choice
SHOW ENGINES lists supported engines and their capabilities. INFORMATION_SCHEMA.TABLES tells you the engine assigned to an existing base table. An empty metadata result can mean the table is absent, the schema name differs or privileges hide it; it does not prove that InnoDB is unavailable. MariaDB and MySQL share ancestry and protocol conventions but have diverged. Use MariaDB documentation for engine options and version support instead of assuming instructions for another product are interchangeable.
SHOW ENGINES;
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'harbor_lab' AND TABLE_TYPE = 'BASE TABLE';The first statement describes capabilities; the second inventories only visible training tables. Both are inspection examples.
Client syntax and a safe learning boundary
The semicolon normally ends a statement in the command-line client. DELIMITER, used later for stored routines, is a client directive rather than SQL understood by the server. A successful connection says nothing about whether changing data is appropriate. This course never asks you to connect to an application database: examples are explanatory and marked non-runnable because they were reviewed by reasoning, not executed here. Read warnings and error codes before retrying a failed statement.
Common errors and fixes
ERROR 1046 (3D000): No database selected
- Why it happens
- An unqualified table name needs a default database.
- How to fix it
- Select the intended lab schema or qualify schema.table.
- Prevent it next time
- Print DATABASE() before object work and record the target.
Rollback appears to do nothing
- Why it happens
- The changed table may use a nontransactional engine, or autocommit already committed.
- How to fix it
- Inspect the engine and transaction boundary; restore training data from its fixture.
- Prevent it next time
- State engine and session assumptions alongside each mutation.
ERROR 2002 (HY000): connection failure
- Why it happens
- The selected local socket endpoint may not have a listening server.
- How to fix it
- Check the authorized endpoint and service information without changing authentication.
- Prevent it next time
- Distinguish socket connections from TCP and keep endpoints explicit.
Check your understanding
1. A teammate reports that their client is MariaDB 11.4. What evidence identifies the server and the engine of harbor_lab.shipments?
Show worked solution
Use SELECT VERSION() on the intended training connection, then inspect INFORMATION_SCHEMA.TABLES with both schema and table predicates. Report the returned server string and engine separately. The client binary version alone proves neither, and no result should be guessed when the table is absent.
2. An order update and audit insert must succeed together. Choose InnoDB or ordinary Aria and justify the choice.
Show worked solution
Choose InnoDB for both tables and place the two writes in one explicit transaction. Crash safety alone is insufficient: the business requirement is atomic rollback of both changes. Also verify foreign-key and durability settings later; selecting an engine is necessary but does not define the entire recovery policy.
Key takeaways
- Inspect server, session and engine independently before interpreting behavior.
- Compatibility is a question to verify for a particular MariaDB version.