17. The miraj-migrate migration tool

miraj-migrate copies an existing SQL database (schema, views and rows) to a running MIRAJ server. It reads the source in read-only mode over the network protocol: the source is neither stopped nor modified, so the tool can be rerun as many times as needed before the cutover.

17.1 Easier migration from a legacy-protocol server#

The source is a SQL server reached through the legacy protocol, the one MIRAJ also speaks in the MIRAJ language: migrating therefore does not require rewriting your queries or changing the application connector (JDBC driver, ODBC, PDO, etc.). In practice:

1. Migrate the data with miraj-migrate run (see §17.3).
2. Point your applications at the MIRAJ server, changing only the host and port in their connection string: the source port (3306 in general) becomes 7007 (MIRAJ). The account, password and database name remain unchanged once the accounts are recreated (§17.6).
3. Restart the applications: they keep sending the same SQL queries, with the same connector, without any code change.

This compatibility covers the common SQL language and connection protocol. Objects specific to the original engine (triggers, stored routines, events, accounts and privileges) are not copied automatically: see §17.6, "What is not migrated".

17.2 General syntax#

miraj-migrate inspect --source-db <database> [source options]
miraj-migrate run     --source-db <database> [source and target options]
                      [--drop-existing] [--skip-data] [--batch-rows <n>] [--only t1,t2]
miraj-migrate --help

Exit codes:

The source is always read through the legacy protocol (--source-protocol has no effect). The target is joined in the protocol of its language, recognized at connection time (--protocol auto, default; see 17.8).

17.3 Commands#

17.4 Options#

Source

Without --source-password, the password is read from the MIRAJ_SOURCE_PASSWORD environment variable if it exists.

Target (MIRAJ server)

Without --password, the password is read from the MIRAJ_PASSWORD environment variable if it exists.

Behavior of run

Password. Written on the command line (--password=secret, or --password secret as in previous versions), it is visible to the other users of the machine in the process list: the tool then prints a warning. In interactive use, give --password alone: it is asked at the keyboard, without echo. In a script, use the environment variables (MIRAJ_PASSWORD, MIRAJ_SOURCE_PASSWORD), set by the scheduler or by a file readable only by the service account (0600 permissions on Linux). A password that starts with a hyphen is written --password=-mypassword.

TLS encryption. The two connections (source and target) are set separately, the source options carrying the --source- prefix:

On the MIRAJ side, TLS is enabled with --tls-cert and --tls-key when the server starts (chapter 15); --require-tls then refuses clear-text connections.

17.5 How a migration proceeds#

1. Connection to the source and to the target (both in full UTF-8, emoji included); creation of the target database if necessary. On the target, the migration session turns off foreign key checking (FOREIGN_KEY_CHECKS = 0) and the maximum duration of a statement (max_statement_time = 0), which a bulk write could exceed.
2. For each table (sequences first, since a default value may call NEXTVAL): reading of its definition on the source, creation on the target (with --drop-existing, prior deletion) with the next AUTO_INCREMENT number of the source, then copy of the rows by multi-row INSERT.
3. Verification: the source row count, the count of rows read and the target row count must be identical.
4. For each view: creation on the target, then a check read (SELECT … LIMIT 1). The source DEFINER account is removed, as it generally does not exist on the target. A view that depends on a view not yet created is retried in the next pass, as long as a pass creates at least one.
5. Summary: number of tables migrated, number of failures, objects to be handled by hand; the details of the failures are written to the error output (- table x : …).

Tables are processed one after another; a failure on one table does not stop the following ones. Foreign key constraints are recreated with the table; the copy is done without checking the order between tables, since the source has already guaranteed consistency.

Example:

> miraj-migrate inspect --source-db gestion --source-host 10.0.0.5 --source-user lecture
Base source « gestion » ([email protected]:3306) : 2 table(s), 1 vue(s)
  table clients                                          3001 ligne(s)
  table lignes                                          15005 ligne(s)
  vue   v_soldes
Total : 18006 ligne(s)
À reprendre manuellement — déclencheurs : maj_solde

> miraj-migrate run --source-db gestion --source-host 10.0.0.5 --source-user lecture --drop-existing
Migration de « gestion » ([email protected]:3306) vers « gestion » ([email protected]:7007, langage MIRAJ)
  table clients                                          3001 ligne(s) copiée(s), vérifiée(s)
  table lignes                                          15005 ligne(s) copiée(s), vérifiée(s)
  vue   v_soldes                                 créée
À reprendre manuellement — déclencheurs : maj_solde
Bilan : 2 table(s) migrée(s), 0 échec(s)

17.6 What is not migrated#

• Triggers, stored routines and events: towards a target in MIRAJ language, they are only listed ("À reprendre manuellement", i.e. "to be redone manually"). Their body depends on the source dialect and must be reviewed, then recreated with MIRAJ SQL (chapters 6 and 7). Towards a target in PostgreSQL language, routines and triggers are translated to PL/pgSQL when possible (17.8).
• Accounts and privileges: to be recreated with CREATE USER and GRANT (chapter 14).
• Concurrent writes: the copy is not a snapshot. For a database still in use, suspend writes during run, or rerun run --drop-existing just before the cutover.
• Content: verification compares row counts, not values. For a stronger check, compare aggregates or a per-table fingerprint on both servers.

Table definitions are replayed as the source provides them: a clause that MIRAJ rejects appears as a failure for that table, with the error message of the MIRAJ server (chapter 27).

The ENGINE clause follows the same rule: an InnoDB table from the source becomes an in-memory table in MIRAJ, an Aria or MyISAM table becomes a disk table (<table>.dmrj, chapter 6, "Storage engines"). To keep in memory a large table declared MyISAM at the source, or the reverse, change the engine after the migration with ALTER TABLE … ENGINE = ….

17.7 Common error messages#

17.8 PostgreSQL-language target#

A target instance in PostgreSQL language (POSTGRESQL element of sql_mode, see chapter 29) is recognized at connection time (--protocol auto, or --protocol postgresql to force it). Migration there differs in three ways:

• rewritten definitions: the source's CREATE TABLE and CREATE VIEW are re-read by MIRAJ and written in PostgreSQL language ("…" identifiers, PostgreSQL types, indexes as CREATE INDEX, TINYINT(1) as boolean, ENUM kept); a definition that MIRAJ cannot re-read makes the table or view fail ("définition non convertible en langage PostgreSQL");
• rows loaded with COPY … FROM STDIN (text format), as the source is read, in one statement per table: an error writes no row of the table; --batch-rows is irrelevant. Bytes are sent as \x…, dates and numbers in their text form;
• routines and triggers translated to PL/pgSQL (without --only): each procedure, function and trigger of the source is re-read (SHOW CREATE) then translated as best as possible: blocks, variables, IF, CASE, labeled loops, cursors without parameters, EXIT handlers (EXCEPTION clause), a single-assignment CONTINUE HANDLER FOR NOT FOUND handler, SIGNAL (RAISE); a trigger becomes a RETURNS trigger function of the same name and a CREATE TRIGGER … EXECUTE FUNCTION. A construct without translation (SELECT returning rows to the client, @x variables, cursors with parameters, other CONTINUE handlers, SQLWARNING, session statements…) or a definition refused by the target gives a warning quoting the definition in a comment, without counting as a failure. Translation difference: a SELECT … INTO with no row sets its targets to NULL in PL/pgSQL (MIRAJ leaves them unchanged). Events remain listed ("À reprendre manuellement").

The final check (source and target row counts) is the same.