16. Server Administration

This chapter is intended for administrators who operate miraj-server, the Miraj network server. It covers startup and shutdown, configuration (command line and XML file), the layout of the data folder, durability, operational security, monitoring, backup, and performance tuning.

16.1 Starting and stopping the server#

16.1.1 Basic command#

miraj-server.exe --root D:\data --lang fr --log

--root designates the server's data folder (one folder per database, plus a miraj subfolder for accounts and internal logs). If it is omitted, the server uses a data folder relative to the current directory.

For troubleshooting and account maintenance (see 16.5.3), the server has a dedicated mode:

miraj-server.exe --root D:\data --reset-accounts [--key-dir <folder>]

16.1.2 Stopping the server#

The only orderly shutdown of miraj-server is the SQL statement:

SHUTDOWN;

It requires the SHUTDOWN privilege. The server writes all modified tables, answers the client, writes one last time whatever arrived in the meantime, then the process exits (SHUTDOWN: server stopped at a client's request. in the console or the log). A stop through this statement therefore leaves nothing to replay at restart.

The server does not handle any termination signal, however: whether the stop comes from a Ctrl-C, from the process being closed (service stop, kill), or from a machine power loss, no final checkpoint is written before exit. Every stop that does not go through SHUTDOWN must therefore be treated, from the administrator's point of view, as an abrupt stop: it is the write-ahead log (WAL) and its replay at restart that guarantee data integrity (see 16.4). This differs from embedded use of Miraj (miraj.dll library): there, closing the instance writes everything before returning control.

A data folder is opened by only one process at a time: the miraj.lck file in the root is locked as long as the server runs. miraj-cli, miraj-backup --root or miraj-dump --root launched on this folder in the meantime fail with "Data folder … already opened by another Miraj process"; go through the network instead.

16.1.3 Command-line options#

All options are optional; the default values are those applied when neither the miraj_config.xml file nor the command line changes them (see 16.2 for the order of precedence).

The MCP endpoint and the REST endpoint each have their own options (--mcp-*, --rest-*); the detailed tables are in chapters 20 and 23. The archive journal options (continuous incremental backup) are set through miraj_config.xml and SET GLOBAL, not on the command line (see 19.1.5).

Note on terminology: command-line option names use hyphens (--long-query-time), while XML keys and session variables use underscores (long_query_time); this chapter follows this convention throughout.

16.1.4 Replication (Cluster edition)#

The Cluster edition adds multi-node replication: a primary node writes, secondary nodes receive its log continuously and replay it read-only. Clients connect to a secondary on its usual client port (7007 by default); any write attempt there fails with error

1. Nodes communicate with each other on a separate port, over mutual TLS (certificate signed by the cluster authority). Configuration is done through --cluster-config:

[node]
id = "n1"                    # stable node identifier
listen = "0.0.0.0:7107"      # inter-node port (not the clients' port)
advertise = "10.0.0.1:7107"  # address advertised to other nodes

[cluster]
name = "gestium-prod"
seeds = ["10.0.0.1:7107", "10.0.0.2:7107"]
tls_cert = "node.pem"        # paths relative to the file
tls_key = "node-key.pem"
tls_ca = "cluster-ca.pem"
ack = "written"              # or "durable": the secondary flushes its
                              # log to disk before acknowledging
journal_retention_mb = 1024  # beyond this, a secondary that was absent is
                              # re-seeded upon its return

At first startup, all nodes are secondaries. The primary role is designated once with:

SET GLOBAL cluster_role = 'primary';

the other nodes then join the primary (copy of the databases, then log stream). Promoting a secondary uses the same command; a former primary restarted after a failover is set aside (@@cluster_role = 'FENCED', read-only) until it is made to rejoin the new primary:

SET GLOBAL cluster_role = 'secondary';

its unreplicated writes are then set aside in <root>\miraj\quarantaine. Replication monitoring:

SHOW CLUSTER STATUS;
SELECT * FROM information_schema.MIRAJ_NODES;
SELECT * FROM information_schema.MIRAJ_REPLICATION;
SELECT @@read_only, @@cluster_role, @@cluster_epoch, @@cluster_primary;

16.2 The miraj_config.xml configuration file#

Miraj accepts configuration through an XML file, modeled on a classic .cnf/.ini file. Three levels combine, in this order of increasing priority:

1. internal default value;
2. value from the miraj_config.xml file;
3. command-line argument (highest priority).

The file read is, in order: the path given by --config; miraj_config.xml in the folder of miraj-server.exe, if it exists there; otherwise <root>\miraj_config.xml (created with a commented skeleton if it is missing). The path retained is written at startup (Configuration: …). A variable that is absent or commented out keeps its default value. Excerpt:

<?xml version="1.0" encoding="UTF-8"?>
<miraj>
    <!-- Language of session error messages (en, fr, zh, hi, es, ar, pt, ru, de, ja) -->
    <!-- <language>en</language> -->
    <!-- Save policy (relaxed, statement, periodic) -->
    <!-- <save_policy>relaxed</save_policy> -->
    <!-- Server listening address -->
    <!-- <bind>127.0.0.1</bind> -->
    <!-- Server TCP listening port -->
    <!-- <port>7007</port> -->
    <!-- Maintain the <folder>/miraj/server.log file -->
    <!-- <log>false</log> -->
</miraj>

To apply a value, uncomment the line and change the value; the rest of the file (the other commented-out variables) does not need to be modified.

A second file, miraj_default.xml, is regenerated at every startup of the server: it lists all recognized variables with their current default value. It is a reference file, never read back by the server — do not write your own settings in it, they would be erased at the next startup. Use it to retrieve, version after version, the complete list of available keys and their default value.

The keys of the XML file cover the same settings as the command-line options: language, save_policy, save_interval, checkpoint_interval, checkpoint_large_table, lock_wait_timeout, sql_mode, deferred_update, concurrency, key_dir, data_encryption, default_table_encryption, encryption_passphrase_file (chapter 25), lob_threshold, lob_cache, event_scheduler, parallel_threads, slow_query_log, slow_query_log_file, long_query_time, max_statement_time, group_concat_max_len, max_statement_memory, max_server_memory, min_free_disk_space, secure_file_priv, backup_dir, journal_archive, journal_archive_dir, journal_archive_max_lag (see 19.1.5), require_password, local_infile, change_events_buffer_events, change_events_buffer_size, change_events_bulk_rows, change_events_max_payload, change_events_max_listeners, change_events_wait_timeout, change_events_retention, log_bin, server_id, gtid_domain_id, max_binlog_size, binlog_expire_logs_seconds, expire_logs_days, sync_binlog, binlog_checksum, gtid_strict_mode, log_slave_updates (see 17.13.1), bind, port, log, result_buffer, write_timeout, connect_timeout, idle_timeout, max_allowed_packet, tls_cert, tls_key, require_tls, allow_plain_with_tls, max_connections, max_connect_errors, initial_root_password, cluster_config, license_file (chapter 31), proxy_protocol_from, mcp, mcp_port, mcp_bind, mcp_idle_timeout, mcp_statement_timeout, mcp_max_rows, rest, rest_port, rest_bind, rest_idle_timeout, rest_statement_timeout, rest_max_rows, rest_cors_origins, rest_endpoints, rest_tables, rest_sql, rest_basic, rest_basic_access. Only --lazy-databases, --journal-gap-replay, --reset-accounts, --install-license, --machine-id and --config have no XML key (checkpoint_interval, checkpoint_large_table and journal_archive* have no command-line option). The exact list, with the default value of each, is that of miraj_default.xml.

initial_root_password is read only when the account vault is created: remove it from the file once the server has started, it is there in clear text.

checkpoint_interval (300000 ms, i.e. 5 min, by default) has no equivalent on the command line: it is the interval between two checkpoints (see 16.4.2), adjustable only through this file. The same goes for checkpoint_large_table (64 MB by default), the size from which an in-memory table is no longer rewritten at every periodic checkpoint (see 16.4.2).

Recommended usage: the XML file is suited to a permanent setting, specific to an instance (one data folder), that you do not want to repeat at each launch in a script or a Windows service; the command line remains useful for a one-off setting (diagnostics, test script) that must take precedence over the file.

16.3 Layout of the data folder#

<root folder>/
  miraj_config.xml       configuration (if present)
  miraj_default.xml      reference of default values, regenerated at startup
  miraj.lck              folder lock: one process at a time
  journal.reserve        space reserve (4 MiB) returned to the volume when it is full
  miraj-bin.NNNNNN.mrb   binary log, if --log-bin (index: miraj-bin.mrbi, see 12.13)
  replica-source.json    configuration of a replica (CHANGE MASTER TO, see 12.13.4)
  <database>/
    <table>.mrj          one table = one file (MIRA v2 format)
    <table>.bmrj         store of the table's long BLOBs (created on first deposit)
    <table>.dmrj         a disk table (ENGINE = Aria / MyISAM / DISK): rows in pages
    <table>.dbmrj        large values of a disk table (created on first deposit)
    <table>.dmrj.ckpt    checkpoint in progress for a disk table (temporary)
    doublewrite.mrw      double write of disk-table pages (reserved, not yet written)
    journal.mrl          write-ahead log (WAL) of the database
    sequences.mrq        sequences of the database (definition and state), if it has any
    views.mrv, routines.mrp, triggers.mrt, events.mre
                         views, procedures and functions, triggers, events
    replica.mrs          replication state of the node (Cluster edition)
  miraj/
    accounts.mra         encrypted account vault
    miraj.lic            license installed by --install-license (chapter 31)
    server.log           server log, if --log is active
    slow.log             slow query log, if enabled
    quarantaine/          unreplicated writes of a FENCED node (Cluster edition)

Practical points for administration:

• Each database is a subfolder of the root folder; each table is a .mrj file in that subfolder. Creating or dropping a database amounts to creating or deleting that subfolder (done by the server itself through CREATE DATABASE / DROP DATABASE, never by hand while the server is running).
• A BLOB that exceeds --lob-threshold (8192 bytes by default) is deposited in the .bmrj file of its table rather than in the .mrj; this file only appears after the first deposit of a BLOB of that size. Both files must be copied together: a .mrj without its associated .bmrj loses its offloaded BLOB values. After lowering --lob-threshold, values already stored that reach the new threshold move to the .bmrj when the database is opened (the table is rewritten at the next checkpoint). On the primary of a cluster, this move goes through the log, within a cap of 256 MiB per table (see 17.7): beyond that, the values remain in the table, readable, and an AVERTISSEMENT line is written to the server log.
• A disk table is a .dmrj file instead of the .mrj (see Storage engines): its rows stay there, only its indexes are loaded into memory, with a 256 MB page cache (not configurable for now). Its values larger than a quarter of a page go to <table>.dbmrj; as with the .bmrj, both files go together. Modified pages stay in memory until the checkpoint, which first writes them, together with the superblock and, after a DDL statement (COMMENT, CREATE / DROP INDEX…), the pages of the new schema, to <table>.dmrj.ckpt, synced, then in place: a complete .ckpt found at opening is replayed (it repairs a page torn by a power loss during the write in place), an incomplete .ckpt is ignored (nothing had yet been written in place). Do not delete this file by hand. No page is written outside this path; the doublewrite.mrw file (one per database), read back at opening, is not used yet: it is reserved for the early writing of modified pages, to come. A damaged page (failing medium, altered file) is reported by CHECK TABLE and repaired by REPAIR TABLE; the superblock and the schema each have a copy in the file, which takes over and rewrites them at opening (see Table maintenance).
• journal.mrl is the database's write-ahead log: it makes it possible to replay the writes made after each table's last checkpoint in the event of an abrupt stop (see 16.4). Never delete or modify it by hand.
• The account vault miraj/accounts.mra is specific to the data folder; its decryption key is stored elsewhere (see 16.5.1). Copying a data folder without its key makes the vault unreadable at restart.
• Backup: with the server stopped (see 16.1.2 and 16.7), a plain copy of the root folder (databases, miraj/, configuration files) is enough to obtain a consistent image of it, provided that you also copy the account vault key if it is needed for the restoration (see 16.5.1 and 16.7).

16.4 Durability and crash recovery#

16.4.1 Save policies (save_policy)#

Miraj writes each modification statement to the log (WAL) of the database concerned before considering the statement complete; how this log reaches the disk depends on the chosen policy:

These three policies can also be chosen per session, without changing the server setting:

SET SESSION save_policy = 'statement';

(a fourth value, manual, exists internally but is refused as a server policy: it would never write anything unless the session orders it, which makes no sense as a default policy).

How to choose:

• statement maximizes safety (no possible loss, including during a power outage) at the cost of a per-statement write latency tied to the disk — to be chosen for data that no loss, however small, can be allowed to affect.
• relaxed (the default setting) offers a good compromise: no loss in the event of a stop of the server process alone (the most frequent case in practice), and a loss window bounded by --save-interval only in case of a power loss of the machine itself.
• periodic further reduces write latency in exchange for an identical loss window in both cases (process or machine) — to be reserved for workloads where write throughput takes precedence over strict durability guarantees.

In all cases, the log itself remains consistent: what may be lost are the very last writes not yet flushed, never the integrity of the file.

16.4.2 Checkpoints#

A checkpoint consolidates the current state of the tables and makes it possible to truncate the write-ahead log accordingly. It is triggered automatically:

• every checkpoint_interval milliseconds (300,000 ms, i.e. 5 min, by default — adjustable only via miraj_config.xml, see 16.2);
• when the log exceeds 64 MB;
• on the clean closing of an embedded instance.

An in-memory table is written in full to its .mrj file. So as not to rewrite every 5 min a large table of which only a few rows have changed, the periodic checkpoint skips in-memory tables of at least checkpoint_large_table megabytes (64 by default): their modifications stay in the log, which protects them exactly as before. They are written at the full checkpoint (64 MB log reached, SHUTDOWN, clean closing of the instance), or by a statement that needs their file to be up to date (RENAME TABLE, REPAIR TABLE…). A hot backup (BACKUP) copies the log along with them.

The log is then shortened down to the oldest file that still needs it, and only if the rotation removes at least as much as it copies. Safety in case of a power loss does not change, it depends on save_policy (see 16.4.1). In return, recovery after an abrupt stop may replay up to 64 MB of log instead of one minute of writes. Disk tables are never deferred: their checkpoint only writes the modified pages. checkpoint_large_table at 0 writes all tables at every checkpoint, as before.

A log flush or a checkpoint that fails in the background (full disk, file held by an antivirus, privileges removed) is retried at every cycle and recorded in the server log, once per database and per cause, without repetition at each cycle: SAUVEGARDE: checkpoint of database … impossible: …, then SAUVEGARDE: … restored when it succeeds again. As long as the failure lasts, modifications remain protected by the log, which grows.

The flush to disk of the log itself (fsync) is an exception: if it fails (failing medium, lost network volume), the system may have lost bytes that it reported as written, and a later successful flush would not prove otherwise. The database's log is then stopped: the statement that was waiting for this flush (statement policy), then every following write on this database, return error 1026 (58030 in PostgreSQL language), whose message names the log file and states that the log is stopped and that the outcome of the last commits is unknown; no more writes are accepted on this database, reads continue, and checkpoints no longer write anything to it. Fix the cause then restart the server: the database restarts from what its log actually holds on disk. The last commits, already visible to other sessions at the time of the failure, are to be checked after the restart.

A statement whose write to the log fails (full disk, for example) has no effect: nothing of it is visible or kept pending, and it can be rerun once the cause is removed.

16.4.3 Deleted rows and OPTIMIZE TABLE#

A DELETE marks rows as deleted without removing them right away. They are physically removed (compaction):

• during writes, as soon as they reach 20% of the table's rows;
• at every checkpoint (see 16.4.2), whatever their number.

The BLOB store (.bmrj) is compacted automatically only beyond 64 MB, when at least half of its content has become orphaned.

OPTIMIZE TABLE t [, …] does both right away, without any threshold condition: deleted rows removed, .mrj file rewritten, BLOB store compacted if it contains orphaned bytes. It is used after a large purge, or to choose the moment of the rewrite (night maintenance).

OPTIMIZE TABLE ventes, ventes_lignes;

The result has one or more rows per table (Table, Op, Msg_type, Msg_text):

• info: number of deleted rows removed, BLOB store compacted;
• last status row: OK, Table is already up to date (nothing to remove) or Operation failed (unknown table, view, or table held by an open transaction or a LOCK TABLES: compaction is then deferred to the next checkpoint where the table is free).

The statement commits the session's open transaction and takes the catalog for writing: other statements wait for the end of the rewrite. The SELECT and INSERT privileges on each table are required. With the manual policy, rows are removed in memory and the file is written only at the explicit save. In the Cluster edition, the compaction done on the primary is journaled and replayed by the secondaries; on a secondary, OPTIMIZE TABLE does nothing (note). Accepted options with no effect: NO_WRITE_TO_BINLOG, LOCAL, WAIT n, NOWAIT.

16.4.4 Recovery after an abrupt stop#

At startup, the server opens the table files of each database and then replays the log records that follow the LSN (log sequence number) already present in each .mrj file. File writes are atomic (temporary file, flush, then replacement — on Windows through MoveFileExW with MOVEFILE_WRITE_THROUGH), and the BLOB store (.bmrj) is always flushed to disk before the log and the table file that reference it: an abrupt stop can at worst leave orphaned BLOB bytes in the .bmrj, never a reference without content. This mechanism is proven by abrupt stops of the process and by power cuts (disk cut in the middle of a write), during writes, ALTER TABLE statements and index creations, on in-memory tables as on disk tables: a write that was committed and flushed to disk is never lost, and a damaged file is reported rather than misread.

Damaged files found at opening: before any repair, the files concerned are copied to <root>\miraj\quarantaine\<timestamp>\<database>\ with a rapport.txt, and each finding is recorded at startup (RÉCUPÉRATION: … lines of the server log).

• End of the log: an interrupted write (incomplete last record, run of zeros) is removed silently; this is the normal effect of an abrupt stop. An unreadable end longer than one record, with nothing valid after it (media wear rather than an interrupted write), is copied to quarantine then removed, with a finding.
• Damaged zone in the middle of the log: only the records that precede it are replayed; those that follow it stay in the quarantined copy. --journal-gap-replay replays them too, for one startup: they may depend on the lost modifications, and a table for which a record does not apply keeps what was applied before it (finding forced replay: …). Tables completed this way are written immediately.
• .mrj and .dmrj files of the same table (interrupted engine conversion): the most recent is kept, a .mrj with invalid content loses; the other is copied to quarantine before being removed. If one of the two cannot be read (file held by an antivirus), nothing is deleted and the table stays unreadable until the next startup.
• Damaged table file (truncated, emptied, unreadable end): it is never read back as a smaller table or as one with no writes to replay; the table is reported unreadable (finding at startup, error when it is used). Restore it from a backup; for a disk table, REPAIR TABLE … EXTENDED recovers the rows of the readable pages. Bytes added after the valid end of a table file are ignored, and the log is replayed normally.
• Unreadable miraj system database (log held by an antivirus, disk in error): its opening is retried for a few seconds, then the server starts without it. It is marked unavailable (directory intact), the other databases are served, and only local connections are accepted: a remote host receives error 9048, on the main port as well as on the REST and MCP endpoints. The server log announces it (BASE SYSTÈME INDISPONIBLE (…)); fix the cause then restart the server.

As noted in 16.1.2, miraj-server has no clean shutdown: it is precisely this recovery mechanism, and not a shutdown sequence, that guarantees data integrity at each restart.

16.4.5 Memory and disk space safeguards#

Three settings keep a statement or a full disk from endangering the server. Each cleanly refuses the statement concerned: the session stays open and usable, and the data stays intact.

0 lifts each of these limits. Reads, ROLLBACK, DELETE, TRUNCATE, DROP and KILL remain allowed below the free-space threshold: they are what frees up room. The journal.reserve file of the data folder (4 MiB) is returned to the volume when it is full, so that a flush or a deletion can still be written to the log. A disk that is really full also gives error 1021 (or 1114 for the table being written), rather than an internal error.

SET max_statement_memory = 1048576;   -- 1 MiB for the session
SELECT v FROM t ORDER BY v DESC LIMIT 15000, 1;
SELECT v, COUNT(*) FROM t GROUP BY v LIMIT 1;

ERREUR 1038 (HY001) : Out of sort memory: the statement exceeds max_statement_memory (1 Mio)
ERREUR 1114 (HY000) : The table '#sql_tmp (1 Mio)' is full

SET GLOBAL max_server_memory = 100000;
SELECT DISTINCT v FROM t LIMIT 1;

ERREUR 9067 (HY001) : Out of server memory: the statements running on the server would exceed max_server_memory (100000 octets); retry later

SET GLOBAL min_free_disk_space = 900000000000000;
INSERT INTO gestion.clients VALUES (50, 'Bloqué', NULL);

ERREUR 1021 (HY000) : Disk full (15829 MiB free on the volume of 'D:\donnees\gestion', below min_free_disk_space (858306884 MiB)); the write was refused, free some space and retry

SET GLOBAL … = DEFAULT restores the starting value. The memory currently held by the server's statements can be read in SHOW GLOBAL STATUS LIKE 'Miraj_server_memory_used'.

These limits do not spill to disk: a sort or grouping that is too large is refused, not written to a temporary file. The result returned to the client, tables, indexes and caches are not counted.

The text of a query is bounded in the same way: a single submission (one statement, or a script of several statements sent as one block) counts at most 2 million syntax elements (a value of an INSERT … VALUES or of an IN list counts as one, a column, a table or a SELECT as a few) and 8 million tokens (words, numbers, signs). Beyond that, it is refused with error 7620 (SQLSTATE 54001, also in PostgreSQL language) before any execution:

ERREUR 7620 (54001) : Statement too complex: more than 2000000 syntax elements in one script (split it into several statements)

An INSERT … VALUES of more than two million values (on the order of 25 MB of ordinary rows) must therefore be split into several statements. miraj-dump restore and miraj-cli connected to a server send a file statement by statement: only a statement that is too large is refused. miraj-cli opened on a data folder, however, runs a .sql file as a single block (chapter 26): a very large script is better loaded through the console connected to a server.

16.5 Operational security#

16.5.1 Account vault and key management#

Accounts (users, passwords, privileges, roles) are kept in an encrypted vault authenticated with AES-256-GCM, <root>\miraj\accounts.mra — never logged, written atomically, protected against the restoration of an old copy by an increasing generation number.

The 32-byte key that protects this vault is stored outside the data folder:

• on Windows, it is encrypted with DPAPI for the machine (readable by any account of that machine, including SYSTEM at startup with no open session), in %LOCALAPPDATA%\MIRAJ\keys;
• on other systems, in a file with 0600 permissions;
• --key-dir <folder> (or the XML key key_dir) lets you choose another location, for example to share the key between several services or to store it on a medium separate from the data folder.

Consequence for backup and deployment: copying only the data folder is not enough to be able to reopen the accounts on another machine or under another Windows account; the key (or its --key-dir folder) must also be transferred, protecting it at least as well as an administrator password.

16.5.2 Initial password, connections and network listening#

Initial password of root. At the first startup of miraj-server on a folder, the account vault is created with the local root account as its only account, which receives a 24-character password drawn at random by the system generator. It is displayed only once on the console, with the command to change it, and is written nowhere (neither in server.log, nor in the vault other than as a verifier):

================================================================================
Compte root@localhost créé avec le mot de passe provisoire : Xq7mPt3hKc9WbZ2rNf8sLd4v
Il n'est affiché qu'une fois et n'est écrit nulle part : notez-le, puis changez-le :
  ALTER USER root@localhost IDENTIFIED BY 'nouveau mot de passe';
================================================================================

With a password set in advance, the console only displays where it came from:

Compte root@localhost créé avec le mot de passe initial donné par --initial-root-password (à retirer ensuite).

For an automated installation, set it yourself through --initial-root-password, through the environment variable MIRAJ_INITIAL_ROOT_PASSWORD (preferable: a process's command line is visible to other users) or through the key initial_root_password of the configuration file; it is then not displayed. An empty value passed on the command line or through the variable is refused (the server does not start). When the vault is created, server.log records where the password came from (command line, variable, configuration file or random draw), never the password itself. These settings are ignored, with a note, as soon as the vault exists, unless the local root there has remained without a password (vault written by the embedded engine, miraj-cli for example, before the first startup of the server): it then receives the initial password. A local root without a password is flagged by a warning at every startup. In a cluster, only the primary creates accounts: a secondary that starts on a blank folder without an initial password creates a waiting vault whose root is inaccessible, then receives the accounts from the primary. Replace the temporary password at the first connection:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'un mot de passe robuste';

Network listening. By default the server listens on the loopback interface (127.0.0.1). Choosing a non-local listening address (--bind) without TLS triggers a warning, and the server flags the accounts without a password that would then become reachable remotely. As long as require_password is active (default), such an account cannot in fact be created (error 9033, see 15.2).

Number of connections. At most --max-connections connections (151 by default) are served at a time; each takes its place before its session is created. Beyond that, the client receives error 1040 ("Too many connections") in place of the handshake, and the refusal is reported at most once a minute in the log. A handshake must complete within --connect-timeout seconds, TLS included, counted from acceptance.

Authentication failures. Each wrong answer (error 1045) is counted for the client's address: the answer is delayed by 100 ms on the first failure, doubling at each following failure, 5 s at most. After --max-connect-errors consecutive failures (100 by default), the address is blocked for 15 minutes, a delay restarted by each new attempt: its connections are refused at acceptance with error 1129, even with the right password. A successful authentication resets the address's count to zero. Blocks and repeated refusals are written to the log (SÉCURITÉ: …). To unblock all addresses:

FLUSH HOSTS;   -- RELOAD privilege

Addresses with no failure for an hour are forgotten, and at most 10,000 addresses are tracked (the least recently active is evicted beyond that). Note: all clients of one machine share its address, the loopback included; a script that keeps sending the wrong password can block the other clients of its machine.

Identity before authentication. As long as a connection has not completed its authentication, it belongs to no account: SHOW PROCESSLIST shows it as unauthenticated user, only to the accounts that see all sessions (PROCESS), which are the only ones able to stop it with KILL.

16.5.3 --reset-accounts: recovery procedure#

An account vault that is altered, deleted, or whose key is missing/unreadable prevents the server from starting. A vault deleted while its key still exists is refused (message "accounts.mra a disparu alors qu'une clé existe pour ce dossier") as soon as the folder contains data: a database, or in miraj/ a file other than a log (*.log). Deleting the vault therefore does not give access to root without a password. The only case accepted: a data folder deleted entirely then recreated at the same path, with no database at all. The key of the old folder, left in the keys folder, is then ignored (detached from that path, not erased: an old folder moved elsewhere finds it again), a "new data folder" note is written at startup and a new vault is created as at first startup.

The local recovery procedure is:

miraj-server.exe --root D:\data --reset-accounts [--key-dir <folder>]

This command recreates the vault with the local root as its only account, then exits (it does not start the network server). root receives a new password drawn at random, displayed only once on the console as at the first startup, or that of --initial-root-password (or of MIRAJ_INITIAL_ROOT_PASSWORD), which is not displayed. It must be run locally, with access to the data folder and, implicitly, access equivalent to that of the key. After a --reset-accounts:

• all previously defined accounts and privileges are lost (only the local root remains); recreate the application accounts and grant back the necessary privileges;
• replace the temporary password of root without delay (see the ALTER USER command above).

Use this procedure only as a last resort (corrupted vault, lost key, administrator account locked with no other access): it restores nothing, it starts over from a blank account state.

16.5.4 Recommendations for a production deployment#

• In the Enterprise or Cluster edition, encrypt data at rest from installation (--data-encryption ON, chapter 25) and keep the passphrase off the machine.

• Keep listening on the loopback interface (127.0.0.1, the default value) as long as an application firewall or tunnel does not protect wider access; choose a non-local address only after having secured all accounts with passwords.
• Enable TLS (--tls-cert / --tls-key) as soon as clients connect across a network that is not physically isolated: it is then required of all clients. Use --allow-plain-with-tls only for the time needed to migrate clients that cannot encrypt yet.
• Adjust --max-connections to the number of expected clients and leave --max-connect-errors at a low value: a password-guessing attempt is slowed down then blocked.
• Keep the account vault key (--key-dir) separate from the backup medium of the data folder, or clearly document that it must be copied separately during a restoration.
• Leave secure_file_priv empty (default value, LOAD_FILE disabled) unless explicitly needed; if you enable it, point it to a dedicated folder, distinct from the data folder, the keys folder, that of the TLS key and the backups folder (the server refuses to start otherwise).
• Systematically start with --log (see 16.6) to keep a trace of SQL errors and internal incidents.
• Keep in mind the absence of a clean shutdown (16.1.2): plan the save policy (16.4.1) according to the data loss you can tolerate in the event of a power loss, rather than counting on a careful shutdown of the service.

16.6 Monitoring#

16.6.1 SHOW STATUS and SHOW VARIABLES#

SHOW VARIABLES;              -- current session/server settings
SHOW VARIABLES LIKE 'save_%';
SHOW STATUS;                 -- activity counters (session scope)
SHOW GLOBAL STATUS;          -- same counters, for the whole server
SHOW STATUS LIKE 'Miraj_%';

SHOW VARIABLES returns the same values as SELECT @@name for each variable; this is what common client tools (graphical consoles, connectors) read at connection time.

SHOW STATUS (or SHOW SESSION STATUS, information_schema.SESSION_STATUS) returns the counters Questions, Com_*, Bytes_*, Handler_*, Select_*, Sort_*, Created_tmp_* and Innodb_rows_* of the session alone; SHOW GLOBAL STATUS (information_schema.GLOBAL_STATUS) returns those of all sessions since startup. The other counters exist only for the server and have the same value in both scopes. A statement is counted as soon as it starts: the SHOW STATUS that reads the counters counts itself.

SHOW STATUS exposes, as of now, in particular:

This list will grow over the course of versions; consult SHOW STATUS without a filter for the complete list of the running instance.

16.6.2 information_schema#

information_schema (read-only) provides a structured view useful for administration, in particular: SCHEMATA, TABLES, COLUMNS, STATISTICS, VIEWS, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, as well as the privilege tables USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, APPLICABLE_ROLES, ENABLED_ROLES. An account sees, in SHOW DATABASES, SHOW TABLES and information_schema, only what it has the privilege to access.

In the Cluster edition, information_schema.MIRAJ_NODES and information_schema.MIRAJ_REPLICATION complement SHOW CLUSTER STATUS for replication monitoring (see 16.1.4).

Other monitoring views: PROCESSLIST (like SHOW PROCESSLIST), INNODB_TRX (open transactions), PARTITIONS, ENDPOINTS and ENDPOINT_PRIVILEGES (REST API, chapter 24), ROUTINE_PRIVILEGES (rights per procedure or function, chapter 15), ENGINES, COLLATIONS, COLLATION_CHARACTER_SET_APPLICABILITY, GLOBAL_VARIABLES / SESSION_VARIABLES (like SHOW VARIABLES, upper-case names), GLOBAL_STATUS / SESSION_STATUS (like SHOW STATUS), KEYWORDS (keywords of the reference and of Miraj's parser, combined) and SQL_FUNCTIONS (functions actually available, those specific to Miraj included). FILES, TABLESPACES, INNODB_LOCKS and USER_STATISTICS exist for tools but remain empty (@@userstat is OFF, like the reference by default).

The performance_schema and sys databases also exist, read-only (writing to a table: 1142; creating a table or a view, CREATE / DROP DATABASE: 1044), for administration tools. As in the reference, these two databases require a privilege: an account with no right on them does not see them (SHOW DATABASES, information_schema) and cannot read them (1142, USE: 1044); GRANT SELECT ON performance_schema.* TO … opens them.

performance_schema offers global_variables, session_variables, global_status, session_status and threads (one row per session, seen like SHOW PROCESSLIST), and statement instrumentation, kept in memory since the server started (@@performance_schema is ON):

• events_statements_summary_by_digest: one row per digest and per current database. The digest (DIGEST, 32 hexadecimal digits) is that of the normalized statement text (DIGEST_TEXT): literals and parameters replaced by ?, keywords in upper case, identifiers in backticks, comments removed, value lists reduced to (...). Two statements that differ only in their values therefore accumulate together: executions (COUNT_STAR), durations in picoseconds (SUM_, MIN_, AVG_, MAX_TIMER_WAIT, SUM_LOCK_TIME), errors, warnings, rows affected, sent and examined, full reads (SUM_NO_INDEX_USED, SUM_SELECT_SCAN), sorts, first and last execution. No literal is kept there (neither password nor data). The normalized text is cut at 1024 bytes; beyond 10,000 digests, the following ones are accumulated on a row with no digest (DIGEST set to NULL).
• accounts, users, hosts: open sessions and connections since startup, by account, by user and by host.
• table_io_waits_summary_by_index_usage: one row per index of each table, with the number of reads that went through it (COUNT_STAR, COUNT_READ, COUNT_FETCH; durations and writes at 0).

TRUNCATE TABLE resets these summaries (DROP privilege on the table), as in the reference, and global_status like FLUSH STATUS; the other tables (session_status included) refuse any write. The setup and history tables (setup_*, events_waits_*, events_statements_current, _history, _history_long, events_stages_history_long, session_connect_attrs, summaries by table, by file and of memory) exist with their columns but remain empty.

-- The ten statements that cost the most time since startup
SELECT query, db, exec_count, total_latency, avg_latency, rows_examined_avg, full_scan
FROM sys.statement_analysis LIMIT 10;

-- Indexes never read since startup
SELECT * FROM sys.schema_unused_indexes;

sys offers version, sys_config, processlist and session (open sessions: current statement, its duration, its examined rows and its lock wait; last finished statement, under its normalized text without literals, and its duration), statement_analysis (digests from the most to the least costly, durations written in plain form: 1.50 ms), user_summary and host_summary (statements, total and average duration, full reads, open and total connections by user and by host) and schema_unused_indexes (indexes never read since startup, primary keys excepted — to be read after a representative period of activity). schema_table_statistics, schema_index_statistics and the memory_* views remain empty; files and memory are not recorded (columns set to NULL).

A database created by an earlier version under the name sys or performance_schema would be hidden by the virtual database: its directory is renamed at startup (sys_base, performance_schema_base), it remains accessible under that name and the server log reports it.

MIRAJ_PARALLELISM gives one row: edition, budget threads and busy threads, chunk size (MORSEL_ROWS), queries run in parallel, serially or reduced, and fair share, the same figures as the Miraj_parallel_* counters.

Tables specific to Miraj for concurrency: MIRAJ_CONCURRENCY (model in use, active transactions, purge watermark, oldest transaction and deferred update counters), MIRAJ_VERSIONS (pending versions, per table) and MIRAJ_DEFERRED_UPDATE (deferred update, one row of counters accumulated since startup):

SELECT INTENTS_RECORDED, ROWS_APPLIED, COMMIT_FAILURES, FAILED_READ_VALIDATION,
       NORMAL_KEY, NORMAL_TRIGGER
FROM information_schema.MIRAJ_DEFERRED_UPDATE;

INTENTS_RECORDED counts deferred UPDATEs, ROWS_APPLIED the rows written at COMMIT, IMAGES_MATERIALIZED the deferred UPDATEs caught up by an ordinary write of the same transaction; COMMIT_FAILURES the COMMITs refused for a transaction that had deferred UPDATEs, broken down by cause (FAILED_WITNESS: row changed so that the WHERE or a column read by an AFTER UPDATE trigger no longer holds; FAILED_ROW_GONE, FAILED_CONCURRENT_WRITER, FAILED_WARNING, FAILED_TABLE_REDEFINED, FAILED_ERROR, FAILED_READ_VALIDATION: the transaction had read a row modified since); ENGINE_RETRIES the autocommit statements replayed by the engine after a conflict; the NORMAL_* columns the UPDATEs of a session set to ON that could not be deferred, by reason (SESSION, FORM, TABLE, TRIGGER, KEY, ROW_COUNT, ROW, CALCULATION). A high FAILED_READ_VALIDATION indicates transactions that read a row before updating it (see 14.2).

16.6.3 Logging#

• --log (or log in the XML file) maintains <root>\miraj\server.log: this file receives, timestamped, only the SQL errors returned to clients (with the statement at fault), internal failures (captured panics, never passed to the caller) and server operating problems. Successful queries do not appear there; connections and errors are however also displayed on standard output.
• Passwords and encryption keys are never written in clear text in these logs: the text of a statement is kept there with IDENTIFIED BY '<secret>', PASSWORD('<secret>'), SET PASSWORD … = '<secret>', AES_ENCRYPT(x, '<secret>')… The same goes for the Info column of SHOW PROCESSLIST and of information_schema.PROCESSLIST, visible to other accounts. The miraj-cli console does not write these statements to its history on disk (they can still be recalled during the session).
• --slow-query-log (or slow_query_log / slow_query_log_file) records in <root>\miraj\slow.log (or the chosen file) each statement longer than --long-query-time seconds, with the volume sent to the client (Bytes_sent). SET GLOBAL log_output = 'TABLE' switches this log to the miraj.slow_log table; FLUSH SLOW LOGS reopens the file after an external rotation.
• At startup (--slow-query-log-file or XML file), the path of the slow log is free, absolute or relative to the data folder. At run time, SET GLOBAL slow_query_log_file = '…' is confined: the path must be relative, with no .., drive letter or :, and its folder must already exist in the data folder (otherwise, in the folder of the file set at startup by an absolute path) without leaving it through a link. A symbolic link, a folder, an engine file (.mrj, .mrl…) or the account vault's keys folder are refused. Every refusal returns error 1231; SHOW WARNINGS gives the reason. To write the log elsewhere, set the path at startup.

16.6.4 FLUSH#

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] option [, option] … requires the RELOAD privilege (1227 otherwise) and implicitly commits the open transaction, as in the reference. Options:

TABLES is used alone (FLUSH TABLES, STATUS: 1064, as in the reference); an unknown option gives 1064.

Global read lock. FLUSH TABLES WITH READ LOCK waits for the end of writes already started (at most lock_wait_timeout seconds, then 1205), writes the tables to disk and keeps the lock until UNLOCK TABLES or the end of the session. Meanwhile:

• all sessions read freely;
• any write by another session — INSERT, UPDATE, DELETE, LOAD DATA, DDL, accounts and GRANT, LOCK TABLES … WRITE, OPTIMIZE / REPAIR TABLE, RESTORE, and the COMMIT of a transaction that has written — waits without holding anything, then fails with 1205 beyond lock_wait_timeout (SHOW PROCESSLIST: Waiting for global read lock); the session's temporary tables remain modifiable;
• the session that holds the lock receives 1223 if it writes; it can additionally take LOCK TABLES … READ, and UNLOCK TABLES releases both. Under LOCK TABLES, FLUSH TABLES WITH READ LOCK is refused (1192).

Several sessions can hold the lock at the same time. This is what backup tools use when they copy files or read several databases in a frozen state:

FLUSH TABLES WITH READ LOCK;
BACKUP DATABASE gestion TO 'gestion-fige';   -- allowed: BACKUP reads
-- … file copy, reading positions …
UNLOCK TABLES;

Cluster edition: the lock is local to the node. On a primary, it holds back the writes of this node's clients, not the application of the log on the secondaries; on a secondary, it does not hold back the application of the log received from the primary.

Binary log (--log-bin): its writes are made at commit, under the registration of the committing write; the lock therefore stops them at the same point as the tables, and the position read by SHOW MASTER STATUS under the lock no longer moves until UNLOCK TABLES. On a replica (CHANGE MASTER TO, chapter 17), the SQL thread is held back, as in the reference, but group by group: a group being applied is finished before the lock is granted, the next one waits with no delay or error (SHOW SLAVE STATUS, Slave_SQL_Running_State: Waiting for global read lock); STOP SLAVE interrupts this wait and returns the group to the queue. A backup taken under the lock on a replica therefore sees a frozen state, at the position @@gtid_slave_pos read at the same time.

16.7 Backup and restore#

The server backs up a database hot, without stopping it. Start it with a backup folder (--backup-dir, see 16.1.3), then:

BACKUP DATABASE gestion TO 'gestion-2026-09-24';
RESTORE DATABASE gestion_copie FROM 'gestion-2026-09-24';

The copy is consistent, with all transactions committed up to one instant and none beyond. Writes are suspended for only a few milliseconds. The miraj-backup tool drives these statements from a scheduled task, and miraj-dump exports a database as a portable SQL script, accounts included. Everything is detailed in chapter 19.

These backups do not contain the account vault. Export the accounts with miraj-dump --users, or cold-copy the miraj\ subfolder and the vault key (see below). Their manifest is signed with a key derived from that of the vault: a server that has a different key restores the backup with a warning, a forged manifest is refused (see 19.1.4).

Cold copy of the complete folder, for a change of machine with the accounts, for example:

1. Stop the server. As noted in 16.1.2, miraj-server offers no clean shutdown sequence: treat every stop as an abrupt stop. It is log recovery (16.4.4) that guarantees a consistent folder at the next startup.
2. Copy the data folder (--root) in its entirety: databases (.mrj, .bmrj, .dmrj and .dbmrj of disk tables, journal.mrl), miraj\ subfolder (accounts, internal logs) and any XML configuration files.
3. Copy the account vault key separately if it is needed for the restoration on another machine or under another Windows account (see 16.5.1): it is not in the data folder.
4. To restore, put this folder (and the key, if needed) back at the expected location, then restart the server normally.

For an embedded database (use through miraj.dll, outside the network server), the normal closing of the instance writes everything before returning control: a cold backup after this closing does not have to worry about an abrupt stop.

16.8 Performance: settings to know about#

Parallel execution (Enterprise and Cluster editions — the Express edition runs each query on the single thread of its session, and the Developer edition limits the server budget to a single thread) spreads large reads across several threads: filters, projections, aggregation, sorting, DISTINCT, INNER/LEFT joins, and the underlying read of UPDATE/DELETE/INSERT ... SELECT — with, in all cases, the same results and the same order as in serial execution.

Two settings control this parallelism:

• --parallel-threads <n> (server): sets the total number of threads that all the server's queries may occupy together. By default, one thread per core of the machine. Reduce it if the server shares the machine with other services, or adjust it if measurements show contention between concurrent queries.
• max_parallel_degree (session variable): bounds the degree of parallelism of a given session, within the limit of the server budget above. Useful for reserving most of the threads for interactive processing while letting a background job (import, bulk recalculation) run at a lower degree, or the reverse.

EXPLAIN displays the degree of parallelism chosen for a given query, which makes it possible to check that a setting has the expected effect before generalizing it. EXPLAIN ANALYZE (or ANALYZE, see 9.21) executes the query and additionally returns the actual rows and timings of each operator: the way to spot the costly step of a slow query recorded in the slow query log. Without sending the rows to the client, it really executes the query, and ANALYZE UPDATE|DELETE really modifies the rows: run it with the same care as an ordinary statement.

Full-text search: score and memory#

The score of MATCH … AGAINST is that of the reference by default. For a ranking that takes the length of the records into account (BM25, chapter 9, §9.22), the application sets SET miraj_ft_ranking = 'bm25' when opening its sessions (session variable only; miraj_ft_bm25_k1 and miraj_ft_bm25_b tune the formula). Fuzzy search (word~1, IN FUZZY MODE) scans the index dictionary at planning time: of the order of a millisecond for 20,000 distinct words; miraj_ft_fuzzy_max_expansions bounds the number of words retained per searched word. FULLTEXT indexes are kept in memory and rebuilt when each table is opened: allow of the order of 500 MB and 25 s per million rows of 56 words, of which 4 bytes per row for the length of the rows used by the BM25 score.

Automatic switch between single-core and multi-core#

The Enterprise and Cluster editions switch by themselves between multi-core and single-core execution, with no setting:

• According to load. The server counts the statements currently running. A query receives only its fair share of threads: the budget divided by that number. Alone, it takes all the cores. With two, it takes half. As soon as there are as many running statements as threads, each one runs on the single thread of its session, as in single-core: no extra thread comes to compete with other sessions for the cores. A waiting session (SLEEP, WAIT FOR CHANGES, table lock of another statement or of a LOCK TABLES) does not count. Threads are returned as soon as the read ends, without waiting for the client to have received the whole result.
• According to the query. A GROUP BY aggregation first processes one chunk on the session's thread. If it finds almost one group per row there (grouping on a key), it continues serially. In that case, merging the threads' partial results would cost more than parallelism brings.
• According to table size. A distributed read is cut into about 8 chunks per thread, of at least 16,384 rows and at most parallel_morsel_rows rows (131,072 by default). On a table of a million rows, all the cores thus receive work, and the trial chunk of a GROUP BY delays only a small share of it.

The counters Miraj_parallel_statements_active, Miraj_parallel_fair_share and Miraj_parallel_queries_serialized_load / _downgraded / _volume (see 16.6) show these decisions. EXPLAIN indicates the degree that the query requests. The degree it obtains at execution depends on the load of the moment. --parallel-threads and max_parallel_degree remain ceilings: SET max_parallel_degree = 1 always forces serial execution of a session.

For BLOB volumes, --lob-threshold and --lob-cache (see 16.1.3 and 16.3) also influence performance: a lower threshold offloads more values out of the table files (useful if rows must remain compact in memory), a larger cache reduces repeated reads of large BLOBs at the cost of the memory it occupies.

16.9 Graphical manager Miraj Server Manager#

On Windows, Miraj Server Manager (miraj-manager.exe, installed by the Miraj Express wizard) gathers the usual administration tasks of a server on the machine, from an icon in the notification area:

• start, stop and restart the server, through the "Miraj Express" scheduled task of the installation or by launching miraj-server.exe --root <data> --log;
• follow its state, its processor, memory and disk consumption, with the curves of the last ten minutes;
• follow queries: total number (Questions), queries per second, running queries (SHOW FULL PROCESSLIST), slow queries (Slow_queries, miraj.slow_log) and setting of the slow query log;
• make a full backup (BACKUP ALL DATABASES) to a chosen folder, immediate or scheduled, with a dedicated miraj_backup account limited to BACKUP_ADMIN.

Its texts follow the language variable of miraj_config.xml. Its operation is described in chapter 22.

16.10 Instance in PostgreSQL language#

A Miraj instance speaks a single SQL language, chosen at startup by the POSTGRESQL element of sql_mode:

• without it (default), Miraj language and legacy protocol: Gestium, miraj-cli, graphical tools and drivers of the legacy protocol;
• with it, PostgreSQL language and protocol: psql, psycopg2/psycopg, psqlODBC, Npgsql, pgjdbc and the tools built on them.

Miraj's tools (30.12), Miraj's ODBC driver (chapter 29) and the miraj Python library (27.12) recognize the instance's language at connection and speak both.

The choice is made by --sql-mode POSTGRESQL,STRICT_TRANS_TABLES, by <sql_mode>POSTGRESQL,STRICT_TRANS_TABLES</sql_mode> in miraj_config.xml, by the SQL language choice of the configuration editor (which adds or removes the POSTGRESQL element alone without touching the others), by the question of the installation wizard or, on macOS, by the Instance SQL language choice of the first configuration in Miraj Server Manager. Changing language requires a restart; the databases and their data remain valid. Startup announces it:

MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, langage PostgreSQL, dossier "D:\donnees", 10 fils, licence ….

16.10.1 A single port#

The PostgreSQL protocol is served on the main port (--port, 7007 by default), in place of the legacy protocol: there is no second port. miraj-proxy (PROXY v2 header from a trusted relay, --proxy-protocol-from) and the firewall need no change. The connection rules are those of the legacy protocol: connect_timeout, wait_timeout, max_connections, blocking of an address after max_connect_errors failures, max_allowed_packet, KILL, local-only mode.

A client that picks the wrong language is closed with a clear message on standard output and in server.log:

• legacy-protocol client (Gestium, a Miraj tool launched with --protocol miraj…) on a PostgreSQL instance: it waits for the server greeting, which waits for its startup message; after connect_timeout, the connection is closed with "client without PostgreSQL startup message (instance language: PostgreSQL, see sql_mode)";
• PostgreSQL client on a Miraj-language instance: closed immediately with "PostgreSQL client on a Miraj-language instance (sql_mode without POSTGRESQL)".

Miraj's tools (miraj-cli, miraj-dump, miraj-backup, miraj-migrate, the managers, the miraj-proxy probe) recognize the instance's language themselves (--protocol auto, default): their first connection to a Miraj instance is such a probe, closed without being counted in Aborted_connects (30.12).

16.10.2 Encryption and authentication#

• TLS: with --tls-cert and --tls-key, the server accepts the clients' encryption request (sslmode=prefer, require, verify-ca, verify-full) with the same certificate as the legacy protocol; without a certificate, it declines it and a client in sslmode=prefer continues in clear. TLS required (default as soon as a certificate is given, except --allow-plain-with-tls): a clear connection is refused (FATAL 28000).

• Accounts: the same as in the Miraj language (CREATE USER, hosts, roles, locking). The authentication mechanism depends on the account:

  • SCRAM-SHA-256 (password_encryption = scram-sha-256) for any account whose password was set by CREATE USER … IDENTIFIED BY, ALTER USER … IDENTIFIED BY or SET PASSWORD, in either language, and for root (initial password): the server keeps, next to the usual verifier, a SCRAM verifier (random salt, 4,096 iterations) in the encrypted account vault. The password does not travel: the connection is accepted with or without TLS, from any address. The server only advertises SCRAM-SHA-256; a client that requires TLS channel binding (channel_binding=require) is refused (FATAL 28000), channel_binding=prefer (default) works.
  • Clear-text password (AuthenticationCleartextPassword) for an account with no SCRAM verifier: password given by its hash (IDENTIFIED BY PASSWORD '*…'), or set before SCRAM was supported (older data folder). It is requested only under TLS or from the loopback address: a clear connection from another machine, or relayed by a trusted relay, is refused before the password travels (FATAL 28000, with a message inviting to redefine the password). To give such an account a SCRAM verifier, simply redefine its password (ALTER USER 'name'@'host' IDENTIFIED BY '…', or SET PASSWORD by the holder); the hash alone does not allow it.
  • None for an account with no password: accepted without asking anything.

  Wrong password: FATAL 28P01, counted against the address as in the legacy protocol (slowdown, blocking after max_connect_errors). An unknown account receives the same SCRAM exchange (stable salt derived from its name) and the same 28P01 refusal: nothing distinguishes an unknown account from a wrong password. Only an existing account with no SCRAM verifier, contacted in clear from another machine, is recognized by its 28000 refusal. Neither the password, nor the proof, nor the SCRAM messages are written to server.log; the log notes the mechanism used (account app@% (SCRAM-SHA-256, TLS)). rolpassword remains masked (******** in pg_roles, NULL in pg_authid).

• Connection database (dbname): miraj, postgres or the user name (client defaults) are ignored; the name of a Miraj database makes it the current database; an unknown database gives a notice (NOTICE 3D000) and the connection opens with no current database.

16.10.3 Connecting with psql#

$env:PGPASSWORD = "password"
psql -h 127.0.0.1 -p 7007 -U root -c "SELECT 1"
psql "host=server.example port=7007 user=root dbname=gestion sslmode=require"

A query sent by psql -c or typed in the console may contain several statements; each returns its result and its tag (SELECT 3, INSERT 0 1, UPDATE 2, CREATE TABLE…). Warnings arrive as WARNING or NOTICE, errors with a PostgreSQL SQLSTATE (42P01 missing table, 42703 unknown column, 23505 duplicate, 42601 syntax, 3D000 unknown database…). Ctrl-C cancels the running query (57014, like KILL QUERY). In a BEGIN … COMMIT block, an error fails the block: only ROLLBACK (or COMMIT, which then rolls back) and ROLLBACK TO SAVEPOINT are accepted afterwards, other statements receiving 25P02.

\copy loads or writes a file on the client machine (text or CSV) through COPY … FROM STDIN and COPY … TO STDOUT (30.11):

boutique=# \copy articles (code, nom, prix) from 'articles.csv' with (format csv, header)
COPY 1250
boutique=# \copy (SELECT * FROM ventes WHERE jour >= '2026-10-01') to 'ventes-octobre.txt'
COPY 312

The server works only in UTF-8 (client_encoding = UTF8): under Windows, switch the console to UTF-8 (chcp 65001) or set PGCLIENTENCODING=UTF8, otherwise accented text typed in another code page is refused (22021).

psql's description commands read the pg_catalog catalog (30.9): \l shows the single PostgreSQL database miraj, \dn the Miraj databases (each one is a schema), \dt, \dv, \di, \ds and \d the tables, views, indexes and sequences of the current database (that of the dbname parameter, or of SET search_path), \d table and \d+ table their columns (PostgreSQL types, default values, identity), indexes, constraints and foreign keys, \du the accounts, \df the routines, \dx the vector extension, \dp the tables (without detailed rights), \x the extended display. \dt boutique.* lists the tables of another database.

boutique=# \d clients
                        Table « boutique.clients »
  Colonne  |         Type          | ... | NULL-able |            Par défaut
-----------+-----------------------+-----+-----------+----------------------------------
 id        | integer               |     | not null  | generated by default as identity
 nom       | character varying(80) |     | not null  |
 solde     | numeric(12,2)         |     |           | 0
Index :
    "clients_pkey" PRIMARY KEY, btree (id)
Référencé par :
    TABLE "commandes" CONSTRAINT "commandes_ibfk_1" FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE

16.10.4 Drivers: extended protocol, Npgsql, pgjdbc#

Drivers that prepare their queries (Npgsql, pgjdbc, and any library that sends $1, $2… parameters) use the extended protocol: named or anonymous prepared statements (Parse), portals (Bind), description (Describe), execution (Execute), closing (Close), Sync and Flush.

• Parameters $1, $2…: the same number may appear several times and out of order (WHERE a = $1 OR b = $1). Each value is bound by the engine, never copied into the SQL text. Values in text or binary: int2/int4/int8, oid, float4/float8, bool, numeric, text/varchar/bpchar/name, bytea, date, time, timestamp, timestamptz (instant brought back to the session time zone), uuid, json/jsonb; NULL. A date-time in text followed by an offset (2026-10-06 12:30:00+02, as pgjdbc sends setTimestamp) loses it, like '…'::timestamp on the reference server.
• Parameter type (ParameterDescription): the one the client imposes, otherwise text.
• Results in text or binary, at the client's choice for each column: integers, floats, bool, numeric (exact binary format, up to the 18 digits of DECIMAL), bytea, texts, date, time, timestamp, timestamptz (text followed by the session offset, binary in UTC microseconds), uuid. A value that cannot be converted to the type announced to the client is refused (22003, 22P02) rather than sent wrong; a zero date (0000-00-00) has no binary form (22008).
• Reading in chunks (pgjdbc setFetchSize in a transaction): the result is kept in memory by the server and sent in packets of the requested size.
• Errors: after an error, the following messages are ignored until Sync, as on the reference server; in a BEGIN block, the block fails.
• Batches (pgjdbc executeBatch, NpgsqlBatch): writes sent between two Syncs outside a block form a transaction, committed at Sync or rolled back entirely if one fails.
• Cancellation: NpgsqlCommand.Cancel(), Statement.cancel() and Ctrl-C also interrupt a prepared query (57014).
• RETURNING: Describe of an INSERT, UPDATE or DELETE statement with a RETURNING clause describes its columns; pgjdbc's getGeneratedKeys (which adds RETURNING * or RETURNING "id") and Npgsql's ExecuteReader read the written rows, the tag remaining that of the write (INSERT 0 1).
• COPY: in simple query only (CopyInResponse, CopyData, CopyDone, CopyFail; CopyOutResponse, CopyData, CopyDone): pgjdbc's CopyManager, Npgsql's BeginTextImport and BeginTextExport, psycopg2's copy_from, copy_to and copy_expert. Binary format (Npgsql's BeginBinaryImport) not supported.
• Catalog: columns taken from a table announce the OID of their table and their number (RowDescription, as pg_class and pg_attribute give them); Npgsql loads at opening the types of pg_type (base types, arrays, enumerations), pgjdbc and DBeaver read pg_namespace, pg_class, pg_attribute, pg_index and pg_constraint (see 30.9).
• Session parameters: the SET statements sent by drivers (extra_float_digits, application_name, DateStyle, TimeZone, client_encoding…) are accepted and readable by SHOW; client_encoding remains UTF8, DateStyle ISO. SET search_path, BEGIN ISOLATION LEVEL …, SET SESSION CHARACTERISTICS AS TRANSACTION … and SHOW TRANSACTION ISOLATION LEVEL (Npgsql BeginTransaction, pgjdbc setTransactionIsolation) are read by the PostgreSQL grammar (chapter 30); DEALLOCATE and DISCARD ALL also release the prepared statements of the extended protocol.

Drivers tried:

DBeaver goes through pgjdbc: its browser (schemas, tables, columns, keys, indexes) and row editing rely on the metadata tried above.

await using var conn = new NpgsqlConnection("Host=serveur;Port=7007;Username=root;Password=…;Database=gestion");
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand("SELECT nom, prix FROM articles WHERE prix > $1", conn);
cmd.Parameters.Add(new NpgsqlParameter { Value = 10.5m, NpgsqlDbType = NpgsqlDbType.Numeric });

16.10.5 What is available at this stage#

The PostgreSQL language today includes:

• simple and extended protocol, results in text or binary, types announced by their PostgreSQL OID (int2/int4/int8, numeric, float4/float8, bool, text/varchar/bpchar, date, time, timestamp, timestamptz, interval, bytea, json/jsonb, uuid, bit/varbit, enumerations, {…} arrays of one expression, and for the catalog oid, name, "char"); the notices (NOTICE, WARNING) of a statement that fails are sent before its error;
• the pg_catalog catalog and PostgreSQL's functions (version(), format_type, pg_get_indexdef, ::regclass…): psql's \d commands, pgjdbc and DBeaver metadata, Npgsql types and GetSchema (30.9);
• the PostgreSQL grammar ("x", standard '…', ::, $1, ILIKE, FETCH FIRST, SERIAL, SET search_path, cursors…), with Miraj's extensions (SHOW TABLES, USE, vector indexes, MATCH … AGAINST…); Miraj's own syntax (backticks, LIMIT a, b…) is refused there: see chapter 30;
• SCRAM-SHA-256 authentication (16.10.2);
• INSERT … RETURNING, UPDATE … RETURNING, DELETE … RETURNING, INSERT … ON CONFLICT, COPY … FROM STDIN / TO STDOUT in text and CSV, and COPY … TO 'file' on the server (30.11);
• functions, procedures, triggers and anonymous DO blocks written in PL/pgSQL or in SQL (PostgreSQL language manual, chapter 40);
• Miraj tools (miraj-cli in network mode, miraj-dump, miraj-backup, miraj-migrate, managers, installer, miraj-proxy probe), protocol recognized at connection, and clusters in PostgreSQL language (30.12);