Rails: follow routing rules in information_schema.tables

[mdkent]

Related issue in #6895.

Feature Description

Allow information_schema.tables to follow routing rules like SELECTs. This is more predictable than the current behaviour:

mysql> use mainunsharded;
Database changed
mysql> select * from entries limit 1;
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
| id | account_id | topic_id | entryable_type | entryable_id | creator_id | status | created_at          | updated_at          | active_at           | replied_entry_id | alternative_sender_name |
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
|  1 |          1 |        1 | Message        |            1 |          1 |      1 | 2019-04-08 12:49:06 | 2019-04-08 12:49:06 | 2019-04-08 12:49:06 |             NULL | NULL                    |
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
1 row in set (0.11 sec)

mysql> SELECT count(*) FROM information_schema.tables WHERE table_schema = database() and table_name='entries';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.10 sec)

mysql> use main;
Database changed
mysql> select * from entries limit 1;
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
| id | account_id | topic_id | entryable_type | entryable_id | creator_id | status | created_at          | updated_at          | active_at           | replied_entry_id | alternative_sender_name |
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
|  1 |          1 |        1 | Message        |            1 |          1 |      1 | 2019-04-08 12:49:06 | 2019-04-08 12:49:06 | 2019-04-08 12:49:06 |             NULL | NULL                    |
+----+------------+----------+----------------+--------------+------------+--------+---------------------+---------------------+---------------------+------------------+-------------------------+
1 row in set (0.10 sec)

mysql> SELECT count(*) FROM information_schema.tables WHERE table_schema = database() and table_name='entries';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.10 sec)

and will fix an issue with Rails where it expects to be able to run schema queries to determine data sources.

Use Case(s)

We have both unsharded and sharded tables in different keyspaces, which we can link together with routing rules in the same database, but rails expects to be able to run schema level queries on all of them identically.

[harshit-gangal]

Based on the current query even if we add routing rules to information_schema if the query's where condition has table_schema provided it will supersede the routing rule and query will be routed to table_schema.

In your case the selected database is use mainunsharded and the where condition is table_schema = database() so it will be routed to mainunsharded even if routing rules exists for the table provided in table_name.

[mdkent]

We have a routing rule for

  "mainunsharded.entries": [
   "main.entries"
  ],

and rails is configured to use the mainunsharded database.

Could database() be resolved for the current connection prior to routing?

[mdkent]

Based on #6638 it looks like the current direction is the opposite of what we need - though I'm not clear on the dangers of replacing database() with the currently selected target. It seems like this would be a more common use-case then selecting the underlying database.

[harshit-gangal]

This is similar to what we have done in show full columns from <table_name> from <db_name> if dbname is provided or table_name contains a qualifier like <qualifier>.<table_name> then routing will happen on those keyspace otherwise routing rules will be applied.

We can add support for routing rules when table_schema is not provided in the query.

[systay]

I think we could make the planning clever enough to recognise the situation where the query has a predicate for table_schema and table_name, and in this case we can let the routing rules determine where we send the query.

This will only work for equality comparisons - LIKE and range queries would not be able to be routed using the routing rules. But I don't think that is needed for Rails compatibility, right?

[mdkent]

I think we could make the planning clever enough to recognise the situation where the query has a predicate for table_schema and table_name, and in this case we can let the routing rules determine where we send the query.

This will only work for equality comparisons - LIKE and range queries would not be able to be routed using the routing rules. But I don't think that is needed for Rails compatibility, right?

Correct, we're only looking to satisfy this function and this query, passing both a table_schema (which will always be database()) and table_name.

Admittedly it does feel a bit gross to encourage the further disconnect between the contents of information_schema.tables and the ways to query it:

mysql> SELECT table_schema,database() FROM information_schema.tables WHERE table_schema = database() and table_name='entries'\G
*************************** 1. row ***************************
table_schema: foo_production
  database(): foo_production
1 row in set (0.11 sec)

mysql> select database();
+------------+
| database() |
+------------+
| main       |
+------------+
1 row in set (0.10 sec)

but this is the path of least resistance for compatibility with an ORM that expects a unified keyspace.

I do wonder if in the longer term it would make sense to either make ActiveRecord aware of routing rules via a specific config (like a copy of GetRoutingRules) or even provide an alternate information_schema table (say vt_tables) with a subset of the current information that would be sufficient enough for AR to make decisions. Something with a fully resolved set of routing rules - representing the unified keyspace.