数据库 TiDB & ActiveRecord 避坑指南

hooopo · 2021年07月30日 · 最后由 saillee 回复于 2024年02月27日 · 3091 次阅读

本帖已被管理员设置为精华贴

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