Dvadmin中对接TiDB

972次阅读
一条评论

共计 15355 个字符，预计需要花费 39 分钟才能阅读完成。

Dvadmin中对接TiDB

本想用mysql，可惜最开始其他project用了tidb，只能折腾下了，看了下双方的文档也还好，可以兼容mysql驱动

快速创建一个TiDB

[root@localhost ~]# curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 7323k  100 7323k    0     0  3576k      0  0:00:02  0:00:02 --:--:-- 3577k
WARN: adding root certificate via internet: https://tiup-mirrors.pingcap.com/root.json
You can revoke this by remove /root/.tiup/bin/7b8e153f2e2d0928.root.json
Successfully set mirror to https://tiup-mirrors.pingcap.com
Detected shell: bash
Shell profile:  /root/.bash_profile
/root/.bash_profile has been modified to add tiup to PATH
open a new terminal or source /root/.bash_profile to use it
Installed path: /root/.tiup/bin/tiup
===============================================
Have a try:     tiup playground
===============================================

[root@localhost ~]# source /root/.bash_profile
[root@localhost ~]# tiup playground v5.3.0 --db 2 --pd 3 --kv 3
tiup is checking updates for component playground ...
A new version of playground is available:
   The latest version:         v1.12.3
   Local installed version:
   Update current component:   tiup update playground
   Update all components:      tiup update --all

The component `playground` version  is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/playground-v1.12.3-linux-amd64.tar.gz 8.07 MiB / 8.07 MiB 100.00% 4.45 MiB/s
Starting component `playground`: /root/.tiup/components/playground/v1.12.3/tiup-playground v5.3.0 --db 2 --pd 3 --kv 3
Start pd instance:v5.3.0
The component `pd` version v5.3.0 is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/pd-v5.3.0-linux-amd64.tar.gz 41.25 MiB / 41.25 MiB 100.00% 4.60 MiB/s
Start pd instance:v5.3.0
Start pd instance:v5.3.0
Start tikv instance:v5.3.0
The component `tikv` version v5.3.0 is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/tikv-v5.3.0-linux-amd64.tar.gz 167.92 MiB / 167.92 MiB 100.00% 4.72 MiB/s
Start tikv instance:v5.3.0
Start tikv instance:v5.3.0
Start tidb instance:v5.3.0
The component `tidb` version v5.3.0 is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/tidb-v5.3.0-linux-amd64.tar.gz 47.67 MiB / 47.67 MiB 100.00% 4.79 MiB/s
Start tidb instance:v5.3.0
Waiting for tidb instances ready
127.0.0.1:4000 ... Done
127.0.0.1:4001 ... Done
The component `prometheus` version v5.3.0 is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/prometheus-v5.3.0-linux-amd64.tar.gz 87.08 MiB / 87.08 MiB 100.00% 4.65 MiB/s
download https://tiup-mirrors.pingcap.com/grafana-v5.3.0-linux-amd64.tar.gz 50.00 MiB / 50.00 MiB 100.00% 4.49 MiB/s
Start tiflash instance:v5.3.0
The component `tiflash` version v5.3.0 is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/tiflash-v5.3.0-linux-amd64.tar.gz 412.72 MiB / 412.72 MiB 100.00% 4.70 MiB/s
Waiting for tiflash instances ready
127.0.0.1:3930 ... Done

🎉 TiDB Playground Cluster is started, enjoy!

Connect TiDB:   mysql --comments --host 127.0.0.1 --port 4000 -u root
Connect TiDB:   mysql --comments --host 127.0.0.1 --port 4001 -u root
TiDB Dashboard: http://127.0.0.1:2379/dashboard
Grafana:        http://127.0.0.1:3000

# 如果想要外部访问则添加--host参数
[root@localhost ~]# tiup playground v5.3.0 --db 2 --pd 3 --kv 3 --host 0.0.0.0
tiup is checking updates for component playground ...
Starting component `playground`: /root/.tiup/components/playground/v1.12.3/tiup-playground v5.3.0 --db 2 --pd 3 --kv 3 --host 0.0.0.0
Start pd instance:v5.3.0
Start pd instance:v5.3.0
Start pd instance:v5.3.0
Start tikv instance:v5.3.0
Start tikv instance:v5.3.0
Start tikv instance:v5.3.0
Start tidb instance:v5.3.0
Start tidb instance:v5.3.0
Waiting for tidb instances ready
192.168.44.162:4000 ... Done
192.168.44.162:4001 ... Done
Start tiflash instance:v5.3.0
Waiting for tiflash instances ready
192.168.44.162:3930 ... Done

🎉 TiDB Playground Cluster is started, enjoy!

Connect TiDB:   mysql --comments --host 192.168.44.162 --port 4000 -u root
Connect TiDB:   mysql --comments --host 192.168.44.162 --port 4001 -u root
TiDB Dashboard: http://192.168.44.162:2379/dashboard
Grafana:        http://0.0.0.0:3000

# 创建数据库
MySQL [(none)]> CREATE DATABASE `dvadmin` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 0 rows affected (0.12 sec)

# 创建用户
MySQL [(none)]> CREATE USER 'tiuser'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.05 sec)

# 创建授权
MySQL [(none)]> GRANT ALL ON dvadmin.* TO 'tiuser'@'%';
Query OK, 0 rows affected (0.05 sec)

Django安装依赖

pip3 install django-tidb

修改Dvadmin的env.py连接ENGINE

# 使用mysql时，改为此配置
# DATABASE_ENGINE = "django.db.backends.mysql"
DATABASE_ENGINE = "django_tidb"
DATABASE_NAME = 'dvadmin'

# 数据库地址 改为自己数据库地址
DATABASE_HOST = "192.168.44.162"
# # 数据库端口
DATABASE_PORT = 4000
# # 数据库用户名
DATABASE_USER = "tiuser"
# # 数据库密码
DATABASE_PASSWORD = "123456"

若是TiDB有用SSL连接，则需要提供CA证书路径

DATABASES = {
    "default": {
        "ENGINE": DATABASE_ENGINE,
        "NAME": DATABASE_NAME,
        "USER": DATABASE_USER,
        "PASSWORD": DATABASE_PASSWORD,
        "HOST": DATABASE_HOST,
        "PORT": DATABASE_PORT,
        'OPTIONS': {
            'ssl': {
                "ca": "/etc/ssl/cert.pem"
            },
        },
    },
}

各操作系统的根证书默认路径

# MAC
/etc/ssl/cert.pem

# Debian / Ubuntu / Arch
/etc/ssl/certs/ca-certificates.crt

# RedHat / Fedora / CentOS / Mageia
/etc/pki/tls/certs/ca-bundle.crt

# Alpine
/etc/ssl/cert.pem

# OpenSUSE
/etc/ssl/ca-bundle.pem

生成表迁移文件

(venv) F:\VM\python-project\taizhang_demo\django-vue-admin\backend>python manage.py makemigrations
请先进行数据库迁移!
请先进行数据库迁移!
F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\commands\makemigrations.py:105: RuntimeWa
rning: Got an error checking a consistent migration history performed for database connection 'default': (1115, "Unknown character set: 'utf8mb3'")
  warnings.warn(
Migrations for 'system':
  dvadmin\system\migrations\0001_initial.py
    - Create model Users
    - Create model Dept
    - Create model Menu
    - Create model MenuButton
    - Create model MessageCenter
    - Create model Role
    - Create model Post
    - Create model OperationLog
    - Create model MessageCenterTargetUser
    - Add field target_role to messagecenter
    - Add field target_user to messagecenter
    - Create model LoginLog
    - Create model FileList
    - Create model Dictionary
    - Create model Area
    - Create model ApiWhiteList
    - Add field dept to users
    - Add field groups to users
    - Add field post to users
    - Add field role to users
    - Add field user_permissions to users
    - Create model SystemConfig
Migrations for 'zhangdan_demo':
  zhangdan_demo\migrations\0001_initial.py
    - Create model ClusterPrefixInfoModel

看样子是有个报错：’default’: (1115, “Unknown character set: ‘utf8mb3′”)，博主用的utf8mb4，怎么会出现这个error?试下migrate

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\manage.py", line 22, in <module>
    main()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\__init__.py", line 419, in execut
e_from_command_line
    utility.execute()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\__init__.py", line 413, in execut
e
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\base.py", line 354, in run_from_a
rgv
    self.execute(*args, **cmd_options)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\commands\migrate.py", line 75, in
 handle
    self.check(databases=[database])
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\management\base.py", line 419, in check
    all_issues = checks.run_checks(
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\checks\registry.py", line 76, in run_checks
    new_errors = check(app_configs=app_configs, databases=databases)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\core\checks\database.py", line 13, in check_datab
ase_backends
    issues.extend(conn.validation.check(**kwargs))
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\mysql\validation.py", line 9, in chec
k
    issues.extend(self._check_sql_mode(**kwargs))
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\mysql\validation.py", line 13, in _ch
eck_sql_mode
    if not (self.connection.sql_mode & {'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES'}):
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django_tidb\base.py", line 92, in sql_mode
    sql_mode = self.tidb_server_data['sql_mode']
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django_tidb\base.py", line 57, in tidb_server_data
    with self.temporary_connection() as cursor:
  File "C:\Users\K-ONE\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 119, in __enter__
    return next(self.gen)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 603, in temporary
_connection
    with self.cursor() as cursor:
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 259, in cursor
    return self._cursor()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 235, in _cursor
    self.ensure_connection()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 219, in ensure_co
nnection
    self.connect()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 219, in ensure_co
nnection
    self.connect()
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\base\base.py", line 200, in connect
    self.connection = self.get_new_connection(conn_params)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\utils\asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\django\db\backends\mysql\base.py", line 234, in get_new_
connection
    connection = Database.connect(**conn_params)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\MySQLdb\__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\MySQLdb\connections.py", line 197, in __init__
    self.set_character_set(charset)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\MySQLdb\connections.py", line 298, in set_character_set
    super().set_character_set(charset)
django.db.utils.OperationalError: (1115, "Unknown character set: 'utf8mb3'")

前面建库时用得uft8mb4，估计是客户端字符集设置出错，去server端查看了下确实，但是不能修改

MySQL [(none)]> set global  character_set_connection = utf8mb4;
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]>
MySQL [(none)]> set global  character_set_client = utf8mb4;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]>
MySQL [(none)]> show variables like '%char%'
    -> ;
+--------------------------------------+--------------------------------------------------------+
| Variable_name                        | Value                                                  |
+--------------------------------------+--------------------------------------------------------+
| character_set_client                 | utf8                                                   |
| character_set_connection             | utf8                                                   |
| character_set_database               | utf8mb4                                                |
| character_set_filesystem             | binary                                                 |
| character_set_results                | utf8                                                   |
| character_set_server                 | utf8mb4                                                |
| character_set_system                 | utf8                                                   |
| character_sets_dir                   | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |
| validate_password_special_char_count | 1                                                      |
+--------------------------------------+--------------------------------------------------------+
9 rows in set (0.01 sec)

一时间也没找到TiDB配置得地方，先在Django中配个option吧

DATABASES = {
    "default": {
        "ENGINE": DATABASE_ENGINE,
        "NAME": DATABASE_NAME,
        "USER": DATABASE_USER,
        "PASSWORD": DATABASE_PASSWORD,
        "HOST": DATABASE_HOST,
        "PORT": DATABASE_PORT,
        "OPTIONS": {
            "charset": "utf8mb4",
        },
    }
}

再次执行migrate，结果提示不支持JSONFields

(venv) F:\VM\python-project\zhangdan_demo\django-vue-admin\backend>python manage.py migrate
请先进行数据库迁移!
请先进行数据库迁移!
SystemCheckError: System check identified some issues:

ERRORS:
system.SystemConfig: (fields.E180) TiDB does not support JSONFields.
system.SystemConfig: (fields.E180) TiDB does not support JSONFields.
system.SystemConfig: (fields.E180) TiDB does not support JSONFields.
system.SystemConfig: (fields.E180) TiDB does not support JSONFields.

看了下TiDB文档，是支持JSONFields的，查了下错误来自django/db/models/fields/json.py

    def _check_supported(self, databases):
        errors = []
        for db in databases:
            if not router.allow_migrate_model(db, self.model):
                continue
            connection = connections[db]
            if (
                self.model._meta.required_db_vendor and
                self.model._meta.required_db_vendor != connection.vendor
            ):
                continue
            if not (
                'supports_json_field' in self.model._meta.required_db_features or
                connection.features.supports_json_field
            ):
                errors.append(
                    checks.Error(
                        '%s does not support JSONFields.'
                        % connection.display_name,
                        obj=self.model,
                        id='fields.E180',
                    )
                )
        return errors

继续查看 supports_json_field，发现django-tidb中的features.py内

    @cached_property
    def supports_json_field(self):
        return False

尝试修改为True，再次执行又提示新的错误

  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "F:\VM\python-project\zhangdan_demo\django-vue-admin\backend\venv\lib\site-packages\MySQLdb\connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (8200, "unsupported add column 'token_id' constraint UNIQUE KEY when altering 'dvadmin.token_blacklist_blacklistedtoken'"
)

直接搜了下TiDB github上Issues，也存在类似的报错：DDL不支持多个操作，看上面的操作是增加字段后设置唯一键，需要分成两步写…..博主的解决方式很暴力，直接删除改模块的迁移文件后重新生成即可，另外一种方式是拿到建表语句自己修改

# 查看迁移情况
python manage.py showmigrations

# 查看迁移sql语句
(venv) F:\VM\python-project\zhangdan_demo\django-vue-admin\backend>python manage.py sqlmigrate token_blacklist 0001
--
-- Create model OutstandingToken
--
CREATE TABLE `token_blacklist_outstandingtoken` (`id` bigint AUTO_INCREMENT NOT NULL PRIMARY KEY, `jti` varchar(255) NOT NULL UNIQUE, `token` longt
ext NOT NULL, `created_at` datetime(6) NULL, `expires_at` datetime(6) NOT NULL, `user_id` bigint NULL);
--
-- Create model BlacklistedToken
--
CREATE TABLE `token_blacklist_blacklistedtoken` (`id` bigint AUTO_INCREMENT NOT NULL PRIMARY KEY, `blacklisted_at` datetime(6) NOT NULL, `token_id`
 bigint NOT NULL UNIQUE);

目前使用中暂未发现其他问题，哎希望不要出其他兼容性问题，不然就裂开，期间可以不使用django-tidb这个包，可以用dvadmin中配置mysql驱动去运行，后面迁移时也会遇到提示不能多个字段的DDL语句报错。顺便看了下官网v6.5+支持并行DDl，读者可以去测试：https://docs.pingcap.com/zh/tidb/stable/basic-features

正文完
django python
发表至： Python 代码编程
2023-06-24
 
xadocker
版权声明：本站原创文章，由 xadocker 2023-06-24发表，共计15355字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
有什么好看的电影的呢？
Django-初次见面
python批量插入es数据
用selenium给我的flask商品中心服务整点样例数据
评论（一条评论）
阿斯顿 评论达人 LV.1
2023-06-28 11:24:39 回复

原来如此

 Macintosh  Chrome  中国广东省广州市联通