TiDB & ActiveRecord ORM Integration Guide
最近为了调研 TiDB 与 ActiveRecord 的兼容程度，搭建了一个 CI 环境，用来跑 TiDB 和 ActiveRecord 的单元测试。把（TiDB 5.1，TiDB nightly）x （AR 6-1-stable，AR main）都已经跑通。
Finished in 841.391538s, 9.0695 runs/s, 29.2872 assertions/s. 7631 runs, 24642 assertions, 0 failures, 0 errors, 135 skips
下面主要讲一下目前存在的问题、影响以及解决办法。
Default Character Set and Collation
TiDB 默认 collation 行为与MySQL不一致，TiDB 默认使用的是utf8mb4_bin，会影响到字符串比较和匹配，MySQL 默认是大小写不敏感（Case-insensitive），TiDB默认大小写敏感（Case-sensitive），如果想完全兼容 MySQL，需要额外的配置。
TiDB 默认情况，Case-sensitive：
mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+-------------+---------+------+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------+---------+------+---------+----------+---------+
| utf8mb4_bin | utf8mb4 | 46 | Yes | Yes | 1 |
+-------------+---------+------+---------+----------+---------+
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; --由于new_collations_enabled_on_first_bootstrap 没有设置为true，collate设置并没有生效。
SELECT 'A' = 'a', 'A' like '%a%';
'A' = 'a' | 'A' like '%a%'
-----------+----------------
0 | 0
如果想和 MySQL 默认行为完全兼容，解决方法是启动集群时开启 new_collations_enabled_on_first_bootstrap = true，并且collation设置为 utf8mb4_general_ci:
mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+--------------------+---------+------+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+------+---------+----------+---------+
| utf8mb4_bin | utf8mb4 | 46 | Yes | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 |
+--------------------+---------+------+---------+----------+---------+
tiup playground 简单配置演示：
tiup playground --db.config config.toml
cat config.toml
new_collations_enabled_on_first_bootstrap = true
开启new_collations_enabled_on_first_bootstrap之后的结果与 MySQL 默认行为一致：
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
mysql> select 'a' = 'A' , 'a' like '%A%';
+-----------+----------------+
| 'a' = 'A' | 'a' like '%A%' |
+-----------+----------------+
| 1 | 1 |
+-----------+----------------+
database.yml 修改：
diff --git a/config/database.yml b/config/database.yml
index fa6ab2d..e7226a8 100644
--- a/config/database.yml
+++ b/config/database.yml
@@ -12,10 +12,15 @@
default: &default
adapter: mysql2
encoding: utf8mb4
+ collation: utf8mb4_general_ci
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
+ host: 127.0.0.1
+ port: 4000
password:
- socket: /tmp/mysql.sock
+ variables:
+ tidb_enable_noop_functions: ON
Unsupported multi schema change
TiDB 相关issue：https://github.com/pingcap/tidb/issues/14766
ActiveRecord 支持将多个 DDL 语句合并成一条：
class BulkTest < ActiveRecord::Migration[6.1]
def change
change_table :posts, bulk: true do |t|
t.integer :new_column1
t.string :new_column2
t.boolean :new_column3
end
end
end
产生 SQL 语句：
Migrating to BulkTest (20210728090251)
(110.8ms) ALTER TABLE `posts` ADD `new_column1` int, ADD `new_column2` varchar(255), ADD `new_column3` tinyint(1)
目前 TiDB 还不支持，相关issue正在开发中。
== 20210728090251 BulkTest: migrating =========================================
-- change_table(:posts, {:bulk=>true})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Unsupported multi schema change
/Users/hooopo/w/ping/myapp/db/migrate/20210728090251_bulk_test.rb:3:in `change'
/Users/hooopo/w/ping/myapp/bin/rails:5:in `<top (required)>'
/Users/hooopo/w/ping/myapp/bin/spring:10:in `block in <top (required)>'
/Users/hooopo/w/ping/myapp/bin/spring:7:in `tap'
/Users/hooopo/w/ping/myapp/bin/spring:7:in `<top (required)>'
临时解决方法是 patch mysql adapter 的 supports_bulk_alter? 方法，patch 之后，Rails 会自动忽略 bulk 选项。
require 'active_record/connection_adapters/mysql2_adapter'
ActiveRecord::ConnectionAdapters::Mysql2Adapter.class_eval do
def supports_bulk_alter?
false
end
end
Unsupported get_lock and release_lock functions
TiDB 相关 issue：https://github.com/pingcap/tidb/issues/14994
ActiveRecord 里使用get_lock和release_lock来防止并发 migration 问题，大多数场景是不需要的，可以有两种办法解决：
第一种是在database.yml里设置变量tidb_enable_noop_functions: ON：
default: &default
adapter: mysql2
encoding: utf8mb4
collation: utf8mb4_general_ci
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
host: 127.0.0.1
port: 4000
password:
variables:
tidb_enable_noop_functions: ON
另外一种方法是 patch mysql adapter的 supports_advisory_locks? 方法：
require 'active_record/connection_adapters/mysql2_adapter'
ActiveRecord::ConnectionAdapters::Mysql2Adapter.class_eval do
def supports_advisory_locks?
false
end
end
Unsupported savepoint
TiDB 相关issue：https://github.com/pingcap/tidb/issues/6840
ActiveRecord 里savepoint的使用场景有两种，一种是清理构建测试集所产生的临时数据；另一种是实现嵌套事物。
测试场景解决方案很简单，只需要设置 use_transactional_tests 为 false
module ActiveRecord
class TestCase < ActiveSupport::TestCase #:nodoc:
self.use_transactional_tests = false
end
end
嵌套事物场景，可以使用下面的的补丁来临时解决，TiDB 的 savepoint 功能已经在开发中，估计不久就会支持：
require 'active_record/connection_adapters/abstract/database_statements.rb'
ActiveRecord::ConnectionAdapters::DatabaseStatements.class_eval do
def transaction(requires_new: nil, isolation: nil, joinable: true)
if requires_new
Rails.logger.warn "savepoint statement was used, but your db not support, ignored savepoint."
Rails.logger.warn caller
requires_new = nil
end
if !requires_new && current_transaction.joinable?
if isolation
raise ActiveRecord::TransactionIsolationError, "cannot set isolation when joining a transaction"
end
yield
else
transaction_manager.within_new_transaction(isolation: isolation, joinable: joinable) { yield }
end
rescue ActiveRecord::Rollback
# rollbacks are silently swallowed
end
end
show keys from is not compatible with mysql
TiDB 相关 issue： https://github.com/pingcap/tidb/issues/26110
由于 show keys from 返回结果与 MySQL 不兼容，导致 schema.rb 导出索引数据不正确，对应用程序本身无影响。TiDB 最新代码已经修复了这个问题，只有旧版本会遇到。
可以通过设置 schema_format = :sql 来临时解决：
module YourApp
class Application < Rails::Application
config.load_defaults 6.0
# Add this line:
config.active_record.schema_format = :sql
end
end
others
下面这些 issue 是使用场景非常小，实际使用中极小概率会遇到的：
- create table select from：https://github.com/pingcap/tidb/issues/4754
- modify auto_increment：https://github.com/pingcap/tidb/issues/10190
- SPATIAL functions：https://github.com/pingcap/tidb/issues/6347
- Drop temporary table：https://github.com/pingcap/tidb/issues/26278
结论
这就是跑完 ActiveRecord 大概25000个测试用例发现的所有问题。可以说 TiDB 和 ActiveRecord 集成完全没有问题了。接下来可能会提供 TiDB ActiveRecord Adapter，提供 TiDB 特有功能的支持。
- CI 地址：https://buildkite.com/hooopo
- ActiveRecord 测试集地址：https://github.com/tidb-incubator/rails