Add support for TiDB

[bx80]

After the linked general database abstraction issues have been completed it should be possible to create a new PDO adapter and schema definition to trial running Matomo entirely on TiDB. an open source distributed MySQL compatible database.

This change will allow for long term suitability testing of TiDB as an alternative high-performance database backend for Matomo.

Suggested Approach

• Create a new PDO adapter core/Db/Adapter/Pdo/Tidb based on core/Db/Adapter/Pdo/Mysql.php.

  • Fully implement all requirements of core/Db/AdapterInterface.
  • Implement getLock() using the approach in the lockalt branch.
  • supportsWindowFunctions() to return true.
  • preferSubqueries() to return true.
  • Create a new OmniFixture dump compatible with the TiDB schema and return it's filename with `getOmniFixtureFilename()'.
  • Check and set the global sql_mode to disable strict grouping operators.

• Create a new schema class at core/Db/Schema/Tidb

  • Use AUTO_RANDOM Primary keys.
  • Set TiFlash replicas for all log_xxx tables, this may need config settings for clusters with multiple replicas.
  • Create TiDB specific indexes.
  • TEXT columns cannot have default values, remove the defaults.

Related general database abstraction issues:

#19643: Abstract the database get_lock function to PDO adapter
#19646: Support option for ranking queries to use window functions
#19647: Abstract database specific tests and fixture code
#19648: Abstract database specific helper methods to PDO adapter
#19649: Support database specific DDL in migrations
#19650: Use sequences instead of auto increment keys for user facing identifiers
#19651: Support optional sub-query substitution for COUNT(DISTINCT x) queries

[sgiehl]

@bx80 I haven't yet read all the other issues in detail, but would something like #18371 be a good base for starting TiDB support as well?

[bx80]

@sgiehl This would be covered by issue #19648. My suggested approach there is almost identical to your solution for #18371, the only difference is you've implemented the abstraction using a db schema class whereas I was thinking to have statement code in a db specific PDO adapter and just have schema changes in the schema class.

PDO adapter descendants get access to override some lower level functions such as query() which might be needed if we implement a multi-tenancy solution at some point and need to enforce query safe guards. Also some of the existing MySQL PDO adapter code is already too MySQL specific to work with TiDB, SingleStore, etc, which don't have InnoDB tables at all, different default ports and different version numbering.

We could allow database choice during installation and then set the existing config.ini.php adapter and schema settings to determine the schema and PDO adapter classes to use.

It looks like #18371 is already compatible with this approach. Maybe it could be finished at the same time #19648 is implemented? It'd be a good test of the abstraction structure.

[dankasak]

Hi there. Just a quick note on #19650 - as of TiDB v6.4.x, TiDB has a high-performance, monotonically-incrementing AUTO_INCREMENT implementation, which can be activated on a per-table basis with the AUTO_ID_CACHE 1 directive, eg CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1
See: https://docs.pingcap.com/tidb/dev/auto-increment#mysql-compatibility-mode

Therefore, #19650 may no longer be required for TiDB support. Note however that tables with AUTO_INCREMENT primary keys are still prone to write ( and potentially read ) hotspots. Please feel free to reach out if you want to discuss further.

[tsteur]

This is great 🎉 Thanks @dankasak