数据库 TiDB & ActiveRecord 避坑指南

hooopo · 2021年07月30日 · 最后由 saillee 回复于 2024年02月27日 · 3080 次阅读
本帖已被管理员设置为精华贴

最近为了调研 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_lockrelease_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_testsfalse

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 是使用场景非常小,实际使用中极小概率会遇到的:

结论

这就是跑完 ActiveRecord 大概 25000 个测试用例发现的所有问题。可以说 TiDB 和 ActiveRecord 集成完全没有问题了。接下来可能会提供 TiDB ActiveRecord Adapter,提供 TiDB 特有功能的支持。

huacnlee 将本帖设为了精华贴。 07月30日 19:49

炮哥去 PingCAP 了?

所以不是完美兼容 mysql 哦

Awlter1 回复

完美兼容 mysql 的应该不存在,连 MariaDB 都并不完美兼容 mysql...

TiDB 也是有各种坑需要填的

唉,信创需求要迁移达梦的崩溃中...😭 💀 💀 💀

需要 登录 后方可回复, 如果你还没有账号请 注册新账号