最近为了调研 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
下面主要讲一下目前存在的问题、影响以及解决办法。
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
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
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
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 mysqlTiDB 相关 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
下面这些 issue 是使用场景非常小,实际使用中极小概率会遇到的:
这就是跑完 ActiveRecord 大概 25000 个测试用例发现的所有问题。可以说 TiDB 和 ActiveRecord 集成完全没有问题了。接下来可能会提供 TiDB ActiveRecord Adapter,提供 TiDB 特有功能的支持。