This sent me to the route of looking into a self contained orchestrator binary + backend DB. I would have orchestrator spawn up its own backend database instead of connecting to an external one.

Why even relational?

Can’t orchestrator just use a key-value backend?

Maybe it could. But frankly I enjoy the power of relational databases, and the versatility they offer has proven itself multiple times with orchestrator, being able to answer interesting, new, complex questions about one’s topology by crafting SQL queries.

Moreover, orchestrator is already heavily invested in the relational model. At this time, replacing all SQL queries with key-value reads seems to me as a significant investment in time and risk. So I was looking for a relational, SQL accessible embeddable database for orchestrator.

Why sqlite?

I am in particular looking at two options: sqlite (via the go-sqlite3 binding) and TiDB. sqlite does not need much introduction, and I’ll just say it’s embeddable within the golang-built binary.

TiDB is a pure-Go, MySQL dialect compatible server which provides relational model on top of key-value store. The store could be local or distributed, and the TiDB project cleverly separates involved layers.

Of the two, sqlite is mature, alas uses a different SQL dialect and has different behavior. TiDB’s compatibility with MySQL is an impressive feat, but still ongoing.

Both require adaptations of SQL code. Here are some observations on adaptations required when moving an existing app from MySQL backend to sqlite backend.

Differences

Just to answer an obvious question: can’t everything be abstracted away by an ORM that speaks both dialects?

I don’t think so. I always exploit SQL beyond the standard insert/delete/update/select, exploits that ORMs just don’t support.

Here’s an incomplete list of differences I found. Some purely syntactical, some semantical, some behavioral, and some operational.

• Data types: no CHARACTER SET clause
• Data types: you can’t associate UNSIGNED to any int type
• Data types: no enum. However there’s an alternative in the form of:
  race text check (race in ('human', 'dog', 'alien'))
• auto_increment is called integer
• Data types: timestamps are not a thing. There’s no timezone info.
• TIMESTAMP has no ON UPDATE clause.
• No after clause for adding columns
• Indexes are not part of table creation. Only PRIMARY KEY is. The rest of indexes are created via CREATE INDEX statement
• Indexes have unique names across the schema. This is unfortunate, since it forces me to use longer names for indexes so as to differentiate them. For example, in MySQL I can have an index named name_idx in two different tables; in sqlite I append table name for “namespacing”
• Temporal values and functions: poor support for time arithmetic.
  • Getting the diff between two datetimes is non-trivial (what’s the diff in months for a leap year?)
    
  • INTERVAL keyword not respected. Appending/subtracting dates can be done via:
    datetime('now', '-3 hour')
    Can you see the problem in the above? What is the number 3 is a positional argument? In MySQL I would use NOW() - INTERVAL ? HOUR. To make positional arguments work in sqlite, the above gets to be datetime('now', printf('-%d hour', ?)). How would you even translate NOW() - INTERVAL (? * 2) SECOND?
  • UNIX_TIMESTAMP not respected. Instead using strftime('%s', 'now'), I dislike the use of string functions to generate times.
• insert ignore turns to insert or ignore
• replace into remains replace into. Yay!
• insert on duplicate key update has no equivalent. It’s worthwhile noting a replace into does not replace (pun intended) an insert ... on duplicate key as the latter can choose to only update a subset of column in the event of a constraint violation. It’s really very powerful.
• IS TRUE and IS FALSE are not respected.
• ALTER TABLE:
  • When adding a not null column one must specify the default value (e.g. 0 for an int)
  • You cannot add a timestamp column that defaults to CURRENT_TIMESTAMP. You can have such column in your CREATE TABLE definition, but you cannot add such a column. The reason being that CURRENT_TIMESTAMP is not a constant value. When adding a column to a table, sqlite does not actually apply the change to all existing rows, but only to newly created ones. It therefore does not know what value to provide to those older rows.
  • You cannot DROP COLUMN. I’ll say it again. You cannot DROP COLUMN
  • You cannot modify a PRIMARY KEY
  • You cannot rename a column or change its datatype.
  • In fact, the only supported ALTER TABLE statements are ADD COLUMN and RENAME (renaming the table itself)

Regarding the ALTER TABLE limitations, the advice for dropping/changing/renaming columns or changing the primary key is to “create a new table with the new format, copy the rows, drop the old table, rename”. This is certainly feasible, but requires a substantial overhead from the user. And it’s non atomic. It requires a change in the state of mind but also a change in state of operations, the latter being non-trivial when moving from one DBMS to another, or when wishing to support both.

I’m still looking into this, and trying to work my way around differences with cheap regular expressions for as much as possible. I’m mainly interested right now in finding all semantic/logic differences that would require application changes. So far the TIMESTAMP behavior is such, and so is the INSERT ... ON DUPLICATE KEY statement.

1. World peace
2. Good health to all
3. Relaxed GTID constraints

I’m still not using GTID, and still see operational issues with working with GTID. As a latest example, our new schema migration solution, gh-ost, allows us to test migrations in production, on replicas. The GTID catch? gh-ost has to write something to the binary log. Thus, it “corrupts” the replica with a bogus GTID entry that will never be met in another server, thus making said replica unsafe to promote. We can work around this, but…

I understand the idea and need for the Executed GTID Set. It will certainly come in handy with multi-writer InnoDB Cluster. However for most use cases GTID poses a burden. The reason is that our topologies are imperfect, and we as humans are imperfect, and operations are most certainly imperfect. We may wish to operate on a replica: test something, by intention or mistake. We may wish to use a subchain as the seed for a new cluster split. We may wish to be able to write to downstream replicas. We may use a 3rd party tool that issues a flush tables with read lock without disabling sql_log_bin. Things just happen.

For that, I would like to suggest GTID control levels, such as:

1. Strict: same as Oracle’s existing implementation. Executed sets, purged sets, whatnot.
2. Last executed: a mode where the only thing that counts is the last executed GTID value. If I repoint replica, all it needs to check is “hey this is my last executed GTID entry, give me the coordinates of yours. And, no, I don’t care about comparing executed and purged sets, I will trust you and keep running from that point on”
3. Declarative: GTIDs are generated, are visible in each and every binary log entry, but are completely ignored.

I realize Oracle MySQL GTID is out for some over 3 years now, but I’m sorry – I still have reservations and see use cases where I fear it will not serve me right.

How about my previous years wishes? World peace and good health never came through, however:

• My 2015 wish for “decent, operations friendly built in online table refactoring” was unmet, however gh-ost is a thing now and exceeds my expectations. No, really. Please come see Tom & myself present gh-ost and how it changed our migration paradigm.
• My 2012 wish for “decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL” was met by MariaDB’s window functions.
  Not strictly Window Functions, but Oracle MySQL 8.0 will support CTE (hierarchial/recursive), worth a mention.

See you in Amsterdam!

... where state in ('started', 'completed', 'failed') ...

However I’ve had a few cases where I used an inverted format. Here’s one use case followed by an inverted IN clause.

Dynamic query building

Say we have this function:

GetLaggingSlaves(clusterName string)

Which, based on whether given clusterName is empty or not, would return list of all lagging slaves, or only those in the given cluster, respectively:

SELECT hostname FROM database_instance WHERE slave_lag_seconds > 60

or

SELECT hostname FROM database_instance WHERE cluster_name = 'mycluster:3306' AND slave_lag_seconds > 60

To avoid SQL injection you would create a prepared statement, but you don’t want to copy+paste everything, and so you build your query dynamically based on the value of clusterName. You want to end up with either:

SELECT hostname FROM database_instance WHERE slave_lag_seconds > ?

, acceptableLag

or

SELECT hostname FROM database_instance WHERE cluster_name = ? AND slave_lag_seconds > ?

, clusterName, acceptableLag

Dynamic query building is good practice, but a little pain (BTW I’m designing a new, simple & non intrusive query builder for golang). Is there a way to just get away with one query that has it all?

This is one option:

SELECT hostname FROM database_instance WHERE (cluster_name = ? OR ? = '') AND slave_lag_seconds > ?

,clusterName, clusterName, acceptableLag

and it is somewhat painful to list clusterName twice in the arguments list. This is where the inverted IN clause kicks in. It will negate usage of an index, and may look strange at first glance, but as you get used to it it just becomes another pattern:

SELECT hostname FROM database_instance WHERE ? IN (cluster_name, '') AND slave_lag_seconds > ?

, clusterName, acceptableLag

So when clusterName is empty, all rows where slave_lag_seconds > acceptableLag are fetched; when non empty, only those where cluster_name equals our value.

What of Zookeeper?

Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for Zookeeper. It has HA via multiple nodes & quorum,  ephemeral nodes, all you need. To achieve similar benefits with MySQL you’d need to use Galera or NDB Cluster; so why not use Zk? The use case at hand is

orchestrator, a multi-node, mostly stateless service that happens to use MySQL as backend datastore. Ir relies on MySQL to exist in backend. It already expects it to be there. If the MySQL server is down, so is the service, effectively. In such case it doesn’t hurt adding another dependency on MySQL; this does not reduce HA. You need to take care of MySQL HA anyhow so there’s no additional cost. In fact, going to Zookeeper makes the additional cost as you introduce a new component to the system that can be avoided.

Terms of the solution

Our proposed solution offers:

• Single leader election out of multiple nodes
• Leader actively reaffirms its leadership periodically
• Timeout based re-election: decision to re-elect new leader based on the fact current leader has not reaffirmed its leadership over X seconds
• A way to forcibly assume leadership for a specific node
• A way to forcibly call for re-elections by demoting existing leader
• A node/service can easily tell whether it’s the leader or not
• Anyone can tell who the leader is

SQL solution

The solution is composed of a single table and a set of queries which implement the above offers. We assume a service can uniquely identify itself; this is easy to achieve:

• If services are running from different hosts (as should be the case, this is service HA), use hostname for ID
  • But what if the service restarts? Are you good with calling this “the same service” or is this now a new service running on the same host?
• In such case use combination of hostname & OS process ID
  • Or generate a random token upon startup
  • Or use startup timestamp Whichever solution you pick, make sure it is human readable, such that it is easy to tell

which service is the leader. This helps operations. We note this as service_id

Table

The following table will have a single row; the

service_id in that row is the active leader.

CREATE TABLE service_election ( 
  anchor tinyint(3) unsigned NOT NULL, 
  service_id varchar(128) NOT NULL, 
  last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (anchor) 
) ENGINE=InnoDB 

Attempt election

All services issue the following, periodically. Say every service issues this once per second:

insert ignore into service_election ( anchor, service_id, last_seen_active ) values ( 1, 'my_service_number_7', now() ) on duplicate key update service_id = if(last_seen_active < now() - interval 20 second, values(service_id), service_id), last_seen_active = if(service_id = values(service_id), values(last_seen_active), last_seen_active) ;

• Replace ‘my_service_number_7’ with specific service_id per service.* The above is the most complex query in our solution; breakdown:
• Assume timeout is set for 20 seconds
• The first ever service (my_service_number_7) succeeds in registering (inserting its own service_id into the table)
• The next time this service issues the query, it finds that it is already the owner, hence updates last_seen_active.
  • And while it is alive and keeps on polling, it remains the leader.
• Immediately following, another service (my_service_number_12) does not update anything: since last_seen_active < now() – interval 20 second is false, service_id is unchanged, nor is last_seen_active.
• Assuming said service (my_service_number_7) has died and hasn’t injected anything in the last 20 seconds, a different service will inject its own service_id as well as update last_seen_active. It will become the leader.

Forcibly assume leadership

Make a certain service the leader:

replace into service_election ( anchor, service_id, last_seen_active ) values ( 1, 'my_service_number_12', now() ) 

The next time my_service_number_12 attempts to register its own leadership, it finds out it’s already the leader and updates last_seen_active.

Force re-elections

The next service to attempt election will succeed after this:

delete from service_election;

Am I the leader?

A service can query as follows:

select count(*) as is_leader from service_election where anchor=1 and service_id='my_service_number_7';

Who is the leader?

This is of course very simple:

select max(service_id) as leader from service_election where anchor=1;

What of GET_LOCK()?

GET_LOCK() allows one to acquire a helper-lock of arbitrary name; this lock is reserved as long as the connection that acquired it lives. No other connection can acquire lock of same name.

SELECT GET_LOCK("my_service_leadership", 0)

Returns 1 on success, 0 on failure to obtain lock. This supposedly makes a much simpler leader election mechanism. However I don’t like it for the following reasons: * You must allocate a dedicated connection that lives for the duration of your service * This doesn’t play too well with ORMs or typical connection pools * There is no visibility (< MySQL 5.7) into who is holding the lock. You only know that it’s being held. * If your service hangs (but does not die), the lock is still being held. There is no “keepalive” requirement on the lock’s side.

q allows you to query your text files or standard input with SQL. You can:

SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1

And you can:

SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING (c1, c2) WHERE inno.c3 IS NULL

And you can also combine with your favourite shell commands and tools:

grep "my_term" /tmp/my_file.txt | q "SELECT c4 FROM - JOIN /home/shlomi/static.txt USING (c1)" | xargs touch

Some of q‘s functionality (and indeed, SQL functionality) can be found in command line tools. You can use grep for pseudo WHERE filtering, or cut for projecting, but you can only get so far with cat my_file.csv | sort | uniq -c | sort -n. SQL is way more powerful for working with tabulated data, and so q makes for a great addition into one’s toolbox.

The tool is authored by my colleague Harel Ben-Attia, and is in daily use over at our company (it is in fact installed on all production servers).

It is of course free and open source (get it on GitHub, where you can also find documentation), and very easy to setup. Enjoy!

“Nearly identical” meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one tables (while others can be NULL in the second).

Otherwise no contradicting data: it was not possible for some data to be “3” in one table and “4” in the other.

How do you create a merge of the tables, such that all missing rows are completed, and NULLs replaced by actual values when possible?

pt-table-sync comes to mind: one can do a bidirectional syncing of two tables, and actually stating how to resolve ambiguities (like “greater value wins”). Very powerful! An example would be:

pt-table-sync --bidirectional --conflict-column=a --conflict-comparison=greatest --tables ...

However I didn’t actually have any problem with the tables themselves. The two tables were just fine as they were; missing or NULL data does not indicate an error on their part. I wanted to get their merge. pt-table-sync is still up for the job: we can duplicate them, merge on the copy… But I prefer a query over an external script when possible.

INSERT…ON DUPLICATE KEY UPDATE

This MySQL-specific syntax is actually quite powerful. It basically says “if the insert fails due to unique constraint, you get a chance to update the row causing the failure”. But it also allows for smart setting of the column via the VALUES() clause. Let’s present some sample data and then see the solution.

Assume the following table definition:

create table t1 (
  pkdt datetime,
  pki int,
  a int,
  b int,
  c int,
  primary key (pkdt, pki)
);

Same structure holds for t1, t2 and tmerge — our target table. Looking at table data we have:

> select * from t1;
+---------------------+-----+------+------+------+
| pkdt                | pki | a    | b    | c    |
+---------------------+-----+------+------+------+
| 2012-01-01 00:00:00 |   1 |    4 |    5 |    6 |
| 2012-01-02 00:00:00 |   2 | NULL |    5 |    6 |
| 2012-01-04 00:00:00 |   4 |    4 | NULL | NULL |
| 2012-01-05 00:00:00 |   5 | NULL |    8 | NULL |
+---------------------+-----+------+------+------+

> select * from t2;
+---------------------+-----+------+------+------+
| pkdt                | pki | a    | b    | c    |
+---------------------+-----+------+------+------+
| 2012-01-01 00:00:00 |   1 |    4 | NULL |    6 |
| 2012-01-03 00:00:00 |   3 |    4 |    5 |    6 |
| 2012-01-04 00:00:00 |   4 |    4 |    5 |    6 |
| 2012-01-05 00:00:00 |   5 |    7 | NULL |    9 |
+---------------------+-----+------+------+------+

We can see a row with pki=2 appears in t1 but not in t2; a row with pki=3 appears in t2 but not in t2, and various NULLs appear throughout the rows that are shared.

To get the shared table, we throw in the data from t1 and t2 into tmerge, in such way that a real value overwrites a NULL, like this:

insert into tmerge select * from t1;

insert into tmerge select * from t2
on duplicate key update
  a = ifnull(tmerge.a, values(a)),
  b = ifnull(tmerge.b, values(b)),
  c = ifnull(tmerge.c, values(c))
;

So even while I’m inserting values to tmerge, I’m able to check for current value, compared to the value I wish to insert, and have time to make a decision. This is really cool! The result:

> select * from tmerge;
+---------------------+-----+------+------+------+
| pkdt                | pki | a    | b    | c    |
+---------------------+-----+------+------+------+
| 2012-01-01 00:00:00 |   1 |    4 |    5 |    6 |
| 2012-01-02 00:00:00 |   2 | NULL |    5 |    6 |
| 2012-01-03 00:00:00 |   3 |    4 |    5 |    6 |
| 2012-01-04 00:00:00 |   4 |    4 |    5 |    6 |
| 2012-01-05 00:00:00 |   5 |    7 |    8 |    9 |
+---------------------+-----+------+------+------+

Just what we wanted: all possible rows inside; real value takes over NULL whenever possible.

CREATE TABLE employee (
  employee_id INT UNSIGNED PRIMARY KEY,
  employee_name VARCHAR(100),
  manager_id INT UNSIGNED,
  CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id)
) engine=innodb
;
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
|           1 | Rachel        |       NULL |
|           2 | John          |          1 |
|           3 | Stan          |          1 |
|           4 | Naomi         |          2 |
+-------------+---------------+------------+

Questions like “What is the (nested) list of employees managed by Rachel?” or “Get Naomi’s managers chain of command” are classical questions. There are sometimes dependencies: if John leaves, does that mean Naomi loses her job, or does she get promoted, or something else? If John and Stan are on sick leave, does Rachel have any reason to come to work?

Hierarchical data is not limited to a single-table structure. Sometimes it takes a combination of a few tables (it’s just a JOIN) to make out the parent-child relation.

Hierarchical data is difficult to manage with SQL. This is especially true for MySQL, which does not support the WITH recursive query syntax.

Where can you find hierarchical data?

Even if you do not provide it by yourself, MySQL’s INFORMATION_SCHEMA has some for you. Partly obvious, partly implicit, here are some examples:

Foreign Keys

This is probably the most obvious hierarchical dataset in INFORMATION_SCHEMA. The KEY_COLUMN_USAGE table lists the table dependencies based on foreign key constraints. It’s a bit confusing, as it also lists UNIQUE constraints, and the complete list of FKs are in REFERENTIAL_CONSTRAINTS — but the latter table does not list the dependencies. You typically want to join both tables to get the complete information.

So, taking sakila‘s DVD rental shop sample,  film_actor table depends on film as well as on actor via foreign keys. film can depend on the category table, etc. The hierarchies can turn to be very complex, with multiple roots an very deep branches.

Dependency questions are very clear when speaking of foreign keys: what happens when we delete some film record? Does it hold that we also delete all references to that film on film_actor? Or do we deny deletion of said film in such case?

Redundant Keys

The KEY (col1, col2) makes the KEY (col1) redundant. The latter is not strictly required (though you may wish to keep it for covering index performance reason). The list of dominant-redundant keys makes for hierarchical data. It is typically very shallow (keys can only be redundant within the scope of a single table — how deep can you get with such small dataset?)

There is no immediate reference in INFORMATION_SCHEMA as for redundant keys, but it can be inferred by self joining the STATISTICS table onto itself. It is not immediate, since you need to do some aggregation and check for particular cases. For example, UNIQUE KEY (col1, col2) is actually made redundant by UNIQUE KEY (col1), which is just the opposite from our previous example.

common_schema provides with this implicit information now turned explicit, in the for of the redundant_keys view: the redundant_index_name and dominant_index_name columns make for parent-child relationship.

Dependency questions are a bit redundant here: the general objective is to not have dependencies. So get rid of redundant keys – and do so wisely. There’s a good discussion of index redundancies on redundant_keys‘s documentation.

Locked transactions

A transaction is locked. It is locked because another transaction holds some locks needed by locked transaction. But this transaction in itself can obtain locks needed by yet other transactions. And so we can get a hierarchy of locked transaction. The “parent” is the one blocking the “child”.

Combining InnoDB’s INNODB_TRX – INNODB_LOCK_WAITS – INNODB_TRX tables, we get this information. common_schema provides with this inferred data in the form of innodb_locked_transactions.

Hierarchies in locked-transactions could be deep, and they can most commonly be wide. A single transaction can lock dozens of others.

Dependency questions are “would killing this transaction release all other waiting transactions?”; “What is the one transaction I need to kill in order to release the bottleneck?”; “Why are these transactions related in the first place? Can I remove this dependency?”. etc.

Locks

You can look at the same dataset as above from a different angle. Instead of looking at transaction-lock-transaction, you can look at lock-transaction-lock. Which locks are causing other locks to be held? This is counter-intuitive to our understanding of how things work, but is valid nonetheless.

Views

A VIEW can query a table or another view. It can join multiple views. This hierarchy of view-reading-from-view can turn out to be complex; if not for the human mind then for the optimizer.

Surprisingly, there is no data in INFORMATION_SCHEMA, other than the CREATE VIEW clause, to help us out in resolving these dependencies. Even more surprising is MySQL’s own inability to get clear conclusions itself. The view definition clause is parsed, re-parsed and re-evaluated whenever information on “parent” views is required. For example, try to DESCRIBE a view. How does MySQL deduce the data types of columns? It dives in head first into the hierarchy, crawls and parses view definitions, till it resolves the answer. The mycheckpoint projects uses view hierarchies intensively. It draws powers from the hierarchy and produces some surprising data (charts from raw data, for example). But it also suffers from MySQL indirect inference of views. Checking up a deep-nested mycehckpoint view in INFORMATION_SCHEMA makes for a heavyweight dive for MySQL into locks and table handles.

Dependency questions are “what is the type of this column?”, “are there any TEMPTABLE views along the chain of execution? Or are all MERGE views?” and more.

Can you guess the results of the following statements?

Pop quiz 1

SET @x := 7;
SELECT ++@x;

What is the computation result? What will be the value of @x?

Pop quiz 2

SET @ := 4;
SELECT @ + @'' + @``

What is the computation result?

Pop quiz 3

SET @a := 2;
SELECT @a = @'a' = @`a`

Do we get TRUE or FALSE? When?

Hints

Consider the following queries as hints to the above questions:

SELECT +++++-@x;
SELECT @ = @'', @ = @``
SELECT (@a = @'a') = @`a`

Answers

• Pop quiz 1

++@x is interpreted as +(+(@x)), which is in turn evaluated as 0+(0+(@x)). No relation to C‘s ++ operator. @x is unchanged.

• Pop quiz 2

@x is the same as @’x’ and as @`x`. What’s funny is that one is allowed to create the empty-named user defined variable @”. Makes for a weird looking syntax, but nothing special about it. Computation result is 12.

• Pop quiz 3

We’ve already established that @a, @’a’ and @`a` are the same, I just used this notation for adding pepper to the quiz. The real question is whether 2 = 2 = 2 holds true.  It does not. There is no 3-way comparison. All comparisons are in pairs, which is why the expression evaluates as (2 = 2) = 2, leading to 1 = 2, “1” being the TRUE value of (2 = 2). The only value of @a for which the expression holds true is 1.

Another new year by Jewish calendar. What do I wish for the following year?

1. World peace
2. Good health to all
3. Get a decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL.

I mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]. But it makes for a poor substitution to Window Functions, and only solves a subset of issues.

My wishes in previous two years [2010], [2011] have not come true. I’m still willing to settle for two out of three.

Here’s another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table and no casting. The query outputs the largest 5 countries (by surface area) per continent.

SELECT
  Continent,
  Name,
  SurfaceArea,
  Population
FROM
  world.Country,
  (
    SELECT 
      GROUP_CONCAT(top_codes_per_group) AS top_codes
    FROM
      (
        SELECT 
          SUBSTRING_INDEX(GROUP_CONCAT(Code ORDER BY SurfaceArea DESC), ',', 5) AS top_codes_per_group
        FROM
          world.Country
        GROUP BY
          Continent
      ) s_top_codes_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(Code, top_codes)
ORDER BY
  Continent,
  SurfaceArea DESC
;

+---------------+----------------------------------------------+-------------+------------+
| Continent     | Name                                         | SurfaceArea | Population |
+---------------+----------------------------------------------+-------------+------------+
| Asia          | China                                        |  9572900.00 | 1277558000 |
| Asia          | India                                        |  3287263.00 | 1013662000 |
| Asia          | Kazakstan                                    |  2724900.00 |   16223000 |
| Asia          | Saudi Arabia                                 |  2149690.00 |   21607000 |
| Asia          | Indonesia                                    |  1904569.00 |  212107000 |
| Europe        | Russian Federation                           | 17075400.00 |  146934000 |
| Europe        | Ukraine                                      |   603700.00 |   50456000 |
| Europe        | France                                       |   551500.00 |   59225700 |
| Europe        | Spain                                        |   505992.00 |   39441700 |
| Europe        | Sweden                                       |   449964.00 |    8861400 |
| North America | Canada                                       |  9970610.00 |   31147000 |
| North America | United States                                |  9363520.00 |  278357000 |
| North America | Greenland                                    |  2166090.00 |      56000 |
| North America | Mexico                                       |  1958201.00 |   98881000 |
| North America | Nicaragua                                    |   130000.00 |    5074000 |
| Africa        | Sudan                                        |  2505813.00 |   29490000 |
| Africa        | Algeria                                      |  2381741.00 |   31471000 |
| Africa        | Congo, The Democratic Republic of the        |  2344858.00 |   51654000 |
| Africa        | Libyan Arab Jamahiriya                       |  1759540.00 |    5605000 |
| Africa        | Chad                                         |  1284000.00 |    7651000 |
| Oceania       | Australia                                    |  7741220.00 |   18886000 |
| Oceania       | Papua New Guinea                             |   462840.00 |    4807000 |
| Oceania       | New Zealand                                  |   270534.00 |    3862000 |
| Oceania       | Solomon Islands                              |    28896.00 |     444000 |
| Oceania       | New Caledonia                                |    18575.00 |     214000 |
| Antarctica    | Antarctica                                   | 13120000.00 |          0 |
| Antarctica    | French Southern territories                  |     7780.00 |          0 |
| Antarctica    | South Georgia and the South Sandwich Islands |     3903.00 |          0 |
| Antarctica    | Heard Island and McDonald Islands            |      359.00 |          0 |
| Antarctica    | Bouvet Island                                |       59.00 |          0 |
| South America | Brazil                                       |  8547403.00 |  170115000 |
| South America | Argentina                                    |  2780400.00 |   37032000 |
| South America | Peru                                         |  1285216.00 |   25662000 |
| South America | Colombia                                     |  1138914.00 |   42321000 |
| South America | Bolivia                                      |  1098581.00 |    8329000 |
+---------------+----------------------------------------------+-------------+------------+

In bold are the conditions by which we nominate our selected rows (condition is SurfaceArea DESC, number of rows is 5, so 5 largest countries).

What’s going on here?

So the inner s_top_codes_per_group query produces the codes for largest countries per continent:

+---------------------+
| top_codes_per_group |
+---------------------+
| CHN,IND,KAZ,SAU,IDN |
| RUS,UKR,FRA,ESP,SWE |
| CAN,USA,GRL,MEX,NIC |
| SDN,DZA,COD,LBY,TCD |
| AUS,PNG,NZL,SLB,NCL |
| ATA,ATF,SGS,HMD,BVT |
| BRA,ARG,PER,COL,BOL |
+---------------------+

The wrapping s_top_codes query concatenates all the above to one long text:

+---------------------------------------------------------------------------------------------------------------------------------------------+
| top_codes                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| CHN,IND,KAZ,SAU,IDN,RUS,UKR,FRA,ESP,SWE,CAN,USA,GRL,MEX,NIC,SDN,DZA,COD,LBY,TCD,AUS,PNG,NZL,SLB,NCL,ATA,ATF,SGS,HMD,BVT,BRA,ARG,PER,COL,BOL |
+---------------------------------------------------------------------------------------------------------------------------------------------+

And the final query simply demands that Code must be found within this string, by calling upon FIND_IN_SET(Code, top_codes).

Notes

• This solution works for PRIMARY KEYs or otherwise UNIQUE KEYs of all sorts (a CHAR(3) in our example, but same for integers etc.)
• And you still have to have a sufficient group_concat_max_len (see this post). You must have a large enough value to fit in the very long text you may be expecting in s_top_codes.
• Performance-wise there are full scans here, as well as string searching.

* UPDATE

I should pay closer attention. This comment had it 5 years ago.